Stored Procedure

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)

참고

• 생활코딩 DB
• 참고 블로그