2-2장 SQL 활용 - 8절 절차형 SQL

1. 절차형 SQL 개요

절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로 PL/SQL, SQL/PL, T-SQL 등의 절차형 SQL을 제공
절차형 SQL을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성 가능

 

2. PL/SQL 개요

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

 

PL/SQL 엔진처리 과정
PL/SQL Block 프로그램을 입력받으면 SQL 문장과 프로그램 문장을 구분하여 처리 → 프로그램 문장은 PL/SQL 엔진이 처리하고 SQL 문장은 Oracle 서버의 SQL Statement Executor가 실행하도록 작업을 분리하여 처리

 

PL/SQL 구조
DECLARE: BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부
BEGIN~END: 개발자가 처리하고자 하는 SQL문과 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부
EXCEPTION: BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면  에러를 어떻게 처리할 것이지를 정의하는 예외 처리부(선택항목)

 

PL/SQL 기본문법
CREATE [OR REPLACE] Procedure [Procedure_name]
argument1 [mode] data_type1, argument2 [mode] date_type2, ... ... )
IS [AS] ... ... BEGIN ... ... EXCEPTION ... ... END;
DROP Procedure [Procedure_name];
- CREATE TABLE 명령어로 테이블을 생성하듯 CREATE 명령어로 데이터베이스 내에 프로시저를 생성
- [OR REPLACE] 절은 데이터베이스 내에 같은 이름의 프로시저가 있을 경우, 새로운 내용으로 덮어쓰기 하겠다는 의미
- Argument는 프로시저 호출 시 프로시저로 어떤 값이 들어오거나 처리한 결과값을 리턴시킬 매개 변수를 지정
- [mode] 부분에 지정할 수 있는 매개 변수의 유형은 3가지 (IN은 운영 체제 → 프로시저로 전달 / OUT은 프로시저에서 처리된 결과 → 운영체제로 전달 / INOUT은  IN과 OUT 두 가지의 기능을 동시에 수행)

 

3. T-SQL 개요

근본적으로 SQL Server를 제어하기 위한 언어
MS사에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가해 보완적으로 만든 것

 

특징
변수 선언 기능 @@이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있다.
지역변수: 사용자가 자신의 연결 시간 동안만 사용하기 위해 만들어지는 변수
전역변수: 이미 SQL 서버에 내장된 값
데이터 유형(Data Type)을 제공한다. 즉 int, float, varchar 등의 자료형을 의미한다.
연산자(Operator) 산술연산자( +, -, *, /)와 비교연산자(=, <, >, <>) 논리연산자(and, or, not) 사용이 가능하다.
흐름 제어 기능 IF-ELSE 와 WHILE, CASE-THEN 사용이 가능하다.
주석 기능한줄 주석 : -- 뒤의 내용은 주석범위 주석 : /* 내용 */ 형태를 사용하며, 여러 줄도 가능함

 

T-SQL 구조
DECLARE: BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부
BEGIN ~ END: 개발자가 처리하고자 하는 SQL문과 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부ERROR 처리: BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부
T-SQL에서는 BEGIN, END 문을 반드시 사용해야하는 것은 아니지만, 블록 단위로 처리하고자 할 때는 반드시 작성해야 한다.

 

T-SQL 문법
CREATE Procedure [schema_name.]Procedure_name @parameter1 data_type1 [mode], @parameter2 date_type2 [mode], ... ... WITH AS ... ... BEGIN ... ... ERROR 처리 ... ... END;
DROP Procedure [schema_name.]Procedure_name;

- CREATE TABLE 명령어로 테이블을 생성하듯 CREATE 명령어로 데이터베이스 내에 프로시저를 생성
- 프로시저의 변경이 필요할 경우 Oracle은 [CREATE OR REPLACE]와 같이 하나의 구문으로 처리
- SQL Server는 CREATE 구문을 ALTER 구문으로 변경
- @parameter는 프로시저 호출 시 프로시저로 어떤 값이 들어오거나 처리한 결과 값을 리턴 시킬 매개변수 지정
- [mode] 부분에 지정할 수 있는 매개 변수(@parameter)의 유형은 4가지
① VARYING결과 집합이 출력 매개 변수로 사용되도록 지정 CURSOR 매개변수에만 적용된다.
② DEFAULT 지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우 지정된 기본값으로 처리. 기본 값이 지정되어 있으면 해당 매개 변수 지정하지 않아도 프로시저가 지정된 기본 값으로 정상적으로 수행
③ OUT, OUTPUT프로시저에서 처리된 결과 값을 EXECUTE 문 호출 시 반환한다.
④ READONLY 자주 사용되지는 않는다. 프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음을 나타낸다. 매개 변수 유형이 사용자 정의 테이블 형식인 경우 READONLY를 지정해야 한다.
- WITH 부분에 지정할 수 있는 옵션은 3가지
① RECOMPILE 데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일 됨. 데이터베이스 엔진에서 저장 프로시저 안의 개별 쿼리에 대한 계획을 삭제할 때 RECOMPILE 쿼리 힌트를 사용
② ENCRYPTIONCREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환. 변조된 출력은 SQL Server의 카탈로그 뷰 어디에서도 직접 표시 X. 원본을 볼 수 있는 방법이 없어 백업을 해두어야 한다.
③ EXECUTE AS 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정한다.

 

4. Procedure

어떤 작업을 절차적 일괄처리 작업하는데 사용. 반복적인 트랜잭션을 수행할 수 있는 PL/SQL 블록, db내에 미리 컴파일되어 저장되있다가 필요할시 매번 사용가능

 

프로시저 생성시 주의할 점
PL/SQL 및 T-SQL 에서는 다양한 변수가 있다. SCALAR 변수는 사용자의 임시 데이터를 하나만 저장할 수 있는 변수이며 거의 모든 형태의 데이터 유형을 지정할 수 있다.
PL/SQL에서 사용하는 SELECT 문장은 결과값이 반드시 있어야 하며, 그 결과 역시 반드시 하나여야 한다. 조회 결과가 없거나 하나 이상인 경우에는 에러를 발생 T-SQL 에서는 결과 값이 없어도 에러가 발생 X
T-SQL 을 비롯하여 일반적으로 대입 연산자는 “=”을 사용하지만 PL/SQL에서는 “:=”를 사 용한다.
에러 처리를 담당하는 EXCEPTION 에는 WHEN ~ THEN 절을 사용하여 에러의 종류별로 적절히 처리한다. OTHERS를 이용하여 모든 에러를 처리할 수 있지만 정확하게 에러를 처리하는 것이 좋다. T-SQL 에서는 에러 처리를 다양하게 처리할 수 있다.

 

5. 사용자 정의 함수

절차형 SQL을 로직과 함께 DB내에 저장해 놓은 명령문의 집합. 반드시 RETURN을 통해 하나의 값 반환

 

6. 트리거

DML 문이 수행되었을 때 자동으로 동작하는 프로그램(프로시저는 EXCUTE로 실행함), DCL와 TCL은 실행 블가(TCL은 사용 가능)

 

7. 프로시저와 트리거의 차이점

프로시저는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK 과 같은 트랜잭션 종료 명령어를 사용할수 있지만, 데이터베이스 트리거는 BEGIN ~ END 절 내에 사용할 수 없다