[5] PROCEDURE, FUNCTION, TRIGGER

1. PROCEDURE

(1) Procedure

프로시저는 쿼리 문의 집합으로 볼 수 있으며, 특정 동작을 일괄적으로 처리할 수 있다. 매개변수는 입력 매개변수와 출력 매개변수로 나눌 수 있다. 입력 매개변수는 일반적인 함수의 매개변수와 같이 프로시저에 전달하는 값이다. 출력 매개변수는 일반적인 함수에서 return값에 해당한다.

--예시1) 입력 매개변수와 출력 매개변수를 포함한 프로시저
DROP PROCEDURE IF EXISTS user_proc3;
DELIMITER $$
CREATE PROCEDURE user_proc(
    IN txtValue CHAR(10), --입력 매개변수는 앞에 in을 붙임
    OUT outValue INT --출력 매개변수는 앞에 out을 붙임
    )
BEGIN
  INSERT INTO noTable VALUES(NULL,txtValue);
  SELECT MAX(id) INTO outValue FROM noTable; 
END $$ DELIMITER ;
CALL user_proc('test1', @maValue)
SELECT CONCAT('입력된 ID값 =>', @maValue)

--예시2) while문을 포함한 프로시저
DROP PROCEDURE IF EXISTS while_proc;
DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
    DECLARE hap INT; -- 합계
    DECLARE num INT; -- 1부터 100까지 증가
    SET hap = 0; -- 합계 초기화
    SET num = 1; 
    WHILE (num <= 100) DO  -- 100까지 반복.
        SET hap = hap + num;
        SET num = num + 1; -- 숫자 증가
    END WHILE;
    SELECT hap AS '1~100 합계';
END $$ DELIMITER ;
CALL while_proc();

--예시3) 동적 sql
DROP PROCEDURE IF EXISTS dynamic_proc;
DELIMITER $$
CREATE PROCEDURE dynamic_proc(
    IN tableName VARCHAR(20)
)
BEGIN
  SET @txt = CONCAT('SELECT * FROM ', tableName);
  PREPARE myQuery FROM @txt;
  EXECUTE myQuery;
  DEALLOCATE PREPARE myQuery;
END $$ DELIMITER ;
CALL dynamic_proc ('member');

동적 sql문이 조금 까다로운데, concat()을 이용해서 @txt 변수를 만들어서 실행해야 오류가 나지 않는다. PREPARE에서 ?을 입력하고 EXECUTE myQuery using [입력 매개변수]를 실행했던 문법상 오류가 나왔다.

마지막으로, 실행을 마친 쿼리는 할당을 풀어주는 것이 바람직하다.

(2) Procedure with Cursor

커서는 한 행씩 처리할 때 사용하는 방식이다.

--예시) 커서를 이용한 member 테이블에서 회원의 평균 인원 수를 계산하는 프로시저
DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
	# 관련 변수 선언
    DECLARE memNumber INT; -- 회원의 인원수
    DECLARE cnt INT DEFAULT 0; -- 읽은 행의 수
    DECLARE totNumber INT DEFAULT 0; -- 인원의 합계
    DECLARE endOfRow BOOLEAN DEFAULT FALSE; -- 행의 끝 여부(기본을 FALSE)
    # 커서 선언
    DECLARE memberCuror CURSOR FOR
        SELECT mem_number FROM member;
    # 반복 조건 선언
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET endOfRow = TRUE; -- 행 끝나면 TRUE 반환
    # 커서 열기
    OPEN memberCuror;
    # 반복문
    cursor_loop:
    LOOP
    	# 한 행씩 읽어오기
        FETCH memberCuror INTO memNumber; 
        IF endOfRow THEN 
            LEAVE cursor_loop; -- 행 끝나면(=TRUE) 실행
        END IF;
        SET cnt = cnt + 1;
        SET totNumber = totNumber + memNumber;        
    END LOOP cursor_loop;
    SELECT (totNumber/cnt) AS '회원의 평균 인원 수';
    # 커서 닫기
    CLOSE memberCuror; 
END $$ DELIMITER ;
# 프로시저 호출
CALL cursor_proc();

2. FUNCTION

내장 함수 외에 직접 함수를 만들 수도 있다. 프로시저와 비슷하지만 다음과 같은 차이점이 있다.

• 하나의 값을 반환해야 함 (return)
• 입력 매개변수만 있으므로 in을 명시하지 않아도 됨
• 프로시저는 call로 호출하지만, 함수는 select에서 호출됨
• 프로시저 안에서 select문 사용 가능하지만, 함수에서는 select문 사용 불가능
• 함수를 생성하기 위해서는 함수 생성 권한을 허용해야 함

** 함수 생성 권한을 허용하는 쿼리
SET GLOBAL log_bin_trust_function_creators = 1;

--예시1)
DROP FUNCTION IF EXISTS sumFunc;
DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
    RETURNS INT -- 반환할 값의 형식을 꼭 명시해줘야 한다.
BEGIN
    RETURN number1 + number2;
END $$
DELIMITER ;
SELECT sumFunc(100, 200) AS '합계';

--예시2)
DROP FUNCTION IF EXISTS calcYearFunc;
DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT)
    RETURNS INT
BEGIN
    DECLARE runYear INT; -- 활동기간(연도)
    SET runYear = YEAR(CURDATE()) - dYear;
    RETURN runYear;
END $$
DELIMITER ;
SELECT mem_id, mem_name, calcYearFunc(YEAR(debut_date)) AS '활동 햇수' FROM member; 

3. TRIGGER

DML문(insert, update, delete)의 이벤트가 발생하면 자동으로 실행되는 코드로, 대표적으로 백업 테이블을 생성할 때 유용하게 사용된다.

예시)
DROP TRIGGER IF EXISTS myTrigger;
DELIMITER $$ 
CREATE TRIGGER myTrigger  -- 트리거 이름
    AFTER  DELETE -- delete 후에만 작동하도록 지정
    ON trigger_table -- 부착할 테이블 이름
    FOR EACH ROW -- 각 행에 모두 적용
BEGIN
    SET @msg = '가수 그룹이 삭제됨' ; -- 트리거 작동 시 실행
END $$ 
DELIMITER ;
DELETE FROM trigger_table WHERE id = 4; --delete문 실행했으므로 trigger 작동
SELECT @msg; -- '가수 그룹이 삭제됨'이 저장돼있음

테이블에서 DML문이 실행되면 임시로 사용되는 테이블인 NEW와 OLD가 존재한다.

• insert : 입력한 값이 NEW에 임시 저장
• delete : 삭제될 값이 OLD에 임시 저장 후 삭제
• update : 삭제될 값이 OLD에 임시 저장 후 삭제되고, 입력한 값이 NEW에 임시 저장

이를 바탕으로 백업 테이블을 만들어보자.

# singer 테이블과 이를 백업할 backup_singer 테이블 생성
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), --'수정'or'삭제'
  	 modDate  DATE,
   	 modUser  VARCHAR(30) -- 변경한 사용자
	);

# update시 실행되는 트리거
DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg
    AFTER UPDATE -- 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 ;

# DML문 실행 후 결과 확인
UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;
SELECT * FROM backup_singer;
TRUNCATE TABLE singer; -- delete가 아니므로 트리거가 작동하지 않음
SELECT * FROM backup_singer;

---

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