Computer Science/DB
Stored Procedure
nauni
2021. 5. 21. 00:08
Stored Program
- SQL 명령어는 선언적
- What 에 초점
- 데이터베이스에서 절차적 명령어(How)를 수행하기 위해 stored program 을 사용
- 스토어드 루틴 이라고 하기도 함
- 스토어드 함수, 스토어드 프로시저, 트리거, 이벤트 핸들러 등을 포괄하는 개념
- 게임분야에서 많이 사용한다고 한다(웹에서도 사용한다고 함)
- ORM 등을 사용하면서 사용이 줄었다고 하는 듯 하다.
장점
- 응용프로그램의 성능향상
- 네트워크 트래픽 감소
- 보안성 항상: 자체적인 보안 설정 기능이 있어, 스토어드 프로그램 단위로 실행권한 부여 가능. SQL 인젝션 등을 방지가능
- 개발 업무의 구분: 애플리케이션 관리 조직과 DBMS 관련 조직간의 업무구분이 가능
단점
- 애플리케이션과 버전이 따로 관리됨 => 유지보수가 매우 어려움, Git 관리 어려움, 문제가 생겼을 시, rollback 문제
- 낮은 처리 성능: 느리다.
종류
- 스토어드 프로시져: 자주 사용되고 복잡한 쿼리를 모듈화 시켜 저장해두고 사용.
call 프로시저명;으로 실행 - 스토어드 함수: 스토어드 프로시져와 비슷하나 함수는 쿼리내에 사용가능하지만 프로시저는 불가능. 제약사항이 많다고 함.
- 트리거
- 이벤트
Stored Procedure
- 호출:
call 프로시저명; - 등록
DROP PROCEDURE IF EXISTS sp_sample;
DELIMITER $$ -- ; 대신 delimiter 를 $$ 로 설정
CREATE PROCEDURE sp_sample() -- 프로시저 생성
BEGIN -- 시작
DECLARE str CHAR(30) DEFAULT "hi"; -- 기본변수 선언(프로시저 내에서만 유효)
SET str = "This is sample"; -- 기본변수에 값 넣기
SELECT str;
END $$ -- 끝내기
DELIMITER ; -- $$ 대신 다시 delimiter 를 ;로 변경
CALL sp_sample(); -- 호출- 세션변수: 세선(접속) 내에서 계속 유효,
SET @변수명 = 값; - 쿼리의 결과를 변수에 넣을 수 있다.(쿼리의 결과 값이 스칼라 값일 때만 가능)
- if, while, case, repeat~until(do while 과 같은 용도) 등을 사용 가능 (문법은 좀 다르므로 사용시 확인이 필요)
- 함수와는 다르게 리턴을 하지 않음
- 매개변수를 사용하여 값을 넣고 확인이 가능
- IN: readOnly, 입력에 사용
- OUT: 출력에 사용
- INOUT: IN + OUT 기능을 합친 것
실습
DROP PROCEDURE IF EXISTS sp_star;
DELIMITER $$
CREATE PROCEDURE sp_star(IN num INT)
BEGIN -- procedure 시작
DECLARE a INT; -- 선언
WHILE num > 0 DO -- while
SET a = num * num; -- 변수 셋팅
SET num = num - 1;
INSERT INTO star (stars) VALUES (a); -- 쿼리문 실행
END WHILE; -- while 종료
END $$ -- procedure 종료
DELIMITER ;
CALL sp_star(10); -- 호출- 결과
mysql> select * from star;
+----+-------+
| id | stars |
+----+-------+
| 1 | 100 |
| 2 | 81 |
| 3 | 64 |
| 4 | 49 |
| 5 | 36 |
| 6 | 25 |
| 7 | 16 |
| 8 | 9 |
| 9 | 4 |
| 10 | 1 |
+----+-------+
10 rows in set (0.00 sec)