개념

stored procedure란? query문의 집합으로 어떠한 동작을 일괄처리하기 위한 용도로 사용하는 것이다. 여러쿼리를 한번에 수행하는 것이라고 할 수도 있다.

• 다른 프로그램의 module화, function, method, batch, macro ...와 유사하다.
  SQL언어에 절차적인 프로그래밍이 가능하도록 변수, 제어문 등과 같은 기법을 적용한다.
• 기본 구조 : begin(실행부) ~ exception(예외처리부) ~ end
• 장점 : 편리한 유지 보수, 개발 업무와 분리가 가능 , 네트워크 부하 감소, 처리 시간 축소 등의 장점이 있다.

문법과 예시

• 기본 형식

DELIMITER // -- // 또는 $$ \
CREATE OR REPLACE PROCEDURE 프로시저이름(변수)
BEGIN
DECLARE 변수선언
SELECT 추출
END; -- 세미콜론 없어도 됨
//
DELIMITER ;

SHOW CREATE PROCEDURE 프로시저명; -- 만들어진 구조 보기
DROP PROCEDURE 프로시저명; -- 삭제

CALL 프로시저명(입력값); -- 입력값에 따른 결과 보기 
``` \
- ex1
``` sql
DELIMITER //
CREATE OR REPLACE PROCEDURE sp_1(a INT, b INT) -- 변수 선언, 대소문자 구분 x
BEGIN
DECLARE X,Y INT DEFAULT 0; 
SET X = 10;
SELECT X,Y;
SELECT a + b; 
END; 
//
DELIMITER ;

CALL sp_1(1,3);

SHOW CREATE PROCEDURE sp_1; 

• ex2

DELIMITER //
CREATE OR REPLACE PROCEDURE sp_2() 
BEGIN
SELECT * FROM jikwon WHERE jikwon_no < 3;
SELECT * FROM buser;
END; 
//
DELIMITER ;

CALL sp_2;

• ex3

DELIMITER //
CREATE OR REPLACE PROCEDURE sp_3(ar1 INT, ar2 INT) 
BEGIN
SELECT * FROM jikwon WHERE jikwon_no = ar1;
SELECT * FROM jikwon WHERE jikwon_no = ar2;
END;
//
DELIMITER ;

CALL sp_3(3,8);

• ex4

DELIMITER //
CREATE OR REPLACE PROCEDURE sp_4(IN jik varchar(20) CHARSET UTF8, num int) 
BEGIN
SELECT jik;
SELECT * FROM jikwon WHERE jikwon_jik = jik;
if(num = 10) then SELECT * FROM jikwon WHERE buser_num = 10;
ELSEIF(num =20) then SELECT * FROM jikwon WHERE buser_num = 20;
ELSE SELECT * FROM jikwon WHERE buser_num != 10;
END if;
END;
//
DELIMITER ;

CALL sp_4('대리',10);

• ex5 while 반복문

DELIMITER //
CREATE OR REPLACE PROCEDURE sp_5() 
BEGIN
DECLARE X INT;
DECLARE str VARCHAR(255);
SET X = 1;
SET str = '';
while X <= 5 DO 
	SET str = CONCAT(str, X, ',');
	SET X = X + 1;
END while;
SELECT str;
END;
//
DELIMITER ;

CALL sp_5;

• ex6 repeat 반복문

DELIMITER //
CREATE OR REPLACE PROCEDURE sp_6() 
BEGIN
DECLARE X INT;
DECLARE str VARCHAR(255);
SET X = 1;
SET str = '';
repeat 
	SET str = CONCAT(str, X, ',');
	SET X = X + 1;
until X > 5
END repeat;
SELECT str;
END
//
DELIMITER ;

CALL sp_6;