PowerPoint 프레젠테이션

Transcription

1 U N I T 24 스토어드프로그램 이장에서는 DB2가제공하는 Stored Function 및 Stored Procedure, Trigger 을통한 Application Logic 작성에대한내용을소개합니다. 441

2 DB2 9.7 개발자가이드 Developer Edition Stored Program IBM Data Studio Developer를통해 Stored Program 생성 IBM Data Studio Developer를통해 UDF 생성 IBM Data Studio Developer를통해 UDF 생성 - 예 IBM Data Studio Developer를통해 Table UDF 생성 Stored Procedure 작성 Trigger 작성모듈 (Module) 작성 442

3 Stored Program Stored Program 은 UDF, Trigger, Stored Procedure 등의작성을통하여 DBMS 내에응용프로그램로직을저장하고동작하도록하는기능입니다. 1 Stored Program 은 DBMS Application Object 를사용함으로써응용프로그램코드의단순화, 성능개선, 코드의재사용향상및이식성을증대할수있습니다. Tip Data Studio Developer는 9.7에서는 Optim Development Studio로변경되었습니다. 2 3 일반적은 Editor 를이용하여작성및생성할수있습니다. 그러나 IBM Data Studio Developer 를사용하면프로그램디버깅이가능하여, 작성시매우편리합니다. Data Studio Developer 시작방법은아래와같습니다. Windows 에서 시작 프로그램 IBM Software Development Platform IBM Data Studio Developer IBM Data Studio Developer 를수행합니다. 443 DB2 9.7 개발자가이드

4 02 Stored Program 생성 IBM Data Studio Developer 실행하여 Stored Program 을생성하는절차를소개합니다. 1 데이터베이스애연결합니다. IBM 에서 연결 새연결 를실행합니다. 연결하려는 DB 이름과호스트및포트번호를그림과기술합니다. Tip 각프로젝트는 DB connecti on 이필요합니다. Figure 2402A 데이터베이스연결 Figure 2402B 데이터개발프로젝트를실행 UNIT 24 스토어드프로그램 444

5 02 Stored Program 생성 IBM Data Studio Developer 실행하여 Stored Program 을생성하는절차를소개합니다. Tip 연결테스트 가성공하면다음과같은팝업이뜹니다. 2 스토어드프로시저작성을선택합니다. 데이터개발프로젝트에서스토어드프로시저에서새로작성을실행합니다. 이름과언어및개발하려는상황에맞게설정후다음버튼을클릭합니다. Figure 2402C IBM Data Studio Developer 스토어드프로시저작성 Figure 2402D IBM Data Studio Developer 스토어드프로시저작성 445 DB2 9.7 개발자가이드

6 02 Stored Program 생성 IBM Data Studio Developer 실행하여 Stored Program 을생성하는절차를소개합니다. 3 SQL 작성을클릭하여스토어드프로시저를작성합니다. Figure 2402E IBM Data Studio Developer 스토어드프로시저작성 UNIT 24 스토어드프로그램 446

7 UDF 생성 IBM Data Studio Developer 를실행하여 User-Defined Function (UDF) 을작성하는방법입니다. Tip SQL UDF를단순한과정을거쳐생성합니다. 다음예는세금을계산하는간단한 TAX function을생성합니다. 1 UDF 생성하기 Figure 24A IBM Data Studio Developer UDF 새로작성 -1 다음그림에서완료버튼을클릭하고아래와같이내용을수정합니다. Figure 24B IBM Data Studio Developer UDF 새로작성 DB2 9.7 개발자가이드

8 UDF 생성 IBM Data Studio Developer 를실행하여 User-Defined Function (UDF) 을작성하는방법입니다. Tip CREATE FUNCTION 다음에는함수이름이반드시기술되어야하며, 괄호내부에매개변수와데이터타입을여러개지정할수있습니다. 2 UDF 의필수구문및선택구문구성 CREATE FUNCTION 문장은몇가지필수구문및선택구문으로구성됩니다. 예제에서는 p_amount DECIMAL(9,2) 하나의 Parameter 를정의하였습니다. Parameter p_amount 는판매금액을나타냅니다. Application 에서함수를 call 하면, 함수는판매금액에대한세금을반환합니다. RETURN 구문은 DECIMAL(9,2) 반환을기술합니다. 함수본문은 BEGIN ATOMIC END SQL block 내부에기술합니다. 함수는다음한문장을기술합니다. IBM Data Studio Developer 에서 전개 버튼을눌러실행합니다. Figure 24C IBM Data Studio Developer 새로작성 - 매개변수지정 위와같이. 입력변수 p_amount 에대한입력창이팝업됩니다. 임의의값을입력하고 확인 버튼을눌러실행합니다. 함수가성공적으로수행되면, 다음과같이표시됩니다. Figure 24D IBM Data Studio Developer UDF 새로작성 - 출력보기 UNIT 24 스토어드프로그램 448

9 UDF 생성 IBM Data Studio Developer 를실행하여 User-Defined Function (UDF) 을작성하는방법입니다. 빠른실행을위해, 명령창에서다음과같이 Query 를수행합니다. SELECT product_id, retail_price, KJINST1.TAX(retail_price) AS tax FROM KJINST1.product; Figure 24E IBM Data Studio Developer UDF 새로작성 - 출력보기 Tip TAX 함수의 Qualifier에유의하세요. 함수앞의스키마를제거하기위해서는 DB2 CURRENT PATH 를수정하세요. 이는 PATH와같은환경변수역할을합니다. DB2 세션에서함수에대한경로를결정하는데사용됩니다. 명령창에서다음 Query 을통해 current path 를확인할수있습니다. VALUES CURRENT PATH; 만일 db2admin 계정을 DB 에 Connect 하면, 다음과같이결과가표시됩니다. "SYSIBM","SYSFUN","SYSPROC", DB2ADMIN" 결과에서보듯이, CURRENT PATH 는스키마리스트입니다. 뒤이어, 접속사용자스키마가붙습니다. 함수이름앞에스키마가정의되어있지않으면, CURRENT PATH 에따라함수를결정합니다. 스키마순서에따라, 실행할함수를검색하게됩니다. CURRENT PATH 를변경하기위해, SET CURRENT PATH 를사용합니다. SET CURRENT PATH = CURRENT PATH,"KJINST1" VALUES CURRENT PATH "SYSIBM","SYSFUN","SYSPROC","DB2ADMIN","KJINST1" SET CURRENT PATH = SYSTEM PATH,"KJINST1" VALUES CURRENT PATH "SYSIBM","SYSFUN","SYSPROC","KJINST1" DB2 9.7 개발자가이드

10 UDF 생성 IBM Data Studio Developer 를실행하여 User-Defined Function (UDF) 을작성하는방법입니다. Tip 디폴트 SQL 경로 ( 또는사용자스키마앞에 SYSIBM이있는 SQL 경로 ) 를사용하고스키마에새 SYSIBM 함수와이름이같은기존함수가있는경우, SYSIBM 함수가대신사용됩니다. 이상황은일반적으로성능을향상시키지만예기치않은동작의원인이될수있습니다. SET CURRENT PATH = USER,"KJINST1" VALUES CURRENT PATH "DB2ADMIN","KJINST1" 3 SET CURRENT PATH = "DB2ADMIN","SYSIBM","SYSFUN","SYSPROC","KJINST1" VALUES CURRENT PATH "DB2ADMIN","SYSIBM","SYSFUN","SYSPROC","KJINST1" CURRENT PATH 에스키마 KJINST1 를추가합니다. CURRENT PATH 를 SYSTEM PATH 와 KJINST1 로변경합니다. 현재의 USER ID 와 KJINST1 를포함하여 CURRENT PATH 를변경합니다. CURRENT PATH 에모든 SCHMA 를기술하여적용합니다. UNIT 24 스토어드프로그램 450

11 04 UDF 생성 - 예 IBM Data Studio Developer 를실행하여 UDF 를작성하는예제입니다. 1 복잡한 Query 실행하기 다음은특정기간동안에판매금액에대한이익률을계산하는 UDF 를작성합니다. 해당정보가다음과같이세개의 Table 에분산되어있습니다. 1) PRODUCT_PURCHASE : 각제품별판매가격정보 2) PRODUCT : 제품별비용 3) SALES : 판매에대한일자 / 시간관리정보 특정기간동안의이익률을계산하려면, 매번세개의 Table 을 Join 하여야합니다. 이를단순화하기위하여, PRODUCT ID, 시작일자및종료일자를입력파라미터를갖는 UDF 를작성합니다. Table Layout CREATE TABLE KJINST1.PRODUCT ( PRODUCT_ID INT NOT NULL, DESCRIPTION VARCHAR(40) NOT NULL, COST DECIMAL(7,2) NOT NULL, RETAIL_PRICE DECIMAL(7,2) NOT NULL, INVENTORY INT NOT NULL, MINIMUM_INVENTORY INT NOT NULL WITH DEFAULT 0, PRIMARY KEY (PRODUCT_ID) ); CREATE TABLE KJINST1.CUSTOMER ( CUSTOMER_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0 INCREMENT BY 1), CREDIT_CARD CHAR(16), EXPIRY_DATE CHAR(4), LASTNAME VARCHAR(28), FIRSTNAME VARCHAR(28), ADDRESS VARCHAR(300), ZIP_CODE CHAR(6), PHONE CHAR(10), PRIMARY KEY (CUSTOMER_ID) ); CREATE TABLE KJINST1.SALES ( SALES_TRANSACTION_ID INT GENERATED ALWAYS AS IDENTITY (START WITH 0 INCREMENT BY 1), CUSTOMER_ID INT REFERENCES KJINST1.CUSTOMER(CUSTOMER_ID), SUB_TOTAL DECIMAL(7,2), TAX DECIMAL(7,2), TYPE INT NOT NULL, TRANSACTION_TIMESTAMP TIMESTAMP, PRIMARY KEY (SALES_TRANSACTION_ID) ); 451 DB2 9.7 개발자가이드

12 04 UDF 생성 - 예 IBM Data Studio Developer 를실행하여 UDF 를작성하는예제입니다. CREATE TABLE KJINST1.PRODUCT_PURCHASES ( SALES_TRANSACTION_ID INT REFERENCES KJINST1.SALES(SALES_TRANSACTION_ID) ON DELETE CASCADE, PRODUCT_ID INT REFERENCES KJINST1.PRODUCT(PRODUCT_ID), PRICE DECIMAL(7,2) NOT NULL, QTY INT NOT NULL ); CREATE TABLE KJINST1.AUDIT_STOCKJINST1HK ( STAFF VARCHAR(50), CHECKTIME TIMESTAMP ); PROD_PROFIT UDF 생성하기 CREATE FUNCTION KJINST1.PROD_PROFIT ( p_pid INTEGER, p_sdate DATE, p_edate DATE ) RETURNS DECIMAL(9,2) SQL UDF (Scalar) F1: BEGIN ATOMIC DECLARE v_retail_price DECIMAL(9,2); DECLARE v_cost DECIMAL(9,2); DECLARE v_err VARCHAR(70); SET (v_retail_price, v_cost) = ( SELECT SUM(retail_price), SUM(cost) FROM KJINST1.product p, KJINST1.product_purchases pp, KJINST1.sales s WHERE p.product_id = pp.product_id AND pp.sales_transaction_id = s.sales_transaction_id AND p.product_id = p_pid AND DATE(s.transaction_timestamp) BETWEEN p_sdate AND p_edate ); SET v_err = 'Error: product ID ' CHAR(p_pid) ' was not found.'; IF ( v_retail_price IS NULL OR v_cost IS NULL ) THEN SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT = v_err; END IF; RETURN ( v_retail_price - v_cost ) / v_cost * 100; END UNIT 24 스토어드프로그램 452

13 04 UDF 생성 - 예 IBM Data Studio Developer 를실행하여 UDF 를작성하는예제입니다. 1) UDF KJINST1.PROD_PROFIT 를생성합니다. 2) PROD_PROFIT에세개의 INPUT Parameter를지정합니다. p_pid : Product ID p_sdate : 시작일자조건 p_edate : 종료일자조건 3) DECIMAL(9,2) 형태의이익률을반환합니다. 4) UDF KJINST1.TAX 는단문으로이루어진함수입니다. PROD_PROFIT 처럼복문으로구성된 UDF 는 BEGIN ATOMIC(4) 와 END(11) 사이에함수 Logic 을기술합니다. 5) 함수내에서사용될변수를기술합니다. v_retail_price : 판매금액 v_cost : 판매비용 v_err : PRODUCT ID가없을경우의 Error Message 6) PRODUCT, PRODUCT_PURCHASES와 SALES를 Join하여판매금액과비용을산 정한다. SET 문장을통해값을변수에 Assign한다. 7) SALES Table 의 transaction_timestamp 가 TIMESTAMP 형태로저장되어있습니다. 이를 p_sdate, p_edate 와비교하기위하여 DATE() 함수를사용하여 DATE 값으로변환한다. 8) PRODUCT ID 가없을경우 Error Message 를생성합니다. 9) 만일, PRODUCT ID 가없을경우, UDF 는 Error 를발생한다. 이후, Function 수행을종료하고, 호출한 Application 에 Error 를반환한다. v_err 를 VARCHAR(70) 으로정의하였습니다. SIGNAL SQLSTATE 의 error text 의한계가 70 자입니다. 만일, Message 가한계를초과하면, 경고없이절삭됩니다. 10) 판매금액과비용을통해이익률을계산하여반환합니다. IBM Data Studio Developer 를통해빌드후, 실행합니다. Figure 2404A IBM Data Studio Developer UDF 실행하기 DB2 9.7 개발자가이드

14 04 UDF 생성 - 예 IBM Data Studio Developer 를실행하여 UDF 를작성하는예제입니다. Tip SP를명령창에서실행하고자할때는아래와같은방법으로사용합니다. >db2 call KJINST1.PROD_PROFIT (111, , ) PROD_PROFIT 를실행 Figure 2404B IBM Data Studio Developer UDF 실행하기 -2 실행결과화면 & 잘못된 Product ID 를입력했을때의화면 Figure 2404C IBM Data Studio Developer UDF 실행하기 -2 UNIT 24 스토어드프로그램 454

15 05 Table UDF 작성 IBM Data Studio Developer 를실행하여 Table UDF 를작성하는예제입니다. 1 Table UDF Query 의 FROM 절에서사용되며, Table 형 xo 의 Row 를반환합니다. 다음은입고가필요한모든 PRODUCT 를반환하는 Table 함수입니다. 누가, 언제 STOCK 를점검했는지 LOG 을위해 AUDIT_STOCKCHK Table 을사용합니다. STOCKCHK UDF 생성하기 CREATE FUNCTION KJINST1..STOCKCHK() RETURNS TABLE ( PRODUCT_ID INTEGER, DESCRIPTION VARCHAR(40), INVENTORY INTEGER, MINIMUM_INVENTORY INTEGER ) MODIFIES SQL DATA SQL UDF (TABLE) F1: BEGIN ATOMIC INSERT INTO KJINST1.AUDIT_STOCKCHK VALUES (USER, CURRENT TIMESTAMP); END RETURN SELECT PRODUCT_ID, DESCRIPTION, INVENTORY, MINIMUM_INVENTORY FROM KJINST1.PRODUCT WHERE INVENTORY < MINIMUM_INVENTORY; 1) UDF KJINST1.STOCCHK를입력 Parameter없이생성합니다. 2) RETURN절에 Table function이반환할 Column과 Type을정의합니다. 3) Table function은기본적으로실행위주로수행됩니다. MODIFIES SQL DATA 를기술하여, Function내에서 INSERT, UPDATE 및 DLETE를할수있도록합니다. 4) 누가 Call을했는지를관리하기위해, AUDIT_STOCKCHK Table에데이터를입력합니다. Special Register USER : DB에접속한 current user ID CURRENT TIMESTAMP : 현재시간 455 DB2 9.7 개발자가이드

16 05 Table UDF 작성 IBM Data Studio Developer 를실행하여 Table UDF 를작성하는예제입니다. Figure 2405A IBM Data Studio Developer Table UDF STOCKCHK UDF 수행 SELECT * FROM TABLE (KJINST1.STOCKCHK()) AS STOCKCHK; Figure 2405B IBM Data Studio Developer Table UDF UNIT 24 스토어드프로그램 456

17 06 Stored Procedure 작성 IBM Data Studio Developer 를실행하여 Stored Procedure 를생성하는예입니다. 1 Stored Procedure 이해하기 SP 는 DB 내의데이터를 Access 하거나수정하는하나이상의 SQL 문장으로구성된 DB Object 입니다. SP 는 DB2 의제어하에수행되고관리됩니다. SP 는 SQL PL, C/C++, Java, COBOL, CLR 및 OLE 를사용하여작성됩니다. SQL Procedure 가간단하기때문에주로사용됩니다. SQL Stored Procedure 의장점 Code 의재사용을통한 Business Logic 통합 보안 Level 강화 성능개선 SP 는 DB 에저장되며, SQL 문장과 Business Logic 을 encapsulate 합니다. 적절한권한을가진모든 Application Client 에서 SP 를호출할수있습니다. 또한 Code 의재사용률을증대합니다. 또한, SP 내의 Business Logic 변경이관련된모든 Application 또는 Client 와독립적이므로관리비용을절감할수있습니다. 사용자는 SP 를통해 Access 하는 Table 또는 View 에대해권한이필요치않습니다. 다만 SP 를호출할수있는권한만있으면됩니다. 이를통해, 사용자의예상치않은접근에대한통제를할수있습니다. SP 는 DBMS 내에 SQL 과 Business Logic 을가지고수행합니다. 즉, Application 과 DB 사이의 N/W Traffic 을줄일수있습니다. 또한성능을위해 SQL 이컴파일됩니다. Tip Table에 Column을추가하기위해, ALTER TABLE 문장을사용하거나제어센터 ( Control Center ) 를사용할수있습니다. 2 Stored Procedure 개발하기 다음은 SP 를통해할수있는몇가지예를소개합니다. 이를위해 DB 내의 table 을변경합니다. 예제에서는, Internet On-Line 주문을위한 Web site 를 SP 를소개합니다. On-Line 주문을위해, SALES 와 PRODUCT_PURCHASES table 을 POS (-of- Sale) 시스템과같이변경합니다. 주문상태를관리하기위해, Column 을추가합니다. SALES Table 의 ORDER_STATUS column : Column 에 Check Constraints ( N, C, P & I ) 를추가합니다. - N : New Order - C : 주문완료 - P : 부분적주문완료 - I : 고객정보부족 PRODCUT_PURCHASES Table 의 STATUS column : Column 에 Check Constraints ( N, C & O ) 를추가합니다. - N : New Order - C : 주문완료 - O : 재고부족 457 DB2 9.7 개발자가이드

18 06 Stored Procedure 작성 IBM Data Studio Developer 를실행하여 Stored Procedure 를생성하는예입니다. Stored procedure (SP) 생성 다음은새로운계약에관해정보를조회하는 SP 를 IBM Data Studio Developer 를통해생성하는과정을보여줍니다. Figure 2406A IBM Data Studio Developer 에서 SP 구현하기 -1 Procedure PROCEDD_NEWORDER 를 Parameter 없이정의합니다. Procedure 본문은 BEGIN 과 END 블록사이에기술합니다. 다음의 Query 결과를처리하기위해 FOR loop 를정의합니다. FOR Loop 내에, 새로운주문에대한처리를위해 Business Logic 을기술합니다. SELECT SALES_TRANSACTION_ID, CUSTOMER_ID FROM SALES WHERE ORDER_STATUS = 'N' Stored Procedure PROCESS_NEW 생성 Figure 2406B IBM Data Studio Developer 에서 SP 구현하기 -2 IBM Data Studio Developer 에서 PROCESS_NEW 를 Build 합니다. UNIT 24 스토어드프로그램 458

19 06 Stored Procedure 작성 IBM Data Studio Developer 를실행하여 Stored Procedure 를생성하는예입니다. 이제까지, 단순 SP 를생성하는과정을살펴보았습니다. 예제에서는단순한 SP 를작성하였습니다. 다음은 stored procedure 의강력한기능을통해새로운 SP 를전개합니다. PROCESS_NEWORDER 에서 Web application 에입력한새로운주문을처리합니다. 이제까지는새로운주문을검색하는 Logic 을작성하였습니다. FOR loop 내의각주문에다음과같은처리를합니다. - 제품을주문한고객정보를점검합니다. - 만일고객의주소나신용카드정도가불충분하면처리하지않습니다. - 검증된주문은처리를계속합니다. 수정된 KJINST1.PROCESS_NEWORDER CREATE PROCEDURE KJINST1.PROCESS_NEWORDER() SQL STORED PROCEDURE P1: BEGIN DECLARE V_ORDERNO INTEGER; DECLARE V_CUSTID INTEGER; DECLARE V_CREDITCARD CHAR(16); DECLARE V_EXPIRYDATE CHAR(4); DECLARE V_ADDRESS VARCHAR(300); DECLARE V_ERR VARCHAR(70); FOR C1 AS SELECT SALES_TRANSACTION_ID, CUSTOMER_ID FROM KJINST1.SALES WHERE ORDER_STATUS = 'N' DO SELECT CREDIT_CARD, EXPIRY_DATE, ADDRESS INTO V_CREDITCARD, V_EXPIRYDATE, V_ADDRESS FROM KJINST1.CUSTOMER WHERE CUSTOMER_ID = C1.CUSTOMER_ID; IF (V_CREDITCARD IS NULL AND V_EXPIRYDATE IS NULL) OR (V_ADDRESS IS NULL) THEN SET V_ERR = 'THE CUSTOMER INFORMATION IS' ' NOT COMPLETE TO PROCESS THE ORDER NO ' CHAR(V_ORDERNO); SIGNAL SQLSTATE'80000' SET MESSAGE_TEXT = V_ERR; 4 END IF; --CALL ANOTHER SP TO PROCESS THE VALID ORDERS SET V_ORDERNO = C1.SALES_TRANSACTION_ID; CALL KJINST1.FILLORDER(V_ORDERNO, V_CUSTID); 5 END P1 END FOR; 459 DB2 9.7 개발자가이드

20 06 Stored Procedure 작성 IBM Data Studio Developer 를실행하여 Stored Procedure 를생성하는예입니다. 1) Query 결과를처리하기위한 FOR Loop 정의 2) 고객정보에해당하는카드정보및주소확인하는 Process 3) 고객정보의정합성점검 4) 필요한정보가부적하면 Error 처리 : SQLSTATE ) 주문번호및고객정보를입력변수로 SP FILLORDER 를 CALL 힙니다. 수정된 KJINST1.PROCESS_NEWORDER CREATE PROCEDURE KJINST1.FILLORDER ( IN V_SALESTXNID INTEGER, IN V_CUSTID INTEGER ) SQL STORED PROCEDURE P1: BEGIN -- DECLARE VARIABLES DECLARE V_PRODID INTEGER; DECLARE V_QTY INTEGER; DECLARE V_INVENTORY INTEGER; DECLARE V_PRICE DECIMAL(5,2); DECLARE V_TOTALCOUNT INTEGER DEFAULT 0; DECLARE V_COUNT INTEGER DEFAULT 0; DECLARE V_LASTPRODUCT INTEGER DEFAULT 0; DECLARE V_ERR VARCHAR(70); -- DECLARE CURSORS DECLARE C1 CURSOR WITH HOLD FOR SELECT PP.PRODUCT_ID, RETAIL_PRICE, QTY FROM KJINST1.PRODUCT_PURCHASES PP, KJINST1.PRODUCT P WHERE PP.PRODUCT_ID =P.PRODUCT_ID AND SALES_TRANSACTION_ID=V_SALESTXNID; -- DECLARE EXCEPTION HANDLER DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_LASTPRODUCT = 1; DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL; UNIT 24 스토어드프로그램 460

21 06 Stored Procedure 작성 IBM Data Studio Developer 를실행하여 Stored Procedure 를생성하는예입니다. IF V_SALESTXNID IS NULL THEN --CREATION A NEW SALES TXN INSERT INTO KJINST1.SALES ( SALES_TRANSACTION_ID, CUSTOMER_ID, SUB_TOTAL, TYPE, TRANSACTION_TIMESTAMP) VALUES ( DEFAULT, V_CUSTID, 0, 5, CURRENT TIMESTAMP); VALUES IDENTITY_VAL_LOCAL() INTO V_SALESTXNID; END IF; OPEN C1; FETCH C1 INTO V_PRODID, V_PRICE, V_QTY; WHILE (V_LASTPRODUCT = 0) DO BEGIN DECLARE C_NO_STOCK CONDITION FOR SQLSTATE '80000'; DECLARE EXIT HANDLER FOR C_NO_STOCK BEGIN UPDATE KJINST1.PRODUCT_PURCHASES SET STATUS = 'O' WHERE SALES_TRANSACTION_ID = V_SALESTXNID AND PRODUCT_ID = V_PRODID; END; SET V_TOTALCOUNT = V_TOTALCOUNT + 1; SELECT INVENTORY INTO V_INVENTORY FROM PRODUCT WHERE PRODUCT_ID = V_PRODID; 461 DB2 9.7 개발자가이드

22 06 Stored Procedure 작성 IBM Data Studio Developer 를실행하여 Stored Procedure 를생성하는예입니다. --CHECK IF INVENTORY SATISFY THE QTY IF V_INVENTORY >= V_QTY THEN BEGIN ATOMIC INSERT INTO KJINST1.PRODUCT_PURCHASES (SALES_TRANSACTION_ID, PRODUCT_ID, PRICE, QTY, STATUS) VALUES (V_SALESTXNID, V_PRODID, V_PRICE, V_QTY, 'C'); UPDATE KJINST1.PRODUCT SET INVENTORY=INVENTORY-V_QTY WHERE PRODUCT_ID = V_PRODID; SET V_COUNT = V_COUNT + 1; END; ELSE --NOT ENOUGH STOCK TO FILL ORDER SET V_ERR = THERE IS NOT ENOUGH STOCK FOR PRODUCT ID CHAR(V_PRODID) ' TO FILL THE ORDER ' CHAR(V_SALESTXNID); SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT = V_ERR; END IF; END; FETCH C1 INTO V_PRODID, V_PRICE, V_QTY; END WHILE; IF V_COUNT = V_TOTALCOUNT THEN UPDATE KJINST1.SALES SET ORDER_STATUS = 'C' WHERE SALES_TRANSACTION_ID = V_SALESTXNID; ELSE UPDATE KJINST1.SALES SET ORDER_STATUS = 'P' WHERE SALES_TRANSACTION_ID = V_SALESTXNID; END IF; UPDATE KJINST1.SALES SET SUB_TOTAL = (SELECT SUM(PRICE) FROM PRODUCT_PURCHASES WHERE SALES_TRANSACTION_ID = V_SALESTXNID) WHERE SALES_TRANSACTION_ID = V_SALESTXNID; END P1 UNIT 24 스토어드프로그램 462

23 07 Trigger 작성 Trigger 에대해알아봅니다. 1 Trigger 이해하기 Trigger 는 Table 에 INSERT/UPDATE/DELETE 처리전 후에필요한일련의작업을자동으로수행하기위한 Table 과관련있는 Database Object 입니다. Trigger 를발생시키는문장을 Triggering SQL 문장이라합니다. Trigger 를 Triggering SQL 문장전또는후에실행하게할것인지를선택할수있습니다. 세가지 Trigger Type Trigger Type BEFORE AFTER 설명 Table 의 Data 가 Triggering SQL 문장에의해변경되기전에수행됩니다. Triggering SQL 문장이성공적으로완료되면 Trigger 가수행됩니다. Trigger 에따라, AFTER Trigger 는다른 Trigger 를수행할수있습니다. DB2 는최대 16 Level 까지다른 Trigger 를연쇄적으로수행할수있습니다. INSTRADE OF Trigger 가 View 를대상으로정의됩니다. SQL 문장이복잡한 View 에대해 INSERT/UPDATE/DELETE 를할때유용합니다. View 에대해허용되지않는 INSERT/UPDATE/DELETE 에대해사용됩니다. View 의 Column 은해당 Table 에 Column 과자동으로 Mapping 되지않으므로 INSERT/UPDATE/DELETE 할수없다. Business Logic 과해당 Table 과 View 의 column 에대한 Mapping 정보를알고있다면, SQL 의제약사항을우회적으로 INSTEAD Trigger 본문에해당 Business Logic 에정의할수있습니다. INSTEAD OF Trigger 에 SQL 문장을정의하여 Application Interface 를단순화할수있습니다. Trigger 는 Application 전반에걸쳐수행되어야될 Business Rule 을항상수행하게될때유용하게사용됩니다. 특정 Table 의 Data 가다른 Table 의 Data 와관련있는 Business Rule 이있을수있다. 만일, Business Rule 이변경되면, DB 에있는 Trigger 정의만변경하면되며, 모든 Application 은추가적인변경없이새로운 Business Rule 을따르게됩니다. 463 DB2 9.7 개발자가이드

24 07 Trigger 작성 Trigger 에대해알아봅니다. 2 CLP/ 제어센터를통한 Trigger 생성 Trigger 를생성하기위한많은 Tool 들이있습니다. CREATE TRIGGER 명령도그중하나입니다. CLP 에서수행 CREATE TRIGGER KJINST1.UPD_PRODINV_TRIG AFTER INSERT ON KJINST1.PRODUCT_PURCHASES REFERENCING NEW AS NEWROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC IF ( NEWROW.QTY > 0 ) THEN UPDATE KJINST1.PRODUCT SET INVENTORY = INVENTORY - NEWROW.QTY WHERE PRODUCT_ID = NEWROW.PRODUCT_ID; ELSEIF ( NEWROW.QTY < 0 ) THEN UPDATE KJINST1.PRODUCT SET INVENTORY = INVENTORY + NEWROW.QTY WHERE PRODUCT_ID = NEWROW.PRODUCT_ID; END IF; END@ db2 td@ -vf <fie_name> PRODUCT_PURCHASES Table 에대해 INSERT 가수행되면, UPD_PRODINV_TRIG Trigger 가수행됩니다. 수량이 0 보다크면 ( 판매 ) 재고가감소됩니다. 수량이 0 보다작으면 ( 반품 ) 재고가증가됩니다. 제어센터를통해 Trigger 를생성하는방법을소개합니다. 제어센터에서 Database 를선택한후, PRODUCT_PURCHASES Table 에서마우스오른쪽을클릭합니다. 제어센터를통한 Trigger 생성 Figure 2407A 제어센터에서 Trigger 작성하기 -1 UNIT 24 스토어드프로그램 464

25 07 Trigger 작성 Trigger 에대해알아봅니다. 트리거작성 다이얼로그에서 Trigger 정의를기술합니다. 화면이 트리거 와 트리거조치 탭으로나뉘어집니다. 주석 을제외한모든항목을정의합니다. 트리거스키마 : KC 트리거이름 : UPT_PRODINV_TRIG 테이블또는뷰스키마 : KC 테이블또는뷰이름 : PRODUCT_PURCHASE 트리거조치시간 : AFTER 트리거가실행되도록하는조작 : 삽입 트리거작성 : 트리거탭 Figure 2407B 제어센터에서트리거작성하기 -2 INSERT/UPDATE/DELETE 된이전행과새행에대한참조명을정의할수있습니다. 이전이 (Transition) 변수는 Trigger 본문에서사용됩니다. OLD Transition 변수 는 UPDATE/DELETE 가수행되면생성됩니다. 한편, NEW Transition 변수 는 UPDATE /INSERT 가수행되면생성됩니다. Triggering SQL OLD Transition NEW Transition DELETE OLD INSERT NEW UPDATE OLD NEW 465 DB2 9.7 개발자가이드

26 07 Trigger 작성 Trigger 에대해알아봅니다. 트리거작성 : 트리거조치탭 Figure 2407C 제어센터에서트리거작성하기 -3 INSERT Trigger 를작성하므로, 새행에관련된참조만활성화됩니다. 새행에대한상관이름 에 NEWROW 를입력합니다. 다음은 Triggering SQL 문장에의해영양을받는각 Row 단위로 Trigger 를실행할것인지, 또는 Row 수에관계없이각문장별로실행할것인지를선택합니다. UPT_PRODINV_TRIG Trigger 에서는 PRODUCT_PURCHASE 에입력되는각 Row 단위로 Trigger 를수행하게합니다. 그러므로실행단위로서 행 을선택합니다. 마지막으로, Trigger 가실행될때의 SQL 문장을 트리거조치 에기술합니다. 트리거조치에는기본적인템플릿이제공됩니다. WHEN 절에는 Trigger 수행을위한조건을정의합니다. 예를들어, Trigger 의 Base Table 에조건을만족하는경우에만 Trigger 를수행하도록정의할수있습니다. Tip UPT_PRODINV_TRIG Trigger에서는 PRODUCT_PURCHASE Table에입력되는모든 Operation 에대해 Trigger 를수행하므로, WHEN절을정의하지않습니다. WHEN 절 Ex) 가격 x 수량 이 100 보다큰경우만수행 WHEN ( newrow.price * newrow.qty > 100 ) WHEN 절다음에는 BEGIN ATOMIC ~ END 에해당하는 SQL 문장이정의됩니다. SQL 문장중, 하나라도실패하면 Trigger 조치가실패하도록반드시 ATOMIC 으로기술하여야합니다. Trigger 문장을기술할때는몇가지규칙이있습니다. 그중하나가, BEFORE Trigger 에서는 INSERT/UPDATE/DELETE 를기술할수없습니다. 만일, Data 수정을원한다면, AFTER Trigger 를정의하세요. 자세한내용은 "DB2 SQL Reference Guide 를참조하세요 l. 계속해서, Trigger 조치를작성합니다. PRODUCT_PURCHASES Table 에대해 INSERT 가수행되면, UPD_PRODINV_TRIG Trigger 가수행됩니다. 수량이 0 보다크면 ( 판매 ) 재고가감소됩니다. 수량이 0 보다작으면 ( 반품 ) 재고가증가됩니다. 각 Trigger 문장뒤에는 ; 으로끝을맺습니다. UNIT 24 스토어드프로그램 466

27 07 Trigger 작성 Trigger 에대해알아봅니다. Figure 2407D 제어센터에서트리거작성하기 -4 Tip 해당창에서 SQL문장을복사하거나, 다른파일로저장할수있습니다. Tool 에의해생성된 CREATE TRIGGER 에대한문장을보려면 SQL 표시 를 Click 합니다. 트리거작성 창에서바로생성하기위하여 확인 을 Click 합니다. 제어센터에서트리거 Object 를선택하여생성된 Trigger 를확인합니다. Figure 2407E 제어센터에서트리거작성하기 DB2 9.7 개발자가이드

28 07 Trigger 작성 Trigger 에대해알아봅니다. Figure 2407F 제어센터에서트리거작성하기 -6 3 Create Trigger 문 UNIT 24 스토어드프로그램 468

29 07 Trigger 작성 Trigger 에대해알아봅니다. 4 Trigger 사용예제 BEFORE INSERT TRIGGER CREATE TRIGGER KJINST1.default_class_end NO CASCADE BEFORE INSERT ON KJINST1.cl_sched REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN (n.ending IS NULL) SET n.ending = n.starting + 1 HOUR AFTER UPDATE TRIGGER CREATE TRIGGER KJINST1.audit_emp_sal AFTER UPDATE OF salary ON KJINST1.employee REFERENCING OLD AS o NEW AS n FOR EACH ROW MODE DB2SQL INSERT INTO KJINST1.audit VALUES (CURRENT TIMESTAMP, ' Employee ' o.empno ' salary changed from CHAR(o.salary) ' to ' CHAR(n.salary) ' by ' USER) SQL PL 을이용한 BEFORE INSERT TRIGGER CREATE TRIGGER KJINST1.validate_sched NO CASCADE BEFORE INSERT ON KJINST1.cl_sched REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL vs: BEGIN ATOMIC -- supply default value for ending time if null IF (n.ending IS NULL) THEN SET n.ending = n.starting + 1 HOUR; END IF; -- ensure that class does not end beyond 9PM IF (n.ending > '21:00') THEN SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT='class ending time is beyond 9pm'; ELSEIF (n.day=1 or n.day=7) THEN SIGNAL SQLSTATE '800 SET MESSAGE_TEXT='class cannot be scheduled on a weekend'; END IF; END vs 469 DB2 9.7 개발자가이드

30 07 Trigger 작성 Trigger 에대해알아봅니다. INSTEAD OF TRIGGER CREATE VIEW KJINST1.org_by_division (division, number_of_dept) AS SELECT division, count(*) FROM KJINST1.org GROUP BY division CREATE TRIGGER KJINST1.upd_org INSTEAD OF UPDATE ON KJINST1.org_by_division REFERENCING OLD AS o NEW AS n FOR EACH ROW MODE DB2SQL BEGIN ATOMIC IF (o.number_of_dept!= n.number_of_dept) THEN SIGNAL SQLSTATE '800' SET MESSAGE_TEXT = The number of department is not updatable.'; END IF; UPDATE KJINST1.org SET division = n.division WHERE division = o.division; END View 에대한 Update 후의처리결과를확인할수있습니다. select * from KJINST1.org_by_division; DIVISION NUMBER_OF_DEPT Corporate 1 Eastern 5 Midwest 2 Western 2 UPDATE KJINST1.org_by_division SET division='eastern_1' WHERE division='eastern ; select * from KJINST1.org_by_division; DIVISION NUMBER_OF_DEPT Corporate 1 Eastern_1 5 Midwest 2 Western 2 UNIT 24 스토어드프로그램 470

31 07 Trigger 작성 Trigger 에대해알아봅니다. SP 를 Call 하는 Trigger CREATE TRIGGER KJINST1.tr_autoproc_order AFTER INSERT ON KJINST1.sales FOR EACH ROW MODE DB2SQL BEGIN ATOMIC CALL KJINST1.process_neworder(); END 5 View Trigger 와 Table Trigger 비교 다음예를통해 View Trigger 와 Table Trigger 의차이점을살펴봅니다. 이를위해 Table 과 View, Table Insert Trigger 와 View Insert Trigger 를사용합니다. Table, View 및 Trigger 생성 CREATE TABLE KJINST1.T_AIRPORT ( AIRPORT_CODE CHAR( 3) NOT NULL, AIRPORT_NAME CHAR(50) ); CREATE VIEW KJINST1.V_AIRPORT AS SELECT * FROM KJINST1.T_AIRPORT ; CREATE TRIGGER KJINST1.INSERT_T_AIRPORT AFTER INSERT ON KJINST1.T_AIRPORT FOR EACH ROW MODE DB2SQL BEGIN ATOMIC END; CREATE TRIGGER KJINST1.INSERT_V_AIRPORT INSTEAD OF INSERT ON KJINST1.V_AIRPORT FOR EACH ROW MODE DB2SQL BEGIN ATOMIC END; 471 DB2 9.7 개발자가이드

32 07 Trigger 작성 Trigger 에대해알아봅니다. Table, View 및 Trigger 생성 INSERT INTO KJINST1.t_airport VALUES ('KOR', 'SEOUL_T'); INSERT INTO KJINST1.v_airport VALUES ('KOR', 'SEOUL_V'); select * from KJINST1.t_airport; AIRPORT_CODE AIRPORT_NAME KOR SEOUL_T select * from KJINST1.v_airport; AIRPORT_CODE AIRPORT_NAME KOR SEOUL_T INSTEAD OF Trigger 수정 CREATE TRIGGER KJINST1.insert_v_airport INSTEAD OF INSERT ON KJINST1.v_airport REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO KJINST1.t_airport VALUES (n.airport_code, n.airport_name); END; UNIT 24 스토어드프로그램 472

33 08 모듈작성 DB2 모듈은저장프로시저, 함수, 변수, 커서드의오브젝트를하나의묶음으로관리합니다. 오라클의패키지 (Package) 와유사한기능을수행합니다. 1 모듈 (Module) DB2 9.7 에서새롭게제공하는모듈을통해서일련의 DBMS 의오브젝트를그룹으로관리합니다. 오브젝트리스트 : 저장프로시저, 사용자함수, 배열, 레코드, 사용자정의커서등서브모듈로지정할수있습니다. 2 모듈작성실습 : 모듈에프로시저포함하기 Tip 모듈에등록된프로시저나함수는독립적으로도실행이가능합니다. 하지만모듈을사용하면모듈간의의존성관리가쉽습니다. 1. 간단테스트테이블하나작성 create table TEST_TEST (name varchar(20), entrydate timestamp) 4. 모듈선언 CREATE MODULE mod_test1 5. 선언한모듈에프로시져추가 ( 고정변수 ) ALTER MODULE mod_test1 PUBLISH PROCEDURE proc1_test2 (name varchar(20)) BEGIN call proc1_test ('kasung'); END 6. 모듈실행 call mod_test1. proc1_test3 ('HONG') 7. 데이타이상유무확인 select * from test_test NAME ENTRYDATE kasung 오전 3:37:36 8. 스키마. 모듈명. 프로시져명으로도실행예시 call db2inst1.mod_test1. proc1_test3 ('KOREA') 473 DB2 9.7 개발자가이드

34 08 모듈작성 DB2 모듈은저장프로시저, 함수, 변수, 커서등의 DB2 오브젝트를하나의묶음으로관리합니다. 오라클의패키지 (Package) 와유사한기능을수행합니다. 3 모듈작성실습 : 모듈에함수포함하기 1. 기존의모듈에 appending 하기 ALTER MODULE mod_test1 PUBLISH function fn_addsum123 (p1 int) returns int BEGIN return ( select fn_addsum(p1) from sysibm.sysdummy1 ); END 2. 모듈의함수실행 select mod_test1.fn_addsum123 (10) from sysibm.sysdummy 모듈작성실습 : 모듈에사용자정의타입포함하기 1. CREATE MODULE INVENTORY 2. ALTER MODULE INVENTORY ADD TYPE ITEMLIST AS INTEGER ARRAY[VARCHAR(100)] 3. ALTER MODULE INVENTORY ADD VARIABLE ITEMS ITEMLIST UNIT 24 스토어드프로그램 474

35 08 모듈작성 사용자정의모듈과서브모율정보를조회해봅니다. 모듈정보조회 (1) : DB2 9.7 에추가된관리자뷰이용 SQL> SELECT MODULENAME, DIALECT, MODULETYPE, REMARKS FROM SYSCAT.MODULES MODULENAME DIALECT MODULETYPE REMARKS EMP_ADMIN PL/SQL P PL/SQL Package Body MOD_TEST1 DB2 SQL PL M (null) SQL>SELECT OBJECTMODULENAME, OBJECTNAME, OBJECTTYPE FROM SYSCAT.MODULEOBJECTS OBJECTMODULENAME OBJECTNAME OBJECTTYPE EMP_ADMIN GET_DEPT_NAME FUNCTION EMP_ADMIN UPDATE_EMP_SAL FUNCTION 모듈정보조회 (2) : DB2 9.7 이전버전에서지원하는관리자뷰예시 SQL> SELECT ROUTINEMODULENAME, ROUTINENAME, ROUTINETYPE FROM SYSCAT.ROUTINES ROUTINEMODULENAME ROUTINENAME ROUTI NETYPE DBMS_OUTPUT ENABLE P DBMS_OUTPUT GET_LINES P DBMS_ALERT INIT P 475 DB2 9.7 개발자가이드

36 00 23 Memo UNIT 24 스토어드프로그램 476