[SQLD : Ⅳ. SQL 활용] 8-2. 절차형 SQL

* User Defined Function 생성과 활용

 -  SUM, SUBSTR, NVL 등의 함수는 벤더에서 미리를 만들 수도 있다

 -  Function이 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;

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

Oracle CREATE OR REPLACE Function UTIL_ABS (v_input in number) ---------------- ① 
       return NUMBER IS v_return number := 0; ---------------- ② 
       BEGIN if v_input < 0 then ---------------- ③ 
       v_return := v_input * -1; else v_return := v_input; end if; RETURN v_return; ---------------- ④
END; /

SQL Server CREATE Function dbo.UTIL_ABS (@v_input int) ---------------- ① 
           RETURNS int AS BEGIN DECLARE @v_return int ---------------- ② 
           SET @v_return=0 IF @v_input < 0 ---------------- ③
           SET @v_return = @v_input * -1 ELSE SET @v_return = @v_input RETURN @v_return; ---------------- ④
END

  ① 숫자 값을 입력 받는다. 예제에서는 숫자 값만 입력된다고 가정한다.

  ② 리턴 값을 받아 줄 변수인 v_return를 선언한다.

  ③ 입력 값이 음수이면 -1을 곱하여 v_return 변수에 대입한다.

  ④ v_return 변수를 리턴한다.

  [예제] 함수를 이용하여 앞의 SQL을 수정하여 실행한다.

Oracle SELECT SCHE_DATE 경기일자, HOMETEAM_ID || ' - ' || AWAYTEAM_ID 팀들, 
              HOME_SCORE || ' - ' || AWAY_SCORE SCORE, UTIL_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, dbo.UTIL_ABS(HOME_SCORE - AWAY_SCORE) AS 점수차 
           FROM SCHEDULE 
           WHERE GUBUN = 'Y' AND SCHE_DATE BETWEEN '20120801' AND '20120831' 
           ORDER BY SCHE_DATE;

 

* Trigger 생성과 활용

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

 -  Trigger는 테이블과 뷰, 데이터베이스 작업을 대상으로 정의 가능

  [예제] 트리거(Trigger)를 사용하여 주문한 건이 입력될 때마다,

          일자별 상품별로 판매수량과 판매금액을 집계하여 집계자료를 보관하도록 한다. 먼저 관련 테이블을 생성

Oracle CREATE TABLE ORDER_LIST ( ORDER_DATE CHAR(8) NOT NULL, PRODUCT VARCHAR2(10) NOT NULL, 
                                  QTY NUMBER NOT NULL, AMOUNT NUMBER NOT NULL); 
       CREATE TABLE SALES_PER_DATE ( SALE_DATE CHAR(8) NOT NULL, PRODUCT VARCHAR2(10) NOT NULL,
                                     QTY NUMBER NOT NULL, AMOUNT NUMBER NOT NULL);

SQL Server CREATE TABLE ORDER_LIST ( ORDER_DATE CHAR(8) NOT NULL, PRODUCT VARCHAR(10) NOT NULL,
                                     QTY INT NOT NULL, AMOUNT INT NOT NULL); 
           CREATE TABLE SALES_PER_DATE ( SALE_DATE CHAR(8) NOT NULL, 
                                         PRODUCT VARCHAR(10) NOT NULL, 
                                         QTY INT NOT NULL, AMOUNT INT NOT NULL);

  [예제] Trigger를 작성

          Trigger의 역할은 ORDER_LIST에 주문 정보가 입력되면

          주문 정보의 주문 일자(ORDER_LIST.ORDER_DATE)와 주문 상품(ORDER_LIST.PRODUCT)을 기준으로

          판매 집계 테이블(SALES_PER_DATE)에 해당 주문 일자의 주문 상품 레코드가 존재하면

          판매 수량과 판매 금액을 더하고 존재하지 않으면 새로운 레코드를 입력한다.

Oracle CREATE OR REPLACE Trigger SUMMARY_SALES ---------------- ①
AFTER INSERT ON ORDER_LIST FOR EACH ROW DECLARE ---------------- ② 
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 ---------------- ③ 
SET qty = qty + :NEW.qty, amount = amount + :NEW.amount WHERE sale_date = o_date AND product = o_prod; 
if SQL%NOTFOUND then ---------------- ④ 
INSERT INTO SALES_PER_DATE VALUES(o_date, o_prod, :NEW.qty, :NEW.amount); 
end if; 
END; /

  ① Trigger를 선언한다.

      CREATE OR REPLACE Trigger SUMMARY_SALES : Trigger 선언문

      AFTER INSERT : 레코드가 입력이 된 후 Trigger 발생

      ON ORDER_LIST : ORDER_LIST 테이블에 Trigger 설정

      FOR EACH ROW : 각 ROW마다 Trigger 적용

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

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

      : OLD는 수정, 삭제되기 전의 레코드를 가지고 있는 구조체 [표 Ⅱ-2-17] 참조

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

  ④ 처리 결과가 SQL%NOTFOUND이면 해당 주문 일자의 주문 상품 실적이 존재하지 않으며,

      SALES_ PER_DATE 테이블에 새로운 집계 데이터를 입력한다.

 

SQL Server CREATE Trigger dbo.SUMMARY_SALES ---------------- ① 
ON ORDER_LIST AFTER INSERT AS 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 ---------------- ② 
UPDATE SALES_PER_DATE ---------------- ③ 
SET qty = qty + @qty, amount = amount + @amount WHERE sale_date = @o_date AND product = @o_prod; 
IF @@ROWCOUNT=0 ---------------- ④ 
INSERT INTO SALES_PER_DATE VALUES(@o_date, @o_prod, @qty, @amount) END

  ① Trigger를 선언한다.

      CREATE Trigger SUMMARY_SALES : Trigger 선언문

      ON ORDER_LIST : ORDER_LIST 테이블에 Trigger 설정

      AFTER INSERT : 레코드가 입력이 된 후 Trigger 발생

  ② o_date(주문일자), o_prod(주문상품), qty(수량), amount(금액) 값을 저장할 변수를 선언하고,

     신규로 입력된 데이터를 저장한다.

      inserted는 신규로 입력된 레코드의 정보를 가지고 있는 구조체

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

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

  ④ 처리 결과가 0건이면 해당 주문 일자의 주문 상품 실적이 존재하지 않으며,

      SALES_PER_DATE 테이블에 새로운 집계 데이터를 입력한다.

  [예제] ORDER_LIST 테이블에 주문 정보를 입력한다.

Oracle SQL> SELECT * FROM ORDER_LIST; 선택된 레코드가 없다. 
SQL> SELECT * FROM SALES_PER_DATE; 선택된 레코드가 없다. 
SQL> INSERT INTO ORDER_LIST VALUES('20120901', 'MONOPACK', 10, 300000); 1개의 행이 만들어졌다. 
SQL> COMMIT; 커밋이 완료되었다.

SQL Server SELECT * 
FROM ORDER_LIST; 선택된 레코드가 없다. 
SELECT * FROM SALES_PER_DATE; 선택된 레코드가 없다. 
INSERT INTO ORDER_LIST VALUES('20120901', 'MONOPACK', 10, 300000); 1개의 행이 만들어졌다.

  주문정보와 판매 집계

SQL> SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT QTY AMOUNT
--------- -------- -------- ------- 
20120901 MONOPACK 10 300000 

SQL> SELECT * FROM SALES_PER_DATE;
SALE_DATG PRODUCT QTY AMOUNT
-------- -------- -------- -------- 
20120901 MONOPACK 10 300000

  [예제] 다시 한 번 같은 데이터를 입력해보고, 두 테이블의 데이터를 확인한다.

Oracle SQL> INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',20,600000); 1개의 행이 만들어졌다. 

SQL> COMMIT; 커밋이 완료되었다. 
SQL> SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT QTY AMOUNT 
--------- ---------- ------ ------- 
20120901 MONOPACK 10 300000 
20120901 MONOPACK 20 600000 

SQL> SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT QTY AMOUNT 
-------- --------- ----- ------- 
20120901 MONOPACK 30 900000

SQL Server INSERT INTO ORDER_LIST VALUES('20120901','MONOPACK',20,600000); 1개의 행이 만들어졌다.
SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT QTY AMOUNT 
--------- --------- ----- -------- 
20120901 MONOPACK 10 300000 
20120901 MONOPACK 20 600000 

SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT QTY AMOUNT -------- --------- ---- -------- 20120901 MONOPACK 30 900000

  [예제] 이번에는 다른 상품으로 주문 데이터를 입력한 후 두 테이블의 결과를 조회해 보고 트랜잭션을 ROLLBACK

          판매 데이터의 입력 취소가 일어나면,

          주문 정보 테이블과 판매 집계 테이블에 동시에 입력(수정) 취소가 일어나는지 확인해본다.

Oracle SQL> INSERT INTO ORDER_LIST VALUES('20120901','MULTIPACK',10,300000); 1개의 행이 만들어졌다.
SQL> SELECT * FROM ORDER_LIST; 
ORDER_DA PRODUCT QTY AMOUNT 
-------- -------- ------ -------
20120901 MONOPACK 10 300000 
20120901 MONOPACK 20 600000 
20120901 MULTIPACK 10 300000 

SQL> SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT QTY AMOUNT 
-------- -------- ------ ------- 
20120901 MONOPACK 30 900000 
20120901 MULTIPACK 10 300000 

SQL> ROLLBACK; 롤백이 완료되었다. 
SQL> SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT QTY AMOUNT 
-------- -------- ------ ------- 
20120901 MONOPACK 10 300000 
20120901 MONOPACK 20 600000 

SQL> SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT QTY AMOUNT
-------- --------- ------ ------- 
20120901 MONOPACK 30 900000

 

SQL Server BEGIN TRAN INSERT INTO ORDER_LIST VALUES('20120901','MULTIPACK',10,300000); 
1개의 행이 만들어졌다. 
SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT QTY AMOUNT 
--------- --------- ------ ------- 
20120901 MONOPACK 10 300000 
20120901 MONOPACK 20 600000
20120901 MULTIPACK 10 300000

SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT QTY AMOUNT 
-------- --------- ------ ------- 
20120901 MONOPACK 30 900000 
20120901 MULTIPACK 10 300000 

ROLLBACK; 롤백이 완료되었다. 
SELECT * FROM ORDER_LIST; 
ORDER_DATG PRODUCT QTY AMOUNT 
--------- -------- ------ ------- 
20120901 MONOPACK 10 300000 
20120901 MONOPACK 20 600000 

SELECT * FROM SALES_PER_DATE; 
SALE_DATG PRODUCT QTY AMOUNT 
-------- -------- ------ ------- 
20120901 MONOPACK 30 900000

 ROLLBACK 시 하나의 트랜잭션이 취소, Trigger로 입력된 정보까지 한 트랜잭션으로 인식, 두 테이블 모두 입력 취소

 Trigger는 데이터베이스에 의해 자동 호출, INSERT, UPDATE, DELETE 문과 한 트랜잭션 안에서 일어나는 일련의 작업

 

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