[혼자공부하는SQL] 18강

07-1. 스토어드 프로시저 사용 방법

01. 시작하기 전에

• 스토어드 프로시저(Stored Procedure) : SQL에 프로그래밍에 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있다.

1. 스토어드 프로시저 기본

1) 스토어드 프로시저의 개념과 형식

• 스토어드 프로시저는 쿼리 문의 집합으로도 볼 수 있다.
• 어떠한 동작을 일괄 처리하기 위한 용도로도 사용한다.
• 자주 사용하는 일반적인 쿼리를 반복하는 것보다는, 스토어드 프로시저로 묶어 놓고, 필요할 때마다 간단히 호출만 하면 훨씬 편리하게 MySQL을 운영할 수 있다.

(1) 스토어드 프로시저 생성

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

• $$는 $를 1개만 사용해도 되지만, 명확하게 표시하기 위해 2개를 사용하며, ##, &&, %%, // 등으로 바꿔도 된다.
• 이름은 마음대로 지어도 되지만, 가능하면 이름만으로 스토어드 프로시저라는 것을 알 수 있도록 표현하는 것이 좋다.
• CREATE PROCEDURE는 스토어드 프로시저를 만든 것뿐이며, 아직 실행(호출)한 것은 아니다.

(2) 스토어드 프로시저 호출

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

(3) 예시

-- 스토어드 프로시저 생성
DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN
    SELECT * FROM member;
end $$
DELIMITER ;

-- 스토어드 프로시저 호출
CALL user_proc();

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

2. 스토어드 프로시저 실습

1) 매개변수의 사용

• 스토어드 프로시저에서는 실행 시, 입력 매개변수를 지정할 수 있다.

(1) 종류

• 입력 매개변수
• 출력 매개변수

2) 입력 매개변수

(1) 형식

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

(2) 사용

CALL 프로시즈_이름(전달_값);

3) 출력 매개변수

(1) 형식

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

(2) 사용

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

3) 예시를 통한 매개변수의 활용

(1) 입력 매개변수

DROP PROCEDURE IF EXISTS user_proc1;

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

CALL user_proc1('에이핑크');

(2) 여러 개의 입력 매개변수

DROP PROCEDURE IF EXISTS user_proc2;

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

(3) IN / OUT 매개변수

DROP PROCEDURE IF EXISTS user_proc3;

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 ;

-- 없는 테이블이라고 뜬다.
-- 앞서 user_proc3에서는 noTable이 없는데 스토어드 프로시저가 생성되었다.
-- 테이블이 없어도 스토어드 프로시저 안에서 사용하고, 실행하기 전에만 CREATE TABLE로 만들면 된다.
DESC noTable;

CREATE TABLE IF NOT EXISTS noTable(
    id INT AUTO_INCREMENT PRIMARY KEY,
    txt CHAR(10)
);

CALL user_proc3 ('테스트1', @myValue);
SELECT CONCAT('입력된 ID 값 --> ', @myValue);

(4) 조건문

DROP PROCEDURE IF EXISTS  ifelse_proc;

DELIMITER $$
CREATE PROCEDURE ifelse_proc(
    IN memName VARCHAR(10)
)
BEGIN
    -- 변수 선언
    DECLARE debutYear INT;
    -- 연도만 추출해서 debutYear 변수에 대입
    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('오마이걸');
CALL ifelse_proc('소녀시대');

(5) 반복문

DROP PROCEDURE IF EXISTS while_proc;

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

(6) 동적 SQL

DROP PROCEDURE IF EXISTS dynamic_proc;
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');
CALL dynamic_proc('buy');