[정보처리기사 실기 - 데이터베이스] 스토어드 프로시저 (Stored Procedure)

1. 스토어드 프로시저의 개념

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

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

- 스토어드 프로시저의 구성

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

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

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

 

 

2. 스토어드 프로시저의 장점

(1) 모듈별 프로그래밍 허용

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

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

 

(2) 빠른 SQL 실행 시간

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

- 스토어드 프로시저는 한 번 실행된 후에는 메모리에 캐시되어 다음 실행에서는 빠르게 실행될 수 있다.

 

(3) 보안성 향상

(4) 네트워크 통신량 감소

 

 

3. 스토어드 프로시저 생성

- CREATE PROCEDURE 명령어를 사용

- 표기형식

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

[지역변수 선언]

프로시저 BODY;

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

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

- 파라미터

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

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

- INOUT : 스토어드 프로시저에 값을 전달하고, 실행 후 호출 프로그램에 값을 반환해야 한다는 것을 지정한다.

- 프로시저 BODY

- BEGIN으로 시작해서  END로 끝난다. 적어도 하나의 SQL문이 있어야 함.

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

 

 

4. 스토어드 프로시저 제거

DROP PROCEDURE 프로시저명;

 

 

 

5. 스토어드 프로시저 실행

EXECUTE 프로시저명;

EXEC 프로시저명;

 

 

 

 

 

[참고] 2011 시나공 정보처리기사 실기 문제집