Oracle PL/SQL入門
目次
はじめに
PL/SQLの基本文法を独学するための入門記事です。
sqlplusを使って、Helloworldから始まり、データ形式、制御構造、関数の書き方などをサラっと学習します。
今後、オラクル社のリファレンスで学習できるようにするのが目的です。
参考資料には、オラクル社へのマニュアル・リンクを用意しました。
入門書に書いてある例えなどはありません。
言語習得に必要な部分のみしか書きませんので、sqlplusでサンプルを実行しながら学習してください。
PL/SQLと他の言語を比較してポイントとなる部分は赤 マークしています。
対象者
Oracleをこれから始める方。
PL/SQLとは
PL/SQL(読み:ぴーえるえすきゅーえる)は、Oracle(読み:オラクル)データベースで使える手続き型プログラミング言語です。
元々SQLには、分岐処理や繰り返し処理などができませんでしたが、この言語により可能になりました。
最近主流の言語に比べると、文法や構文に特徴があります。これは、PL/SQLの開発にはDIANAというAda言語の表現方法が
使われたためです。Ada言語の影響が強いので、ALGOL(読み:あるごる)系統の文法や構文になります。
最近の言語では、Free Pascal(読み:ぱすかる)やDelphi(読み:デルファイ)が、近い文法になります。
コメント
コメントの書き方は二つあります。
-- １行のコメントはハイフン(-)２つです。
/*
複数行のコメントは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 <an identifier>
<a double-quoted delimited-ident
制約をつけたい場合は、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の開発環境は粗悪なので非常に有効である。
参考資料
- DBMagazine 2000/11-連載 やっぱり役立つPL/SQL徹底活用講座
- OTN Japan - ドキュメント:Oracle9i Database
- PL／SQLユーザーズ・ガイドおよびリファレンス リリース2(9.2) [PDF]* HTML版はありません。
- Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンス 10gリリース2(10.2) B19257-01[HTML] [PDF]
- OTN Japan - PL/SQL Center
入門サイト
やさしくわかる事をコンセプトにPLSQLの入門を書いている方がいらしたのでリンクで紹介しておきます。
さいごに
2004年3月ごろから全然更新していませんでしたが、PL/SQL自体のインターネット上の情報が少ない事もありGoogleの高いページランクを維持してきました。今回、ユーザがオラクル社の情報を調べる事を仮定してオラクル社のマニュアルへのリンクを作成しました。私のサイト内でもこのPL/SQLの記事は、かなりユーザ数が多く、学習に必要な情報のみを記述するという学習方法がすこし実っているかとも感じています。 - 2006/03/16記
更新履歴
- 2009年09月01日 - PL/SQLとはを追加。
- 2006年10月03日 - 入門サイトを追加.
- 2006年07月27日 - W3Cの妥当性通過.
- 2006年07月23日 - XHTMLに移行作業.
- 2006年07月22日 - XHTMLに移行作業.
- 2006年07月06日 - 戻るリンクの削除.文章の修正.
