PL/SQL

コメントの記述

--　　1行のコメントはハイフン(-)２つで記述 　/* 　　　　複数行のコメント　スラッシュ、アスタリスクで記述 　*/

PL/SQLブロック

PL/SQLは3つのブロックで構成されている DECLARE 　　　--宣言部 BEGIN 　　　--実行部 EXCEPTION 　　　--例外処理 END ・宣言部(DECLARE) 　　　変数、定数の宣言と初期化 　　　変数、定数、カーソル、ユーザー定義例外が含まれ、これらがブロック内で参照される ・実行部(BEGIN) 　　　宣言部で宣言した変数、定数、カーソル等を使用し、実際のデータベースへの操作を記述（SQL文） ・例外処理部(EXCEPTION) 　　　実行部でエラーが発生した際に対する処理を記述 ・ブロック終了(END)

変数の宣言

変数名 データ型; 変数名 データ型 := 初期値; 変数名 データ型 NOT NULL := 初期値; 変数名 データ型 DEFAULT 初期値; 変数名 データ型 := 式; 変数名 データ型 := 変数; 変数は宣言部で定義 複数の変数を1度に指定できない 初期値を省略した場合はNULL NOT NULLを指定した場合は初期値が必須 英数字、かな漢字、$、#、_の30文字以内で構成 アルファベットの大文字、小文字の区別なし 先頭は英字、かな漢字 ORACLE予約語は不可

定数の宣言

変数名 CONSTANT データ型 := 式または値; 定数は宣言部で定義 複数の変数を1度に指定できない 初期値を省略できない NOT NULLは指定できない 計算式などと一緒に利用可能

%TYPE属性

変数名 表名.列名%TYPE; %TYPE はデータベースに格納された列のデータ型を与えることができる(データベースの列定義) 変数を宣言する時に直接データ型を指定するのではなく%TYPEで宣言 データベース列の定義に対して変更が加えられても考慮する必要はなく、自動的に変数がデータ型に対応される NOT NULL制約やDEFAULT制約は継承されない為、明示的に定義する必要がある

%ROWTYPE属性

変数名 表名%ROWTYPE; %ROWTYPE は表から選択された行全体、またはカーソルで取り出された行全体を与えることができる TYPE ... IS RECORD を使って独自のレコード型を作成する事も可能 列数やデータ型を知らなくても宣言が可能 テーブルとカーソルの %rowtype の一括代入はタイプが違う為、できない 列名が単純名でなくなる場合(金額＋消費税等の式)、別名を指定する必要がある ＜例＞ DECLARE 　　/* %ROWTYPEを宣言 */ 　　rowtype_tbl table1%ROWTYPE; BEGIN 　　/* SELECT INTO句でtable1表を代入 */ 　　SELECT * INTO rowtype_tbl FROM table1 WHERE key = 1; 　　/* DBMS_OUTPUT デバック */ 　　DBMS_OUTPUT.PUT_LINE(rowtype_tbl.field1); 　　DBMS_OUTPUT.PUT_LINE(rowtype_tbl.field2); END;

変数への代入

変数名 := 式または値; 右辺から左辺に代入 演算子は = ではなく := 右辺には値、変数、定数、SQL関数、式を置く データ型が同一か互換型の必要がある

SELECT INTO句

SELECT 列1, 列2, 列3, ...INTO 変数1, 変数2, 変数3, ... FROM 表名 ; SELECT文で取り出した値をINTO句に記述した変数に格納する 問合わせた列と変数は順番に関連付けられる 問合わせた列と変数の個数は一致していなければならない PL/SQLブロック内でSELECT文を使用する場合はINTO句が必須 複数件のデータを取得したい場合にはカーソル処理を利用して実現可能 SELECT INTO句によって戻されるレコードは1レコードでなければならない データが1件もない場合はNO_DATA_FOUNDエラーが発生 データが複数件の場合はTOO_MANY_ROWSエラーが発生

LOOP文

LOOP 　　/* 条件式がTRUEならループ脱出 */ 　　EXIT [WHEN 条件式]; 　　〜 END LOOP; LOOPからEND LOOPまでを繰り返す EXITで終了　※EXIT文がないと無限ループになる WHENオプションで条件式がTRUEの場合　処理終了

FOR文

開始値から終了値まで１ずつ増分(減少)させながら反復処理をする FOR カウンタ IN [REVERSE] 開始値 ... 終了値 LOOP 　　〜 　　/* ループ脱出 */ 　　EXIT WHEN 終了条件 END LOOP; ＜例＞　Cntが1〜10になるまで繰り返す FOR Cnt IN 1..10 LOOP 　　〜 END LOOP;

CASE文 ORACLE9i以降

条件により処理を分岐させる CASE 値 　　WHEN 条件1 THEN 　　　　〜　処理1; 　　WHEN 条件2 THEN 　　　　〜　処理2; 　　ELSE 　　　　〜　処理3; END CASE; ＜例1＞　Aが1の場合処理1　2の場合処理2　それ以外は処理3 CASE A 　　WHEN '1' THEN 　　　　〜　処理1; 　　WHEN '2' THEN 　　　　〜　処理2; 　　ELSE 　　　　〜　処理3; END CASE; ＜例2＞　Ａが1の場合処理1　2、3、4の場合処理2　それ以外は処理3 CASE 　　WHEN A = '1' THEN 　　　　〜　処理1; 　　WHEN A IN ('2','3','4') THEN 　　　　〜　処理2; 　　ELSE 　　　　〜　処理3; END CASE;

UPDATEしたレコード件数を取得

明示的カーソル カーソル名%ROWCOUNT 暗黙カーソル SQL%ROWCOUNT

動的SQL

DBMS_SQLパッケージ(SELECT文) /* SQL文作成 */ vc2_SQL := ''; vc2_SQL := vc2_SQL || 'SELECT field1 FROM table1 WHERE key = ''XXX'''; /* 文字列"XXX"をnum_Keyで置換 */ vc2_SQL := REPLACE(vc2_SQL,'XXX',num_Key); /* カーソルオープン */ num_Cur := DBMS_SQL.OPEN_CURSOR; /* SQL文セット [接続先のデータベースの動作：DBMS_SQL.NATIVE] */ DBMS_SQL.PARSE(num_Cur,vc2_SQL,DBMS_SQL.NATIVE); /* カラム宣言 [NUMBER以外は桁数指定 例では5] */ DBMS_SQL.DEFINE_COLUMN(num_Cur,1,vc2_ANS,5); /* カーソル実行 */ num_Exe := DBMS_SQL.EXECUTE(num_Cur); /* １カラム目のデータ取出 */ DBMS_SQL.COLUMN_VALUE(num_Cur,1,vc2_ANS); /* カーソルクローズ */ DBMS_SQL.CLOSE_CURSOR(num_Cur); DBMS_SQLパッケージ(UPDATE文) /* SQL文作成 */ vc2_SQL := ''; vc2_SQL := vc2_SQL || 'UPDATE table1 SET field1 = ''1'' WHERE key = ''XXX'''; /* 文字列"XXX"をnum_Keyで置換 */ vc2_SQL := REPLACE(vc2_SQL,'XXX',num_Key); /* カーソルオープン */ num_Cur := DBMS_SQL.OPEN_CURSOR; /* SQL文セット [DBMS_SQL.V7はお作法] */ DBMS_SQL.PARSE(num_Cur,vc2_SQL,DBMS_SQL.V7); /* カーソル実行 */ num_Exe := DBMS_SQL.EXECUTE(num_Cur); /* カーソルクローズ */ DBMS_SQL.CLOSE_CURSOR(num_Cur);

例外処理呼び出し（ユーザ例外を定義し利用）

DECLARE 　　ERR_END EXCEPTION; BEGIN 　　〜 　　IF 〜 THEN 　　　　/* 例外処理ERR_ENDへ */ 　　　　RAISE ERR_END; 　　END IF; 　　〜 EXCEPTION 　　WHEN ERR_END THEN 　　　　〜 　　WHEN OTHERS THEN 　　　　〜 END;

GOTO文(無条件にラベルへジャンプ(跳ぶ))

DECLARE BEGIN 　　〜 　　IF 〜 THEN 　　　　/* ラベルERR_ENDへ */ 　　　　GOTO ERR_END; 　　END IF; 　　〜 <<ERR_END>> 　　　　〜 EXCEPTION 　　　　〜 END;

PL/SQL内でのTRUNCATE実行方法

DBMS_SQLパッケージを使用する /* 宣言部で宣言 */ num_Cur NUMBER; num_Exe NUMBER; /* ここから実行部 */ num_Cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE( num_Cur, 'TRUNCATE TABLE table1', DBMS_SQL.NATIVE ); num_Exe := DBMS_SQL.EXECUTE( num_Cur ); DBMS_SQL.CLOSE_CURSOR( num_Cur );

特定の時間だけ処理を中断させる方法(SLEEP)

DBMS_LOCK.SLEEP(sec) 　SYSユーザでexecute on dbms_lock 権限を実行ユーザに付与しておく /* ここで30秒処理を中断させることができる */ DBMS_LOCK.SLEEP(30）

UTL_FILEパッケージの設定　　-UTL_FILE_DIR-

ディレクトリ作成 OSのDBAユーザ（通常はoracle）でディレクトリを作成 ファイルの読み書きができる権限を設定 初期化パラメータUTL_FILE_DIRの設定 Oracle 8i init[Oracle_SID].oraを編集 UTL_FILE_DIR='d/dir/bat','d/dir/send' Oracle 9i サーバパラメータファイル（SPFILE)を ALTER SYSTEM コマンドで編集 ALTER SYSTEM SET 　　UTL_FILE_DIR='d/dir/bat','d/dir/send' SCOPE = SPLILE; データベース再起動 設定確認 SELECT NAME,VALUE FROM V$PARAMETER2 WHERE NAME='utl_file_dir';

UTL_FILEパッケージの設定　　-DIRECTORY- ORACLE9i以降

DIRECTORYを使用するとユーザ単位、読み書きの制限が可能　また再起動も不要 ディレクトリ作成 OSのDBAユーザ（通常はoracle）でディレクトリを作成 ファイルの読み書きができる権限を設定 ディレクトリオブジェクトの作成 CREATE DIRECTORY dir_bat AS 'd/dir/bat'; CREATE DIRECTORY dir_send AS 'd/dir/send'; ※ CREATE DIRECTORY 権限が必要 ディレクトリへのアクセス権の設定 GRANT READ ON DIRECTORY dir_bat TO scott; GRANT WRITE ON DIRECTORY dir_send TO scott; 設定確認 SELECT * FROM ALL_DIRECTORIES;

UTL_FILEパッケージを使用してファイル書込み

ファイル書込み処理例 DECLARE 　typHandle 　　UTL_FILE.FILE_TYPE; 　vc2Dirname　　VARCHAR2(250); 　vc2Filename 　VARCHAR2(250); 　vc2Output 　　VARCHAR2(100);　　--最大32767バイト BEGIN 　/* ディレクトリの絶対パス名(ディレクトリオブジェクト)、ファイル名をセット */ 　vc2Dirname 　:= 'C:\FILE' ; 　vc2Filename 　:= 'test.txt'; 　/* ファイル作成 */ 　typHandle := UTL_FILE.FOPEN(vc2Dirname,vc2Filename,'w',100); 　vc2Output := 'ここの文字列をファイルに出力します'; 　/* 1行書込み */ 　UTL_FILE.PUT_LINE(typHandle,vc2Output); 　/* ファイル閉じる */ 　UTL_FILE.FCLSE(typHandle); END;

UTL_FILEパッケージを使用してファイル読込み

ファイル読込み処理例 DECLARE 　typHandle 　　UTL_FILE.FILE_TYPE; 　vc2Dirname　　VARCHAR2(250); 　vc2Filename 　VARCHAR2(250); 　vc2Input　　　VARCHAR2(100);　　--最大32767バイト BEGIN 　/* ディレクトリの絶対パス名(ディレクトリオブジェクト)、ファイル名をセット */ 　vc2Dirname 　:= 'C:\FILE' ; 　vc2Filename 　:= 'test.txt'; 　/* ファイル作成 */ 　typHandle := UTL_FILE.FOPEN(vc2Dirname,vc2Filename,'r',100); 　BEGIN 　　LOOP 　　　/* 1行読込み */ 　　　UTL_FILE.GET_LINE(typHandle,vc2Input,100); 　　　/* 出力 */ 　　　DBMS_OUTPUT.PUT_LINE(vc2Input); 　　END LOOP; 　EXCEPTION 　WHEN NO_DATA_FOUND THEN 　　DBMS_OUTPUT.PUT_LINE('読込処理終了'); 　END; 　UTL_FILE.FCLSE(typHandle); END;