[MariaDB] 마리아DB 스토어드 프로그램 - 스토어드 프로시저 Stored Procedure (MySQL)

[MariaDB] 마리아DB 스토어드 프로그램 - 스토어드 프로시저 Stored Procedure (MySQL)

 

스토어드 프로그램 종류는 4가지 입니다.

1. 스토어드 프로시저 Stored Procedure
2. 스토어드 함수 Stored Function
3. 커서 Cursor
4. 트리거 Trigger

이번 포스팅에서는 스토어드 프로시저(Stored Procedure)에 대해 설명하겠습니다.

스토어드 프로시저는 쿼리문의 집합으로 어떤 동작을 일괄 처리하기 위한 용도로 사용됩니다.

자주사용하는 쿼리가 있다면 매번 쿼리를 다 치는게 아니라 스토어드 프로시저로 만들어 모듈화 시킨 후 필요할 때마다 호출하여 사용하면 편리합니다.

 

스토어드 프로시저(Stored Procedure)의 장점도 4가지 입니다.

1. DB의 성능을 향상시킬 수 있다.
2. 유지관리가 간편하다
3. 모듈식 프로그래밍이 가능하다
4. 보안을 강화할 수 있다.

보안을 강화하는 방법은 사용자 별로 테이블에 접근권한을 주지 않고 스토어드 프로시저에만 접근권한을 줌으로써 테이블의 모든 정보를 사용자에게 노출하지 않고 프로시저에서 선택한 정보만 사용자에게 보여줄 수 있습니다.

 

스토어드 프로시저의 정의 형식 및 사용 예제

-- 스토어드 프로시저 형식
DELIMITER $$	--스토어드 프로그램 시작. 구분문자 $$ 선언. $$가 아니라 다른 문자로도 사용가능
CREATE PROCEDURE 스토어드프로시저이름(IN 또는 OUT 파라미터)	-- 파라미터를 받을때는 IN, 내보낼때는 OUT
BEGIN	--프로시저를 호출할때 실행 될 코드 시작위치
	......	--이 부분에 SQL 프로그램 코딩
END $$	--코딩 끝을 선언하고 구분문자 $$ 닫기
DELIMITER;	--최종적으로 스토어드프로시저 DELIMITER로 닫기

CALL 스토어드프로시저이름();	-- 필요할때 스토어드 프로시저 호출()



-- 스토어드 프로시저 예제
USE sqlDB;	-- DB사용
DROP PROCEDURE IF EXISTS userProc;	-- userProc가 있다면 삭제
DELIMITER $$	-- 스토어드 프로그램 시작. 구분문자 $$
CREATE PROCEDURE userProc()	--프로시저 userProc 생성
BEGIN	-- 코드 시작
    SELECT * FROM userTBL; -- 스토어드 프로시저 내용
END $$	-- 코드 끝. 구분문자 $$ 닫기
DELIMITER ;	-- 스토어드 프로그램 끝(닫기)

CALL userProc();	-- userProc 호출

 

매개변수가 있는 프로시저 사용 예제

USE sqlDB;	-- sqlDB사용
DROP PROCEDURE IF EXISTS userProc1;	-- userProc1이 존재하면 삭제
DELIMITER $$	-- 스토어드 프로그램 시작
CREATE PROCEDURE userProc1(IN userName VARCHAR(10))	-- userProc1 프로시저 생성, IN 입력매개변수명 데이터형식
BEGIN	-- 코딩 시작
  SELECT * FROM userTBL WHERE name = userName; 	-- 매개변수(파라미터, parameter) userName을 이용하여 넘어온 코딩
END $$	-- 코딩 끝
DELIMITER ;	--스토어드 프로그램 끝

CALL userProc1('김이름');	-- 전달인자(아귀먼트, argument) '김이름'을 넣어서 userProc1 호출

 

매개변수가 2개인 프로시저 사용 예제

DROP PROCEDURE IF EXISTS userProc2;	-- userProc2가 존재하면 삭제
DELIMITER $$	-- 스토어드 프로그램 시작
CREATE PROCEDURE userProc2(	--userProc2 프로시저 생성
    IN userBirth INT, 	-- 입력 매개변수(파라미터), 매개변수명 userBirth, INT 데이터형식
    IN userHeight INT	-- 입력 매개변수(파라미터), 매개변수명 userHeight, INT 데이터형식
)
BEGIN	--코드 시작
  SELECT * FROM userTBL 
    WHERE birthYear > userBirth AND height > userHeight;	-- 매개변수보다 크고 나이많은 user 선택
END $$	-- 코드 끝
DELIMITER ;	-- 스토어드 프로그램 끝

CALL userProc2(1970, 178);	-- 1970, 178 전달인자로 userProc2호출. 

 

출력매개변수 사용 예제

DROP PROCEDURE IF EXISTS userProc3;	
DELIMITER $$
CREATE PROCEDURE userProc3(
    IN txtValue CHAR(10),	-- 입력매개변수 IN, 매개변수명 txtValuer, 데이터형식 CHAR(10)
    OUT outValue INT	-- 출력매개변수 OUT, 매개변수명 outValue, 데이터형식 INT
)
BEGIN	-- 코딩시작
  INSERT INTO testTBL VALUES(NULL,txtValue);	-- 입력매개변수 txtValue를 이용하여 INSERT 쿼리실행
  SELECT MAX(id) INTO outValue FROM testTBL; 	-- testTBL테이블의 id값중 가장 높은 값을 출력매개변수 outValue에 집어 넣음
END $$
DELIMITER ;

CREATE TABLE IF NOT EXISTS testTBL(	-- 테이블이 존재하면 삭제하고 생성
    id INT AUTO_INCREMENT PRIMARY KEY, 	-- id 값은 INT로 자동증가. id는 생성 시 마다 1,2,3,4,... 로 증가
    txt CHAR(10)	-- txt는 not null을 안했기에 null값이 입력가능
);	-- 테이블을 프로시저보다 뒤에 생성할 수는 있지만 프로시저호출은 반드시 테이블 생성 후에 해야 함

CALL userProc3 ('테스트값', @myValue);	-- 전달인자(아귀먼트) '테스트값'을 txtValue로 보내고, outValue에 INTO(입력)된 id값을 @myValue 변수로 받음

SELECT CONCAT('현재 입력된 ID 값 ==>', @myValue);	-- @myValue 변수를 이용. INSERT시 AUTO_INCREMENT로 생성된 id값이 들어 있음

 

IF ELSE문을 활용한 스토어드 포르시저 예제

DROP PROCEDURE IF EXISTS ifelseProc;
DELIMITER $$
CREATE PROCEDURE ifelseProc(	-- ifelseProc 프로시저 생성
    IN userName VARCHAR(10)	--입력 매개변수 userNmae을 VARCHAR(10)형식으로 받음
)
BEGIN
    DECLARE bYear INT; -- 변수 bYear 선언
    SELECT birthYear into bYear FROM userTBL	-- userTBL테이블의 birthYear값을 bYear변수에 입력
        WHERE name = userName;	-- userTBL테이블의 name이 매개변수 userName과 같을 때
    IF (bYear >= 2000) THEN	-- IF시작. 만약 변수 bYear가 2000보다 크다면
            SELECT '21세기에 태어났습니다.';	-- 참일 경우 실행
    ELSE
            SELECT '20세기에 태어났습니다.';	-- 거짓일 경우 실행
    END IF;	--IF 종료
END $$
DELIMITER ;

CALL ifelseProc ('박이름');	-- 박이름을 전달인자(아귀먼트)로 ifelseProc 호출

 

CASE문을 활용한 스토어드 프로시저 예제

DROP PROCEDURE IF EXISTS caseProc;	-- caseProc 프로시저가 존재하면 삭제
DELIMITER $$
CREATE PROCEDURE caseProc(	-- 프로시저 caseProc 생성
    IN userName VARCHAR(10)	-- 입력매개변수 userName VARCHAR(10)
)
BEGIN
    DECLARE bYear INT; 	-- 변수 bYear INT 형식으로 선언
    DECLARE tti  CHAR(3);-- 변수 tti(띠) CAHR(3) 형식으로 선언
    SELECT birthYear INTO bYear FROM userTBL
        WHERE name = userName;
    CASE 	-- CASE문 시작
        WHEN ( bYear%12 = 0) THEN    SET tti = '원숭이';	-- 변수 bYear를 12로 나눈 나머지가 0일때(when), tti 변수에 원숭이 입력(SET)
        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 = '양';	-- 위에 WHEN에 없는 경우는 tti 변수를 '양'으로 SET
    END CASE;	-- CASE문 끝
    SELECT CONCAT(userName, '의 띠 ==>', tti);	-- (매개변수)userNAME의 띠 ==> (변수)tti 출력
END $$
DELIMITER ;

CALL caseProc ('강이름');	-- 강이름을 아귀먼트로 caseProc 호출

 

WHILE문을 활용한 스토어드 프로시저 예제

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

DROP PROCEDURE IF EXISTS whileProc;	-- whileProc 프로시저가 존재하면 삭제
DELIMITER $$
CREATE PROCEDURE whileProc()
BEGIN
    DECLARE str VARCHAR(100); -- 변수 srt 선언. 각 단을 문자열로 저장하기위한 변수
    DECLARE i INT; -- 변수 i 선언. 구구단 앞자리
    DECLARE k INT; -- 변수 k 선언. 구구단 뒷자리
    SET i = 2; -- 앞자리 변수i를 2로 입력하여 2단부터 계산
    
    WHILE (i < 10) DO  -- WHILE문 시작. i가 10보다 작다면 실행. 바깥 반복문. 2단~9단까지.
        SET str = ''; -- 변수 str 초기화. 각 단의 결과를 저장할 문자열 초기화
        SET k = 1; -- 변수 k는 1로 입력. 구구단 뒷자리는 항상 1부터 9까지.
        WHILE (k < 10) DO	-- WHILE문 시작. k가 1~9까지 돌아감(10보댜 작을때까지)
            SET str = CONCAT(str, '  ', i, 'x', k, '=', i*k); -- 문자열 만들기. i'x'k'='i*k
            SET k = k + 1; -- 뒷자리 변수 k 1증가
        END WHILE;	-- WHILE 시작위치로 돌아감 (뒷자리 k WHILE)
        SET i = i + 1; -- 앞자리 변수 I 1증가
        INSERT INTO guguTBL VALUES(str); -- 각 단의 결과 변수 str을 값으로 테이블에 입력(INSERT).
    END WHILE;	-- WHILE 시작위치르 돌아감 (앞자리 i WHILE)
END $$
DELIMITER ;

CALL whileProc();	-- 호출
SELECT * FROM guguTBL;	-- guguTBL 테이블 셀렉트. str값으로 입력된 테이블 데이터 SELECT 됨.

 

DECLARE HANDLER를 이용한 오류처리 스토어드 프로시저 예제

DROP PROCEDURE IF EXISTS errorProc;
DELIMITER $$
CREATE PROCEDURE errorProc()	-- erroProc 프로시저 생성
BEGIN
    DECLARE i INT; -- 변수 i 선언. 1씩 증가하는 값
    DECLARE hap INT; -- 변수 hap 선언. 합계 (정수형). 오버플로 발생시킬 예정.
    DECLARE saveHap INT; -- 변수 saveHap 선언. 합계 (정수형). 오버플로 직전의 값을 저장.

    DECLARE EXIT HANDLER FOR 1264 -- INT형 오버플로(에러 1264)가 발생하면 아래 BEGIN 수행
    BEGIN
        SELECT CONCAT('INT 오버플로 직전의 합계 --> ', saveHap);	--saveHap 출력
        SELECT CONCAT('1+2+3+4+...+',i ,'=오버플로');	--1씩 증가한값을 쌓았을때 최정적으로 더하게된 값이 i
    END;

    SET i = 1; -- i는 1부터 시작
    SET hap = 0; -- 합계를 누적

    WHILE (TRUE) DO  -- 무한 루프.
        SET saveHap = hap; -- 오버플로 직전의 합계를 저장
        SET hap = hap + i;  -- 오버플로가 나면 11, 12행을 수행함(DECLARE HANDLER)
        SET i = i + 1; 
    END WHILE;
END $$
DELIMITER ;

CALL errorProc();	-- errorProc 프로시저 호출

 

테이블 이름을 파라미터로 전달하는 방법 예제

DROP PROCEDURE IF EXISTS nameProc;
DELIMITER $$
CREATE PROCEDURE nameProc(
    IN tblName VARCHAR(20)	--입력매개변수(파리미터) tblName VARCHAR(20) 데이터형식
)
BEGIN	-- 코드시작
  SET @sqlQuery = CONCAT('SELECT * FROM ', tblName); 
  -- 변수 @sqlQuery에 CONCAT 내용 입력. CONCAT값은 매개변수 tblName을 이용한 SELCT문. FROM 뒤 공백 1개 주의.
  PREPARE myQuery FROM @sqlQuery;	-- 변수@sqlQuery를 'myQuery'에 준비시킴(PREPARE)
  EXECUTE myQuery;	-- 'myQuery' 실행
  DEALLOCATE PREPARE myQuery; 'myQuery' 준비를 해제시킴
END $$
DELIMITER ;

CALL nameProc ('userTBL');	-- userTBL값을 전달인자(아귀먼트)로 nameProc 프로시저 호출

 

 

[MariaDB] 전체보기

 

'MariaDB' 카테고리의 글 목록