[SQL] MYSQL 스토어드 프로시저

✍️ Study Note

 

 

 

---

 

 

스토어드 프로시저 생성, 삭제

2021.10.20 - [코딩공부] - [SQL] MYSQL 스토어드 프로시저(IF, CASE, WHILE)와 동적 SQL

[SQL] MYSQL 스토어드 프로시저(IF, CASE, WHILE)와 동적 SQL

 

 

스토어드 프로시저 매개변수 사용

1. 입력 매개변수 (IN)

-- #1
USE TABLEDB;
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
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);					-- 매개변수 사용

 

1-1. 입력 매개변수 & IF (가정문)

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('조용필');

 

1-2. 입력 매개변수 & CASE (가정문)

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('김범수');

 

1-3. 입력 매개변수 & WHILE (반복문)

DELIMITER $$
CREATE PROCEDURE WHILEPROC()
BEGIN 
    DECLARE STR VARCHAR(100); 			-- 변수 선언
    DECLARE I INT; 				-- 구구단 앞자리
    DECLARE K INT; 				-- 구구단 뒷자리
    SET I = 2; 					-- 구구단 2단부터 시작
    
    WHILE (I < 10) DO 				-- 구구단 뒷쪽
	SET STR = ''; 				-- 각 단의 결과를 저장할 문자열 초기화
        SET K =1;	
        WHILE (K < 10) DO 			-- 구구단 뒷쪽
		SET STR = CONCAT (STR, ' ', I, 'X', K, '=', I*K); -- 각 단의 문자열 만들기
            	SET K = K + 1; 			-- 구구단 뒷자리 9까지 반복
	END WHILE;
        SET I = I + 1; 				-- 구구단 앞자리 9까지 반복
        INSERT INTO GUGUTBL VALUES(STR); 	-- 각 단의 결과를 테이블에 입력
	END WHILE;
END $$
DELIMITER ; 

CREATE TABLE GUGUTBL (TXT VARCHAR(100));

CALL WHILEPROC();
SELECT * FROM GUGUTBL;

 

1-4. 입력 매개변수 & DECLARE HANDLER (오류 처리)

DELIMITER $$
CREATE PROCEDURE ERRORPROC()
BEGIN 
    DECLARE I INT;			-- 변수 설정
    DECLARE HAP INT;
    DECLARE SAVEHAP INT;
    
    DECLARE EXIT HANDLER FOR 1264	-- 에러 발생 시 작업 설정
    BEGIN 
	SELECT CONCAT('INT 오버플로 직전의 합게 --> ', SAVEHAP);
        SELECT CONCAT('1+2+3+4+...+', I ,'=오버플로');
    END;
    
    SET I =1; 				-- 변수 시작
    SET HAP = 0;
					-- 변수 변화주기
    WHILE(TRUE) DO			-- 아래 작업 무한 반복
		SET SAVEHAP = HAP;	-- 에러 직전값 저장
        SET HAP = HAP + I;		-- 변수 +1
        SET I = I + 1;			-- 변수 +1
	END WHILE;
END $$
DELIMITER ;

CALL ERRORPROC();

 

2. 출력 매개변수 (OUT)

DELIMITER $$
CREATE PROCEDURE USERPROC3 (IN TXTVALUE CHAR(10), OUT OUTVALUE INT) -- 입력 & 출력 매개변수 설정
BEGIN 
    INSERT INTO TESTTBL VALUES(NULL, TXTVALUE);  -- TESTTBL에 값 입력
    SELECT MAX(ID) INTO OUTVALUE FROM TESTTBL;   -- ID 최신값 출력
END $$
DELIMITER ;

CREATE TABLE IF NOT EXISTS TESTTBL 		-- testtbl 테이블이 없었기에 만들어줌.
(ID INT AUTO_INCREMENT PRIMARY KEY,
 TXT CHAR(10)
 );

call userproc3('테스트값', @myvalue);
select concat ('현재 입력된 id 값 ==>', @myvalue);

-- (1) txtvalue 값 ('테스트값')을 입력하면
-- (2) testtbl 테이블에 txtvalue 값이 입력되고 id가 순서대로 생성됨.
-- (3) 생성된 id중 가장 최신 값이 @myvalue에 나타나고
-- (4) select의 문장과 합쳐져 '하나의 문장으로' 출력됨.

 

 

 

스토어드 프로시저 조회

SHOW CREATE PROCEDURE TABLEDB.USERPROC3;

• 스토어드 프로지서 이름, SQL 모드, CREATE문 등 조회 가능

 

테이블 이름을 파라미터로 사용한 동적 SQL

DELIMITER $$
CREATE PROCEDURE NAMEPROC(IN TBLNAME VARCHAR(20) ) -- 테이블 이름을 변수로 설정
BEGIN
    SET @SQLQUERY = CONCAT('SELECT * FROM ', TBLNAME);	-- 임시쿼리 생성
    PREPARE MYQUERY FROM @SQLQUERY;		-- 임시쿼리를 불러올 동적 SQL 생성
    EXECUTE MYQUERY;		-- 동적 SQL 실행
    DEALLOCATE PREPARE MYQUERY;		-- 동적 SQL 문장해제
END $$
DELIMITER ;

CALL NAMEPROC('USERTBL');

 

스토어드 프로시저 특징

1. MYSQL 성능 향상
2. 유지관리 간편 : 사용자들이 스토어드 프로시저 이름만 호출하여 사용 -> 일관되게 수정 및 유지보수 가능
3. 모듈식 프로그래밍 : 언제든 실행 가능하며, 쿼리 수정 및 삭제 용이
4. 보안 강화 : 스토어드 프로시저에만 접근 권한을 줌으로서 보안 강화