(Microsoft Word - Lab5-PLSQL\203I\203u\203W\203F\203N\203g\202\314\210\332\215s.doc)

Transcription

1 [Lab 5] PLSQL オブジェクトの移行 2009 年 12 月日本アイアイ ビービー エムエム株式会社

2 Contents CONTENTS はじめに ハンズオンで使用する DDL について 独立したしたタイプタイプの宣言 スタンドアロン タイプの移行 パッケージ Envelope の作成 タイプを参照する箇所の修正 パッケージの作成 パッケージ ACCOUNT_PACKAGE の作成 AVERAGE_BNAD 関数の作成 パッケージ仕様部の作成 パッケージ本体の作成 パッケージ OFFICE_PACKAGE の作成 パッケージ仕様部の作成 パッケージ本体の作成 プロシージャーの作成 COUNT_PROJECTS のパラメーター確認 COUNT_PROJECTS のプロシージャーへの書き直し COUNT_PROJECTS プロシージャーの作成 ADD_NEW_EMPLOYEE プロシージャーの作成 ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーの作成 VIEW の作成 トリガーの作成 Update_Departments トリガーの作成 UPDATE_ACC_ON_NEW_EMPL トリガーの作成

3 1. はじめに このハンズオンでは Oracle から抽出した DDL を元に DB2 への投入を行い PLSQL のプロシージャーや関数を作成します このハンズオンで行う作業は下記となります パッケージの作成 プロシージャーの作成 関数の作成 ビューの作成 トリガーの作成 このハンズオンで使用するディレクトリーの構成は下記の様になっています PLSQL のオブジェクトを作成するための DDL は workshoplab5plsql_ddl を参照してください workshop lab5 :Anonymous block など plsql_ddl :Lab5 で使う PLSQL DDL 作業フォルダ plsql_ddl_modified : 変更済み DDL 1.1 ハンズオンで使用使用する DDL について このハンズオンで使用する DDL は Oracle から IBM Data Movement Tool を使用して抽出したものです Data Movement Tool は DDL 抽出の際 オブジェクトの種別ごとファイルを作成します そのため 一つの DDL ファイルには複数のオブジェクトの DDL が格納されます 例えば 一つのファイルに複数のオブジェクトが格納されている場合 ハンズオンの手順が煩雑となるため このハンズオン中では下記の様にして オリジナルの DDL ファイルをオブジェクトごとに分割しています オリジナルの DDL: db2plsql_package.db2 分割した DDL: db2plsql_package.db2.account_package db2plsql_package.db2.envelope db2plsql_package.db2.office_package 分割された DDL は workshoplab5plsql_ddl ディレクトリに格納されています 3

4 2. 独立したしたタイプタイプの宣言 このセクションでは スタンドアロン タイプを取り扱います スタンドアロン タイプ とは PLSQL オブジェクトとして作成されるタイプの中で パッケージ中で宣言されるのではなく 独立して宣言されるものです このハンズオンには 1 つのスタンドアロン タイプが含まれます IBM Data Movement Tool は下記の 2 つのファイルにスタンドアロン タイプを抽出します db2plsql_type.db2 db2plsql_type_body.db2 DB2 9.7 はスタンドアロン タイプをサポートしていないため 別のオブジェクトとして作成する必要があります 移行先のオブジェクトとして パッケージが使用可能です パッケージは プロシージャーやファンクション タイプや変数の宣言をまとめて取り扱うための単位となります パッケージの一要素としてタイプを宣言することで 独立したタイプと同様にタイプの作成が可能になります ただし そのタイプを使用する際はパッケージの名称をスキーマ ( 修飾子 ) として追加する必要があります 2.1 スタンドアロン タイプタイプの移行 このステップではスタンドアロン タイプをパッケージに移行します 移行先のパッケージとしてパッケージ ENVELOPE を使用します 下記に示す様に このパッケージは変数宣言のみを含んでいます 移行元となるスタンドアロン タイプ EMP_INFO_TYPE の DDL は db2plsql_type.db2 に含まれています このタイプ宣言を パッケージ ENVELOPE の中へ移行します 下記のコマンドを実行して EMP_INFO_TYPE の DDL を確認してください cd workshoplab5plsql_ddl cat db2plsql_type.db2 実行例 db2inst1@db2v97onsles10:workshoplab5> cd workshoplab5plsql_ddl db2inst1@db2v97onsles10:workshoplab5plsql_ddl> cat db2plsql_type.db2 --#SET TERMINATOR CONNECT TO LABDB SET CURRENT SCHEMA = 'SALES' --#SET :TYPE:SALES:EMP_INFO_TYPE CREATE OR REPLACE TYPE "SALES"."EMP_INFO_TYPE" AS OBJECT ( EMP_ID NUMBER(5), FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(20), BAND CHAR(1), HIRE_DATE TIMESTAMP(3)) < 省略 > 4

5 _ パッケージ ENVELOPE へのタイプ宣言追加スタンドアロン タイプをパッケージ中のタイプ宣言に移行する際は 下記の様な変更を行ってください CREATE OR REPLACE キーワードの除去 SALES スキーマの除去 AS OBJECT キーワードを IS RECORD へ変更 宣言の最終部分を から ;( セミコロン ) へ変更 パッケージ ENVELPE の変更は下記の手順で行います 1.vi エディターでパッケージ ENVELOPE の DDL ファイルを開きます vi db2plsql_package.db2.envelope 2.db2plsql_type.db2 ファイルから EMP_INFO_TYPE の DDL をコピーします 3. 前述の変更を行います 変更例 --#SET TERMINATOR CONNECT TO LABDB SET CURRENT SCHEMA = 'SALES' --#SET :PACKAGE:SALES:ENVELOPE CREATE OR REPLACE PACKAGE "SALES"."ENVELOPE" AS -- * DESCRIPTION: The package to store the declaration of types * -- type declaration TYPE RCT1 IS REF CURSOR; -- add type declaration for standalone type TYPE "EMP_INFO_TYPE" IS RECORD ( EMP_ID NUMBER(5), FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(20), BAND CHAR(1), HIRE_DATE TIMESTAMP(3)); END ENVELOPE; TERMINATE このハンズオンでは vi エディターを使用して DDL を編集することを想定していますが 時間のない方や vi の操作に不慣れな方は workshoplab5plsql_ddl_modified ディレクトリーに修正済みの DDL がありますので こちらを使用していただく事も可能です このハンズオンで今後登場する DDL は 全て同様に修正済みの DDL が格納されています 5

6 2.2 パッケージ Envelope の作成 前ステップで DDL の変更が完了したので パッケージ ENVELOPE を作成します 下記のコマンドを実行して パッケージを作成してください db2 -tvf db2plsql_package.db2.envelope 実行例 workshoplab5plsql_ddl_modified> db2 -tvf db2plsql_package.db2.envelope < 省略 > SET CURRENT SCHEMA = 'SALES' CREATE OR REPLACE PACKAGE "SALES"."ENVELOPE" AS -- * DESCRIPTION: The package to store the declaration of types * -- type declaration TYPE RCT1 IS REF CURSOR; -- add type declaration for standalone type TYPE "EMP_INFO_TYPE" IS RECORD ( EMP_ID NUMBER(5), FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(20), BAND CHAR(1), HIRE_DATE TIMESTAMP(3)); END ENVELOPE; TERMINATE DB20000I TERMINATE コマンドが正常に完了しました 2.3 タイプを参照参照するする箇所箇所の修正 EMP_INFO_TYPE を ENVELOPE パッケージ内での宣言に変更したことで EMP_INFO_TYPE を参照する際には ENVELOPE を修飾子として追加する必要があります 下記のコマンドを実行して 移行対象の PLSQL オブジェクトから EMP_INFO_TYPE を参照している部分を確認してください cd workshoplab5plsql_ddl head db2plsql* egrep -i "^== EMP_INFO_TYPE" このコマンドは db2plsql をファイル名に持つファイルから EMP_INFO_TYPE を含む行を抽出します 6

7 コマンド実行例 > cd workshoplab5plsql_ddl workshoplab5plsql_ddl> head db2plsql* egrep -i "^== EMP_INFO_TYPE" ==> db2plsql_function.db2.average_band <== ==> db2plsql_function.db2.count_projects <== ==> db2plsql_package.db2.account_package <== ==> db2plsql_package.db2.envelope <== ==> db2plsql_package.db2.office_package <== ==> db2plsql_package_body.db2.account_package <== ==> db2plsql_package_body.db2.office_package <== ==> db2plsql_procedure.db2.add_new_employee <== o_employee OUT EMP_INFO_TYPE, ==> db2plsql_procedure.db2.assign_employee_to_new_account <== ==> db2plsql_procedure.db2.get_employee_resume <== ==> db2plsql_trigger.db2.update_acc_on_new_empl <== ==> db2plsql_trigger.db2.update_departments <== ==> db2plsql_type.db2 <== --#SET :TYPE:SALES:EMP_INFO_TYPE CREATE OR REPLACE TYPE "SALES"."EMP_INFO_TYPE" AS OBJECT ( ==> db2plsql_type_body.db2 <== ==> db2plsql_views.db2.organization_structure <== この結果から EMP_INFO_TYPE を参照するオブジェクトは プロシージャー ADD_NEW_EMPLOYEE のみであることがわかります vi エディターを使用して DDL ファイル db2plsql_procedure.db2.add_new_employee を開き ENVELOPE パッケージ中の EMP_INFO_TYPE への参照が問題なく行えるよう ADD_NEW_EMPLOYEE 中の該当行を編集し ENVELOPE を追加してください vi db2plsql_procedure.db2.add_new_employee 編集前編集後 : o_employee OUT EMP_INFO_TYPE, : o_employee OUT ENVELOPE.EMP_INFO_TYPE, ここで編集した DDL は 後続のステップで使用します 7

8 3. パッケージの作成 この章では パッケージの作成を行います 先ほどの演習でも登場しましたが パッケージとはプロシージャーや関数 タイプや変数の宣言を一括で取り扱うための単位です パッケージは パッケージ仕様部およびパッケージ本体という 2 つの主要コンポーネントで構成されます パッケージ仕様部はパッケージの外部に対して公開されたパブリック インターフェースで 当該パッケージの外部から参照可能な要素で構成されます パッケージ仕様部を作成するには CREATE PACKAGE ステートメントを実行します パッケージ本体には パッケージ仕様部内で宣言されたプロシージャーおよび関数の 実際の振る舞いを定義するための実装を記述します また それ以外にもパッケージ気外部に公開しないプライベートのタイプ 変数 およびカーソルの宣言を行うことができます パッケージ本体を作成するには CREATE PACKAGE BODY ステートメントを実行します この章では 下記に示す 2 つのパッケージを作成します ACCOUNT_PACKAGE OFFICE_PACKAGE 3.1 パッケージ ACCOUNT_PACKAGE の作成 パッケージ ACCOUNT_PACKAGE を作成します このパッケージは パッケージの前が示す様に ACCOUNT データを取り扱う処理をひとまとめにして管理しています パッケージのメンバーとしては下記の 4 つのプロシージャー Add_Account Remove_Account Account_List Display_Account_List 及び タイプ customer_name_cache を含みます 前述したように どのような要素を含むかはパッケージ本体を見ることでわかります ACCOUNT_PACKAGE のパッケージ本体は db2plsql_package.db2.account_package に格納されています AVERAGE_BNAD 関数の作成 ACCOUNT_PACKAGE のメンバーの一つ Display_Account_List プロシージャーでは AVERAGE_BNAD 関数を参照しています そのため AVERAGE_BNAD 関数を先に作成します この関数は 部署コードとアカウントコードを引数として受け取り 該当の部署コード アカウントコードを持つ従業員を EMPLOYEES 表から検索した上で 職位の平均を戻します 8

9 AVERAGE_BNAD 関数の DDL --#SET TERMINATOR CONNECT TO LABDB SET CURRENT SCHEMA = 'SALES' --#SET :FUNCTION:SALES:AVERAGE_BAND CREATE OR REPLACE FUNCTION "SALES"."AVERAGE_BAND" (p_department IN employees.dept_code%type, p_acct_id IN employe es.acct_id%type) RETURN CHAR AS -- variable declaration v_averageband CHAR(1); v_numericband NUMBER; v_totalband NUMBER:=0; v_numberemployees NUMBER; -- CURSOR declaration CURSOR c_employees IS SELECT band FROM employees WHERE dept_code = p_department AND acct_id = p_acct_id; OPEN c_employees; LOOP FETCH c_employees INTO v_numericband; EXIT WHEN c_employees%notfound; v_totalband := v_totalband + v_numericband; END LOOP; v_numberemployees:=c_employees%rowcount; IF(v_NumberEmployees = 0) THEN RAISE_APPLICATION_ERROR(-20001, 'No employees exist for ' p_department ' ' p_acct_id); END IF; SELECT DECODE(ROUND(v_TotalBandv_NumberEmployees), 5, 'A', 4, 'B', 3, 'C', 2, 'D', 1, 'E') INTO v_averageband FROM dual; RETURN v_averageband; END Average_Band; この関数は ソースコードの変更無しにそのまま DB2 で作成可能です 下記のコマンドを使用して AVERAGE_BNAD 関数を作成してください db2 -tvf db2plsql_function.db2.average_band 9

10 実行例 $ db2 -tvf db2plsql_function.db2.average_band < 省略 > CREATE OR REPLACE FUNCTION "SALES"."AVERAGE_BAND" (p_department IN employees.dept_code%type, p_acct_id IN employe es.acct_id%type) < 省略 > RETURN v_averageband; END Average_Band; DB20000I SQL コマンドが正常に完了しました この関数が正常に作成できたことを workshoplab5 ディレクトリで下記のスクリプトで確認してください db2 -tvf call.average_band.clp このスクリプトは 下記の様な無名ブロックを含みます この無名ブロックでは部署コード A00 アカウントコード 1 を引数として AVERAGE_BNAD 関数を呼び出します 下記の実行例のように 結果として C 1 文字が返却されれば成功です 実行例 $ cd workshoplab5 $ db2 -tvf call.average_band.clp < 省略 > DECLARE str CHAR(30); str := SALES.AVERAGE_BAND('A00',1); DBMS_OUTPUT.PUT_LINE(str); END; C TERMINATE DB20000I TERMINATE コマンドが正常に完了しました パッケージ仕様部仕様部の作成 ACCOUNT_PACKAGE のパッケージ仕様部を作成します パッケージ仕様部は下記の様な DDL となります 10

11 ACCOUNT_PACKAGE パッケージ宣言部の DDL CREATE OR REPLACE PACKAGE "SALES"."ACCOUNT_PACKAGE" AS -- type declaration TYPE customer_name_cache IS TABLE OF Employees%ROWTYPE INDEX BY PLS_INTEGER; -- PROCEDURE declaration PROCEDURE Add_Account(p_AccountId IN accounts.acct_id%type, p_deptcode IN accounts.dept_code%type, p_accountdesc IN accounts.acct_desc%type, p_maxemployees IN accounts.max_employees%type); PROCEDURE Remove_Account(p_AccountId IN accounts.acct_id%type, p_deptcode IN accounts.dept_code%type); PROCEDURE Account_List(p_dept_code IN accounts.dept_code%type, p_acct_id IN accounts.acct_id%type, p_employees_name_cache OUT Customer_Name_Cache); PROCEDURE Display_Account_List(p_dept_code IN accounts.dept_code%type, p_acct_id IN accounts.acct_id%type); END Account_Package; 下記のコマンドを実行して パッケージ仕様部を作成してください db2 -tvf db2plsql_package.db2.account_package 正常にパッケージ仕様部が作成されれば がメッセージとして返却されます ここでは作成の確認は行わず パッケージ本体の作成に進みます パッケージ本体本体の作成 パッケージ本体の DDL は db2plsql_package_body.db2.account_package ファイルに格納されています パッケージ本体についても Oracle で稼働していた PLSQL のロジックがそのまま使用できます ただし パッケージ本体に含まれるプロシージャー中で参照している関数 (AVERAGE_BAND) の名前解決をするため 関数の検索順序に SALES スキーマを追加する必要があります _ パッケージ本体の DDL 修正 vi エディターから db2plsql_package_body.db2.account_package ファイルを開き 下記の DDL のように SET CURRENT PATH = SALES, CURRENT PATH を追加してください ACCOUNT_PACKAGE パッケージ本体の DDL( 一部抜粋 ) 11

12 $ cat db2plsql_package_body.db2.account_package --#SET TERMINATOR CONNECT TO LABDB SET CURRENT SCHEMA = 'SALES' SET CURRENT PATH = SALES, CURRENT PATH --#SET :PACKAGE_BODY:SALES:ACCOUNT_PACKAGE CREATE OR REPLACE PACKAGE BODY "SALES"."ACCOUNT_PACKAGE" AS PROCEDURE Add_Account(p_AccountId IN accounts.acct_id%type, p_deptcode IN accounts.dept_code%type, p_accountdesc IN accounts.acct_desc%type, p_maxemployees IN accounts.max_employees%type) IS < 省略 > _ パッケージ本体の作成修正が完了した後 下記のコマンドを使用してパッケージ本体を作成してください db2 -tvf db2plsql_package_body.db2.account_package 正常にパッケージ本体が作成されれば がメッセージとして返却されます 12

13 _ パッケージの稼働確認 ACCOUNT_PACKAGE のメンバーの一つ プロシージャー Display_Account_List を動かしてみることで パッケージの稼働確認を行います workshoplab5 ディレクトリで下記のコマンドを実行してください db2 -tvf call.display_account_list.clp このコマンドは 下記の様なスクリプトを含んでいます CONNECT TO LABDB; SET SQLCOMPAT PLSQL; SET CURRENT SCHEMA SALES; SET SERVEROUTPUT ON; sales.account_package.display_account_list( 'C01', 3 ); END; TERMINATE; END で囲まれた部分が無名ブロックを構成しており ここから Display_Account_List を呼び出しています 実行例 db2inst1@db2v97onsles10:> cd workshoplab5 db2inst1@db2v97onsles10:workshoplab5> db2 -tvf call.display_account_list.clp < 省略 > SET SERVEROUTPUT ON DB20000I SET SERVEROUTPUT コマンドが正常に完了しました sales.account_package.display_account_list( 'C01', 3 ); END; List of employees Record id : 1 Employee : KWAN Number of projects : 1 Average Band in department : C Record id : 2 Employee : QUINTANA Number of projects : 2 Average Band in department : C Record id : 3 Employee : NICHOLLS Number of projects : 4 Average Band in department : C Record id : 4 Employee : NATZ Number of projects : 1 Average Band in department : C TERMINATE DB20000I TERMINATE コマンドが正常に完了しました 上記のように 4 エントリー分の社員情報が表示されれば正常に稼働しています 13

14 3.2 パッケージ OFFICE_PACKAGE の作成 このステップでは パッケージ OFFICE_PACKAGE を作成します このパッケージは OFFICE 表に対するデータの登録と削除を行うプロシージャーを含んでいます パッケージのメンバーは下記の 2 プロシージャーです Add_Office Remove_Office 先ほど作成した ACCOUNT_PACKAGE では PLSQL のソースコードはそのまま使用できましたが この OFFICE_PACKAGE は そのままでは DB2 上で作成できない記述を含んでいます 次ページに表示した Remove_Office プロシージャーの青字部分がそれに該当します Remove_Office プロシージャーはパッケージ OFFICE_PACKAGE に属するパッケージですが 自分自身の内部に別の関数 CHECK_EMP_EXISTANCE の宣言を含んでいます CHECK_EMP_EXISTANCE は 削除対象のレコードが存在するかどうかを確認する関数として定義されており Remove_Office プロシージャーからの内部からのみ参照できる関数です このように 関数やプロシージャーの内部で宣言される関数 及びプロシージャーを ネストされた関数 ネストされたプロシージャー と呼び DB2 9.7 ではサポートされていません そのため このような関数やプロシージャーを DB2 に対して作成するためには ネストされた関数やプロシージャー部分を分離して 独立した関数もしくはプロシージャーとして作成する必要があります 独立したオブジェクトとして作成する際に考慮する必要があるのは下記の 2 点です 1. 関数名 プロシージャー名の重複ネストされた関数 プロシージャーは その定義を含む関数やプロシージャーの内部からしか参照できません そのため 例えば 別々のプロシージャーの中に定義されたネストされた関数が同じ名前であったとしても問題ありません 独立したオブジェクトとして作成する場合 一つのスキーマ配下に同じ名前の関数は一つしか存在できません そのため どちらかの名前を変更する必要があります また 名前を変更した関数については 呼び出し元のソースコードについても変更する必要があります 2. 変数の共有についてネストされた関数 プロシージャーと その定義を含む関数やプロシージャーでは 変数を共有することが可能です ところが 独立したオブジェクト同士では変数の共有ができません 上記の課題を解決するために推奨される方策は パッケージへの変更 です パッケージを使用した場合 パッケージ仕様部のメンバーとしてグローバル変数を宣言することで 複数の関数やプロシージャーから変数を共有することが可能です 14

15 db2plsql_package_body.db2.office_package の抜粋 ( プロシージャー Remove_Office ) PROCEDURE Remove_Office(p_office_id IN offices.office_id%type) IS -- * DESCRIPTION: Removes the Office from database based on the office id -- EXAMPLE: EXEC OFFICE_PACKAGE.REMOVE_OFFICE(1) * -- variable declaration v_existance number; -- exception declaration e_officenotregistered EXCEPTION; PRAGMA EXCEPTION_INIT (e_officenotregistered, ); -- * DESCRIPTION: Function declaration for checking * FUNCTION CHECK_EMP_EXISTANCE ( p_office_id IN offices.office_id%type) RETURN NUMBER AS -- variable declaration v_count NUMBER; v_flag NUMBER; SELECT count(*) INTO v_count from EMPLOYEES WHERE OFFICE_ID = p_office_id; IF ( v_count!= 0 ) THEN v_flag := 1; ELSE v_flag := 0; END IF; RETURN v_flag; END CHECK_EMP_EXISTANCE; -- Checking the parameter value v_existance := CHECK_EMP_EXISTANCE (p_office_id); IF ( v_existance = 1 ) THEN DBMS_OUTPUT.PUT_LINE('Office ID ' p_office_id ' has employee.'); RETURN; END IF; -- Execute deleting DELETE FROM offices WHERE office_id = p_office_id; IF SQL%NOTFOUND THEN RAISE e_officenotregistered; END IF; EXCEPTION WHEN e_officenotregistered THEN DBMS_OUTPUT.PUT_LINE('Office ' p_office_id ' does not exist.'); WHEN others THEN RAISE e_officenotregistered; END Remove_Office; 15

16 3.2.1 パッケージ仕様部仕様部の作成 前述の通り OFFICE_PACKAGE を DB2 で作成するために ネストされた関数 CHECK_EMP_EXISTANCE を 独立した関数に変更します パッケージ仕様部で宣言するメンバーは下記の様に変更となります 変更前 : Add_Office( プロシージャー ) Remove_Office( プロシージャー ) 変更後 Add_Office( プロシージャー ) Remove_Office( プロシージャー ) CHECK_EMP_EXISTANCE( 関数 ) _DDL の編集 vi エディターで db2plsql_package.db2.office_package ファイルを開き CHECK_EMP_EXISTANCE 関数の記述を追加してください 変更例 --#SET TERMINATOR CONNECT TO LABDB SET CURRENT SCHEMA = 'SALES' --#SET :PACKAGE:SALES:OFFICE_PACKAGE CREATE OR REPLACE PACKAGE "SALES"."OFFICE_PACKAGE" AS -- PROCEDURE declaration PROCEDURE Add_Office(p_office_id IN offices.office_id%type, p_building IN offices.building%type, p_number_seats IN offices.number_seats%type, p_description IN offices.description%type); PROCEDURE Remove_Office(p_office_id IN offices.office_id%type); -- FUNCTION declaration FUNCTION CHECK_EMP_EXISTANCE ( p_office_id IN offices.office_id%type) RETURN NUMBER; END OFFICE_PACKAGE; _ パッケージ仕様部の作成 DDL の編集が完了した後に 下記のコマンドを使用してパッケージ仕様部を作成してください db2 -tvf db2plsql_package.db2.office_package DB20000I メッセージが返却されれば パッケージ仕様部の作成は成功です 16

17 3.2.2 パッケージ本体本体の作成 次に パッケージ本体を作成します パッケージ本体では Remove_Office プロシージャーからネストされた関数の記述を除去し 独立した関数として作成する必要があります _DDL の編集 vi エディターから db2plsql_package_body.db2.office_package ファイルを開き 下記の変更を行ってください Remove_Office プロシージャーからネストされた関数 CHECK_EMP_EXISTANCE の記述を除去 Remove_Office プロシージャーと同列に CHECK_EMP_EXISTANCE 関数のソースコードを移動 次ページに変更後の DDL を抜粋して掲載します 青字となっている部分が新たに追加された記述となります また Remove_Office プロシージャーから FUNCTION 定義が除去されていることがわかります 17

18 db2plsql_package_body.db2.office_package の変更後の DDL( 抜粋 ) CREATE OR REPLACE PACKAGE BODY "SALES"."OFFICE_PACKAGE" AS < 省略 > PROCEDURE Remove_Office(p_office_id IN offices.office_id%type) IS -- variable declaration v_existance number; -- exception declaration e_officenotregistered EXCEPTION; PRAGMA EXCEPTION_INIT (e_officenotregistered, ); -- Checking the parameter value v_existance := CHECK_EMP_EXISTANCE (p_office_id); IF ( v_existance = 1 ) THEN DBMS_OUTPUT.PUT_LINE('Office ID ' p_office_id ' has employee.'); RETURN; END IF; -- Execute deleting DELETE FROM offices WHERE office_id = p_office_id; IF SQL%NOTFOUND THEN RAISE e_officenotregistered; END IF; EXCEPTION WHEN e_officenotregistered THEN DBMS_OUTPUT.PUT_LINE('Office ' p_office_id ' does not exist.'); WHEN others THEN RAISE e_officenotregistered; END Remove_Office; -- * DESCRIPTION: Function declaration for checking * FUNCTION CHECK_EMP_EXISTANCE ( p_office_id IN offices.office_id%type) RETURN NUMBER AS -- variable declaration v_count NUMBER; v_flag NUMBER; SELECT count(*) INTO v_count from EMPLOYEES WHERE OFFICE_ID = p_office_id; IF ( v_count!= 0 ) THEN v_flag := 1; ELSE v_flag := 0; END IF; RETURN v_flag; END CHECK_EMP_EXISTANCE; END OFFICE_PACKAGE; 18

19 _ パッケージ本体の作成 DDL の編集が完了した後に 下記のコマンドを使用してパッケージ本体を作成してください db2 -tvf db2plsql_package_body.db2.office_package 正常にパッケージ本体が作成されれば DB20000I メッセージが返却されます _ パッケージの稼働確認下記のスクリプトを使用して パッケージが正常に稼働しているかどうかを確認します cd workshoplab5 db2 -tvf call.office_package.clp このスクリプトは下記の様な SQL を含んでおり Add_Office プロシージャーと Remove_Office プロシージャーを使用してオフィスの登録と削除を行います SELECT * FROM OFFICES; sales.office_package.add_office( 3, '46 E. Main Street', 2, 'Temporary office in Hotel'); END SELECT * FROM OFFICES; sales.office_package.remove_office( 3); END SELECT * FROM OFFICES; 正常にパッケージが作成されている場合 次ページの実行例のようにオフィス ID=3 のオフィスが追加 削除されることが確認できます 19

20 OFFICE_PACKAGE の実行例 db2 -tvf call.office_package.clp < 省略 > SELECT * FROM OFFICES OFFICE_ID BUILDING NUMBER_SEATS DESCRIPTION Offshore Drive 30. Main small office rented by the company Park Avenue 40. Additional facility for new hires 2 レコードが選択されました sales.office_package.add_office( 3, '46 E. Main Street', 2, 'Temporary office in Hotel'); END Office 46 E. Main Street successfully registered. SELECT * FROM OFFICES OFFICE_ID BUILDING NUMBER_SEATS DESCRIPTION Offshore Drive 30. Main small office rented by the company Park Avenue 40. Additional facility for new hires E. Main Street 2. Temporary office in Hotel 3 レコードが選択されました sales.office_package.remove_office( 3); END SELECT * FROM OFFICES OFFICE_ID BUILDING NUMBER_SEATS DESCRIPTION Offshore Drive 30. Main small office rented by the company Park Avenue 40. Additional facility for new hires 2 レコードが選択されました 20

21 4. プロシージャーの作成 この章では以下の 3 つのプロシージャーの作成を行います COUNT_PROJECTS ADD_NEW_EMPLOYEE ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT COUNT_PROJECTS は Oracle 上ではファンクションとして作成されていたオブジェクトです DB2 9.7 では アウトパラメーターを持つファンクションをサポートしていない為 移行するにはアウトパラメーターを持つプロシージャーとして書き換える必要があります ADD_NEW_EMPLOYEE は スタンドアロン タイプを参照しており修正が必要ですが 1 章で既に対応済みのため ここでの修正は必要ありません ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT は修正の必要がなくそのまま作成が可能です 4.1 COUNT_PROJECTS のパラメーターパラメーター確認 _ パラメーターの確認 最初に workshoplab5plsql_ddl ディレクトリで以下のコマンドを実行し COUNT_PROJECTS ファンクションの DDL を確認します cat db2plsql_function.db2.count_projects DDL の確認では 特にこのファンクションのパラメーターを確認します 上記のコマンドの実行例の青字部分になります このファンクションでは IN パラメーターと OUT パラメーターを 1 つずつ持ち NUMBER の戻り値を返します 21

22 実行例 cat db2plsql_function.db2.count_projects --#SET TERMINATOR CONNECT TO LABDB SET CURRENT SCHEMA = 'SALES' --#SET :FUNCTION:SALES:COUNT_PROJECTS CREATE OR REPLACE FUNCTION "SALES"."COUNT_PROJECTS" (p_empid IN employees.emp_id%type, o_acct_id OUT employees.acct_id%type) RETURN NUMBER AS -- * DESCRIPTION: Function that counts the project based on the employeed id and also -- returns information on total projects of the account to which employee id belongs DEMO PURPOSE: Function with OUT parameter, FOR LOOP over cursor EXAMPLE: SELECT COUNT_PROJECTS(1, acct_id) FROM DUAL; * -- variable declaration v_totalprojects NUMBER:=0; v_accountprojects NUMBER; < 省略 > 4.2 COUNT_PROJECTS のプロシージャープロシージャーへのへの書き直し db2plsql_function.db2.count_projects の以下の点を書き換えます ( 修正したファイルは workshoplab5plsql_ddl_modified ディレクトリにあります お時間のない方や vi の操作に不慣れな方はこちらをご使用ください ) 8 行目 CREATE FUNCTION 文から CREATE PROCEDURE 文への変更と戻り値をアウトパラメーターとして定義し直します 変更前 CREATE OR REPLACE FUNCTION "SALES"."COUNT_PROJECTS" (p_empid IN employees.emp_id%type, o_acct_id OUT employees.acct_id%type) RETURN NUMBER 変更後 CREATE OR REPLACE PROCEDURE "COUNT_PROJECTS" (p_empid IN employees.emp_id%type, o_acct_id OUT employees.acct_id%type, out_return OUT NUMBER) 22

23 48 行目の RETURN をアウトパラメーターへの値の代入に書き換えます 変更前 RETURN v_totalprojects; 変更後 out_return:= v_totalprojects; 修正例 ( 青字部分が修正箇所 ) --#SET TERMINATOR CONNECT TO LABDB SET CURRENT SCHEMA = 'SALES' --#SET :FUNCTION:SALES:COUNT_PROJECTS CREATE OR REPLACE PROCEDURE "COUNT_PROJECTS" (p_empid IN employees.emp_id%type, o_acct_id OUT employees.acct_id%type, out_return OUT NUMBER) AS -- * < 省略 > -- * < 省略 > FOR v_accountrec IN c_deptaccts LOOP o_acct_id:=v_accountrec.acct_id; -- Determine the projects for this account. SELECT num_projects INTO v_accountprojects FROM accounts WHERE dept_code = v_accountrec.dept_code AND acct_id = v_accountrec.acct_id; -- Add it to the total so far. v_totalprojects := v_totalprojects + v_accountprojects; END LOOP; out_return:= v_totalprojects; END Count_Projects; TERMINATE 4.3 COUNT_PROJECTS プロシージャーの作成 _ プロシージャーの作成修正した DDL を以下のコマンドで実行し プロシージャーを作成します db2 -tvf db2plsql_function.db2.count_projects 23

24 実行例 db2 -tvf db2plsql_function.db2.count_projects CONNECT TO LABDB データベース接続情報 データベース サーバー = DB2LINUX SQL 許可 ID = DB2INST1 ローカル データベース別名 = LABDB SET CURRENT SCHEMA = 'SALES' CREATE OR REPLACE PROCEDURE "COUNT_PROJECTS" (p_empid IN employees.emp_id%type, o_acct_id OUT employees.acct_id%type, out_return OUT NUMBER) AS < 省略 > -- Add it to the total so far. v_totalprojects := v_totalprojects + v_accountprojects; END LOOP; out_return:= v_totalprojects; END Count_Projects; TERMINATE DB20000I TERMINATE コマンドが正常に完了しました CREATE OR REPLACE PROCEDURE 文の実行後に DB20000I SQL コマンドが正常に完了しました が表示されることを確認します _COUNT_PROJECTS プロシージャーの稼動確認下記のスクリプトを使用して パッケージが正常に稼働しているかどうかを確認します cd workshoplab5 db2 -tvf call.count_projects.clp このスクリプトは下記の様な SQL を含んでおり COUNT_PROJECTS プロシージャーを呼び出してアウトパラメーターに返された値を表示します CONNECT TO LABDB; SET SQLCOMPAT PLSQL; SET CURRENT SCHEMA SALES; SET SERVEROUTPUT ON; DECLARE i_emp_id NUMBER:=15; o_acct_id employees.acct_id%type; 24

25 o_num NUMBER; SALES.COUNT_PROJECTS(i_emp_id, o_acct_id, o_num); DBMS_OUTPUT.PUT_LINE ( 'Employee ID : ' i_emp_id); DBMS_OUTPUT.PUT_LINE ( 'Related account ID: ' o_acct_id); DBMS_OUTPUT.PUT_LINE ( 'Total projects : ' o_num); END; 正常にプロシージャーが作成されている場合 実行時に以下の実行例のように EmployeeID=15 の Related account ID と Total projects が表示されます 実行例 db2inst1@db2v97onsles10:workshoplab5plsql_ddl_modified> cd workshoplab5 db2inst1@db2v97onsles10:workshoplab5> db2 -tvf call.count_projects.clp CONNECT TO LABDB データベース接続情報 データベース サーバー = DB2LINUX SQL 許可 ID = DB2INST1 ローカル データベース別名 = LABDB SET SQLCOMPAT PLSQL DB20000I SET SQLCOMPAT コマンドが正常に完了しました SET CURRENT SCHEMA SALES SET SERVEROUTPUT ON DB20000I SET SERVEROUTPUT コマンドが正常に完了しました DECLARE i_emp_id NUMBER:=15; o_acct_id employees.acct_id%type; o_num NUMBER; SALES.COUNT_PROJECTS(i_emp_id, o_acct_id, o_num); DBMS_OUTPUT.PUT_LINE ( 'Employee ID : ' i_emp_id); DBMS_OUTPUT.PUT_LINE ( 'Related account ID: ' o_acct_id); DBMS_OUTPUT.PUT_LINE ( 'Total projects : ' o_num); END; Employee ID : 15 Related account ID: 5 Total projects : 4 TERMINATE DB20000I TERMINATE コマンドが正常に完了しました 25

26 4.4 ADD_NEW_EMPLOYEE プロシージャーの作成 ADD_NEW_EMPLOYEE プロシージャーは Oracle で稼働していた PLSQL のロジックがそのまま使用できます ただし プロシージャー中で参照しているパッケージの変数 (ENVELOPE..EMP_INFO_TYPE,) の名前解決をするため 関数の検索順序に SALES スキーマを追加する必要があります _ プロシージャーの DDL 修正 vi エディターから 1 章で修正した db2plsql_procedure.db2.add_new_employee ファイルを開き 下記の DDL のように SET CURRENT PATH = SALES, CURRENT PATH を追加してください (EMP_INFO_TYPE-> ENVELOPE.EMP_INFO_TYPE への変更は 1 章で対応済み ) db2plsql_procedure.db2.add_new_employee ファイル修正例 ( 一部抜粋 ) --#SET TERMINATOR CONNECT TO LABDB SET CURRENT SCHEMA = 'SALES' SET CURRENT PATH = SALES, CURRENT PATH --#SET :PROCEDURE:SALES:ADD_NEW_EMPLOYEE CREATE OR REPLACE PROCEDURE "SALES"."ADD_NEW_EMPLOYEE" ( p_firstname employees.first_name%type, p_lastname employees.last_name%type, p_empmgrid employees.emp_mgr_id%type, p_deptcode employees.dept_code%type, p_account employees.acct_id%type, o_employee OUT ENVELOPE.EMP_INFO_TYPE, p_createdate employees.create_date%type DEFAULT SYSDATE, p_officeid employees.office_id%type DEFAULT 2 ) AS -- * DESCRIPTION: Procedure to add a new employee 修正後 以下のコマンドを実行して ADD_NEW_EMPLOYEE プロシージャーを作成します db2 -tvf db2plsql_procedure.db2.add_new_employee 26

27 実行例 db2 -tvf db2plsql_procedure.db2.add_new_employee CONNECT TO LABDB データベース接続情報 データベース サーバー = DB2LINUX SQL 許可 ID = DB2INST1 ローカル データベース別名 = LABDB SET CURRENT SCHEMA = 'SALES' SET CURRENT PATH = SALES, CURRENT PATH CREATE OR REPLACE PROCEDURE "SALES"."ADD_NEW_EMPLOYEE" ( p_firstname employees.first_name%type, p_lastname employees.last_name%type, p_empmgrid employees.emp_mgr_id%type, p_deptcode employees.dept_code%type, p_account employees.acct_id%type, o_employee OUT ENVELOPE.EMP_INFO_TYPE, p_createdate employees.create_date%type DEFAULT SYSDATE, p_officeid employees.office_id%type DEFAULT 2 ) AS < 省略 > OPEN c_get_employee; FETCH c_get_employee INTO o_employee; CLOSE c_get_employee; END Add_New_Employee; _ADD_NEW_EMPLOYEE プロシージャーの稼動確認下記のスクリプトを使用して パッケージが正常に稼働しているかどうかを確認します cd workshoplab5 db2 -tvf call.add_new_employee.clp このスクリプトは下記の様な SQL を含んでおり ADD_NEW_ENPLOYEE プロシージャーを呼び出し EMPLOYEES 表への INSERT を実施します EMPLOYEES 表に FIRST_NAME= MAX のデータが挿入されるのが確認できるはずです CONNECT TO LABDB; SET SQLCOMPAT PLSQL; SET CURRENT SCHEMA SALES; SET SERVEROUTPUT ON; alter sequence employee_sequence restart with 999; delete from employees where emp_id = 1000; select emp_id,first_name,last_name,band from employees where first_name = 'MAX'; DECLARE emp_info sales.envelope.emp_info_type; 27

28 実行例 sales.add_new_employee('max', 'Trenton', 1, 'A00', 1, emp_info,sysdate); DBMS_OUTPUT.PUT_LINE('EMP_ID :' emp_info.emp_id); DBMS_OUTPUT.PUT_LINE('FIRST_NAME :' emp_info.first_name); DBMS_OUTPUT.PUT_LINE('LAST_NAME :' emp_info.last_name); DBMS_OUTPUT.PUT_LINE('BAND :' emp_info.band); DBMS_OUTPUT.PUT_LINE('HIRE DATE :' emp_info.hire_date); END; select emp_id,first_name,last_name,band from employees where first_name = 'MAX'; db2inst1@db2v97onsles10:workshoplab5> db2 -tvf call.add_new_employee.clp CONNECT TO LABDB < 省略 > alter sequence employee_sequence restart with 999 delete from employees where emp_id = 1000 select emp_id,first_name,last_name,band from employees where first_name = 'MAX' EMP_ID FIRST_NAME LAST_NAME BAND レコードが選択されました DECLARE emp_info sales.envelope.emp_info_type; sales.add_new_employee('max', 'Trenton', 1, 'A00', 1, emp_info,sysdate); DBMS_OUTPUT.PUT_LINE('EMP_ID :' emp_info.emp_id); DBMS_OUTPUT.PUT_LINE('FIRST_NAME :' emp_info.first_name); DBMS_OUTPUT.PUT_LINE('LAST_NAME :' emp_info.last_name); DBMS_OUTPUT.PUT_LINE('BAND :' emp_info.band); DBMS_OUTPUT.PUT_LINE('HIRE DATE :' emp_info.hire_date); END; EMP_ID :1000 FIRST_NAME :MAX LAST_NAME :TRENTON BAND :1 HIRE DATE : select emp_id,first_name,last_name,band from employees where first_name = 'MAX' EMP_ID FIRST_NAME LAST_NAME BAND MAX TRENTON 1 1 レコードが選択されました 28

29 4.5 ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーの作成 ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーは Oracle で稼働していた PLSQL のロジックがそのまま使用できます 以下のコマンドを実行して ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーを作成します db2 -tvf db2plsql_procedure.db2.assign_employee_to_new_account 実行例 db2 -tvf db2plsql_procedure.db2.assign_employee_to_new_account CONNECT TO LABDB データベース接続情報 データベース サーバー = DB2LINUX SQL 許可 ID = DB2INST1 ローカル データベース別名 = LABDB SET CURRENT SCHEMA = 'SALES' CREATE OR REPLACE PROCEDURE "SALES"."ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT" ( p_employeeid IN employees.emp_id%type, p_deptcode IN accounts.dept_code%type, p_acctid IN accounts.acct_id%type) AS -- * DESCRIPTION: Re-assigns employee to a new account DEMO PURPOSE: RAISE_APPLICATION_ERROR, < 省略 > EXCEPTION WHEN NO_DATA_FOUND THEN --Account information passed to this procedure doesn't exist. Raise an error RAISE_APPLICATION_ERROR(-20001, p_deptcode ' ' p_acctid ' doesn''t exist!'); END Assign_Employee_To_New_Account; _ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーの稼動確認下記のスクリプトを使用して パッケージが正常に稼働しているかどうかを確認します cd workshoplab5 db2 -tvf call.assign_employee_to_new_account.clp 29

30 このスクリプトは下記の様な SQL を含んでいます 正しくプロシージャーが作成されていれば ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーを呼び出しにより EMP_ID=15 の DEPT_CODE が E21 に変更されるはずです CONNECT TO LABDB; SELECT SMALLINT(ACCT_ID) ACCT_ID, DEPT_CODE, MAX_EMPLOYEES, CURRENT_EMPLOYEES FROM ACCOUNTS WHERE DEPT_CODE in ('D11', 'E21'); SELECT EMP_ID, FIRST_NAME, LAST_NAME, DEPT_CODE, ACCT_ID FROM EMPLOYEES WHERE EMP_ID=15; CALL SALES.ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT(15, 'E21', 4); SELECT SMALLINT(ACCT_ID) ACCT_ID, DEPT_CODE, MAX_EMPLOYEES, CURRENT_EMPLOYEES FROM ACCOUNTS WHERE DEPT_CODE in ('D11', 'E21'); SELECT EMP_ID, FIRST_NAME, LAST_NAME, DEPT_CODE, ACCT_ID FROM EMPLOYEES WHERE EMP_ID=15; 実行例 db2 -tvf call.assign_employee_to_new_account.clp < 省略 > SELECT SMALLINT(ACCT_ID) ACCT_ID, DEPT_CODE, MAX_EMPLOYEES, CURRENT_EMPLOYEES FROM ACCOUNTS WHERE DEPT_CODE in ('D11', 'E21') ACCT_ID DEPT_CODE MAX_EMPLOYEES CURRENT_EMPLOYEES E D SELECT EMP_ID, FIRST_NAME, LAST_NAME, DEPT_CODE, ACCT_ID FROM EMPLOYEES WHERE EMP_ID=15 EMP_ID FIRST_NAME LAST_NAME DEPT_CODE ACCT_ID BRUCE ADAMSON D11 5. CALL SALES.ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT(15, 'E21', 4) リターン状況 = 0 SELECT SMALLINT(ACCT_ID) ACCT_ID, DEPT_CODE, MAX_EMPLOYEES, CURRENT_EMPLOYEES FROM ACCOUNTS WHERE DEPT_CODE in ('D11', 'E21') ACCT_ID DEPT_CODE MAX_EMPLOYEES CURRENT_EMPLOYEES E D SELECT EMP_ID, FIRST_NAME, LAST_NAME, DEPT_CODE, ACCT_ID FROM EMPLOYEES WHERE EMP_ID=15 EMP_ID FIRST_NAME LAST_NAME DEPT_CODE ACCT_ID BRUCE ADAMSON E

31 5. View の作成 この章では 以下の View を作成します ORGANIZATION_STRUCTURE" この View の DDL では Oracle 固有の FORCE オプションが指定されています このオプションは View に依存関係を持つオブジェクトが View 作成時になくても View の作成を行えるようにするオプションです DB2 9.7 では FORCE オプションは CREATE VIEW 文で記述できないため このオプションを取り除いて実行する必要があります _DDL の確認 workshoplab5plsql_ddl ディレクトリにある db2plsql_views.db2.organization_structure ファイルの内容を以下のコマンドを実行して確認します cat db2plsql_views.db2.organization_structure FORCE オプションがついていることを確認します 実行例 cat db2plsql_views.db2.organization_structure --#SET TERMINATOR CONNECT TO LABDB SET CURRENT SCHEMA = 'SALES' --#SET :VIEW:SALES:ORGANIZATION_STRUCTURE CREATE OR REPLACE FORCE VIEW "SALES"."ORGANIZATION_STRUCTURE" ("LEVEL", "FULL_NAME", "DEPARTMENT") AS SELECT LEVEL, SUBSTR((LPAD(' ', 4 * LEVEL - 1) INITCAP(e.last_name) ', ' INITCAP(e.first_name)), 1, 40), NVL(d.dept_name, 'Uknown') FROM employees e, departments d WHERE e.dept_code=d.dept_code(+) START WITH emp_id = 1 CONNECT BY NOCYCLE PRIOR emp_id = emp_mgr_id _FORCE オプションの除去 vi エディタを使用して db2plsql_views.db2.organization_structure ファイルを修正します (workshoplab5plsql_ddl_modified ディレクトリに修正済みのファイルがあります お時間のない方や vi の操作に不慣れな方はこちらをご使用ください ) 31

32 修正点 FORCE オプションを取り除く 修正例 --#SET TERMINATOR CONNECT TO LABDB SET CURRENT SCHEMA = 'SALES' --#SET :VIEW:SALES:ORGANIZATION_STRUCTURE CREATE OR REPLACE VIEW "SALES"."ORGANIZATION_STRUCTURE" ("LEVEL", "FULL_NAME", "DEPARTMENT") AS SELECT LEVEL, SUBSTR((LPAD(' ', 4 * LEVEL - 1) INITCAP(e.last_name) ', ' INITCAP(e.first_name)), 1, 40), NVL(d.dept_name, 'Uknown') FROM employees e, departments d WHERE e.dept_code=d.dept_code(+) START WITH emp_id = 1 CONNECT BY NOCYCLE PRIOR emp_id = emp_mgr_id _View の作成修正したファイルを以下のコマンドで実行します db2 -tvf db2plsql_views.db2.organization_structure DB20000I メッセージが表示されトリガーが正常に作成されたことを確認します 実行例 db2inst1@db2v97onsles10:workshoplab5plsql_ddl_modified> db2 -tvf db2plsql_views.db2.organization_structure CONNECT TO LABDB データベース接続情報 データベース サーバー = DB2LINUX SQL 許可 ID = DB2INST1 ローカル データベース別名 = LABDB SET CURRENT SCHEMA = 'SALES' CREATE OR REPLACE VIEW "SALES"."ORGANIZATION_STRUCTURE" ("LEVEL", "FULL_NAME", "DEPARTMENT") AS SELECT LEVEL, SUBSTR((LPAD(' ', 4 * LEVEL - 1) INITCAP(e.last_name) ', ' INITCAP(e.first_name)), 1, 40), NVL(d.dept_name, 'Uknown') FROM employees e, departments d WHERE e.dept_code=d.dept_code(+) START WITH emp_id = 1 CONNECT BY NOCYCLE PRIOR emp_id = emp_mgr_id 32

33 6. トリガーの作成 この章では Update_Departments,UPDATE_ACC_ON_NEW_EMPL の 2 つのトリガーを作成します 6.1 Update_Departments トリガーの作成 Update_Departments トリガーは複数のアクションで起動するトリガーです 具体的には INSERT UPDATE DELETE 全ての変更処理に対して起動します DB2 9.7 ではこのようなトリガーをサポートしていません DB2 で使用できるようにするためには それぞれのアクションで起動するトリガーとして分割して作成します ( 例 INSERT トリガー UPDATE トリガー DELETE トリガーの 3 つに分割 ) _UPDATE_DEPARTMENTS トリガーの確認最初に Update_DEPARTMENTS トリガーの DDL を確認します 以下のコマンドを実行し workshoplab5pl_sql_ddl ディレクトリにある db2plsql_trigger.db2.update_departments の内容を確認します cat db2plsql_trigger.db2.update_departments 青字になっている部分がトリガーの起動アクションの設定になります 実行例 db2inst1@db2v97onsles10:workshoplab5plsql_ddl> cat db2plsql_trigger.db2.update_departments < 省略 > CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS" < 省略 > AFTER INSERT OR DELETE OR UPDATE ON employees ees FOR EACH ROW DECLARE CURSOR c_stats IS SELECT dept_code, COUNT(*) total_employees, SUM(current_projects) total_projects FROM employees GROUP BY dept_code; IF DELETING THEN UPDATE departments SET (total_projects, total_employees)= (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:old.dept_code; ELSIF INSERTING THEN UPDATE departments SET (total_projects, total_employees)= (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:new.dept_code; ELSIF UPDATING THEN UPDATE departments SET (total_projects, total_employees)= (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code IN (:old.dept_code, :new.dept_code); END IF; END UpdateDepartments; 33

34 ここでは UPDATE_DEPARTMENTS トリガーを以下の 3 つのトリガーに分割して作成します UPDATE_DEPARTMENTS_ON_UPDATE UPDATE_DEPARTMENTS_ON_INSERT UPDATE_DEPARTMENTS_ON_DELETE _UPDATE トリガーの作成 db2plsql_trigger.db2.update_departments ファイルを db2plsql_trigger.db2.update_departments_update ファイルとしてコピーし以下の点を修正します ( 修正済みのファイルは workshoplab5plsql_ddl_modified に用意してあります お時間がない方や vi の操作が不慣れな方はこちらをご使用ください ) 修正点 1 トリガー名の変更 UPDATE_DEPARTMENTS UPDATE_DEPARTMENTS_ON_UPDATE 2. トリガーの起動アクションの変更 AFTER INSERT OR DELETE OR UPDATE AFTER UPDATE 3.UPDATE 時以外の処理をコメントアウト 修正例 ( 修正点は青字部分 ) --#SET TERMINATOR < 省略 > --#SET :TRIGGER:SALES:UPDATE_DEPARTMENTS CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_UPDATE UPDATE_DEPARTMENTS_ON_UPDATE" -- * < 省略 > -- * AFTER UPDATE ON employees FOR EACH ROW DECLARE CURSOR c_stats IS SELECT dept_code, COUNT(*) total_employees, SUM(current_projects) total_projects FROM employees GROUP BY dept_code; -- IF DELETING THEN -- UPDATE departments SET (total_projects, total_employees)= -- (SELECT T count(1), SUM(current_projects) FROM employees) WHERE dept_code=:old.dept_code; -- ELSIF INSERTING THEN -- UPDATE departments SET (total_projects, total_employees)= -- (SELECT count(1), SUM(current_projects) FROM employees) WHERE W dept_code=:new.dept_code; -- ELSIF UPDATING THEN UPDATE departments SET (total_projects, total_employees)= (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code IN (:old.dept_code, :new.dept_code); -- END IF; END UpdateDepartments; 34

35 修正後 以下のコマンドで DDL を実行し トリガーを作成します db2 -tvf db2plsql_trigger.db2.update_departments_update DB20000I メッセージが表示されトリガーが正常に作成されたことを確認します 実行例 db2 -tvf db2plsql_trigger.db2.update_departments_update CONNECT TO LABDB データベース接続情報 データベース サーバー = DB2LINUX SQL 許可 ID = DB2INST1 ローカル データベース別名 = LABDB SET CURRENT SCHEMA = 'SALES' CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_UPDATE" < 省略 > UPDATE departments SET (total_projects, total_employees)= (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code IN (:old.dept_code, :new.dept_code); -- END IF; END UpdateDepartments; TERMINATE DB20000I TERMINATE コマンドが正常に完了しました 35

36 _INSERT トリガーの作成 db2plsql_trigger.db2.update_departments ファイルを db2plsql_trigger.db2.update_departments_insert ファイルとしてコピーし以下の点を修正します ( 修正済みのファイルは workshoplab5plsql_ddl_modified に用意してあります お時間がない方や vi の操作が不慣れな方はこちらをご使用ください ) 修正点 1 トリガー名の変更 UPDATE_DEPARTMENTS UPDATE_DEPARTMENTS_ON_INSERT 2. トリガーの起動アクションの変更 AFTER INSERT OR DELETE OR UPDATE AFTER INSERT 3.INSERT 時以外の処理をコメントアウト 修正例 ( 修正点は青字部分 ) < 省略 > CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_INSERT UPDATE_DEPARTMENTS_ON_INSERT" -- * < 省略 > -- * AFTER INSERT ON employees FOR EACH ROW DECLARE CURSOR c_stats IS SELECT dept_code, COUNT(*) total_employees, SUM(current_projects) total_projects FROM employees GROUP BY dept_code; -- IF DELETING THEN -- UPDATE departments SET (total_projects, total_employees)= -- (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:old.dept_code; -- ELSIF INSERTING THEN UPDATE departments SET (total_projects, total_employees)= (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:new.dept_code; -- ELSIF UPDATING THEN -- UPDATE departments SET (total_projects, total_employees)= -- (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code IN (:old.dept_code, :new.dept_code); -- END IF; END UpdateDepartments; 修正後 以下のコマンドで DDL を実行し トリガーを作成します db2 -tvf db2plsql_trigger.db2.update_departments_insert DB20000I メッセージが表示されトリガーが正常に作成されたことを確認します 36

37 実行例 db2 -tvf db2plsql_trigger.db2.update_departments_insert CONNECT TO LABDB < 省略 > CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_INSERT" < 省略 > -- IF DELETING THEN -- UPDATE departments SET (total_projects, total_employees)= -- (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:old.dept_code; -- ELSIF INSERTING THEN UPDATE departments SET (total_projects, total_employees)= (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:new.dept_code; -- ELSIF UPDATING THEN -- UPDATE departments SET (total_projects, total_employees)= -- (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code IN (:old.dept_code, :new.dept_code); -- END IF; END UpdateDepartments; 37

38 _DELETE トリガーの作成 db2plsql_trigger.db2.update_departments ファイルを db2plsql_trigger.db2.update_departments_delete ファイルとしてコピーし以下の点を修正します ( 修正済みのファイルは workshoplab5plsql_ddl_modified に用意してあります お時間がない方や vi の操作が不慣れな方はこちらをご使用ください ) 修正点 1 トリガー名の変更 UPDATE_DEPARTMENTS UPDATE_DEPARTMENTS_ON_DELETE 2. トリガーの起動アクションの変更 AFTER INSERT OR DELETE OR UPDATE AFTER DELETE 3.DELETE 時以外の処理をコメントアウト 修正例 ( 修正点は青字部分 ) --#SET TERMINATOR < 省略 > CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_DELETE UPDATE_DEPARTMENTS_ON_DELETE" -- * < 省略 > -- * AFTER DELETE ON employees FOR EACH ROW DECLARE CURSOR c_stats IS SELECT dept_code, COUNT(*) total_employees, SUM(current_projects) total_projects FROM employees GROUP BY dept_code; -- IF DELETING THEN UPDATE departments SET (total_projects, total_employees)= (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:old.dept_code; -- ELSIF INSERTING THEN -- UPDATE departments SET (total_projects, total_employees)= -- (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:new.dept_code; -- ELSIF UPDATING THEN -- UPDATE departments SET (total_projects, total_employees)= -- (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code IN (:old.dept_code, :new.dept_code); -- END IF; END UpdateDepartments; 修正後 以下のコマンドで DDL を実行し トリガーを作成します db2 -tvf db2plsql_trigger.db2.update_departments_delete DB20000I メッセージが表示されトリガーが正常に作成されたことを確認します 38

39 実行例 db2 -tvf db2plsql_trigger.db2.update_departments_delete CONNECT TO LABDB < 省略 > CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_DELETE" -- * < 省略 > -- IF DELETING THEN UPDATE departments SET (total_projects, total_employees)= (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:old.dept_code; -- ELSIF INSERTING THEN -- UPDATE departments SET (total_projects, total_employees)= -- (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:new.dept_code; -- ELSIF UPDATING THEN -- UPDATE departments SET (total_projects, total_employees)= -- (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code IN (:old.dept_code, :new.dept_code); -- END IF; END UpdateDepartments; 39

40 6.2 UPDATE_ACC_ON_NEW_EMPL トリガーの作成 UPDATE_ACC_ON_NEW_EMPL トリガーは UPDATE_DEPARTMENTS トリガーのように複数の操作に対して起動するトリガーではなく INSERT に対してのみ起動するトリガーであり DB2 9.7 でもそのまま稼動させることが可能です workshoplab5plsql_ddl ディレクトリにある db2plsql_trigger.db2.update_acc_on_new_empl ファイルの DDL を以下のコマンドで確認します cat db2plsql_trigger.db2.update_acc_on_new_empl 実行例 cat db2plsql_trigger.db2.update_acc_on_new_empl < 省略 > CREATE OR REPLACE TRIGGER "SALES"."UPDATE_ACC_ON_NEW_EMPL" < 省略 > AFTER INSERT ON employees FOR EACH ROW DECLARE v_num_projects accounts.num_projects%type; -- Determine the number of projects for this account. SELECT num_projects INTO v_num_projects FROM accounts WHERE dept_code = :new.dept_code AND acct_id = :new.acct_id; UPDATE employees SET current_projects = current_projects + v_num_projects WHERE emp_id = :new.emp_mgr_id; -- Add one to the number of employees in the project. UPDATE accounts SET current_employees = current_employees + 1 WHERE dept_code = :new.dept_code AND acct_id = :new.acct_id; END Update_Acc_On_New_Empl; 以下のコマンドで DDL を実行し トリガーを作成します db2 -tvf db2plsql_trigger.db2.update_acc_on_new_empl DB20000I メッセージが表示されトリガーが正常に作成されたことを確認します 40

41 実行例 db2 -tvf db2plsql_trigger.db2.update_acc_on_new_empl < 省略 > CREATE OR REPLACE TRIGGER "SALES"."UPDATE_ACC_ON_NEW_EMPL" < 省略 > -- Determine the number of projects for this account. SELECT num_projects INTO v_num_projects FROM accounts WHERE dept_code = :new.dept_code AND acct_id = :new.acct_id; UPDATE employees SET current_projects = current_projects + v_num_projects WHERE emp_id = :new.emp_mgr_id; -- Add one to the number of employees in the project. UPDATE accounts SET current_employees = current_employees + 1 WHERE dept_code = :new.dept_code AND acct_id = :new.acct_id; END Update_Acc_On_New_Empl; 以上です 41

42 Copyright IBM Corporation 2009 All Rights Reserved. 本書に含まれている情報は 正式な IBM のテストを受けていません また 明記にしろ 暗黙的にしろ なんらの保証もなしに配布されるものです この情報の使用またはこれらの技術の実施は いずれも 使用先の責任において行われるべきものであり それらを評価し 実際に使用する環境に統合する使用先の判断に依存しています それぞれの項目は ある特定の状態において正確であることが IBM によって調べられていますが 他のところで同じまたは同様の結果が得られる保証はありません これらの技術を自身の環境に適用することを試みる使用先は 自己の責任において行う必要があります 42