절차형 SQL

1. 개요

SQL로도 절차적 프로그램이 가능하도록 제공하는 SQL을 말한다. DBMS 벤더별로 PL/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등이 있다.

이번 글에서는 절차형 SQL로 만들 수 있는 Procedure, User Defined Function, Trigger에 대해 살펴본다.

 

2. PL/SQL

PL/SQL은 저장 모듈을 개발하고 사용할 수 있게 해준다. 저장 모듈이란 PL/SQL 문장을 DB 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다. Oracle에서는 Procedure, User Defined Function, Trigger을 제공한다.

 1) 특징

- Block 구조로 되어 있어 각 기능별로 모듈화가 가능하다

- 변수, 상수 등을 선언하여 문장 간 값을 교환할 수 있다

- IF, LOOP 등의 절차형 언어를 사용 가능하다

- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다

- PL/SQL은 Oracle에 내장되어 있어서 Oracle과 PL/SQL를 지원하면 어떤 서버라도 프로그램을 옮길 수 있다

- PL/SQL은 응용 프로그램의 성능을 향상시킨다

- 여러 SQL 문장을 Block으로 묶고 한 번에 서버로 보내기 때문에 통신량을 줄일 수 있다

2) PL/SQL Architecture

https://docs.oracle.com/

SQL을 처리하는 엔진과 프로그램 문장을 처리하는 엔진이 따로 있어서 작업을 분리하여 처리한다.

3) PL/SQL 구조

DECLARE
# 선언부 (변수, 상수)

BEGIN
# 개발자가 처리하고자 하는 SQL문과 필요한 로직

EXCEPTION
# (선택) 에러 처리를 정의

END

 

4) PL/SQL Syntax

CREATE [OR REPLACE] Procedure [Procedure_name]
( argument1 [mode] data_type1,
 argument2 [mode] data_type2,
 ...
)
IS [AS]
 ...
BEGIN
 ...
EXCEPTION
 ...
END;
/ # 데이터베이스에게 컴파일하라는 명령어

OR REPLACE: 기존 프로시저가 있을 때 덮어쓰기 하겠다는 의미

Argument: 프로시저가 호출될 때 입력되는 값이거나 혹은 프로시저의 결과값을 운영 체제로 리턴시킬 매개 변수를 지정할 때 사용한다

[mode]: 지정할 수 있는 매개 변수의 유형은 3가지이다.

   (1) IN: 운영체제에서 프로시저로 전달될 변수의 MODE
   (2) OUT: 프로시저의 결과값이 운영체제로 전달되는 MODE
   (3) INOUT: 두 가지 기능을 동시에 하는 MODE

 

3. T-SQL

1) 특징

- @@이라는 전역변수(시스템 변수)와 @이라는 지역변수가 있다

- 지역변수는 사용자의 연결 시간 동안만 사용하기 위해 만들어진 변수이며 전역변수는 이미 SQL 서버에 내장된 값이다

- Data Type을 제공한다 (int, float, varchar)

- 산술 연산자와 비교연산자, 논리연산자 사용이 가능

- IF-ELSE, WHILE, CASE-THEN 사용 가능

- -- 혹은 /* */로 주석 기능이 있다

2) T-SQL 구조

DECLARE
# 선언부 (변수, 상수)

BEGIN
# 개발자가 처리하고자 하는 SQL문과 필요한 로직

ERROR 처리
# (선택) 에러 처리를 정의

END

3) T-SQL Syntax

CREATE Procedure [schema_name.]Procedure_name
@parameter1 data_type1 [mode],
@parameter1 data_type1 [mode],
 ...
WITH <proc_option>
AS
 ...
BEGIN
 ...
ERROR 처리
 ...
END;

 

5. User Defined Function

Funtion과 Procedure의 다른 점은 Funtion은 반드시 하나의 값을 되돌려줘야 한다.

6. Trigger

특정한 테이블에 DML문이 수행되었을 때, 데이터 베이스에서 자동으로 동작하도록 작성된 프로그램이다.

예를 들어, 쇼핑몰의 주문 데이터를 조회한다고 할 때, 수만 건의 데이터를 매번 읽어오는 것보다 주문 데이터가 갱신될 때마다 집계자료를 보관하도록 할 수 있다. Trigger는 테이블과 뷰, 데이터베이스 작업을 대상으로 할 수 있고, 전체 트랜잭션 작업에 대해 발생되는 Trigger와 각 행에 대해 발생되는 Trigger가 있다.

1) Syntax

CREATE OR REPLACE Trigger SUMMARY_SALES
  AFTER INSERT  # 입력된 후 Trigger 발생
  ON ORDER_LIST # ORDER_LIST 테이블에 Trigger 설정
  FOR EACH ROW  # 각 ROW마다 Trigger 적용
DECLASE
  o_date ORDER_LIST.order_date%TYPE;
  o_prod ORDER_LIST.product%TYPE;
BEGIN
  o_date  :=  :NEW.order_date;  # :NEW 신규로 입력된 레코드의 정보를 가지고 있는 구조체. :OLD는 이전 레코드
  o_prod  := :NEW.product;
  UPDATE SALES_PER_DATE   # 입력된 값으로 update
  SET qty = qty + :NEW.qty,
      amount = amount + :NEW.amount
  WHERE sale_date = o_date
   AND product = o_prod;
  if SQL%NOTFOUND then    # 결과가 SQL%NOTFOUND이면 insert
    INSERT INTO SALES_PER_DATE
    VALUES(d_date, o_prod, :NEW.pty, :NEW.amount);
  end if;
END;
/

 

2) Procedure와 차이점

BEGIN ~ END 절 내에 COMMIT, ROLLBACK 과 같은 트랜잭션 종료 명령어를 사용할 수 없다.