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

スポンサーリンク

PL/SQLの概要

PL/SQL (Procedural Language Extention to SQL)は、非手続き型言語であるSQLに、手続き型言語拡張を施したものである。PL/SQLはOracle DatabaseやOracle TimesTen In-Memory Databaseで使用することができる。

PL/SQLブロック

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

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

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

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

例外処理部は EXCEPTION 文から始まり、エラーが起こったときに実行する処理を記述する。

PL/SQLブロックは END 文で終了する。

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

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

宣言部で宣言した変数を使ってメッセージを出力する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;
/

ステートメント

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

コメント

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

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

単一行コメント

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

複数行コメント

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

属性

%TYPE属性

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

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

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

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

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

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

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

宣言部

宣言部 はさまざまな定義や宣言を行う場所である。匿名PL/SQLブロックの場合、宣言部は DECLARE 文から始まる。プロシージャまたはファンクションの場合、宣言部は IS (または AS )句から始まる。宣言部にて行うことを次に示す。

コレクションの定義

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
コレクション名

BEGIN

SELECT文

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

SELECT col [,col...] INTO var [,var...] FROM tab [,tab...] [WHERE cond]
col

列やSQL式を指定する。

var

SELECT結果を格納する変数を指定する。

tab

表、ビュー又は副問合せを指定する。

cond

選択条件を指定する。

IF文

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

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;

LOOP文

LOOPEND 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;

WHILE文

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

FOR LOOP

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>

RETURN文

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

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

RETURN 文の構文を次に示す。

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

RETURN;

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

RETURN expr;
expr

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

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;
/

システム・パッケージ

Oracle DatabaseやTimesTen In-Memory Databaseでは、PL/SQLアプリケーションの構築を支援するために、製品固有のシステム・パッケージが用意されている。

DBMS_ALERT パッケージ

データベース内の特定の値が変更されたときに、データベース・トリガーを使用してアプリケーションに警告するストアド・プロシージャ群を含むパッケージ

DBMS_PIPE プロシージャ

名前付きパイプを通じて異なるセッション間で通信するストアド・プロシージャ群を含むパッケージ

DBMS_STANDARD

DBMS_STANDARDとは、PL/SQLの標準パッケージである。PL/SQLからDBMS_STANDARDパッケージを呼び出す場合、パッケージ名の指定を省略できる。

DBMS_STANDARDのプロシージャ
プロシージャ 説明
RAISE_APPLICATION_ERROR ユーザ独自のエラーメッセージ(ORA-n)を発生させる。

RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERRORは、ユーザ独自のエラーメッセージ(ORA-n)を発生させるプロシージャである。

RAISE_APPLICATION_ERROR(error_number, message)
RAISE_APPLICATION_ERROR(error_number, message, add_stack)
error_number

エラー番号を-20000から-20999の範囲で指定する。

message

エラーメッセージを指定する。

add_stack

TRUEを指定した場合、エラーは以前のスタックに配置される。FALSEを指定した場合、エラーは以前のエラーをすべて置換する。

スポンサーリンク