[Lab 5] PL/SQL オブジェクトの移行

[Lab 5]
PL/SQL オブジェクトの
オブジェクトの移行
2009 年 12 月
日本アイ
日本アイ･
アイ･ビー･
ビー･エム株式会社
エム株式会社
Contents
Contents
CONTENTS............................................................................................................2
CONTENTS
1.
はじめに .........................................................................................................3
1.1
2.
独立した
独立したタイプ
したタイプの
タイプの宣言......................................................................................4
宣言
2.1
2.2
2.3
3.
ハンズオンで使用する DDL について ..........................................................3
スタンドアロン・タイプの移行.......................................................................4
パッケージ Envelope の作成.......................................................................6
タイプを参照する箇所の修正......................................................................6
パッケージの
パッケージの作成............................................................................................8
作成
3.1 パッケージ ACCOUNT_PACKAGE の作成 ...................................................8
3.1.1
AVERAGE_BNAD 関数の作成 ................................................................8
3.1.2
パッケージ仕様部の作成 .....................................................................10
3.1.3
パッケージ本体の作成 .........................................................................11
3.2 パッケージ OFFICE_PACKAGE の作成......................................................14
3.2.1
パッケージ仕様部の作成 .....................................................................16
3.2.2
パッケージ本体の作成 .........................................................................17
4.
プロシージャーの
プロシージャーの作成 ...................................................................................21
4.1
4.2
4.3
4.4
4.5
COUNT_PROJECTS のパラメーター確認...................................................21
COUNT_PROJECTS のプロシージャーへの書き直し ..................................22
COUNT_PROJECTS プロシージャーの作成 ...............................................23
ADD_NEW_EMPLOYEE プロシージャーの作成............................................26
ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーの作成 ...............29
5.
VIEW の作成 .................................................................................................31
6.
トリガーの
トリガーの作成 ..............................................................................................33
6.1
6.2
Update_Departments トリガーの作成 .........................................................33
UPDATE_ACC_ON_NEW_EMPL トリガーの作成...........................................40
2
1.
はじめに
このハンズオンでは、Oracle から抽出した DDL を元に、DB2 への投入を行い、PL/SQL のプロ
シージャーや関数を作成します。このハンズオンで行う作業は下記となります。
パッケージの作成
プロシージャーの作成
関数の作成
ビューの作成
トリガーの作成
このハンズオンで使用するディレクトリーの構成は下記の様になっています。PL/SQL のオブジ
ェクトを作成するための DDL は/workshop/lab5/plsql_ddl を参照してください。
/workshop
└─lab5
:Anonymous block など
├─plsql_ddl
:Lab5 で使う PL/SQL 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 は/workshop/lab5/plsql_ddl ディレクトリに格納されています。
3
2.
独立した
独立したタイプ
したタイプの
タイプの宣言
このセクションでは、スタンドアロン・タイプを取り扱います。「スタンドアロン・タイプ」とは、
PL/SQL オブジェクトとして作成されるタイプの中で、パッケージ中で宣言されるのではなく、独
立して宣言されるものです。
このハンズオンには 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 /workshop/lab5/plsql_ddl
cat db2plsql_type.db2
実行例
db2inst1@db2V97onSLES10:/workshop/lab5> cd /workshop/lab5/plsql_ddl
db2inst1@db2V97onSLES10:/workshop/lab5/plsql_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
＿パッケージ ENVELOPE へのタイプ宣言追加
スタンドアロン・タイプをパッケージ中のタイプ宣言に移行する際は、下記の様な変更を行ってく
ださい。
・「CREATE OR REPLACE」キーワードの除去
・「SALES」スキーマの除去
・「AS OBJECT」キーワードを、「IS RECORD」へ変更
・宣言の最終部分を「/」から「；（セミコロン）」へ変更
パッケージ ENVELPE の変更は下記の手順で行います
１．vi エディターでパッケージ ENVELOPE の DDL ファイルを開きます
vi db2plsql_package.db2.envelope
２．db2plsql_type.db2 ファイルから、EMP_INFO_TYPE の DDL をコピーします
３．前述の変更を行います
変更例
--#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 の操作に不慣れな方は、/workshop/lab5/plsql_ddl_modified/ディレクトリーに修正
済みの DDL がありますので、こちらを使用していただく事も可能です。このハンズオンで今後
登場する DDL は、全て同様に修正済みの DDL が格納されています。
5
2.2
パッケージ Envelope の作成
前ステップで DDL の変更が完了したので、パッケージ ENVELOPE を作成します。
下記のコマンドを実行して、パッケージを作成してください。
db2 -tvf db2plsql_package.db2.envelope
実行例
/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_package.db2.envelope
<省略>
SET CURRENT SCHEMA = 'SALES'
DB20000I SQL コマンドが正常に完了しました。
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;
DB20000I
SQL コマンドが正常に完了しました。
TERMINATE
DB20000I TERMINATE コマンドが正常に完了しました。
2.3
タイプを
タイプを参照する
参照する箇所
する箇所の
箇所の修正
EMP_INFO_TYPE を ENVELOPE パッケージ内での宣言に変更したことで、EMP_INFO_TYPE を
参照する際には ENVELOPE を修飾子として追加する必要があります。下記のコマンドを実行
して、移行対象の PL/SQL オブジェクトから、EMP_INFO_TYPE を参照している部分を確認して
ください。
cd /workshop/lab5/plsql_ddl
head -99999 db2plsql* |egrep -i "^==|EMP_INFO_TYPE"
このコマンドは、「db2plsql」をファイル名に持つファイルから、EMP_INFO_TYPE を含む行を抽出
します。
6
コマンド実行例
/> cd /workshop/lab5/plsql_ddl
/workshop/lab5/plsql_ddl> head -99999 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,
ENVELOPE.
ここで編集した DDL は、後続のステップで使用します。
7
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」に格納さ
れています。
3.1.1
AVERAGE_BNAD 関数の
関数の作成
ACCOUNT_PACKAGE のメンバーの一つ、Display_Account_List プロシージャーでは
AVERAGE_BNAD 関数を参照しています。そのため、AVERAGE_BNAD 関数を先に作成します。
この関数は、部署コードとアカウントコードを引数として受け取り、該当の部署コード・アカウント
コードを持つ従業員を EMPLOYEES 表から検索した上で、職位の平均を戻します。
8
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;
BEGIN
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_TotalBand/v_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
実行例
$ 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 コマンドが正常に完了しました
この関数が正常に作成できたことを、/workshop/lab5 ディレクトリで下記のスクリプトで確認し
てください。
db2 -tvf call.average_band.clp
このスクリプトは、下記の様な無名ブロックを含みます。この無名ブロックでは部署コード「A00」、
アカウントコード「1」を引数として AVERAGE_BNAD 関数を呼び出します。下記の実行例のよう
に、結果として「C」1 文字が返却されれば成功です。
実行例
$ cd /workshop/lab5
$ db2 -tvf call.average_band.clp
<省略>
DECLARE
str CHAR(30);
BEGIN
str := SALES.AVERAGE_BAND('A00',1);
DBMS_OUTPUT.PUT_LINE(str);
END;
DB20000I
SQL コマンドが正常に完了しました。
C
TERMINATE
DB20000I TERMINATE コマンドが正常に完了しました。
3.1.2
パッケージ仕様部
パッケージ仕様部の
仕様部の作成
ACCOUNT_PACKAGE のパッケージ仕様部を作成します。パッケージ仕様部は下記の様な
DDL となります。
10
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
p_DeptCode
p_AccountDesc
p_MaxEmployees
IN
IN
IN
IN
accounts.acct_id%TYPE,
accounts.dept_code%TYPE,
accounts.acct_desc%TYPE,
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
p_acct_id
IN
accounts.dept_code%TYPE,
accounts.acct_id%TYPE);
END Account_Package;
/
下記のコマンドを実行して、パッケージ仕様部を作成してください。
db2 -tvf db2plsql_package.db2.account_package
正常にパッケージ仕様部が作成されれば、「DB20000I SQL コマンドが正常に完了しました。」
がメッセージとして返却されます。ここでは作成の確認は行わず、パッケージ本体の作成に進
みます。
3.1.3
パッケージ本体
パッケージ本体の
本体の作成
パッケージ本体の DDL は、db2plsql_package_body.db2.account_package ファイルに格納されて
います。パッケージ本体についても、Oracle で稼働していた PL/SQL のロジックがそのまま使
用できます。ただし、パッケージ本体に含まれるプロシージャー中で参照している関数
（AVERAGE_BAND）の名前解決をするため、関数の検索順序に SALES スキーマを追加する必
要があります。
＿パッケージ本体の DDL 修正
vi エディターから db2plsql_package_body.db2.account_package ファイルを開き、下記の DDL の
ように「SET CURRENT PATH = SALES, CURRENT PATH/」を追加してください。
ACCOUNT_PACKAGE パッケージ本体の DDL（一部抜粋）
11
$ 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
正常にパッケージ本体が作成されれば、「DB20000I SQL コマンドが正常に完了しました。」が
メッセージとして返却されます。
12
＿パッケージの稼働確認
ACCOUNT_PACKAGE のメンバーの一つ、プロシージャーDisplay_Account_List を動かしてみる
ことで、パッケージの稼働確認を行います。/workshop/lab5 ディレクトリで下記のコマンドを実
行してください。
db2 -tvf call.display_account_list.clp
このコマンドは、下記の様なスクリプトを含んでいます。
CONNECT TO LABDB;
SET SQLCOMPAT PLSQL;
SET CURRENT SCHEMA SALES;
SET SERVEROUTPUT ON;
BEGIN
sales.account_package.Display_Account_List( 'C01', 3 );
END;
/
TERMINATE;
BEGIN/END で囲まれた部分が無名ブロックを構成しており、ここから Display_Account_List を
呼び出しています。
実行例
db2inst1@db2V97onSLES10:/> cd /workshop/lab5
db2inst1@db2V97onSLES10:/workshop/lab5> db2 -tvf call.display_account_list.clp
<省略>
SET SERVEROUTPUT ON
DB20000I SET SERVEROUTPUT コマンドが正常に完了しました。
BEGIN
sales.account_package.Display_Account_List( 'C01', 3 );
END;
DB20000I
SQL コマンドが正常に完了しました。
List of employees
---------------------------Record id
:
Employee
:
Number of projects
:
Average Band in department :
Record id
:
Employee
:
Number of projects
:
Average Band in department :
Record id
:
Employee
:
Number of projects
:
Average Band in department :
Record id
:
Employee
:
Number of projects
:
Average Band in department :
1
KWAN
1
C
2
QUINTANA
2
C
3
NICHOLLS
4
C
4
NATZ
1
C
TERMINATE
DB20000I TERMINATE コマンドが正常に完了しました。
上記のように、4 エントリー分の社員情報が表示されれば正常に稼働しています。
13
3.2
パッケージ OFFICE_PACKAGE の作成
このステップでは、パッケージ「OFFICE_PACKAGE」を作成します。このパッケージは OFFICE
表に対するデータの登録と削除を行うプロシージャーを含んでいます。パッケージのメンバー
は下記の 2 プロシージャーです。
Add_Office
Remove_Office
先ほど作成した ACCOUNT_PACKAGE では、PL/SQL のソースコードはそのまま使用できまし
たが、この OFFICE_PACKAGE は、そのままでは DB2 上で作成できない記述を含んでいます。
次ページに表示した、Remove_Office プロシージャーの青字部分がそれに該当します。
Remove_Office プロシージャーはパッケージ OFFICE_PACKAGE に属するパッケージですが、
自分自身の内部に別の関数 CHECK_EMP_EXISTANCE の宣言を含んでいます。
CHECK_EMP_EXISTANCE は、削除対象のレコードが存在するかどうかを確認する関数として
定義されており、Remove_Office プロシージャーからの内部からのみ参照できる関数です。
このように、関数やプロシージャーの内部で宣言される関数・及びプロシージャーを「ネストされ
た関数」、「ネストされたプロシージャー」と呼び、DB2 9.7 ではサポートされていません。
そのため、このような関数やプロシージャーを DB2 に対して作成するためには、ネストされた
関数やプロシージャー部分を分離して、独立した関数もしくはプロシージャーとして作成する必
要があります。独立したオブジェクトとして作成する際に考慮する必要があるのは下記の 2 点
です。
１．関数名、プロシージャー名の重複
ネストされた関数、プロシージャーは、その定義を含む関数やプロシージャーの内部から
しか参照できません。そのため、例えば、別々のプロシージャーの中に定義されたネスト
された関数が同じ名前であったとしても問題ありません。独立したオブジェクトとして作成
する場合、一つのスキーマ配下に同じ名前の関数は一つしか存在できません。
そのため、どちらかの名前を変更する必要があります。また、名前を変更した関数につい
ては、呼び出し元のソースコードについても変更する必要があります。
２．変数の共有について
ネストされた関数・プロシージャーと、その定義を含む関数やプロシージャーでは、変数を
共有することが可能です。ところが、独立したオブジェクト同士では変数の共有ができま
せん。
上記の課題を解決するために推奨される方策は、「パッケージへの変更」です。パッケージを使
用した場合、パッケージ仕様部のメンバーとしてグローバル変数を宣言することで、複数の関
数やプロシージャーから変数を共有することが可能です。
14
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, -20060);
-- /*
-- ||----------------------------------------------------------------------- || 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;
BEGIN
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;
BEGIN
-- 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
3.2.1
パッケージ仕様部
パッケージ仕様部の
仕様部の作成
前述の通り、OFFICE_PACKAGE を DB2 で作成するために、ネストされた関数
CHECK_EMP_EXISTANCE を、独立した関数に変更します。
パッケージ仕様部で宣言するメンバーは下記の様に変更となります。
変更前：
Add_Office（プロシージャー）
Remove_Office（プロシージャー）
変更後
Add_Office（プロシージャー）
Remove_Office（プロシージャー）
CHECK_EMP_EXISTANCE（関数）
＿DDL の編集
ｖｉエディターで 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
p_building
p_number_seats
p_description
PROCEDURE Remove_Office(p_office_id
IN
IN
IN
IN
offices.office_id%TYPE,
offices.building%TYPE,
offices.number_seats%TYPE,
offices.description%TYPE);
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
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
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, -20060);
BEGIN
-- 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;
BEGIN
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
＿パッケージ本体の作成
DDL の編集が完了した後に、下記のコマンドを使用してパッケージ本体を作成してください。
db2 -tvf db2plsql_package_body.db2.office_package
正常にパッケージ本体が作成されれば、DB20000I メッセージが返却されます。
＿パッケージの稼働確認
下記のスクリプトを使用して、パッケージが正常に稼働しているかどうかを確認します。
cd /workshop/lab5
db2 -tvf call.office_package.clp
このスクリプトは下記の様な SQL を含んでおり、Add_Office プロシージャーと Remove_Office
プロシージャーを使用してオフィスの登録と削除を行います。
SELECT * FROM OFFICES;
BEGIN
sales.office_package.add_office( 3, '46 E. Main Street', 2, 'Temporary office in Hotel');
END
/
SELECT * FROM OFFICES;
BEGIN
sales.office_package.Remove_Office( 3);
END
/
SELECT * FROM OFFICES;
正常にパッケージが作成されている場合、次ページの実行例のようにオフィス ID=3 のオフィス
が追加・削除されることが確認できます。
19
OFFICE_PACKAGE の実行例
db2inst1@db2V97onSLES10:/workshop/lab5> db2 -tvf call.office_package.clp
<省略>
SELECT * FROM OFFICES
OFFICE_ID
--------1.
2.
BUILDING
NUMBER_SEATS DESCRIPTION
------------------ ------------ ----------------------------------------123 Offshore Drive
30. Main small office rented by the company
2348 Park Avenue
40. Additional facility for new hires
2 レコードが選択されました。
BEGIN
sales.office_package.add_office( 3, '46 E. Main Street', 2, 'Temporary office in
Hotel');
END
DB20000I
SQL コマンドが正常に完了しました。
Office 46 E. Main Street successfully registered .
SELECT * FROM OFFICES
OFFICE_ID
--------1.
2.
3.
BUILDING
NUMBER_SEATS DESCRIPTION
------------------ ------------ ----------------------------------------123 Offshore Drive
30. Main small office rented by the company
2348 Park Avenue
40. Additional facility for new hires
46 E. Main Street
2. Temporary office in Hotel
3 レコードが選択されました。
BEGIN
sales.office_package.Remove_Office( 3);
END
DB20000I
SQL コマンドが正常に完了しました。
SELECT * FROM OFFICES
OFFICE_ID
--------1.
2.
BUILDING
NUMBER_SEATS DESCRIPTION
------------------ ------------ ----------------------------------------123 Offshore Drive
30. Main small office rented by the company
2348 Park Avenue
40. Additional facility for new hires
2 レコードが選択されました。
20
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 のパラメーター確認
パラメーター確認
＿パラメーターの確認
最初に/workshop/lab5/plsql_ddl ディレクトリで以下のコマンドを実行し、COUNT_PROJECTS
ファンクションの DDL を確認します。
cat db2plsql_function.db2.count_projects
DDL の確認では、特にこのファンクションのパラメーターを確認します。上記のコマンドの実行
例の青字部分になります。
このファンクションでは、IN パラメーターと OUT パラメーターを 1 つずつ持ち、NUMBER の戻り
値を返します。
21
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl> 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"
"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 の以下の点を書き換えます。（修正したファイルは
/workshop/lab5/plsql_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
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
-- /*
・・・<省略>・・・
-- */
＜省略＞
BEGIN
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
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_function.db2.count_projects
CONNECT TO LABDB
データベース接続情報
データベース・サーバー
SQL 許可 ID
ローカル・データベース別名
= DB2/LINUX 9.7.0
= DB2INST1
= LABDB
SET CURRENT SCHEMA = 'SALES'
DB20000I SQL コマンドが正常に完了しました。
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;
DB20000I SQL コマンドが正常に完了しました。
TERMINATE
DB20000I TERMINATE コマンドが正常に完了しました。
CREATE OR REPLACE PROCEDURE 文の実行後に「DB20000I SQL コマンドが正常に完了し
ました」が表示されることを確認します。
＿COUNT_PROJECTS プロシージャーの稼動確認
下記のスクリプトを使用して、パッケージが正常に稼働しているかどうかを確認します。
cd /workshop/lab5
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
o_num NUMBER;
BEGIN
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:/workshop/lab5/plsql_ddl_modified> cd /workshop/lab5
db2inst1@DB2V97onSLES10:/workshop/lab5> db2 -tvf call.count_projects.clp
CONNECT TO LABDB
データベース接続情報
データベース・サーバー
SQL 許可 ID
ローカル・データベース別名
= DB2/LINUX 9.7.0
= DB2INST1
= LABDB
SET SQLCOMPAT PLSQL
DB20000I SET SQLCOMPAT コマンドが正常に完了しました。
SET CURRENT SCHEMA SALES
DB20000I SQL コマンドが正常に完了しました。
SET SERVEROUTPUT ON
DB20000I SET SERVEROUTPUT コマンドが正常に完了しました。
DECLARE
i_emp_id NUMBER:=15;
o_acct_id employees.acct_id%TYPE;
o_num NUMBER;
BEGIN
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;
DB20000I SQL コマンドが正常に完了しました。
Employee ID
: 15
Related account ID: 5
Total projects
: 4
TERMINATE
DB20000I TERMINATE コマンドが正常に完了しました。
25
4.4
ADD_NEW_EMPLOYEE プロシージャーの
プロシージャーの作成
ADD_NEW_EMPLOYEE プロシージャーは Oracle で稼働していた PL/SQL のロジックがそのま
ま使用できます。ただし、プロシージャー中で参照しているパッケージの変数
（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
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_procedure.db2.add_new_employee
CONNECT TO LABDB
データベース接続情報
データベース・サーバー
SQL 許可 ID
ローカル・データベース別名
= DB2/LINUX 9.7.0
= DB2INST1
= LABDB
SET CURRENT SCHEMA = 'SALES'
DB20000I SQL コマンドが正常に完了しました。
SET CURRENT PATH = SALES, CURRENT PATH
DB20000I SQL コマンドが正常に完了しました。
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;
DB20000I SQL コマンドが正常に完了しました。
＿ADD_NEW_EMPLOYEE プロシージャーの稼動確認
下記のスクリプトを使用して、パッケージが正常に稼働しているかどうかを確認します。
cd /workshop/lab5
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
BEGIN
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:/workshop/lab5> db2 -tvf call.add_new_employee.clp
CONNECT TO LABDB
・・・<省略>・・・
alter sequence employee_sequence restart with 999
DB20000I SQL コマンドが正常に完了しました。
delete from employees where emp_id = 1000
DB20000I SQL コマンドが正常に完了しました。
select emp_id,first_name,last_name,band from employees where first_name = 'MAX'
EMP_ID FIRST_NAME
LAST_NAME
BAND
------- -------------------- -------------------- ---0 レコードが選択されました。
DECLARE
emp_info sales.ENVELOPE.EMP_INFO_TYPE;
BEGIN
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;
DB20000I SQL コマンドが正常に完了しました。
EMP_ID
FIRST_NAME
LAST_NAME
BAND
HIRE DATE
:1000
:MAX
:TRENTON
:1
:2009-09-12-06.17.03.000
select emp_id,first_name,last_name,band from employees where first_name = 'MAX'
EMP_ID FIRST_NAME
LAST_NAME
BAND
------- -------------------- -------------------- ---1000. MAX
TRENTON
1
1 レコードが選択されました。
28
4.5 ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャー
の作成
ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーは Oracle で稼働していた PL/SQL
のロジックがそのまま使用できます。
以下のコマンドを実行して、ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーを作成し
ます。
db2 -tvf db2plsql_procedure.db2.assign_employee_to_new_account
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl_modified> db2 -tvf
db2plsql_procedure.db2.assign_employee_to_new_account
CONNECT TO LABDB
データベース接続情報
データベース・サーバー
SQL 許可 ID
ローカル・データベース別名
= DB2/LINUX 9.7.0
= DB2INST1
= LABDB
SET CURRENT SCHEMA = 'SALES'
DB20000I SQL コマンドが正常に完了しました。
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;
DB20000I SQL コマンドが正常に完了しました。
＿ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーの稼動確認
下記のスクリプトを使用して、パッケージが正常に稼働しているかどうかを確認します。
cd /workshop/lab5
db2 -tvf call.assign_employee_to_new_account.clp
29
このスクリプトは下記の様な 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;
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5> 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
------4
5
DEPT_CODE MAX_EMPLOYEES CURRENT_EMPLOYEES
--------- ------------- ----------------E21
3.
2.
D11
4.
4.
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
------- -------------------- -------------------- --------- ------15. 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
------4
5
DEPT_CODE MAX_EMPLOYEES CURRENT_EMPLOYEES
--------- ------------- ----------------E21
3.
3.
D11
4.
3.
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
------- -------------------- -------------------- --------- ------15. BRUCE
ADAMSON
E21
4.
30
5.
View の作成
この章では、以下の View を作成します。
ORGANIZATION_STRUCTURE"
この View の DDL では、Oracle 固有の FORCE オプションが指定されています。このオプション
は、View に依存関係を持つオブジェクトが View 作成時になくても View の作成を行えるように
するオプションです。
DB2 9.7 では FORCE オプションは CREATE VIEW 文で記述できないため、このオプションを取
り除いて実行する必要があります。
＿DDL の確認
/workshop/lab5/plsql_ddl ディレクトリにある db2plsql_views.db2.organization_structure ファイル
の内容を以下のコマンドを実行して確認します。
cat db2plsql_views.db2.organization_structure
FORCE オプションがついていることを確認します。
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl> 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 ファイルを修正します。
（/workshop/lab5/plsql_ddl_modified ディレクトリに修正済みのファイルがあります。お時間のな
い方や vi の操作に不慣れな方はこちらをご使用ください。）
31
修正点
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:/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_views.db2.organization_structure
CONNECT TO LABDB
データベース接続情報
データベース・サーバー
SQL 許可 ID
ローカル・データベース別名
= DB2/LINUX 9.7.0
= DB2INST1
= LABDB
SET CURRENT SCHEMA = 'SALES'
DB20000I SQL コマンドが正常に完了しました。
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
DB20000I SQL コマンドが正常に完了しました。
32
6.
トリガーの
トリガーの作成
この章では Update_Departments,UPDATE_ACC_ON_NEW_EMPL の２つのトリガーを作成します。
6.1
Update_Departments トリガーの
トリガーの作成
Update_Departments トリガーは複数のアクションで起動するトリガーです。具体的には、
INSERT、UPDATE、DELETE 全ての変更処理に対して起動します。DB2 9.7 ではこのようなトリ
ガーをサポートしていません。DB2 で使用できるようにするためには、それぞれのアクションで
起動するトリガーとして分割して作成します。（例 INSERT トリガー、UPDATE トリガー、DELETE
トリガーの３つに分割）
＿UPDATE＿DEPARTMENTS トリガーの確認
最初に Update_DEPARTMENTS トリガーの DDL を確認します。
以下のコマンドを実行し、/workshop/lab5/pl_sql_ddl ディレクトリにある
db2plsql_trigger.db2.update_departments の内容を確認します。
cat db2plsql_trigger.db2.update_departments
青字になっている部分がトリガーの起動アクションの設定になります。
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl> cat db2plsql_trigger.db2.update_departments
・・・<省略>・・・
CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS"
・・・<省略>・・・
AFTER INSERT OR DELETE OR UPDATE ON employees
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;
BEGIN
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
ここでは、UPDATE_DEPARTMENTS トリガーを以下の３つのトリガーに分割して作成します。
UPDATE_DEPARTMENTS_ON_UPDATE
UPDATE_DEPARTMENTS_ON_INSERT
UPDATE_DEPARTMENTS_ON_DELETE
＿UPDATE トリガーの作成
db2plsql_trigger.db2.update_departments ファイルを
db2plsql_trigger.db2.update_departments_update ファイルとしてコピーし以下の点を修正します。
（修正済みのファイルは、/workshop/lab5/plsql_ddl_modified に用意してあります。お時間がな
い方や vi の操作が不慣れな方はこちらをご使用ください。）
修正点
１・トリガー名の変更
UPDATE_DEPARTMENTS→ UPDATE_DEPARTMENTS_ON_UPDATE
２．トリガーの起動アクションの変更
AFTER INSERT OR DELETE OR UPDATE→ AFTER UPDATE
３．UPDATE 時以外の処理をコメントアウト
修正例（修正点は青字部分）
--#SET TERMINATOR /
・・・<省略>・・
--#SET :TRIGGER:SALES:UPDATE_DEPARTMENTS
CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_UPDATE
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;
BEGIN
-- IF DELETING THEN
-UPDATE departments SET (total_projects, total_employees)=
-(SELECT
(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
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;
/
・・・
34
修正後、以下のコマンドで DDL を実行し、トリガーを作成します。
db2 -tvf db2plsql_trigger.db2.update_departments_update
DB20000I メッセージが表示されトリガーが正常に作成されたことを確認します。
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_trigger.db2.update_departments_update
CONNECT TO LABDB
データベース接続情報
データベース・サーバー
SQL 許可 ID
ローカル・データベース別名
= DB2/LINUX 9.7.0
= DB2INST1
= LABDB
SET CURRENT SCHEMA = 'SALES'
DB20000I SQL コマンドが正常に完了しました。
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;
DB20000I SQL コマンドが正常に完了しました。
TERMINATE
DB20000I TERMINATE コマンドが正常に完了しました。
35
＿INSERT トリガーの作成
db2plsql_trigger.db2.update_departments ファイルを
db2plsql_trigger.db2.update_departments_insert ファイルとしてコピーし以下の点を修正します。
（修正済みのファイルは、/workshop/lab5/plsql_ddl_modified に用意してあります。お時間がな
い方や vi の操作が不慣れな方はこちらをご使用ください。）
修正点
１・トリガー名の変更
UPDATE_DEPARTMENTS→ UPDATE_DEPARTMENTS_ON_INSERT
２．トリガーの起動アクションの変更
AFTER INSERT OR DELETE OR UPDATE→ AFTER INSERT
３．INSERT 時以外の処理をコメントアウト
修正例（修正点は青字部分）
・・・<省略>・・・
CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_INSERT
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;
BEGIN
-- IF DELETING THEN
-UPDATE departments SET (total_projects, total_employees)=
-(SELECT
(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
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_trigger.db2.update_departments_insert
CONNECT TO LABDB
・・・<省略>・・・
CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_INSERT"
・・・＜省略＞・・・
BEGIN
-- 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;
DB20000I SQL コマンドが正常に完了しました。
・・・
37
＿DELETE トリガーの作成
db2plsql_trigger.db2.update_departments ファイルを
db2plsql_trigger.db2.update_departments_delete ファイルとしてコピーし以下の点を修正します。
（修正済みのファイルは、/workshop/lab5/plsql_ddl_modified に用意してあります。お時間がな
い方や vi の操作が不慣れな方はこちらをご使用ください。）
修正点
１・トリガー名の変更
UPDATE_DEPARTMENTS→ UPDATE_DEPARTMENTS_ON_DELETE
２．トリガーの起動アクションの変更
AFTER INSERT OR DELETE OR UPDATE→ AFTER DELETE
３．DELETE 時以外の処理をコメントアウト
修正例（修正点は青字部分）
--#SET TERMINATOR /
・・・<省略>・・・
CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_DELETE
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;
BEGIN
-- 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
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_trigger.db2.update_departments_delete
CONNECT TO LABDB
・・・<省略>・・・
CREATE OR REPLACE TRIGGER "SALES"."UPDATE_DEPARTMENTS_ON_DELETE"
-- /*
・・・＜省略＞・・・
BEGIN
-- 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;
DB20000I SQL コマンドが正常に完了しました。
・・・
39
6.2
UPDATE_ACC_ON_NEW_EMPL トリガーの
トリガーの作成
UPDATE_ACC_ON_NEW_EMPL トリガーは、UPDATE_DEPARTMENTS トリガーのように複数の
操作に対して起動するトリガーではなく、INSERT に対してのみ起動するトリガーであり、DB2
9.7 でもそのまま稼動させることが可能です。
/workshop/lab5/plsql_ddl ディレクトリにある db2plsql_trigger.db2.update_acc_on_new_empl ファ
イルの DDL を以下のコマンドで確認します。
cat db2plsql_trigger.db2.update_acc_on_new_empl
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl> 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;
BEGIN
-- 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
UPDATE
SET
WHERE
AND
one to the number of employees in the project.
accounts
current_employees = current_employees + 1
dept_code = :new.dept_code
acct_id = :new.acct_id;
END Update_Acc_On_New_Empl;
/
以下のコマンドで DDL を実行し、トリガーを作成します。
db2 -tvf db2plsql_trigger.db2.update_acc_on_new_empl
DB20000I メッセージが表示されトリガーが正常に作成されたことを確認します。
40
実行例
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl> 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
UPDATE
SET
WHERE
AND
one to the number of employees in the project.
accounts
current_employees = current_employees + 1
dept_code = :new.dept_code
acct_id = :new.acct_id;
END Update_Acc_On_New_Empl;
DB20000I SQL コマンドが正常に完了しました。
以上です。
41
© Copyright IBM Corporation 2009
All Rights Reserved.
本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布され
るものです。
この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用
する環境に統合する使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べら
れていますが、他のところで同じまたは同様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使
用先は、自己の責任において行う必要があります。
42

Download Report