DBMS 2
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
Stored Program은 DBMS Application Object 를 사용함으로써 응용프로그램 코드 의 단순화, 성능개선, 코드의 재 사용 향상 및 이식성을 증대할 수 있습니다. 일반적은 Editor를 이용하여 작성 및 생성 할수 있습니다. Data Studio Developer 시작 방법은 아래와 같습니다. 데이터베이스애 연결합니다. 스토어드 프로시저 작성을 선택합니다. SQL 작성을 클릭하여 스토어드 프로시저를 작성합니다. UDF 생성하기 UDF 의 필수구문 및 선택구문 구성 함수 본문은 “BEGIN ATOMIC … END SQL” block 내부에 기술합니다. 함수는 다음 한 문장을 기술합니다. IBM Data Studio Developer에서 “전개”버튼을 눌러 실행합니다. 복잡한 Query 실행하기 특정 기간 동안의 이익률을 계산 하려면, 매번 세 개의 Table을 Join하여야 합니다. 이를 단순화 하기 위하여, PRODUCT ID, 시작일자 및 종료일자를 입력 파라미터를 갖는 UDF를 작성합니다. -> PROD_PROFIT UDF 생성하기 1) UDF KJINST1.PROD_PROFIT를 생성합니다. Table UDF -> STOCKCHK UDF 생성하기 1) UDF KJINST1.STOCCHK를 입력 Parameter없이 생성합니다. Stored Procedure 이해하기 Stored Procedure 개발하기 Trigger 이해하기 CLP/제어센터를 통한 Trigger 생성 Trigger 사용 예제 View Trigger 와 Table Trigger 비교 모듈(Module) 모듈 작성 실습 : 모듈에 프로시저 포함하기 모듈 작성 실습: 모듈에 함수 포함하기 모듈 작성 실습: 모듈에 사용자 정의 타입 포함하기스토어드 프로그램
스토어드 프로그램
Stored Program
그러나 IBM Data Studio Developer를 사용하면 프로그램 디버깅이 가능하여, 작성 시 매우 편리합니다.
Stored Program 생성
UDF 생성
예제 에서는 p_amount DECIMAL(9,2) 하나의 Parameter를 정의하였습니다.
Parameter p_amount는 판매금액을 나타냅니다. Application에서 함수를
call하면, 함수는 판매금액에 대한 세금을 반환합니다. “RETURN” 구문은
DECIMAL(9,2) 반환을 기술합니다.
UDF 생성 - 예
다음은 특정 기간 동안에 판매금액에 대한 이익률을 계산하는 UDF를 작성합니다. 해당 정보가 다음과 같이 세 개의 Table에 분산 되어 있습니다.
2) PRODUCT : 제품별 비용
3) SALES : 판매에 대한 일자/시간 관리 정보
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) );
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 );
( 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
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를 통해 빌드 후, 실행합니다.
Table UDF 작성
Query의 FROM절에서 사용되며, Table형xo의 Row를 반환합니다. 다음은 입고가 필 요한 모든 PRODUCT를 반환하는 Table 함수입니다. 누가, 언제 STOCK를 점검했는지 LOG을 위해 AUDIT_STOCKCHK Table을 사용합니다.
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);
RETURN
SELECT PRODUCT_ID
, DESCRIPTION
, INVENTORY
, MINIMUM_INVENTORY
FROM KJINST1.PRODUCT
WHERE INVENTORY < MINIMUM_INVENTORY;
END
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 : 현재 시간
Stored Procedure 작성
Trigger 작성
모듈 작성
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_tes
t ('kasung');
END
6. 모듈 실행
call mod_test1. proc1_test3 ('HONG')
7. 데이타 이상유무 확인
select * from test_test
NAME ENTRYDATE
------------- ---------------------
kasung 2009. 4. 3 오전 3:37:36
8. 스키마.모듈명.프로시져명으로 도 실행 예시
call db2inst1.mod_test1. proc1_test3 ('KOREA')
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.sysdummy1
1
----
1000
2. ALTER MODULE INVENTORY ADD
TYPE ITEMLIST AS INTEGER ARRAY[VARCHAR(100)]
3. ALTER MODULE INVENTORY ADD VARIABLE ITEMS ITEMLIST
