はじめに
対象者
コメント
構造
コーディング規約
ハローワールド
データ形式
演算子
制御構造
データ処理
エラー処理
関数
API
デバック
sqlplusを使って、Helloworldから始まり、データ形式、制御構造、関数の書き方などをサラっと学習します。
今後、オラクル社のリファレンスで学習できるようにするのが目的です。
参考資料には、オラクル社へのマニュアル・リンクを用意しました。
入門書に書いてある例えなどはありません。
言語習得に必要な部分のみしか書きませんので、sqlplusでサンプルを実行しながら学習してください。
PL/SQLと他の言語を比較してポイントとなる部分は赤 マークしています。
対象者
Oracleをこれから始める方。
コメント
コメントの書き方は二つあります。
-- １行のコメントはハイフン(-)２つです。
/*
複数行のコメントはC言語などのように
スラッシュ、アスタリスクです。
*/
構造
下記の構造をPL/SQLブロックという
Begin End;のみ必須
入れ子で書くこと(=ネストブロック)も可能
サンプル
Declare
-- 宣言
Begin
-- 処理
Exception
-- エラー処理
End;
ネストブロック
Declare
Begin
Declare
-- 宣言
Begin
-- 処理
Exception
-- エラー処理
End;
Exception
End;
declare
tmp_data number := 0;
begin
/* nest block */
declare
tmp_data number;
begin
select 1/0 into tmp_data from dual;
exception
when ZERO_DIVIDE then
tmp_data :=SQLCODE;
insert into test_tbl values (tmp_data, 'testok');
commit;
end;
exception
when others then
null;
end;
/
コーディング規約
コーディング規約はない。
ハローワールド
サンプルテーブル
SQL> desc test_tbl
名前 NULL? 型
---- ----- ----------------------------
COL1 NUMBER
COL2 VARCHAR2(8)
hello.sql作成
hello.sqlで以下のPL/SQLプログラムを保存。
declare
test_var date;
begin
select sysdate into test_var from dual;
end;
/
解説
文はセミコロン(;)で終了
ブロックの最後にスラッシュ（/）が必要
大文字、小文字は関係ない
実行
SQL> @hello.sql
PL/SQLプロシージャが正常に完了しました。
データ形式
データ型(スカラー型・プリミティブ型)
NUMBER 符号付整数、固定小数点数、浮動小数点数
BINARY_INTEGER NUMBER型よりメモリ領域が少ない
PLS_INTEGER NUMBER型より早い
CHAR
VARCHAR2
LONG 互換性のためにあるLOB型を推奨
LONGROW 互換性のためにあるLOB型を推奨
NCHAR
NVARCHAR2
RAW 互換性のためにあるLOB型を推奨
UROWID ROWID
BOOLEAN TRUE | FALSE | NULL
DATE
BFILE
BLOB
CLOB
NCLOB
データ定義
変数名、データ型の順番で定義
文はセミコロン(;)で終了
初期値は:=で 指定
NOT NULL制約が可能
定数の定義はCONST
変数定義(=データ定義)
変数名 型
test_number number(10);
test_string varchar2(100);
jpn_tax number(5,2) := 1.05;
test_number2 number(3) := 10 not null;
test_date date := sysdate;
MAX_VALUE const number := 1.05;
属性定義
PL/SQLでは、テーブル定義からデータ型を参照することが出来る。
サンプルのテーブル
create table emp_tbl (
empid number,
emp_name varchar2(20)
)
/
属性定義の仕方
declare
test_no emp_tbl.empid%type;
begin
end;
/
解説
emp_tblテーブルのempidカラムのデータ型を%typeで 取得できるのでテーブル定義が変更してもPL/SQLの実装を修正する必要がなくなる。
索引付き表（スカラー型の配列）
type 索 引付き表型名 is table of スカラー型 index by binary_integer;
declare
type test_array is table of hoge_table.fuga_txt%type index by binary_integer;
test_variable test_array;
cursor c_hoge is select hoge_id,fuga_txt from hoge_table;
begin
for wk_cursor in c_hoge loop
test_variable(wk_cursor.hoge_id) := wk_cursor.fuga_txt;
end loop;
end;
/
show errors;
レコード
type レコード型名 is record (列名 データ型, 列名 データ型, ...);
declare
-- CAUTION!!
-- A user execute this program on sqlplus who must input command that 'set
serveroutput on'.
--
-- definition
type t_rec is record (
id_record test_record_tbl.id_record%type,
record_name test_record_tbl.record_name%type
);
-- declaretion
wk_val t_rec;
cursor c_sample is select id_record, record_name from test_record_tbl;
begin
open c_sample;
loop
fetch c_sample into wk_val;
dbms_output.put_line(wk_val.record_name);
exit when c_sample%notfound;
end loop;
dbms_output.put_line('end loop');
close c_sample;
end;
/
show errors;
レコードの索引付き表(二次元配列)
type レコード型名 is record (列名 データ型, 列名 データ型, ...);
type レコードの索引付き表型名 is table of レコード型名 index by binary_integer;
declare
-- the generic program language say multi-dimensional array.
-- existed samle function is 'varray' object.
-- 1. define record
type r_sample is record (
id_record test_record_tbl.id_record%type,
record_name test_record_tbl.record_name%type
);
-- 2. define arrray
type idx_ is table of r_sample index by binary_integer;
-- 3. declaration
dimen_ary idx_;
cursor c_tmp is select id_record, record_name from test_record_tbl;
begin
for wk_emp in c_tmp loop
dimen_ary(wk_emp.id_record) := wk_emp;
end loop;
end;
/
show errors;
演算子
= ==ではないので注意
!=
<> != と同意
>
<
<=
>=
IN
NOT IN
BETWEEN x AND y
NOT BETWEEN x AND y
LIKE
NOT LIKE
IS NULL
IS NOT NULL
PL/SQLは、NULL は何と比較してもNULLである。
制御構造
分岐
if文
if 条件1 then
-- 処理
elsif 条件2 then
-- 処理
end if;
declare
test_var number := 1;
begin
if test_var = 1 then
null; -- null は何もしない時に記述
elsif test_var = 2 then
null;
end if;
end;
/
反復
for文
for カウンタ in min...max [reverse] loop
end loop;
以下の例は、グローバルなtest_var変数とfor文中のtest_var変数２つを定義している。
for文中のカウンタであるtest_var変数は暗黙で定義しているので型を書いたりしていない。
サンプルのテーブル定義
create table test_tbl (col1 number);
declare
test_var number := 100;
begin
for test_var in 1..10 loop -- ローカルとしてtest_var変数を暗黙定義.
insert into test_tbl values (test_var);
end loop;
insert into test_tbl values (test_var);
commit;
end;
/
前判定反復
while文
while 条件 loop
end loop;
declare
test_var number := 100;
begin
while test_var <= 110 loop
insert into test_tbl values (test_var);
test_var := test_var + 1; -- インクリメント演算は出来ない
end loop;
commit;
end;
/
後判定反復
loop文
loop
end loop;
PL/SQLではC言語のように、while文を反復の最後に設定できないのでif文とloop文で行う。
declare
test_var number := 100;
begin
loop
-- 処理を書く.
-- 後判定
if test_var = 100 then
exit; -- loopを抜ける
end if;
end loop;
end;
/
if文を利用してexitするのは、exit when文を使えば１ 行に出来る。
declare
test_var number := 100;
begin
loop
exit when test_var = 100;
end loop;
end;
/
データ処理
select into文
select into文は、１レコードの取得しか出来ません。
複数のレコードの場合はカーソルを使います。
select カラム名 into 変数 from テーブル [where 検索条件など]
declare
test_var date;
begin
-- dual表はテストなどダミーで使う表.
select sysdate into test_var from dual;
end;
/
カーソル
select文の結果セット（データ集合）に対して、１レコードづつ処理していく時に利用する。
PL/SQLのカーソルは戻る事が出来ない。(Javaのjava.sql.ResultSetなどは戻る事が可能)
カーソル作成手順
カーソル型の定義
カーソル用の変数定義
オープン
フェッチ(fetch)（定義した変数にデータを格納する事）
データ存在チェック
処理
クローズ
SQL> desc test_tbl
名前 NULL? 型
---- ----- --------------
COL1 NUMBER
COL2 VARCHAR2(8)
declare
/* カーソル型の定義 */
cursor c_test is select col1, col2 from test_tbl;
/* カーソル用の変数定義 */
c_test_val c_test%rowtype;
begin
open c_test;
loop
fetch c_test into c_test_val;
exit when c_test%notfound;
end loop;
close c_test;
end;
/
解説
%rowtypeでcol1の型(NUMBER)とcol2の型 (VARCHAR2(8))を取得している。%notfoundで存在チェック
カーソルforループ
for レコード型変数名 in カーソル loop
end loop;
カーソルfor loop でレコード型変数名をforスコープ内に出来る。
declare
/* cursor definition */
cursor c_test is select col1, col2 from test_tbl;
begin
for c_test_val in c_test loop
exit when c_test%notfound;
end loop;
end;
/
エラー処理
例外処理と通知
exceptionブロックに例外処理を書く
バインド変数で外部に通知する
PL/SQLではexceptionからbeginには復旧できない
例外をハンドリングしてexceptionブロックが正常に終了するとエラーがなかったようになる。
このサンプルは、sqlplusで実行するとエラーが出ます。各自、情報収集して解決してください。
declare
cursor c_tmp is select col1, col2 from test_tbl;
tmp_val c_tmp%rowtype;
begin
open c_tmp;
loop
fetch c_tmp into tmp_val;
exit when c_tmp%notfound;
end loop;
close c_tmp
:status := '0'; /* bind variable */
exception
when others then
if c_tmp%isopen then
close c_tmp;
end if;
:status := '1';
end;
/
解説
when others thenは、C言語ではswitch文の defalt句、Javaではjava.lang.Exception
サンプル2
declare
tmp_date date;
begin
insert into test_tbl values (111, 'test');
select sysdate into tmp_date from dual where 1=2;
commit;
exception
when others then
commit;
end;
/
実行結果
SQL> @test9.sql
PL/SQLプロシージャが正常に完了しました。
解説
例外処理(exception)を書いているので正常終了になる
例外ハンドラ
例外一覧
例外名 ORA-XXX SQLCODE 条件
ACCESS_INTO_NULL 6530 -6530 初期化していないオブジェクト（アトミックNULL)の属性に代入した時
COLLECTION_IS_NULL 6531 -6531
CURSOR_ALREADY_OPEN 6511 -6511
DUP_VAL_ON_INDEX 1 -1 UNIQUE索引に重複(Duplicate)データを格納した時
INVALID_CURSOR 1001 -1001 不正カーソル処理時
INVALID_NUMBER 1722 -1722 文字列から数値の置換に失敗した時
LOGIN_DENIED 1017 -1017 ログイン失敗時
NO_DATA_FOUND 1403 +100
NOT_LOGGED_ON 1012 -1012 Oracleに接続していないプログラムがデータベースコールを発行した時
PROGRAM_ERROR 6501 -6501 PL/SQL内部に問題点がある時
ROWTYPE_MISMATCH 6504 -6504
SELF_IS_NULL 30625 -30625
STORAGE_ERROR 6500 -6500 メモリ関連エラー時
SUBSCRIPT_BEYOND_COUNT 6533 -6533 コレクションの要素数より大きいインデックスでアクセスした時
SUBSCRIPT_OUTSIDE_LIMIT 6532 -6532 有効範囲外で表、varrayなどにアクセスした時
SYS_INVALID_ROWID 1410 -1410
TIMEOUT_ON_RESOURCE 51 -51
TOO_MANY_ROWS 1422 -1422 select into文が複数行を返した時
VALUE_ERROR 6502 -6502 算術、変換、切り捨て、サイズ制約などのエラー時
ZERO_DIVIDE 1476 -1476
when 例外名 then
declare
tmp_data number;
begin
select 1/0 into tmp_data from dual;
exception
when ZERO_DIVIDE then
tmp_data := 10;
end;
/
declare
tmp_data number;
begin
select col1 into tmp_data from test_tbl;
exception
when no_data_found or too_many_rows then
null;
end;
/
declare
tmp_data number := 0;
begin
select 1/0 into tmp_data from dual;
exception
when no_data_found then
null;
when too_many_rows then
null;
when zero_divide then
null;
when others then
null;
end;
/
エラーメッセージ取得
SQLCODE関数とSQLERRM(ERRor-Message)関数を利用
SQLCODEとSQLERRMは必ず変数に代入し なければならない。
declare
tmp_id number;
tmp_data test_tbl.col2%type := '';
error_msg varchar2(2000);
begin
select 1/0 into tmp_data from dual;
exception
when others then
if SQLCODE = -1476 then
tmp_id := SQLCODE;
insert into test_tbl values(tmp_id, 'test ok');
commit;
else
tmp_id := SQLCODE;
error_msg := SQLERRM;
insert into test_tbl values(tmp_id, tmp_data);
commit;
end if;
end;
/
関数
関数の種類
Function
戻り値が１つ
Procedure
戻り値が複数
構文
Function構文
Function 名前 [ ([引数名 [IN | OUT | INOUT]? データ型 [default デフォルト値]? ]* ) ]
Return データ型 is [宣言部]
Begin
[実行部]
[Exception]
End;
Procedure構文
Procedure 名前[ ([引数名 [IN | OUT | INOUT]? データ型 [default デフォルト値]? ]* ) ] is [宣言部]
Begin
[実行部]
[Exception]
End;
プロシージャのサンプル
declare
procedure test_pcd( tmp_num IN number, tmp_str IN varchar2 ) is
begin
insert into test_tbl values (tmp_num, tmp_str);
end;
begin
test_pcd(99,'99');
commit;
end;
/
function, procedureでは、引数でデータサイズなどの制約をつけられない。
declare
procedure sample1(var in varchar2(20)) is
begin
end;
begin
end;
/
show errors;
SQL> @test04.sql
procedure sample1(var in varchar2(20)) is
*
行2でエラーが発生しました。:
ORA-06550: 行2、列36:
PLS-00103: 記号"("が見つかりました。 次のうちの1つが入るとき:
:= . ) , @ % default character
記号":=" は続行のために"("に代わりました。
ORA-06550: 行4、列3:
PLS-00103: 記号"END"が見つかりました。 次のうちの1つが入るとき:
begin case declare exit for goto if loop mod null pragma
raise return select update while with
制約をつけたい場合は、subtypeを使う
subtype データ名 is データ型
declare
subtype myVChar is varchar2(20);
procedure sample1(var in myVChar) is
begin
null;
end;
begin
null;
end;
/
show errors;
共通ライブラリ化の方法
PL/SQLでは、共通ライブラリ化することを、ストアドプロ グラムと呼ぶ
他の言語では、一般的にファイルとしてライブラリとするが、PL/SQLはデータベース内に格納する
データベーススキーマのオブジェクトであるので実行権限(Execute権限)が必要
PL/SQLをコンパイルしたコードをPコードと 呼ぶ
作成時にコンパイルしているため、解析フェーズが省略される分、パフォーマンスが良い
ユーザに関係なく、システム表領域に作成される
種類 概要
ストアドプロシージャ １つのプロシージャをライブラリ化
ストアドファンクション １つのファンクションをライブラリ化
データベーストリガ イベントハンドラ
パッケージ 上記をまとめる。
ストアド化
create or replace
procedure test_pcd(
tmp_num IN number,
tmp_str IN varchar2
) is
begin
insert into test_tbl values (tmp_num, tmp_str);
commit;
end;
/
show errors;
パッケージ作成
仕様部と本体部を作成する。
仕様部が外部インタフェースになる。
仕様部と本体部は１つのファイルに纏めても、分割しても良い
仕様部を最初にコンパイルしなければならない。
仕様部
create or replace package パッケージ名 is
...
end パッケージ名;
create or replace package sample_package is
procedure sample1(wk_col1 in number);
function sample2(wk_col2 in varchar2) return varchar2;
end sample_package;
/
show errors;
本体部
create or replace package body パッ ケージ名 is
...
end パッケージ名;
create or replace package body sample_package is
procedure sample1(wk_col1 in number) is
begin
insert into test_tbl values (wk_col1, 'package');
commit;
end;
function sample2(wk_col2 in varchar2) return varchar2 is
begin
insert into test_tbl values ('11', wk_col2);
commit;
return '0';
end;
end sample_package;
/
show errors;
作ったものをテストするプログラム
declare
wk_str varchar2(100);
function test_fnc(wk_col1 in number) return number is
begin
return 0;
end;
begin
sample_package.sample1(1);
wk_str := sample_package.sample2('1');
end;
/
show errors;
解説
パッケージもオブジェクトなので、ピリオドを利用して呼び出す。 適当なファイル名をつけて実行してみてください。
トリガー
ユーザはトリガーが起動した事は分からない
DMLの前後などに設定する。
プログラムでよくあるイベントハンドラである。
トリガーは引数を持たない。
注意点
トリガーは、自動起動するため、開発者はその存在を忘れる可能性がある。 運用後の保守、デバック等が複雑になる傾向があるので、基本的にはトリガー機能を使わず、代案がない場合に利用するように心がける事
トリガーの管理
alter triger トリガー名 [enable | disable]
user_triggers
create or replace trigger トリガー名 before
[insert | update | delete] or on テーブル名
declare
begin
end;
/
create or replace trigger sample_trigger before
insert or update or delete on test_tbl
declare
begin
insert into trigger_log values (99,'trger go');
end;
/
実行
SQL> insert into test_tbl values (1, '111');
1行が作成されました。
結果
SQL> select * from trigger_log;
COL1 COL2
---------- ----------------
99 trger go
API(Application Program Interface)
Oracle9i PLSQL パッケージ・プロシージャおよびタイプ・リファレンス リリース2(9.2)がAPIのドキュメントになる。 sysユーザ権限でdesc standardを実行すれば、 standardと呼ばれる、標準APIが分かる。 以下でソースコードが読める。
select text from user_source where name= 'STANDARD';
デバック
プログラムに空白行を含めない。
PL/SQLでは、空白行は実行時に１行として含めない。つまり空白行は実行時に削除 する。 そのため、エ ラー時の行番がエディタ上の行番号とづれてしまう。 エディタでの開発の場合は、プログラムに空白行を含めない方が効率がよい。
user_errors表をつかう。
PL/SQLでは、ストアドプログラムは、プログラムにエラーが存在しても、取り合え ずデータベースに登録する。どこにエラーがあるか判断するには、user_errors表を確認する。
user_source表をつかう。
コンパイルした結果とソースコードをこの表に記録しているので、デバック時に役立つ
show errorsコマンド
user_errors表を確認するのは、手間が掛かるのでソースコードの最終行に show errorsコマンドを書いておく事でデバックが簡単になる。
他の言語よりコンパイルのサイクルを短くする。
PL/SQLをエディタで開発する場合は、ソースコードを全て記述してからコンパイル するのではなく少しコーディングしたら直ぐにコンパイルするように心がける事。PL/SQLの開発環境は粗悪なので非常に有効である。
