PL/SQL - OTN

PL/SQL
コーディング TIPS
第1版
1999/06/29
日本オラクル株式会社
この資料では、PL/SQLの概要、コーディング上便利な機能・注意すべき点・コツ
を中心にご説明させていただきます。
この資料の内容は、PL/SQL リリース8.0.5での機能を基に記述しております。
1
38
2
目次

PL/SQLとは
とは

組み込みパッケージ
コード設計/開発のヒント
コード設計
開発のヒント

その他TIPS
その他


コードの管理
PL/SQLのデバッグ
のデバッグ

PL/SQLのチューニング
のチューニング

2
PL/SQLとは
PL/SQLとは
Procedual Language / Structured Query Language
（SQLへの手続き型言語拡張）
への手続き型言語拡張）
アプリケーション
begin
SQL文
SQL文
if
SQL文
SQL文
else
SQL文
SQL文
end if;
end;
PL/SQLは、SQL言語（標準的リレーショナル・データベース言語）には無いプロ
グラミング構造を提供します。
SQL言語を手続き型言語として機能拡張したものです。
3
PL/SQLとは
PL/SQLとは
- 利点
z
プロシージャ機能
z
エラー処理
z
パフォーマンスの向上
z
z
高い移植性
Oracleとの統合
との統合
プロシージャ機能
• 変数、定数の利用
エラー処理
• エラー（例外）を補足、処理が行える
パフォーマンスの向上
• 複数のSQLを一度にOracleに送信（特にネットワーク環境では効果有り）
高い移植性
• Oracleの稼働環境すべてに移行可能（Ｐｌａｔｆｏｒｍに依存しない）
Oracleとの統合
Oracleとの統合
• すべてのＳＱＬデータ型をサポート
• データベース列の定義を基にした変数の宣言
4
組み込みパッケージ
z
DBMS_OUTPUT
z
UTL_FILE
z
DBMS_SQL
z
DBMS_PIPE
z
DBMS_ALERT
z
DBMS_JOB
z
DBMS_UTILITY
z
その他
代表的な組み込みパッケージについて、その用途とサンプルをご紹介させてい
ただきます。
5
組み込みパッケージ
- DBMS_OUTPUT
値、メッセージを表示します。
z
PUT
バッファに情報を書き込む（改行無）
PUT_LINE
バッファに情報を書き込む（改行有）
GET_LINE
バッファから1
バッファから1行を受け取る
NEW_LINE
バッファに改行を挿入
DBMS_OUTPUTで変数などの値とメッセージなどをバッファに出力します。
以下、検索結果を標準出力に表示するサンプル実行例です。
SQL*Plus、ServerManagerでは以下のように“ set serveroutput on”としてください。
それ以外では、GET_LINEで取り出す必要があります。
SQL> set serveroutput on
SQL> begin
2
select deptno into :info from dept where loc = 'BOSTON';
3
dbms_output.put_line('Value: ' ||:info);
4
end;
5
/
Value: 40
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
参照：『PL/SQL
ユーザーズ・ガイドおよびリファレンス リリース8.0』
参照：『
リリース 』 (A56827-1) 8章
章-16
6
組み込みパッケージ
- UTL_FILE
z
OSのテキスト･ファイルの読み書き
のテキスト･ファイルの読み書き
Server
Client
Database
Text File
プログラムのインプットとアウトプットとして、サーバOS上のテキストファイルを使用する
ことで、PL/SQLプログラム以外との情報の受け渡しが可能になります。
テキストファイルのオープン、クローズ、リード、ライトの機能を提供します。
安全を保証する為に、ファイルへアクセスするには、初期化パラメータファイル
(init.ora)のUTL_FILE_DIRパラメータににディレクトリを指定しておく必要が有ります。
以下の手順で実行します。
ファイル入出力の手順
① ファイルをOPENする。 --- FOPEN
② ファイル操作。
ファイルから読み込む --- GET_LINE
ファイルに書き出す --- PUT, PUT_LINE, PUTF, NEW_LINE
③ ファイルをCLOSEする。 --- FCLOSE, FCLOSE_ALL
参照：『アプリケーション開発者ガイド リリース8.0』
章-27
リリース 』 (A56822-1) - 12章
7
以下、ディレクトリ(c:¥temp)にファイル(aaa.txt)を作成し、1行書き出し、その内容を読み込
み出力するサンプルです。
INIT.ORA（
（UTL_FILE_DIR）
）:
INIT.ORA に以下のようにディレクトリを指定（絶対パス）。大文字/小文字の指定まで正し
いことを確認する。
UTL_FILE_DIR = *
または、
UTL_FILE_DIR = c:\
c:\temp
fopenプロシージャで指定するディレクトリの書き方と全く同じでなくては有効になりません。
（PATHの最後に‘/’または‘¥’を付けるなど）
set serveroutput on
DECLARE
filehndl UTL_FILE.FILE_TYPE;
filedata varchar2(100);
BEGIN
/* 書き込みモードでOPEN
書き込みモードでOPEN */
filehndl := UTL_FILE.FOPEN('c:\
UTL_FILE.FOPEN('c:\temp','aaa.txt','w');
/* ファイルに
ファイルにHello
Hello Worldと出力
Worldと出力 */
UTL_FILE.PUT_LINE(filehndl,'Hello World');
UTL_FILE.FCLOSE(filehndl);
/* 読込モードで
読込モードでOPEN
OPEN */
filehndl := UTL_FILE.FOPEN('c:\
UTL_FILE.FOPEN('c:\temp','aaa.txt','r');
/* ファイルの内容を１行読み込む */
UTL_FILE.GET_LINE(filehndl,filedata);
/* ファイルから読み込んだ内容の表示
ファイルから読み込んだ内容の表示*/
*/
DBMS_OUTPUT.PUT_LINE(filedata);
UTL_FILE.FCLOSE(filehndl);
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE(filehndl);
RAISE_APPLICATION_ERROR(-20051,'Invalid Operation');
END;
/
文字コード・改行コードについて：
書き出される文字コードと改行コードはサーバーの環境に依存します。具体的には、文字
コードはデータベースのキャラクタセット、改行コードはサーバーのOSに依存します。
（例）UNIXサーバ、データベース・キャラクタセット JA16EUCの場合
• 日本語EUCの改行コード(0x0A)
8
組み込みパッケージ
- DBMS_SQL
z
動的SQLの実行
の実行
動的
–
z
DDL文、動的に条件の変わる
文、動的に条件の変わるSQL文
文
文、動的に条件の変わる
プログラムが汎用的になる。
動的SQLとは、コンパイル時は未完成（条件等が決まっていない）ようなDML文や、
DDL文のことを表わします。
実行時に、条件などが指定できるのでプログラムに汎用性を持たせることができます。
処理の手順は以下のようになります。
＜＜処理手順＞＞
① カーソルOPEN （DBMS_SQL.OPEN_CURSOR）
② SQL文の解析 （DBMS_SQL.PARSE)
③ EXECUTEのコール （DBMS_SQL.EXECUTE）
④ カーソルのクローズ （DBMS_SQL.CLOSE_CURSOR）
DBMS_SQLパッケージ・ファンクション（一部）
パッケージ・ファンクション（一部）
BIND_VARIABLE
所定の値を所定の変数にバインド。
DEFINE_COLUMN
指定されたカーソルから選択され、SELECT文だ
けで使用される列を定義します。
COLUMN_VALUE
所定のカーソルの所定の位置のカーソル要素の
値を戻す。
FETCH_ROWS
所定のカーソルから行をフェッチ。
IS_OPEN
指定されたカーソルがオープンならTRUE
参照：『アプリケーション開発者ガイド リリース8.0』
章
リリース 』 (A56822-1) - 14章
9
以下、指定された番号よりも大きな従業員番号を持つ従業員を、EMP表から検
索するサンプルです。
CREATE or REPLACE PROCEDURE rows_greater_than (low_value number) AS
cursor1
integer;
rows_processed
integer;
myempno number;
myename varchar2(20);
BEGIN
①
②
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse (cursor1, 'select empno, ename from emp where empno >
:x',dbms_sql.v7);
dbms_sql.bind_variable(cursor1, 'x', low_value);
dbms_sql.define_column (cursor1, 1, myempno);
dbms_sql.define_column (cursor1, 2, myename, 20);
rows_processed := dbms_sql.execute (cursor1);
③
loop
if dbms_sql.fetch_rows (cursor1) > 0 then
dbms_sql.column_value (cursor1, 1, myempno);
dbms_sql.column_value (cursor1, 2, myename);
dbms_output.put_line(to_char(myempno) || '
' || myename);
else
exit;
end if;
end loop;
dbms_sql.close_cursor (cursor1);
④
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
if dbms_sql.is_open (cursor1) then
dbms_sql.close_cursor (cursor1);
end if;
END;
/
----------- 以下、実行結果 ----------SQL> set serveroutput on
SQL> execute rows_greater_than(7900);
7902
FORD
7934
MILLER
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
10
組み込みパッケージ
- DBMS_PIPE
z
パイプ（SGA）を使ったセッション間通信
）を使ったセッション間通信
パイプ（
Database
Oracle SGA
メッセージ送信
セッション
メッセージ受信
セッション
同一インスタンス内のプログラムでお互いにメッセージのやり取りができます。
非同期に、いつでもメッセージを受け取ることが可能です。
SGAにメッセージ情報を記録しますので、SHUTDOWNを行うと消えてしまいま
す。
DBMS_PIPEパッケージ・ファンクション（一部）
パッケージ・ファンクション（一部）
PACK_MESSAGE(ﾒｯｾｰｼﾞｱｲﾃﾑ)
ローカル・バッファにメッセージを作成
SEND_MESSAGE(ﾊﾟｲﾌﾟ名,ﾀｲﾑｱｳﾄ,最大ﾊﾟｲﾌﾟｻｲｽﾞ)
名前付きパイプにメッセージを送信。名前付きパイプが存在しな
い場合には、パブリック・パイプが暗黙的に作成される。
RECEIVE_MESSAGE(ﾊﾟｲﾌﾟ名,ﾀｲﾑｱｳﾄ)
名前付きパイプからローカル・バッファへメッセージをコピー。
UNPACK_MESSAGE(ﾒｯｾｰｼﾞｱｲﾃﾑ)
バッファ内の次の項目へアクセス。
PURGE(ﾊﾟｲﾌﾟ名)
名前付きパイプの内容を削除。
参照：『アプリケーション開発者ガイド リリース8.0』
章
リリース 』 (A56822-1) - 12章
11
以下、PIPEを使用した簡単なサンプルプログラムです。
SQL*Plusを2つ立ち上げ、接続します。（ユーザーは同じでなくても可）
一方を＜メッセージ送信セッション＞、もう片方を<メッセージ受信セッション>として
以下を実行してください。
<メッセージ送信セッション>
メッセージ送信セッション>
declare
pipe_stat integer;
begin
/*項目をメッセージ･バッファに詰め込む
/*項目をメッセージ･バッファに詰め込む*/
項目をメッセージ･バッファに詰め込む*/
DBMS_PIPE.PACK_MESSAGE('HELLO!!');
/* メッセージ･バッファの内容をパイプに送信 */
PIPE
pipe_stat :=DBMS_PIPE.SEND_MESSAGE('pipe1',10);
end;
/
<メッセージ受信セッション>
メッセージ受信セッション>
declare
pipe_stat integer;
item_val varchar2(10);
begin
/* メッセージを受け取りメッセージ・バッファにコピー */
pipe_stat :=DBMS_PIPE.RECEIVE_MESSAGE('pipe1',30);
/* メッセージ･バッファから変数に格納 */
DBMS_PIPE.UNPACK_MESSAGE(item_val);
DBMS_OUTPUT.PUT_LINE(item_val);
/* パイプの内容を空にする */
DBMS_PIPE.PURGE('pipe1');
end;
/
実際の利用例としては、ストアド・プロシージャ等から送信されたメッセージをPro*C
プログラムで受信し、OS上のコマンドを起動するといったことが考えられます。
参照：『アプリケーション開発者ガイド リリース8.0』
章-14 例-2
リリース 』 (A56822-1) - 12章
表を使って、メッセージのやり取りを行う方法も考えられますが、送信時に
COMMITを発行しなければならないので、他の処理に影響が及ぶ可能性が有る
という点に注意する必要が有ります。PIPEではその必要は有りません。
12
組み込みパッケージ
- DBMS_ALERT
アプリケーションB
アプリケーションB
(WAIT中
WAIT中)
アプリケーションA
アプリケーションA
更新
Oracle
Database
更新の通知
EMP表
EMP表
トリガー
EMP_TRIG
DBMS_ALERTパッケージで提供されるアラート機能を使用して、データベース
イベントの通知を行うことが可能です。トリガーの機能に似ていますが、トリガー
はDML文の実施ごとに発生するのに対し，アラートはトランザクション処理がコミッ
トされた時点で発行されます。（トランザクションがロールバックされるとアラートは
発生しません）
上の例では、アプリケーションAによるEMP表の更新を自動的にアプリケーショ
ンBに通知する例になります。
DBMS_PIPEとの違いは、
• DBMS_ALERTはシグナルを送るトランザクションがCOMMITされてか
らアラートを送る。
• アラート発生時にユーザーの要求が無ければ受け取らない。
• （DBMS_ALERTはWAITしつつアラートを待つのに対して、
DBMS_PIPEはメッセージをPIPEに取りにいく）
ということです。
参照：『アプリケーション開発者ガイド リリース8.0』
章
リリース 』 (A56822-1) - 16章
13
以下サンプルです。
実行方法は、以下の通りです。
SQL*Plusを2つ立ち上げ、同じユーザーで接続します。
１. 一方のSQL*Plusから、TESTREGISTER.SQLを実行します。
２. もう一方から、TESTSIGNAL.SQLを実行します。
TESTREGISTER.SQL
---------------set serveroutput on;
declare
namex varchar(50) :='aaa';
msg varchar(50) := '';
status number := 99;
DBMS_ALERT.REGISTER(ｱﾗｰﾄ名)
timeout number := 60;
begin
/* アラートに関心のあることを登録 */
dbms_alert.register(namex);
commit;
/* シグナルを待つ(
シグナルを待つ(タイムアウト60
タイムアウト60秒
60秒) */
dbms_alert.waitany(namex,msg,status,timeout);
dbms_output.put_line('name=' || namex);
dbms_output.put_line('msg=' || msg);
dbms_output.put_line('status=' || status || ',timeout=' ||
timeout);
end;
/
DBMS_ALERT.WAITANY(ｱﾗｰﾄ名,ﾒｯｾｰｼﾞ,ｽﾃｰﾀｽ,ﾀｲﾑｱｳﾄ)
TESTSIGNAL.SQL
-------------declare
name varchar(50) := 'aaa';
msg varchar(50) := 'テスト･メッセージ
'テスト･メッセージ';
テスト･メッセージ';
begin
dbms_alert.signal(name,msg);
commit;
end;
/
DBMS_ALERT.SIGNAL(ｱﾗｰﾄ名,ﾒｯｾｰｼﾞ)
----------------- 以下、実行結果 ----------------------SQL> @TESTREGISTER.SQL
name=AAA
msg=テスト･メッセージ
msg=テスト･メッセージ
status=0,timeout=60
PL/SQLプロシージャが正常に完了しました。
PL/SQLプロシージャが正常に完了しました。
14
組み込みパッケージ
- DBMS_JOB
z
ジョブのスケジューリング
–
ジョブの1回実行、反復実行
ジョブの 回実行、反復実行
–
ジョブをバックグラウンド、並列実行
SUBMIT --------- ジョブをキューに送る。
REMOVE --------- キューから指定したジョブを削除する。
CHANGE --------- 指定したジョブを変更する。
[ジョブの定義、ジョブの実行時刻、ジョブの実行間隔]
WHAT ----------- 指定したジョブのジョブの定義を変更
NEXT_DATE ------ 指定したジョブの次の実行時刻を変更
INTERVAL ------- 指定したジョブの実行間隔を変更
BROKEN --------- ジョブの実行を禁止にする。
RUN ------------ 指定したジョブを強制的に実行させる。
SNP バックグラウンド・プロセス
待ち行列に入っている実行予定のジョブキューを定期的に起動し、実行します。
ＳＮＰプロセスの設定はINIT.ORAの以下のパラメータで行います。
JOB_QUEUE_PROCESSES ： プロセス数。
JOB_QUEUE_INTERVAL ： インスタンスのSNPバックグラウンド・プロ
セスの起動する間隔を設定する。
ジョブキューの情報は以下のディクショナリから、取得できます。
DBMS_JOB_RUNNING ： 現在実行中のJOB
DBA_JOBS,USER_JOBS ： JOBの一覧
以下、ジョブをキューに送る例です。1日1回proc1プロシージャが実行さ
れます。
DECLARE
JOB# BINARY_INTEGER;
BEGIN
DBMS_JOB.SUBMIT(JOB#,’proc1
proc1’,SYSDATE,
,SYSDATE,’SYSDATE+1
DBMS_JOB.SUBMIT(JOB#, proc1
,SYSDATE, SYSDATE+1’);
SYSDATE+1 );
END;
参照：『Oracle8
Server 管理者ガイド リリース8.0』
章
参照：『
リリース 』 (A56816-1) - 7章
15
組み込みパッケージ
- DBMS_UTILITY
z
便利なユーティリティ
P
P
P
P
ANALYZE_SCHEMA
COMPILE_SCHEMA
COMMA_TO_TABLE
TABLE_TO_COMMA
アナライズ（解析）
コンパイル
カンマ区切りの文字列をPL/SQL
カンマ区切りの文字列をPL/SQL表に格納
PL/SQL表に格納
PL/SQL表からカンマ区切りの文字列に移動
PL/SQL表からカンマ区切りの文字列に移動
F
F
F
F
FORMAT_ERROR_STACK
FORMAT_CALL_STACK
GET_TIME
PORT_STRING
現在のエラースタック入手（参照:
現在のエラースタック入手（参照:Page43,44）
Page43,44）
現在のコール・スタック入手（参照:
現在のコール・スタック入手（参照:Page42）
Page42）
経過時間（参照:
経過時間（参照:Page46）
Page46）
Oracle、
Oracle、OS情報
OS情報
P：プロシージャ、F:
：プロシージャ、F:ファンクション
F:ファンクション
ANALYZE_SCHEMA、
、COMPILE_SCHEMA
指定されたオブジェクトをアナライズ/コンパイルします。
スキーマ全体の一括処理
COMMA_TO_TABLE,TABLE_TO_COMMA
PLSQL表 ←→ カンマ区切り文字列
declare
out_tabb dbms_utility.uncl_array;
begin
:in_list := 'scott,emp,ename';
dbms_utility.comma_to_table(:in_list,:out_tablen,out_tabb);
......
例えば、CSV形式のファイルを読み込んで加工するときなどに使用しま
す。
PORT_STRING
Oracleのバージョン（init.oraのCOMPATIBLEの内容）、OS情報
(例)
SunSolaris2.6上で、Oracle8.0.5の実行例： SVR4-be-8.0.0
WindowsNT4.0上のOracle8.1.5の実行例： IBMPC/WIN_NT-8.1.0
16
組み込みパッケージ
- その他
z
DBMS_LOCK.SLEEP
… 特定時間セッションを中断（秒数を指定）
z
DBMS_RANDOM
Oracle8i
… ランダム値を生成（-9999999999～
～9999999999）
）
ランダム値を生成（
z
DBMS_UTILITY. EXEC_DDL_STATEMENT
Oracle8i
… DDL文を実行
文を実行
DBMS_RANDOM,DBMS_UTILITY.EXEC_DDL_STATEMENTについては、
Oracle8ではマニュアルに記述が有りませんので、サポート対象外です。
Oracle8iでサポートします。
以下、3つのパッケージの利用例です。
DBMS_RANDOMを利用するには、
$ORACLE_HOME/rdbms/admin/catoctk.sqlを実行してください。
SET SERVEROUTPUT ON
BEGIN
DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TABLE SOME_TABLE');
DBMS_UTILITY.EXEC_DDL_STATEMENT('CREATE TABLE TEST (A NUMBER)');
END;
/
DECLARE
I BINARY_INTEGER;
BEGIN
DBMS_RANDOM.INITIALIZE(92541);
FOR CNT IN 1..10
LOOP
DBMS_LOCK.SLEEP(10);
I := DBMS_RANDOM.RANDOM;
INSERT INTO SOME_TABLE VALUES(I);
COMMIT;
END LOOP;
DBMS_RANDOM.TERMINATE;
END;
/
17
DDL文の実行をOracle8で行うにはには動的SQL(DBMS_SQL)を使います。
DBMS_RANDOMについては、以下の例のようにすると簡単な疑似乱数が取得
できます。
create or replace package random
is
procedure srand( new_seed in number );
function rand return number;
procedure get_rand( r OUT number );
function rand_max( n IN number ) return number;
procedure get_rand_max( r OUT number, n IN number );
end random;
/
実行
declare
i pls_integer;
randno pls_integer;
begin
random.srand(92541);
random.get_rand_max(i,92541);
dbms_output.put_line(i);
i:=1;
for i in 1..10 loop
random.get_rand(randno);
dbms_output.put_line(i||' | '||randno);
end loop;
end;
/
create or replace package body random
is
multiplier
constant number
:= 22695477;
increment
constant number
:= 1;
"2^32"
constant number
:= 2 ** 32;
"2^16"
constant number
:= 2 ** 16;
"0x7fff"
constant number
:= 32767;
Seed
number := 1;
-procedure srand( new_seed in number )
is
begin
Seed := new_seed;
end srand;
-function rand return number
is
begin
seed := mod( multiplier * seed + increment, "2^32" );
return bitand( seed/"2^16", "0x7fff" );
end rand;
-procedure get_rand( r OUT number )
is
begin
r := rand;
end get_rand;
-function rand_max( n IN number ) return number
is
begin
return mod( rand, n ) + 1;
end rand_max;
-procedure get_rand_max( r OUT number, n IN number )
is
begin
r := rand_max( n );
end get_rand_max;
-begin
srand( userenv( 'SESSIONID' ) );
end random;
/
grant execute on random to public
/
create public synonym random for random
/
18
コード設計/
コード設計/開発のヒント(1)
開発のヒント(1)
z
モジュール名・パラメータ名
–
z
独立したモジュール
–
z
パラメータ・モード
再利用
パラメータ設計のヒント
–
グローバル変数の利用に注意。
–
コメントを付ける。
PROCEDURE calc_elapsed_time
(start_time_in IN NUMBER
/* スタート時間 値域：0
値域：0～24 */
start_loc_in IN VARCHAR2 /* 出発地：NY,CHI,SF
出発地：NY,CHI,SF */
end_loc_in
IN VARCHAR2 /* 目的地：DC,LA,NO
目的地：DC,LA,NO */;
プロシージャ、関数、パラメータの使用についての効果的なPL/SQLプログラミン
グの方法をまとめます。
モジュール名・パラメータ名
• モジュールの動作内容の分かるモジュール名をつける。
• 仮パラメータのパラメータ･モードを使いパラメータの種類が分かるよう
にする。ただし、ファンクションにはOUTパラメータは使わない。
独立したモジュール
• モジュールを作成するときには、現在のシステムとそれ以外のシステム
でも再利用できるような書き方をする。
• コードの記述量が減り、バグの発生も開発時間も減少する。
パラメータ設計のヒント
• グローバル変数はできるだけ使わず、パラメータを使用する。
（モジュールのインターフェイスのみで使用方法が分かるように）
• パラメータと機能についてコメントをつける。
19
コード設計/
コード設計/開発のヒント(2)
開発のヒント(2)
ファンクション（関数）の利用
z
–
–
–
汎用性のある関数を作る。
関数は1つ値を返すようにコーディングする。
つ値を返すようにコーディングする。OUT
関数は
つ値を返すようにコーディングする。
パラメータは使用しないようにする。
例外処理
–
無効なパラメータのトラップ
ファンクション（関数）の利用
ファンクションは、式の一部としてコールされます。
IF sal_ok(new_sal, new_title) THEN ...
使用しやすさから、ファンクションは複数の値を返すようにコーディングす
べきでは有りません。
----- 以下、関数の例 --------------------------------------------FUNCTION status_desc (status_cd_in IN VARCHAR2) RETURN VARHAR2
IS
return_value VARCHAR2(20) := NULL;
BEGIN
/* 有効なパラメータの検査 */
C’ THEN return_value := ‘CLOSED
CLOSED’;
IF status_cd_in = ‘C
CLOSED ;
ELS IF status_cd_in = ‘O
O’ THEN return_value := ‘OPEN
OPEN’;
OPEN ;
ELSE
RAISE value_error;
END IF;
RETURN return_value;
EXCEPTION
WHEN value_error THEN
RETURN NULL;
END;
20
コード設計/
コード設計/開発のヒント(3)
開発のヒント(3)
ローカル・モジュールの活用
z
–
コードの重複をなくす
ローカル･モジュール ：
宣言部内に定義されたプロシージャ / ファンクション
有効範囲は宣言されているモジュール内
ローカル・モジュールの活用
以下の例では、実行部で繰り返し利用される処理をファンクション（NPV）
として、宣言部に定義しています。これによって処理の定義を繰り返し行
わなくてすみます。
PROCEDURE format_data
(projected_sales_in IN NUMBER,year_in IN INTEGER)
IS
total_cost
NUMBER(9);
gross_profit NUMBER(9);
crew_labor
mgmt_labor
宣言部
NUMBER(9);
NUMBER(9);
/* ---------- ローカル･モジュール ----------*/
FUNCTION npv (column_in IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN
TO_CHAR((net_present_value ( ‘‘ || column_in, year_in) /
*100),’999.99
999.99’);
projected_sales_in *100),
999.99 );
END;
BEGIN
:owner.total_cost_pc := npv(‘total_cost
npv( total_cost’);
total_cost );
:owner.gross_profit_pc
:owner.gross_profit_pc := npv(‘grew_profit
npv( grew_profit’);
grew_profit );
実行部
:owner.crew_labor_pc
:owner.crew_labor_pc := npv(‘crew_labor
npv( crew_labor’);
crew_labor );
:owner.mgmt_labor_pc
:owner.mgmt_labor_pc := npv(‘mgmt_labor
npv( mgmt_labor’);
mgmt_labor );
END;
21
コード設計/
コード設計/開発のヒント(4)
開発のヒント(4)
z
LOOP、無名ブロックにラベルを付ける
、無名ブロックにラベルを付ける
<<ラベル
<<ラベル>>
ラベル>>
z
DB内のレコードの存在チェック方法
内のレコードの存在チェック方法
cursor%NOTFOUND
LOOP、無名ブロックにラベル
、無名ブロックにラベル
<<every_month>>
FOR month IN 1..12 LOOP
END LOOP every_month;
<<sgn>>
BEGIN
dbms_output.put_line('hello...');
END sgn;
レコードの有無の確認
DB内のレコードの存在チェック方法を調べるには'SELECT COUNT(*)
...'を使わずに、 カーソルを開いて1行FETCHしcursor%NOTFOUND.
declare
cursor c1 is select * from notfound;
not_rec c1%rowtype;
begin
open c1;
fetch c1 into not_rec;
if (c1%notfound) then
dbms_output.put_line('Nashi');
end if;
end;
/
22
コード設計/
コード設計/開発のヒント(5)
開発のヒント(5)
z
パッケージ化
プロシージャ
• 利点
ファンクション
変数宣言
定数宣言
カーソル宣言
例外宣言
-
モジュール性
トップダウンアプリケーション設計
情報隠蔽
永続的状態
パフォーマンスの向上
モジュール性
理論的に関連するオブジェクトをまとめる
トップダウンアプリケーション設計
パッケージ内の手続き部分のコーディング以前に、呼び出し方法のみを
登録できる。
情報隠蔽
外部から使用できる要素（パブリック･プロシージャ、グローバル変数）と、
パッケージ内でのみ使用できる要素（プライベート･プロシージャ、ローカ
ル変数）を区別できる。
永続的状態
変数やカーソルはセッションを通じてその内容を保持する。
単一Oracleセッション内でグローバル
パフォーマンスの向上
パッケージ内の要素が呼び出されると、パッケージ全体がメモリにロード
される。
23
パッケージ仕様部
• 仕様部分のみの記述
• 仕様部分のみでコンパイル可能
CREATE PACKAGE package_name
IS
[ 変数と型の宣言 ]
[ カーソルの仕様 ]
[モジュールの仕様]
モジュールの仕様]
PROCEDURE ...(..);
FUNCTION ...(..);
END; [ パッケージ名 ]
パッケージ本体
• カーソルとモジュールの完全な実装
CREATE PACKAGE BODY package_name
IS
[ 変数と型の宣言 ]
[ カーソルの仕様とSELECT
カーソルの仕様とSELECT文
SELECT文 ]
[モジュールの仕様と本体]
モジュールの仕様と本体]
PROCEDURE ...(..)
IS
BEGIN
処理
END;
FUNCTION ...(..)
IS
BEGIN
処理
END;
[ BEGIN
実行文 ]
[ EXCEPTION
例外ハンドラ ]
END; [ パッケージ名 ]
24
コード設計/
コード設計/開発のヒント(6)
開発のヒント(6)
変数の宣言
z
–
%TYPE
my_empno emp.
emp.empno%TYPE;
empno%TYPE;
–
%ROWTYPE
emp_rec emp%ROWTYPE;
emp%ROWTYPE;
cursor c1 is select * from dept;
dept_rec c1%ROWTYPE;
変数の宣言 （ %TYPE、
、%ROWTYPE ）
＜＜%TYPE＞＞
データベースの要素が変わっても、動作可能なモジュールが作成できま
す。（生成時にデータベースを参照、データベースの要素が変更される
と自動的に再コンパイルされます）
また、正確なデータ型を知らなくてすみます。
上の例では、my_empno をemp表のempnoカラムと同じデータ型で扱い
ます 。
＜＜%ROWTYPE＞＞
上の一番目の例では、emp表から選択された行が格納できます。例えば、
以下のように使用します。
ドット表記で各フィールドを参照できます。
DECLARE
emp_rec emp%ROWTYPE;
BEGIN
select * into emp_rec from emp where ...;
emp_req.sal = emp_req.sal + 10;
...
2番目の例は、カーソルc1で取り出された行を格納できます。
25
その他 TIPS
- コレクション
z
PL/SQL表
表 Vs. Nested Table & Varray
PL/SQL表
PL/SQL表
PL/SQL内
PL/SQL内
z
Nested Table &Varray
&Varray
PL/SQL内、データベース内
PL/SQL内、データベース内
Nested Table と Varray
要素数の制限の指定
順序付け
Varray
Nested Table
必要あり
あり
必要なし
なし
PL/SQL リリース2（Oracle7）までは、PL/SQL表という一次元の配列をサポートし
ていました。
PL/SQL リリース8.0(Oracle8)からはNested TableとVarrayをサポートします。
VARRAYは要素の数の制限を指定する必要があります。
Nested Tableは上限がないため各オブジェクトの変更が自由に行えます。
VARRAYと
とNested Tableは、
は、PL/SQL内で利用する場合には他に違いは有り
内で利用する場合には他に違いは有り
は、
ません。
＊現時点PL/SQL
リリース8.0（
＊現時点
リリース （Oracle8 リリース8.0）では、
リリース ）では、3つのデータ型ともサ
）では、 つのデータ型ともサ
ポートしていますが、新規に作成される場合PLSQL表より
表よりNested
Tableを使うこ
を使うこ
ポートしていますが、新規に作成される場合
表より
とをお勧めします。
26
declare
type color_tab_type is table of varchar2(10)
index by binary_integer;
color_tab color_tab_type;
begin
color_tab(1) := 'Black';
color_tab(2) := 'White';
color_tab(3) := 'Green';
color_tab(4) := 'Red';
PL/SQL表
PL/SQL表
for i in 1..color_tab.count
loop
dbms_output.put_line(color_tab(i));
end loop;
end;
/
declare
type Color_tab_t is table of varchar2(10);
colors Color_tab_t := Color_tab_t();
begin
colors.extend(4);
colors(1) := 'Blue';
colors(2) := 'Yellow';
colors(3) := 'Gold';
colors(4) := 'Silver';
Nested Table
for i in 1..colors.count
loop
dbms_output.put_line(colors(i));
end loop;
end;
/
declare
type Color_varray_t is varray(16) of varchar2(10);
colors Color_varray_t := Color_varray_t();
begin
colors.extend(4);
colors(1) := 'Pink';
colors(2) := 'Purple';
colors(3) := 'Gray';
colors(4) := 'Orange';
Varray
for i in 1..colors.count
loop
dbms_output.put_line(colors(i));
end loop;
end;
/
27
その他 TIPS
- ラージ･オブジェクト（LOB
ラージ･オブジェクト（LOB）
LOB）
z
z
… データベース表領域内に格納
内部LOB
内部
–
BLOB － バイナリファイル
–
バイト固定幅の文字データ
CLOB － 1バイト固定幅の文字データ
–
NCLOB －マルチバイト固定幅の文字データ
… 表領域外に格納、OS
外部LOB
ファイル
外部
表領域外に格納、
–
BFILE － バイナリ・ファイル
DBMS_LOBパッケージ
パッケージ … PL/SQLで
でLOBを操作
を操作
Oracle8ではLOB（４GBまでのRAWまたはバイナリ・データ、文字データ）を格納できるデータ型
、SQL DDL/DMLコマンド、OCI/PLSQLのAPIを提供しています。
• LOBはレプリケーションすることも可能（BFILEを除く)
• 一つの表に複数のカラムを持つことも可能です。
• 格納方法もOracleの内部はもちろん、外部にも格納することができます。
• データベース・バッファを有効に利用するために、LOB型データをバッファリングする
かどうかの指定をすることが可能です。
• ロギングも行うかどうかの指定ができます。
DBMS_LOB
APPEND
LOB値を別の
LOB値を別のLOB
値を別のLOBに追加します。
LOBに追加します。
COPY
LOBの一部を別の
LOBの一部を別のLOB
の一部を別のLOBにコピーします。
LOBにコピーします。
ERASE
指定のオフセットから開始して、LOB
指定のオフセットから開始して、LOBの一部を消去します。
LOBの一部を消去します。
LOADFROMFILE
BFILEデータを内部
BFILEデータを内部LOB
データを内部LOBにロードします。
LOBにロードします。
WRITE
指定されたオフセットからLOB
指定されたオフセットからLOBにデータを書き込みます。
LOBにデータを書き込みます。
GETLENGTH
LOB値の長さを取得します。
LOB値の長さを取得します。
READ
指定されたオフセットからLOB
指定されたオフセットからLOBデータを読み込みます。
LOBデータを読み込みます。
FILEOPEN
ファイルをオープン。
FILECLOSE
ファイルをクローズ。
OPEN
LOBを
LOBをOPEN。
OPEN。
CLOSE
LOBを
LOBをCLOSE。
CLOSE。
28
BLOB,BFILE 格納と長さの取得
以下のサンプルでは、
1. BLOB、BFILEの各カラムにファイル(/tmp/aaa)の内容を挿入します。
2. DB内の、BLOB、BFILEデータを読み込み内容の長さを取得、表示します。
CREATE TABLE TEMP
( KEY INTEGER, IMG_IN_DB BLOB, IMG_OUT_DB BFILE );
CREATE DIRECTORY HDOC AS '/TMP';
DECLARE
LOBD
BLOB;
FILS
BFILE := BFILENAME('HDOC', 'aaa');
AMT
INTEGER;
BEGIN
/* ファイルを読込み専用でオープン */
DBMS_LOB.FILEOPEN(FILS, DBMS_LOB.FILE_READONLY);
/* ファイルの長さを取得 */
AMT:=DBMS_LOB.GETLENGTH(FILS);
/* BLOBカラムを初期化、空の
BLOBカラムを初期化、空のBLOB
カラムを初期化、空のBLOB値と共に
BLOB値と共に1
値と共に1行INSERT、ロケータを返す
INSERT、ロケータを返す */
INSERT INTO TEMP VALUES (1, EMPTY_BLOB(), FILS)
RETURNING IMG_IN_DB INTO LOBD;
/* LOB値に実データを挿入
LOB値に実データを挿入 */
DBMS_LOB.LOADFROMFILE(LOBD, FILS, AMT);
COMMIT;
/* ファイルをクローズ */
DBMS_LOB.FILECLOSE(FILS);
/* BFILE , BLOBに格納された内容を読込、出力
BLOBに格納された内容を読込、出力 */
SELECT IMG_IN_DB, IMG_OUT_DB INTO LOBD, FILS FROM TEMP;
AMT:=DBMS_LOB.GETLENGTH(FILS);
DBMS_OUTPUT.PUT_LINE(‘BFILE:
||AMT);
DBMS_OUTPUT.PUT_LINE( BFILE: ’||AMT);
AMT:=DBMS_LOB.GETLENGTH(LOBD);
DBMS_OUTPUT.PUT_LINE(‘BLOB:
||AMT);
DBMS_OUTPUT.PUT_LINE( BLOB: ’||AMT);
END;
/
実行結果：
BFILE: 9
BLOB: 9
29
CLOB 格納と操作
以下のサンプルでは、
1. CLOBカラムに文字列（‘Character Lob’）を挿入します。
2. DB内の、CLOBデータを2文字ずつ読み込み、表示します。
CREATE TABLE TEST (A NUMBER,B CLOB);
INSERT INTO TEST VALUES (1,'Character Lob');
COMMIT;
SELECT A,DBMS_LOB.GETLENGTH(B) FROM TEST;
実行結果：
A DBMS_LOB.GETLENGTH(B)
--------- --------------------1
13
DECLARE
LOB_LOC
CLOB;
BUFFER
VARCHAR2(2); /* 読込バッファ*/
読込バッファ*/
AMOUNT
BINARY_INTEGER := 2;
LENGTH
BINARY_INTEGER;
POSITION
INTEGER := 1;
BEGIN
SELECT B INTO LOB_LOC FROM TEST;
DBMS_LOB.OPEN(LOB_LOC, DBMS_LOB.LOB_READONLY);
/* CLOBの内容の長さを入手
CLOBの内容の長さを入手 */
LENGTH := DBMS_LOB.GETLENGTH(LOB_LOC);
/* オフセットを2
オフセットを2文字ずつ進め、文字をバッファに読み込む */
WHILE POSITION <= LENGTH LOOP
DBMS_LOB.READ (LOB_LOC, AMOUNT, POSITION, BUFFER);
POSITION := POSITION + AMOUNT;
DBMS_OUTPUT.PUT_LINE(BUFFER);
END LOOP;
DBMS_LOB.CLOSE(LOB_LOC);
END;
/
実行結果：
Ch
ar
ac
te
r
Lo
b
NCLOB等を使用するためには、各国語キャラクタ・セットに固定幅マルチバイト・キャラクタ
NCLOB等を使用するためには、各国語キャラクタ・セットに固定幅マルチバイト・キャラクタ
・セットを指定してください。
CREATE DATABASE時に
DATABASE時にNATIONAL
時にNATIONAL CHARACTER SETに適切なキャラクタセット
SETに適切なキャラクタセット(
に適切なキャラクタセット(JA16SJISFIXEDや
JA16SJISFIXEDや
JA16EUCFIXED等）を指定します。
JA16EUCFIXED等）を指定します。
また、一度設定した値をCREATE
また、一度設定した値をCREATE DATABASE後に変更することはできません。
DATABASE後に変更することはできません。
30
コードの管理
z
ラッピング
z
データベース内のコード
–
USER_OBJECTS
–
USER_OBJECT_SIZE
–
USER_SOURCE
–
USER_DEPENDENCIES
31
コードの管理
- ラッピング
z
PL/SQL ラッパー
–
PL/SQLソースコードをオブジェクトコードの
ソースコードをオブジェクトコードの
中間形式に変換する。
–
アプリケーションの内部を隠す。
以下のコマンドを実行
WRAP INAME=input_file
INAME=input_file [ONAME=output_file
[ONAME=output_file]
output_file]
PL/SQL ラッパーを使用すると、PL/SQLソースコードをオブジェクトコードの中間
形式に変換します。
ディクショナリ内でも、ラップ化されます。（USER_SOURCE等）
利点
• コードを隠して、アプリケーションの公開
• ソースコードと同等の移植性（動的ローディング、EXP/IMPなど）
ラップ前
CREATE OR REPLACE PROCEDURE get_nextline
(file_in IN UTL_FILE.FILE_TYPE,
line_out OUT VARCHAR2,
eof_out OUT BOOLEAN)
IS
BEGIN
UTL_FILE.GET_LINE (file_in, line_out);
eof_out := FALSE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
line_out := NULL;
eof_out := TRUE;
END;
ラップ後
CREATE OR REPLACE PROCEDURE get_nextline wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
。。
32
コードの管理
- DB内のコード
DB内のコード(1)
内のコード(1)
z
USER_OBJECTS
–
PL/SQLオブジェクトの状態
オブジェクトの状態
OBJECT_TYPE
OBJECT_NAME
STATUS
-------------------- ------------------------------ --------PACKAGE
DBMS_REPCAT_AUTH
VALID
PACKAGE BODY
DBMS_REPCAT_AUTH
VALID
PROCEDURE
ORA$_SYS_REP_AUTH
VALID
USER_OBJECTS：ユーザーが所有するオブジェクト情報
：ユーザーが所有するオブジェクト情報
モジュール内で参照されている表・他のプログラムが変更されるとそのモ
ジュールの状態（STATUS）がINVALIDになります。INVALID状態のモ
ジュールは実行されるときに自動的に再コンパイルされます。その間、ユー
ザーは待ち状態となってしまいます。それを回避する為に手動で再コン
パイルしておく必要が有ります。
以下のスクリプトをSQL*Plusで実行すると上のような結果が得られます。
SET PAGESIZE 66
COLUMN object_type FORMAT A20
COLUMN object_name FORMAT A30
COLUMN status FORMAT A10
BREAK ON object_type SKIP 1
SPOOL psobj.lis
SELECT object_type, object_name, status
FROM user_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION',
'PROCEDURE')
ORDER BY object_type, status, object_name
/
SPOOL OFF
33
コードの管理
- DB内のコード
DB内のコード(2)
内のコード(2)
z
USER_OBJECT_SIZE
–
PL/SQLコードサイズの分析
コードサイズの分析
火 May 25
ページ
1
Size of PL/SQL Objects > 2000 Bytes
NAME
CODE_SIZE
TYPE
SOURCE_SIZE PARSED_SIZE
------------------ -------------- ----------- ----------- -------DBMS_REPCAT_AUTH
3130
PACKAGE BODY
10712
0
USER_OBJECT_SIZE：ユーザーが所有するオブジェクトのサイズ
：ユーザーが所有するオブジェクトのサイズ
ストアド･オブジェクトのコンパイル済みのコードは、DBのSGAにロードさ
れ実行されるので、あまりに大きなプログラムは避けるべきです。（大きな
プログラムをSGAに確保するとそれ以外のプログラムがSGAから排除さ
れてしまい、次回の実行時にロードし直さなくてはならない為）
以下のスクリプトをSQL*Plusで実行すると、上のように比較的大きなモ
ジュールを検索できます。
SET PAGESIZE 66
COLUMN name FORMAT A30
COLUMN type FORMAT A15
COLUMN source_size FORMAT 999999
COLUMN parsed_size FORMAT 999999
COLUMN code_size FORMAT 999999
TTITLE 'Size of PL/SQL Objects > 2000 Bytes'
SPOOL pssize.lis
SELECT name, type, source_size, parsed_size, code_size
FROM user_object_size
WHERE code_size > 2000
ORDER BY code_size DESC
/
SPOOL OFF
34
コードの管理
- DB内のコード
DB内のコード(3)
内のコード(3)
z
USER_SOURCE
–
ソース･コードの相互参照
–
行番号でソースを検索
LINE TEXT
---------1
2
3
4
5
6
-------------------------------------------------PROCEDURE get_nextline
(file_in
(file_in IN UTL_FILE.FILE_TYPE,
line_out OUT VARCHAR2,
eof_out OUT BOOLEAN)
IS
BEGIN
USER_SOURCE：作成したオブジェクトのソース
：作成したオブジェクトのソース
以下のスクリプトをSQL*Plusで実行すると上の例のように、ソースが参照
できます。
これによって、PL/SQLが表示するエラーの発生行を特定することができ
ます。
column text format a50
select line,text from user_source
where name=UPPER(‘&1’) /* オブジェクトの名前 */
and type = 'PROCEDURE' /* オブジェクトのタイプ */
order by line;
35
コードの管理
- DB内のコード
DB内のコード(4)
内のコード(4)
z
USER_DEPENDENCIES
–
FROM_
オブジェクトの依存関係を参照する
TO_
TO_TYPE
TO_OWNER
TO_LINK
------------ ------------ ------------ ------------ -----------PROC2
PROC1
PROCEDURE
SCOTT
PROC3
PROC2
PROCEDURE
SCOTT
PTEST2
PTEST1
PROCEDURE
SCOTT
USER_DEPENDENCIES：ユーザーが所有するオブジェクトの依存関係
：ユーザーが所有するオブジェクトの依存関係
以下のスクリプトをSQL*Plusで実行すると上の例のように、SCOTTユー
ザーのプロシージャを参照しているオブジェクトが表示されます。（TO_
に表示されるオブジェクトを変更するとFROM_ に表示されている
PL/SQLオブジェクトが影響を受けINVALIDになる）
column From_ format a12
column To_ format a12
column To_TYPE format a12
column To_OWNER format a12
column To_LINK format a12
SELECT name From_,
referenced_name To_,
referenced_type To_type,
referenced_owner To_owner,
referenced_link_name To_link
FROM user_dependencies
WHERE referenced_owner = UPPER ('scott')
AND referenced_type = UPPER ('procedure');
36
PL/SQLのデバッグ
PL/SQLのデバッグ
z
コンパイル時のエラー表示
z
実行時の変数、式の値
z
Tool
z
コール・スタック情報
z
エラー･スタック情報
37
PL/SQLのデバッグ
PL/SQLのデバッグ
- コンパイル時のエラー表示
z
SHOW ERRORS （SQL*Plus）
）
–
コンパイル時のエラーを表示する
警告:
警告: プロシージャが作成されましたが、
コンパイル・エラーがあります。
プロシージャ、パッケージのコンパイル時のエラーはSQL*Plusを用いている場
合、SHOW ERRORSコマンドを使用して表示することができます。
ここでのエラーの行番号は、USER_SOURCE（参照：Page35)で確認してください。
SQL> SHOW ERRORS PROCEDURE ANCHOR_TST
PROCEDURE ANCHOR_TSTのエラーです。
ANCHOR_TSTのエラーです。
LINE/COL ERROR
-------- ----------------------------------------------------------------3/10
PLS-00201: 識別子:
識別子: PLS_ANCHOR_TEST.AAAを宣言してください。
PLS_ANCHOR_TEST.AAAを宣言してください。
3/10
PL/SQL: Item ignored
4/16
PL/SQL: SQL Statement ignored
4/32
PLS-00201: 識別子:
識別子: PLS_ANCHOR_TESTを宣言してください。
PLS_ANCHOR_TESTを宣言してください。
7/3
PL/SQL: SQL Statement ignored
7/17
PLS-00320: この式の型の宣言が不完全か、または形式が誤っています。
8/3
PL/SQL: Statement ignored
8/24
PLS-00320: この式の型の宣言が不完全か、または形式が誤っています。
38
PL/SQLのデバッグ
PL/SQLのデバッグ
- 実行時の変数、式の値
z
変数および式の値
–
標準出力
DBMS_OUTPUTパッケージ
–
ダミー表に挿入
Insert + Commit
–
PIPE
DBMS_PIPEパッケージ
–
外部ファイル（OSファイル）に出力
外部ファイル（ ファイル）に出力
UTL_FILEパッケージ
DBMS_OUTPUTのPUT文、PUT_LINE文を使用して、変数および式の値を端
末に出力できます。
パッケージなどを作成して、使いやすいように改良。 簡単な入力ですむようにな
ど
以下、利用方法と内容が確認できるタイミングの違いについてまとめました。
DBMS_OUTPUT （標準出力） （参照： Page6）
）
実行後にまとめて出力されます。以下の例では、約10秒後に全結果が
出力されます。
set serveroutput on
declare
temp_no pls_integer :=1;
begin
for i in 1..10 loop
dbms_output.put_line('Temp No is ' ||temp_no);
dbms_lock.sleep(1);
temp_no := temp_no+1;
end loop;
end;
/
39
ダミー表
変数の値を格納する為の一時表を作成しておき、INSERT文を組み込み
値を挿入します。Commit を発行した時点で参照が可能になるので、実
処理に影響が出る恐れがありますので注意が必要です。
…
for i in 1..10 loop
insert into temp values(temp_no);
commit;
dbms_lock.sleep(1);
temp_no := temp_no+1;
end loop;
…
PIPE
DBMS_PIPEパッケージを使用しPIPE内にメッセージとして、変数の値を
送信します。他セッションで値の確認を行います。(参照：Page11,12)
要求を出した時点での、参照が可能です。
…
for i in 1..10 loop
dbms_pipe.pack_message('Temp No is '||temp_no);
pstat := dbms_pipe.send_message('debug',10);
dbms_lock.sleep(1);
temp_no := temp_no+1;
end loop;
…
外部ファイル
UTL_FILEパッケージを使用します。（参照：Page7,8）
実行ログとしても利用が可能です。
UTL_FILE.FFLUSHを行えば逐次の参照が可能です。以下の例では、
毎行の情報を外部ファイルから取得することができます。（例えばUNIX
コマンド“tail”など）
…
fhandle := utl_file.fopen('/tmp','debug.log','w');
for i in 1..10 loop
utl_file.put_line(fhandle,'Temp No is ' ||temp_no);
utl_file.fflush(fhandle);
dbms_lock.sleep(1);
temp_no := temp_no+1;
end loop;
…
40
PL/SQLのデバッグ
PL/SQLのデバッグ
- Tool
z
Oracle Procedure Builder
–
Oracle Developer/2000の一部
の一部
Oracle Procedure Builderを使用すると、ブレークポイントの設定、変数の値のリ
スト、その他のデバッグ作業が行えます。
41
PL/SQLのデバッグ
PL/SQLのデバッグ
- コール・スタック情報
z
コール･スタックの取得
DBMS_UTILITY.FORMAT_CALL_STACK
Procedure
proc1
呼び出し
Procedure
proc2
呼び出し
Procedure
proc3
実行
DBMS_UTILITYのファンクションを使用して、現在のコール・スタック及びエ
ラー･スタックを取得できます。
例えば、
dbms_output.put_line(dbms_utility.format_call_stack);
を、エラーの発生する直前に、挿入しておけば、エラー発生時のコール・スタック
情報が取得できます。
以下の例では、上の図のような関係のプロシージャのうち、プロシージャproc1の
中でコールスタックを取得した結果です。
呼び出したモジュールと、その行数が分かります。
----- PL/SQL Call Stack ----object
line
handle
number
object
name
809612c8
4
procedure YHAYASHI.PROC1
80960268
4
procedure YHAYASHI.PROC2
8095c77c
8095c77c
4
procedure YHAYASHI.PROC3
808b6318
2
anonymous block
42
PL/SQLのデバッグ
PL/SQLのデバッグ
- エラー・スタック情報
z
エラー･スタックの取得
DBMS_UTILITY.FORMAT_ERROR_STACK
実行
プロシージャ：A
プロシージャ：A
例外発生
ttt_insert
実行
プロシージャ：B
プロシージャ：B
INSER
T
表：TTT
表：TTT
トリガー
（失敗）
また、
dbms_output.put_line(dbms_utility.format_error_stack);
を例外処理の中で使用すれば発生した一連のエラー･スタックが入手できます。
次のページの例では、トリガー内でのエラー情報がプロシージャ(B)内の例外処
理で取得できています。プロシージャ(A)の処理は例外処理を行わないのでエ
ラー情報は取得していません。
43
CREATE TABLE ttt (f1 number);
CREATE OR REPLACE TRIGGER ttt_insert
BEFORE INSERT ON ttt
BEGIN
例外を発生させる
RAISE ZERO_DIVIDE;
END ttt_insert;
/
CREATE OR REPLACE PROCEDURE B AS
BEGIN
INSERT INTO ttt VALUES (7);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('PROC B:
'||dbms_utility.format_error_stack ||'##');
END B;
/
CREATE OR REPLACE PROCEDURE A AS
BEGIN
B;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('PROC A:
'||dbms_utility.format_error_stack ||'##');
END A;
/
set serveroutput on
exec a;
---------------- 以下、実行結果 ---------------------PROC B: ORA-01476: 除数がゼロです。
ORA-06512: "YHAYASHI.TTT_INSERT", 行:
2
ORA-04088: トリガー:
トリガー: YHAYASHI.TTT_INSERTの実行中にエラーが発生しました。
YHAYASHI.TTT_INSERTの実行中にエラーが発生しました。
##
44
PL/SQLのチューニング
PL/SQLのチューニング
z
パフォーマンス分析
z
アクセス
z
アルゴリズム
45
PL/SQLのチューニング
PL/SQLのチューニング
- パフォーマンス分析
z
コード内で、パフォーマンスに悪影響を与えて
いる個所を特定します。
DBMS_UTILITY.GET_TIME
： 時間(1/100
時間(1/100秒
(1/100秒)を返します。
以下、ある処理(ここでは3秒間SLEEPさせています)の実行時間を表示するサン
プルです。
DECLARE
time_before BINARY_INTEGER;
time_after BINARY_INTEGER;
BEGIN
time_before := DBMS_UTILITY.GET_TIME;
--- 任意の処理が入ります。 calc_totals;
--- ここでは、3秒間の
を行わせています。
ここでは、 秒間のSLEEPを行わせています。
秒間の
DBMS_LOCK.SLEEP(3);
time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE(time_after - time_before);
END;
/
＜＜実行結果＞＞
301
PL/SQLプロシージャが正常に完了しました。
プロシージャが正常に完了しました。
46
PL/SQLのチューニング
PL/SQLのチューニング
- アクセス
z
重要なコードはSGAに固定
に固定
重要なコードは
–
DBMS_SHARED_POOL.KEEP ： 固定する
–
DBMS_SHARED_POOL.UNKEEP ： 解放する
z
自動再コンパイルを避ける
z
クライアント側SQLを避ける
を避ける
クライアント側
重要なコードはSGAに固定する
重要なコードは
に固定する
ストアド・モジュールを実行する際には、コンパイル済みのコードがSGA
にロードされている必要が有ります。すでにSGAにロードされていればそ
の分、実行時間は速くなります。もっとも使用するパッケージは常にメモリ
上に有るようにすると効果的です。
KEEP実行時にはロードされず、パッケージ実行時に実際はロードされま
す。DB起動直後のSGAが断片化していないときにパッケージの固定を
行うことが推奨されています。
他のリソースの使用量も考えて固定するパッケージについて検討する必
要が有ります。
例えば、以下のようなパッケージの固定が考えられます。
STANDARD、
、DBMS_STANDARD、
、DBMS_UTILITY、
、
DBMS_DESCRIBE、
、DBMS_OUTPUT、
、DBMS_LOCK、
、
DBMS_PIPE
自動再コンパイルを避ける。
参照：Page 33
クライアント側SQLを避ける
を避ける
クライアント側
同じSQLコードが複数のプログラムで共有しやすくなり、解析済みの共有
SQLコードの利用率が高まります。
プロシージャや関数にまとめることで、ネットワークに1回アクセスするだ
けで一連の操作が実行できます。
47
PL/SQLのチューニング
PL/SQLのチューニング
- アルゴリズム(1)
アルゴリズム(1)
z
ローカル変数の使用
z
PLS_INTEGER
z
NOT NULL制約を避ける
制約を避ける
ローカル変数の使用
パラメータ値をローカルコピーを作成しコピーする。
これにより、以下の左の例では繰り返しUPPER関数を呼び出しているの
に対して、右の例では1回の呼び出しですみます。
PROCEDURE calc_sales
(company_id IN NUMBER, action_in IN VARCHAR2)
IS
PROCEDURE calc_sales
(company_id IN NUMBER, action_in IN VARCHAR2)
IS
BEGIN
IF UPPER (action_in) = ‘ANNUAL’ THEN ...
action_int VARCHAR2(10) := UPPER(action_in);
BEGIN
ELSIF UPPER (action_in) = ‘QUARTERLY’ THEN ...
IF action_int = ‘ANNUAL’ THEN ...
ELSIF UPPER (action_in) = ...
ELSIF action_int = ‘QUARTERLY’ THEN ...
END IF;
ELSIF action_int = ...
END;
END IF;
END;
48
PL/SQLのチューニング
PL/SQLのチューニング
- アルゴリズム(2)
アルゴリズム(2)
z
ローカル変数の使用
z
PLS_INTEGERデータ型
データ型
z
NOT NULL制約を避ける
制約を避ける
PLS_INTEGERデータ型
データ型
符号付き整数（-2147483647 ～ 2147483647）の操作には
PLS_INTEGERデータ型の使用が一番効率が良い。（記憶領域、処理
速度の点で）
NOT NULL制約を避ける
制約を避ける
PL/SQL内の変数にNOT NULL制約をつけると、変数の代入毎に一時
変数を使い値の検証を行ってしまいます。避ける為にはプログラミングで
制約を実現します。
PROCEDURE foo IS
m number NOT NULL;
BEGIN
m := a+b;
m := m * 1.2;
m := m * m;
...
END;
PROCEDURE foo IS
m number;
BEGIN
m := a+b;
m := m * 1.2;
m := m * m;
if (m is null) then
----- エラー処理 ----end if;
...
END;
49
参考
- technet.oracle.com
technet.oracle.com
http://technet
http://technet.oracle.com
technet.oracle.com
技術情報
開発ツール
の紹介
Sample
Code and
Tips
Free
Utiliities
•技術情報
技術情報
• 開発ツールの紹介
商用開発ツール、Emacs(Free)
• Sample Code and Tips
依存関係トレース
• Free Utiliities
Random Generator
Bitwise Operation
50
この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更さ
れることがあります。この文書に関連して不都合が生じた場合も、米国オラクル
社及び日本オラクル株式会社は一切保証せず、特に責任は負いかねますので
ご容赦下さい。また許可なく、改編、引用することを禁じます。
51