콘텐츠로 이동
≡

Administrator

Developer

Tools

External Tools

1. 저장 프로시저#

저장 프로시저의 개요#

저장 프로시저(Stored Procedure)란 SQL문들과 흐름 제어문, 할당문, 오류 처리 루틴 등으로 구성된 데이터베이스 객체 (object) 중의 하나이다. 저장 프로시저는 생성될 때 컴파일 되어 바로 실행 가능한 상태로 데이터베이스에 저장되며 여러 세션에서 동시에 하나의 저장 프로시저를 실행하는 것도 가능하다.

"저장 프로시저(Stored Procedure)"라는 용어는 때때로 저장 프로시저와 저장 함수(Stored Function)를 모두 지칭하기도 한다. 저장 프로시저와 저장 함수의 다른 점은 저장 함수가 실행 시 값을 반환하는 것 외에는 차이가 없다.

저장 프로시저와 저장 함수는 각각 CREATE PROCEDURE 와 CREATE FUNCTION 구문을 사용해서 생성할 수 있다. 이 구문에 대한 자세한 설명은 2장 "저장 프로시저 SQL문"을 참고하기 바란다.

저장 프로시저의 종류#

저장 프로시저#

저장 프로시저는 SQL구문이나 다른 저장 프로시저 내에서 입력 인자, 출력 인자, 입출력 인자를 가지고 실행할 수 있다. 저장 프로시저 호출 시, 프로시저의 바디 부분에 정의된 절차에 따라서 SQL문을 수행하게 된다. 저장 프로시저는 반환 값을 가지지 않지만, 출력 인자와 입출력 인자들을 통해 프로시저를 호출한 클라이언트에게 값을 전달할 수도 있다. 이는 반환 값을 갖지 않기 때문에 SQL문의 연산식 (expression) 내에서 피연산자로 사용될 수 없다.

저장 함수#

값을 반환하는 것만 제외하면 저장 프로시저와 동일하다. 저장 프로시저와 달리 하나의 반환 값을 가지므로 SQL문의 연산식 (expression)내에서 피연산자로 사용할 수 있다.

타입 세트#

저장 프로시저의 사용자 정의 타입들을 정의한 집합이다. 이는 주로 저장 프로시저끼리 인자 또는 리턴 값으로 사용자 정의 타입을 주고받을 때 사용한다.

자세한 내용은 7장 "타입 세트"에서 다룬다.

저장 프로시저의 특징#

SQL 구문을 이용한 절차적 프로그램#

Altibase PSM (Persistent Stored Module)은 흐름 제어문과 예외 처리문을 제공하므로 SQL문을 사용해서 절차적 프로그래밍이 가능하다.

성능#

여러 SQL문을 순차적으로 수행하는 클라이언트 프로그램의 경우에는 각 SQL문 수행 시 마다 데이터베이스 서버와 통신을 해야 하므로 통신 비용이 많이 발생한다. 반면, 저장 프로시저로 작성된 프로그램은 프로시저 호출 시 한번의 통신만으로 여러 SQL문을 수행할 수 있다.

따라서, 저장 프로시저를 사용하면 통신 부하의 감소와 함께 데이터베이스 서버와 클라이언트 응용 프로그램간의 데이터 타입의 차이로 인해 발생하는 내부적인 데이터 타입 변환의 부하도 줄일 수 있다.

모듈화#

업무 절차를 구현하는데 필요한 모든 SQL작업을 하나의 저장 프로시저로 묶어 모듈화하여 관리할 수 있다.

소스 코드 관리의 용이성#

저장 프로시저는 데이터베이스 서버에 저장되는 모듈이기 때문에, 업무 로직의 변경 시 여러 클라이언트에 설치된 프로그램들을 모두 수정할 필요 없이 저장 프로시저만 변경하면 되므로 프로그램 관리가 용이하다.

공유와 생산성#

한 사용자가 생성한 저장 프로시저는 데이터베이스에 저장되므로 접근 권한이 부여된 다른 사용자도 해당 저장 프로시저를 실행할 수 있어 서로 공유할 수 있을 뿐만 아니라, 한 저장 프로시저 내에서 다른 저장 프로시저의 호출이 가능하므로 같은 업무 절차의 재 프로그래밍이 필요 없으므로 생산성을 높일 수 있다.

SQL과의 통합성#

저장 프로시저 내의 흐름 제어문의 조건절은 SELECT 문의 조건절을 그대로 사용할 수 있다. 즉, C/C++등의 주 언어의 흐름 제어문의 조건절에서는 사용할 수 없는 SQL문 스타일의 기능을 사용할 수 있다. 또한, 연산식에 부질의 (subquery)를 사용하거나 SQL문이 지원하는 시스템 제공 함수들을 그대로 사용할 수 있다는 점 등 SQL문과 밀착된 프로그래밍이 가능하다.

에러 및 예외처리#

저장 프로시저 내에서 Exception Handler를 제공하므로 SQL문 수행 도중 오류가 발생했을 때 적절한 대응 조치를 서버 내에서 바로 처리할 수 있다.

저장성#

저장 프로시저 또한 데이터베이스 객체이기 때문에 사용자가 삭제하기 전까지 데이터베이스 내에 저장된다. 따라서 업무 절차 또한 데이터베이스에 저장하여 보존시킬 수 있다.

보안강화#

저장 프로시저 및 저장 함수 등의 PSM 코드 프로그램을 altiwrap 유틸리티로 암호화하여 노출되는 것을 방지할 수 있다. 이 유틸리티에 대한 설명은 Utilities Manual을 참고한다. Altibase가 암호화할 수 있는 구문은 아래와 같다.

  • CREATE [OR REPLACE] PROCEDURE

  • CREATE [OR REPLACE] FUNCTION

  • CREATE [OR REPLACE] TYPESET

  • CREATE [OR REPLACE] PACKAGE

  • CREATE [OR REPLACE] PACKAGE BODY

저장 프로시저의 구조#

저장 프로시저는 블록으로 구조화된 언어로, 저장 프로시저의 바디는 여러 개의 논리적인 블록들로 구성된다.

저장 프로시저는 크게 헤더와 바디로 나뉘어진다. 저장 프로시저의 바디는 하나의 큰 블록으로서 선언부, 프로시저의 실제 바디, 예외 처리부로 구성된다. 바디는 다시 여러 개의 하위 블록들을 가질 수 있다.

저장 프로시저 구조를 예를 들어 설명하면 다음과 같다.

블록2는 블록1의 하위 블록으로 블록1의 구조와 같이 선언부, 바디, 예외 처리부로 구성될 수도 있다.

흐름 제어문도 명시적인 시작과 끝을 알 수 있는 하나의 블록이다.

저장 프로시저 사용시 주의 사항#

트랜잭션 관리#

저장 프로시저 내에서 사용 가능한 트랜잭션 제어문은 COMMIT, ROLLBACK 문이다. 저장 프로시저 내에서 사용한 트랜잭션 제어문은 저장 프로시저 밖의 작업에도 영향을 미칠 수 있다.

예를 들어서 NON-AUTOCOMMIT 모드에서 다음과 같은 작업을 수행했다고 가정하자.

iSQL> INSERT INTO t1 values (1);
iSQL> INSERT INTO t1 values (2);
iSQL> EXECUTE proc1;

proc1이 "INSERT INTO t1 values (3)" 구문과 "ROLLBACK" 문을 수행한다면 프로시저 내에서 입력한 3 뿐만 아니라 프로시저 외부의 iSQL에서 입력한 1과 2도 ROLLBACK된다. 즉, 위의 두 INSERT 문과 EXECUTE 문은 하나의 트랜잭션으로 처리된다.

제약 사항#

커서가 OPEN된 상태에서 COMMIT 또는 ROLLBACK을 실행할 수 있다.

SELECT 문 내에서 호출되는 저장 함수의 경우 저장 함수 내에 INSERT, UPDATE, DELETE 문은 사용할 수 없으며, 트랜잭션 제어문도 수행할 수 없다.

INSERT, UPDATE, DELETE 문 내에서 호출되는 저장 함수내에서도 트랜잭션 제어문을 수행할 수 없다.

관련 메타 테이블#

저장 프로시저 관련 메타 테이블에 대한 자세한 내용은 General Reference 의 데이터 딕셔너리 부분을 참조한다.