procedure

Sted Procedure

Stored Procedure(저장 프로시저)란

DB내부에 저장된 일련의 SQL명령문을들 하나의 함수처럼 실행하기 위한 쿼리들의 집합이다.

​

장점

Stored Procedure을 사용하면 반복적인 작업을 피할 수 있고,

개발언어에 비의존적이므로 개발언어가 바뀌어도 동작하는 데 있어 문제가 없다.

​

문법

아래 형식에 맞추어 저장 프로시저를 작성한다.

​

DELIMITER $$ <-- 저장프로시저의 코딩할 부분을 묶는다 (DELIMITER은 문법의 끝(기본적으로 세미콜론)을 바꿔준다)

CREATE PROCEDURE Name(매개변수)

BEGIN

SQL 프로그래밍 코딩부

END $$

DELIMITER ; <-- 저장프로시저의 작성을 다 마쳤으면 DELIMITER 의 값을 다시 세미콜론으로 바꿔준다

​

CALL 저장프로시저이름(); <-- 저장프로시저를 호출한다

​

ex)​

DELIMITER $$

CREATE PROCEDURE userPROC()

BEGIN

SELECT * FROM EMP;

SELECT * FROM DEPT;

END $$

DELIMITER ;

​

호출할 땐

CALL userPROC(); 을 사용한다.

​

저장된 저장 프로시저를 확인하려면,

SHOW CREATE PROCEDURE userPROC;

​

​

위에서 작성한 프로시저는 단순히 EMP와 DEPT테이블의 튜플들을 불러오는 역할만 수행하기 때문에 매개변수를 지정할 필요가 없었다.

하지만 경우에 따라서는 프로시저의 매개변수를 선언하여 다소 복잡한 명령문을 실행할 수도 있다.

​

프로시저 내부에서 돌아갈 변수를 선언해주자.

CREATE PROCEDURE [프로시저이름] (IN [입력매개변수이름] [데이터형식] ... )

으로 내부변수를 지정할 수 있다.

​

호출시 지정된 테이블에 INSERT 시키는 프로시저를 만들어보자.

테이블에 INSERT를 하려면 변수로 해당 테이블의 튜플 타입에 맞는 매개변수들이 필요할 것이다.

​

major1 테이블의 구조는 다음과 같다.

이에 맞춰 저장프로시저는 다음처럼 짤 수 있다.

​

DELIMITER $$

CREATE PROCEDURE MJ_PR

(IN usr_NUM int(5),

IN usr_KEY varchar(10),

IN usr_NAME varchar(18),

IN usr_BUILDING varchar(10))

BEGIN

INSERT INTO major1 VALUES (usr_NUM, usr_KEY, usr_NAME, usr_BUILDING);

END $$

DELIMITER ;

​

이 프로시저를 호출할 때는

CALL MJ_PR(6, 'E', 'BIOLOGY', 'IH') 처럼 프로시저이름과 매개변수를 결합시킨다.

CALL MJ_PR(6, 'E', 'BIOLOGY', 'IH')

이렇게 자동으로 테이블에 데이터가 저장하는 기능 뿐 아니라 CRUD 기능의 전부를 저장프로시저를 통해 편리하게 작업하는것이 가능하다.

​

다른 예시로

프로시저 매개변수에 PK숫자를 넣었을 때 그 숫자에 해당하는 튜플이 나오는 프로시저도 가능하다.

​

EMP테이블을 꺼내보자. 이 테이블에서 PK는 EMPNO이다.

select * from EMP;

​

DELIMITER $$

CREATE PROCEDURE emp_pd

(IN usr_empno int)

BEGIN

SELECT * FROM EMP WHERE EMPNO = usr_empno;

END$$

DELIMITER ;

​

CALL emp_pd(7934);

CALL emp_pd(7934);

​

​

​

조건문

여타 프로그래밍 언어가 조건문과 반복문 기능을 가지고 있는 것 처럼 저장 프로시저에서도 마찬가지로 조건문과 반복문을 사용할 수 있다.

​

1. IF ... ELSE ... END IF 문

​

IF 조건1 THEN

SQL 명령1;

ELSEIF 조건2 THEN

SQL 명령2;

…

ELSE

SQL 명령N;

END IF;

​

​

2. CASE 문

​

CASE

WHEN 조건1 THEN

SQL 명령 1;

WHEN 조건2 THEN

SQL 명령 2;

…

ELSE

SQL 명령 N;

END CASE;

​

​

반복문

저장 프로시저에서 While문을 통해 반복문을 만들 수 있다.

하지만 프로시저의 LABEL / ITERATE 문은 자바와 같은 프로그래밍과는 다른 특징을 보여준다

​

우선 while 문 자체는 간단하다.

​

1. WHILE 문

​

WHILE 조건 DO

SQL 명령문;

END WHILE;

​

하지만 유의할 점은, 프로시저에서의 while은 자바에서처럼 자동으로 조건에 따른 무한반복이 되지 않는다.

밑에 나오는 ITERATE문을 통해 다시 while 조건절로 이동시킨다.

​

2. LABEL 지정

- WHILE 문에 라벨(사용자지정이름)을 지정한다

​

3. ITERATE 문

- ITERATE문을 만나면 바로 WHILE문으로 이동하여 다시 조건을 비교한다

​

4. LEAVE 문

- LEAVE문을 만나면 WHILE문을 빠져나온다

​

실제 WHILE, ITERATE, LEAVE문을 사용해보기 전에 변수를 선언하는 법에 대해 알아보자.

이 변수선언은 처음 설명했던 내부변수와는 다르게 지역변수처럼 사용이 가능하다.

* 반드시 BEGIN 구문 이후에 넣어야 작동된다.

​

변수 선언

DECLARE num1 INT;

​

값 대입

SET num1 = 100;

​

​

활용

예제를 보면서 이해해보자

​

예제1.

- 1~100의 수 중 5의 배수를 제외한 나머지 수들의 합계를 구하는데,

합이 500이 넘지 않도록 하는 프로시저를 만드시오.

​

DELIMITER $$

CREATE PROCEDURE sum5pc()

BEGIN

DECLARE i int;

DECLARE sum int;

SET i=1;

SET sum=0;

myLoop:WHILE(i<=100) DO //while 문에 LABEL 지정

​

IF(i%5=0) THEN

SET i=i+1;

ITERATE myLoop; // 지정한 LABEL문으로 이동

END IF;

​

SET sum = sum+i;

​

IF(sum>500) THEN

SET sum=sum-i;

LEAVE myLoop; // 지정한 LABEL문을 빠져나옴 (while문 종료)

END IF;

​

SET i=i+1;

END WHILE;

​

END $$

​

CALL sum5pc();

​

​

​

예제2.

EMP 테이블에 존재하는 7876번 사원의 급여가 1300미만이면 LOW, 1300이상 2900미만이면 MIDDLE,

2900이상이면 HIGH로 표시하는 SQL문을 작성하시오.

​

- 프로시저 이름은 RANK_SAL

- 입력받는 매개변수 이름은 P_EMPNO, 데이터형식은 INT(10)

- 급여정도는 (HIGH, MIDDLE, LOW) 의 컬럼명은 GRADE로 출력

​

DELIMITER $$

CREATE PROCEDURE RANK_SAL()

(IN P_EMPNO INT(10))

BEGIN

SELECT EMPNO, ENAME, JOB, SAL

(CASE

WHEN SAL < 1300 THEN 'LOW'

WHEN SAL < 2900 THEN 'MIDDLE'

ELSE 'HIGH'

END) AS 'GRADE'

FROM EMP

WHERE EMPNO=P_EMPNO;

END $$

​

DELIMITER ;

​

CALL RANK_SAL(7876);

​

​

예제2.

10%의 세율을 얻는 함수

​

DELIMITER $$

CREATE FUNCTION tax(p_tax INT)

RETURNS DECIMAL(5,2)

BEGIN

RETURN (p_tax * 0.1);

END $$

​

DELIMITER ;

 

​

프로시저 호출은 내부변수의 값까지 넣어주어야 실행된다. 예컨데 위의 예지의 프로시저의 호출은

SELECT tax(1300);

​

프로시저 삭제는

DROP PROCEDURE [프로시저이름];

 

출처 : m.blog.naver.com/cec7777/221722327481