[Lab 5]
[Lab 5]
[Lab 5]
[Lab 5]
PL/SQL
PL/SQL
PL/SQL
PL/SQL
オブジェクト
オブジェクト
オブジェクト
オブジェクトの
の
の
の移行
移行
移行
移行
2009
2009
2009
2009
年
年
年
年
12
12
12
12
月
月
月
月
日本
日本
日本
日本アイ
アイ
アイ
アイ･
･
･
･ビー
ビー･
ビー
ビー
･
･
･エム
エム
エム株式会社
エム
株式会社
株式会社
株式会社
Content
Content
Content
Contents
s
s
s
CONTENTS
CONTENTS
CONTENTS
CONTENTS ... 2
1.
1.
1.
1.
はじめに
はじめに ... 3
はじめに
はじめに
1.1
ハンズオンで使用する DDL について
...3
2.
2.
2.
2.
独立
独立した
独立
独立
した
した
したタイプ
タイプの
タイプ
タイプ
の
の
の宣言
宣言
宣言 ... 4
宣言
2.1
スタンドアロン・タイプの移行
...4
2.2
パッケージ Envelope の作成
...6
2.3
タイプを参照する箇所の修正
...6
3.
3.
3.
3.
パッケージ
パッケージの
パッケージ
パッケージ
の
の
の作成
作成
作成
作成... 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.
4.
4.
4.
プロシージャー
プロシージャーの
プロシージャー
プロシージャー
の
の
の作成
作成
作成 ... 21
作成
4.1
COUNT_PROJECTS のパラメーター確認
...21
4.2
COUNT_PROJECTS のプロシージャーへの書き直し
...22
4.3
COUNT_PROJECTS プロシージャーの作成
...23
4.4
ADD_NEW_EMPLOYEE プロシージャーの作成
...26
4.5
ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT
プロシージャーの作成
...29
5.
5.
5.
5.
VIEW
VIEW
VIEW
VIEW
の
の
の
の作成
作成
作成
作成 ... 31
6.
6.
6.
6.
トリガー
トリガーの
トリガー
トリガー
の
の作成
の
作成
作成
作成 ... 33
6.1
Update_Departments トリガーの作成
...33
1
1
1
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
1
1.
.1
.
.
1
1
1
ハンズオン
ハンズオン
ハンズオン
ハンズオンで
で
で使用
で
使用する
使用
使用
する
する
する
DDL
DDL
DDL
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
ディレクトリに格納されています。
2
2
2
2.
.
.
.
独立
独立
独立
独立した
したタイプ
した
した
タイプ
タイプの
タイプ
の
の
の宣言
宣言
宣言
宣言
このセクションでは、スタンドアロン・タイプを取り扱います。「スタンドアロン・タイプ」とは、
PL/SQL オブジェクトとして作成されるタイプの中で、パッケージ中で宣言されるのではなく、独
立して宣言されるものです。
このハンズオンには
1 つのスタンドアロン・タイプが含まれます。IBM Data Movement Tool
は
下記の 2 つのファイルにスタンドアロン・タイプを抽出します。
db2plsql_type.db2
db2plsql_type_body.db2
DB2 9.7 はスタンドアロン・タイプをサポートしていないため、別のオブジェクトとして作成する必
要があります。移行先のオブジェクトとして、パッケージが使用可能です。パッケージは、プロシ
ージャーやファンクション、タイプや変数の宣言をまとめて取り扱うための単位となります。パッ
ケージの一要素としてタイプを宣言することで、独立したタイプと同様にタイプの作成が可能に
なります。ただし、そのタイプを使用する際はパッケージの名称をスキーマ（修飾子）として追加
する必要があります。
2
2
2
2.
.
.1
.
1
1
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' /
＿パッケージ 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 をコピーします
３．前述の変更を行います
変更例
このハンズオンでは、vi
エディターを使用して DDL を編集することを想定していますが、時間の
ない方や vi の操作に不慣れな方は、/workshop/lab5/plsql_ddl_modified/ディレクトリーに修正
済みの DDL がありますので、こちらを使用していただく事も可能です。このハンズオンで今後
登場する DDL は、全て同様に修正済みの 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 /
2
2
2
2.
.
.
.2
2
2
2
パッケージ
パッケージ
パッケージ
パッケージ Envelope
Envelope
Envelope
Envelope
の
の作
の
の
作
作
作成
成
成
成
前ステップで DDL の変更が完了したので、パッケージ
ENVELOPE を作成します。
下記のコマンドを実行して、パッケージを作成してください。
db2 -tvf db2plsql_package.db2.envelope
実行例
2
2
2
2.
.
.3
.
3
3
3
タイプ
タイプ
タイプ
タイプを
を
を
を参照
参照する
参照
参照
する
する
する箇所
箇所
箇所の
箇所
の
の修正
の
修正
修正
修正
EMP_INFO_TYPE を ENVELOPE パッケージ内での宣言に変更したことで、EMP_INFO_TYPE を
参照する際には
ENVELOPE を修飾子として追加する必要があります。下記のコマンドを実行
して、移行対象の PL/SQL オブジェクトから、EMP_INFO_TYPE を参照している部分を確認して
ください。
cd /workshop/lab5/plsql_ddl
/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 コマンドが正常に完了しました。
コマンド実行例
この結果から、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.
ENVELOPE.
ENVELOPE.EMP_INFO_TYPE,
ENVELOPE.
ここで編集した DDL は、後続のステップで使用します。
/> 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 <==
3
3
3
3.
.
.
.
パッケージ
パッケージ
パッケージ
パッケージの
の
の作成
の
作成
作成
作成
この章では、パッケージの作成を行います。先ほどの演習でも登場しましたが、パッケージとは
プロシージャーや関数、タイプや変数の宣言を一括で取り扱うための単位です。パッケージは、
パッケージ仕様部およびパッケージ本体という、2 つの主要コンポーネントで構成されます。
パッケージ仕様部 はパッケージの外部に対して公開されたパブリック・インターフェースで、当
該パッケージの外部から参照可能な要素で構成されます。パッケージ仕様部を作成するには、
CREATE PACKAGE ステートメントを実行します。
パッケージ本体 には、パッケージ仕様部内で宣言されたプロシージャーおよび関数の、実際の
振る舞いを定義するための実装を記述します。また、それ以外にもパッケージ気外部に公開し
ないプライベートのタイプ、変数、およびカーソルの宣言を行うことができます。パッケージ本体
を作成するには、CREATE PACKAGE BODY ステートメントを実行します。
この章では、下記に示す 2 つのパッケージを作成します。
ACCOUNT_PACKAGE
OFFICE_PACKAGE
3
3
3
3.
.1
.
.
1
1
1
パッケージ
パッケージ ACCOUNT_PACKAGE
パッケージ
パッケージ
ACCOUNT_PACKAGE
ACCOUNT_PACKAGE
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
3
3
AVERAGE_BNAD
関数の DDL
この関数は、ソースコードの変更無しにそのまま DB2 で作成可能です。下記のコマンドを使用
して、AVERAGE_BNAD 関数を作成してください。
db2 -tvf db2plsql_function.db2.average_band
--#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; /
実行例
この関数が正常に作成できたことを、/workshop/lab5 ディレクトリで下記のスクリプトで確認し
てください。
db2 -tvf call.average_band.clp
このスクリプトは、下記の様な無名ブロックを含みます。この無名ブロックでは部署コード「A00」、
アカウントコード「1」を引数として AVERAGE_BNAD 関数を呼び出します。下記の実行例のよう
に、結果として「C」1 文字が返却されれば成功です。
実行例
3
3
3
3.
.1
.
.
1
1
1.
.
.
.2
2
2
2
パッケージ仕様部
パッケージ
パッケージ
パッケージ
仕様部
仕様部
仕様部の
の
の
の作成
作成
作成
作成
ACCOUNT_PACKAGE のパッケージ仕様部を作成します。パッケージ仕様部は下記の様な
DDL となります。
$ 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
コマンドが正常に完了しました。
$ 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;
ACCOUNT_PACKAGE パッケージ宣言部の DDL
下記のコマンドを実行して、パッケージ仕様部を作成してください。
db2 -tvf db2plsql_package.db2.account_package
正常にパッケージ仕様部が作成されれば、「DB20000I SQL コマンドが正常に完了しました。」
がメッセージとして返却されます。ここでは作成の確認は行わず、パッケージ本体の作成に進
みます。
3
3
3
3.
.1
.
.
1
1
1.
.
.
.3
3
3
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（一部抜粋）
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_body.db2.account_package
正常にパッケージ本体が作成されれば、「DB20000I SQL コマンドが正常に完了しました。」が
メッセージとして返却されます。
$ 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 <省略>
＿パッケージの稼働確認
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 を
呼び出しています。
実行例
上記のように、4 エントリー分の社員情報が表示されれば正常に稼働しています。
db2inst1@db2V97onSLES10:/> cd /workshop/lab5 db2inst1@db2V97onSLES10:/workshop/lab5> db2 -tvf call.display_account_list.clp <省略> SET SERVEROUTPUT ONDB20000I SET SERVEROUTPUT コマンドが正常に完了しました。 BEGIN sales.account_package.Display_Account_List( 'C01', 3 ); END; DB20000I SQL コマンドが正常に完了しました。 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
3
3
3
3.
.
.2
.
2
2
2
パッケージ
パッケージ
パッケージ
パッケージ
OFFICE_PACKAGE
OFFICE_PACKAGE
OFFICE_PACKAGE
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 点
です。
１．
関数名、プロシージャー名の重複
ネストされた関数、プロシージャーは、その定義を含む関数やプロシージャーの内部から
しか参照できません。そのため、例えば、別々のプロシージャーの中に定義されたネスト
された関数が同じ名前であったとしても問題ありません。独立したオブジェクトとして作成
する場合、一つのスキーマ配下に同じ名前の関数は一つしか存在できません。
そのため、どちらかの名前を変更する必要があります。また、名前を変更した関数につい
ては、呼び出し元のソースコードについても変更する必要があります。
２．
変数の共有について
ネストされた関数・プロシージャーと、その定義を含む関数やプロシージャーでは、変数を
共有することが可能です。ところが、独立したオブジェクト同士では変数の共有ができま
せん。
上記の課題を解決するために推奨される方策は、「パッケージへの変更」です。パッケージを使
用した場合、パッケージ仕様部のメンバーとしてグローバル変数を宣言することで、複数の関
数やプロシージャーから変数を共有することが可能です。
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;
3
3
3
3.
.2
.
.
2
2
2.
.
.
.1
1
1
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 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; /
3
3
3
3.
.2
.
.
2
2
2.
.
.
.2
2
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
定義が除去されている
ことがわかります。
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;
＿パッケージ本体の作成
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 のオフィス
OFFICE_PACKAGE の実行例
db2inst1@db2V97onSLES10:/workshop/lab5> db2 -tvf call.office_package.clp <省略>
SELECT * FROM OFFICES
OFFICE_ID BUILDING NUMBER_SEATS DESCRIPTION
--- --- --- --- 1. 123 Offshore Drive 30. Main small office rented by the company 2. 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 BUILDING NUMBER_SEATS DESCRIPTION
--- --- --- --- 1. 123 Offshore Drive 30. Main small office rented by the company 2. 2348 Park Avenue 40. Additional facility for new hires
3. 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 BUILDING NUMBER_SEATS DESCRIPTION
--- --- --- --- 1. 123 Offshore Drive 30. Main small office rented by the company 2. 2348 Park Avenue 40. Additional facility for new hires 2 レコードが選択されました。
4
4
4
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
4
4
4.
.1
.
.
1
1
1
COUNT_PROJECTS
COUNT_PROJECTS
COUNT_PROJECTS
COUNT_PROJECTS
の
のパラメーター
の
の
パラメーター
パラメーター
パラメーター確認
確認
確認
確認
＿パラメーターの確認
最初に/workshop/lab5/plsql_ddl
ディレクトリで以下のコマンドを実行し、COUNT_PROJECTS
ファンクションの DDL を確認します。
cat db2plsql_function.db2.count_projects
DDL の確認では、特にこのファンクションのパラメーターを確認します。上記のコマンドの実行
例の青字部分になります。
このファンクションでは、IN パラメーターと
OUT パラメーターを 1 つずつ持ち、NUMBER
の戻り
実行例
4
4
4
4.
.
.2
.
2
2
2
COUNT_PROJECTS
COUNT_PROJECTS
COUNT_PROJECTS
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"
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"."COU CREATE OR REPLACE FUNCTION "SALES"."COU CREATE OR REPLACE FUNCTION "SALES"."COU
CREATE OR REPLACE FUNCTION "SALES"."COUNT_PROJECTS" (p_empID IN employees.emp_ID%TYPE, o_acct_id OUT NT_PROJECTS" (p_empID IN employees.emp_ID%TYPE, o_acct_id OUT NT_PROJECTS" (p_empID IN employees.emp_ID%TYPE, o_acct_id OUT NT_PROJECTS" (p_empID IN employees.emp_ID%TYPE, o_acct_id OUT employees.acct_id%TYPE)
employees.acct_id%TYPE)employees.acct_id%TYPE) employees.acct_id%TYPE) RETURN NUMBER AS RETURN NUMBER ASRETURN NUMBER AS 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; ＜省略＞
48 行目の RETURN をアウトパラメーターへの値の代入に書き換えます。
変更前
RETURN v_Totalprojects;
変更後
out_return:= v_Totalprojects;
修正例（青字部分が修正箇所）
4
4
4
4.
.3
.
.
3
3
3
COUNT_PROJECTS
COUNT_PROJECTS
COUNT_PROJECTS
COUNT_PROJECTS
プロシージャー
プロシージャーの
プロシージャー
プロシージャー
の
の作成
の
作成
作成
作成
＿プロシージャーの作成
修正した DDL を以下のコマンドで実行し、プロシージャーを作成します。
db2 -tvf db2plsql_function.db2.count_projects
--#SET TERMINATOR / CONNECT TO LABDB /SET CURRENT SCHEMA = 'SALES' /
--#SET :FUNCTION:SALES:COUNT_PROJECTS
CREATE OR REPLACE PROCEDURE "COUNT_PROJECTS" CREATE OR REPLACE PROCEDURE "COUNT_PROJECTS" CREATE OR REPLACE PROCEDURE "COUNT_PROJECTS" CREATE OR REPLACE PROCEDURE "COUNT_PROJECTS" (p_empID IN employees.emp_ID%TYPE, (p_empID IN employees.emp_ID%TYPE, (p_empID IN employees.emp_ID%TYPE, (p_empID IN employees.emp_ID%TYPE, o_acct_id OUT employees.acct_id%TYPE, o_acct_id OUT employees.acct_id%TYPE, o_acct_id OUT employees.acct_id%TYPE, o_acct_id OUT employees.acct_id%TYPE,
out_return OUT NUMBER)out_return OUT NUMBER)out_return OUT NUMBER)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; out_return:= v_Totalprojects; out_return:= v_Totalprojects; out_return:= v_Totalprojects; END Count_Projects; / TERMINATE /
実行例
CREATE OR REPLACE PROCEDURE 文の実行後に「DB20000I SQL コマンドが正常に完了し
ました」が表示されることを確認します。
＿COUNT_PROJECTS プロシージャーの稼動確認
下記のスクリプトを使用して、パッケージが正常に稼働しているかどうかを確認します。
cd /workshop/lab5
db2 -tvf call.count_projects.clp
このスクリプトは下記の様な SQL を含んでおり、COUNT_PROJECTS プロシージャーを呼び出
してアウトパラメーターに返された値を表示します。
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_function.db2.count_projects CONNECT TO LABDB データベース接続情報 データベース・サーバー = DB2/LINUX 9.7.0 SQL 許可 ID = DB2INST1 ローカル・データベース別名 = LABDBSET 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 コマンドが正常に完了しました。
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 データベース接続情報 データベース・サーバー = DB2/LINUX 9.7.0 SQL 許可 ID = DB2INST1 ローカル・データベース別名 = LABDB SET SQLCOMPAT PLSQLDB20000I 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
4
4
4
4.
.4
.
.
4
4
4
ADD_NEW_EMPLOYEE
ADD_NEW_EMPLOYEE
ADD_NEW_EMPLOYEE
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
ファイル修正例（一部抜粋）
修正後、以下のコマンドを実行して ADD_NEW_EMPLOYEE プロシージャーを作成します。
db2 -tvf db2plsql_procedure.db2.add_new_employee
--#SET TERMINATOR / CONNECT TO LABDB /SET CURRENT SCHEMA = 'SALES' /
SET CURRENT PATH = SALES, CURRENT PATH SET CURRENT PATH = SALES, CURRENT PATH SET CURRENT PATH = SALES, CURRENT PATH 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,ENVELOPE.EMP_INFO_TYPE,ENVELOPE.EMP_INFO_TYPE,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 プロシージャーの稼動確認
下記のスクリプトを使用して、パッケージが正常に稼働しているかどうかを確認します。
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; db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_procedure.db2.add_new_employee CONNECT TO LABDB データベース接続情報 データベース・サーバー = DB2/LINUX 9.7.0 SQL 許可 ID = 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;
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 :1000 FIRST_NAME :MAX LAST_NAME :TRENTON BAND :1 HIRE DATE :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
4
4
4
4.
.
.5
.
5
5
5
ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT
ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT
ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT
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
実行例
＿ASSIGN_EMPLOYEE_TO_NEW_ACCOUNT プロシージャーの稼動確認
下記のスクリプトを使用して、パッケージが正常に稼働しているかどうかを確認します。
cd /workshop/lab5
db2 -tvf call.assign_employee_to_new_account.clp
db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_procedure.db2.assign_employee_to_new_account CONNECT TO LABDB データベース接続情報 データベース・サーバー = DB2/LINUX 9.7.0 SQL 許可 ID = DB2INST1 ローカル・データベース別名 = LABDBSET 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;
このスクリプトは下記の様な 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 DEPT_CODE MAX_EMPLOYEES CURRENT_EMPLOYEES --- --- --- --- 4 E21 3. 2. 5 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 DEPT_CODE MAX_EMPLOYEES CURRENT_EMPLOYEES --- --- --- --- 4 E21 3. 3. 5 D11 4. 3.
5
5
5
5.
.
.
.
View
View
View
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 オプションがついていることを確認します。
実行例
＿FORCE オプションの除去
vi
エディタを使用して db2plsql_views.db2.organization_structure ファイルを修正します。
（/workshop/lab5/plsql_ddl_modified ディレクトリに修正済みのファイルがあります。お時間のな
い方や vi
の操作に不慣れな方はこちらをご使用ください。）
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 FORCEFORCEFORCEFORCE 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 オプションを取り除く
修正例
＿View
の作成
修正したファイルを以下のコマンドで実行します。
db2 -tvf db2plsql_views.db2.organization_structure
DB20000I
メッセージが表示されトリガーが正常に作成されたことを確認します。
実行例
--#SET TERMINATOR / CONNECT TO LABDB /SET CURRENT SCHEMA = 'SALES' /
--#SET :VIEW:SALES:ORGANIZATION_STRUCTURE
CREATE OR REPLACE VIEW CREATE OR REPLACE VIEW CREATE OR REPLACE VIEW 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 / db2inst1@DB2V97onSLES10:/workshop/lab5/plsql_ddl_modified> db2 -tvf db2plsql_views.db2.organization_structure CONNECT TO LABDB データベース接続情報 データベース・サーバー = DB2/LINUX 9.7.0 SQL 許可 ID = 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),
6
6
6
6.
.
.
.
トリガー
トリガー
トリガー
トリガーの
の
の作成
の
作成
作成
作成
この章では
Update_Departments,UPDATE_ACC_ON_NEW_EMPL
の２つのトリガーを作成します。
6
6
6
6.
.1
.
.
1
1
1
Update_Departments
Update_Departments
Update_Departments
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 ONAFTER INSERT OR DELETE OR UPDATE ONAFTER INSERT OR DELETE OR UPDATE ONAFTER INSERT OR DELETE OR UPDATE ON employ employ employ employees FOR EACH ROWees FOR EACH ROWees FOR EACH ROWees 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; /
ここでは、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_UPDATEUPDATE_DEPARTMENTS_ON_UPDATEUPDATE_DEPARTMENTS_ON_UPDATEUPDATE_DEPARTMENTS_ON_UPDATE" -- /*
・・・<省略>・・・ -- */
AFTER UPDATE AFTER UPDATE AFTER 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 IF DELETING THEN IF DELETING THEN IF DELETING THEN
---- UPDATE departments SET (total_projects, total_employees)= UPDATE departments SET (total_projects, total_employees)= UPDATE departments SET (total_projects, total_employees)= UPDATE departments SET (total_projects, total_employees)=
---- (SELEC (SELEC (SELEC (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:old.dept_code;T count(1), SUM(current_projects) FROM employees) WHERE dept_code=:old.dept_code;T count(1), SUM(current_projects) FROM employees) WHERE dept_code=:old.dept_code;T count(1), SUM(current_projects) FROM employees) WHERE dept_code=:old.dept_code;
---- ELSIF INSERTING THEN ELSIF INSERTING THEN ELSIF INSERTING THEN ELSIF INSERTING THEN
---- UPDATE departments SET (total_projects, total_employees)= UPDATE departments SET (total_projects, total_employees)= UPDATE departments SET (total_projects, total_employees)= UPDATE departments SET (total_projects, total_employees)=
---- (SELECT count(1), SUM(current_projects) FROM employees) W (SELECT count(1), SUM(current_projects) FROM employees) W (SELECT count(1), SUM(current_projects) FROM employees) W (SELECT count(1), SUM(current_projects) FROM employees) WHERE dept_code=:new.dept_code;HERE dept_code=:new.dept_code;HERE dept_code=:new.dept_code;HERE dept_code=:new.dept_code;
---- ELSIF UPDATING THEN ELSIF UPDATING THEN ELSIF UPDATING THEN ELSIF UPDATING THEN
