- 追加された行はこの色です。
- 削除された行はこの色です。
- PL/SQL へ行く。
* PL/SQL [#q6140beb]
* [[Oracleメモ]] - PL/SQL(Oracle) [#q8adc448]
#setlinebreak(on);
** PL/SQL [#q6140beb]
-[[PL/SQLとは>#summary]]~
-[[SQL*PLUSとは>#sqlplus_summary]]~
--[[SQL*PLUSのコマンド>#sqlplus_cmd]]~
--[[SQL*PLUSのシステム変数>#sqlplus_vars]]~
-[[PL/SQLの構文>#construction]]~
-[[PL/SQLの使用例>#exercise]]~
--[[変数の宣言>#exercise01]]~
--[[変数への値代入>#exercise02]]~
---[[:=を使用しての代入>#exercise02_01]]~
---[[SELECT文からの代入>#exercise02_02]]~
--[[条件分岐>#exercise03]]~
--[[コマンドライン引数の受取>#exercise04]]~
--[[ラベル宣言とGOTO文>#exercise05]]~
--[[繰り返し処理>#exercise06]]~
---[[LOOP>#exercise06_01]]~
---[[WHILE LOOP>#exercise06_02]]~
---[[FOR LOOP>#exercise06_03]]~
--[[カーソルの利用>#exercise07]]~
---[[カーソルからのデータ読込>#exercise07_01]]~
---[[カーソルFOR LOOP>#exercise07_02]]~
--[[戻り値の設定>#exercise12]]~
--[[例外の利用>#exercise08]]~
---[[例外のCATCH>#exercise08_01]]~
---[[ユーザ定義例外の利用>#exercise08_02]]~
--[[Functionの利用>#exercise09]]~
--[[Procedureの利用>#exercise10]]~
--[[パッケージの利用>#exercise11]]~
---[[標準出力への表示>#exercise11_01]]~
---[[ファイルの読み書き>#exercise11_02]]~
---[[乱数の利用>#exercise11_03]]~
-[[その他>#etc]]
--[[SQL*PLUS起動時に良く使う設定を自動的に読み込む>#etc01]]
&aname(summary);
-''PL/SQLとは''
Oracle社が開発したプログラミング言語で、データベース問い合わせ言語の標準であるSQLに
手続き型プログラミング言語を組み合わせたような仕様を持つ。
同社のデータベース管理システムである「Oracle」と組み合わせて使用し、プログラミング言語による処理の記述と、
SQLコマンドによるデータベースの検索やデータの更新などを組み合わせ、データベースアプリケーションを開発することができる。
高速な処理が必要なプログラムは、ストアドプログラムとしてシステム内に保存することができ、
高速に実行することができる。また、ストアドプログラムを外部から呼び出しやすいようパッケージ化することにより、
オブジェクト指向的な開発が可能となっている。
ちなみに、PostgreSQLのPL/pgSQLは、このPL/SQLを参考に実装されたものであるが、データベース管理システム (DBMS) の
実装に依存する部分（トランザクション管理の概念が異なる、例外を発生させることはできるが、キャッチできないなど）や、
構文中のシングルクォーテーションをエスケープしなければならないなど完全な互換性はない。
RIGHT:（ http://ja.wikipedia.org/wiki/PL/SQL 、 http://e-words.jp/w/PL2FSQL.html より抜粋 ）
//&br;
&aname(sqlplus_summary);
-''SQL*Plusとは''
SQL*Plusは、データベース（Oracle）へSQLの発行、問い合わせ用を行う為の対話ツール。（もちろんPL/SQLも実行できる）
SQL*Plus でしか利用できない便利なコマンドが用意されている。
※ 旧ライセンス形式では SQL*Plus の使用の用途が制限されている場合があるので要注意。
&aname(sqlplus_cmd);
--''SQL*PLUSのコマンド'' ※参照 : http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19277-01/ch12.html
|SQLPlus コマンド|コマンド 説明|h
|@|ファイルの内容を実行（詳細は START 参照）|
|@@|~|
|@?|~|
|n|カレント行を n にする|
|/|バッファ内のSQL、PL/SQLブロックを実行（SQL バッファ非表示）|
|ACCEPT|ACCEPT vData PROMPT ' vDataを入力する。'|
|APPEND|行末にテキストを追加|
|ARCHIVE LOG|アーカイブログ運用の設定と状態表示|
|ATTRIBUTE|オブジェクト型列の表示書式を設定|
|BREAK||
|BTITLE|下部に指定のタイトルの書式設定|
|CHANGE|SQL バッファの文字を置換|
|CLEAR|画面、バッファ、書式、タイマーの設定などの初期化|
|COLUMN|カラムの書式指定（書式モデル）|
|COMPUTE||
|CONNECT|データベースへの接続|
|COPY|下位互換用|
|DEFINE|ユーザー定義定数の設定と表示|
|DEL|SQL バッファの一部を削除|
|DESCRIBE|スキーマオブジェクトの仕様を表示|
|DISCONNECT|データベースから切断|
|ED[IT]|ホストのエディタを使用した編集|
|EXECUTE|PL/SQL ブロックの実行|
|EXIT|SQL*Plus の終了|
|GET|ファイルの内容をSQLバッファに読み込む|
|HELP|オンライン・コマンドヘルプ|
|HOST|シェルでコマンドの実行|
|INPUT|文字列の追加(単独の場合には複数行追加可能)|
|LIST|SQL バッファ内を表示|
|PASSWORD|パスワードの変更|
|PAUSE|処理の一時停止|
|PRINT|バインド変数の内容を表示する|
|PROMPT|メッセージの表示|
|RECOVER|データベースのリカバリー|
|REMARK|コメント行|
|REPFOOTER||
|REPHEADER||
|QUIT|EXIT コマンドのエイリアス|
|RUN|バッファ内SQL、PL/SQLブロックを表示し実行|
|SAVE|SQLバッファの内容をファイルに保存|
|[[SET>#sqlplus_vars]]|システム変数を設定する|
|SHOW|メモリ、システム変数、初期化パラメータなどの表示|
|SHUTDOWN|オラクルの停止|
|SPOOL|出力結果をファイルへ出力の開始と終了|
|START|ファイルの内容を実行|
|STARTUP|オラクルの起動|
|STORE||
|TIMING|タイマーの設定、表示と解除|
|TTITLE|上部に指定のタイトルの書式設定|
|UNDEFINE|ユーザー定義定数の削除|
|VARIABLE|バインド変数 の定義|
|WHENEVER OSERROR|OS レベルのエラー発生時の例外処理|
|WHENEVER SQLERROR|SQL レベルのエラー発生時の例外処理|
※たくさんあるが、よく使うのは ED , SET ぐらい~
&aname(sqlplus_vars);
--''SQL*Plus のシステム変数''~
※システム変数は SQL*Plus コマンドの ''SET システム変数 値'' という形式で設定する。~
（ [[SQL*PLUS起動時に自動的に環境設定する方法]]もある。 ）~
|コマンド|説明|h
|SET APPINFO|スクリプトの進行状況などを設定|
|SET ARRAYSIZE|DBMS_OUTPUT のバッファ、データベースから一度にフェッチする配列の大きさ|
|SET AUTOCOMMIT|オートコミットの有効／無効|
|SET AUTOPRINT|バインド変数 の自動表示|
|SET AUTORECOVERY|RECOVERY コマンドのアーカイブログファイル名を自動補完する／しない|
|SET AUTOTRACE|オートトレースの有効／無効、または、トレースモードの設定|
|SET BLOCKTERMINATOR|PL/SQL ブロックの終了文字の設定|
|SET CMDSEP|SQL*Plus コマンドの区切り文字|
|SET COLSEP|カラムとカラムの間の区切り文字|
|SET COMPATIBILITY|Oracle 8 以前向けの下位互換動作用|
|SET CONCAT|後続に文字連結するときの置換変数の終了文字の設定|
|SET COPYCOMMIT|COPY コマンドのコミットポイント設定|
|SET COPYTYPECHECK|COPY コマンドの型チェックの有効／無効|
|SET DEFINE|置換変数を有効／無効または接頭辞を設定|
|SET DESCRIBE|DESCRIBE コマンド の表示方法の調整|
|SET ECHO|スクリプトのSQL*Plusコマンドを表示をする／しない|
|SET EDITFILE|EDIT コマンド で使用する一時ファイル名の設定|
|~|エディタプログラムの指定は _EDITOR 事前定義変数|
|SET EMBEDDED|異なるレポート(SQL)でも同一ページ含める／含めない|
|SET ESCAPE|エスケープ機能を有効／無効、または、別のエスケープ文字を設定|
|SET FEEDBACK|問い合わせの結果の件数を表示をする／しない、または、表示する最小件数の設定|
|SET FLAGGER|SQL が SQL92 に準拠しているか調べる／調べないを設定|
|SET FLUSH|画面出力にバッファを使用する／しないの設定|
|SET HEADING|列ヘッダを表示する／しない|
|SET HEADSEP|列ヘッダの区切り文字の設定|
|SET INSTANCE|デフォルトのインスタンスの設定|
|SET LINESIZE|１行に表示する（文字数）バイト数の設定|
|SET LOBOFFSET|CLOB、NCLOBの取り出し開始位置の設定|
|SET LOGSOURCE|RECOVERY コマンドで使用するアーカイブログファイル位置の設定|
|SET LONG|LONG、CLOB、NCLOB の表示幅|
|SET LONGCHUNKSIZE|LONG、CLOB、NCLOBのチャンクサイズ|
|SET MARKUP|出力をHTML 形式にエンコードをする／しないの設定(SQL*Plusの場合）|
|SET NEWPAGE|ページの開始位置から列ヘッダーまでの余白行数の設定|
|SET NULL|NULL データの代替文字列設定|
|SET NUMFORMAT|数値のデフォルト書式の設定（書式モデル）|
|SET NUMWIDTH|数値のデフォルト表示幅|
|SET PAGESIZE|1ページの行数|
|SET PAUSE|１ページ毎に一時停止をする／しない|
|SET RECSEP|レコードセパレータを常に出力する／しない、または、折り返しが発生時のみ出力する|
|SET RECSEPCHAR|RECSEP が発生したときに表示される文字の設定|
|SET SERVEROUTPUT|標準出力に表示する／しない、または、バッファの上限サイズ|
|SET SHIFTINOUT|シフト文字を表示する／しない（特殊なコンソールのみ）|
|SET SHOWMODE|システム変数の変更前値の通知を表示する／しない|
|SET SQLBLANKLINES|SQL コマンド、PL/SQL ブロックに空白行を許可する／しない|
|SET SQLCASE|すべての入力文字を大文字、または、小文字に変換する／しない|
|SET SQLCONTINUE|複数行の SQL*Plusコマンド入力時のプロンプトの設定|
|SET SQLNUMBER|複数行の SQL コマンド、PL/SQL ブロック入力時に表示される行番号を表示する／しない|
|SET SQLPLUSCOMPATIBILITY|動作保証できる SQL*Plus のバージョンの設定|
|SET SQLPREFIX|SQL*Plus コマンドの接頭辞の設定|
|SET SQLPROMPT|SQL*Plus のコマンドプロンプトに表示される文字列の設定|
|SET SQLTERMINATOR|SQL コマンド、PL/SQL ブロックの終了文字の設定|
|SET SUFFIX|スクリプトファイルのデフォルト拡張子の設定|
|SET TAB|タブ文字の使用をする／しない|
|SET TERMOUT|@、@@、START のスクリプトによるアウトプットを表示する／しない|
|SET TIME|SQL プロンプトに時刻表示する／しない|
|SET TIMING|タイミング統計（タイマー）を表示する／しない|
|SET TRIMOUT|各行の出力の終わりから行末までの空白を出力する／しない（スプールには無関係）|
|SET TRIMSPOOL|スプールにおける TRIMOUT システム変数（画面出力には無関係）|
|SET UNDERLINE|列ヘッダの下の表示される -----… 文字のあり／なし／文字の設定|
|SET VERIFY|置換変数に設定する前後の状態を表示する／しない|
|SET WRAP|表示幅に収まらない部分を折り返す／切り捨てる|
&aname(construction);
-''PL/SQLの構文''
DECLARE
変数,カーソル,関数,例外等の宣言
BEGIN
メイン処理
END;
※但し、変数などを宣言する必要がなければ DECLARE部は不要。
&aname(exercise);
-''PL/SQLの使用例''
&aname(exercise01);
--''変数の宣言''
DECLARE
/* char型 */
char01 CHAR(10);
char02 CHAR(10) := '1234567890'; -- 初期値を設定する場合は 宣言に続けて := でセットする
/* varchar2型 */
varchar01 VARCHAR2(10);
/* number型 */
number01 NUMBER(10);
number02 NUMBER(10,2); -- 全桁数,小数点以下の桁値
/* BOOLEAN型 */
boolean01 BOOLEAN;
/* ユーザ定義例外 */
exception01 EXCEPTION;
/* ファイルハンドラ */
file01 UTL_FILE.FILE_TYPE;
/* レコードタイプ */
TYPE type01 IS RECORD (
ymd CHAR(6)
,shn_cd CHAR(10)
,uriage NUMBER(10)
);
/* レコード変数 */
rec01 type01;
/* カーソル宣言 */
CURSOR cur01 IS
SELECT
TABLE_NAME
,TABLESPACE_NAME
FROM
USER_TABLES;
/* カーソルデータ格納用レコード */
cur01_rec cur01%ROWTYPE;
BEGIN
・
・
&aname(exercise02);
--''変数への値代入''
&aname(exercise02_01);
---'':=を使用しての値代入''
DECLARE
varchar01 VARCHAR2(10);
varchar02 VARCHAR2(10);
BEGIN
varchar01 := '1234567890';
varchar02 := varchar01;
END;
&aname(exercise02_02);
---''SELECT文からの値代入''
DECLARE
date_str VARCHAR2(10);
BEGIN
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD')
INTO date_str
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(date_str);
END;
&aname(exercise03);
--''条件分岐''
BEGIN
char01 := '1';
IF char01 = '1' THEN
/* char01 ＝ 1 時の処理 */
ELSE
/* char01 ≠ 時の処理 */
END IF;
END;
&aname(exercise04);
--''コマンドライン引数の受取''
DECLARE
/* １つめの引数を取得し、変数 args01 へセット */
args01 VARCHAR2(255) := '&1';
・
・
&aname(exercise05);
--''ラベル宣言とGOTO文''
DECLARE
args01 VARCHAR2(255) := '&1';
BEGIN
/* 引数から得た値がNULLまたは空文字の時は処理しない */
IF args01 IS NULL OR args01 = '' THEN
GOTO end_proc;
END IF;
・
・
<<end_proc>>
NULL;
END;
&aname(exercise06);
--''繰り返し処理''
&aname(exercise06_01);
---''繰り返し処理(LOOP)''
DECLARE
LOOP_CNT NUMBER(10);
BEGIN
-- カウンタ初期化
LOOP_CNT := 0;
-- 繰り返し処理
LOOP
-- カウンタ値を加算
LOOP_CNT := LOOP_CNT + 1;
-- カウンタが10以上の場合は繰り返し処理終了
IF LOOP_CNT > 10 THEN
EXIT;
END IF;
/*
もしくは
EXIT WHEN LOOP_CNT > 10;
でも可。
*/
/* 繰り返して行う処理 */
・
・
END LOOP;
END;
&aname(exercise06_02);
---''繰り返し処理(WHILE LOOP)''
DECLARE
LOOP_CNT NUMBER(10);
BEGIN
-- カウンタ初期化
LOOP_CNT := 0;
-- 繰り返し処理
WHILE LOOP_CNT < 10 LOOP
-- カウンタ値を加算
LOOP_CNT := LOOP_CNT + 1;
/* 繰り返して行う処理 */
・
・
END LOOP;
END;
&aname(exercise06_03);
---''繰り返し処理(FOR LOOP)''
BEGIN
-- 繰り返し処理
FOR i IN 1..10 LOOP
/* 繰り返して行う処理 */
・
・
END LOOP;
END;
&aname(exercise07);
--''カーソルの利用''
&aname(exercise07_01);
---''カーソルからのデータ読込''
-- DBMS_OUTPUTパッケージからの出力をON
SET SERVEROUTPUT ON SIZE 1000000;
-- ここからPL/SQLブロック
DECLARE
/* カーソル宣言 */
CURSOR cur01 IS
SELECT
TABLE_NAME
,TABLESPACE_NAME
FROM
USER_TABLES;
/* カーソルデータ格納用レコード */
cur01_rec cur01%ROWTYPE;
BEGIN
-- カーソルのOPEN
OPEN cur01;
-- 1件目の取得
FETCH cur01 INTO cur01_rec;
-- 繰り返し処理
LOOP
-- カーソルにデータがない時は繰り返し処理を終了
IF cur01%NOTFOUND THEN
EXIT;
END IF;
-- テーブル名、表領域を表示
DBMS_OUTPUT.PUT_LINE(cur01_rec.TABLE_NAME || ',' || cur01_rec.TABLESPACE_NAME);
-- 2件目以降の取得
FETCH cur01 INTO cur01_rec;
END LOOP;
-- カーソルをCLOSE
CLOSE cur01;
END;
&aname(exercise07_02);
---''カーソルFOR LOOP''
-- DBMS_OUTPUTパッケージからの出力をON
SET SERVEROUTPUT ON SIZE 1000000;
-- ここからPL/SQLブロック
DECLARE
/* カーソル宣言 */
CURSOR cur01 IS
SELECT
TABLE_NAME
,TABLESPACE_NAME
FROM
USER_TABLES;
BEGIN
-- 繰り返し処理
FOR rec IN cur01 LOOP
-- テーブル名、表領域を表示
DBMS_OUTPUT.PUT_LINE(rec.TABLE_NAME || ',' || rec.TABLESPACE_NAME);
END LOOP;
END;
&aname(exercise12);
--''戻り値の設定''
/* シェルからSQL*PLUSを起動してPL/SQLファイルを実行する場合の戻り値の設定方法 */
--システムエラー発生時、1を戻り値にセットして終了
WHENEVER OSERROR EXIT 1;
--ORACLEエラー発生時、独自の戻り値をセットして終了
WHENEVER SQLERROR EXIT :return_cd;
--変数宣言
-- エラーコード(戻り値)
VARIABLE return_cd NUMBER;
DECLARE
・
・
BEGIN
・
・
EXCEPTION
WHEN XXXXXXXX THEN
:return_cd := 2;
・
・
WHEN OTHERS THEN
:return_cd := 3;
・
・
END;
/
EXIT :return_cd
/
&aname(exercise08);
--''例外の利用''
&aname(exercise08_01);
---''例外のCATCH''
DECLARE
tbl_name VARCHAR2(255);
BEGIN
SELECT TABLE_NAME
INTO tbl_name
FROM USER_TABLES
WHERE TABLE_NAME = 'DUMMY';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('データなし!');
END;
/* 例外時も処理を継続する場合は
BEGINブロックをネストさせて処理を記述する。
（極力 functionやprocedureを利用して見やすくする事。）*/
BEGIN
BEGIN
SELECT TABLE_NAME
INTO tbl_name
FROM USER_TABLES
WHERE TABLE_NAME = 'DUMMY';
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('データなし!');
END;
/* 後続処理 */
・
・
・
END;
&aname(exercise08_02);
---''ユーザ定義例外の利用''
DECLARE
/* ユーザ定義例外の宣言 */
exception01 EXCEPTION;
BEGIN
-- 例外を発生させる
RAISE exception01;
/* 後続処理 */
・
・
・
EXCEPTION
WHEN exception01 THEN
DBMS_OUTPUT.PUT_LINE('exception01が発生しました。');
END;
&aname(exercise09);
--''Functionの利用''
DECLARE
/* 加算処理 */
FUNCTION func01 ( arg1 IN NUMBER
,arg2 IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (arg1 + arg2);
END func01;
BEGIN
DBMS_OUTPUT.PUT_LINE('10 + 20 = ' || func01(10,20));
END;
&aname(exercise10);
--''Procedureの利用''
DECLARE
-- 計算結果格納用
result_num NUMBER(10);
/* 加算処理 */
PROCEDURE proc01 ( arg1 IN NUMBER
,arg2 IN NUMBER
,arg3 IN OUT NUMBER
) IS
BEGIN
arg3 = arg1 + arg2;
END proc01;
BEGIN
proc01(10,20,result_num);
DBMS_OUTPUT.PUT_LINE('10 + 20 = ' || result_num);
END;
&aname(exercise11);
--''パッケージの利用''
&aname(exercise11_01);
---''標準出力への表示''
-- 標準出力への表示をON ( SQL*PLUSコマンド )
SET SERVEROUTPUT ON SIZE 10000;
-- ここからPL/SQLブロック
BEGIN
DBMS_OUTPUT.PUT_LINE('TEST!!');
END;
&aname(exercise11_02);
---''ファイルの読み書き''
DECLARE
uf01 UTL_FILE.FILE_TYPE;
file_rec VARCHAR2(255);
BEGIN
/* ディレクトリ作成
(PL/SQLでDDLを実行する場合は、動的SQL文かDBMS_SQLパッケージを使用する) */
EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY TEST_DIR AS ''c:/tmp''';
-- 書込テスト
uf01 := UTL_FILE.FOPEN( 'TEST_DIR' , 'utl_file_test.txt', 'a', 256);
UTL_FILE.PUT_LINE ( uf01, '書き込みTEST!!', true);
UTL_FILE.FCLOSE(uf01);
-- 読込テスト
uf01 := UTL_FILE.FOPEN( 'TEST_DIR' , 'utl_file_test.txt', 'r', 256);
LOOP
file_rec := '';
BEGIN
UTL_FILE.GET_LINE ( uf01, file_rec, 256);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('READ ERROR!');
END;
IF file_rec IS NULL OR file_rec = '' THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(file_rec);
END LOOP;
UTL_FILE.FCLOSE(uf01);
END;
※CREATE DIRECTORYを行うには CREATE ANY DIRECTORY 権限が必要。
（ grant CREATE ANY DIRECTORY to ユーザ; ）
※DIRECTORY下のファイルに読み書きを行うには、該当DIRECTORYへの READ,WRITE権限が必要。
（GRANT READ,WRITE ON DIRECTORY ディレクトリ名 TO ユーザ; )
&aname(exercise11_03);
---''乱数の利用''
BEGIN
-- 1〜10の間の乱数を生成
DBMS_OUTPUT.PUT_LINE( DBMS_RANDOM.VALUE(1,10) )
END;
&aname(etc01);
---''SQL*PLUS起動時に良く使う設定を自動的に読み込む''
(1)SQL*PLUSへのショートカットを作成する
(2)ショートカットの作業フォルダを自分のMyDocument等に変更する
(3)(2)で設定した作業フォルダに、下記のファイルを login.sql という名前で作成する
SET LINESIZE 200
SET SERVEROUTPUT ON SIZE 1000000
SET SQLPROMPT "&_USER.@&_CONNECT_IDENTIFIER>"
Oracle10gでない場合は、事前定義変数 _USER が使用できないので自分で接続ユーザ名を取得する
SET LINESIZE 200
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
SET HEADING OFF
DEFINE UNAME = ''
COLUMN UNAME_FOR_PROMPT NEW_VALUE UNAME NOPRINT
SELECT USER UNAME_FOR_PROMPT FROM DUAL;
SET SQLPROMPT "&UNAME.@&_CONNECT_IDENTIFIER> "
SET FEEDBACK ON
SET HEADING ON
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
