Oracle互換（前半）
本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。
この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合す る使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまた は同様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。 © Copyright IBM Japan Systems Engineering Co., Ltd. 2009
＜第１．００版 ２００９年 ７月＞
• Oracle互換機能強化概要
互換機能強化概要
互換機能強化概要
互換機能強化概要
• DB2_COMPATIBILITY_VECTOR
• データタイプ
データタイプ
データタイプ
データタイプの
の
の
の互換性
互換性
互換性
互換性
• スカラー
スカラー
スカラー
スカラー関数
関数
関数の
関数
の
の
の機能強化
機能強化
機能強化
機能強化
• ビルトイン
ビルトイン
ビルトイン
ビルトイン・
・・
・パッケージ
パッケージ
パッケージ
パッケージ・
・・
・ライブラリー
ライブラリー
ライブラリー
ライブラリー
• PL/SQLサポート
サポート
サポート
サポート
• パッケージ
パッケージ
パッケージ
パッケージ（
（（
（モジュール
モジュール）
モジュール
モジュール
））
）の
の
のサポート
の
サポート
サポート
サポート
• 参考資料
参考資料
参考資料
参考資料
内容
Oracle互換機能強化概要
Oracle互換機能強化概要
Oracle互換機能の強化により移行が容易に
Oracle
Database
DB2
Database
Your Applications
Oracle SQL・
・・
・データ
データ
データ
データ型
型
型
型
PL/SQL
ビルトイン
ビルトイン
ビルトイン
ビルトイン関数
関数
関数
関数
ビルトイン
ビルトイン
ビルトイン
ビルトイン・
・・
・パッケージ
パッケージ
パッケージ
パッケージ
Concurrency Control
SQL*Plus Scripts
ほとんどそのまま
ほとんどそのまま
ほとんどそのまま
ほとんどそのまま
理解
理解
理解
理解できる
できる
できる
できる。
。
。
。
ほとんどそのまま
ほとんどそのまま
ほとんどそのまま
ほとんどそのまま動
動
動く
動
く
く
く
Oracle固有の機能や動作
Oracle互換機能概要
•
データタイプ
•
NUMBER、VARCHAR2、TIMESTAMP(n)、“DATE”
•
BOOLEAN、INDEX BY、VARRAY、ROW TYPE、Ref Cursor
•
ビルトイン関数
•
形式・型変換関連 （TO_CHAR, TO_DATE, TO_TIMESTAMP, TO_NUMBER, TO_CLOB）
•
日付操作関連（EXTRACT, ADD_MONTHS, …）
•
文字列操作関連（INITCAP, RPAD, LPAD, INSTR, REVERSE・・・）
•
その他（DECODE, NVL, LEAST, GREATEST, BITAND）
•
ビルトイン・パッケージ・ライブラリー
•
DBMS_OUTPUT、UTL_FILE、DBMS_ALERT、DBMS_PIPE、DBMS_JOB、DBMS_LOB、DBMS_SQL、・・・
•
フィーチャー
•
CONNECT BY句を使用した階層照会
•
外部結合演算子 (+)-join
•
DUAL表
•
ROWNUM擬似列
•
TRUNCATE TABLEステートメント
•
Public synonym
•
作成済み一時表
•
NEXTVAL/CURRVAL（シーケンスの呼び出し方法）
•
MINUS（集合演算子 EXCEPTのAlias）
PL/SQLサポート概要
•
PL/SQLの機能
•
All logic
•
EXCEPTION
•
User Defined Exceptions
•
Constant variables
•
FOR over range
•
over SELECT
•
over cursor
•
%TYPE
•
%ROWTYPE
•
BULK COLLECT/FETCH
•
FORALL
•
Autonomous transaction
•
PL/SQLの記述場所
•
Anonymous block
•
Scalar function
•
Procedure
•
Package
•
Trigger
•
パッケージオブジェクトのサポート
•
CREATE PACKAGE
•
CREATE PACKAGE BODY
•
Replace package body
•
PKG [BODY]
VARIABLE
•
CURSOR
•
TYPE
•
EXCEPTION
DB2_COMPATIBILITY_VECTOR
DB2_COMPATIBILITY_VECTOR
DB2_COMPATIBILITY_VECTOR レジストリ変数
• Oracleとの互換機能を有効にするレジストリ変数
•
Oracle互換機能の中にはこのレジストリ変数の設定が必要なものがあります。
• 設定方法
•
12ビットのビットでそれぞれの機能をON、OFFを決定します。このビットの16進
値を設定します。
•
それぞれのビットに割り当てられている機能は次ページを参照
•
全ての互換機能を有効にするにはFFF（ =0x01+0x02+・・・+0x400+0x800）を指定
•
DB2_COMPATIBILITY_VECTOR=ORAの設定も可能
•
この設定はFFFと同じように全ての互換フィーチャーを有効にします。
•
この設定はOracle との最大の互換性を実現する設定であり、Oracle互換機能を
使用する際の推奨値となります。
•
またこの設定により、DB2_DEFERRED_PREPARE_SEMANTICS=YESの設
定も暗黙的に有効になります。
•
DB2_DEFERRED_PREPARE_SEMANTICS=YESは、Unicode,SBCS環境でのみの
推奨値となります。
Unicode,SBCS環境
・DB2_COMPATIBILITY_VECTOR=ORA
・DB2_DEFERRED_PREPARE_SEMANTICS=YES
それ以外の環境
・DB2_COMPATIBILITY_VECTOR=ORA
・DB2_DEFERRED_PREPARE_SEMANTICS=NO
以上により、こちらが
それぞれの環境での推奨値
暗黙的に設定されま
すが明示指定される
ことをお勧めします
DB2_COMPATIBILITY_VECTORで設定可能なフィーチャー
PL/SQL ステートメントおよび言語要素のコンパイルと実行を可能にします。
PL/SQL のコンパイル
12 (0x800)
データ・ディクショナリー互換ビューの作成を可能にします。
データ・ディクショナリー互換ビュー
11 (0x400)
配列で first、last、next、previous などの演算を実行するメソッドの使用を可能にしま
す。また、配列内の特定の要素の参照に、大括弧の代わりに括弧を使用できるよう
にします。例えば、array1(i) は array1 の要素 i を参照します。
コレクション・メソッド
10 (0x200)
バイト長が 254 以下である文字定数および GRAPHIC ストリング定数に、CHAR デー
タ・タイプまたは GRAPHIC データ・タイプ (VARCHAR データ・タイプでも
VARGRAPHIC データ・タイプでもない) を割り当てることを可能にします。
文字リテラル
9 (0x100)
TRUNCATE ステートメント用の代替セマンティクスを有効にします。IMMEDIATE がオ
プションのキーワードであり、指定がない場合にデフォルトになります。TRUNCATE
ステートメントが論理作業単位内の最初のステートメントではない場合、TRUNCATE
ステートメントが実行される前に暗黙的なコミット操作が実行されます。
TRUNCATE TABLE
8 (0x80)
DATE データ・タイプを日付と時間の値を結合した TIMESTAMP(0) として使用するこ
とを可能にします。
DATE データ・タイプ
7 (0x40)
VARCHAR2 データ・タイプおよび関連する文字ストリング処理を有効にします。
VARCHAR2 データ・タイプ
6 (0x20)
NUMBER データ・タイプおよび関連する数値処理を有効にします。
NUMBER データ・タイプ
5 (0x10)
CONNECT BY 節を使用した階層照会のサポートを有効にします。
階層照会
4 (0x08)
外部結合演算子 (+) のサポートを有効にします。
外部結合演算子
3 (0x04)
未使用
未使用
2 (0x02)
ROWNUM を ROW_NUMBER() OVER() の同義語として使用することを可能にし、
ROWNUM を SQL ステートメントの WHERE 節に含めることを許可します。
ROWNUM
1 (0x01)
説明
互換性フィーチャー
ビット位置
DB2_COMPATIBILITY_VECTORの設定
• DB2_COMPATIBILITY_VECTOR、DB2_DEFERRED_PREPARE_SEMANTICSを
設定後、インスタンスの再起動をして設定を有効にします。
•
以下の機能は、データベース作成時にDB2_COMPATIBILITY_VECTORを設定し
ておかなければ、機能を有効に出来ません。
•
VARCHAR,NUMBER,DATEのデータタイプの互換性
•
データディクショナリー互換ビュー
C:¥work>db2set DB2_COMPATIBILITY_VECTOR=ORA
C:¥work>db2set DB2_DEFERRED_PREPARE_SEMANTICS=YES
C:¥work>db2stop force
2009-06-30 09:29:08 0 0 SQL1064N DB2STOP の処理が正常に終了しました。
SQL1064N DB2STOP の処理が正常に終了しました。
C:¥work>db2start
2009-06-30 09:29:13 0 0 SQL1063N DB2START の処理が正常に終了しました。
SQL1063N DB2START の処理が正常に終了しました。
C:¥work>db2set
DB2_DEFERRED_PREPARE_SEMANTICS=YES
DB2_COMPATIBILITY_VECTOR=ORA
DB2INSTOWNER=・・・
・・・
DB2_COMPATIBILITY_VECTOR設定例
データタイプの互換性
データタイプの互換性
データタイプの互換性
• NUMBER,VARCHAR2,DATEの
の
の
の互換性
互換性
互換性
互換性
•
Oracleからの移行時のデータタイプのマッピングやDDLの変更を最小限できる
•
Oracleがサポートしている以下のデータタイプとの互換性をサポート
•
DATE
•
NUMBER
•
VARCHAR2
•
データタイプの
データタイプ
データタイプ
データタイプ
の
の互換性
の
互換性
互換性を
互換性
を
を有効
を
有効
有効にするには
有効
にするには、
にするには
にするには
、
、
、DB2_COMPATIBILITY_VECTORの
の
の
の設定
設定
設定が
設定
が
が必要
が
必要
必要
必要
•
データベース作成時に設定が行われている必要がある、データベース作成後は変更不可
•
以下のデータベース構成パラメーターの以下の項目で互換性サポートを有効化を確認できる
•
NUMBER データ・タイプの互換性
•
VARCHAR2 データ・タイプの互換性
•
データ・タイプ DATE の TIMESTAMP(0) への互換性
C:¥>db2 get db cfg for sample | find "互換性"
NUMBER データ・タイプの互換性
= ON
VARCHAR2 データ・タイプの互換性
= ON
データ・タイプ DATE の TIMESTAMP(0) への互換性
= ON
DATEデータタイプの互換性
• 「
「「
「データ
データ
データ
データ・
・・
・タイプ
タイプ
タイプ DATE の
タイプ
の TIMESTAMP(0) への
の
の
への
への
への互換性
互換性
互換性
互換性」
」」
」が
が
がON
が
•
DATEタイプがTIMESTAMP(0)として扱われる
•
OracleのDATEとの互換
•
OracleのDATEは、秒までの精度を持つ
create table tab1(c1 date)
DB20000I SQL コマンドが正常に完了しました。
describe table tab1
データ・タイ データ・ 列の スケ
列名 プ・スキーマ タイプ名 長さ ール NULL
- --- - ---- --- ---C1 SYSIBM DATE 4 0 はい
1 レコードが選択されました。
insert into tab1 values current date
DB20000I SQL コマンドが正常に完了しました。
select * from tab1
C1
---2009-04-22
1 レコードが選択されました。
create table tab1(c1 date)
DB20000I SQL コマンドが正常に完了しました。
describe table tab1
データ・タイ データ・ 列の スケ
列名 プ・スキーマ タイプ名 長さ ール NULL
--- --- - ---- --- ---C1 SYSIBM TIMESTAMP 7 0 はい
1 レコードが選択されました。
insert into tab1 values current date
DB20000I SQL コマンドが正常に完了しました。
select * from tab1
C1
---2009-04-22-13.39.31
1 レコードが選択されました。
データ・タイプ DATE の TIMESTAMP(0) への互換性=OFF データ・タイプ DATE の TIMESTAMP(0) への互換性=ON
Date型でテーブルを作成すると Timestamp(0)として定義される
参考）TIMESTAMPの精度指定
•
TIMESTAMPの
の
の精度
の
精度
精度
精度の
の指定
の
の
指定
指定
指定が
が
が
が可能
可能
可能
可能
•
互換機能（DB2_COMPATIBILITY_VECTOR）に関係なくV9.7から使用可能
•
小数点以下の秒数の精度を指定
•
0から12までを指定可能（デフォルト：6）
•
OracleのTimestamp型との互換 ← OracleのTimestampでも精度指定可能(指定可能な桁数 0-9）
•
より高い精度を持つTIMESTAMP型の使用
•
低い精度を指定することでスペースの節約
create table tab1(
c0 timestamp(0),c1 timestamp(1),c2 timestamp(2), c3 timestamp(3),c4 timestamp(4),c5 timestamp(5), c6 timestamp(6),c7 timestamp(7),c8 timestamp(8), c9 timestamp(9),c10 timestamp(10),c11 timestamp(11), c12 timestamp(12),c13 timestamp )
DB20000I SQL コマンドが正常に完了しました。 describe table tab1
データ・タイ データ・ 列の スケ 列名 プ・スキーマ タイプ名 長さ ール NULL -- --- - ---- --- ---C0 SYSIBM TIMESTAMP 7 0 はい C1 SYSIBM TIMESTAMP 8 1 はい C2 SYSIBM TIMESTAMP 8 2 はい C3 SYSIBM TIMESTAMP 9 3 はい C4 SYSIBM TIMESTAMP 9 4 はい C5 SYSIBM TIMESTAMP 10 5 はい C6 SYSIBM TIMESTAMP 10 6 はい C7 SYSIBM TIMESTAMP 11 7 はい C8 SYSIBM TIMESTAMP 11 8 はい C9 SYSIBM TIMESTAMP 12 9 はい C10 SYSIBM TIMESTAMP 12 10 はい C11 SYSIBM TIMESTAMP 13 11 はい C12 SYSIBM TIMESTAMP 13 12 はい C13 SYSIBM TIMESTAMP 10 6 はい
values current timestamp 1
---2009-04-22-18.53.07.156000
1 レコードが選択されました。
values current timestamp(0) 1
---2009-04-22-18.53.07
1 レコードが選択されました。
values current timestamp(12) 1 ---2009-04-22-18.53.07.171000000000 精度の指定により列の長さ が変わる 精度を短くとることでスペー スの節約が可能 CURRENT TIMESTAMP特殊 レジスターも精度指 定可能
NUMBER データ・タイプの互換性
• 「
「NUMBERデータ
「「
データ
データ
データ・
・・
・タイプ
タイプ
タイプ
タイプの
の互換性
の
の
互換性
互換性
互換性」
」」
」が
が
が
がON
•
表定義時の列タイプやプロシージャー内の変数タイプなどでNUMBER型を使用する
ことが出来る
•
実際には以下のような型として作成される
•
NUMBER -> DECFLOAT（16）
•
NUMBER(p)->DECIMAL(p)
•
NUMBER(p,s)->DECIMAL(p,s)
•
精度（p）は31まで
create table tab1(c1 number
number
number
number,c2 number
number
number
number(10),c3 number
number
number
number(10,5))
DB20000I SQL コマンドが正常に完了しました。
describe table tab1
データ・タイ データ・
列の
スケ
列名
プ・スキーマ タイプ名
長さ
ール
NULL
--- --- - ---- ---
---C1 SYSIBM DECFLOAT 8 0 はい
C2 SYSIBM DECIMAL 10 0 はい
C3 SYSIBM DECIMAL 10 5 はい
3 レコードが選択されました。
NUMBERデータ
データ
データ
データ・
・・
・タイプ
タイプ
タイプ
タイプの
の
の
の
互換性
互換性
互換性
互換性＝
＝
＝
＝ON
精度を付けないNUMBER型でテーブルを作成すると
DECFLOAT（16）型として定義されます。
精度、位取りを付けたNUMBER型はDECIMALとして
定義されます。
DDLでNUMBERを使用することが出来ます。
VARCHAR2 データ・タイプの互換性
• 「
「VARCHAR2データ
「「
データ
データ・
データ
・・
・タイプ
タイプ
タイプ
タイプの
の互換性
の
の
互換性
互換性
互換性」
」」
」が
が
がON
が
•
表定義時の列タイプやプロシージャー内の変数タイプなどでVARCHAR2型を使用
することが出来る
•
実際には、VARCHARとして作成される
•
空文字（''）がNULLとして扱われる
•
VARCHARの比較には非空白埋め比較セマンティクスが使用される
•
長さが最大 254 バイトまでの文字ストリング・リテラルのデータ・タイプは CHARになり、
254以上はVARCHARになる
create table tab1(c1 varchar2
varchar2
varchar2(10),c2 varchar2
varchar2
varchar2
varchar2(10) for bit data)
varchar2
DB20000I SQL コマンドが正常に完了しました。
describe table tab1
データ・タイ データ・
列の
スケ
列名
プ・スキーマ タイプ名
長さ
ール
NULL
--- --- - ---- ---
---C1 SYSIBM VARCHAR 10 0 はい
C2 SYSIBM VARCHAR 10 0 はい
2 レコードが選択されました。
Varchar2型でテーブルを作成す
るとVarcharとして定義される
VARCHAR2データ
データ
データ
データ・
・・
・タイプ
タイプ
タイプ
タイプの
の
の
の
互換性
互換性
互換性
互換性=ON
DDLでVARCHAR2が使用できる
VARCHAR2 データ・タイプの互換性 ON/OFFの違い
•
空文字
空文字
空文字
空文字（
（（
（''））））の
の
の
の扱
扱
扱い
扱
いとVARCHARの
い
い
の
の
の末尾
末尾
末尾
末尾ブランク
ブランクの
ブランク
ブランク
の
の
の比較
比較
比較の違いについて確認
比較
C:¥work¥asnclp>db2 -tvf varchar2test.sql
create table tab1(c1 varchar(10))
DB20000I SQL コマンドが正常に完了しました。
insert into tab1 values (null),(''),('a'),('a ')
DB20000I SQL コマンドが正常に完了しました。
select c1,hex(c1) from tab1
C1 2
---
---
-a 61
a 6120
4 レコードが選択されました。
select c1,hex(c1) from tab1 where c1 = 'a'
C1 2
---
---a 61
a 6120
2 レコードが選択されました。
C:¥work¥asnclp>db2 -tvf varchar2test.sql
create table tab1(c1 varchar(10))
DB20000I SQL コマンドが正常に完了しました。
insert into tab1 values (null),(''),('a'),('a ')
DB20000I SQL コマンドが正常に完了しました。
select c1,hex(c1) from tab1
C1 2
---
---
--
-a 61
a 6120
4 レコードが選択されました。
select c1,hex(c1) from tab1 where c1 = 'a'
C1 2
---
---a 61
1 レコードが選択されました。
VARCHAR2 データ・タイプの互換性=OFF
VARCHAR2 データ・タイプの互換性=ON
空文字（’’）
OFF：0バイトの文字
ON：NULL
VARHCHARの比較
OFF：末尾ブランクは無視される
ON：末尾ブランクも含めて比較
参考）非空白埋め比較セマンティクスと空白埋め比較セマンティクス
• 非空白埋
非空白埋
非空白埋
非空白埋め
め
め比較
め
比較
比較
比較セマンティクス
セマンティクス
セマンティクス
セマンティクス
•
以下の文字は異なるものとみなす
•
'A'、'A_'、'A__' （'_'は空白文字）
• 空白埋
空白埋
空白埋
空白埋め
め
め
め比較
比較
比較セマンティクス
比較
セマンティクス
セマンティクス
セマンティクス
•
空白文字をパティングして比較する
•
末尾の空白文字の違いは無視される
•
以下の文字は同じものとみなす
•
'A'、'A_'、'A__' （'_'は空白文字）
OracleのVARCHARの比較
はこちらが使用される
VARCHAR2 データ・タイプの
互換性＝OFFの時のDB2の
VARCHARの比較はこちらが
使用される
VARCHAR2 データ・タイプの
互換性＝ONの時のDB2の
VARCHARの比較はこちらが
使用される
スカラー関数の機能強化
スカラー関数の機能強化
スカラー関数の機能強化
• Oracleの
の
のスカラー
の
スカラー
スカラー
スカラー関数
関数
関数
関数と
と同
と
と
同
同
同じ
じ
じ
じ名前
名前
名前
名前で
で同
で
で
同
同
同じ
じ
じ
じ動作
動作
動作
動作をする
をする関数
をする
をする
関数
関数
関数の
の
の
の追加
追加
追加
追加
•
Oracleからの移行の際に、これらの関数によりSQLの書き換えが必要なく
なる（Oracleの全関数をサポートしていないため注意）
• 以下
以下
以下
以下のような
のような
のような
のような関数
関数
関数を
関数
を
を
を新規
新規で
新規
新規
で
で
でサポート
サポート
サポート
サポートまたは
または
または
または機能拡張
機能拡張
機能拡張
機能拡張
•
形式・型変換関連
•
TO_CHAR, TO_DATE, TO_TIMESTAMP, TO_NUMBER, TO_CLOB
•
日付操作関連
•
EXTRACT, ADD_MONTHS, …
•
文字列操作関連
•
INITCAP, RPAD, LPAD, INSTR, REVERSE・・・
•
その他
形式・型変換関連のスカラー関数の使用例
• 関数
関数
関数
関数
•
フォーマットストリングを使用し、フォーマットの変換を行いながら型変換を行える
•
TO_NUMBER（数値型への変換）
•
TO_CHAR（文字型への変換）
•
TO_DATE（日付型への変換）
•
TO_TIMESTAMP（タイムスタンプへの変換）
•
キャストとの違い
•
フォーマットの変換を行いながら型変換を行う
•
例
•
'$123,456,789' （文字）→ 123456789（数値）
•
123456789 （数値）→ '$123,456,789' （文字）
•
'09/05/20' （文字）→ 2009-05-20-00.00.00（タイムスタンプ）
•
2009-05-20-00.37.24.843000（タイムスタンプ） → '09/05/20' （文字）
C:¥>db2 values to_number('$123,456,789','$999,999,999')
1
---123456789
1 レコードが選択されました。
C:¥>db2 values to_char(123456789,'$999,999,999')
1
---$ 123,456,789
1 レコードが選択されました。
数値型に変換
TO_NUMBER使用例
TO_CHAR使用例
文字型に変換
ビルトイン・パッケージ・ライブラリー
（ビルトイン・モジュール）
ビルトイン・パッケージ・ライブラリー
（ビルトイン・モジュール）
ビルトイン・パッケージ・ライブラリー（ビルトイン・モジュール）
• V9.7では
ではアプリケーション
では
では
アプリケーション
アプリケーション
アプリケーション開発
開発
開発に
開発
に
に
に役立
役立
役立
役立つ
つビルトイン
つ
つ
ビルトイン
ビルトイン
ビルトイン・
・・
・パッケージ
パッケージ
パッケージ・
パッケージ
・・
・ライブラ
ライブラ
ライブラ
ライブラ
リー
リー
リー
リーを
を
を多数提供
を
多数提供
多数提供
多数提供
•
パッケージ（モジュール）は、関数やプロシージャーや変数などをまとめ
たオブジェクトでSQLから呼び出すことが可能です。（PL/SQLやSQL
PLからも呼び出すことも可能）
•
SQLのみでは出来ないような処理（例：ファイル操作、メッセージの出力
など）を行うことができます。
• Oracleと
と
と同
と
同
同じ
同
じ
じ
じ名前
名前で
名前
名前
で
で同
で
同
同
同じ
じ
じ
じ機能
機能を
機能
機能
を
を
を持
持
持
持つ
つ
つビルトイン
つ
ビルトイン・
ビルトイン
ビルトイン
・・
・パッケージ
パッケージ
パッケージ・
パッケージ
・・
・ライブラリー
ライブラリー
ライブラリー
ライブラリー
を
を
を
を提供
提供
提供
提供
•
OracleのPL/SQLの開発では、ビルトイン・パッケージ･ライブラリーが
頻繁に利用されます。
•
OracleのPL/SQLの移行の際に、使用されているビルトイン・パッケー
ジ･ライブラリーがDB2 V9.7でも提供されていれば書き直しなしでの移
行も可能です。
ビルトイン・パッケージ・ライブラリー（ビルトイン・モジュール）
説明
説明
説明
説明
モジュール
モジュール
モジュール
モジュール名
名
名
名
UTL_SMTP モジュールは、SMTP (Simple Mail Transfer Protocol) を介して E メールを送信する機能を提供しま
す。
UTL_SMTP
UTL_MAIL モジュールは、E メールを送信する機能を提供します。
UTL_MAIL
UTL_FILE モジュールは、データベース・サーバーのファイル・システム上のファイルとの間で読み取りおよび書き
込みを行うための一連のルーチンを提供します。
UTL_FILE
UTL_DIR モジュールは、UTL_FILE モジュールで使用するディレクトリー別名を維持するための一連のルーチン
を提供します。
UTL_DIR
DBMS_UTILITY モジュールは、さまざまなユーティリティー・プログラムを提供します。
DBMS_UTILITY
DBMS_SQL モジュールは、動的 SQL を実行するための一連のプロシージャーを提供します。したがって、さまざ
まなデータ操作言語 (DML) ステートメントやデータ定義言語 (DDL) ステートメントをサポートします。
DBMS_SQL
DBMS_PIPE モジュールは、同じデータベースに接続されたセッション内またはセッション間のパイプを通して、
メッセージを送信するための一連のルーチンを提供します。
DBMS_PIPE
DBMS_OUTPUT モジュールは、メッセージ・バッファーにメッセージを書き込み (複数行のテキスト)、メッセージ・
バッファーからメッセージを取得する一連のプロシージャーを提供します。これらのプロシージャーは、メッセージ
を標準出力に書き込む必要のあるアプリケーション・デバッグの際に役立ちます。
DBMS_OUTPUT
DBMS_LOB モジュールは、ラージ・オブジェクトを操作する機能を提供します。
DBMS_LOB
DBMS_JOB モジュールは、ジョブの作成、スケジューリング、および管理のためのプロシージャーを提供します。
DBMS_JOB
DBMS_ALERT モジュールは、アラートの登録、アラートの送受信を行うための一連のプロシージャーを備えてい
ます。
DBMS_ALERT
DBMS_OUTPUT使用例
• DBMS_OUTPUTパッケージ
パッケージ
パッケージ
パッケージ（
（（
（モジュール
モジュール
モジュール）
モジュール
））
）
•
メッセージバッファーへの書き込み、読み込み行うためのプロシジャー、関数を提供
•
処理結果の出力、デバッグなどで役立ちます。
C:¥work>db2 -td@ -vf dbms_output_test.sql
drop procedure proc1
DB20000I SQL コマンドが正常に完了しました。
CREATE PROCEDURE proc1( P1 VARCHAR(10) )
BEGIN
CALL DBMS_OUTPUT.PUT
DBMS_OUTPUT.PUT
DBMS_OUTPUT.PUT( 'P1 = ' );
DBMS_OUTPUT.PUT
CALL DBMS_OUTPUT.PUT_LINE
DBMS_OUTPUT.PUT_LINE
DBMS_OUTPUT.PUT_LINE( P1 );
DBMS_OUTPUT.PUT_LINE
END
DB20000I SQL コマンドが正常に完了しました。
SET SERVEROUTPUT ON
SET SERVEROUTPUT ON
SET SERVEROUTPUT ON
SET SERVEROUTPUT ON
DB20000I SET SERVEROUTPUT コマンドが正常に完了しました。
CALL proc1( '10' )
リターン状況 = 0
P1 = 10
CLPでメッセージバッファーを
読み出すための設定
メッセージ・バッファーから読
み出されたデータ
DBMS_OUTPUTパッケージのPUTプロ
シージャー、PUT_LINEプロシージャーを
呼び出して、メッセージ・バッファーへの書
き込みを行います。
UTL_DIR、UTL_FILE使用例
call utl_dir.create_directory
utl_dir.create_directory
utl_dir.create_directory
utl_dir.create_directory('mydir','C:¥work')
リターン状況 = 0
call utl_dir.get_directory_path
utl_dir.get_directory_path
utl_dir.get_directory_path
utl_dir.get_directory_path('mydir',?)
出力パラメーターの値
---パラメーター名: PATH
パラメーター値: C:¥work
リターン状況 = 0
• UTL_DIRパッケージ
パッケージ
パッケージ
パッケージ
•
ディレクトリを管理するためのプ
ロシージャーや関数を提供
• UTL_FILEパッケージ
パッケージ
パッケージ
パッケージ
•
ファイルの読み取りや書き込みを
行うプロシージャーや関数を提供
begin
declare v_filehandle
utl_file.file_type
utl_file.file_type
utl_file.file_type
utl_file.file_type;
declare isopen
boolean;
declare row varchar(4000);
set v_filehandle = utl_file.fopen
utl_file.fopen
utl_file.fopen
utl_file.fopen('mydir','myfile.csv','w');
set isopen = utl_file.is_open
utl_file.is_open
utl_file.is_open
utl_file.is_open( v_filehandle );
if isopen != true then
return ;
end if;
for cur as (select deptno,deptname,mgrno from department)
do
set row = cur.deptno||','||cur.deptname||','||cur.mgrno;
call utl_file.put_line
utl_file.put_line
utl_file.put_line
utl_file.put_line(v_filehandle,row);
end for;
call utl_file.fclose
utl_file.fclose
utl_file.fclose
utl_file.fclose(v_filehandle);
end
DB20000I SQL コマンドが正常に完了しました。
!type myfile.csv
A00,SPIFFY COMPUTER SERVICE DIV.,000010
B01,PLANNING,000020
C01,INFORMATION CENTER,000030
D11,MANUFACTURING SYSTEMS,000060
・・・省略・・・
UTL_DIRを使用してディレクトリの準備
UTL_FILEを使用してファイルに出力
CSV形式で
ファイル出力
PL/SQLサポート
PL/SQLサポート
PL/SQLとは
• SQLの手続き型拡張機能としてOracle社が提供するプログラミング言語
•
Oracle向けに独自に開発されているため、SQLのように標準仕様にのっとったものではない。
• Oracle Database の内部でSQLとの緊密な連携を行うことで業務処理を効率的に実行する。
• 反復処理や分岐といった制御構造を使って複雑な処理を行うことができる。
•
なぜPL/SQLが必要？
• SQLはデータの集合を操作するための言語であるが、実際の業務処理（ビジネス・ロジック）では手続き型の処理が必要な場 合もある。Oracleでの
PL/SQL実行例
制御文とSQLで
構成される
PL/SQLが記述される場所
• PL/SQLブロック（Anonymous Block）
•
データベース外部に保持
• PL/SQLストアード・プログラム
（データベースのオブジェクトとしてデータベース内にロジックを格納）
•
プロシージャー
•
ファンクション
•
パッケージ
•
トリガー
declare ・・・ begin ・・・ end create procedure xx is ・・・ begin ・・・end create function xxxreturn varchar2 is ・・・ begin ・・・ end; create trigger xxx before insert ・・・ on ・・・
for each row declare ・・・ begin ・・・ end;
プロシージャー
ファンクション
トリガー
PL/SQLブロック
実行
DB2 9.7でのPL/SQLサポート
• DB2 9.7では新たにPL/SQLをサポート
•
V9.5まではSQL PLのみ
DB2 Server
PL/SQL
Compiler
SQL PL
Compiler
SURE
(SQL Unified Runtime Engine)
Data base
Editor
DB2 9.7では
PL/SQL用とSQL PL用の２つの
コンパイラを持つ
それぞれのコンパイラから作成
されたモジュールは同じランタイム
で稼動する
New
DB2 9.7でPL/SQLを実行
カーソルを使って、DEPT
表から結果を読み取る。
前のページと全く同一
PL/SQLスクリプトを実施
PL/SQLユニークな文法
がDB2で利用できる。
DB2とOracleのPL/SQL実行比較
• CLPPlusと
と
と
とSQL*Plusで
で
で
で同
同
同
同じ
じPL/SQLブロック
じ
じ
ブロック
ブロック
ブロックを
を
を
を実行
実行
実行
実行
Oracle Database 11g Enterprise Edition Release
11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real
Application Testing options
に接続されました。
SQL> set serveroutput on
SQL> DECLARE
2 str VARCHAR2(30);
3 BEGIN
4 str := 'HELLO WORLD';
5 DBMS_OUTPUT.PUT_LINE(str);
6 END;
7 /
HELLO WORLD
PL/SQLプロシージャが正常に完了しました。
SQL>
CLPPlus: バージョン 1.0
Copyright (c) 2009, IBM CORPORATION. All
rights reserved.
SQL> set serveroutput on
SQL> DECLARE
2 str VARCHAR2(30);
3 BEGIN
4 str := 'HELLO WORLD';
5 DBMS_OUTPUT.PUT_LINE(str);
6 END;
7 /
HELLO WORLD
DB250000I: コマンドは正常に完了しました。
SQL>
CLPPlusからDB2に対して実行
SQL*PlusからOracleに対して実行
PL/SQLを実行するには？
• DB2_COMPATIBILITY_VECTOR （レジストリ変数）
•
PL/SQLコンパイラを使用可能にするかを指定
•
PL/SQLを実行するためには設定は必須
• SET SQLCOMPAT PLSQL（CLPオプション）
•
CLPにPL/SQLの区切り文字（/）を認識させるオプション
DB2_COMPATIBILITY_VECTOR=800の設定
• DB2_COMPATIBILITY_VECTOR=800を
を
を設定
を
設定
設定
設定することで
することでPL/SQLを
することで
することで
を
を処理
を
処理
処理できるようにな
処理
できるようにな
できるようにな
できるようにな
る
る
る
る
C:¥work>db2 -tvf plsqltest2.sql!db2set |findstr DB2_COMPATIBILITY_VECTOR set sqlcompat plsql
DB20000I SET SQLCOMPAT コマンドが正常に完了しました。 set serveroutput on
DB20000I SET SERVEROUTPUT コマンドが正常に完了しました。 declare v1 varchar(100) := 'PL/SQL Test'; begin dbms_output.put_line(v1); end; DB21034E コマンドが、有効なコマンド行プロセッサー・コマン ドでないため、 SQLステートメントとして処理されました。 SQL 処理中に、そのコマンドが返されました。 SQL0104N "BEGIN-OF-STATEMENT" に続いて予期しないトークン "declare v1 varchar"が見つかりました。予期されたトークン に "<values>"が含まれている可能性があります。 LINE NUMBER=2. SQLSTATE=42601 C:¥work>db2 -tvf plsqltest2.sql
!db2set |findstr DB2_COMPATIBILITY_VECTOR
DB2_COMPATIBILITY_VECTOR=800 DB2_COMPATIBILITY_VECTOR=800 DB2_COMPATIBILITY_VECTOR=800 DB2_COMPATIBILITY_VECTOR=800
set sqlcompat plsql
DB20000I SET SQLCOMPAT コマンドが正常に完了しました。 set serveroutput on
DB20000I SET SERVEROUTPUT コマンドが正常に完了しました。 declare v1 varchar(100) := 'PL/SQL Test'; begin dbms_output.put_line(v1); end; DB20000I SQL コマンドが正常に完了しました。 PL/SQL Test
DB2_COMPATIBILITY_VECTOR=800を
を
を
を設定
設定
設定
設定
DB2_COMPATIBILITY_VECTOR=800設定
設定
設定
設定あり
あり
あり
あり
DB2_COMPATIBILITY_VECTOR=800設定
設定
設定
設定なし
なし
なし
なし
C:¥>db2set DB2_COMPATIBILITY_VECTOR=800 C:¥>db2stop force 2009-05-12 12:37:25 0 0 SQL1064N DB2STOP の処理が正常に終了 しました。 SQL1064N DB2STOP の処理が正常に終了しました。 C:¥>db2start 2009-05-12 12:37:31 0 0 SQL1063N DB2START の処理が正常に終 了しました。 SQL1063N DB2START の処理が正常に終了しました。PL/SQLブロック
ブロック
ブロックが
ブロック
が
が
が
正常
正常
正常
正常に
に
に処理
に
処理
処理
処理される
される
される
される
PL/SQLブロック
ブロック
ブロック
ブロックの
の
の
の
処理
処理
処理
処理に
に
に
に失敗
失敗
失敗
失敗
SET SQLCOMPAT PLSQL（
（（
（CLPオプション
オプション
オプション
オプション）
））
）
• CLPからPL/SQLを実行するためのオプション
• SET SQLCOMPAT PLSQLを設定することで“/”をPL/SQLブロックの終わりと認識する
•
PL/SQLでは、“；”はステートメントの区切り。ブロック単位で実行させるために、ブロックの最
後を”/”で認識
• セッションの終わりまで有効
• SET SQLCONMPAT DB2 で元に戻す
PL/SQLサポート制約事項
• 以下
以下
以下
以下の
の
の
の製品
製品エディション
製品
製品
エディション
エディション
エディションでは
では
では
では、
、
、
、PL/SQL ステートメント
ステートメントの
ステートメント
ステートメント
の
のコンパイル
の
コンパイル
コンパイル
コンパイルおよび
および
および
および実行
実行は
実行
実行
は
は
はサポート
サポート
サポート
サポートされ
され
され
され
ません
ません
ません
ません。
。
。
。
•
DB2 Express
•
DB2 Express-C
•
DB2 Personal Edition
• パーティション
パーティション・
パーティション
パーティション
・・
・データベース
データベース
データベース
データベース環境
環境では
環境
環境
では
では、
では
、
、
、PL/SQL 関数
関数および
関数
関数
および
およびトリガー
および
トリガー
トリガー
トリガーは
は
は
は作成
作成できません
作成
作成
できません
できません
できません。
。
。
。
• データベース
データベース
データベース
データベースが
が
が
が Unicode データベース
データベース
データベース
データベースとして
として
として
として定義
定義されていない
定義
定義
されていない
されていない
されていない場合
場合
場合
場合、
、
、
、PL/SQL ステートメン
ステートメン
ステートメン
ステートメン
ト
ト
ト
トおよび
および PL/SQL コンテキスト
および
および
コンテキスト
コンテキストにおいて
コンテキスト
において
において、
において
、
、
、NCLOB データ
データ
データ・
データ
・・
・タイプ
タイプの
タイプ
タイプ
の
の
の使用
使用
使用
使用は
は
は
はサポート
サポート
サポート
サポートされませ
されませ
されませ
されませ
ん
ん
ん
ん。
。
。 Unicode データベース
。
データベース
データベースでは
データベース
では
では
では、
、
、
、NCLOB データ
データ
データ
データ・
・タイプ
・・
タイプ
タイプ
タイプは
は
は、
は
、DB2 DBCLOB データ
、
、
データ
データ
データ・
・・
・タイプ
タイプ
タイプ
タイプ
に
に
に
にマップ
マップ
マップ
マップされます
されます
されます
されます。
。
。
。
• XMLTYPE データ
データ
データ・
データ
・・
・タイプ
タイプ
タイプ
タイプは
はサポート
は
は
サポート
サポート
サポートされません
されません
されません。
されません
。
。
。
• TYPE 宣言
宣言
宣言は
宣言
は
は、
は
、
、
、関数
関数、
関数
関数
、
、
、プロシージャー
プロシージャー
プロシージャー
プロシージャー、
、
、
、トリガー
トリガー、
トリガー
トリガー
、
、
、および
および
および
および無名
無名
無名ブロック
無名
ブロックでは
ブロック
ブロック
では
では
ではサポート
サポート
サポートされません
サポート
されません
されません
されません。
。
。
。
• FOR EACH STATEMENT オプション
オプション
オプション
オプションは
は
は
は、
、
、
、PL/SQL トリガー
トリガーでは
トリガー
トリガー
では
では
ではサポート
サポート
サポート
サポートされません
されません
されません
されません。
。
。
。
パッケージ（モジュール）のサポート
パッケージ（モジュール）のサポート
パッケージ（モジュール）のサポート
• モジュール
•
Oracleの
の
のパッケージ
の
パッケージ
パッケージ
パッケージに
に相当
に
に
相当
相当
相当する
する
するオブジェクト
する
オブジェクト
オブジェクト
オブジェクト
•
関数、
関数
関数
関数
、
、
、プロシージャー
プロシージャー
プロシージャー、
プロシージャー
、
、
、タイプ
タイプ
タイプ、
タイプ
、変数
、
、
変数
変数
変数などの
などの
などのオブジェクト
などの
オブジェクト
オブジェクト
オブジェクトをまとめた
をまとめた
をまとめた
をまとめたオブジェ
オブジェ
オブジェ
オブジェ
クト
クト
クト
クト
•
以下のオブジェクトを含められる
•
SQLプロシージャー
•
SQLファンクション
•
外部プロシージャー
•
外部ファンクション
•
グローバルコンディション
•
Moduleの初期化処理
•
ユーザー定義タイプ（配列、連想配列、行タイプ、カーソルタイプ）
•
グローバル変数
•
PL/SQLの
の
のパッケージ
の
パッケージ
パッケージと
パッケージ
と
と
と同
同じ
同
同
じ
じ
じ機能
機能
機能
機能を
を
を
を提供
提供
提供
提供
•
PL/SQLではCREATE PACKAGE文によりモジュールを作成
•
SQL PLではCREATE MODULE文によりモジュールを作成
Module
SQLプロシージャー
グローバル変数
SQLファンクション
・
・・
・
・
・・
・
・
・・
・
モジュールの構成
• Moduleは仕様部と本体で構成される
•
仕様部
•
オブジェクトの呼び出し方法の記述
•
グローバル変数やユーザー定義タイプ（配列、連想配列、カーソルタイプ・・・）の宣言も行う
•
本体
•
プロシージャーやファンクションを実装
CREATE MODULE moo@
ALTER MODULE moo PUBLISH PROCEDURE prot
(IN a INT, IN b BIGINT, IN c VARCHAR(20))
LANGUAGE SQL@
ALTER MODULE moo ADD PROCEDURE prot
(IN a INT, IN b BIGINT, IN c VARCHAR(20))
BEGIN
DECLARE stmt VARCHAR(1000);
DECLARE c1 CURSOR WITH RETURN FOR sl;
SET stmt = 'SELECT 1 FROM (values(1))';
PREPARE sl FROM stmt;
OPEN c1;
END@
仕様部
仕様部
仕様部
仕様部
本体
本体
本体
本体
モジュールのメリット
• モジュール内オブジェクトの共用
•
変数、カーソル、型、例外、ローカルルーチンを共有できる
• 名前衝突の回避
•
プロシージャー名、ファンクション名、変数名の衝突を回避で
きる
• 情報の隠蔽
•
モジュール内のみで使用可能なオブジェクトの作成が可能
• 権限のコントロール
•
モジュール単位に権限のgrant/revokeが可能
モジュールとPL/SQL
• PL/SQLでパッケージを作成するとモジュールとして登録される
•
PL/SQLではモジュールと同じ機能をパッケージで提供している
C:¥work>db2 -tvf modtest.sql set sqlcompat db2
DB20000I SET SQLCOMPAT コマンドが正常に完了しました。
create module mod1
DB20000I SQL コマンドが正常に完了しました。
alter module mod1 publish variable var1 int default 0 DB20000I SQL コマンドが正常に完了しました。
alter module mod1 publish procedure print DB20000I SQL コマンドが正常に完了しました。
alter module mod1 publish procedure plus(in p1 int) DB20000I SQL コマンドが正常に完了しました。
alter module mod1 publish function get() returns int DB20000I SQL コマンドが正常に完了しました。
alter module mod1 add procedure print begin call dbms_output.put_line(var1); end
DB20000I SQL コマンドが正常に完了しました。
alter module mod1 add procedure plus(in p1 int default 1) begin set var1 = var1 + p1; end
DB20000I SQL コマンドが正常に完了しました。
alter module mod1 add function get() returns int return var1 DB20000I SQL コマンドが正常に完了しました。
CC:¥work>db2 -tvf pkgtest.sql set sqlcompat plsql
DB20000I SET SQLCOMPAT コマンドが正常に完了しました。
create or replace package pkg1 is var1 int :=0;
procedure print;
procedure plus(p1 in int); function get return int; end;
DB20000I SQL コマンドが正常に完了しました。
create or replace package body pkg1 is procedure print is
begin
dbms_output.put_line(var1); end;
procedure plus(p1 in varchar2 default 1) is begin
var1 := var1 + p1; end;
function get return int is begin return var1; end; end; DB20000I SQL コマンドが正常に完了しました。
SQL PLでモジュールを作成
・CREATE MODULE文でModuleを作成後、ALTER MODULE
文で仕様部、本体を追加する
PL/SQLでパッケージ（モジュール）を作成
・CREATE PACKAGE文、CREATE PACKAGE
BODY文を使用して作成
仕様部
仕様部
仕様部
仕様部
本体
本体
本体
本体
仕様部
仕様部
仕様部
仕様部
本体
本体
本体
本体
モジュールに関するシステムカタログ
•
SYSCAT.MODULES
•
モジュール情報
•
SYSCAT.MODULEOBJECTS
•
モジュールに含まれるオブジェクト情報
select char(modulename,5) modulename,dialect,moduletype from syscat.modules where modulename in ('PKG1','MOD1') MODULENAME DIALECT MODULETYPE
---MOD1 DB2 SQL PL M
PKG1 PL/SQL P
2 レコードが選択されました。
select char(objectmodulename,10) objectmodulename,char(objectname,20) objectname,objecttype,published from syscat.moduleobjects where objectmodulename in ('PKG1','MOD1') order by 1
OBJECTMODULENAME OBJECTNAME OBJECTTYPE PUBLISHED --- -- - ---MOD1 VAR1 VARIABLE Y
MOD1 PLUS PROCEDURE Y MOD1 GET FUNCTION Y MOD1 PRINT PROCEDURE Y PKG1 VAR1 VARIABLE Y PKG1 GET FUNCTION Y PKG1 PRINT PROCEDURE Y PKG1 PLUS PROCEDURE Y
PL/SQL PackageにはPが入る
SQL PL作成されたModuleにはMが入る
MOD1に含まれるオブジェクト
PKG1に含まれるオブジェクト
参照例
モジュールへのdb2lookの対応
• db2lookの
の
の-modオプション
の
オプション
オプション
オプションにより
により
により
によりモジュール
モジュール
モジュール
モジュールの
のDDLの
の
の
の
の生成
の
生成
生成
生成が
が
が
が可能
可能
可能
可能
•
-modオプション
-mod: Generate DDL statements for Module
このオプションは、-e および -x/xd オプションとともに使用できます。
C:¥SQLLIB¥samples¥sqlpl>db2look -d testdb -e -mod -- USER は以下のとおりです: DB2ADMIN -- 表の DDL の作成 -- この CLP ファイルの作成に使用した DB2LOOK のバージョン: "9.7" -- タイム・スタンプ: 2009/05/19 23:18:34 -- データベース名: TESTDB -- データベース・マネージャーのバージョン: DB2/NT Version 9.7.0 -- データベース・コード・ページ: 1208 -- データベース照合シーケンス: SYSTEM_943 CONNECT TO TESTDB; ---モジュール・サポートの DDL ステートメント "DB2ADMIN"."PKG1" ---create or replace package pkg1 is
var1 int :=0; procedure print;
procedure plus(p1 in int); function get return int; end;
;
create or replace package body pkg1 is ・・・
---モジュール・サポートの DDL ステートメント "DB2ADMIN"."MOD1" ---CREATE MODULE "DB2ADMIN"."MOD1";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SOTA"; alter module mod1 publish variable var1 int default 0;
SET CURRENT SCHEMA = "DB2ADMIN";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SOTA"; alter module mod1 PUBLISH procedure print ;
SET CURRENT SCHEMA = "DB2ADMIN";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SOTA"; alter module mod1 PUBLISH procedure plus(in p1 int default 1) ; SET CURRENT SCHEMA = "DB2ADMIN";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SOTA"; ・・・・
