[SQL] 절차형 SQL

절차형 SQL

절차형 SQL 을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다. 

 

 DBMS별로 제공하는 절차형 SQL

오라클 : PL(Procedural language)/SQL

sql server : T-SQL

 

오라클 PL/SQL

오라클의 PL/SQL은 block 구조로 되어있다. 

block 내에는 DML 문장과 쿼리 문장, 절차형 언어(IF, LOOP) 등을 사용할 수 있다. 

PL/SQL 을 이용하면 다양한 저장 모듈을 개발할 수 있다.

저장 모듈이란 PL/SQL 문장을 데이터베이스 서버에 저장해서 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이다. (독립적으로 실행될 수 있는 실행 프로그램을 만들 수 있는 것이다.)

오라클의 저장 모듈에는 procedure, user defined function, trigger 가 있다. 

 

PL/SQL의 특징

• block 구조라서 기능별로 모듈화가 가능하다
• 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
• DBMS 정의 에러나 사용자 정의 에러를 사용할 수 있다.
• 오라클에 내장되어 있어 오라클과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.
• PL/SQL은 응용 프로그램의 성능을 향상시킨다.
• 여러 문장을 block으로 묶고 한번에 block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다. 

PL/SQL 구조

DECLARE -- 선언부(변수, 상수) BEGIN ~ END 에서 사용할 변수나 인수에 대한 정의 및 데이터 타입 선언

BEGIN  -- 개발자가 처리하고자 하는 SQL문과 필요한 로직이 정의되는 실행부.

EXCEPTION -- 에러를 처리하는 에러부 (선택항목) 

END -- 끝

 

PL/SQL 기본 문법

-- 프로시져 생성하는 방법
CREATE [OR REPLACE] procedure [procedure_name] 
       -- [OR REPLACE] 는 같은 이름의 프로시저가 있을 때 덮어쓰라는 명령어.
                              (argument 1 [mode] data_type1,       -- argument 에는 변수 입력
                               argument 2 [mode] data_type2, ...)  -- mode 에는 in, out, inout
IS [AS] ...
BEGIN ...
EXCEPTION ...
END ;
/  -- 프로시저를 컴파일 하라 

-- 프로시져 삭제하는 명령어
DROP procedure [procedure_name] ;

 

MS사의 T-SQL 특징

• 변수 선언 기능 @@ 이라는 시스템 함수(전역 변수)와 @ 이라는 지역변수가 있다. 
• 전역변수는 SQL 에 내장된 값이고, 지역변수는 사용자가 만드는 값이다.
• 데이터 유형(data type)을 제공한다. (int, varchar 등 자료형을 의미함)
• 산술연산자, 비교연산자, 논리연산사 사용이 가능하다.
• if - else, while, case - then 사용이 가능하다.
• -- 행 주석, /* */ 범위 주석(여러줄 주석)이 가능하다. 

T-SQL 구조 

DECLARE -- 선언부(변수, 상수) BEGIN ~ END 에서 사용할 변수나 인수에 대한 정의 및 데이터 타입 선언

BEGIN  -- 개발자가 처리하고자 하는 SQL문과 필요한 로직이 정의되는 실행부.

ERROR -- 에러를 처리하는 에러부 (선택항목) 

END -- 끝

 

T-SQL 기본 문법

-- 프로시져 생성하는 방법
CREATE procedure [schema_name.] procedure_name 
                                @parameter1 data_type1  [mode],       -- argument 에는 변수 입력
                                @parameter2 data_type2, [mode] ...  -- mode 에는 in, out, inout
WITH <proc_option>
AS ...
BEGIN
ERROR ...
END ;

@paramenter 는 프로시저가 호출될 때 프로시저 안으로 어떤 값이 들어오거나 혹은 프로시저에서 처리한 결과 값을 리턴 시킬 매개 변수를 지정할 때 사용한다.

 

[mode] 부분에 지정할 수 있는 매개 변수(@parameter) 유형은 4가지가 있다.

1. VARYING - 결과 집합이 출력 매개 변수로 사용되도록 지정한 CURSOR 매개변수에만 지정된다.
2. DEFAULT - 지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우 지정된 기본값으로 처리한다. 즉, 기본 값이 지정되어 있으면 해당 매개 변수를 지정하지 않아도 프로시저가 지정된 기본 값으로 정상적으로 수행된다.
3. OUT, OUTPUT - 프로시저에 처리된 결과 값을 EXECUTE 문 호출 시 반환한다. 
4. READONLY - 자주 사용되지 않는다. 프로시저 본문 내에서 매개 변수를 업데이트 하거나 수정할 수 없음을 나타낸다. 

WITH 부분에 지정할 수 있는 옵션 3가지

1. RECOMPILE - 데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일 된다. 
2. ENCRYPTION - CREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환된다. 원본을 볼 수 있는 방법이 없으므로 반드시 원본 백업을 해두어야 한다. 
3. EXECUTE AS - 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정한다. 

-- 프로시져 삭제하는 명령어
DROP procedure [schema_name.]procedure_name ;

 

 

 

 

 

kdata 한국데이터진흥원에서 출간한 SQL 전문가 가이드 2013 Edition을 요약했습니다.