[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #8

<목차>

• PL/SQL 개요

• Procedure의 생성과 활용

• User Defined Function의 생성과 활용

• Trigger의 생성과 활용

• Procedure와 Trigger의 차이점

---

<< 절차형 SQL >>

* 일반적인 개발 언어처럼 SQL에도 절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로 PL/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등의 절차형 SQL을 제공한다.

 

* 이번 게시글에서는 절차형 SQL을 이용하여 만들 수 있는 저장 모듈인 Procedure, User Defined Function, Trigger 에 대해서 살펴볼 것이다.

---

 

1. PL/SQL 개요 (Oracle)

 

A. PL/SQL 특징

* Block 구조로 되어있고, Block 내에는 DML 문과 Query 문, 절차형 언어(IF, LOOP) 등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어이다.

 

* 특징

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

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

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

• 응용 프로그램의 성능을 향상시킨다.

  * 여러 SQL 문장을 Block 단위로 묶어서 서버와 통신하여, 통신량이 줄어들기 때문이다.

* 처리 과정

Oracle의 PL/SQL 엔진

 

 

1-2. PL/SQL 구조

* PL/SQL의 Block구조를 표현.

PL/SQL 블록 구조

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

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

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

 

 

1-3. PL/SQL 기본 문법 (SYNTAX)

* Procedure를 통해서 기본 문법을 정리한다.

 

* 예시 : Procedure 생성

CREATE [OR REPLACE] Procedure [Procedure_name] ( argument1 [mode] data_type1, argument2 [mode] date_type2, ... ... ) 
IS [AS] ... ... 
BEGIN ... ... 
EXCEPTION ... ... 
END; 
/   /* '/' 는 프로시저를 컴파일하라는 명령어 */

 

* 예시 : Procedure 삭제

DROP Procedure [Procedure_name];

 

* CREATE로 생성한 프로시저는 DB에 저장된다.

* 자주 실행하는 로직을 절차적인 언어로 작성한 프로그램 모듈이므로, 필요할 때 호출하여 실행할 수 있다.

* [OR REPLACE] 는 이미 프로시저가 존재하는 경우, 기존 프로시저에 덮어쓰기를 하겠다는 의미이다.

* Argument의 mode에는 3가지가 들어올 수 있다. (IN, OUT, INOUT)

---> IN : OS에서 프로시저로 전달될 변수

---> OUT : 프로시저에서 처리된 결과가 OS로 전달될 변수

---> INOUT : IN, OUT 모드를 동시에 수행

* '/' 는 DB에게 프로시저를 컴파일하라는 명령어.

 

2. Procedure의 생성과 활용

* 생성할 프로시저의 기능을 Flow Chart로 나타낸 그림이다.

부서 입력 Flow Chart

 

* 예시 - 문제

   : SCOTT 유저가 소유하고 있는 DEPT 테이블에 새로운 부서를 등록하는 프로시저를 작성한다. SCOTT 유저가 기본적으로 소유한 DEPT 테이블의 구조는 아래와 같다.

DEPT 테이블 구조

 

* 예시 - 결과

CREATE OR REPLACE Procedure p_DEPT_insert ------------ (1)
( 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 -------------(2) 
    FROM DEPT 
    WHERE DEPTNO = v_DEPTNO 
    AND ROWNUM = 1; 
 
    if 
        cnt > 0 
    then -------------(3)
        v_result := '이미 등록된 부서번호이다'; 
    else 
        INSERT INTO DEPT (DEPTNO, DNAME, LOC) -------------(4)
        VALUES (v_DEPTNO, v_dname, v_loc); 
        COMMIT; -------------(5) 
        v_result := '입력 완료!!'; 
    end if; 
EXCEPTION -------------(6)
    WHEN OTHERS 
    THEN ROLLBACK; 
    v_result := 'ERROR 발생'; 
END; 
/

(1) : 인수 값들은 (부서코드, 부서이름, 위치, 결과값) 이다.

(2) : 입력 받은 v_DEPTNO와 같은 부서코드를 가진 부서가 존재하는지 확인하는 작업, 변수 cnt(SCALAR 변수)에 값을 할당

---> PL/SQL 에서 사용하는 SELECT 문에서는 결과 값이 반드시 존재해야 하며, 값이 하나여야만 한다.

(3) : 0보다 큰 경우 같은 부서코드가 존재한다는 뜻

(4) : 부서코드가 존재하지 않는 경우(else), (부서코드, 부서이름, 위치)를 DEPT 테이블에 삽입하고

(5) : COMMIT을 통해 해당 (4)의 트랜잭션을 DB에 반영하고 트랜잭션을 종료한다.

(6) : 에러가 발생한 경우 ROLLBACK을 통해 모든 트랜잭션을 취소한다.

 

---> PL/SQL 에서는 대입 연산자로 '=' 가 아닌 ':='를 사용한다.

---> 예외 처리부에서는 WHEN ~ THEN 절을 사용하여 각 에러의 종류별로 적절히 처리해주도록 한다. OTHERS를 통해 모든 예외를 처리할 수 있지만 정확하게 에러를 처리할 수 있으면 그러는 것이 좋다.

 

* 예시 : 위의 프로시저를 테스트해보자.

-------------------- (1) 
SELECT * FROM DEPT; 
 
/*
DEPTNO DNAME LOC 
------- ------- --------- 
10 ACCOUNTING NEW YORK 
20 RESEARCH DALLAS 
30 SALES CHICAGO 
40 OPERATIONS BOSTON 
*/
 
 
---------------------------(2) 
variable rslt varchar2(30); 
 
 
----------------------------------------------(3)
EXECUTE p_DEPT_insert(10,'dev','seoul',:rslt);  
/* PL/SQL 처리가 정상적으로 완료되었다. */
 
 
-----------(4) 
print rslt; 
/*
RSLT 
---------------------- 
이미 등록된 부서번호이다 
*/
 
 
-------------------------------------------------(5) 
EXECUTE p_DEPT_insert(50,'NewDev','seoul',:rslt); 
/* PL/SQL 처리가 정상적으로 완료되었다. */
 
 
-----------(6) 
print rslt; 
/*
RSLT 
----------
입력 완료!! 
*/
 
 
-------------------(7) 
SELECT * FROM DEPT; 
/*
DEPTNO DNAME LOC 
------ -------- --------- 
10 ACCOUNTING NEW YORK 
20 RESEARCH DALLAS 
30 SALES CHICAGO 
40 OPERATIONS BOSTON 
50 NewDev SEOUL 
5개의 행이 선택되었다.
*/

(1) : 부서 추가 전에 부서가 4개인 것을 확인할 수 있다.

(2) : Procedure를 실행한 결과 값을 받을 변수를 선언한다. (varchar2로 타입을 맞춰준다.)

(3) : p_DEPT_insert 이름의 프로시저를 실행, 결과 값을 rslt에 저장.

(4) : DEPTNO 가 10인 부서는 이미 존재하기 때문에 rslt에 해당하는 메시지 저장됨.

(5) : p_DEPT_insert 이름의 프로시저를 실행, 결과 값을 rslt에 저장.

(6) : DEPTNO 가 50인 부서는 없기 때문에 프로시저에서 부서를 추가한 뒤, 해당하는 메시지 저장됨.

(7) : DEPT 테이블을 조회하면 DEPTNO가 50인 데이터가 정확히 저장됨을 확인할 수 있다.

 

 

3. User Defined Function의 생성과 활용

* 앞의 Procedure 처럼 절차형 SQL을 로직과 함께 DB 내에 저장해 놓은 명령문의 집합을 의미한다.

* Procedure와의 차이점은 RETURN 을 통해 반드시 하나의 값을 반환해야 한다는 것이다.

 

* 예시 - 문제

   : K-리그 8월 경기 결과와 두 팀간의 점수차를 ABS 함수를 통해 절대값으로 출력한다.

 

* 예시 - 결과

/* Oracle */
SELECT 
    SCHE_DATE 경기일자, 
    HOMETEAM_ID || ' - ' || AWAYTEAM_ID 팀들, 
    HOME_SCORE || ' - ' || AWAY_SCORE SCORE, 
    ABS(HOME_SCORE - AWAY_SCORE) 점수차 
FROM SCHEDULE 
WHERE GUBUN = 'Y' 
AND SCHE_DATE BETWEEN '20120801' AND '20120831' 
ORDER BY SCHE_DATE;
 
 
/* SQL Server */
SELECT 
    SCHE_DATE 경기일자, 
    HOMETEAM_ID + ' - ' + AWAYTEAM_ID AS 팀들, 
    HOME_SCORE + ' - ' + AWAY_SCORE AS SCORE, 
    ABS(HOME_SCORE - AWAY_SCORE) AS 점수차 
FROM SCHEDULE 
WHERE GUBUN = 'Y' 
AND SCHE_DATE BETWEEN '20120801' AND '20120831' 
ORDER BY SCHE_DATE;
 
/*
경기일자 팀들        SCORE 점수차 
-------- ---------- ----- ----- 
20120803 K01 - K03  3 - 0  3 
20120803 K06 - K09  2 - 1  1 
20120803 K08 - K07  1 - 0  1 
20120804 K05 - K04  2 - 1  1 
20120804 K10 - K02  0 - 3  3 
20120811 K07 - K10  1 - 1  0 
20120811 K03 - K08  2 - 0  2 
20120811 K09 - K05  0 - 1  1 
20120811 K04 - K02  0 - 2  2 
20120811 K01 - K06  0 - 0  0 
20120818 K05 - K01  0 - 2  2 
20120818 K02 - K09  1 - 2  1 
20120818 K08 - K10  3 - 1  2 
20120818 K04 - K07  1 - 0  1 
20120818 K06 - K03  3 - 1  2
....
25개의 행이 선택되었다.
*/

---> ABS 함수를 User Defined Function으로 재정의 해본다.

CREATE OR REPLACE Function UTIL_ABS (v_input in number) ---------------- (1)
RETURN NUMBER 
IS v_return number := 0; ---------------- (2)
BEGIN 
    if 
        v_input < 0 
    then ---------------- (3)
        v_return := v_input * -1; 
    else 
        v_return := v_input; 
    end if; 
RETURN v_return; ---------------- (4)
END; 
/

(1) : NUMBER 타입의 v_input을 입력 받는다.

(2) : 리턴 값을 받을 v_return 선언, 초기값은 0

(3) : 입력 값이 음수면 -1 곱해서 v_return 값에 할당한다.

(4) : v_return 값을 반환한다.

---> ABS 대신 UTIL_ABS로 사용하면 된다.

 

4. Trigger의 생성과 활용

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

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

 

* 예시 - 문제

   : Trigger를 사용해 주문한 건이 입력될 때마다, 일자별 상품별로 판매 수량과 판매 금액을 집계하여 집계자료를 보관하도록 한다.

 

* 참고 테이블

 

* 예시 - 결과

   : 주문정보 테이블(ORDER_LIST)에 주문 정보가 입력되면 주문 정보의 주문 일자와 주문 상품을 기준으로 판매 집계 테이블(SALES_PER_DATE)에 해당 주문 일자의 주문 상품 레코드가 존재하면 수량/판매 금액을 올리고, 존재하지 않으면 새로운 레코드 입력.

CREATE OR REPLACE Trigger SUMMARY_SALES ---------------- (1) 
AFTER INSERT ON ORDER_LIST FOR EACH ROW 
DECLARE ---------------- (2) 
    o_date ORDER_LIST.order_date%TYPE; 
    o_prod ORDER_LIST.product%TYPE; 
BEGIN     
    o_date := :NEW.order_date; 
    o_prod := :NEW.product; 
    UPDATE SALES_PER_DATE ---------------- (3) 
    SET qty = qty + :NEW.qty, amount = amount + :NEW.amount 
    WHERE sale_date = o_date AND product = o_prod; 
    
    if 
        SQL%NOTFOUND 
    then ---------------- (4) 
        INSERT INTO SALES_PER_DATE 
        VALUES(o_date, o_prod, :NEW.qty, :NEW.amount); 
    end if; 
END; 
/

(1) : Trigger 선언.

    : BEFORE/AFTER - 이벤트 전/후

    : INSERT ON ORDER_LIST - ORDER_LIST 테이블에 INSERT가 발생한 후에 트리거 실행.

    : FOR EACH ROW - 각 ROW 마다 Trigger 적용

(2) : ORDER_LIST 테이블의 order_date, product의 데이터 타입과 동일한 타입으로 o_date, o_prod 변수 선언한다.

(3) : 입력된 주문 일자와 주문 상품을 기준으로 SALES_PER_DATE 테이블의 UPDATE 진행

(4) : 결과가 SQL%NOTFOUND면 존재하지 않는 것이므로 새로운 데이터를 입력해준다.

Trigger 에서 사용하는 레코드 구조체 비교-1

Trigger 에서 사용하는 레코드 구조체 비교-2

 

* 예시 : 트리거를 적용시켜보자.

-- 최초 ORDER_LIST 테이블
SELECT * FROM ORDER_LIST; 
선택된 레코드가 없다. 
 
 
-- 최초 SALES_PER_DATE 테이블
SELECT * FROM SALES_PER_DATE; 
선택된 레코드가 없다. 
 
 
-- ORDER_LIST에 레코드 추가 : 트리거 발생
INSERT INTO ORDER_LIST VALUES('20120901', 'MONOPACK', 10, 300000); 
1개의 행이 만들어졌다. 
 
 
COMMIT; 
커밋이 완료되었다.
 
 
-- ORDER_LIST에 추가한 레코드 출력
SELECT * FROM ORDER_LIST; 
/*
ORDER_DATE PRODUCT  QTY AMOUNT 
---------- -------- --- ------- 
20120901   MONOPACK 10  300000 
*/
 
 
-- 트리거를 통해 SALES_PER_DATE에도 레코드 추가됨.
SELECT * FROM SALES_PER_DATE; 
/*
SALE_DATE PRODUCT  QTY AMOUNT 
--------- -------- --- -------- 
20120901  MONOPACK 10  300000
*/
 
 
-- 같은 제품의 추가 주문이 들어온 상태 : 트리거 발생
INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',20,600000); 
1개의 행이 만들어졌다.
 
 
COMMIT; 
커밋이 완료되었다.
 
 
-- 2개의 주문 데이터가 존재한다.
SELECT * FROM ORDER_LIST; 
/*
ORDER_DATE PRODUCT  QTY  AMOUNT 
---------- -------- ---- ------- 
20120901   MONOPACK 10   300000 
20120901   MONOPACK 20   600000 
*/
 
 
-- 트리거에서 같은 제품이기 때문에 QTY와 AMOUNT를 더해서 최신화만 시킨다.
SELECT * FROM SALES_PER_DATE; 
/*
SALE_DATE PRODUCT  QTY  AMOUNT 
--------- -------- ---- ------- 
20120901  MONOPACK 30   900000
*/
 
 
-- 제품명이 다른 주문이 들어온 상태 : 트리거 발생
INSERT INTO ORDER_LIST VALUES('20120901','MULTIPACK',10,300000); 
1개의 행이 만들어졌다. 
 
 
-- 3개의 주문 데이터가 존재한다.
SELECT * FROM ORDER_LIST; 
/*
ORDER_DATE PRODUCT   QTY  AMOUNT 
---------- --------- ---- ------
20120901   MONOPACK  10   300000 
20120901   MONOPACK  20   600000 
20120901   MULTIPACK 10   300000 
*/
 
 
-- 트리거에서 다른 제품이기 때문에 새로운 레코드를 생성했다.
SELECT * FROM SALES_PER_DATE; 
/*
SALE_DATE PRODUCT   QTY  AMOUNT 
--------- --------- ---- ------
20120901  MONOPACK  30   900000 
20120901  MULTIPACK 10   300000 
*/
 
 
ROLLBACK; 롤백이 완료되었다. 
 
 
-- ROLBLACK시, 하나의 트랜잭션이 취소되어 이전 상태로 돌아간다.
SELECT * FROM ORDER_LIST; 
/*
ORDER_DATE PRODUCT   QTY  AMOUNT 
---------- --------- ---- ------
20120901   MONOPACK  10   300000 
20120901   MONOPACK  20   600000 
*/
 
 
-- 트리거로 입력된 정보까지 하나의 트랜잭션으로 인식하기 때문에 두 테이블 모두 입력취소됨.
SELECT * FROM SALES_PER_DATE; 
/*
SALE_DATE PRODUCT   QTY  AMOUNT 
--------- --------- ---- ------ 
20120901  MONOPACK  30   900000 
*/

---> Trigger는 DB 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 등에 사용될 수 있다.

 

5. Procedure와 Trigger의 차이점

* 프로시저는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용할 수 있지만, 트리거는 BEGIN ~ END 절 내에 사용할 수 없다. 또한 DCL 역시 사용 불가능하다.

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

 

 

 

출처

www.dbguide.net/db.db?cmd=view&boardUid=148207&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1

데이터 전문가 지식포털 DBGuide.net