[SQL] Procedure, Function, Trigger

Procedure 생성 예제

SCOTT 유저가 소유하고 있는 DEPT 테이블에 새로운 부서를 등록하는 프로시저 만드는 예제

SCOTT 유저가 소유한 DEPT 테이블의 구조는 다음과 같음.

 

<DEPT 테이블>

컬럼	타입	길이	인덱스
DEPTNO	NUMBER	2	PK
DNAME	VARCHAR2	14
LOC	VARCHAR2	13

 

 

오라클

CRAETE 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                                        ---- (2)
   SELECT COUNT(*) INTO CNT
   FROM   DEPT
   WHERE  DEPTNO = v_DEPTNO                   ---- (3) 
   AND    ROWNUM = 1 ;
   if cnt > 0 then
      v_result := '이미 등록된 부서번호이다';
   else
      INSERT INTO DEPT (DEPTNO, ENAME, 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 ;
/
    

 

sql server

CREATE procedure dbo.p_DEPT_insert            ---- (1) 
@v_DEPTNO  int, 
@v_dname   varchar(30),
@v_loc     varchar(30),
@v_result  varchar(100) OUTPUT
AS
DELARE @cnt int
BEGIN  
   SELECT @cnt = COUNT(*)                     ---- (2)
   FROM   DEPT
   WHERE  DEPTNO = @v_DEPTNO
   IF     @cnt > 0                            ---- (3)
   BEGIN  
      SET @v_result = '이미 등록된 부서번호이다'
      RETURN
   END
   ELSE
   BEGIN 
      BEGIN   TRAN
      INSERT  INTO DEPT (DEPTNO, DNAME, LOC) ---- (4) 
      VALUSE  (@v_DEPTNO, @v_dname, @v_loc)
      IF      @@ERROR <> 0
      BEGIN   
          ROLLBACK                           ---- (6) 
          SET @v_result = 'ERROR 발생'
          RETURN
      END
      ELSE
      BEGIN 
          COMMIT                            ---- (5) 
          SET @v_result = '입력완료!!'
          RETURN
      END
  END
END 

1. DEPT 테이블에 들어갈 컬럼 값(부서코드, 부서명, 위치) 을 입력 받는다.
2. 입력 받은 부서코드가 존재하는지 확인한다.
3. 부서코드가 존재하면 '이미 등록된 부서입니다' 라는 메세지를 출력값에 넣는다. 
4. 부서코드가 존재하지 않으면 입력받은 필드값으로 새로운 부서 레코드를 입력한다. 
5. 새로운 부서가 정상적으로 입력됐을 경우에는 COMMIT 명령 
6. 에러가 발생하면 모든 트랜잭션을 취소하고 'ERROR 발생'을 출력.

 

프로시저 작성 시 주의할 점

1. PL/SQL 및 T-SQL 에는 다양한 변수가 있다. 예제에 나온 cnt 라는 변수를 SCALAR 변수라고 한다. SCALAR 변수는 사용자의 임시 데이터를 하나만 저장할 수 있는 변수이며 거의 모든 형태의 데이터 유형을 지정할 수 있다. 
2. PL/SQL 에서 사용하는 SQL 구문 중 SELECT 문장은 결과값이 반드시 있어야 하고, 결과값은 하나여야 한다. 조회결과가 없거나 하나가 아니면 에러가 발생한다. (T-SQL은 결과값이 없어도 에러가 발생하지 않음)
3. T-SQL 을 비롯한 일반적인 대입 연산자는 '=' 를 사용하지만 PL/SQL 에서는 ':=' 를 사용한다. 
4. 에러를 처리하는 EXCEPTION 에는 WHEN ~ TEHN 절을 사용하여 에러의 종류별로 적절히 처리한다. 

오라클 실행결과

[실행 결과] 

Oracle SQL> SELECT * FROM DEPT; -----------------① 
DEPTNO   DNAME      LOC 
------- -------    --------- 
10      ACCOUNTING NEW YORK 
20      RESEARCH   DALLAS 
30      SALES      CHICAGO 
40      OPERATIONS BOSTON 

SQL> variable rslt varchar2(30); -----------------② 

SQL> EXECUTE p_DEPT_insert(10,'dev','seoul',:rslt); -----------------③ 
PL/SQL 처리가 정상적으로 완료되었다. 

SQL> print rslt; -----------------④ 
RSLT 
-------------------------------- 
이미 등록된 부서번호이다 

SQL> EXECUTE p_DEPT_insert(50,'NewDev','seoul',:rslt); ----------------⑤ PL/SQL 처리가 정상적으로 완료되었다. 

SQL> print rslt; ----------------⑥ 
RSLT 
-------------------------------- 입력 완료!! 

SQL> SELECT * FROM DEPT; ----------------⑦ 
DEPTNO  DNAME     LOC 
------ --------   --------- 
10     ACCOUNTING  NEW YORK 
20     RESEARCH    DALLAS 
30     SALES       CHICAGO 
40     OPERATIONS  BOSTON 
50     NewDev SEOUL 
5개의 행이 선택되었다.

1. DEPT 테이블을 조회하면 총 4개 행의 결과가 출력된다. 
2. procedure 를 실행할 결과값을 받을 변수를 선언한다 (BIND 변수)
3. 존재하는 DEPTNO(10)을 가지고 procedure 를 실행한다. 
4. DEPTNO 가 10 인 부서는 이미 존재하기 때문에 rslt를 출력해보면 '이미 등록된 부서번호이다' 라고 출력된다.
5. DEPTNO(50) 을 입력한다.
6. rslt 를 출력해보면 '입력완료' 라고 나온다.
7. DEPT 테이블을 조회해보면 DEPTNO가 50인 데이터가 저장되었다.

sql server 생략

 

 

User-Defined Function 의 생성과 활용

User Defined Function 은 procedure 처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다. 

SUM, SUBSTR, NVL등의 함수는 벤더에서 미리 만들어둔 내장 함수이고, 사용자가 별도의 함수를 만들 수도 있다. 

Function 이 procedure 와 다른 점은 RETURN 을 사용해서 하나의 값을 반드시 되돌려 줘야 한다는 것이다. 

즉, Function 은 특정 작업을 수앻아고 반드시 수행 결과 값을 리턴한다. 

 

8월 경기결과와 두 팀간의 점수차를 ABS 함수를 이용해서 절대값으로 출력한다. 

오라클

SELECT   SCHE_DATE 경기일자, 
         HOMETEAM_ID || '-' || AWAYTEAM_ID 팀들, 
         HOME_SCORE  || '-' || AWAY_SCORE,
         ABS(HOME_SCORE - AWAY_SCORE) 점수차 
FROM     SCHEDULE
WHERE    GUBUN = 'Y'
AND      SCHE_DATE BETWEEN '20120801' AND '20120831'
ORDER BY SCHE_DATE ; 

 

위에서 사용한 ABS 함수를 만드는데, INPUT 값으로 숫자만 들어온다고 가정한다. 

CERATE 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 ;                                ---- (4) 
 RETURN v_return ;
END ;
/

UTIL_ABS function 의 처리과정

1. 숫자 값을 입력 받는다. 예제에서는 숫자 값만 입력된다고 가정한다. 
2. 리턴 값을 받아 줄 변수인 v_return 을 선언한다. 
3. 입력값이 음수이면 -1 을 곱하여 v_return 변수에 대입한다. 
4. v_return 변수를 리턴한다. 

 

Trigger 생성과 활용

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

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

 

ORDER_LIST에 주문 정보가 입력되면

주문 정보의 주문 일자(ORDER_LIST.ORDER_DATE) 와 

주문 상품(ORDER_LIST.PRODUCT) 을 기준으로 

판매 집계 테이블(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) 트리거를 선언한다. 

     CREATE OR REPLACE trigger SUMMARY_SALES : 트리거 선언문

     AFTER  INSERT : 레코드가 입력된 후 트리거 발생
     ON     ORDER_LIST : 오더 리스트 테이블에 트리거 설정
     FOR    EACH ROW : 각 로우마다 트리거 적용

(2) o_date(주문일자), o_prod(주문상품) 값을 저장할 수 있는 변수를 선언하고, 신규로 입력된 데이터를 저장한다. 

     : NEW 는 신규로 입력된 레코드를 가지고 있는 구조체 

     : OLD 는 수정, 삭데되기 전의 레코드를 가지고 있는 구조체

(3) 먼저 입력된 주문 내역의 주문 일자와 주문 상품을 기준으로 SALES_PER_DATE 테이블에 업데이트 한다. 

(4) 처리 결과가 SQL%NOTFOUND 면 해당 주문 일자의 주문 상품 실적이 존재하지 않으며, SALES_PER_DATE 테이블에 새로운 집계 데이터를 입력한다. 

 

 

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

프로시저는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK 같은 트랜잭션 종료 명령어를 사용할 수 있지만,

트리거는 BEGIN ~ END 절 내에 사용할 수 없다.

프로시저	트리거
CREATE procedure 문법 사용	CREATE Trigger 문법 사용
EXCUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 사용안됨

 

 

 

 

 

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