[혼공SQL] 7-1 스토어드 프로시저 사용 방법

본 포스팅은 '한빛출판네트워크'의 '혼공SQL' 책을 기반으로 작성한 포스팅입니다.

 

✏️ 스토어드 프로시저(stored procedure)
: SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 냄

 

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

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

• MySQL에서 제공하는 프로그래밍 기능으로 내부에서 사용할 때 적절한 프로그래밍 기능을 제공
• 데이터베이스의 개체 중 한 가지로 테이블처럼 각 데이터베이스 내부에 저장
• 쿼리 문의 집합 / 어떠한 동작을 일괄 처리하기 위한 용도로 사용
  → 자주 사용하는 일반적인 쿼리를 반복하는 것보다 스토어드 프로시저로 묶어 놓고 필요할 때 호출하면 편리함

 

📌 스토어드 프로시저 만드는 형식

DELIMITER $$  -- 구분자 / 스토어드 프로시저를 묶어주는 기능

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

DELIMITER ;

 

📌 스토어드 프로시저 호출

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

 

1.2 스토어드 프로시저의 생성

use market_db;

drop procedure if exists user_proc;

delimiter $$
create procedure user_proc()    -- 스토어드 프로시저 구문 만들기
begin
	select * from member;      -- 스토어드 프로시저 내용
end $$

delimiter $$

call user_proc();    -- 생성한 user_proc라는 스토어드 프로시저 실행

 

1.3 스토어드 프로시저의 삭제

• DROP PROCEDURE 사용
• 🚫 DROP PROCEDURE에서는 스토어드 프로시저 이름 뒤에 괄호 붙이지 않음

drop procedure user_proc;

 

 

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

스토어드 프로시저에는 프로그래밍 기능을 사용하고 싶은 만큼 적용할 수 있음

 

2.1 입력 매개변수

• 실행 시 입력 매개변수(parameter)를 지정할 수 있음
• 스토어드 프로시저에 값을 전달함

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

 

• 입력 매개변수가 있는 스토어드 프로시저를 실행

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

 

📌 1개의 입력 매개변수가 있는 스토어드 프로시저

drop procedure if exists user_proc1;

delimiter $$
create procedure user_proc1(IN userName varchar(10))  --userName 매개변수에 대입

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

 

2.2 출력 매개변수

•  스토어드 프로시저에서 계산된 결과를 돌려받음

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

 

• 출력 매개변수가 있는 스토어드 프로시저를 실행

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

 

📌 출력 매개변수 활용

• 스토어드 프로시저를 만드는 시점에는 아직 존재하지 않는 테이블을 사용해도 됨
  (아래의 코드에서 noTable 테이블은 현재 만들어지지 않은 상태임에도 프로시저는 생성됨)
• 단, CALL로 실행하는 시점에는 사용한 테이블이 있어야 한다.

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;  -- INTO outValue 구문으로 outValue에 id열의 최대값을 저장
END $$

DELIMITER ;

 

• noTable 테이블 만들기

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

 

• 스토어드 프로시저 호출
  • 출력 매개변수 위치에 @변수명 형태로 변수를 전달해주면 그 변수에 결과가 저장됨
    그리고 SELECT로 출력하면 됨

-- 스토어드 프로시저 출력
CALL user_proc3 ('테스트1', @myValue);
SELECT CONCAT('입력된 ID 값 ==>', @myValue);

 

[+] 위의 코드 2줄을 계속해서 실행하면 값이 계속 증가함 (2, 3, 4, ….)

 

 

2.3 SQL 프로그래밍 활용

✔ IF~ELSE문 활용

가수 그룹의 데뷔 연도가 2015년 이전이면 ‘고참 가수’, 이후이면 ‘신인 가수’를 출력

DROP PROCEDURE IF EXISTS ifelse_proc;

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 ('오마이걸');

 

💡 날짜와 관련된 MySQL 함수 💡
- YEAR(날짜), MONTH(날짜), DAY(날짜)
- 현재 날짜 : CURDATE()

 

✔ WHILE문 활용 (1부터 100까지의 합계 계산)

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    -- 100까지 반복
		SET hap = hap + num;
		SET num = num + 1;     -- 1씩 숫자 증가
		END WHILE;
    
    SELECT hap AS '1~100 합계';
END $$
DELIMITER ;

CALL while_proc();

 

✔ 동적 SQL 활용

PREPARE문과 EXECUTE문을 사용

→ 테이블 이름을 매개변수로 전달받아서 해당 테이블을 조회

DROP PROCEDURE IF EXISTS dynamic_proc;

DELIMITER $$

CREATE PROCEDURE dynamic_proc(
	IN tableName VARCHAR(20)
)

BEGIN
	SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);  -- @sqlQuery 변수에 SELECT 문자열 생성
	PREPARE myQuery FROM @sqlQuery;    -- SELECT 문자열 준비
	EXECUTE myQuery;                   -- 실행
	DEALLOCATE PREPARE myQuery;        -- 사용한 myQuery 해제
END $$
DELIMITER ;

CALL dynamic_proc ('member');     -- member 테이블의 모든 데이터 조회