스토어드 프로시저

1. 개념과 형식

개념
스토어드 프로시저 = SQL + 프로그래밍

생성

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

만드는 것과 실행(호출)은 다른 문제
만들었으면,, 실행을 해야지..?

실행

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

으로 실행함

삭제

DROP PROCEDURE 스토어드_프로시저_이름;

명명 규칙
명명 할 때도 proc을 붙여주면, 보고 바로 이해할 수 있음
아하! 프로시저네!!

()는 비어있어도 됨

2. 실습

매개변수의 사용

입력 매개변수
자판기에 넣을 동전

IN 입력_매개변수_이름 데이터_형식
CALL 프로시저_이름(전달_값);

출력 매개변수
미리 준비하고 있는 컵

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

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

변수 = 비어있는 컵
SELECT @변수명;을 통해 담긴 결과값 확인 가능

Case 1

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 ;

INSERT INTO noTable VALUES(NULL,txtValue);
noTable에 (NULL, txtVaule)를 넣을 것임
=> 이때 스토어드 프로시저를 만드는 시점에는 해당 테이블(현재에서는 noTable)이 없어도 된다.
-> 실행하기 전에만 있으면 됨~

SELECT MAX(id) INTO outValue FROM noTable;
MAX(id)가 outValue라는 변수에 저장되게 됨

Case 2

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 ;

데뷔년도에 따라 신인, 고참 나누기

DECLARE debutYear INT;
스토어드 프로시저 안에서의 변수 선언시 DECLARE 필요함
밖에서는 @

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 ;

Case 3

동적 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 ;

=> tableName을 받아와서 해당 테이블의 모든 행 & 열 출력하기