[DBMS] 저장 프로시저 (Stored Procedure)

• 저장 프로시저(Stored Procedure)

일련의 작업 절차를 정리해서 저장한 것

여러 SQL문을 묶어서 미리 정의해 두고 하나의 요청으로 실행할 수 있음 

자주 사용되는 복잡한 작업들을 간단하게 실행 할 수 있음

Application 에서 직접 모든 작업을 요청하지 않아도 되기 때문에 부하가 줄어들고 보안이 향상됨

단, 검증되지 않은 저장 프로시저를 실행하는 것은 매우 위험함

MySQL은 5.0버전부터 사용 가능

 

• 저장 프로시저 생성

mysql> DELIMITER //  mysql> CREATE PROCEDURE <프로시저 이름>( [ ARGUMENT , ... ] )             BEGIN                   실행문 ;                    실행문 ;                           ....             END //  mysql> DELIMITER ;

- DELIMITER

저장 프로시저 안에 여러 SQL문이 들어가는데 각 SQL문의 끝을 나타내기 위해 세미콜론( ; )을 입력 해야 함

→ 프로시저를 생성하는 명령이 끝나버리는 문제가 발생

명령의 끝을 나타내는 구분문자를 임시로 변경하여 작성

mysql> DELEMITER <구문문자>

프로시저 작성이 끝난 뒤에는 반드시 구문문자를 다시 변경 해 줘야 함

 

ARGUMENT(인자값)

프로시저가 실행 될 때 입력 받을 값(프로시저 실행 시 입력해 줌)

형식 : ( 인자이름 데이터형식 [ , ... ] )

 

BEGIN

저장 프로시저 범위의 시작

 

END

저장 프로시저 범위의 끝

---

• 저장 프로시저 확인

프로시저 목록 확인

mysql> SHOW PROCEDURE STATUS ;

 

특정 프로시저 설정 확인

mysql> SHOW CREATE PROCEDURE <프로시저 이름> ;

---

• 저장 프로시저 실행

mysql> CALL <프로시저 이름>( ) ;

( ) 는 생략 가능

---

• 저장 프로시저 삭제

mysql> DROP procedure <프로시저 이름> ; 

---

• 프로시저 내 변수 사용

프로시저 내 지역변수를 선언하여 사용

선언 시 데이터 타입, 기본값 지정 가능

변수 선언 DECLARE <변수명>  <데이터타입> [ DEFAULT <기본값> ] ;  변수에 데이터 저장 SET <변수명> = <데이터> ; <변수명> := <데이터> 변수 사용 <변수명>

---

• 조건문(IF문) 사용

조건에 따라 실행 여부를 결정하는 흐름 제어문

ELSEIF 와 ELSE 는 생략 가능

IF <조건1> THEN  <조건1 만족할 때 실행할 내용; ... >  ELSEIF <조건2> THEN  <조건2 만족할 때 실행할 내용; ... >  ELSE <상위 조건들 모두 만족하지 않을 때 실행할 내용; ... >  END IF;

---

• 선택문(CASE 문) 사용

조건에 따라 실행 내용을 선택하는 흐름 제어문

WHEN 은 반드시 하나 이상은 있어야 함

ELSE 는 생략 가능

CASE <변수>  WHEN <값1> THEN  <변수의 값이 값1과 일치할 때 실행할 내용; ... >  WHEN <값2> THEN  <변수의 값이 값2과 일치할 때 실행할 내용; ... >  ELSE  <변수의 값과 일치하는 값이 없을 때 실행할 내용, ... >  END CASE;

---

• 반복문(WHILE문) 사용

조건의 값이 참인 동안 반복해서 실행하는 흐름 제어문

원하는 횟수만큼 반복하기 위해서 조건을 잘 지정해 줘야 함

WHILE <조건> DO  <조건이 참인 동안 반복 실행할 내용; ... >  END WHILE ;