[MySQL] Stored Program

 

스토어드 프로그램이란 MySQL 안에서 프로그래밍 언어와 같은 기능을 제공하는 것을 통틀어서 말한다. 스포이트 프로그램은 크게 스토어드 프로시저, 스토어드 함수, 트리거, 커서 등이 있다.

 

스토어드 프로그램은 프로그래밍 기능을 제공하고 나아가 시스템 성능 향상에도 도움이 될 수 있습니다.

 

1. 스토어드 프로시저

 

스토어드 프로시저를 알기전 몇 가지 용어 대해 알고 넘어갑니다.

 

PL/SQL(Procedural Language extension to SQL)

 

PL//SQL은 상용 관계형 데이터베이스 시스템인 오라 DBMS에서 SQL 언어를 확장하기 위해 사용하는 컴퓨터 프로그래밍 언어 중 하나입니다.

SQL의 확장된 개념으로 PL/SQL Block 내에서 DML과 Query문, 절차형 언어 if, loop 등을 사용하여 절차적 프로그래밍을 가능하게 한 트랜잭션 언어입니다.

 

PL/SQL BLOCK

 

PL/SQL 소스 프로그램의 기본 단위를 BLOCK이라고 하는데 블록은 선언부, 실행부, 예외 처리부로 구성됩니다.

이 블록은 이름의 유무에따라 구분할 수 있는데 이름이 존재하지 않으면 익명 블록, 그렇지 않으면 함수, 프로시저 , 패키지 등이 포함됩니다.

 

 

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

자주 사용되는 일반적인 쿼리를 사용하기보다는 이것을 모듈화 시켜서 필요할 때마다 호출하도록 하여 편리하게 운영할 수 있습니다.

 

형식

DELIMITER $$
CREATE PROCEDURE 스포어드 프로시저이름(IN 또는 OUT 파라미터)
BEGIN

SQL PROGRAMING CODING....

END $$
DELIMITER ;


------호출

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

 

생성 예제

DELIMITER $$
CREATE PROCEDURE userProc()
BEGIN
	SELECT * FROM Notice;
END $$
DELIMITER ;

CALL userProc();

 

DELIMITER $$로 변경하는 것은 스토어저 프로시저를 묶어주는 부분이다.

MySQL의 종료 문자는 세미콜론인데 프로시저안에서도 세미콜론이 종료 문자이므로 구별하기 위해 변경하며, 프로시저가 끝나는 부분에서 다시 세미콜론으로 변경했다.

 

스토어드 프로시저의 수정과 삭제

 

스토어드 프로시저의 수정은 간단히 ALTER PROCEDURE를 사용하면 되며, 삭제는 DROP PROCUDURE를 사용하면 된다.

 

 

매개 변수의 사용

 

스토어드 프로시저에는 실행 시 입력 매개변수를 지정할 수 있다. 입력된 매개 변수는 스토어드 프로시저의 내부에서 다양한 용도로 사용될 수 있다.

또한 스토어드 프로시저에서 처리된 결과를 출력 매개 변수를 통해 얻을 수도있다.

 

입력 매개 변수 지정 형식

 

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

기본값은 프로시저의 실행 시에 매개 변수에 값을 전달하지 않았을 때 사용한다.

입력 매개 변수가 있는 스토어드 프로시저를 실행하기 위해서는 프로시저 매개 변숫값에 전달 값을 전달해야 한다.

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

입력 매개변수가 있는 프로시저 생성 및 실행

 

 

10행에서 Notice테이블의 title값을 입력 매개변수를 넘겨주어 프로시저에서 조건 처리가 가능하다.

 

 

이제 출력 매개변수를 설정하자.

출력 매개변수 형식 또한 입력 매개변수 형식과 거의 같으며 IN을 OUT으로만 변경해주면 사용 가능하다.

DROP PROCEDURE IF EXISTS sumProc;
DELIMITER $$
CREATE PROCEDURE sumProc(
IN hitValue INT,
OUT outValue INT
)
BEGIN

	INSERT INTO Notice(hit,title,writerId) VALUES(hitValue,'Example','asnic'); 
    SELECT MAX(id) into outValue FROM Notice;
END $$
DELIMITER ;

CALL sumProc(123123,@val);
select CONCAT('현재 입력된 ID값 = > ',@val);

outValue값을 출력 값으로 받아 concat에 사용했습니다.

 

스토어드 프로시저는 테이블이 존재하지 않아도 생성된다 다만 실행 시점에는 테이블이 존재해야 합니다.

 

 

스토어드 프로시저의 내의 오류처리

 

오류가 발생한 경우 DECLARE 액션 HANDLER FOR 오류 조건 처리할_문장 구문을 사용할 수 있다.

DROP PROCEDURE IF EXISTS errorProc;
DELIMITER $$
CREATE PROCEDURE errorProc()
BEGIN
	DECLARE i INT;
    DECLARE hap INT;
    DECLARE saveHap INT;
    
    DECLARE EXIT handler for 1264
    BEGIN 
    SELECT CONCAT('오버플로 직전의 합계',saveHap);
    SELECT CONCAT('1+2+3+....+', i , '=오버플로');
    END;
    
    SET i = 1;
    SET hap = 0;
    
    WHILE (TRUE) DO
		SET saveHap = hap;
        SET hap = hap + i;
        SET i = i +1;
	END WHILE;
    
END $$
DELIMITER ;
    
call errorProc();
    

오류 번호를 통해 예외처리를 할 수 있습니다.

 

스토어드 프로시저의 특징

 

MySQL의 성능을 향상할 수 있다.

 

긴 코드로 구현된 쿼리를 실행하게 되면 클라이언트에서 서버로 쿼리의 모든 텍스트가 전송되어야 합니다.

하지만 스토어드 프로시저가 생성되었다면 단지 매개변수, 혹은 프로시저 이름만 전송하므로 네트워크의 부하를 어느 정도 줄일 수 있습니다.

 

유지 관리가 간편하다.

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

 

모듈식 프로그래밍이 가능하다.

 

한번 스토어드 프로시저를 생성해놓으면 언제든지 실행이 가능합니다.

 

보안을 강화할 수 있다.

 

사용자 별로 테이블의 접근 권한을 주지 않고 스토어드 프로시저에만 접근 권한을 줌으로써 좀 더 보안을 강화할 수 있다.