本稿は PL/SQL の入門向けテキストです。SQL, 簡単なプログラミングの知識がある方が読まれるのを想定しています。

本稿のプログラムは Oracle Database の次のバージョンで動作確認しています。

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL とは

Wikipedia から引用します。

PL/SQL(ピーエル・エスキューエル)は、Oracle社が、Oracle Databaseのためにコンピュータのデータベース言語SQLを独自に拡張したプログラミング言語である。

もともと非手続き型言語であるSQLを手続き型言語 (Procedural Language) に拡張するところから「PL/SQL」と命名された経緯を持つ。 PL/SQLを使用すると、手続き型言語で埋め込みSQLによる処理と同様に、変数の利用やif、for、loopなどの制御構造を記述して業務ロジックをデータベース内に実装することができ、問い合わせ結果の行を一件ずつ処理する場合には、カーソルによるFETCHループでの処理を行うことが出来る。

基礎

PL/SQL では、Oracle データベースの SQL で使用可能な演算子・構文・データ型を使用できます。これらについての説明は本稿では省きます。

PL/SQL のプログラム構造

PL/SQL のプログラムは「ブロック」と呼ばれるプログラムの単位で構成されます。 ブロックの構造は次の通りです。

<<label>>  -- (任意)
DECLARE      -- 宣言部 (任意)
  -- ローカルタイプ・変数、およびサブプログラムの宣言

BEGIN        -- 実行部 (必須)
  -- ステートメント(宣言部で宣言された項目を使用できる)

[EXCEPTION   -- 例外処理部 (任意)
  -- 実行部で発生した例外 (エラー) の例外ハンドラ
]

END;

必ず記述する必要があるのは実行部のみです。宣言はブロックにおいて局所的に有効で、そのブロックの実行が完了すると消滅します。

また、ブロックはネストさせられます。ブロックは実行可能文であるため、別のブロックの実行可能文を配置できる場所に配置できます。

Hello, world!

PL/SQL における Hello, world プログラムは次のように記述できます。

BEGIN
  dbms_output.put_line('Hello, world!');
END;

SQL*Plus, SQL Developer などを使ってこのプログラムを実行できます。

ユーザ定義の識別子

変数名・プロシージャ名・ファンクション名などに使用する識別子は次の規則に従う必要があります。大文字と小文字は区別されません。

  • 先頭には数字・記号でない文字を使用する (マルチバイト文字も使用可能)
  • 2 文字目以降には数字・文字、および次の記号を使用できる

    • ドル記号 ($)
    • シャープ記号 (#)
    • アンダースコア (_)
  • 予約語は使用できない

識別子は、ダブルクォート (") で囲むことによって「引用識別子」とすることができます。このとき、上記の規則に従う必要はありません (任意の文字を使用可能)。また、大文字と小文字が区別されます。

いずれの場合も、識別子は 30 バイトを超えないようにしなければなりません。

変数・定数

PL/SQL における変数・定数の宣言・初期化・代入についての特性・注意事項を列挙します。

  • 初期化しなかった場合は NULL となる。
  • := という演算子を使って初期化・代入する。
  • CONSTANT キーワードと初期値を指定することにより定数を定義できる。
  • NOT NULL 制約を指定できる (このとき初期値を必ず指定する必要がある)。
  • %TYPE キーワードを使って事前に定義された変数の型を参照できる。
  • 代入演算子 (:=) または DEFAULT というキーワードでデフォルト値を指定できる。

例を示します。

DECLARE
  str1 VARCHAR2(32);
  str2 VARCHAR2(32) := 'Hello, world!';
  answer CONSTANT NUMBER(2) := 42;
  num1 NUMBER(4) NOT NULL := 100;
  num2 num1%TYPE := 200;

  PROCEDURE foo(str VARCHAR2 := 'foo') IS
  BEGIN
    dbms_output.put_line('str: ' || str);
  END;

BEGIN
  -- num1 := NULL;  -- NOT NULL の変数に NULL を代入しようとした場合はエラーとなる
  -- num2 := NULL;  -- %TYPE で定義した変数には NOT NULL 制約も継承される
  foo();
  foo(str1);
  foo(str2);
END;

実行結果:

str: foo
str: 
str: Hello, world!

※特に説明していない PROCEDURE を使っていますが、ここでは説明を省きます。詳細については後述します。

プログラム制御構文

IF THEN ELSIF ELSE 文

IF THEN ELSIF ELSE 文の構文は次の通りです。

IF condition_1 THEN
  statements_1
ELSIF condition_2 THEN
  statements_2
[ ELSIF condition_3 THEN
    statements_3
]...
[ ELSE
    else_statements
]
END IF;

例を示します。

DECLARE
  FUNCTION isNull(num PLS_INTEGER) RETURN BOOLEAN IS
  BEGIN
    dbms_output.put_line('isNull: num: ' || num);
    RETURN num IS NULL;
  END;

  PROCEDURE foo(num PLS_INTEGER) IS
  BEGIN
    IF num = 0 THEN
      dbms_output.put_line('foo: num は 0 です');
    ELSIF 1 <= num AND num < 5 OR isNull(num) THEN
      dbms_output.put_line('foo: num は 1 以上 5 未満, または NULL です');
    ELSE
      dbms_output.put_line('foo: num は 5 以上です');
    END IF;
  END;

BEGIN
  foo(0);
  foo(1);
  foo(5);
END;

実行結果:

foo: num は 0 です
foo: num は 1 以上 5 未満, または NULL です
isNull: num: 5
foo: num は 5 以上です

IF 文に限りませんが、条件式は TRUE, または FALSE になるのが確定するまで評価され、それ以降は評価されません。 上記の例では、num に 5 を指定したときに isNull() が呼び出されていますが、1 を指定した場合には呼び出されません。

CASE 文

SQL の CASE 文と同様ですが、構文を掲載しておきます。 SQL と同様に、WHEN 句に値を指定する「単純な CASE 文」と、WHEN 句に条件式を指定する「検索 CASE 文」を記述できます。

単純な CASE 文:

CASE selector
WHEN selector_value_1 THEN statements_1
WHEN selector_value_2 THEN statements_2
...
WHEN selector_value_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;

検索 CASE 文:

CASE
WHEN condition_1 THEN statements_1
WHEN condition_2 THEN statements_2
...
WHEN condition_n THEN statements_n
[ ELSE
  else_statements ]
END CASE;

LOOP 文

LOOP 文にはいくつかのバリエーションが存在します。それぞれの構文を示します。

基本 LOOP 文:

[ <<label>> ]
LOOP
  statements
END LOOP [ <<label>> ];

FOR LOOP 文:

[ <<label>> ]
FOR index IN [ REVERSE ] lower_bound..upper_bound LOOP
  statements
END LOOP [ <<label>> ];

WHILE LOOP 文:

[ <<label>> ]
WHILE condition LOOP
  statements
END LOOP [ <<label>> ];

これらの LOOP 文中では (「statements」の部分では)、EXIT, CONTINUE 文を使用して LOOP を脱出したり LOOP の先頭に戻ったりすることができます。 このとき、ラベルを指定することにより指定したラベルの LOOP に対して EXIT, CONTINUE できます。 また、「WHEN 〜」を指定することにより、指定の条件をみたす場合にのみ EXIT, CONTINUE できます。

EXIT [ label ] [ WHEN boolean_expression ] ;
CONTINUE [ label ] [ WHEN boolean_expression ] ;

「基本 LOOP 文」では、「statements」において EXIT 文を使って LOOP を抜けるようにしておく必要があります (構文的に終了・継続条件を指定できないため無限ループになってしまう)。 次に、LOOP の使用例を示します。指定の数値より大きい素数を (素朴な実装で算出して) 指定の回数表示するプログラムです。

DECLARE
  PROCEDURE printPrimeNumbers(startNum PLS_INTEGER, printLimit PLS_INTEGER) IS
    i PLS_INTEGER := 0;
    num PLS_INTEGER := startNum;
    cntText VARCHAR2(32);
  BEGIN
    <<outerloop>>   -- 「<<>>」で囲んでラベル名を指定する
    WHILE i < printLimit LOOP
      num := num + 1;

      <<innerloop>>
      FOR i IN 2 .. num - 1 LOOP
        CONTINUE outerloop WHEN mod(num, innerloop.i) = 0;  -- 「label_name.」を付与することで、どのラベルのループに属する変数であるか明示的に指定できる
      END LOOP;

      CASE printPrimeNumbers.i  -- 「procedure_name.」も指定可能
        WHEN 0 THEN cntText := '最初';
        ELSE cntText := ' ' || (i + 1) || ' 番目';
      END CASE;
      dbms_output.put_line(startnum || ' より大きい' || cnttext || 'の素数: ' || num);
      i := i + 1;

    END LOOP;
    dbms_output.put_line('');
  END;

BEGIN
  printPrimeNumbers(10, 3);
  printprimenumbers(100, 3);
  printPrimeNumbers(1000, 3);
END;

実行結果:

10 より大きい最初の素数: 11
10 より大きい 2 番目の素数: 13
10 より大きい 3 番目の素数: 17

100 より大きい最初の素数: 101
100 より大きい 2 番目の素数: 103
100 より大きい 3 番目の素数: 107

1000 より大きい最初の素数: 1009
1000 より大きい 2 番目の素数: 1013
1000 より大きい 3 番目の素数: 1019

データ型 (スカラデータ型)

PL/SQL では SQL (Oracle) で使用可能な全ての型と、PL/SQL 独自の型を使用できます。 使用可能な型は次の通りです。

  • SQLデータ型
  • BOOLEAN: 論理値 (TRUE, FALSE, および NULL) を格納する。
  • PLS_INTEGER (BINARY_INTEGER): 32 ビットで表される -2147483648 から 2147483647 の範囲の符号付き整数を格納する。SQL データ型の NUMBER と比べて必要な記憶域が少なく済み、高速に演算される。PLS_INTEGER を使用可能な箇所ではこちらを使用すべき。
  • REF CURSOR
  • ユーザー定義のサブタイプ

なお、PLS_INTEGER には次のサブタイプが事前定義されています。

データ型 データの説明
NATURAL 負でない PLS_INTEGER 値。
NATURALN NOT NULL 制約が指定された負でない PLS_INTEGER 値。
POSITIVE 正の PLS_INTEGER 値。
POSITIVEN NOT NULL 制約が指定された正の PLS_INTEGER 値。
SIGNTYPE -1, 0 または 1 の PLS_INTEGER 値。
SIMPLE_INTEGER NOT NULL 制約が指定された PLS_INTEGER 値。

ユーザ定義のサブタイプ

次のような構文を使ってユーザ定義のサブタイプを定義できます。

SUBTYPE subtype_name IS base_type { precision [, scale ] | RANGE low_value .. high_value } [ NOT NULL ]

RANGE を指定可能なのは PLS_INTEGER およびそのサブタイプのみです。

サブタイプの定義ではベースタイプに対して制約を指定できますが、制約無しでサブタイプを定義することも可能です。その場合、サブタイプはベースタイプの別名に過ぎません (ベースタイプと互換性を持ち、データ型の変換も発生しない)。

次に使用例を示します。

DECLARE
  SUBTYPE Id IS CHAR(8);
  SUBTYPE Percentage IS NUMBER(5, 2) NOT NULL;

  SUBTYPE Digit        IS PLS_INTEGER RANGE  0 ..  9;
  SUBTYPE Double_Digit IS PLS_INTEGER RANGE 10 .. 99;

  i Id := 'foo'; -- 'foo     ' が設定される
  p Percentage := 99.99;

  d Digit;
  dd Double_digit;

BEGIN
  d := 8;
  dd := 42;
  d := dd; -- エラーが発生する
END;

コンポジットデータ型

PL/SQL では、コレクション、およびレコードという 2 種類のコンポジットデータ型を定義できます。 コンポジットデータ型には、スカラデータ型、およびコンポジットデータ型を格納できます。

コレクションには次の 3 つの種類があります。

コレクション 構文
連想配列 (索引付き表) TABLE OF datatype [NOT NULL] INDEX BY keytype
VARRAY (可変サイズの配列) VARRAY(size) OF datatype [NOT NULL]
ネストした表 TABLE OF datatype [NOT NULL]

連想配列

連想配列は、キーで値を関連付けるコレクションです。PL/SQL では「PL/SQL 表」、または「索引付き表」とも呼ばれていたようです。 キーには、文字列型、および PLS_INTEGER 型を使用できます。一般的な連想配列と異なり、PL/SQL の連想配列はキーのソート順で格納されます。文字列型のソート順は、NLS パラメータの NLS_SORT, および NLS_COMP で決まります。 また、連想配列は他のコレクションと異なり初期化の必要がありません (初期化しなかった場合に NULL にならない) が、コンストラクタを使用できません。 定義構文は次の通りです。

TABLE OF datatype [NOT NULL] INDEX BY keytype

使用例を示します。

DECLARE
  TYPE map IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(64);   -- VARCHAR2(64) で PLS_INTEGER をマッピングする連想配列型の宣言
  name2value map;   -- 初期化の必要なし
  key VARCHAR2(64);

BEGIN
  name2value('foo') := 100;
  name2value('bar') := 200;
  name2value('baz') := 300;

  dbms_output.put_line('name2value entries:');
  key := name2value.FIRST;
  WHILE key IS NOT NULL LOOP
    dbms_output.put_line(key || ': ' || name2value(key));
    key := name2value.NEXT(key);
  END LOOP;

  name2value.DELETE('bar');

  dbms_output.put_line('');
  dbms_output.put_line('name2value entries:');
  key := name2value.LAST;
  WHILE key IS NOT NULL LOOP
    dbms_output.put_line(key || ': ' || name2value(key));
    key := name2value.PRIOR(key);
  END LOOP;

END;

実行結果:

name2value entries:
bar: 200
baz: 300
foo: 100

name2value entries:
foo: 100
baz: 300

VARRAY (可変サイズの配列)

VARRAY は可変サイズの配列です。宣言時に最大サイズを指定しますので、そのサイズを超えて要素を追加することはできません。 VARRAY では、配列のサイズを増減させることはできます (要素を末尾に追加・削除できます) が「中間の」要素を削除することはできません (歯抜けの状態にはできない)。 初期化しなかった場合、VARRAY 変数は NULL となります。初期化は、VARRAY 型名を使ったコンストラクタによって行います。このとき、コンストラクタの引数に初期値を指定することができます。 要素を追加する際は、EXTEND メソッドを使って事前にサイズを拡張させておく必要があります (最大サイズまで拡張させることができる)。 また、添字は 1 オリジンです。定義構文は次の通りです。

VARRAY(size) OF datatype [NOT NULL]

使用例を示します。

DECLARE
  TYPE list IS VARRAY(16) OF VARCHAR2(32);
  names list := list(); -- 空の VARRAY として初期化 (初期化しない場合は NULL となる)

  PROCEDURE print_list(ls list) IS
  BEGIN
    dbms_output.put_line('');
    dbms_output.put_line('list count: ' || ls.COUNT);
    FOR i IN 1 .. ls.COUNT
    LOOP
      dbms_output.put_line('name(' || i || '): ' || ls(i));
    END LOOP;
  END;

BEGIN
  names.EXTEND(1);  -- VARRAY を 1 つ拡張
  names(1) := 'foo';
  names.EXTEND(2);  -- VARRAY を 2 つ拡張
  names(2) := 'bar';
  names(3) := 'baz';
  print_list(names);

  names.DELETE; -- 要素を全て削除
  print_list(names);

  names := list('hoge', 'huga');    -- 初期値を指定して初期化
  print_list(names);

END;

実行結果:

list count: 3
name(1): foo
name(2): bar
name(3): baz

list count: 0

list count: 2
name(1): hoge
name(2): huga

ネストした表

VARRAY と同じく、ネストした表も可変サイズの配列です。ただし、宣言時に最大サイズを指定する必要はありません。 また、VARRAY とは異なり、配列の任意の要素を削除することができます (添字を指定して DELETE することができる)。 その他の特性は VARRAY と同じです。

定義構文は次の通りです。

TABLE OF datatype [NOT NULL]

使用例を示します。

DECLARE
  TYPE nested_table IS TABLE OF VARCHAR2(32);   -- サイズの指定は不要
  names nested_table := nested_table(); -- 空のネストした表として初期化 (初期化しない場合は NULL となる)

  PROCEDURE print_list(ls nested_table) IS
  BEGIN
    dbms_output.put_line('');
    dbms_output.put_line('list count: ' || ls.COUNT);
    FOR i IN ls.FIRST .. ls.LAST loop
      IF ls.EXISTS(i) THEN
        dbms_output.put_line('name(' || i || '): ' || ls(i));
      END IF;
    END LOOP;
  END;

BEGIN
  names.EXTEND(1);  -- ネストした表を 1 つ拡張
  names(1) := 'foo';
  names.EXTEND(2);  -- ネストした表を 2 つ拡張
  names(2) := 'bar';
  names(3) := 'baz';
  print_list(names);

  names.DELETE(2);  -- 任意の要素を削除できる (歯抜けの状態にできる)
  print_list(names);

  names := nested_table('hoge', 'huga');    -- 初期値を指定して初期化
  print_list(names);

END;

実行結果:

list count: 3
name(1): foo
name(2): bar
name(3): baz

list count: 2
name(1): foo
name(3): baz

list count: 2
name(1): hoge
name(2): huga

多次元コレクション

コレクションは 1 次元のみですが、コレクションを要素に持つコレクションを使用して、多次元コレクションを実現できます。

DECLARE
  TYPE nt IS TABLE OF VARCHAR2(32);
  ns nt := nt('foo', 'bar');

  TYPE nt2 IS TABLE OF nt;
  nss nt2 := nt2(ns, nt('baz', 'qux'));

BEGIN
  FOR i IN nss.FIRST .. nss.LAST loop
    FOR j IN nss(i).FIRST .. nss(i).LAST LOOP
      dbms_output.put_line('nss(' || i || ', ' || j || '): ' || nss(i)(j));
    END LOOP;
  END LOOP;
END;

実行結果:

nss(1, 1): foo
nss(1, 2): bar
nss(2, 1): baz
nss(2, 2): qux

コレクションメソッド

メソッド 説明
DELETE コレクションから要素を削除します。
引数無しで呼び出した場合は全ての要素を削除します。
連想配列・ネストした表に対しては引数にインデックスを指定できます。
DELETE(n) は、インデックス n でアクセスできる要素を削除します。
DELETE(m, n) は、m <= n の場合に m でアクセスできる要素から n でアクセスできる要素までを削除します。m > n の場合には何もしません。
TRIM VARRAY・ネストした表の末尾から要素を削除します。
引数無しで呼び出した場合は末尾の要素を 1 つ削除します。TRIM(n) は、末尾の n 個の要素を削除します。
EXTEND VARRAY・ネストした表の末尾に要素を追加します。
引数無しで呼び出した場合は末尾に NULL 要素を 1 つ追加します。EXTEND(n) は末尾に n 個の NULL 要素を追加します。EXTEND(n, i) は i でアクセスできる要素を末尾に n 個追加します。
EXISTS VARRAY・ネストした表の指定された要素が存在する場合に TRUE を返します。
FIRST コレクションの最初の索引を返します。
LAST コレクションの最後の索引を返します。
COUNT コレクション内の要素の数を返します。
LIMIT VARRAY に格納できる要素の最大数を返します。連想配列・ネストした表に対して呼び出した場合には NULL が返されます。
PRIOR 指定された索引の前の索引を返します。
NEXT 指定された索引の後の索引を返します。

レコード

PL/SQL では、データベース表の「レコード」のような型、およびその変数を取り扱うことができます。 次の構文でレコード型を定義できます。

record_type_definition ::=
TYPE record_type IS RECORD (field_definition[, field_definition]...);

field_definition ::=
field datatype [[NOT NULL] { := | DEFAULT } expression]

レコード型は、連想配列と同じくコンストラクタによる初期化ができません。このため、NOT NULL を指定した場合は必ずデフォルト値を指定する必要があります。 例えば次のように定義します。

TYPE Employee IS RECORD (
  no PLS_INTEGER NOT NULL := -1,
  name VARCHAR2(16) NOT NULL := 'anonymous',
  age PLS_INTEGER
);

emp1 Employee;

レコードは同じ型のレコードに代入することができます。このとき、レコードの各フィールド値がコピーされます。 レコードに NULL を代入すると、レコードの各フィールドに NULL が設定されます (フィールドがレコード型であるような場合は、再帰的に NULL が代入されます)。

レコード型の使用例を示します。

DECLARE
  TYPE Employee IS RECORD (
    no PLS_INTEGER NOT NULL := -1,
    name VARCHAR2(16) NOT NULL := 'anonymous',
    age PLS_INTEGER
  );

  emp1 Employee;
  emp2 emp1%TYPE := emp1;

  PROCEDURE print_emp(emp Employee) IS
  BEGIN
    dbms_output.put_line('emp#: ' || emp.no || ', name: ' || emp.name || ', age: ' || emp.age);
  END;

BEGIN
  emp1.no := 1;
  emp1.name := 'Eiichiro';
  emp1.age := 35;
  print_emp(emp1);
  print_emp(emp2);

  emp2 := emp1;
  emp2.no := 2;
  print_emp(emp1);
  print_emp(emp2);

  emp2 := NULL;
  print_emp(emp2);

END;

実行結果:

emp#: 1, name: Eiichiro, age: 35
emp#: -1, name: anonymous, age: 
emp#: 1, name: Eiichiro, age: 35
emp#: 2, name: Eiichiro, age: 35
emp#: , name: , age: 

なお、レコードが NULL かどうか・等しいかどうかはそのままテストできません。次の BOOLEAN 式はエラーとなります。

emp1 IS NULL
emp1 = emp2
emp1 > emp2

%ROWTYPE

%ROWTYPE というキーワードを使って、データベースの表・ビュー内の行全体または一部を表すレコードを宣言できます。 このレコードは、表・ビュー内の列と同じ名前・データ型のフィールドを持ちます。表・ビューの構造が変更されると、レコードの構造もそれに追随します。 ただし、このレコードのフィールドは対応する列の制約・初期値を継承しません。次の構文によってレコードを定義できます。

variable_name table_or_view_name%ROWTYPE;

また、%TYPE の場合と同様に、変数 (カーソル) に対して %ROWTYPE を使用することができます (カーソルについては後述します)。

variable_name cursor_name%ROWTYPE;

%ROWTYPE についての使用例を示します。

まず、次の通り department 表、および employee 表が構築されているものとします。

CREATE TABLE department (
  dept_no CHAR(4) NOT NULL,
  dept_name VARCHAR(16) NOT NULL,
  CONSTRAINT department_pk PRIMARY KEY (dept_no)
);

CREATE TABLE employee (
  dept_no CHAR(4) NOT NULL,
  emp_no CHAR(8) NOT NULL,
  emp_name VARCHAR(16) NOT NULL,
  age NUMBER(3, 0) DEFAULT 30 NOT NULL,
  CONSTRAINT employee_pk PRIMARY KEY (dept_no, emp_no),
  CONSTRAINT employee_fk FOREIGN KEY (dept_no) REFERENCES department (dept_no) ON DELETE CASCADE
);

INSERT ALL
INTO department VALUES('0001', 'Admin')
INTO department VALUES('0002', 'Devel')
SELECT * FROM dual;


INSERT ALL
INTO employee VALUES('0001', '00000001', 'Tanaka', 30)
INTO employee VALUES('0002', '00000002', 'Suzuki', 31)
INTO employee VALUES('0002', '00000003', 'Sato', 28)
SELECT * FROM dual;

これらの表に対する %ROWTYPE の使用例です:

DECLARE
  emp1 Employee%ROWTYPE;
  emp2 Employee%ROWTYPE;

  CURSOR c1 IS SELECT d.dept_name, e.emp_name, e.age FROM employee e INNER JOIN department d ON e.dept_no = d.dept_no;
  emp c1%ROWTYPE;

  PROCEDURE print_emp(emp Employee%ROWTYPE) IS
  BEGIN
    dbms_output.put_line('dept#: ' || emp.dept_no || ', emp#: ' || emp.emp_no || ', name: ' || emp.emp_name || ', age: ' || emp.age);
  END;

  PROCEDURE print_emp2(emp c1%ROWTYPE) IS
  BEGIN
    dbms_output.put_line('dept name: ' || emp.dept_name || ', name: ' || emp.emp_name || ', age: ' || emp.age);
  END;

BEGIN
  emp1.dept_no := '0002';
  emp1.emp_no := '00000004';
  emp1.emp_name := 'Eiichiro';
  emp1.age := 35;
  print_emp(emp1);
  print_emp(emp2);

  emp2 := emp1;
  print_emp(emp2);

  OPEN c1;
  LOOP
    FETCH c1 INTO emp;
    EXIT WHEN c1%NOTFOUND;
    print_emp2(emp);
  END LOOP;
END;

実行結果:

dept#: 0002, emp#: 00000004, name: Eiichiro, age: 35
dept#: , emp#: , name: , age: 
dept#: 0002, emp#: 00000004, name: Eiichiro, age: 35
dept name: Admin, name: Tanaka, age: 30
dept name: Devel, name: Sato, age: 28
dept name: Devel, name: Suzuki, age: 31

レコードを使った INSERT, UPDATE

レコードを直接指定して INSERT, UPDATE 文を発行することができます。使用例は次の通りです。

DECLARE
  emp1 Employee%ROWTYPE;
  emp2 Employee%ROWTYPE;

  PROCEDURE print_emp(emp Employee%ROWTYPE) IS
  BEGIN
    dbms_output.put_line('dept#: ' || emp.dept_no || ', emp#: ' || emp.emp_no || ', name: ' || emp.emp_name || ', age: ' || emp.age);
  END;

BEGIN
  emp1.dept_no := '0002';
  emp1.emp_no := '00000004';
  emp1.emp_name := 'Eiichiro';
  emp1.age := 35;
  print_emp(emp1);

  INSERT INTO Employee VALUES emp1;
  SELECT * INTO emp2 FROM Employee WHERE emp_no = emp1.emp_no;
  print_emp(emp2);

  emp1.emp_name := 'Eijiro';
  emp1.age := 33;

  UPDATE Employee SET ROW = emp1 WHERE emp_no = emp1.emp_no;
  SELECT * INTO emp2 FROM Employee WHERE emp_no = emp1.emp_no;
  print_emp(emp2);
END;

実行結果:

dept#: 0002, emp#: 00000004, name: Eiichiro, age: 35
dept#: 0002, emp#: 00000004, name: Eiichiro, age: 35
dept#: 0002, emp#: 00000004, name: Eijiro, age: 33

INSERT 文では、VALUES 句にレコードを指定できます。

UPDATE 文では、ROW というキーワードを使用してレコードを指定できます。

カーソル

PL/SQL において、SQL に関する処理には必ずカーソルが使用されます。

カーソルとは、

  • SELECT 文を使った問い合わせ
  • DML (Data Manipulation Language) 文を使ったデータ操作 (INSERT, UPDATE, DELETE, MERGE)

に関する情報を保持する SQL のプライベート領域を指すポインタです。

SELECT 文において、ユーザが明示的に定義したカーソル (明示カーソル) が指定された場合はそれが使用されます。 SELECT 文で明示カーソルが指定されなかった場合、および DML 文においては、Oracle データベースによって暗黙的に構築され管理されるカーソル (暗黙カーソル) が使用されます。

次の PL/SQL 文では暗黙カーソルが使用されます:

  • SELECT INTO 文
  • 暗黙カーソル FOR LOOP 文

次の PL/SQL 文では明示カーソルが使用されます:

  • 明示カーソル FOR LOOP 文 (ユーザーが明示カーソルを定義しますが、文の実行中は PL/SQL が管理します)
  • OPEN, FETCH および CLOSE 文 (ユーザーが明示カーソルを定義および管理します)

上記のバリエーションについて、例を示します。前述の department 表、および employee 表に対して明示カーソル・暗黙カーソルを使って問い合わせをしてみます。

DECLARE

  -- 1.1. SELECT INTO 文 (SELECT column INTO)
  PROCEDURE printEmpName(empNo CHAR) IS
    vEmpName employee.emp_name%TYPE;
    vAge employee.age%TYPE;
  BEGIN
    SELECT emp_name, age INTO vEmpName, vAge FROM employee WHERE emp_no = empNo;    -- INTO 句に複数の値を指定して一度に代入できる。
    dbms_output.put_line(' ');
    dbms_output.put_line('1.1. emp_no が ' || empNo || ' の従業員: ' || vEmpName || ' (' || vAge || ')');
  END;

  -- 1.2. SELECT INTO 文 (SELECT * INTO)
  PROCEDURE printEmp(empno CHAR) IS
    emp employee%ROWTYPE;
  BEGIN
    SELECT * INTO emp FROM employee WHERE emp_no = empNo;       -- 「SELECT *」の場合は「%ROWTYPE」の変数に代入できる。
    dbms_output.put_line(' ');
    dbms_output.put_line('1.2. emp_no が ' || empNo || ' の従業員: ' || emp.emp_name || ' (' || emp.age || ', ' || emp.emp_no || ')');
  END;

  -- 2. 暗黙カーソル FOR ループ文
  PROCEDURE printEmps2(empNo CHAR) IS
  BEGIN
    dbms_output.put_line(' ');
    dbms_output.put_line('2. emp_no が ' || empNo || ' より大きい従業員:');
    FOR emp IN (SELECT emp_no, emp_name FROM employee WHERE emp_no > empNo) LOOP    -- 暗黙カーソルを使った FOR ループ
      dbms_output.put_line(emp.emp_name || ' (' || emp.emp_no || ')');
    END LOOP;
  END;

  -- 3. 明示カーソル FOR ループ文
  PROCEDURE printEmps3(aAge PLS_INTEGER) IS
    CURSOR empCursor IS SELECT emp_name, age FROM employee WHERE age >= aAge;   -- 明示カーソル
  BEGIN
    dbms_output.put_line(' ');
    dbms_output.put_line('3. ' || aAge || ' 歳以上の従業員:');
    FOR emp IN empCursor LOOP   -- 明示カーソルを使った FOR ループ
      dbms_output.put_line(emp.emp_name || ' (' || emp.age || ')');
    END LOOP;
  END;

  -- 4. OPEN, FETCH, および CLOSE 文
  PROCEDURE printEmps4(deptNo CHAR) IS
    deptName department.dept_name%TYPE;
    CURSOR empCursor(no CHAR) IS SELECT emp_name, age, emp_no FROM employee WHERE dept_no = no; -- 引数を取る明示カーソル
    vEmpName employee.emp_name%TYPE;
    vAge employee.age%TYPE;
    vEmpNo employee.emp_no%TYPE;
  BEGIN
    SELECT dept_name INTO deptName FROM department WHERE dept_no = deptNo;
    dbms_output.put_line(' ');
    dbms_output.put_line('4. ' || deptName || ' 部の従業員:');
    OPEN empCursor(deptNo); -- 引数を指定して明示カーソルを OPEN
    LOOP
      FETCH empCursor INTO vEmpName, vAge, vEmpNo;  -- SELECT INTO の場合と同様に「SELECT *」の場合は「%ROWTYPE」の変数に代入できる
      EXIT WHEN empCursor%NOTFOUND; -- カーソル属性の値によって LOOP を続行するか判定する
      dbms_output.put_line(vEmpName || ' (' || vAge || ', ' || vEmpNo || ')');
    END LOOP;
    CLOSE empCursor;    -- 明示カーソルを CLOSE
  END;

BEGIN
  printEmpName('00000001');
  printEmp('00000001');
  printEmps2('00000001');
  printEmps3(30);
  printEmps4('0002');
END;

実行結果:

1.1. emp_no が 00000001 の従業員: Tanaka (30)

1.2. emp_no が 00000001 の従業員: Tanaka (30, 00000001)

2. emp_no が 00000001 より大きい従業員:
Suzuki (00000002)
Sato (00000003)

3. 30 歳以上の従業員:
Tanaka (30)
Suzuki (31)

4. Devel 部の従業員:
Suzuki (31, 00000002)
Sato (28, 00000003)

カーソル属性

上記の「4. OPEN, FETCH, および CLOSE 文」の例では「%NOTFOUND」というカーソル属性によってループを続行するかどうか判定していました。 カーソルには、その状態を表すいくつかの属性があります。明示カーソルの場合は「カーソル名%属性」という構文でカーソルの属性にアクセスできます。暗黙カーソルの場合は「SQL%属性」という構文を使用します。

カーソルの属性は次の通りです。

属性名 説明
%ISOPEN属性 カーソルがオープンしている場合は真
%FOUND属性 影響を受けた行が (フェッチされた行が) あった場合は真
%NOTFOUND属性 どの行も影響を受けなかった (フェッチされた行がなかった) 場合は真
%ROWCOUNT属性 影響を受けた (フェッチされた) 行数

カーソル変数

カーソルを設定可能な変数です。この変数の型には事前定義の「SYS_REFCURSOR 型」か、または定義した「REF CURSOR 型」を使用できます。

REF CURSOR 型の定義構文は次の通り。

TYPE type_name IS REF CURSOR [ RETURN return_type ]

「RETURN 〜」でカーソルが返す値の型を指定した場合「強い型」のカーソル変数を定義できます。指定しなかった場合は「弱い型」となります。

カーソル変数に設定するカーソルを条件分岐によって切り替えて処理する例を示します。

DECLARE
  PROCEDURE printEmployee(empNo CHAR DEFAULT NULL) IS
    TYPE empCursorType IS REF CURSOR;
    empCursor empCursorType;
    emp employee%ROWTYPE;
    sqlText VARCHAR(256);

  BEGIN
    dbms_output.put_line(' ');

    CASE
      WHEN empNo IS NULL THEN
        dbms_output.put_line('all employees:');
        OPEN empCursor FOR SELECT * FROM employee;

      WHEN empNo IS NOT NULL THEN
        dbms_output.put_line('emp#' || empNo || ':');
        sqlText := 'SELECT * FROM employee WHERE emp_no = :empNo';
        OPEN empCursor FOR sqlText USING empNo;

    END CASE;

    LOOP
      FETCH empCursor INTO emp;
      EXIT WHEN empCursor%NOTFOUND;
      dbms_output.put_line('  ' || emp.emp_name || ' (' || emp.age || ')');
    END LOOP;

    CLOSE empCursor;
  END;
BEGIN
  printEmployee();
  printEmployee('00000001');
END;

実行結果:

all employees:
  Tanaka (30)
  Suzuki (31)
  Sato (28)

emp#00000001:
  Tanaka (30)

CURSOR 式

「CURSOR 式」を使って、ネストしたカーソルを使用できます。構文は次の通りです。

CURSOR (subquery)

CURSOR 式を使った例を次に示します。次のプログラムでは部署ごとに所属する従業員を出力します。

DECLARE
  PROCEDURE printDepartment IS
    CURSOR deptCursor IS
      SELECT
        dept_name,
        CURSOR(SELECT * FROM employee e WHERE e.dept_no = d.dept_no)    -- CURSOR 式
      FROM department d;

    deptName department.dept_name%TYPE;

    TYPE empCursorType IS REF CURSOR RETURN employee%ROWTYPE;
    empCursor empCursorType;
    emp employee%ROWTYPE;

  BEGIN
    OPEN deptCursor;
    LOOP
      FETCH deptCursor INTO deptName, empCursor;
      EXIT WHEN deptCursor%NOTFOUND;
      dbms_output.put_line(' ');
      dbms_output.put_line(deptName || ' の従業員:');
      LOOP
        FETCH empCursor INTO emp;   -- CURSOR 式のカーソルをフェッチ。ここでは OPEN, CLOSE は必要ない
        EXIT WHEN empCursor%NOTFOUND;
        dbms_output.put_line('  ' || emp.emp_name || ' (' || emp.age || ')');
      END LOOP;
    END LOOP;
    CLOSE deptCursor;
  END;
BEGIN
  printDepartment();
END;

実行結果:

Admin の従業員:
  Tanaka (30)

Devel の従業員:
  Suzuki (31)
  Sato (28)

EXECUTE IMMEDIATE文

例外

PL/SQL では全てのブロックに例外処理部を記述可能です。例外処理部は、例えば次のように記述できます。

EXCEPTION
  WHEN exception1 THEN statements...
  WHEN exception2 OR exception3 THEN statements...
  WHEN OTHERS THEN statements...
END;

例外には次の 3 つの種類があります。

  • 事前定義の例外
  • 内部的に定義された例外
  • ユーザー定義の例外

事前定義の例外

PL/SQL において事前定義された例外です。 Oracle のドキュメントに 事前定義された例外の一覧 が掲載されています。

内部的に定義された例外

Oracle データベースにおいて内部的に番号が振られている例外です。例えば、

  • ORA-00001: 一意制約(string.string)に反しています
  • ORA-00060: リソース待機の間にデッドロックが検出されました。

などです。事前定義の例外は約 20 種類しかありませんが、実際には上記の番号の数だけ例外があります (これの完全なリストは、Oracle® Databaseエラー・メッセージ を参照して下さい)。 PL/SQL ではこれらに「名前」が付けられていませんので、これらの例外を処理したければ後述する EXCEPTION_INIT プラグマを使って番号とユーザ定義の例外を関連付ける必要があります。

ユーザー定義の例外

ユーザが独自に定義する例外です。次の構文で定義できます。

exception_name EXCEPTION;

ユーザ定義の例外を発生させるには RAISE 文を使います。構文は次の通りです。

RAISE [ exception_name ];

例外名を省略できるのは例外処理部で例外処理している場合に限ります (この場合、RAISE 文は処理中の例外を再度発生させます)。

RAISE 文で例外を発生させることはできますが、例外にメッセージを与えることはできません。 (DBMS_STANDARD パッケージで定義されている) RAISE_APPLICATION_ERROR プロシージャを (エラーコードを指定して) 使用することにより、メッセージを与えることができます。

このプロシージャを使用するには、EXCEPTION_INIT プラグマを使用して事前に例外とエラーコードを関連付けておく必要があります。

それぞれの構文は次の通りです。

RAISE_APPLICATION_ERROR (error_code, message[, {TRUE | FALSE}]);
PRAGMA EXCEPTION_INIT(exception_name, error_code)

このとき、ユーザ定義の例外として使用できるエラーコードは -20,000 から -20,999 までの範囲となります。また、エラーメッセージは 2,048 バイト以内で指定する必要があります。

例外処理例

DECLARE
  exception1 EXCEPTION;
  exception2 EXCEPTION;
  PRAGMA EXCEPTION_INIT(exception2, -20001);    -- RAISE_APPLICATION_ERROR で発生させた例外を処理するために、事前にエラーコードと例外を関連付けておく


  PROCEDURE ex_1_2 IS
  BEGIN
    RAISE exception1;   -- エラーコード・エラーメッセージ指定なしで例外を発生させる
  END;

  PROCEDURE ex_1 IS
  BEGIN
    ex_1_2;
  END;


  PROCEDURE ex_2_2 IS
  BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'exception2 が発生しました!'); -- 事前に関連付けたエラーコードと指定のメッセージで例外を発生させる
  END;

  PROCEDURE ex_2 IS
  BEGIN
    ex_2_2;
  EXCEPTION
    WHEN exception2 THEN
      dbms_output.put_line('ex_2 で exception2 を処理しています code: ' || SQLCODE || ', message: ' || SQLERRM);
      RAISE;
  END;


  PROCEDURE ex_3_2 IS
  BEGIN
    RAISE_APPLICATION_ERROR(-20002, '未定義の例外が発生しました!');  -- 事前にエラーコードを関連付けていない場合は「OTHERS」でしか処理されない
  END;

  PROCEDURE ex_3 IS
  BEGIN
    ex_3_2;
  END;


  PROCEDURE main(nbr PLS_INTEGER) IS
  BEGIN
    CASE nbr
    WHEN 1 THEN ex_1;
    WHEN 2 THEN ex_2;
    WHEN 3 THEN ex_3;
    END CASE;

  EXCEPTION
    WHEN exception1 THEN
      dbms_output.put_line('exception1 を処理しています code: ' || SQLCODE || ', message: ' || SQLERRM);

    WHEN exception2 THEN
      dbms_output.put_line('main で exception2 を処理しています code: ' || SQLCODE || ', message: ' || SQLERRM);

    WHEN OTHERS THEN
      dbms_output.put_line('その他の例外を処理しています code: ' || SQLCODE || ', message: ' || SQLERRM);

      -- 例外処理では、DBMS_UTILITY パッケージの次の 3 つのファンクションが使用できる
      dbms_output.put_line('call stack:');
      dbms_output.put_line(DBMS_UTILITY.FORMAT_CALL_STACK);
      dbms_output.put_line('error stack:');
      dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);
      dbms_output.put_line('error backtrace:');
      dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  END;

BEGIN
  main(1);
  main(2);
  main(3);
END;

実行結果:

exception1 を処理しています code: 1, message: User-Defined Exception
ex_2 で exception2 を処理しています code: -20001, message: ORA-20001: exception2 が発生しました!
main で exception2 を処理しています code: -20001, message: ORA-20001: exception2 が発生しました!
その他の例外を処理しています code: -20002, message: ORA-20002: 未定義の例外が発生しました!
call stack:
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x66aa9378        64  anonymous block
0x66aa9378        74  anonymous block

error stack:
ORA-20002: 未定義の例外が発生しました!

error backtrace:
ORA-06512: 行35
ORA-06512: 行40
ORA-06512: 行49

サブプログラム (プロシージャ・ファンクション)

プロシージャ・ファンクションを総称してサプフログラムと呼びます。以下に、サブプログラムに関する用語を整理します。

  • プロシージャ: 値を返さないサブプログラム
  • ファンクション: 値を返すサブプログラム

  • ネストしたサブプログラム: ブロック内に定義した場合

  • パッケージサブプログラム: パッケージ内に定義した場合
  • スタンドアロンサブプログラム: スキーマレベルで作成した場合 (CREATE PROCEDURE, CREATE FUNCTION 文で定義する)
  • ストアドサブプログラム: パッケージサブプログラム・スタンドアロンサブプログラムの総称

ここまでのプログラム例でプロシージャ・ファンクションの使用例を何度も掲載してきましたが、改めて構文を掲載します。

プロシージャ定義の構文:

PROCEDURE procedure [ (parameter_declaration [, parameter_declaration ]...) ] { IS | AS }
  { ... }

ファンクション定義の構文:

FUNCTION function [ (parameter_declaration [, parameter_declaration ]...) ]
  RETURN datatype [ DETERMINISTIC
                 | PIPELINED
                 | PARALLEL_ENABLE
                 | RESULT_CACHE [ relies_on_clause ]
                 ]...
  { IS | AS } { ... }

仮パラメータ宣言の構文:

parameter_declaration ::= parameter [ 
    [ IN ] datatype [ { := | DEFAULT } expression ] | 
    { OUT | IN OUT } [ NOCOPY ] datatype 
    ]

サブプログラムは、使用箇所に至るまでに宣言、または定義されている必要があります。最初の使用箇所より後で定義する場合は事前に宣言しておきます。

DECLARE
  PROCEDURE proc1(number1 NUMBER);

  PROCEDURE proc2(number2 NUMBER) IS
  BEGIN
    proc1(number2);
  END;

  PROCEDURE proc1(number1 NUMBER) IS
  BEGIN
  :
  :

また、サブプログラムは仮パラメータの数・型が異なっていれば同じ名前でオーバーロードすることができます。

パラメータの制約

仮パラメータの型には制約を含めることはできません。次の宣言はコンパイルされません。

DECLARE
  PROCEDURE p (n NUMBER(1), v VARCHAR2(1)) IS ...

制約付きサブタイプを使って仮パラメータを宣言することはできますが、この場合も制約は適用されません。

次の例において、2 回目のプロシージャ呼び出しはエラーとなりません。

DECLARE
  SUBTYPE n1 IS NUMBER(1);
  SUBTYPE v1 IS VARCHAR2(1);

  PROCEDURE p (n n1, v v1) IS ...

BEGIN
  foo(1, 'a');
  foo(10, 'ab');
END;

パラメータ・モード

仮パラメータには IN, OUT, IN OUT の 3 種類のパラメータ・モードを指定できます。省略した場合のパラメータ・モードは IN となります。

各パラメータ・モードの特性は次の通りです。

特性/パラメータ・モード IN OUT IN OUT
値/参照渡し 参照渡し 値渡し
ただし、NOCOPY 指定時は参照渡しされる場合がある
値渡し
ただし、NOCOPY 指定時は参照渡しされる場合がある
実パラメータとして指定可能なもの 変数, 定数, リテラル, 式 NOT NULL 指定されていない変数 変数
デフォルト値の指定 不可 不可
仮パラメータに対する代入 不可
その他 - 実パラメータの値に関わらず、仮パラメータは NULL (型のデフォルト値) となる。サブプログラムで仮パラメータに値を代入しなかった場合は、呼び出し元で実パラメータとして指定した変数も NULL (型のデフォルト値) となる。 -

NOCOPY について

OUT, IN OUT モードにおいて NOCOPY を指定した場合についてですが、必ず参照渡しされるわけではなく、参照渡しされるか値渡しされるかは実行時に決定されるそうです。 つまり、サブプログラムを起動するたびに異なる可能性があるため、参照渡しされることを前提にプログラミングしていると痛い目を見る可能性がありそうです。

このあたりについては Oracle 公式のドキュメントにも書かれており (参照渡しパラメータによるサブプログラムのパラメータのエイリアシング)、値渡しとなった場合・参照渡しとなった場合についてどのように実行結果が異なってくるか書かれているのですが、手元の環境で試してみたところ期待した結果にはなりませんでした。

検証に使ったプログラムとその実行結果を示します。

RECORD のネストした表を 5 種類のパラメータモードでプロシージャに渡した場合の動作比較プログラム:

DECLARE
  TYPE rec IS RECORD (str VARCHAR2(8));
  TYPE list IS TABLE OF rec;
--  TYPE list IS VARRAY(8) OF rec;  -- VARRAY の場合も結果は同じだった

  foo list := list(NULL);
  bar list := list(NULL);
  baz list := list(NULL);
  qux list NOT NULL := list(NULL);
  quux list NOT NULL := list(NULL);

  PROCEDURE printList(name VARCHAR2, ls list) IS
    strs VARCHAR2(64);
  BEGIN
    IF ls IS NULL THEN
      dbms_output.put_line(name || ' IS NULL');
      RETURN;
    END IF;
    FOR i IN ls.FIRST .. ls.LAST LOOP
      IF i > 1 THEN
        strs := strs || ', ';
      END IF;
      strs := strs || ls(i).str;
    END LOOP;
    dbms_output.put_line(name || ': ' || strs);
  END;

  PROCEDURE foobar(
    cnt PLS_INTEGER,
    foo2 list,
    bar2 OUT list,
    baz2 OUT NOCOPY list,
    qux2 IN OUT list,
    quux2 IN OUT NOCOPY list
    ) IS
  BEGIN
    dbms_output.put_line(' ');
    dbms_output.put_line('foobar (' || cnt || '):');
    printList('foo', foo2);
    printList('bar', bar2);
    printList('baz', baz2);
    printList('qux', qux2);
    printList('quux', quux2);

    IF cnt = 1 THEN
--      foo2(foo2.LAST).str := 'b'; -- コンパイルできない
      qux2(qux2.LAST).str := 'k';
      quux2(quux2.LAST).str := 'n';
    ELSE
--      foo2 := list(NULL); -- コンパイルできない
      bar2 := list(NULL);
      baz2 := list(NULL);
      qux2 := list(NULL);
      quux2 := list(NULL);
--      foo2(qux2.LAST).str := 'A'; -- コンパイルできない
      bar2(bar2.LAST).str := 'D';
      baz2(baz2.LAST).str := 'G';
      qux2(qux2.LAST).str := 'J';
      quux2(quux2.LAST).str := 'M';
    END IF;
  END;

BEGIN
  foo(foo.LAST).str := 'a';
  bar(bar.LAST).str := 'd';
  baz(baz.LAST).str := 'g';
  qux(qux.LAST).str := 'j';
  quux(quux.LAST).str := 'm';

  dbms_output.put_line(' ');
  dbms_output.put_line('プロシージャコール前:');
  printList('foo', foo);
  printList('bar', bar);
  printList('baz', baz);
  printList('qux', qux);
  printList('quux', quux);

  foobar(1, foo, bar, baz, qux, quux);
  dbms_output.put_line(' ');
  dbms_output.put_line('プロシージャコール後 (1 回目):');
  printList('foo', foo);
  printList('bar', bar);
  printList('baz', baz);
  printList('qux', qux);
  printList('quux', quux);

  foobar(2, foo, bar, baz, qux, quux);
  dbms_output.put_line(' ');
  dbms_output.put_line('プロシージャコール後 (2 回目):');
  printList('foo', foo);
  printList('bar', bar);
  printList('baz', baz);
  printList('qux', qux);
  printList('quux', quux);
END;

実行結果:

プロシージャコール前:
foo: a
bar: d
baz: g
qux: j
quux: m

foobar (1):
foo: a
bar IS NULL
baz IS NULL
qux: j
quux: m

プロシージャコール後 (1 回目):
foo: a
bar IS NULL
baz IS NULL
qux: k
quux: n

foobar (2):
foo: a
bar IS NULL
baz IS NULL
qux: k
quux: n

プロシージャコール後 (2 回目):
foo: a
bar: D
baz: G
qux: J
quux: M

つまり、NOCOPY を指定しようがしまいが参照渡しされたように動作しています。

※試しに、上記 Oracle のドキュメントに掲載されていたプログラムから NOCOPY を 削除して 実行してみたところ「コンパイラがNOCOPYヒントに従う場合」の動作となりました。

というわけで、公式ドキュメントに書かれている通りに動作しないようなシロモノですので、OUT, IN OUT パラメータ・モードについてはとりあえず使用しないのが無難だという結論に至りました。。

ところで、NOCOPY 指定の有無によって例外が発生した場合の動作が異なるのは確認できました。

DECLARE
  foo_error EXCEPTION;

  foo PLS_INTEGER := 20;
  bar PLS_INTEGER := 30;
  baz PLS_INTEGER := 40;
  qux PLS_INTEGER := 50;

  PROCEDURE printParams(message VARCHAR2,
    foop PLS_INTEGER, barp PLS_INTEGER,
    bazp PLS_INTEGER, quxp PLS_INTEGER)
  IS
    fixedLengthMessage CHAR(20) := message;
  BEGIN
    dbms_output.put_line(fixedLengthMessage || ' foo: ' || foop || ', bar: ' || barp || ', baz: ' || bazp || ', qux: ' || quxp);
  END;

  PROCEDURE foobar(cnt PLS_INTEGER,
    foop    OUT PLS_INTEGER, barp    OUT NOCOPY PLS_INTEGER,
    bazp IN OUT PLS_INTEGER, quxp IN OUT NOCOPY PLS_INTEGER) IS
  BEGIN
    printParams('foobar ' || cnt, foop, barp, bazp, quxp);
    foop := cnt * 2;
    barp := cnt * 3;
    bazp := cnt * 4;
    quxp := cnt * 5;
    printParams('foobar ' || cnt, foop, barp, bazp, quxp);
    dbms_output.put_line(' ');
    IF cnt > 1 THEN
      RAISE foo_error;
    END IF;
  END;

BEGIN
  printParams('main 1', foo, bar, baz, qux);
  foobar(1, foo, bar, baz, qux);    -- 仮パラメータに代入したあと正常に戻ってくる
  printParams('main 2', foo, bar, baz, qux);
  foobar(2, foo, bar, baz, qux);    -- 仮パラメータに代入したあと例外が発生する
  printParams('main 3', foo, bar, baz, qux);

EXCEPTION
  WHEN OTHERS THEN
    printParams('caught exception!', foo, bar, baz, qux);   -- NOCOPY が指定されていた bar, qux については値が変更される

END;

実行結果:

main 1               foo: 20, bar: 30, baz: 40, qux: 50
foobar 1             foo: , bar: , baz: 40, qux: 50
foobar 1             foo: 2, bar: 3, baz: 4, qux: 5

main 2               foo: 2, bar: 3, baz: 4, qux: 5
foobar 2             foo: , bar: , baz: 4, qux: 5
foobar 2             foo: 4, bar: 6, baz: 8, qux: 10

caught exception!    foo: 2, bar: 6, baz: 4, qux: 10

実パラメータの指定方法

サブプログラムをに実パラメータを指定する場合、仮パラメータ名指定で指定することができます。次の通りです。

DECLARE
  PROCEDURE foobar(foo VARCHAR2 := 'foo', bar VARCHAR2 := 'bar', baz VARCHAR2) IS
  BEGIN
    dbms_output.put_line('foo: ' || foo || ', bar: ' || bar || ', baz: ' || baz);
  END;
BEGIN
  foobar('FOO', 'BAR', 'BAZ');
  foobar(bar => 'BAR', baz => 'BAZ');
  foobar('BAR', baz => 'BAZ');  -- foo に 'BAR' が設定されてしまう
  foobar(baz => 'BAZ');
END;

実行結果:

foo: FOO, bar: BAR, baz: BAZ
foo: foo, bar: BAR, baz: BAZ
foo: BAR, bar: bar, baz: BAZ
foo: foo, bar: bar, baz: BAZ

SQL でのストアドファンクションの使用

いくつかの制限がありますが、ストアドファンクションは SQL において使用することができます。

SQL から呼び出す際の制限は次の通りです。

  • SELECT 文・パラレル化 DML 文から起動される場合、ファンクションはデータベースを更新できない
  • DML 文から起動される場合、ファンクションは更新対象の表にアクセスできない
  • ファンクションは次のどの SQL も実行できない
    • トランザクション制御文 (COMMIT, ROLLBACK など)
    • セッション制御文 (SET ROLEなど)
    • システム制御文 (ALTER SYSTEMなど)
    • DDL 文 (CREATE など。これらの文は自動的にコミットされます)

パッケージ

パッケージとは、オブジェクト志向プログラミング言語でいうところのクラスに相当するものです。

PL/SQL の、

  • 例外
  • サブプログラム
  • カーソル
  • 変数
  • 定数

をカプセル化するスキーマオブジェクトです。

パッケージは、仕様部、および本体から構成されます。構文は次の通りです。

パッケージ仕様部:

CREATE [ OR REPLACE ] PACKAGE [ schema. ] package_name
  [ invoker_rights_clause ]
  { IS | AS }
  item_list_1
END [ package_name ] ;

パッケージ本体:

CREATE [ OR REPLACE ] PACKAGE BODY [ schema. ] package_name
  { IS | AS }
  declare_section
  [ initialize_section ]
END [ package_name ] ;

パッケージ仕様部の宣言と、パッケージ本体の定義のヘッダは、空白以外の一語一語が一致している必要があります。

(とても簡単な) パッケージ仕様部・本体とそれを使用するプログラムの例を示します。

CREATE OR REPLACE PACKAGE FooBar IS
  name VARCHAR2(32);
  value PLS_INTEGER := 0;
  PROCEDURE foo(name_ VARCHAR2);
END FooBar;
/

CREATE OR REPLACE PACKAGE BODY FooBar IS
  PROCEDURE foo(name_ VARCHAR2) IS
    temp PLS_INTEGER;
  BEGIN
    temp := value + 1;
    dbms_output.put_line('name: ' || name || ' -> ' || name_ || ', value: ' || value || ' -> ' || temp);
    name := name_;
    value := temp;
  END;
End FooBar;
/

DECLARE
BEGIN
  FooBar.foo('foo');
  FooBar.foo('bar');
END;
/

最初の 2 つの CREATE 〜 文を実行すると、パッケージ仕様部・本体がコンパイルされてスキーマに格納されます。

次に、最後の無名ブロックを実行してみます。

name:  -> foo, value: 0 -> 1
name: foo -> bar, value: 1 -> 2

name: bar -> foo, value: 2 -> 3
name: foo -> bar, value: 3 -> 4

2 回続けて実行すると、上記の結果が得られます。

その後、パッケージをリコンパイルするかセッションを切断・再接続して無名ブロックを実行すると次の結果となります。

name:  -> foo, value: 0 -> 1
name: foo -> bar, value: 1 -> 2

name: bar -> foo, value: 2 -> 3
name: foo -> bar, value: 3 -> 4

name: bar -> foo, value: 4 -> 5
name: foo -> bar, value: 5 -> 6

パッケージは、セッションにおいて最初にアクセスされた際にインスタンス化されます。 このインスタンスはセッションが切断されるかパッケージがリコンパイルされるまで維持されます。 また、インスタンスはセッション固有のものですので別のセッションとは無関係です (それぞれのセッションで別々のパッケージインスタンス (状態) を持つ)。

ただし、パッケージが SERIALLY_REUSABLE として宣言されている場合は事情が異なります。

SERIALLY_REUSABLE

上記プログラムのパッケージ仕様部・本体の最初の行に SERIALLY_REUSABLE プラグマを記述したプログラムを実行してみます。

CREATE OR REPLACE PACKAGE FooBar IS
  PRAGMA SERIALLY_REUSABLE;
  :
  :

CREATE OR REPLACE PACKAGE BODY FooBar IS
  PRAGMA SERIALLY_REUSABLE;
  :
  :

最後の無名ブロックを 2 度続けて実行すると次の結果が得られます。

name:  -> foo, value: 0 -> 1
name: foo -> bar, value: 1 -> 2

name:  -> foo, value: 0 -> 1
name: foo -> bar, value: 1 -> 2

SERIALLY_REUSABLE プラグマを記述したパッケージは 1 度のサーバコールの間だけインスタンスが維持され、サーバコールが終了した時点で破棄されます (インスタンスの状態を格納するために確保されていた領域が開放されます)。

入出力

Oracle データベースが提供するいくつかのパッケージを使用してファイルその他に対して入出力できます。 参考までに、それらのパッケージ一覧を掲載しておきます。

パッケージ 説明
DBMS_OUTPUT PL/SQL において標準出力ためのパッケージです。
HTF HTML タグを生成するためのファンクションを提供します。
HTP HTMLタグを生成するためのプロシージャを提供します。
DBMS_PIPE 同じインスタンス内の複数のセッション間で通信できます。
UTL_FILE ファイル入出力機能を提供します。
UTL_HTTP HTTP 経由でインターネットにアクセスするための機能を提供します。
UTL_SMTP SMTP によって電子メールを送信するための機能を提供します。

参考 URL