- 追加された行はこの色です。
- 削除された行はこの色です。
* [[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]]~
&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 より抜粋 ）
&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;
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
