[혼공SQL] 7장. 스토어드 프로시저

Chapter 7. 스토어드 프로시저

7.1 스토어드 프로시저 사용 방법

• 스토어드 프로시저는 MySQL에서 제공되는 프로그래밍 기능이다
• BEGIN ~ END 사이에 코드를 구현한다.
• 스토어드 프로시저를 작성한 후, CALL문을 통해서 스토어드 프로시저를 호출한다.
• 입력 매개변수는 스토어드 프로시저에 값을 전달한다.(형식 : IN)
• 출력 매게변수는 스토어드 프로시저에서 계산된 결과를 돌려받는다.(형식 : OUT)
• 동적SQL은 다이나믹하게 SQL을 생성한 후 실행한다. PREPARE문과 EXECUTE문을 사용한다.
• 스토어드 프로시저를 만드는 시점에는 아직 존재하지 않은 테이블을 사용해도되나, CALL로 실행하는시점에는 사용한 테이블이 있어야 함
• 프로시저 밖에 변수를 만들 땐 @를 사용
• 프로시저 안에 변수를 만들 땐 DECLARE를 사용

<관련 예제> - 테이블이 없어도 프로시저는 실행 가능

noTable이 없는 상태에서 코드를 실행할 경우 error 발생하지 않음

noTable의 테이블 구조를 확인을 해보았으나 테이블이 없다고 error발생

<실습> 1~부터 100까지의 합계 구하기

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();

출력값

※ 참고 

DELIMITER는 SQL 문에서 사용되는 구문 구분자를 변경하기 위한 명령어입니다. 기본적으로 SQL 문은 세미콜론(;)으로 구분되어 실행됩니다. 그러나 때로는 SQL 문 내부에서도 세미콜론을 사용해야 하는 경우가 있습니다. 이런 경우에는 DELIMITER를 사용하여 구문 구분자를 변경하고, 이후에는 변경된 구문 구분자를 사용하여 SQL 문을 작성합니다.

일반적으로 DELIMITER 명령어는 세미콜론 뒤에 공백을 추가하여 사용합니다. 이렇게 함으로써 세미콜론이 SQL 문 내의 구문 구분자로 인식되지 않고, DELIMITER 명령어의 일부로 인식됩니다. 이렇게 구문 구분자를 변경한 후에는 공백 없이 세미콜론으로 문장을 종결할 수 있게 됩니다.

---

7.2 스토어드 함수

• MySQL은 다양한 내장 함수로 제공되지 않는 기능을 스토어드 함수로 만들어서 사용할 수 있습니다.
• 스토어드 함수는 RETURNS 예약어를 통해서 반환될 데이터의 형식을 미리 지정해 놓아야 합니다.
• 스토어드 함수의 매개변수는 모두 다 스토어드 함수로 값이 들어오는 입력 매개변수입니다.
• 스토어드 프로시저와 달리 출력 매개변수는 없습니다.

<실습> 프토어드 함수 만들기(기본형태, 변수사용, SELECT ~ INTO ~, 함수삭제)

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 calcYearFunc(2010) AS '활동 햇수';

결과값

• SELECT ~ INTO ~로 저장했다가 사용하는 예제

SELECT calcYearFunc(2007) INTO @debut2007;
SELECT calcYearFunc(2023) INTO @debut2023;
SELECT @debut2007 - @debut2023 AS '2007과 2023 차이';

결과값

스토어드 함수 삭제 구문

DROP FUNCTION calcYearFunc;

---

7.3 커서(cursor)

• 커서는 한 행씩 처리되도록 하는 기능입니다.
• DECLARE로 선언할 수 있으며, 그 내용은 SELECT문 입니다.
• 커서는 행이 끝날 때까지 계속 반복 -> 행의 끝을 판단하기 위해 변수 endDfRow를 통해 TRUE값인지 체크하는 방식을 사용합니다.

<실습>

USE market_db;

DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
	-- 전체 인원의 합계와 읽은 행의 수를 누적시켜야 하기 때문에 
    -- DEFAULT문을 사용해서 초기값을 0으로 설정
    DECLARE memNumber INT;
    DECLARE cnt INT DEFAULT 0;
    DECLARE totNumber INT DEFAULT 0;
    
    -- 행의 끝을 파악하기 위한 변수endOfRow를 통해 행의 끝이 아닐경우 FALSE로 초기화
    DECLARE endOfRow BOOLEAN DEFAULT FALSE;
    
    DECLARE memberCuror CURSOR FOR
	    SELECT mem_number FROM member;
        
    DECLARE CONTINUE HANDLER		-- 반복 조건을 준비하는 예약어
	    FOR NOT FOUND SET endOfRow = TRUE;	-- 더 이상 행이 없을 때 endOfRow에 TRUE를 대입
        
    OPEN memberCuror;	-- 커서 열기
    
    cursor_loop: LOOP	-- 행 반복하기
	    FETCH memberCuror INTO memNumber;	-- FETCH는 한 행씩 읽어오는 것, 실제 내용
        
	    IF endOfRow THEN
		    LEAVE cursor_loop;	-- 반복문을 빠져나감
	    END IF;
        
        SET cnt = cnt + 1;
        SET totNumber = totNumber + memNumber; -- 10명에 대한 회원수가 누적됨
	END LOOP cursor_loop;
    
    SELECT (totNumber/cnt) AS '회원의 평균 인원 수';
    
    CLOSE memberCuror;	-- 커서 닫기
    
END $$
DELIMITER ;

CALL cursor_proc();

결과값

---

7.4 트리거(trigger)

• 트리거는 테이블에서 DML문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동한다.
• DML(Data Manipulation Language)
• AFTER DELETE는 DELETE 문이 발생된 후에 트리거를 작동하라는 의미이다.
• 트리거는 행 데이터가 삭제 또는 수정되면 기존 데이터를 백업 테이블에 저장하도록 할 수 있다.
• 트리거에서 기존 데이터는 OLD 테이블에, 새로운 데이터는 NEW 테이블에 잠깐 저장된다.
• OLD 및 NEW 테이블은 MySQL이 내부적으로 관리한다.

<실습> : 백업 프토어드 프로시저 만들기, 트리거 삭제 구문

USE market_db;
DROP TABLE IF EXISTS 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), -- 변경된 타입. '수정' 또는 '삭제'
  modDate  DATE, -- 변경된 날짜
  modUser  VARCHAR(30) -- 변경한 사용자
);

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 ;

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 ;

결과확인

결과확인

DELETE FROM singer WHERE mem_number >= 7;

SELECT * FROM backup_singer;

트리거 삭제 구문

TRUNCATE TABLE singer;