アーカイブ

Posts Tagged ‘PL/SQL’

Oracle Database 12cではパッケージ仕様部で定義したコレクション型をパッケージ実装部のSQLで利用することができるみたい。

これまでPL/SQLの内部でSQLを発行するときにIN句に引数の値を利用するには、グローバルな型としてTYPE宣言した方の変数を引数としてもらい、表関数(TABLE)を使ってコレクションのネストを解除して問い合わせを行う必要がありました。

12cでもネストの解除を行う必要が有りますが、パッケージ仕様部で定義したコレクション型をSQLで利用できるようになったようです。

Oracleのマニュアルでは、新機能ガイドでこそっと紹介されています。

Oracle Database 12c 新機能ガイド
2.1.4.12 PL/SQLからSQLへのインタフェースで使用できるPL/SQL固有のデータ型

データ型がPL/SQLで宣言されたコレクションに対するPL/SQLプログラムで表演算子を使用できるようになりました。このとき、データ型としてPL/SQL結合配列を使用することもできます。(以前のリリースでは、コレクションのデータ型をスキーマ・レベルで宣言する必要がありました。)

詳しくはOracle® Database PL/SQL言語リファレンスを参照してくださいということですので、該当の記述があるOracle Database 12cリリース1(12.1)の変更点PL/SQLとSQL間のインタフェースを越えられるPL/SQL専用データ型の追加を参照してください。

例えばこんなパッケージ定義が有るとします。Test2.Sample1は引数にもらったコレクションを元にtestTableテーブルのId列をIn句で検索してヒットした件数を返します。

create or replace package Test2
as

type SampleArrayType is table of char(5);

function Sample1(arr SampleArrayType) return number;

end test2;

create or replace package body Test2
as

function Sample1(arr SampleArrayType) return number
is
   rowCount number(3);
begin
   select count(*) into rowCount
     from testTable
    where id in (select column_value from TABLE(arr));

   return rowCount ;
end Sample1;

end test2;

このSQLを次のような無名ブロックで実行します。

DECLARE
  ARR TEST2.SAMPLEARRAYTYPE := TEST2.SAMPLEARRAYTYPE(‘123’, ‘456’);
  v_Return NUMBER;
BEGIN
  v_Return := TEST2.SAMPLE1(
    ARR => ARR
  );
  DBMS_OUTPUT.PUT_LINE(‘v_Return = ‘ || v_Return);
END;

上記の無名ブロックは12cで実行すると期待通り実行できますが、11gでは下記のようなエラーメッセージが表示され実行できません。12c以前でIn句にコレクションを利用したい場合は、パッケージ仕様部ではなくスキーマのオブジェクトとして配列をTYPE宣言して上げれば同じ結果が得られます。

Error
[row:28,col:1] ORA-21700: オブジェクトが存在しないか、削除マークが設定されています。
ORA-06512: "XXXXX.TEST2", 行9
ORA-06512: 行5

なにげに嬉しいです。

広告
カテゴリー:Oracle タグ: ,