PL/SQLとは？プロシージャの書き方

PL/SQLとは、データベース問い合わせ言語SQLをOracleが独自に拡張したプログラミング言語です。入門者向けに基礎からPL/SQLの書き方を解説します。

DECLARE

PL/SQLブロックは宣言部、実行部および例外処理部に分かれていて、この順序で記述する。このうち、宣言部と例外処理部は省略可能で、実行部は必ず存在しなければならない。

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

宣言部は DECLARE 文から始まり、変数やカーソル、ユーザー定義例外の宣言を行なう。

宣言部で宣言した変数を使ってメッセージを出力するPL/SQLブロックの例を示す。

DECLARE
    -- 変数の宣言
    msg VARCHAR2(50);
BEGIN
    -- 変数に文字列を代入
    msg := 'Hello, world!';

    -- メッセージの出力
    DBMS_OUTPUT.PUT_LINE(msg);
END;
/

宣言部で宣言したプロシージャを呼び出してメッセージを出力するPL/SQLブロックの例を示す。

DECLARE
    -- プロシージャの宣言
    PROCEDURE hello_world(msg IN VARCHAR2) IS
    BEGIN
        -- メッセージの出力
        DBMS_OUTPUT.PUT_LINE(msg);
    END;
BEGIN
    -- プロシージャの呼び出し
    hello_world('Hello, world!');
END;
/

BEGIN

実行部は BEGIN 文から始まり、PL/SQLステートメントを記述する。

PL/SQLのステートメント（文）はセミコロンで終了する。

メッセージを出力するPL/SQLブロックの例を示す。

BEGIN
    -- メッセージの出力
    DBMS_OUTPUT.PUT_LINE('hello, world');
END;
/

単一行コメント

PL/SQLプログラム中にコメントを入れることができる。コメントとは、ユーザーのためにプログラムの使用方法や注意事項といった注釈を記述することであり、PL/SQLプログラムの実行には何の影響も及ぼさない。

コメントの記述スタイルとして、単一行コメントと複数行コメントの2種類がある。

単一行コメントとは、2つ連続したハイフン（-- ）から始まり、その行の末尾で終了するコメントである。行の中の任意の位置からコメントを開始することができる。

BEGIN
    -- メッセージの出力
    DBMS_OUTPUT.PUT_LINE('Hello, world!');
END;
/

END

PL/SQLブロックは END 文で終了する。省略できない。

複数行コメント

複数行コメントとは、スラッシュとアスタリスク（/* ）から始まり、アスタリスクとスラッシュ（*/ ）で終わるコメントである。行の中の任意の位置からコメントを開始することができる。単一行コメントと異なり、複数行に渡ってコメントを記述することができる。

BEGIN
    /*
      メッセージの出力
    */
    DBMS_OUTPUT.PUT_LINE('Hello, world!');
END;
/

%TYPE属性

既存表の列と同じデータ型または既存変数と同じデータ型として変数を宣言する場合、%TYPE 属性を使用することができる。

表の列と同じデータ型として変数を宣言する構文を次に示す。

変数名 表名.列名%TYPE [ := 初期値] ;

他の変数と同じデータ型として変数を宣言する構文を次に示す。

変数名 変数名%TYPE [ := 初期値] ;

%TYPE属性の使用例を次に示す。

DECLARE
    emp1 NUMBER;
    emp2 emp1%TYPE;
    emp3 employee.deptno%TYPE;

CASE

構文はSQLのCASE式と似ているが、PL/SQLのCASE文では、THEN句にPL/SQLのプログラムコードを記述できる点が異なる。


CASE
    WHEN 条件式1 THEN
        -- 条件式1が真のとき実行するステートメント
    WHEN 条件式2 THEN
        -- 条件式2が真のとき実行するステートメント
    WHEN 条件式n THEN
        -- 条件式nが真のとき実行するステートメント
    ELSE
        -- 条件式1～nがすべて偽のとき実行するステートメント
END CASE;

FOR

FOR LOOP文は開始値から終了値までカウンタの値を変えながら処理を繰り返す制御文である。

開始値から終了値までカウンタの値をひとつづつ増やしながら処理を繰り返す。

FOR counter IN start..end LOOP
    statements
END LOOP;

開始値から終了値までカウンタの値をひとつづつ減らしながら処理を繰り返す。

FOR counter IN REVERSE end..start LOOP
    statements
END LOOP;

counter: カウンタの識別子
start: カウンタの開始値
end: カウンタの終了値

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2      FOR i IN 1..3 LOOP
  3          DBMS_OUTPUT.PUT_LINE(i);
  4      END LOOP;
  5  END;
  6  /
1
2
3
SQL>

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2      FOR i IN REVERSE 1..3 LOOP
  3          DBMS_OUTPUT.PUT_LINE(i);
  4      END LOOP;
  5  END;
  6  /
3
2
1
SQL>

IF

IF condition1 THEN
    statement_sequence1
[ELSIF condition2 THEN
    statement_sequence2]
[ELSE
    statement_sequence3]
END IF;

LOOP

LOOP と END LOOP で囲まれた一連の文を無条件で繰り返す。


[<<label_name>>]
LOOP
    statement_sequence
END LOOP [label_name];

label_name にはラベル（固有のループを識別するための名前）を指定する。

ループを終了するには、EXIT 文を使用する。

EXIT [label_name] [ WHEN condition] ;

label_name には、ループのラベルを指定する。label_name を省略した場合は、1番内側のループを終了する。

condition には、ループを終了する条件式を指定する。

LOOP
    IF count < 0 THEN
        EXIT;
    END IF;
    count := count + 1;
    EXIT WHEN count > 100;
END LOOP;

PRAGMA AUTONOMOUS_TRANSACTION

PRAGMA AUTONOMOUS_TRANSACTIONとは、自立型トランザクションを設定するPL/SQL文である。自立型トランザクションを指定したサブプログラムは、サブプログラム呼び出し元のトランザクションとは分離されるため、サブプログラム内の処理だけを COMMIT又はROLLBACKすることができる。

CREATE OR REPLACE PROCEDURE set_ename(
    p_empno IN NUMBER,
    p_ename IN VARCHAR2
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    UPDATE emp SET emane = p_ename WHERE empno = p_empno;
    -- 自立型トランザクションは最後にCOMMIT又はROLLBACKする
    -- このプロシージャの処理(UPDATE)のみCOMMITする
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- 自立型トランザクションは最後にCOMMIT又はROLLBACKする
        -- このプロシージャの処理(UPDATE)のみROLLBACKする
        ROLLBACK;
END;
/

RETURN

RETURN 文は、サブプログラムの実行を終了させ、コールした側に制御を戻す。サブプログラムの中に複数の RETURN 文が存在してもよい。

プロシージャは RETURN 文が無くても実行部の最後でサブプログラムを終了して、コールした側に制御が戻る。ファンクションは実行部の最後に至るまでに必ず RETURN 文が必要である。

RETURN 文の構文を次に示す。

プロシージャの処理を終了して、呼び出し元に戻る。

RETURN;

ファンクションを処理を終了して、呼び出し元に戻る。呼び出し元には戻り値を返す。

RETURN expr;

expr: ファンクションの戻り値を表す式を指定する。

SELECT文

構文はSQLのSELECT文に似ているが、SELECT文の結果を格納する変数をINTO句に指定する点が異なる。

SELECT column INTO variable FROM table [WHERE condition]

column: 問い合わせる列やSQL式を指定する。カンマで区切って、複数の列等を指定できる。
var: SELECT結果を格納する変数を指定する。カンマで区切って、複数の変数を指定できる。
table: 問い合わせ先の表、ビュー又は副問合せを指定する。
condition: 選択条件を指定する。

PL/SQLでSELECT文を使う例を次に示す。

DECLARE
    v_ename emp.ename%TYPE;
    v_sal   emp.sal%TYPE;
BEGIN
    SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = '1901';
END;
/

WHILE

WHILE 条件式
LOOP
    -- 条件式が真のとき実行するステートメント
END LOOP;

コレクションの定義

NESTED TABLEを定義する構文を次に示す。

TYPE type_name IS TABLE OF element_type [NOT NULL];

type_name: 型指定子
element_type: PL/SQLのデータ型

varrayを定義する構文を次に示す。

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];

type_name: 型指定子
size_limit: 最大サイズ
element_type: PL/SQLのデータ型

コレクションの宣言

type_name collection_name;

type_name: 型指定子
collection_name: コレクション名

PL/SQLとは？ プロシージャの書き方