31. MySQL 스토어드 프로그램 - 스토어드 프로시저

김제이아이엠·2025년 11월 10일
mysql

MySQL

목록 보기
29/41

스토어드 프로시저

스토어드 프로시저(Stored Procedure, 저장 프로시저)란 MySQL에서 제공되는 프로그래밍 기능이라고 생각하면 된다. 이것은 일반적인 프로그래밍과는 조금 차이가 있지만 MySQL 내부에서 사용하기 위해서는 아주 적절한 방식을 제공해 준다.

스토어드 프로시저의 개요

한마디로 쿼리문의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용된다. 자주 사용되는 일반적인 쿼리를 사용하는 것보다는 이것을 모듈화 시켜서 필요할 때마다 호출만 하면 훨씬 편리하게 MySQL을 운영할 수 있다.

*스토어드 프로시저도 데이터베이스의 개체 중의 한 가지다. 즉, 테이블처럼 각 데이터베이스 내부에 저장이 된다는 의미다.

스토어드 프로시저의 정의 형식

형식이 좀 복잡해 보이지만 실제로 사용하는 것은 그리 복잡하지 않다. 단지 실제 작동되는 routine_body 부분이 필요에 따라서 수십, 수백 줄이 될 수 있다.


3행과 7행의 DELIMITER $$ ~ END $는 스토어드 프로시저를 묶어주는 부분이다. MySQL의 종료 문자는 세미콜론(;)인데 CREATE PROCEDURE 안에서도 세미콜론이 종료 문자이므로 어디까지가 스토어드 프로시저인지 구별이 어렵다. 그래서 7행의 END $가 나올 때까지를 스토어드 프로시저로 인식하게 하는 것이다. 그리고 다시 8행에서 DELIMITER;로 종료 문자를 세미콜론(;)으로 변경해 놓아야 한다. 10행에서는 생성한 스토어드 프로시저를 호출한다.

스토어드 프로시저의 수정과 삭제
ALTER PROCEDURE, DROP PROCEDURE 사용

매개 변수의 사용
스토어드 프로시저에는 실행 시에 입력 매개 변수를 지정할 수 있다. 입력된 매개 변수는 스토어드 프로시저의 내부에서 다양한 용도로 사용될 수 있다. 또한, 스토어드 프로시저에서 처리된 결과를 출력 매개 변수를 통해서 얻을 수도 있다.

입력 매개 변수를 지정하는 형식은 다음과 같다.

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

디폴트 값은 프로시저의 실행 시에 매개 변수에 값을 전달하지 않았을 때, 사용되는 값이다. 입력 매개 변수가 있는 스토어드 프로시저를 실행하기 위해서는 다음과 같이 사용한다.

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

출력 매개 변수를 지정하기 위해서는 다음의 형식을 따른다.

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

출력 매개 변수에 값을 대입하기 위해서는 주로 SELECT ... INTO문을 사용한다.

출력 매개 변수가 있는 스토어드 프로시저를 실행하기 위해서는 다음과 같이 사용한다.

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

프로그래밍 기능
SQL 프로그래밍의 내용의 대부분이 스토어드 프로시저에 적용될 수 있다.

스토어드 프로시저 내외 오류 처리
DECLARE 액션 HANDLER FOR 오류조건 처리할_문장

<실습>
스토어드 프로시저 내용을 실습하자.

-- 실습1
USE sqlDB;
DROP PROCEDURE IF EXISTS userProc1;
DELIMITER $$
CREATE PROCEDURE userProc1(IN userName VARCHAR(10))
BEGIN
	SELECT * FROM userTbl WHERE name = userName;
END $$
DELIMITER;

CALL userProc1('조관우');

-- 실습2
DROP PROCEDURE IF EXISTS userProc2;
DELIMITER $$
CREATE PROCEDURE userProc2(
	IN userBirth INT,
    IN userHeight INT
)
BEGIN
	SELECT * FROM userTbl
    	WHERE birthYear > userBirth AND height > userHeight;
END $$
DELIMITER;

CALL userProc2(1970, 178);

-- 실습3
DROP PROCEDURE IF EXISTS userProc3;
DELIMITER $$
CREATE PROCEDURE userProc3(
	IN txtValue CHAR(10),
    OUT outValue INT
)
BEGIN
	INSERT INTO testTBL VALUES(NULL, txtValue);
    SELECT MAX(id) INTO outValue FROM testTBL;
END $$
DELIMITER;

testTBL을 만든적이 없는데 저장프로시저는 잘 만들어졌다. 이유가 저장 프로시저를 만드는 시점에서는 테이블이 있는지 없는지 체크를 하지 않는다. 하지만 호출할때는 테이블이 사용된다. 그래서 저장프로시저를 만들고 테이블을 만들어도 된다.

CREATE TABLE IF NOT EXISTS testTBL(
	id INT AUTO_INCREMENT PRIMARY KEY,
    txt CHAR(10)
);
CALL userProc3('테스트값', @myValue);
SELECT CONCAT('현재 입력된 ID 값 ==>', @myValue);



계속 시행하면 현재 입력된 ID 값이 늘어나는 것을 확인할 수 있다.

DROP PROCEDURE IF EXISTS ifelseProc;
DELIMITER $$
CREATE PROCEDURE ifelseProc(
	IN userName VARCHAR(10)
)
BEGIN
	DECLARE bYear INT; -- 변수 선언
    SELECT birthYear into bYear FROM userTbl
    	WHERE name = userName;
    IF (bYear >= 1980) THEN
    		SELECT '아직 젊군요..';
    ELSE
    		SELECT '나이가 지긋하시네요.';
    END IF;
END $$
DELIMITER;

CALL ifelseProc('조용필');

DROP PROCEDURE IF EXISTS caseProc;
DELIMITER $$
CREATE PROCEDURE caseProc(
	IN userName VARCHAR(10)
)
BEGIN
	DECLARE bYear INT;
    DECLARE tti	 CHAR(3); -- 띠
    SELECT birthYear INTO bYear FROM userTbl
    	WHERE name = userName;
    CASE
    	WHEN ( bYear%12 = 0) THEN	SET tti = '원숭이';
        WHEN ( bYear%12 = 1) THEN	SET tti = '닭';
        WHEN ( bYear%12 = 2) THEN	SET tti = '개';
        WHEN ( bYear%12 = 3) THEN	SET tti = '돼지';
        WHEN ( bYear%12 = 4) THEN	SET tti = '쥐';
        WHEN ( bYear%12 = 5) THEN	SET tti = '소';
        WHEN ( bYear%12 = 6) THEN	SET tti = '호랑이';
        WHEN ( bYear%12 = 7) THEN	SET tti = '토끼';
        WHEN ( bYear%12 = 8) THEN	SET tti = '용';
        WHEN ( bYear%12 = 9) THEN	SET tti = '뱀';
        WHEN ( bYear%12 = 10) THEN	SET tti = '말';
        ELSE SET tti = '양';
    END CASE;
    SELECT CONCAT(userName, '의 띠 ==>', tti);
END $$
DELIMITER;

CALL caseProc('김범수');

DROP TABLE IF EXISTS guguTBL;
CREATE TABLE guguTBL (txt VARCHAR(100)); -- 구구단 저장용 테이블

DROP PROCEDURE IF EXISTS whileProc;
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
	DECLARE str VARCHAR(100); -- 각 단을 문자열로 저장
    DECLARE i INT; -- 구구단 앞자리
    DECLARE k INT; -- 구구단 뒷자리
    SET i = 2; -- 2단부터 계산
    
    WHILE (i < 10) DO -- 바깥 반복문. 2단~9단까지.
    	SET str = ''; -- 각 단의 결과를 저장할 문자열 초기화.
        SET k = 1; -- 구구단 뒷자리는 항상 1부터 9까지.
        WHILE (k < 10) DO
        	SET str = CONCAT(str, '	', i, 'x', k, '=', i*k); -- 문자열 만들기
            SET k = k + 1; -- 뒷자리 증가
        END WHILE;
        SET i = i + 1; -- 앞자리 증가
        INSERT INTO guguTBL VALUES(str); -- 각 단의 결과를 테이블에 입력.
    END WHILE;
END $$
DELIMITER;

CALL whileProc();
SELECT * FROM guguTBL;

DROP PROCEDURE IF EXISTS errorProc;
DELIMITER $$
CREATE PROCEDURE errorProc()
BEGIN
	DECLARE i INT; -- 1씩 증가하는 값.
    DECLARE hap INT; -- 합계 (정수형). 오버플로 발생시킬 예정.
    DECLARE saveHap INT; -- 합계 (정수형). 오버플로 직전의 값을 저장.
    
    DECLARE EXIT HANDLER FOR 1264 -- INT형 오버플로가 발생하면 이 부분 수행
    BEGIN
    	SELECT CONCAT('INT 오버플로 직전의 합계 --> ', saveHap);
        SELECT CONCAT('1+2+3+4+...+', i, '=오버플로');
    END;
    
    SET i = 1; -- 1부터 증가
    SET hap = 0; -- 합계를 누적
    
    WHILE (TRUE) DO -- 무한 루프.
    	SET saveHap = hap; -- 오버플로 직전의 합계를 저장
        SET hap = hap + i; -- 오버플로가 나면 11, 12행을 수행함
        SET i = i + 1;
    END WHIlE;
END $$
DELIMITER;

CALL errorProc();   


SELECT routine_name, routine_definition FROM INFORMATION_SCHEMA.ROUTINES 
	WHERE routine_scema = 'sqldb' AND routine_type = 'PROCEDURE';

sqldb안에 저장 프로시저들이 뭐뭐 있는지 확인하는 쿼리문.

하지만 저장프로시저의 파라미터는 나오지 않는다.

SELECT parameter_mode, parameter_name, dtd_identifier
	FROM INFORMATION_SCHEMA.PARAMETERS
    WHERE specific_name = 'userProc3';


userProc3의 파라미터 확인 가능.

SHOW CREATE PROCEDURE sqldb.userProc3;



Open Value in Viewer 클릭

DROP PROCEDURE IF EXISTS nameProc;
DELIMITER $$
CREATE PROCEDURE nameProc(
	IN tblName VARCHAR(20)
)
BEGIN
	SELECT * FROM tblName;
END $$
DELIMITER;

CALL nameProc('userTBL');

오류. 테이블이름자체는 파라미터로 넘길 수 없다.

DROP PROCEDURE IF EXISTS nameProc;
DELIMITER $$
CREATE PROCEDURE nameProc(
	IN tblName VARCHAR(20)
)
BEGIN
	SET @sqlQuery = CONCAT('SELECT * FROM', tblName);
    PREPARE myQuery FROM @sqlQuery;
    EXECUTE myQuery;
    DEALLOCATE PREPARE statement;
END $$
DELIMITER;

CALL nameProc('userTBL');

테이블이름을 파라미터로 넘기고 싶다면, 동적 SQL을 사용하면 된다.

스토어드 프로시저의 특징

  1. MySQL의 성능을 향상시킬 수 있다.
    (긴 코드로 구현된 쿼리를 실행하면, 클라이언트에서 서버로 쿼리의 모든 텍스트가 전송되어야 한다. 하지만, 이 긴 코드의 쿼리를 서버에 스토어드 프로시저로 생성해 놓았다면 단지 스토어드 프로시저 이름 및 매개 변수 등 몇 글자의 텍스트만 전송하면 되므로 네트워크의 부하를 어느 정도는 줄일 수 있으며, 결과적으로 MySQL의 성능을 향상시킨다.)
  • 다른 DBMS에서는 스토어드 프로시저가 처음 호출 시에 1회만 컴파일된 후, 메모리에 로딩된다. 그 이후로는 컴파일을 다시 하지 않고 메모리에 저장된 내용이 호출되므로 상당한 성능 향상이 된다. 하지만 MySQL의 스토어드 프로시저는 호출할 때마다 컴파일이 되기 때문에 다른 DBMS처럼 성능 향상이 많이 되지는 않지만, 네트워크 부하를 줄이는 등의 효과가 있으므로 어느 정도의 성능 향상 효과가 있다고 볼 수 있다.

  1. 유지관리가 간편하다.
    (C#이나 Java 등의 클라이언트 응용 프로그램에서 직접 SQL문을 작성하지 않고 스토어드 프로시저 이름만 호출하도록 설정함으로써, 데이터베이스에서 관련된 스토어드 프로시저의 내용을 일관되게 수정/유지보수 등의 작업을 할 수 있다.)

  2. 모듈식 프로그래밍이 가능하다.
    (한번 스토어드 프로시저를 생성해 놓으면, 언제든지 실행이 가능하다. 또한, 스토어드 프로시저로 저장해 놓은 쿼리의 수정, 삭제 등의 관리가 수월해진다. 더불어 다른 모듈식 프로그래밍 언어와 동일한 장점을 갖는다.)

  3. 보안을 강화할 수 있다.
    (사용자 별로 테이블에 접근 권한을 주지 않고, 스토어드 프로시저에만 접근 권한을 줌으로써 좀 더 보안을 강화할 수 있다. 예로, 우리가 자주 사용해온 sqlDB의 userTbl을 생각해 보자.
    userTBL에는 고객이름/전화번호/주소/출생년도/키 등의 개인적인 정보가 들어 있다. 만약, 배송 담당자가 배송을 하고자 한다면 당연히 UserTbl에 접근해야 한다. 하지만, 배송 담당자는 배송을 위한 정보인 주소/전화번호 외에 다른 정보에도 접근할 수가 있어서 보안상 문제가 발생할 소지가 있다.
    이럴 경우에 다음과 같은 프로시저를 생성한 후에 배송 담당자는 userTbl에는 접근 권한을 주지 않고 스토어드 프로시저에만 접근 권한을 준다면 이 문제가 해결될 수 있다.)

profile
김제이아이엠
1이되기까지
이전 포스트

30. MySQL 인덱스 - 인덱스 실습과 결론

다음 포스트

32. MySQL 스토어드 프로그램 - 스토어드 함수, 커서

0개의 댓글