Oracle PL/SQL プログラミング入門 (再)

PL/SQL の基礎的な知識をまとめる。

PL/SQL とは

SQL を手続き型言語として拡張したのが PL/SQL である。
Oracle データベースで利用できる。

条件分岐やループなど SQL だけではできない処理を実行できる。

PL/SQL プログラムの種類

プログラムをデータベースに格納しておくかどうかで種類が分かれる。

1. ストアドプロシージャ、ストアドファンクション、パッケージ

   PL/SQL ブロックに名前を付けて、データベースに格納しておく。
   名前で処理を呼び出す。
   格納できる PL/SQL ブロックは、プロシージャ・ファンクション・パッケージなど。

2. 無名ブロック

   データベースに格納されていない PL/SQL ブロックのこと。
   ファイルなどに保存しておいた PL/SQL ブロックを指定して実行する。

PL/SQL プログラムの構造

PL/SQL プログラムはブロックと呼ばれる固まりで構成される。

ブロックは DECLARE または BEGIN で始まり、END; で終わる。

ブロックには宣言部、処理部、例外処理部の3つのセクションを定義できる。

各セクションの説明は以下のとおり。

宣言部

DECLARE で開始する。
変数や定数、ファンクション、プロシージャなどを宣言する。

処理部

BEGIN で開始する。
そのブロックで行う処理を記載する。

例外処理部

EXCEPTION で開始する。
そのブロックの処理部で起きた例外を処理する。

すべてのセクションを定義したブロックは次のようになる。

DECLARE
  宣言部

BEGIN
  処理部

EXCEPTION
  例外処理部

END;

3つのセクションのうち、処理部だけが必須である。

次のように BEGIN と END; だけで構成するのが最小の PL/SQL ブロックとなる。

BEGIN
  NULL;
END;

処理部に一つも処理がないとエラーになる。

BEGIN
END;

↓

ORA-06550: 行2、列1:
PLS-00103: 記号"END"が見つかりました。 次のうちの1つが入るとき:

ブロックは入れ子にすることもできる。

BEGIN
  処理-1

  BEGIN
    処理-2
  EXCEPTION
    処理-2 の例外処理
  END;

  処理-3

EXCEPTION
  全体の例外処理
END;

SQL*Plus による PL/SQL ブロックの実行方法

コマンドプロンプトから PL/SQL ブロックを入力して実行する

Hello World! と出力する PL/SQL ブロックを、SQL*Plus から実行してみたい。

1. コマンドプロンプトから SQL*Plus を起動する。
   接続先は適宜変更すること。

   > sqlplus scott/tiger@ORCL

   起動に成功するとコマンドプロンプト “SQL>” が表示される。

2. SET SERVEROUTPUT ON を入力する。

   これを入れておかないと Hello World! が表示されないので注意すること。

3. PL/SQL ブロックを入力する。

   BEGIN
       DBMS_OUTPUT.PUT_LINE('Hello World!');
   END;

   NOTE: DECLARE または BEGIN を入力すると PL/SQL モード になる。そのまま残りのプログラムの入力を続ける。

4. ドット（.）を入力して PL/SQL モードを終了する。
   →これで入力した PL/SQL ブロックが SQL バッファに格納される。

   ドットまで入力が終わった状態で、コマンドプロンプトは次のようになっているはず。
   カーソルは最後の “SQL>” にある。

   SQL> SET SERVEROUTPUT ON
   SQL> BEGIN
     2      DBMS_OUTPUT.PUT_LINE('Hello World!');
     3  END;
     4  .
   SQL>

5. スラッシュ（/）を入力して、SQL バッファのプログラムを実行する。
   → Hello World! が表示される。

   SQL> /
   Hello World!
   
   PL/SQLプロシージャが正常に完了しました。

6. EXIT コマンドを実行して SQL*Plus を終了する。

テキストファイルに保存した PL/SQL ブロックを実行する

一連の流れをテキストファイルに保存しておき、それを SQL*Plus の起動時に実行することもできる。

1. テキストファイルに以下の内容を入力する。
   ファイル名は HelloWorld.sql とする。

   SET SERVEROUTPUT ON
   
   BEGIN
       DBMS_OUTPUT.PUT_LINE('Hello World!');
   END;
   .
   /
   EXIT

2. コマンドプロンプトから SQL*Plus を起動する。
   @ に続けてテキストファイルのパスを指定する。

   > sqlplus scott/tiger@ORCL @HelloWorld.sql

   SQL*Plus 起動 ～ PL/SQL ブロック実行 ～ SQL*Plus 終了まで一気に実行される。
   出力結果のイメージはこのような感じ。
   ↓

   C:\Users\username>sqlplus scott/tiger@ORCL @C:\Example\HelloWorld.sql
   
   SQL*Plus: Release 11.2.0.2.0 Production on 月 1月 1 10:00:00 2024
   
   Copyright (c) 1982, 2014, Oracle.  All rights reserved.
   
   
   
   Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
   に接続されました。
   Hello World!
   
   PL/SQLプロシージャが正常に完了しました。
   
   Oracle Database 11g Release 11.2.0.4.0 - 64bit Productionとの接続が切断されました。

最低でも次の2点は必ず書くこと。

• PL/SQL ブロックが終わったら、その次にスラッシュ（/）だけの行を書く。
  スラッシュ（/）だけの行があれば、ドット（.）だけの行はなくてもいい。

• ファイルの最後に EXIT コマンドを書く。

書き忘れると SQL*Plus が終了されず、いつまで経っても処理が終わらなくなってしまう。

変数の宣言

変数の宣言は、宣言部で変数名とデータ型を指定する。

宣言と同時に初期値の格納もできる。
値を格納するときは := を使う。

変数名     データ型;
変数名     データ型 := 初期値;

例

DECLARE
  id_number   NUMBER(4);
  name        VARCHAR2(32) := 'John';

定数の宣言

定数はプログラムから変更できない値を保持する。

定数の宣言は、宣言部で定数名とデータ型、定数値を指定する。

定数名     CONSTANT データ型 := 定数値;

例

DECLARE
  pi  CONSTANT NUMBER := 3.14159265359;

定数をプログラムから変更しようとするとエラーになる。

BEGIN
  pi := 3.14;

↓

PLS-00363: 式PIは割当てターゲットとして使用できません。

%TYPE 属性によるデータ型の指定

%TYPE 属性を使うと、既存のカラムと同じデータ型を指定することができる。

たとえば、DEPT テーブルの DNAME カラムと同じデータ型で変数を宣言するときは、次のように書く。

var_name    DEPT.DNAME%TYPE;

%ROWTYPE 属性によるデータ型の指定

%ROWTYPE 属性を使うと、既存のテーブルと同じレコードを宣言できる。

たとえば、DEPT テーブルと同じ定義のレコードを宣言するときは、次のように書く。

var_name    DEPT%ROWTYPE;

var_name は DEPT テーブルと同じカラムを持つ変数として扱える。

var_name.DEPTNO := 30;
var_name.DNAME := 'SALES';

検索結果を変数に格納する

SELECT INTO 文を使って検索した結果を変数に格納できる。

SELECT INTO 文は次のように書く。

SELECT 検索するカラム
  INTO 検索するカラムの値を入れる変数
  FROM テーブル名

次のプログラムは、DEPT テーブルの DNAME の内容を変数 tmp_name に格納する。

DECLARE
  tmp_name  DEPT.DNAME%TYPE;
BEGIN
  SELECT DNAME
    INTO tmp_name
    FROM DEPT
   WHERE DEPTNO = '10';

複数のカラムや計算した結果を入れることもできる。

DECLARE
  tmp_name  EMP.ENAME%TYPE;
  tmp_val   EMP.SAL%TYPE;
BEGIN
  SELECT ENAME, SAL * 1.5
    INTO tmp_name, tmp_val
    FROM EMP
   WHERE EMPNO = '7521';

SELECT INTO 文は、検索結果が1件でないと以下のエラーが発生する。

0件 → ORA-01403: データが見つかりません。

2件以上 → ORA-01422: 完全フェッチがリクエストよりも多くの行を戻しました

例外処理

プログラムの実行時に発生したエラーのことを例外と言う。

処理部で発生した例外をキャッチして処理するときは、その内容を例外処理部に定義する。

構文は以下のとおりで、WHEN 例外名 THEN に続けてその例外に対する処理を書く。

WHEN は例外の種類に応じて複数書くことができる。

定義されている例外の名前は、PL/SQL開発者ガイド の 表4-1 事前定義例外 を参照。

発生したすべての例外をキャッチするときは、例外名に OTHERS を指定する。

EXCEPTION
  WHEN 例外名-1 THEN
    例外-1に対する処理
  WHEN 例外名-2 THEN
    例外-2 に対する処理
  WHEN OTHERS THEN
    例外-1、例外-2 以外のすべての例外に対する処理

たとえば、SELECT INTO 文で発生する例外を処理するには次のように定義する。

DECLARE
  tmp_name  DEPT.DNAME%TYPE;

BEGIN
  SELECT DNAME
    INTO tmp_name
    FROM DEPT
   WHERE DEPTNO = '10';

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- TODO: ゼロ件時の処理を書く

  WHEN TOO_MANY_ROWS THEN
    -- TODO: 複数件時の処理を書く

  WHEN OTHERS THEN
    -- TODO: 予期せぬエラーの処理を書く
END;

制御構文

IF 文

条件に応じて処理を分岐するには IF 文を使う。

IF 文の構文は以下のとおり。

IF condition_1 THEN
  statements_1
ELSIF condition_2 THEN
  statements_2
ELSE
  else_statements
END IF;

上から順に条件を判定し、最初に条件を満たした処理を実行する。

condition_1 を満たす場合、statements_1 を実行する。
condition_2 を満たす場合、statements_2 を実行する。
いずれも満たさない場合、else_statements を実行する。

ELSEIF ではなく ELSIF なので間違えないように（E がない）。

ELSIF は複数書ける。

ELSIF と ELSE は任意なのでなくてもよい。

次のサンプルは、変数 grade に応じて出力内容を変更するプログラムである。

IF grade = 'A' THEN
  DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF grade = 'B' THEN
  DBMS_OUTPUT.PUT_LINE('Very Good');
ELSIF grade = 'C' THEN
  DBMS_OUTPUT.PUT_LINE('Good');
ELSIF grade = 'D' THEN
  DBMS_OUTPUT.PUT_LINE('Fair');
ELSIF grade = 'F' THEN
  DBMS_OUTPUT.PUT_LINE('Poor');
ELSE
  DBMS_OUTPUT.PUT_LINE('No such grade');
END IF;

単純な CASE 文

単純な CASE 文の構文は次のとおり。

CASE selector
  WHEN selector_value THEN statements
  ELSE else_statements
END CASE;

selector と selector_value が等しい最初の statements を実行する。
いずれも等しくない場合、else_statements を実行する。

WHEN は複数書ける。

ELSE は任意なのでなくてもよい。
ただし、いずれの条件も満たさず else_statements を実行するとなった場合に ELSE が定義されていないと、実行時エラー「ORA-06592: CASE文の実行中にCASEが見つかりません」が発生する。

次のサンプルは、変数 grade に応じて出力内容を変更するプログラムである。
（IF 文のサンプルを単純な CASE 文で書き直したものである）

たとえば grade が ‘B’ なら “Very Good” と出力される。

CASE grade
  WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
  WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
  WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
  WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
  ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;

検索 CASE 文

検索 CASE 文の構文は次のとおり。

CASE
  WHEN condition THEN statements
  ELSE else_statements
END CASE;

condition を満たす最初の statements を実行する。
いずれも満たさない場合、else_statements を実行する。

WHEN は複数書ける。

ELSE は任意なのでなくてもよい。
ただし、いずれの条件も満たさず else_statements を実行するとなった場合に ELSE が定義されていないと、実行時エラー「ORA-06592: CASE文の実行中にCASEが見つかりません」が発生する。

次のサンプルは、変数 grade に応じて出力内容を変更するプログラムである。
（単純な CASE 文のサンプルを検索 CASE 文で書き直したものである）

たとえば grade が ‘B’ なら “Very Good” と出力される。

CASE
  WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
  WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
  WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
  WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
  ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;

Loop 文

Loop 文の構文は次のとおり。

LOOP
  statements
END LOOP;

statements を繰り返し実行する。

このままでは無限ループとなるため、ループを抜ける条件の指定が必要である。

EXIT 文

EXIT 文は現在のループを抜ける。

次のサンプルは、condition を満たした場合にループを抜けるプログラムである。

LOOP
  statements

  IF condition THEN
    EXIT;   -- ここでループを抜ける
  END IF;
END LOOP;
            -- すると制御がここに渡る

EXIT WHEN 文

EXIT WHEN 文は指定の条件を満たす場合に現在のループを抜ける。

次のサンプルは、condition を満たした場合にループを抜けるプログラムである。

LOOP
  statements

  EXIT WHEN condition;  -- 条件を満たしたらループを抜ける
END LOOP;
            -- すると制御がここに渡る

ラベル付きループを抜ける

ループにラベルを付けておくと外側のループを抜けることもできる。

consition_1 を満たす場合は現在のループを抜ける。
condition_2 を満たす場合は外側のループを抜ける。

＊< と > がタグとして認識されてしまう不具合のため全角文字にしてある。
コピペして使うときは半角に戻していただきたい。

＜＜outer_loop＞＞
LOOP
  ＜＜inner_loop＞＞
  LOOP
    statements
    
    EXIT WHEN inner_loop WHEN consition_1;
    EXIT WHEN outer_loop WHEN condition_2;
  END LOOP inner_loop;
END LOOP outer_loop;

CONTINUE 文

CONTINUE 文は現在のループを終了し、次のループを始める。

次のサンプルは、condition を満たした場合に次のループを始めるプログラムである。

LOOP
                -- すると制御がここに渡る
  statements

  IF condition THEN
    CONTINUE;   -- ここで現在のループを終了
  END IF;
END LOOP;

CONTINUE WHEN 文

CONTINUE WHEN 文は指定の条件を満たす場合に現在のループを終了し、次のループを始める。

次のサンプルは、condition を満たした場合に次のループを始めるプログラムである。

LOOP
                            -- すると制御がここに渡る
  statements

  CONTINUE WHEN condition;  -- 条件を満たしたら現在のループを終了
END LOOP;

FOR LOOP 文

構文は以下のとおり。

FOR index IN lower_bound..upper_bound LOOP
  statements
END LOOP;

index は lower_bound から始まる。
index が upper_bound に達するまで繰り返される。
（index ＞ upper_bound でループを抜ける）
index は 1 ずつ増加する。

次のサンプルは、index を 1 から始め、6 になるとループを抜けるプログラムである。

FOR i IN 1..5 LOOP
  DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

反転 FOR LOOP 文

REVERSE 指定すると index を減らしていくことができる。

FOR index IN REVERSE lower_bound..upper_bound LOOP
  statements
END LOOP;

REVERSE 指定した場合、index は upper_bound から始まる。
index が lower_bound に達するまで繰り返される。
（index ＜ lower_bound でループを抜ける）
index は 1 ずつ減少する。

次のサンプルは、index を 5 から始め、0 になるとループを抜けるプログラムである。

FOR i IN REVERSE 1..5 LOOP
  DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

WHILE LOOP 文

WHILE LOOP 文は条件を満たす間、処理を繰り返す。

構文は以下のとおり。

WHILE condition LOOP
  statements
END LOOP;

condition を満たす間、statements を繰り返し実行する。

GOTO 文

GOTO 文は指定したラベルに制御を渡す。

ラベルはブロックの前、または文の前のみに置くことができる。
ラベルの置き方は、ラベル名を << と >> で囲む。
（GOTO 文にはラベル名だけを指定する）

GOTO 文を多用するとコードの理解やメンテナンスが難しくなるため、極力使わない方が望ましい。

次のサンプルは、condition を満たす場合にラベル example_label へ制御を渡すプログラムである。

LOOP
  IF condition THEN
    GOTO example_label;
  END IF;
END LOOP;

-- NOTE: 不具合対策で全角にしている。コピペ時は半角にしていただきたい
＜＜example_label＞＞
DBMS_OUTPUT.PUT_LINE('EXAMPLE GOTO');

NULL 文

NULL 文は何もしない（次の文に制御を渡すのみ）。

たとえば CASE 文の ELSE に置いておくことで、どの条件も満たさない場合にエラーとなるのを防げる。

CASE grade
  WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
  WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
  WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
  WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
  ELSE NULL;
END CASE;

続く……