[SQL] Stored Procedure

우재남님의 "혼자 공부하는 SQL"을 기반으로 학습한 게시물입니다.

📕 목차

1. What is Stored Procedure?
2. Stored Funcion & Cursor
3. Trigger

1. What is Stored Procedure?

📌 Concept

Stored Procedure = SQL + 프로그래밍 기능

예컨데 일련의 쿼리를 모아 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

🟡 개념과 형식

DELIMITER $$
CREATE PROCEDURE [스토어드 프로시저 이름](IN 또는 OUT 매개변수)
BEGIN
    -- SQL 프로그래밍 코드
END $$
DELIMITER;

CALL [스토어드 프로시저 이름]();

자주 사용하는 쿼리를 묶어 호출만 하면 사용할 수 있다.
장점
- 하나의 요청으로 여러 SQL문 실행 가능 (Network 부하 감소)
- 미리 구문을 분석하고 중간 코드로 변환해놔야 하므로 처리 시간 감소
- Database Trigger와 결합하면 복잡한 규칙에 의한 데이터 참조무결성 유지 가능
단점
- 코드 자산으로서 재사용성 나쁨
- 업무 사양 변경 시 외부 응용 프로그램과 함께 프로시저 재정의 필요

🟡 생성과 호출

DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN
    SELECT * FROM member;
END $$
DELIMITER ;

CALL user_proc();

🟡 삭제

DROP PROCEDURE user_proc;

삭제할 때는 괄호를 붙이지 않고, 함수명만 명시한다.

📌 Practice

🟡 매개변수(parameter)

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

CALL [프로시저 이름](인수)

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

CALL [프로시저 이름](@변수명);
SELECT @변수명;

출력 매개변수에 값을 대입할 때는 주로 SELECT ~ INTO 문을 사용한다.

🟡 입력 매개변수

DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN
    SELECT * FROM member WHERE mem_name = userName;
END $$
DELIMITER ;

CALL user_proc1("에이핑크");

DELIMITER $$
CREATE PROCEDURE user_proc2(
    IN userNumber INT,
    IN userHeight INT)
BEGIN
    SELECT * FROM member 
        WHERE mem_number > userNumber AND height > userHeight;
END $$
DELIMITER ;

CALL user_Proc2(6, 165);

🟡 출력 매개변수

DELIMITER $$
CREATE PROCEDURE user_proc3(
    IN txtValue Char(10),
    OUT outValue INT)
BEGIN
    INSERT INTO noTable VALUES(NULL, txtValue);
    SELECT MAX(id) INTO outValue FROM noTable;
END $$
DELIMITER ;

-- 테이블 생성 후
CALL user_proc3("테스트1", @myValue);
SELECT CONCAT("입력된 ID 값 ==>", @myValue);

Stored Procedure를 등록하는 시점에는 Table이 존재하지 않아도 된다.
하지만 사용하는 시점에는 사용할 Table이 존재해야 한다.

🟡 활용

1️⃣ 조건문 사용

DELIMITER $$
CREATE PROCEDURE ifelse_proc(
    IN memName VARCHAR(10)
)
BEGIN
    Declare debutYear INT; -- 변수 선언
    SELECT YEAR(debut_date) INTO debutYear FROM member
        WHERE mem_name = memName;
    IF (debutYear >= 2015) THEN
        SELECT "신인 가수" AS "메시지";
    ELSE
        SELECT "고참 가수" AS "메시지";
    END IF;
END $$
DELIMITER ;

CALL ifelse_proc("오마이걸");

2️⃣ 반복문 사용

DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
    DECLARE hap INT; 
    DECLARE num INT; 
    SET hap = 0;
    SET num = 1;
    
    WHILE (num <= 100) DO
        SET hap = hap + num;
        SET num = num + 1;
    END WHILE;
    SELECT hap AS '1~100 합계';
END $$
DELIMITER ;

CALL while_proc();

3️⃣ Dynamic SQL

DELIMITER $$
CREATE PROCEDURE dynamic_proc(
    IN tableName VARCHAR(20)
)
BEGIN
    SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
    PREPARE myQuery FROM @sqlQuery;
    EXECUTE myQuery;
    DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;

CALL dynamic_proc("member");

SQL이 동적으로 변경된다
조회하는 Table이 고정되어 있지 않고, 이름을 매개변수로 받아 사용한다.

🙄 Stored Procedure를 써야 할까?

MySQL 쓰면서 하지 말아야 할 것 17가지

*MySQL 쓰면서 하지 말아야 할 것 17가지* 권장사항이다. 이것을 이해하면 당신의 어플리케이션이 더 나은 성능을 발휘할 것이다. 다만 이것이 사람의 실력을 판단하는 척도로 사용되서는 안 될 것

blog.lael.be

이건 그냥 구글링하다가 찾은 건데, 신빙성이 있는지는 모르겠고 공부하다가 참고해볼 목적으로 가져왔다.

ETL 타입 프로시저, 혹은 아주 복잡하지만 자주 실행되지는 않거나, 한 번 요청할 때마다 여러번 실행되는 간단한 함수들만 Stored Procedure를 쓰라고 한다.

근데 핵심은 저거보단 "이해하고 써라"가 맞지 싶다.

코드 자산으로서 재사용성이 나쁜점 때문에 실무에서 거의 안 쓰는 기능이라고 한다.

2. Stored Function & Cursor

📌 Stored Function

🟡 개념과 형식

DELIMITER $$
CREATE FUNCTION dynamic_proc(매개변수) RETURNS 반환형식
BEGIN
    -- 프로그래밍 코딩
    RETURN 반환값;
END $$
DELIMITER ;

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

사용자 정의 함수를 만들 때 사용한다.
IN, OUT을 사용할 수 없고, Parameter는 모두 입력 매개변수로 취급한다.
반환할 값의 데이터 타입을 RETURNS 문으로 지정하고, 하나의 값을 반환해야 한다.
SELECT로 호출한다.
집합 결과를 반환하는 SELECT문을 사용할 수 없다.
보통 간단하고 짧은 값을 얻기위해 사용한다.

🟡 사용 방법

SET GLOBAL log_bin_trust_function_creators = 1;

MySQL에서 Stored Function 생성 권한을 허용해주어야 한다.

DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT) RETURNS INT
BEGIN
    RETURN number1 + number2;
END $$
DELIMITER ;

SELECT sumFunc(100, 200) AS '합계';

DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT) RETURNS INT
BEGIN
    DECLARE runYear INT;
    SET runYear = Year(CURDATE()) - dYear;
    RETURN runYear;
END $$
DELIMITER ;

SELECT calcYearFunc(2010) AS '활동 횟수';

그냥 일반 함수 다루듯이 사용하면 된다.

📌 Cursor

🟡 개념

Procedure 내부에서 복수 개의 행을 처리할 때 사용하는 구성요소
C언어의 lseek()같은 놈인 것 같다.
Query 행 집합에서 한 행씩 옮겨가며 명령을 처리한다.
Cursor 자체는 결과 집합에서 현재 위치를 가리키다.

🟡 단계별 실습

1️⃣ DECLARE

-- 사용할 변수 선언
DECLARE memNumber INT;
DECLARE cnt INT DEFAULT 0;
DECLARE totNumber INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE;

-- 커서 생성
DECLARE memberCursor CURSOR FOR SELECT mem_number FROM member;

-- 반복 조건 선언
DECLARE CONTINUE HANDLER
    FOR NOT FOUND SET endOfRow = TRUE;

평균 인원수를 구하기 위한 변수 선언
- memNumber : 각 회원의 인원수
- totNumber : 전체 인원의 합계
- cnt : 읽은 행의 수
- endOfRow : 행의 끝을 파악하는 용도
커서와 반복 조건 선언
- 행의 끝에 다다르면 endOfRow를 TRUE로 바꾼다.

2️⃣ OPEN

OPEN memberCursor;

3️⃣ LOOP

cursor_loop: LOOP
    -- 반복
END LOOP cursor_loop

4️⃣ FETCH

cursor_loop: LOOP
    FETCH memberCursor INTO memNumber;
    
    IF endOfRow THEN
        LEAVE cursor_loop;
    END IF;
    
    SET cnt = cnt + 1;
    SET totNumber = totNumber + memNumber;
END LOOP cursor_loop

한 행씩 읽어온다.

5️⃣ LEAVE

IF endOfRow THEN
    LEAVE cursor_loop;
END IF;

반복문 탈출 조건

6️⃣ CLOSE

CLOSE memberCursor;

🟡 통합 코드

DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
    DECLARE memNumber INT;
    DECLARE cnt INT DEFAULT 0;
    DECLARE totNumber INT DEFAULT 0;
    DECLARE endOfRow BOOLEAN DEFAULT FALSE;
    
    DECLARE memberCursor CURSOR FOR SELECT mem_number FROM member;
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET endOfRow = TRUE;
    
    OPEN memberCursor;
    
    cursor_loop: LOOP
        FETCH memberCursor INTO memNumber;
        
        IF endOfRow THEN
            LEAVE cursor_loop;
        END IF;
        
        SET cnt = cnt + 1;
        SET totNumber = totNumber + memNumber;
    END LOOP cursor_loop;
    
    SELECT (totNumber/cnt) AS "회원의 평균 인원 수";
    
    CLOSE memberCursor;
END $$
DELIMITER ;

CALL cursor_proc();

그냥 while문으로 코딩한 느낌..굳이 이렇게까지 해야하나 싶다.

위의 코드는 AVG()와 동일한 기능을 한다.

3. Trigger

📌 Trigger basic

🟡 개요

💡 Trigger를 이용해 일련의 프로세스를 자동화함으로써 데이터 무결성을 보장한다.

https://www.mysqltutorial.org/mysql-triggers/create-multiple-triggers-for-the-same-trigger-event-and-action-time/

Table에 어떤 Event(update, insert, delete)가 발생했을 때, 자동으로 실행되는 것을 말한다.
- DML(Data Manipulation Language) 문 : INSERT, UPDATE, DELETE
- 직접 실행할 수 없고, Table에 Event가 발생해야 자동 실행된다.
Table에 부착(attach)되는 프로그램 코드
IN, OUT 매개변수를 사용할 수 없다.
예시
- 퇴사한 직원 데이터를 DELETE 하면, 퇴사자 Table에 자동으로 백업하는 Trigger
- 고객이 물건을 구매(INSERT)하면, 물품 Table을 자동으로 UPDATE 실행하고, 배송 Table에 INSERT 실행

🟡 기본 작동

CREATE TABLE IF NOT EXISTS trigger_table (id INT, txt VARCHAR(10));
INSERT INTO trigger_table VALUES(1, '에스파');
INSERT INTO trigger_table VALUES(2, '아이브');
INSERT INTO trigger_table VALUES(3, '뉴진스');
INSERT INTO trigger_table VALUES(4, '르세라핌');

DELIMITER $$
CREATE TRIGGER myTrigger
    AFTER DELETE
    ON trigger_table
    FOR EACH ROW
BEGIN
    SET @msg = "가수 그룹이 삭제됨" ; -- trigger 실행 시 작동되는 코드들
END $$
DELIMITER ;

SET @msg = "";
INSERT INTO trigger_table VALUES(5, "피프티피프티");
SELECT @msg; -- trigger가 작동하지 않았다
DELETE FROM trigger_table WHERE id = 5;
SELECT @msg;

EACH ROW : Table 안의 영향을 받은 행 각각에 대해 실행되는 행 트리거를 의미한다. (보통 이걸 쓴다.)

[MYSQL] 📚 트리거(Trigger) 개념 & 사용법

Trigger (트리거) 트리거(Trigger)는 사전적 의미로 '방아쇠'라는 뜻이다. MySQL에서 트리거는 테이블에서 어떤 이벤트가 발생했을 때 자동으로 실행되는 것을 말한다. 즉, 어떤 테이블에서 특정한 이

inpa.tistory.com

📌 Trigger advenced

-- 테이블 생성
USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);

-- 테이블 구성
DROP TABLE IF EXISTS backup_singer;
CREATE TABLE backup_singer
( mem_id  		CHAR(8) NOT NULL , 
  mem_name    	VARCHAR(10) NOT NULL, 
  mem_number    INT NOT NULL, 
  addr	  		CHAR(2) NOT NULL,
  modType  CHAR(2), -- 변경된 타입. '수정' 또는 '삭제'
  modDate  DATE, -- 변경된 날짜
  modUser  VARCHAR(30) -- 변경한 사용자
);

-- 트리거 생성 - UPDATE
DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg  -- 트리거 이름
    AFTER UPDATE -- 변경 후에 작동하도록 지정
    ON singer -- 트리거를 부착할 테이블
    FOR EACH ROW 
BEGIN
    INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number, 
        OLD.addr, '수정', CURDATE(), CURRENT_USER() );
END $$ 
DELIMITER ;

-- 트리거 생성 - DELETE
DROP TRIGGER IF EXISTS singer_deleteTrg;
DELIMITER $$
CREATE TRIGGER singer_deleteTrg  -- 트리거 이름
    AFTER DELETE -- 삭제 후에 작동하도록 지정
    ON singer -- 트리거를 부착할 테이블
    FOR EACH ROW 
BEGIN
    INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name, OLD.mem_number, 
        OLD.addr, '삭제', CURDATE(), CURRENT_USER() ); -- 변경되기 전의 것이 저장되는 것이다.
END $$ 
DELIMITER 


-- 트리거 사용
UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;

계좌와 같은 중요한 정보를 누가 입력/수정/삭제 했는지 파악 가능해야 한다.
- 데이터에 DML문이 실행되면 변경한 사용자와 시간 등을 기록할 수 있다.
변경되기 전의 데이터를 저장할 백업 테이블을 관리하는 것도 좋은 방법이다.
OLD Table
- MySQL에서 내부적으로 제공하는 Table
- UPDATE나 DELETE가 수행될 때, 변경되기 전의 데이터가 잠깐 저장되는 임시 테이블(Temporal Table)

✒️ Delete Trigger는 Delete 문에만 작동한다.

TRUNCATE TABLE singer;

SELECT * FROM backup_singer;

백업 Table에 삭제된 내용이 저장되지 않은 것을 확인할 수 있다.

📌 임시 테이블(Temporal Table)

DML 문이 수행되면 임시로 사용되는 System Table이 2개가 있다.

NEW : 새 데이터, Insert로 삽입된 데이터 또는 Update로 바뀐 후의 데이터
OLD : 예전 데이터, delete로 삭제된 데이터 또는 update로 바뀌기 전의 데이터

저작자표시 비영리

1. What is Stored Procedure?

2. Stored Function & Cursor

3. Trigger

티스토리툴바