[MySQL] Stored Procedure, Stored Function 이론 및 예제

목차

Stored Program

Stored Procedure

Exception

Stored Function

 

---

Stored Program

MySQL에서 제공하는 프로그래밍 언어 기능을 통틀어 일컫는 말

쿼리를 하나로 묶어 이름을 붙이고 호출하는 원리로 동작

Stored Program의 종류

- Stored Procedure

- Stored Function

- Trigger

- Cursor

 

변수 선언

DECLARE [변수 이름] [데이터 형식];

DECLARE num1 INT; -> num1 변수 선언

 

값 대입

SET [변수 이름] = 값;

SET num1 = 100; -> num1 변수에 값 대입

 

---

Stored Procedure (저장 프로시저)

MySQL에서 제공하는 프로그래밍 기능

쿼리의 집합으로서 어떠한 동작을 일괄 처리하는데 사용됨

모듈화해서 필요할 때마다 호출

DELIMITER $$
CREATE PROCEDURE 스토어드프로시저이름(매개변수)
BEGIN
~SQL 프로그래밍 코딩부분
END $$
DELIMITER ;

CALL 스토어드프로시저이름(); -> 생성한 프로시저 호출(실행)

 

입력 매개변수 지정

IN 입력매개변수이름 데이터형식

 

출력 매개변수 지정

OUT 출력매개변수이름 데이터형식

 

입력 매개변수가 있는 스토어드 프로시저 실행

CALL 프로시저이름(전달값);

 

예제

DELIMITER $$
CREATE PROCEDURE userPROC()
BEGIN
SELECT * FROM EMP;
SELECT * FROM DEPT;
END $$
DELIMITER ;

CALL userPROC();

 

작성된 Stored Procedure 확인

SHOW CREATE PROCEDURE 프로시저명;

 

Stored Procedure의 활용 1

CRATE TABLE STD(
SNO INT PRIMARY KEY,
SNAME VARCHAR(14) NOT NULL,
AGE INT NOT NULL);

STD 테이블에 데이터를 삽입할 때

INSERT INTO STD VALUES (...) 를 여러 번 수행해야 한다.

이 때, Stored Procedure를 활용해보자.

 

매개변수 : P_SNO, P_SNAME, P_AGE

DELIMITER $$
CREATE PROCEDURE ADD_STD(
IN P_SNO INT,
IN P_SNAME VARCHAR(14),
IN P_AGE INT)
BEGIN
INSERT INTO STD VALUES(P_SNO, P_SNAME, P_AGE);
END $$
DELIMITER ;

CALL ADD_STD(10,'PARK',21);
CALL ADD_STD(20,'KIM',20);

 

Stored Procedure 의 활용 2

DELIMITER $$
CREATE PROCEDURE PRINT_STD(
IN P_SNO INT)
BEGIN
SELECT * FROM STD WHERE SNO=P_SNO;
END $$
DELIMITER ;

CALL PRINT_STD(20);

 

---

IF ... ELSE ... END IF 문

조건에 따라 분기하는 명령 (이중 분기)

IF 조건1 THEN
SQL 명령1;
ELSEIF 조건2 THEN
SQL 명령2;
...
ELSE
SQL 명령N;
END IF;

 

Stored Procedure의 활용 3

DELIMITER $$
CREATE PROCEDURE PRINT_NUM(IN NUM INT)
BEGIN
IF (NUM>10) THEN
SELECT NUM;
ELSEIF (NUM<10) THEN
SELECT NUM*10;
ELSE
SELECT POW(NUM,2);
END IF;
END $$
DELIMITER ;

CALL PRINT_NUM(5);
CALL PRINT_NUM(10);

 

---

CASE 문

조건에 따라 분기하는 명령 (다중 분기)

//SELECT 문에서의 CASE 사용

CASE
WHEN 조건1 THEN '반환값'
WHEN 조건2 THEN '반환값'
...
ELSE '반환값'
END;


//Stored Procedure 내에서의 CASE 사용

CASE
WHEN 조건1 THEN
SQL 명령1;
WHEN 조건2 THEN 
SQL 명령2;
...
ELSE
SQL 명령N;
END CASE;

 

Stored Procedure의 활용 4

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

1300 이상 2900 미만이면 MIDDLE, 2900 이상이면 HIGH로 표시하는 SQL 문 작성

SELECT EMPNO, ENAME, JOB, SAL,
(CASE
WHEN SAL<1300 THEN 'LOW'
WHEN SAL<2900 THEN 'MIDDLE'
ELSE 'HIGH'
END) AS 'RANK'
FROM EMP
WHERE EMPNO=7876;

 

이 SQL문을 응용하여 EMPNO를 입력 받아

'이름(ENAME), 급여(SAL), 급여정도(HIGH, MIDDLE, LOW)' 를 구분하는 Stored Procedure를 작성해보자.

- 프로시저 이름은 RANK_SAL

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

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

DELIMITER $$
CREATE PROCEDURE RANK_SAL(IN P_EMPNO INT(10))
BEGIN
SELECT ENAME, 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);

 

---

WHILE문

조건이 참인 동안 WHILE 문 내의 명령을 반복함

WHILE 조건 DO
SQL 명령문;
END WHILE;

 

LABEL 지정

- WHILE문에 LABEL을 지정

 

ITERATE 문

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

 

LEAVE 문

- LEAVE 문을 만나면 WHILE 문을 빠져나옴

 

예제

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

합이 500이 넘지 않도록 하는 프로그램

 

DECLARE i INT;
DECLARE sum INT;
SET i=1;
SET sum=0;

myLoop: WHILE(i<=100) DO
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;
END IF;

SET i=i+1;
END WHILE;

 

---

Exception 오류 처리

액션

- 오류가 발생했을 때의 행동을 정의

- CONTINUE : 처리할 문장 부분을 처리하고 계속 진행

- EXIT : 처리할 문장 부분을 처리하고 Stored Procedure 종료

오류 조건

- 어떤 오류를 처리할 것인지 지정

- MYSQL_ERRORCODE : MySQL에서만 유효한 에러 식별 번호 (4자리 숫자)

- SQLSTATE : ANSI SQL 표준을 준수하는 DBMS에서 갖는 여러 가지 상태를 의미하는 코드 (5자리 문자열)

- SQLEXCEPTION : 에러가 발생하는 상황을 제어하는 데사용

- SQLWARNING : 경고가 발생하는 상황을 제어하는 데 사용

- NOT FOUND

DECLARE 액션 HANDLER FOR 오류조건 처리할문장;

 

Table이 없는 경우 예제

오류 코드 : 1146 OR 상태 코드 : '42S02'

DELIMITER $$
CREATE PROCEDURE errorProc1()
BEGIN
DECLARE CONTINUE HANDLER FOR 1146
SELECT '이 테이블은 존재하지 않습니다.' AS '메시지';
SELECT * FROM DEPT;
SELECT * FROM noTable;
SELECT * FROM EMP;
END $$
DELIMITER ;

*CONTINUE 나 EXIT으로

 

---

Stored Function (스토어드 함수)

사용자 정의 함수

SELECT 문 안에서 호출

RETURNS 문으로 하나의 값을 반환

DELIMITER $$
CREATE FUNCTION 스토어드함수이름(매개변수)
RETURNS 반환형식
BEGIN
~SQL 프로그래밍 코딩부
RETURN 반환값;
END $$
DELIMITER ;

SELECT 스토어드함수이름();

 

스토어드 함수 생성 시

시스템 변수인 log_bin_trust_function_creators를 on으로 변경

//ON/OFF 확인

SHOW global variables like 'log_bin_trust_function_creators';

//ON 으로 변경

SET global log_bin_trust_function_creators=1;

 

Stored Function 의 활용 1

출생연도를 입력하면 나이가 출력되는 함수

DELIMITER $$
CREATE FUNCTION getAgeFunc(byear INT)
RETURNS INT
BEGIN
DECLARE age INT
SET age=YEAR(CURDATE())-byear+1;
RETURN age;
END $$
DELIMITER ;

SELECT getAgeFunc(1988);

 

Stored Function 의 활용 2

10%의 세율을 얻는 함수

DELIMITER $$
CREATE FUNCTION tax(p_value INT)
RETURNS DECIMAL(5,2)
BEGIN
RETURN (p_value*0.1);
END $$
DELIMITER;

SELECT tax(1300);

 

생성한 Stored Function 확인

SHOW CREATE FUNCTION 함수명;

 

프로시저 삭제

DROP PROCEDURE 프로시저명;

 

함수 삭제

DROP PROCEDURE 함수명;

 

Stored Procedure / Stored Function 차이점

	Stored Procedure	Stored Function
매개변수	입력, 출력 매개변수 모두 사용	입력 매개변수만 사용
반환	반환 구문 x, OUT으로 값 반환	RETURN 문으로 하나의 값 반환
호출	CALL로 호출	SELECT문 안에서 호출
SELECT 문 사용	프로시저 내 SELECT 문 사용 가능	함수 내 SELECT 문 사용 불가
주 역할	여러 쿼리문을 하나로 묶어 일괄 처리	어떤 계산을 통해 하나의 값을 반환

 

---