[EP.10] 스토어드 프로시저의 모든 것

 

 

 

---

 

[ Index ]

 

1. 스토어드 프로시저

(1) 생성 및 삭제      (2) 매개변수      (3) SQL 프로그래밍      (4) 동적SQL

 

2. 기타 프로그래밍 기능

(1) 스토어드 함수      (2) 커서      (3) 트리거

 

---

 

 

 

 

1. 스토어드 프로시저

 

스토어드 프로시저란 MySQL에서 제공하는 프로그래밍 기능이다.

프로그래밍 기능과 쿼리문을 묶어주는 기능을 제공한다.

 

(1) 생성 및 삭제

• 스토어드 프로시저 생성

-- 스토어드 프로시저 생성

-- sample 데이터베이스 사용한다.
USE sample;

-- mem_proc 스토어드 프로시저가 있다면 삭제한다.
DROP PROCEDURE IF EXISTS mem_proc;

-- 구분자를 $$로 변경한다.
DELIMITER $$

-- 스토어드 프로시저를 생성한다.
CREATE PROCEDURE mem_proc()

-- 스토어드 프로시저의 내용
BEGIN
    SELECT * FROM member;
END $$

-- 구분자를 ;로 변경한다.
DELIMITER ;

-- mem_proc 스토어드 프로시저를 실행한다.
CALL mem_proc();

※ DELIMITER로 구분자를 변경할 수 있다.

SQL문의 구분자인 세미콜론(;)과 구분하기 위해서 스토어드 프로시저를 생성하기 전에

새로운 구분자인 $$로 바꿔서 진행한다.

 

• 스토어드 프로시저 삭제

-- 스토어드 프로시저 삭제
DROP PROCEDURE mem_proc;

※ DROP PROCEDURE에서는 괄호를 붙이면 안 된다.

 

 

(2) 매개변수

• 입력 매개변수

       스토어드 프로시저 실행 시 입력 매개변수를 통해 값을 전달할 수 있다.

-- 입력 매개변수

-- sample 데이터베이스를 사용한다.
USE sample;

-- mem_proc1 스토어드 프로시저가 있으면 삭제한다.
DROP PROCEDURE IF EXISTS mem_proc1;

-- 구분자를 $$로 변경한다.
DELIMITER $$

-- mem_proc1 스토어드 프로시저를 생성한다.
-- VARCHAR(4) 형식인 입력 매개변수를 설정한다.
CREATE PROCEDURE mem_proc1(IN memName VARCHAR(4))

-- 스토어드 프로시저 내용
BEGIN
SELECT * FROM member WHERE mem_name = memName;
END $$

-- 구분자를 ;로 변경한다.
DELIMITER ;

-- 스토어드 프로시저를 실행한다.
-- '김태연'을 입력 매개변수로 전달한다.
CALL mem_PROC1('김태연');

Result

※ 입력 매개변수를 여러 개 설정해도 된다.

 

• 출력 매개변수

       스토어드 프로시저의 실행 결과를 출력 매개변수를 통해 얻을 수 있다.

-- 출력 매개변수

-- mem_proc2 스토어드 프로시저가 있으면 삭제한다.
DROP PROCEDURE IF EXISTS mem_proc2;

-- 구분자를 $$로 변경한다.
DELIMITER $$

-- mem_proc2 스토어드 프로시저를 생성한다.
-- CHAR(4) 형식인 입력 매개변수를 설정한다.
-- CHAR(8) 형식인 출력 매개변수를 설정한다.
CREATE PROCEDURE mem_proc2(
    IN memID CHAR(4),
    OUT memPhone CHAR(8))

-- 스토어드 프로시저 내용
BEGIN
    SELECT phone INTO memPhone FROM member WHERE mem_id = memID;
END $$

-- 구분자를 ;로 변경한다.
DELIMITER ;

-- 스토어드 프로시저를 실행한다.
-- 'A111'을 입력 매개변수로 전달한다.
-- @myValue에 출력값을 전달 받는다.
CALL mem_proc2('A111', @myValue);

-- 출력 매개변수로 전달받은 값을 조회한다.
SELECT CONCAT('회원 연락처 ==> ', @myValue);

Result

※ 입력 매개변수와 출력 매개변수를 함께 설정해도 된다.

 

 

(3) SQL 프로그래밍

가입 연도가 2023년이면 '신규 회원', 2023년 이전이면 '기존 회원'으로 분류하는 쿼리를
IF ~ ELSE 문을 이용해 작성하고자 한다. 

-- IF ~ ELSE 문

DROP PROCEDURE IF EXISTS mem_proc3;
DELIMITER $$
CREATE PROCEDURE mem_proc3(
    IN memID VARCHAR(4)
)
BEGIN
    -- joinYear 변수를 생성한다.
    DECLARE joinYear INT;
    -- 입력 받은 회원의 가입 연도를 joinYear 변수에 저장한다.
    SELECT YEAR(join_date) INTO joinYear 
        FROM member
        WHERE mem_id = memID;
    -- 2023년 가입자이면, '신규 회원'이라고 출력한다.
    IF (joinYear < 2023) THEN
        SELECT '신규 회원' AS "회원 구분";
	-- 2023년 이전 가입자이면, '기존 회원'이라고 출력한다.
    ELSE
	SELECT '기존 회원' AS "회원 구분";
    END IF;
END $$
DELIMITER ;
CALL mem_proc3('A111')

Result

 

 

(4) 동적 SQL

테이블명과 컬럼명을 입력받아 데이터를 조회하는 쿼리를 동적 SQL을 이용해 작성하고자 한다.

-- 동적 SQL

DROP PROCEDURE IF EXISTS mem_proc4;
DELIMITER $$
CREATE PROCEDURE mem_proc4(
    IN tableName VARCHAR(10),
    IN colName VARCHAR(10)
)
BEGIN
    -- 실행할 SQL 문을 변수에 저장한다.
    SET @sqlQuery = CONCAT('SELECT ', colName, ' FROM ', tableName);
    -- SQL 문을 미리 준비해놓는다.
    PREPARE myQuery FROM @sqlQuery;
    -- 준비한 SQL 문을 실행한다.
    EXECUTE myQuery;
    -- SQL 문을 해제한다.
    DEALLOCATE PREPARE myQuery;
End $$
DELIMITER ;
-- member 테이블의 mem_name 컬럼을 조회한다.
CALL mem_proc4('member', 'mem_name');

Result

 

 

 

 

2. 기타 프로그래밍 기능

 

(1) 스토어드 함수

MySQL에서 제공하는 함수가 없다면, 직접 만들어서 사용할 수 있다.

이를 가능하게 하는 것이 스토어드 함수이다.

-- 스토어드 함수 생성 권한 허용
SET GLOBAL log_bin_trust_function_creators = 1;

USE sample;
DROP FUNCTION IF EXISTS diffFunc;
DELIMITER $$
-- 두 숫자의 차이를 계산하는 스토어드 함수 생성
CREATE FUNCTION diffFunc(num1 INT, num2 INT)
    -- 함수가 반환하는 값의 데이터 형식 설정
    RETURNS INT
BEGIN
    -- 결과 반환
    RETURN num1 - num2;
END $$
DELIMITER ;

SELECT diffFunc(200, 100) AS 'Result';

Result

※ RETURNS와 RETURN 구분할 것 !

 

 

(2) 커서

커서는 테이블을 한 행씩 처리하기 위한 방식이다.

커서는 첫 번째 행부터 마지막 행까지 한 행씩 접근해서 값을 처리한다.

USE sample;
DROP PROCEDURE IF EXISTS mem_cursor;
DELIMITER $$
-- 평균 가입 일수를 구하는 커서 생성
CREATE PROCEDURE mem_cursor()
BEGIN
    DECLARE joinDate DATE; -- 가입일자
    DECLARE joinDays INT; -- 가입일수
    DECLARE cnt INT DEFAULT 0; -- 행의 수
    DECLARE totDays INT DEFAULT 0; -- 전체 가입일수 합계
    DECLARE end0fRow BOOLEAN DEFAULT FALSE; -- 마지막 행인지 파악
    -- 커서 선언
    DECLARE memCursor CURSOR FOR
        SELECT join_date FROM member;
    -- 반복 조건 준비    
	DECLARE CONTINUE HANDLER
        -- 더 이상 행이 없을 때 문장 수행
	    FOR NOT FOUND SET end0fRow = TRUE;
    -- 커서 열기    
    OPEN memCursor;
    -- 커서 반복
    cursor_loop: LOOP
        -- 한 행씩 읽어옴
        FETCH memCursor INTO joinDate;
        -- 마지막 행이면 반복을 종료
        IF end0fRow THEN
            LEAVE cursor_loop;
		END IF;
        -- 가입일수 계산
        SET joinDays = DATEDIFF(CURDATE(), joinDate);
        SET cnt = cnt + 1;
        SET totDays = totDays + joinDays;
	END LOOP cursor_loop;
    -- 평균 가입일수 계산
    SELECT (totDays/cnt) AS '회원의 평균 가입 일수';
    -- 커서 닫기
    CLOSE memCursor;
END $$
DELIMITER ;

CALL mem_cursor();

Result

 

 

(3) 트리거

트리거는 테이블에 INSERT, UPDATE, DELETE가 발생하면 자동으로 실행되는 코드이다.

아래의 코드를 통해 트리거를 생성해 놓으면  member 테이블에서 삭제가 발생했을 때,

자동으로 백업용 테이블에 해당 내용을 저장한다.

DROP TRIGGER IF EXISTS mem_deleteTrg;
DELIMITER $$
-- 데이터가 삭제되면 자동으로 실행되는 트리거 생성
CREATE TRIGGER mem_deleteTrg
    AFTER DELETE
    ON member -- 트리거를 부착할 테이블
    FOR EACH ROW
BEGIN
    -- 백업용 테이블에 삭제된 데이터 입력
    INSERT INTO mem_del VALUES(OLD.mem_id, OLD.mem_name, OLD.addr, OLD.phone, '삭제', CURDATE(), CURRENT_USER());
END $$
DELIMITER ;

※ OLD 테이블은 UPDATE나 DELETE가 수행될 때, 데이터가 잠깐 저장되는 임시 테이블

 

 

 

 

 

---

 

[ Reference ]

 

📙 우재남, 한빛미디어, 혼자 공부하는 SQL

 

---