PL/SQLのデータ型の理解および使用

ここでは、PL/SQLプログラムでサポートされているPL/SQLデータ型について説明します。TimesTen SQL文でサポートされているデータ型の説明ではありません。TimesTenのSQL文でサポートされているデータ型については、『Oracle TimesTen In-Memory Database SQLリファレンス』のデータ型を参照してください。

この項では、次のトピックについて説明します。

• PL/SQLのデータ型のカテゴリ

• PL/SQLの事前定義済スカラー・データ型

• PL/SQLの複合データ型

• PL/SQL REF CURSOR

追加情報については、『Oracle Database PL/SQL言語リファレンス』のPL/SQLデータ型を参照してください。

PL/SQLのデータ型のカテゴリ

PL/SQLブロックには、各定数、変数、およびパラメータにはデータ型があります。PL/SQLには事前定義されたデータ型およびサブタイプが用意されており、ユーザー自身でPL/SQLサブタイプを定義することもできます。

表3-2に、事前定義されたPL/SQLデータ型のカテゴリを示します。

表3-2 事前定義されているPL/SQLデータ型カテゴリ

データ型のカテゴリ	説明
スカラー	内部コンポーネントを持たない単一値
複合	スカラーまたは複合のいずれかである内部コンポーネント
参照	REF CURSORなどの他のデータ項目へのポインタ。

ノート:

「サポートされていないデータ型」を参照してください。

PL/SQLの事前定義済スカラー・データ型

スカラー・データ型には、内部コンポーネントを持たない単一値が格納されます。

これらについては次の項で説明します。

• スカラー・データ型および型ファミリ

• スカラー・データ型の変数の宣言

• PLS_INTEGERデータ型およびBINARY_INTEGERデータ型

• SIMPLE_INTEGERデータ型

• ROWIDデータ型

• LOBデータ型

ノート:

「サポートされていないデータ型」を参照してください。

スカラー・データ型および型ファミリ

事前定義されているPL/SQLスカラー・データ型および型ファミリを示します。

表3-3に、事前定義されたPL/SQLの重要なスカラー・データ型をデータ型ファミリごとに分類して示します。

表3-3 事前定義されているPL/SQLスカラー・データ型

データ型ファミリ	データ型名
NUMERIC	NUMBER PLS_INTEGER BINARY_FLOAT BINARY_DOUBLE
CHARACTER	CHAR[ACTER] VARCHAR2 NCHAR(各国語キャラクタCHAR) NVARCHAR2(各国語キャラクタVARCHAR2)
BINARY	RAW
BOOLEAN	BOOLEAN ノート: SQL文でBOOLEAN型をバインドすることはできません。
DATETIME	DATE TIMESTAMP
INTERVAL	INTERVAL YEAR TO MONTH INTERVAL DAY TO SECONDS
ROWID	ROWID
LOB	BLOB (バイナリLOB) CLOB (キャラクタLOB) NCLOB(各国語キャラクタLOB)

スカラー・データ型の変数の宣言

この例では、PL/SQL変数を宣言しています。

Command> DECLARE
            v_emp_job     VARCHAR2 (9);
            v_count_loop  BINARY_INTEGER := 0;
            v_dept_total_sal NUMBER (9,2) := 0;
            v_orderdate   DATE := SYSDATE + 7;
            v_valid       BOOLEAN NOT NULL := TRUE;
         ...

PLS_INTEGERデータ型およびBINARY_INTEGERデータ型

PLS_INTEGERデータ型およびBINARY_INTEGERデータ型は同一のものであり、このマニュアルでは同じ意味で使用されています。

PLS_INTEGERデータ型は、32ビットで表される-2,147,483,648から2,147,483,647の範囲の符号付き整数を格納します。このデータ型には、NUMBERデータ型およびそのサブタイプと比較して次のようなメリットがあります。

• PLS_INTEGER値の方が、必要な記憶域が少なくなります。

• PLS_INTEGER演算はハードウェア算術計算を使用するため、ライブラリ算術計算を使用するNUMBER演算より処理速度が速くなります。

適用範囲に含まれるすべての計算にPLS_INTEGER値を使用した方が効率的です。PLS_INTEGERの範囲外の計算には、NUMBERデータ型の事前定義されたサブタイプであるINTEGERを使用してください。

『Oracle Database PL/SQL言語リファレンス』のPLS_INTEGERデータ型およびBINARY_INTEGERデータ型を参照してください。

ノート:

2つのPLS_INTEGERデータ型による計算がPLS_INTEGERの範囲をオーバーフローすると、結果がNUMBERデータ型に割り当てられている場合でも、オーバーフロー例外が発生します。

SIMPLE_INTEGERデータ型

SIMPLE_INTEGERは、PLS_INTEGERデータ型の事前定義されたサブタイプであり、PLS_INTEGERと同じ範囲(-2,147,483,648から2,147,483,647)とNOT NULL制約を持ちます。PLS_INTEGERと異なるのは、オーバーフローが発生しないという点です。

SIMPLE_INTEGERは、値がnullになることがなく、オーバーフロー・チェックが不要な場合に使用できます。NULL値およびオーバーフローをチェックするオーバーヘッドがないため、SIMPLE_INTEGERはPLS_INTEGERよりも高いパフォーマンスを実現します。

『Oracle Database PL/SQL言語リファレンス』のPLS_INTEGERのSIMPLE_INTEGERサブタイプを参照してください。

ROWIDデータ型

表の各行は、ROWIDと呼ばれる一意の識別子を持っています。

アプリケーションでは、SQL文のWHERE句などで、一重引用符で囲んだCHAR定数としてリテラルのROWID値を指定できます。

『Oracle TimesTen In-Memory Database SQLリファレンス』のROWIDデータ型およびROWID疑似列も参照してください。

LOBデータ型

LOB(ラージ・オブジェクト)型ファミリには、CLOB(キャラクタLOB)、NCLOB(各国語キャラクタLOB)、およびBLOB(バイナリLOB)が含まれています。

LOBは、LOBロケータおよびLOB値で構成されています。ロケータは、値へのハンドルとして機能します。たとえば、アプリケーションがLOBを選択する場合、またはパラメータとしてLOBを渡す場合、それは実際の値ではなくロケータを使用しています。

LOBは、永続的または一時的のいずれかに設定できます。永続LOBは、データベース内の特にLOB列の行に存在します。一時LOBはプログラム内で内部的に使用されますが、その後、そのLOBをデータベース内のLOB列に挿入して永続LOBにすることもできます。

TimesTen ClassicでのLOBに関する追加情報は、『Oracle TimesTen In-Memory Database SQLリファレンス』のLOBデータ型を参照してください。

「ラージ・オブジェクト(LOB)」も参照してください。

PL/SQLの複合データ型

複合型には、配列、レコードまたは表の要素などの、個別に操作できる内部コンポーネントがあります。

次の項で、複合データ型の使用について説明します。

• TimesTenでの複合データ型のサポート

• PL/SQLでのコレクションの使用

• PL/SQLでのレコードの使用

• アプリケーションからの連想配列の使用

『Oracle Database PL/SQL言語リファレンス』のPL/SQLのコレクションおよびレコードを参照してください。

TimesTenでの複合データ型のサポート

TimesTenでは、次の複合データ型がサポートされています。

• 連想配列(索引付き表)

• ネストした表

• VARRAY

• レコード

連想配列、ネストした表およびVARRAYは、コレクションとも呼ばれます。

PL/SQLでのコレクションの使用

コレクション・データ型は、他の言語で使用される配列、セットおよびハッシュ表と同じように宣言できます。コレクションとは、要素(すべて同じ型)の順序付きグループのことです。各要素には、コレクション内での位置を決定する一意の添字があります。

PL/SQLでは、配列型はVARRAY(可変サイズ配列)と呼ばれ、セット型はネストした表と呼ばれ、ハッシュ表型は連想配列または索引付き表と呼ばれます。これらはすべてコレクション型です。

次の例では、コレクション型staff_listをemployee_idの表として宣言し、このコレクション型をループおよびSELECT文のWHERE句で使用します。

Command> DECLARE
           TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
           staff staff_list;
           lname employees.last_name%TYPE;
           fname employees.first_name%TYPE;
         BEGIN
           staff := staff_list(100, 114, 115, 120, 122);
           FOR i IN staff.FIRST..staff.LAST LOOP
             SELECT last_name, first_name INTO lname, fname FROM employees
               WHERE employees.employee_id = staff(i);
             DBMS_OUTPUT.PUT_LINE (TO_CHAR(staff(i)) ||
               ': ' || lname || ', ' || fname );
             END LOOP;
         END;
         /
100: King, Steven
114: Raphaely, Den
115: Khoo, Alexander
120: Weiss, Matthew
122: Kaufling, Payam
 
PL/SQL procedure successfully completed.

コレクションは、PL/SQLサブプログラム間でパラメータとして渡すことができます。ただしTimesTenでは、PL/SQLと他の言語で記述されたアプリケーションの間で渡すことができるのは連想配列のみです。(後述の「アプリケーションからの連想配列の使用」を参照してください。)

バルクSQLを使用すると、コレクションによってTimesTen表からデータを出し入れできます。

PL/SQLでのレコードの使用

レコードは、異なるデータ型を持つフィールドで構成された複合データ構造です。レコードは、単一のパラメータでサブプログラムに渡すことができます。

「PL/SQLの変数および定数」内の例で示すとおり、フィールドの名前および型を指定せずに、%ROWTYPE属性を使用して、表内の行を表すレコード、または問合せの結果セット内の行を表すレコードを宣言することもできます。

この例では、レコード・タイプを宣言しています。

Command> DECLARE
           TYPE timerec IS RECORD (hours SMALLINT, minutes SMALLINT);
           TYPE meetin_typ IS RECORD (
           date_held DATE,
           duration timerec, -- nested record
           location VARCHAR2(20),
           purpose VARCHAR2(50));
         BEGIN
           ...
         END;
         /

アプリケーションからの連想配列の使用

TimesTen PL/SQLでは、以前は索引付き表またはPL/SQL表と呼ばれていた連想配列が、OCI、Pro*C/C++、またはJDBCアプリケーションなどからのIN、OUTまたはIN OUTバインド・パラメータとしてサポートされます。これにより、データの配列をアプリケーションとデータベースの間で効率的に渡すことができます。

連想配列は、キー/値ペアのセットです。TimesTenでは、連想配列バインドの場合(PL/SQL内のみで連想配列を使用する場合を除く)、キー(索引)は整数(BINARY_INTEGERまたはPLS_INTEGER)である必要があります。値は、同じデータ型の単純なスカラー値である必要があります。たとえば、部門番号で索引付けされた部門マネージャの配列を使用できます。索引は、作成された順序ではなく、ソートされた順序で格納されます。

連想配列型を宣言した後、次の例のようにPL/SQLの連想配列を宣言できます(INDEX BYに注目)。

declare
   TYPE VARCHARARRTYP IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
   x VARCHARARRTYP;
   ...

次の例と「連想配列の使用」の例を参照してください。

『Oracle TimesTen In-Memory Database C開発者ガイド』のTimesTen OCIにおけるの連想配列のバインドとTimesTen Pro*C/C++における連想配列のバインド、および『Oracle TimesTen In-Memory Database Java開発者ガイド』の連想配列の使用も参照してください。

連想配列に関する一般的な情報については、『Oracle Database PL/SQL言語リファレンス』の連想配列を参照してください。

ノート:

TimesTenでは、次の制限に注意してください。

• 次のLOB、REF CURSOR、TIMESTAMP、ROWIDの型は、連想配列のバインドではサポートされていません。

• 連想配列バインドは、パススルー文では許可されません。

• TimesTenのプログラミングAPIでは、配列の一般的なバルク・バインドはサポートされていません。VARRAYおよびネストした表は、バインド・パラメータとしてサポートされていません。

次の例では、連想配列を操作し、ttIsqlから効率的に連想配列をバインドし、配列を出力します。

Command> var lngvc[1000] varchar2(30);
Command> declare
            TYPE VARCHARARRTYP IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
            x VARCHARARRTYP;
         begin
            x := :lngvc;
            x ( 1 ) := 'One';
            x ( 10 ) := 'Ten';
            :lngvc := x;
         end;
         /
 
PL/SQL procedure successfully completed.
 
Command> print lngvc;
LNGVC                : ARRAY [ 1000 ] (Current Size 10)
LNGVC[1] : One
LNGVC[2] : <NULL>
LNGVC[3] : <NULL>
LNGVC[4] : <NULL>
LNGVC[5] : <NULL>
LNGVC[6] : <NULL>
LNGVC[7] : <NULL>
LNGVC[8] : <NULL>
LNGVC[9] : <NULL>
LNGVC[10] : Ten

PL/SQL REF CURSOR

REF CURSORは、PL/SQLとアプリケーション間でパラメータとして渡すことのできる、SQL結果セットに対するカーソルへのハンドルです。

TimesTenでは、PL/SQLからアプリケーションへのOUT REF CURSORをサポートしています。アプリケーションは、PL/SQL内でREF CURSORをオープンし、適用可能なAPIでそれを渡し、結果セットをフェッチします。

TimesTenでは、ダイレクト接続またはクライアント/サーバー接続のいずれかのために、ODBC、JDBC、ODP.NET、OCI、Pro*C/C++およびTTClassesでREF CURSORがサポートされています。REF CURSORについては、次のTimesTenドキュメントでも説明しています。

• 『Oracle TimesTen In-Memory Database C開発者ガイド』のREF CURSORの使用

• 『Oracle TimesTen In-Memory Database Java開発者ガイド』のREF CURSORの使用

• 『Oracle TimesTen In-Memory Database TTClassesガイド』のREF CURSORの使用

Oracle Databaseで行うときと同じ方法で、TimesTenでもPL/SQLでREF CURSORを定義できます。(『Oracle Database PL/SQL言語リファレンス』のカーソル変数を参照してください。)REF CURSORは通常はメタタイプとして使用され、ご使用のデータに合せて強力な(固有の) REF CURSOR型を定義し、その型のカーソル変数を宣言します。次に例を示します:

Command> DECLARE
           TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;
           dept_cv  DeptCurTyp; -- declare cursor variable
         ...

次の例では、FOO_PACKパッケージのGET_EMPプロシージャを作成して、employees表から従業員情報を取得します。プロシージャではREF CURSOR型のcursor_outを宣言し、その型を出力パラメータとして使用します。

まず、パッケージ定義、REF CURSOR型およびプロシージャ定義を指定します。

create or replace package foo_pack is 
   type cursor_out is ref cursor;
   procedure get_emp (results out cursor_out);
end foo_pack;

その後、パッケージ本体とプロシージャ実装を指定します。

create or replace package body foo_pack as
   procedure get_emp (results out cursor_out) is
      begin
         open results for select employee_id, last_name from employees 
                          where employee_id < 110 order by last_name;
      end get_emp;
end foo_pack;

出力用にREF CURSOR変数を宣言し、プロシージャを実行して、結果を表示します。PL/SQLの外部で宣言できるのは弱い(汎用的な) REF CURSORのみであることに注意してください。

Command> var proc_result refcursor;
Command> exec foo_pack.get_emp(:proc_result);
 
PL/SQL procedure successfully completed.
 
Command> print proc_result;
PROC_RESULT          :
< 105, Austin >
< 102, De Haan >
< 104, Ernst >
< 109, Faviet >
< 108, Greenberg >
< 103, Hunold >
< 100, King >
< 101, Kochhar >
< 107, Lorentz >
< 106, Pataballa >
10 rows found.

または、弱い型指定のREF CURSOR変数はFOO_PACKで宣言できます。

create or replace package foo_pack is 
   procedure get_emp (results out sys_refcursor);
end foo_pack;
 
create or replace package body foo_pack as
   procedure get_emp (results out sys_refcursor) is
      begin
         open results for select employee_id, last_name from employees 
                          where employee_id < 110 order by last_name;
      end get_emp;
end foo_pack;