[혼공SQL] chapter 7. 스토어드 프로시저(1)

여정이·2024년 8월 5일
mysql혼공단 12기혼자 공부하는 SQL
0

혼자 공부하는 SQL

목록 보기
22/28

📒 요약 : 스토어드 프로시저를 사용하면 MySQL 안에서도 다른 프로그래밍 언어처럼 프로그램 로징을 코딩할 수 있다. 스토어드 프로시저를 이용하면 일종의 자동화 프로그램을 만들어 효율적으로 SQL을 사용할 수 있게 된다.

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

스토어드 프로시저를 앞의 챕터에서 잠깐 다루었는데, 그 때는 간단하게 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있는 기능이라고 간단하게 개념만 다루고 넘어갔다. 이번 챕터에서는 스토어드 프로시저를 어떻게 활용할 수 있는지, 실정에서 어떻게 다루는지 알아보자.

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

스토어드 프로시저의 기본 형과 개념부터 살펴 보자.


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

스토어드 프로시저는 MySQL에서 제공하는 프로그래밍 기능이다. 다른 언어들의 프로그래밍과는 조금 다르긴 하지만, MySQL 내부에서 사용할 때 적절한 프로그래밍 기능을 제공한다. 스토어드 프로시저는 쿼리 문의 집합으로, 어떠한 동작을 일괄적으로 처리하기 위한 용도로도 사용된다. 만약 자주 사용하는 쿼리가 있다면, 이를 스토어드 프로시저로 묶어놓고 필요할 때 마다 호출하여 사용할 수 있다. 다른 프로그래밍 언어의 함수처럼 이용할 수 있다. 스토어드 프로시저를 만드는 가장 필수적인 형식은 아래와 같다.

DELIMITER $$ 			#필수적으로 들어가야 하는 항목으로, 스토어드 프로시저를 묶어주는 기능을 한다. DELIMITER는 '구분자'라는 의미이다.
CREATE PROCEDURE 스토어드_프로시저_이름(IN 또는 OUT 매개변수)
BEGIN
	#이 부분에 SQL 프로그래밍 코드 작성
END $$
DELIMITER;

이렇게 CREATE PROCEDURE 문을 이용하여 스토어드 프로시저를 만들었다. CREATE PROCEDURE는 스토어드 프로시저를 만든 것 뿐이다. 해당 프로시저를 사용하려면 따로 프로시저를 호출해야 한다. 스토어드 프로시저를 호출하는 형식은 아래와 같이 간단하게 CALL 다음에 스토어드 프로시저의 이름과 괄호를 붙이면 된다. 함수를 호출할 때와 비슷하다.

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

스토어드 프로시저의 생성

우선 스토어드 프로시저의 생성해보자. 이번에도 market_db 데이터베이스를 이용할 것이다. 아래와 같이 간단하게 스토어드 프로시저를 생성하고 호출할 수 있다. 

USE market_db; 							# market_db 데이터베이스 이용
DROP PROCEDURE IF EXISTS user_proc;		# 기존에 user_proc라는 이름의 스토어드 프로시저가 있다면 삭제
DELIMITER $$							# 스토어드 프로시저 생성
CREATE PROCEDURE user_proc()
BEGIN
    SELECT * FROM member; -- 스토어드 프로시저 내용
END $$
DELIMITER ;

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

스토어드 프로시저의 삭제

앞에서 생성한 user_proc 프로시저를 삭제하려면 아래와 같이 DROP PROCEDURE문을 이용하면 된다. 주의할 점은 CREATE PROCEDURE에서는 스토어드 프로시저 이름 뒤에 괄호를 붙이지만, DROP PROCEDURE에서는 괄호를 붙이지 않아야 한다는 것이다.

DROP PROCEDURE user_proc;





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

매개변수의 사용

스토어드 프로시저를 실행할 때, 입력 매개변수를 지정할 수 있다. 쉽게 말해, 다른 프로그래밍 언어의 함수를 호출할 때 매개변수에 값을 할당해 전달하는 것과 같은 원리이다. 스토어드 프로시저에서 입력 매개변수를 지정하는 형식은 아래와 같다. 그리고 입력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는 괄호 안에 값을 전달하면 된다.

IN  입력_매개변수_이름 데이터_형식
CALL 프로시저_이름(전달_값)			#매개변수 사용하여 스토어드 프로시저 호출하기

이렇게 스토어드 프로시저에서 처리된 결과는 출력 매개변수를 통해 얻을 수 있다. 함수의 리턴값과 비슷한 개념이다. 출력 매개변수의 형식은 아래와 같고, 값을 대입하기 위해서는 주로 SELECT~INTO 문을 사용한다. 출력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는 다음과 같이 사용한다.

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

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

입력 매개변수의 활용

이제 입력 매개변수가 있는 스토어드 프로시저를 생성하고 실행해보자. 아래의 스토어드 프로시저는 '에이핑크'를 입력 매개변수로 전달하여 userName 매개변수에 대입했다.

USE market_db;
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('에이핑크');

그림1. user_proc1 스토어드 프로시저 실행 결과

입력 매개변수는 1개가 아니라 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. user_proc2 스토어드 프로시저 실행 결과

출력 매개변수의 활용

이번에는 출력 매개변수가 있는 스토어드 프로시저를 생성해보자. 아래의 스토어드 프로시저는 noTable이라는 이름의 테이블에 넘겨 받은 값을 입력하고, id 열의 최댓값을 알아내는 기능을 한다. id 열의 최댓값은 결국 방금 입력한 행의 순차 번호이다.

DROP PROCEDURE IF EXISTS user_proc3;
DELIMITER $$
CREATE PROCEDURE user_proc3(
    IN txtValue CHAR(10),
    OUT outValue INT     )						#출력 매개변수인 outValue를 지정
BEGIN
  INSERT INTO noTable VALUES(NULL,txtValue);
  SELECT MAX(id) INTO outValue FROM noTable; 	#INTO outvalue구문으로 outValue에 id열의 최댓값을 지정
END $$
DELIMITER ;

그런데 위의 프로시저에 noTable이라는 테이블이 사용되는 것을 확인할 수 있다. 이는 존재하지 않은 테이블인데, 스토어드 프로시저에서는 프로시저를 생성할 때 존재하지 않는 테이블을 사용하는 것을 허용한다. 단, CALL로 실행하는 시점에는 프로시저에 사용한 테이블이 모두 있어야 한다.


SQL 프로그래밍의 활용

이번에는 스토어드 프로시저 안에 SQL 프로그래밍을을 넣어 보자. 먼저 조건문의 기본인 IF~ELSE문부터 사용해보자. 아래의 스토어드 프로시저는 가수 그룹의 데뷔 연도가 2015년 이전이면 '고참 가수', 2015년 이후(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 ('오마이걸');

그림3. ifelse_proc 스토어드 프로시저 실행 결과

위와 같이 '오마이걸'이 신인 가수로 분류됨을 확인할 수 있다. 이번에는 여러 번 반복하는 while문을 이용해 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();

그림4. while_proc 스토어드 프로시저 실행 결과 스토어드 프로시저를 호출하여 실행한 결과, 우리가 익히 알고 있듯 5050이 출력되는 것을 확인할 수 있다. 마지막으로 동적 SQL을 이용할 차례이다. 동적 SQL은 이름 그대로 다이나믹하게 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');

그림5. dynamic_proc 스토어드 프로시저 실행 결과
profile
여정이
0w0
이전 포스트

[혼공SQL] chapter 6. 인덱스 확인문제

다음 포스트

[혼공SQL] chapter 7. 스토어드 프로시저(2)

0개의 댓글