まずはこれだけPL/SQL【第1回 変数・制御構文・カーソル・例外処理まで】

PL/SQLのプログラムを業務で利用しているが、実務を通してちょいちょい理解していくのは時間がたつと忘れるので、ざっくり体系的にまとめる備忘録。
今回は、PL/SQLの文法である変数/定数の使い方、FORやIFなどの制御構文、例外処理まで。

はじめに（デバッグ方法）

PL/SQLのデバッグ方法は、SQL*Plusの画面に文字を表示させるためによく使う以下の関数を使用する。

DBMS_OUTPUT.PUT_LINE('文字列');

PL/SQLでは原則として、大文字と小文字は区別されない。

なお、文字列を出力させるためには、事前に以下の環境変数をONにする必要がある。

set serveroutput on

また、SQL*Plus上でEDITコマンドを利用することによりテキストエディタを使用して編集が可能。

edit <ファイル名>

ファイルは、@<ファイル名>で実行可能。

@<ファイル名>

環境変数ECHOをONにすると、@<ファイル名>で実行されるソースコードの内容を画面上に表示することができる。

set echo on

エラーが発生した際には、発生した「行」と「列」が表示される。まずは、以下の「l」（小文字のエル）コマンドでERROR箇所を見るのが良い。

l <開始行番号> <終了行番号>

コメントは、一行の場合は「--」、複数行の場合は「/* */」を使う。

基礎文法

PL/SQLブロック

PL/SQLブロックの構造は以下の通り。

DECLARE
  /* 宣言部 */
BEGIN
  /* 実行部 */
EXCEPTION
  /* 例外処理部 */
END;

宣言部と例外処理部は省略可能。また、PL/SQLブロックの中に、PL/SQLブロックをネスト（埋め込む）させることも可能。

変数・定数

宣言部で変数を定義可能。定義は以下の通り。

<変数名> <データ型> [ NOT NULL ] [ { := | DEFAULT } <値> ];

代入演算子（:=）で値の代入も可能。また、DEFAULT値も設定可能。（以下例では、変数名を分かりやすくするために、変数名の末に「$」を加えている。）

DECLARE
  ikep_hp$ NUMBER(5);
  ikep_skill$ VARCHAR2(10) DEFAULT 'たいあたり';
BEGIN
  ikep_hp$ := 500;
  DBMS_OUTPUT.PUT_LINE('ikepのライフポイントは' || ikep_hp$);
END;
/

変数に使用できるデータ型は、スカラー型とコンポジット型がある。

**スカラー型は、Oracleによって事前に定義されているデータ型。**以下の通り。

• NUMBER～数値型
• CHAR・VARCHAR2～文字型
• DATE～日付型
• BOOKEAN～ブール型

コンポジット型は、ユーザが事前にデータ型を定義する。

• RECORD～レコード。表の行のように複数フィールドを一度に持つことができる。
• TABLE～コレクション。表の列のように同一列に複数の値を持つことができる。

%TYPE、%ROWTYPE

selectで検索したデータを直接変数に代入する際には、%TYPE、%ROWTYPE属性を使うと便利。

<変数名> <表名>.<列名>%TYPE;
<変数名> <表名>%ROWTYPE;

例として、userid,name,age,emailの項目を持つUSERSテーブルがあった場合、以下のように宣言部で変数を定義できる。

DECLARE
 name$ users.name%TYPE;
 rec$  users%ROWTYPE;
BEGIN
 --処理
END;
/

注意点として、PL/SQLでは前方参照できないので、必ず変数を使用する前に定義しておく必要がある。

定数

定数は、CONSTANTキーワードを入れる。定義時は、必ず代入演算子で初期化するか、DEFAULTキーワードで値を設定する必要がある。

<定数名> CONSTANT <データ型> [NOT NULL] { := | DEFAULT } <初期値>;

IF-THEN、ELSE、ELSIF文

他の言語と同様に、IF文も使える。

IF <条件式> THEN
 --処理1
ELSIF <条件式> THEN
 --処理2
ELSE
 --処理3
END IF;

FOR-LOOP文

FOR文はループカウンタについては宣言部で定義する必要はない。また、自動で値がセットされループするごとに1ずつ増加する。因みに、値を明示的に代入することはできない。

FOR <ループカウンタ> IN <初期値> .. <終了値> LOOP
 --繰り返し処理
END LOOP ;

以下サンプル。

BEGIN
 FOR r IN 1 .. 5 LOOP
  DBMS_OUTPUT.PUT_LINE(r || '万年と' ||  r * 2 || '千年前から愛してる');
 END LOOP ;
END;
/

WHILE-LOOP文

定義は以下のとおり。条件を満たしている場合は処理を繰り返し行う。

WHILE <条件式> LOOP
 --処理
END LOOP;

SELECT INTO文

検索（SELECT文）した結果を使って処理をしたい場合には、「SELECT INTO文」か「カーソル」を利用する方法の2つある。この二つはよく利用するものなのでしっかりおさえておく。

まずは、SELECT INTO文の定義から。

SELECT <列名> [, ...] INTO <変数名> [, ...] FROM <表名>;

SELECT INTO文は検索結果データが1行だけの時に使用できる。つまりは、WHEREで主キーを指定する場合や、MAX関数といったグループ関数を使用している場合に使う。

以下、例ではUSERSテーブルから、userid(主キー)が'1'のレコードのnameとageをそれぞれ変数に取り出している。

DECLARE
 name$ users.name%TYPE;
 age$  users.age%TYPE;
BEGIN
 SELECT name, age INTO name$, age$ FROM users WHERE userid = '1';
 DBMS_OUTPUT.PUT_LINE(name$ || 'は' || age$ || '歳');
END;
/

カーソル

カーソルを使えば、複数行のデータ（0行、1行も可）を処理できる。定義は以下の通り。

CURSOR <カーソル名> IS <問い合わせ文>;

カーソルを利用する際は、OPENで問い合わせを実行し、FETCHでデータの取り出しを行い、
CLOSEで終了させるが、これらを一度に行える、カーソルFORループを利用するのがおすすめ。

FOR <ループ索引名> IN <カーソル名> LOOP
 --処理
END;

例として、USERSテーブルから、nameとageを全レコード分出力する。

DECLARE
 CURSOR users$ IS SELECT name, age FROM users;
BEGIN
 FOR rec$ IN users$ LOOP
  DBMS_OUTPUT.PUT_LINE(rec$.name || 'は' || rec$.age || '歳');
 END LOOP;
END;
/

パラメータ付きカーソル

カーソルには、動的に引数を渡してWHERE句に追加する、パラメータ付きカーソルというものもある。パラメータ付きカーソルの定義は以下の通り、カーソル名の後ろに仮パラメータを宣言する。

CURSOR <カーソル名>(<仮パラメータ名> <データ型>) IS SELECT ...;

カーソル付きFORループさせる際も、以下の定義通りカーソル名の後ろに実パラメータを渡す。

FOR <ループ索引名> IN <カーソル名>(<実パラメータ>) LOOP
 --処理
END LOOP;

以下の例では、USERSテーブルの年齢が20歳以上のユーザを表示する。in_age$で引数を渡しているので、動的に年齢の条件を変更させることができる。

DECLARE
 in_age$ NUMBER;
 CURSOR users$(age$ NUMBER) IS SELECT name, age FROM users WHERE age > age$;
BEGIN
 in_age$ := 20;
 FOR rec$ IN users$(in_age$) LOOP
  DBMS_OUTPUT.PUT_LINE(rec$.name || 'は' || rec$.age || '歳');
 END LOOP;
END;
/

FOR UPDATE付きカーソル

カーソルのSELECT文には、FOR UPDATE句を使用できる。問い合わせの際に、INSERTやUPDATE、DELETEをする際には、排他ロックが必要になるがカーソルでも同じように可能。

CURSOR <カーソル名>) IS SELECT ... FOR UPDATE;

FOR UPDATE句自体は、PL/SQLの機能では無いが、オプションも使用できる。定義は、以下の通り。

<問い合わせ文> FOR UPDATE [OF <列名>] [NOWAIT | WAIT n];

• OFオプションは、表の結合処理で、指定した列の存在する表だけロックする。
• NOWAITオプションは、検索対象がすでにロック済みの場合、待機せずにERRORを吐く。
• WAIT nオプションは、すでにロックされている際、n秒間だけ待機する。

WHERE CURRENT OF句

カーソルがFOR UPDATE句付きの場合、カーソルをLOOPさせたときの**現在の行に対する更新は、WHERE CURRENT OF句で行える。**これは、UPDATEとDELETEの条件指定時に使える。

UPDATE ... WHERE CURRENT OF <カーソル名>;

以下の例では、USERSテーブルの全ユーザの年齢（age）の値を5歳引いて更新している。

DECLARE
 CURSOR users$ IS SELECT name, age FROM users FOR UPDATE NOWAIT;
BEGIN
 FOR rec$ IN users$ LOOP
  UPDATE users SET age = age - 5 WHERE CURRENT OF users$;
  DBMS_OUTPUT.PUT_LINE(rec$.name || 'は' || '5歳若返って、' || (rec$.age - 5) || '歳になった。');
 END LOOP;
END;
/

明示カーソル、暗黙カーソル属性

カーソルには属性が用意されており、明示カーソルと暗黙カーソルがある。カーソル実行後、各情報を取り出すことができる。明示カーソルは以下の通り。

<カーソル名><明示カーソル名>

• %NOTFOUND～FETCH文で行が取り出せた場合はFALSE、取り出せなかった場合はTRUE。
• %FOUND～％NOTFOUNDの逆
• %ROWCOUNT～FETCH文で取り出された行数

暗黙カーソルは以下の通り。

SQL<暗黙カーソル>

暗黙カーソルでは、直前に実行されたSQL文のカーソル属性しか確認できない。

例外の種類

例外には、大きく分けて内部例外とユーザ定義例外の2種類がある。更に内部例外には、事前定義の内部例外と無名の内部例外の2種に分かれる。

事前定義の内部例外は、Oracleの内部で事前に定義されているものである。以下の例では、SELECT INTOにより、1行よりも多く結果が返ってきてしまったときに出る「too_many_rows」の例外をキャッチしている。

DECLARE
 name$ users.name%TYPE;
BEGIN
 SELECT name INTO name$ FROM users;
EXCEPTION
 WHEN too_many_rows THEN
  DBMS_OUTPUT.PUT_LINE('too many rows');
 WHEN others THEN
  DBMS_OUTPUT.PUT_LINE('others');
END;
/

無名の内部例外は、事前に名前が付けられていない例外のこと。よく「ORA-✖✖エラー」と出るERRORのことである。

ユーザ定義のエラーメッセージでよく利用するのが、RAISE_APPLICATION_ERRORであり、これはERRORとして出力したい条件をユーザ側が決め、独自にエラーコードとエラーメッセージを書いて出力することができる。

RAISE_APPLICATION_ERROR(<エラー番号>, <エラーメッセージ>);

OTHERSハンドラを使用すると、例が処理部で例外名が指定されていないすべての例外を処理することができる。

WHEN others THEN
 --処理

OTHERSハンドラを使用すれば、すべての例外に対応できるため、必ず正常終了できる。OTHERSハンドラを使用する場合、以下のエラー報告関数が利用できる。

• SQLCODE関数～エラー番号を戻す関数
• SQLERRM関数～エラーメッセージを戻す関数

まとめ

ざっと業務で利用するところを駆け足でまとめた。正直なところ言語としては好きになれないところはあるが、予めある程度書き方や文法の知識が入っている、シンプルに書くこともできる。次回は、プロシージャやファンクション、パッケージについてをまとめる。