[DataBase] 스토어드 프로시저 (Stored Procedure)

스토어드 프로시저의 개념

- 스토어드 프로시저란 java에서 보면 일종의 내장 함수라고 보면 된다.

ex) exp()과 같은 것을 말함.

- 특정한 작업을 수행하는 SQL문을 논리적으로 그룹화한 것을 말한다. 프로시저는 그 자체 만으로도 완전한 프로그램의 축소판이라고 할 수 있다.

- 연속된 SQL문들을 하나로 모아 SQL 서버에 미리 컴파일해서 저장해 놓은 것을 말하는데 클라이언트로부터 호출문을 통해 복잡한 SQL의 일괄 작업을 수행하는 데 적합하다.

스토어드 프로시저의 구성

- 선언부 : 타입, 커서, 상수, 변수, 내포된 서브 프로그램을 선언하는 부분

- 실행부 : 코드 실행을 제어하고 데이터를 조작하는 문장들을 작성하는 부분

- 예외 처리부 : 스토어드 프로시저 실행중에 발생하는 예외를 처리하는 부분

장점

- 모듈별 프로그래밍 허용 

스토어드 프로시저를 한 번만 만들어 데이터베이스에 저장한 후, 여러 프로그램에서 계속해서 스토어드 프로시저를 호출하여 사용할 수 있다.

스토어드 프로시저는 데이터베이스 프로그래머가 만들며 원본 코드와 상관없이 수정할 수 있다.

- 빠른 SQL 실행 시간

스토어드 프로시저는 만들어질 때 구문이 분석되고 최적화 된다.

한 번 실행된 후에는 메모리에 캐시되어 다음 실행에서는 빠르게 실행될 수 있다.

- 보안성 향상

사용자가 스토어드 프로시저를 통해서만 데이터에 접근할 수 있도록 사용자에 대한 데이터베이스 접근 권한을 제한 할 수 있다.

 

- 네트워크 통신량 감소

응용 프로그램에서는 수백 줄의 SQL코드를 필요로하는 작업을 네트워크로 보내지 않고 스토어드 프로시저를 실행하는 하나의 명령문만을 보내서 동일한 작업을 수행할 수 있다.

생성

- CREATE PROCEDURE 명령어를 사용한다.

ex) CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)

[지역변수 선언]

프로시저 BODY;

- OR REPLACE: 선택적인 예약어이다. 이를 사용하면 동일한 스토어드 프로시저 이름이 이미 존재하는 경우, 기존의 스토어드 프로시저를 대체할 수 있다.

- 프로시저명 : 생성하려는 스토어드 프로시저의 이름

- 파라미터 : 스토어드 프로시저 파라미터로는 다음과 같은 것들이 올 수  있다.

IN : 호출되는 스토어드 프로시저에 값을 전달하는 것을 지정한다.

OUT : 스토어드 프로시저가 그 호출 프로그램에게 값을 반환한다는 것을 지정한다.

INOUT : IN과 OUT을 합쳐놓은 것, 즉, 전달하는 값과 반환되는 값이 같을 때 사용.

- 프로시저 BODY : 스토어드 프로시저의 본문 코드를 기록하는 부분이다.

BEGIN으로 시작해서 END로 끝나는 데 적어도 하나의 SQL문이 있어야 한다. 그렇지 않으면 오류가 발생.

변수에 값을 치환할 때는 예약어 SET을 이용한다.

ex) CREATE OR REPLACE PROCEDURE emp_change_s(IN i_사원번호 integer)

BEGIN

UPDATE 급여

SET 지급방식 = 'S'

WHERE 사원번호 = i_사원번호;

END

제거 

-DROP PROCEDURE 프로시저명;

-제거 후 다시 되돌릴 수 없다.

실행

- EXECUTE 프로시저명;

-EXEC 프로시저명;