[SQLD] #028 SQL 활용 - 절차형 SQL

목차

1. 절차형 SQL 개요

2. PL/SQL 개요

3. T-SQL 개요

4. Procedure의 생성과 활용

5. User Defined Function의 생성과 활용

6. Trigger의 생성과 활용

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

 

 

---

 

 

 

1. 절차형 SQL 개요

일반적인 개발 언어처럼 SQL에도 절차지향적인 프로그램이 가능하도록 DBMS 벤더별로 PL(Procedural Language)/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server)등의 절차형 SQL을 제공하고 있다. 절차형 SQL을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다. 

 

 

 

 

 

 

 

 

 

2. PL/SQL 개요

1. PL/SQL 특징

Oracle의 PL/SQL은 Block 구조로 되어있고 Block 내에는 DML 문장과 QUERY 문장, 그리고 절차형 언어(IF, LOOP) 등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다. 이런 PL/SQL 을 이용하여 다양한 저장 모듈을 개발할 수 있다.

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

 

 

PL/SQL의 특징은 다음과 같다. 

 

- PL/SQL은 Block 구조로 되어있어 각 기능별로 모듈화가 가능하다. 

- 변수, 상수등을 선언하여 SQL 문장 간 값을 교환한다. 

- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다. 

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

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

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

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

 

 

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

 

 

 

 

 

 

 

 

2. PL/SQL 구조

 

- DECLARE : BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부

- BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부

- EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL 문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것인지를 정의하는 예외 처리부

 

 

 

 

 

 

 

3. PL/SQL 기본 문법

CREATE [OR REPLACE] PROCEDURE [Procedure_name]
( arg1 [mode] data_type1,
  arg2 [mode] data_type2, ...
)
IS [AS] ...
BEGIN ...
EXCEPTION ...
END; /


-- 생성된 프로시저를 삭제하는 명령어
DROP PROCEDURE [Procedure_name];

- CREATE : 데이터베이스 내에 프로시저를 생성할 수 있다. 생성한 프로시저는 데이터베이스 내에 저장된다. 프로시저는 개발자가 자주 실행해야하는 로직을 절차적인 언어로 작성한 프로그램 모듈이기 때문에 필요할 때 호출하여 실행할 수 있다. 

- [OR REPLACE] : 데이터베이스 내에 같은 이름의 프로시저가 있을 경우, 기존의 프로시저를 무시하고 새로운 내용으로 덮어쓰기 하겠다는 의미.  

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

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

 

    - IN : 운영체제에서 프로시저로 전달될 변수의 MODE

    - OUT : 프로시저에서 처리된 결과가 운영체제로 전달되는 MODE

    - INOUT : IN과 OUT 두가지의 기능을 동시에 수행하는 MODE (잘 사용하지 않음)

 

- 마지막에 있는 슬래시 ("/") : 데이터베이스에게 프로시저를 컴파일하라는 명령어

 

 

 

 

 

 

 

 

 

 

3. T-SQL 개요

1. T-SQL 특징

T-SQL은 기본적으로 SQL Server를 제어하기 위한 언어로써, T-SQL은 MS 사이에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가해 보완적으로 만든 것이다. T-SQL을 이용하여 다양한 저장 모듈을 개발할 수 있는데, T-SQL의 프로그래밍 기능은 다음과 같다. 

 

- 변수 선언 기능 : @@이라는 전역변수와 @이라는 지역변수가 있다. 

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

- 데이터 유형을 제공한다. (int, float, varchar 등)

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

- 흐름 제어기능 : IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다. 

- 주석 기능 : --은 한줄 주석, /**/ 은 여러줄 주석이 가능하다. 

 

 

 

 

 

 

2. T-SQL 구조

- DECLARE : BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부

- BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다. T-SQL 에서는 BEGIN, END 문을 반드시 사용해야 하는 것은 아니지만, 블록 단위로 처리하고자 할 때는 반드시 작성해야 한다. 

- ERROR 처리 : BEGIN ~ END 절에서 실행되는 SQL 문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것인지를 정의하는 예외 처리부

 

 

 

 

 

 

 

3. T-SQL 기본 문법

CREATE PROCEDURE [schema_name.]Procedure_name
                  @param1 data_type1 [mode],
                  @param2 data_type2 [mode], ...
WITH AS ...
BEGIN ...
ERROR 처리 ...
END;

- CREATE : 데이터베이스 내에 프로시저를 생성할 수 있다. 생성한 프로시저는 데이터베이스 내에 저장된다. 

- 프로시저의 변경이 필요한 경우 Oracle 은 [CREATE OR REPLACE] 와 같이 하나의 구문으로 처리하지만, SQL Server 는 CREATE 구문을 ALTER 구문으로 변경하여야 한다.

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

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

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

 

- WITH 부분에 지정할 수 있는 옵션은 3가지가 있다. 

1. RECOMPILE : 데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일된다. 데이터베이스 엔진에서 저장 프로시저 안에 있는 개별 쿼리에 대한 계획을 삭제하려 할 때 RECOMPILE 쿼리 힌트를 사용한다. 

2. ENCRYPTION : CREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환된다. 변조된 출력은 SQL Server 의 카탈로그 뷰 어디에서도 직접 표시되지 않는다. 원본을 볼 수 있는 방법이 없기 때문에 반드시 원본은 백업을 해두어야 한다.
3. EXECUTE AS : 해당 저장 프로시저를 실행할 보안 컨텍스트를 지정한다.

 

 

 

 

 

 

 

 

 

 

 

 

 

4. Procedure의 생성과 활용

 

예제) SCOTT 유저가 소유하고 있는 DEPT 테이블에 새로운 부서를 등록하는 Procedure를 작성한다. SCOTT 유저가 기본적으로 소유한 DEPT 테이블의 구조는 다음과 같다. 

 

/*Oracle*/

CREATE OR REPLACE Procedure p_DEPT_insert
-- ① DEPT 테이블에 들어갈 칼럼 값(부서코드, 부서명, 위치)을 입력 받는다.
( v_DEPTNO in number,
  v_dname in varchar2,
  v_loc in varchar2,
  v_result out varchar2 )

IS cnt number := 0;

BEGIN
-- ② 입력 받은 부서코드가 존재하는지 확인한다.
  SELECT COUNT(*) INTO CNT
    FROM DEPT WHERE DEPTNO = v_DEPTNO AND ROWNUM = 1;

-- ③ 부서코드가 존재하면 '이미 등록된 부서번호입니다'라는 메시지를 출력 값에 넣는다.
  if   cnt > 0 then v_result := '이미 등록된 부서번호이다';
-- ④ 부서코드가 존재하지 않으면 입력받은 필드 값으로 새로운 부서 레코드를 입력한다.
  else
       INSERT INTO DEPT (DEPTNO, DNAME, LOC)
       VALUES (v_DEPTNO, v_dname, v_loc);
-- ⑤ 새로운 부서가 정상적으로 입력됐을 경우에는 COMMIT 명령어를 통해서 트랜잭션을 종료한다.
       COMMIT;

       v_result := '입력 완료!!';
  end if;

-- ⑥ 에러가 발생하면 모든 트랜잭션을 취소하고 'ERROR 발생'라는 메시지를 출력값에 넣는다.
  EXCEPTION WHEN OTHERS THEN ROLLBACK;
  v_result := 'ERROR 발생';

END; /

 

/* SQL Server */

CREATE Procedure dbo.p_DEPT_insert

-- ① DEPT 테이블에 들어갈 칼럼 값(부서코드, 부서명, 위치)을 입력 받는다.
@v_DEPTNO int,
@v_dname varchar(30),
@v_loc varchar(30),
@v_result varchar(100) OUTPUT
  AS DECLARE @cnt int
         SET @cnt = 0

BEGIN

-- ② 입력 받은 부서코드가 존재하는지 확인한다.
  SELECT @cnt=COUNT(*)
    FROM DEPT WHERE DEPTNO = @v_DEPTNO

-- ③ 부서코드가 존재하면 '이미 등록된 부서번호입니다'라는 메시지를 출력 값에 넣는다.
  IF @cnt > 0
    BEGIN SET @v_result = '이미 등록된 부서번호이다' RETURN END

-- ④ 부서코드가 존재하지 않으면 입력받은 필드 값으로 새로운 부서 레코드를 입력한다.
  ELSE
    BEGIN
      BEGIN TRAN
	    INSERT INTO DEPT (DEPTNO, DNAME, LOC)
      VALUES (@v_DEPTNO, @v_dname, @v_loc)

-- ⑥ 에러가 발생하면 모든 트랜잭션을 취소하고 'ERROR 발생'라는 메시지를 출력값에 넣는다.
	    IF @@ERROR<>0
	      BEGIN ROLLBACK
            SET @v_result = 'ERROR 발생'
		    RETURN
	      END

-- ⑤ 새로운 부서가 정상적으로 입력됐을 경우에는 COMMIT 명령어를 통해서 트랜잭션을 종료한다.
	    ELSE
	      BEGIN COMMIT
            SET @v_result = '입력 완료!!'
		    RETURN
		  END
    END
END

 

 

프로시저 작성할 때 주의해야 할 문법적 요소

 

1. PL/SQL 및 T-SQL 에서는 다양한 변수가 있다. 예제에서 나온 cnt 라는 변수를 SCALAR 변수라고 하는데, SCALAR 변수는 사용자의 임시 데이터를 하나만 저장할 수 있는 변수이며, 거의 모든 형태의 데이터 유형을 지정할 수 있다. 

2. PL/SQL 에서 사용하는 SQL 구문은 대부분은 동일하지만, SELECT 문장은 다르다. PL/SQL에서 사용하는 SELECT 문장은 결과값이 반드시 있어야 하며 결과가 하나여야 한다. 조회 결과가 없거나 하나 이상인 경우에는 에러가 난다. (T-SQL 에서는 결과 값이 없어도 에러가 나지 않음)

3. T-SQL을 비롯하여 일반적으로 대입 연산자는 '='를 사용하지만 PL/SQL 에서는 ':=' 를 사용한다. 

4. 에러 처리를 담당하는 EXCEPTION 에는 WHEN~THEN 절을 사용하여 에러의 종류별로 적절히 처리한다. OTHERS 를 이용하여 모든 에러를 처리할 수 있지만 정확하게 에러를 처리하는 것이 좋다. 

 

 

 

 

 

 

 

 

 

 

5. User Defined Function의 생성과 활용

User Defined Function 은 Procedure 처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해놓은 명령문의 집합을 의미한다. 앞에서 학습한 SUM, SUBSTR, NVL 등의 함수는 벤더에서 미리 만들어놓은 내장함수이고, 사용자가 별도의 함수를 만들 수도 있다. Function이 Procedure와 다른 점은 RETURN을 사용해서 반드시 하나의 값을 돌려줘야 한다는 것이다. 

 

 

 

 

 

 

 

 

 

6. Trigger의 생성과 활용

Trigger란 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다. 즉, 사용자가 직접 호출하여 사용하는 것이 아닌 데이터베이스에서 자동적으로 수행하게 된다. 

Trigger는 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 Trigger와 각 행에 대해 발생되는 Trigger가 있다. 

 

 

예제) Trigger를 사용하여 주문한 건이 입력될 때 마다, 일자별 상품별로 판매 수량과 판매 금액을 집계하여 집계 자료를 보관하도록 한다. 먼저 관련 테이블을 생성한다. 

 

[Oracle]

/* 
Trigger의 역할은 ORDER_LIST에 주문 정보가 입력되면 주문 정보의 주문 일자(ORDER_LIST.ORDER_DATE)와
주문 상품(ORDER_LIST.PRODUCT)을 기준으로 판매 집계 테이블(SALES_PER_DATE)에 해당 주문 일자의 주문 상품 레코드가 존재하면
판매 수량과 판매 금액을 더하고, 존재하지 않으면 새로운 레코드를 입력한다. 
*/

/* 
1. Trigger를 선언한다.
CREATE OR REPLACE Trigger SUMMARY_SALES : Trigger 선언문
AFTER INSERT : 레코드가 입력이 된 후 Trigger 발생
ON ORDER_LIST : ORDER_LIST 테이블에 Trigger 설정
FOR EACH ROW : 각 ROW마다 Trigger 적용
*/
CREATE OR REPLACE TRIGGER SUMMARY_SALES
AFTER INSERT ON ORDER_LIST FOR EACH ROW

/* 
2. o_date(주문일자), o_prod(주문상품) 값을 저장할 변수를 선언하고, 신규로 입력된 데이터를 저장한다.
:NEW는 신규 입력된 레코드의 정보를 가지고 있는 구조체
:OLD는 수정, 삭제되기 전의 레코드를 가지고 있는 구조체
*/
DECLARE
  o_date ORDER_LIST.order_date%TYPE;
  o_prod ORDER_LIST.product%TYPE;
BEGIN
  o_date := :NEW.order_date;
  o_prod := :NEW.product;

/* 
3. 먼저 입력된 주문 내역의 주문 일자와 주문 상품을 기준으로 SALES_PER_DATE 테이블에 업데이트한다. 
*/
  UPDATE SALES_PER_DATE
  SET qty = qty + :NEW.qty,
         amount = amount + :NEW.amount
  WHERE sale_date = o_date
  AND product = o_prod;

/* 
4. 처리 결과가 SQL%NOTFOUND이면 해당 주문 일자의 주문 상품 실적이 존재하지 않으며, 
SALES_ PER_DATE 테이블에 새로운 집계 데이터를 입력한다.
*/
  if SQL%NOTFOUND then
    INSERT INTO SALES_PER_DATE
    VALUES (o_date, o_prod, :NEW.qty, :NEW.amount);
  end if;
END; /

 

 

 

[SQL]

/* 
1. Trigger를 선언한다.
CREATE Trigger SUMMARY_SALES : Trigger 선언문
ON ORDER_LIST : ORDER_LIST 테이블에 Trigger 설정
AFTER INSERT : 레코드가 입력이 된 후 Trigger 발생
*/
CREATE TRIGGER dbo.SUMMARY_SALES
ON ORDER_LIST AFTER INSERT AS

/* 
2. o_date(주문일자), o_prod(주문상품), qty(수량), amount(금액) 값을 저장할 변수를 선언하고, 신규로 입력된 데이터를 저장한다.
inserted는 신규 입력된 레코드의 정보를 가지고 있는 구조체
deleted는 수정, 삭제되기 전의 레코드를 가지고 있는 구조체
*/
DECLARE
  @o_date DATETIME,
  @o_prod INT,
  @qty int,
  @amount int
BEGIN
  SELECT @o_date=order_date, @o_prod=product, @qty=qty, @amount=amount
  FROM inserted

/* 
3. 먼저 입력된 주문 내역의 주문 일자와 주문 상품을 기준으로 SALES_PER_DATE 테이블에 업데이트한다.
*/
  UPDATE SALES_PER_DATE
  SET qty = qty + @qty,
         amount = amount + @amount
  WHERE sale_date = @o_date
  AND product = @o_prod;

/*
4. 처리 결과가 0건이면 해당 주문 일자의 주문 상품 실적이 존재하지 않으며, 
SALES_PER_DATE 테이블에 새로운 집계 데이터를 입력한다.
*/
  IF @@ROWCOUNT=0
    INSERT INTO SALES_PER_DATE
    VALUES (@o_date, @o_prod, @qty, @amount)
END

 

- ROLLBACK 을 하면 하나의 트랜잭션이 취소가 되어 Trigger 로 입력된 정보까지 하나의 트랜잭션으로 인식하여 두 테이블 모두 입력이 취소된다.

- Trigger는 데이터베이스에 의해 자동 호출되지만, 결국 INSERT, UPDATE, DELETE 문과 하나의 트랜잭션 안에서 일어나는 일련의 작업들이다. 

- Trigger는 데이터베이스 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용될 수 있다.

 

 

 

 

 

 

 

 

 

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

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

 

 

 

 

 

 

 

 

 

 

 

---

출처
이 글의 내용은 모두 한국데이터베이스진흥원이 출판한 SQL 전문가 가이드 2013 Edition을 기본으로 한다.