複合文のCALL文

複合文から外部プロシージャまたはストアド・プロシージャを起動します。つまり、起動はBEGIN ... ENDブロックで実行する必要があります。
OUT引数およびINOUT引数は、通常の値式にできません。これらの引数は、変数またはパラメータである必要があります。IN引数は、通常の値式にできます。
CREATE PROCEDURE文でプロシージャ定義を登録する場合、3GL言語で記述された外部プロシージャの情報をデータベースに格納します。外部プロシージャはデータベースの外部に存在します。CREATE PROCEDURE文の詳細は、「CREATE ROUTINE文」を参照してください。外部プロシージャの作成方法の詳細は、「CREATE ROUTINE文」を参照してください。
ストアド・プロシージャの起動に関するオプションの詳細は、「単一文のCALL文」を参照してください。

環境

複合文は次の環境で使用できます。

対話型SQL内
プリコンパイル対象のホスト言語プログラムに埋め込まれる場合
SQLモジュールのプロシージャの一部として
動的SQLで動的に実行される文として

形式

引数

DEFAULT
Oracle Rdbでパラメータに定義されたDEFAULT式を使用するようにリクエストします。DEFAULTは、CREATE MODULE...PROCEDURE文またはALTER MODULE...ADD PROCEDURE文でINパラメータについて定義できます。DEFAULT句が存在しない場合、NULL式が想定されます。
procedure-name
起動中の外部プロシージャまたはストアド・プロシージャの名前です。
value-expr
DBKEYまたは集計関数を除く値式です。値式の詳細は、第2.6節を参照してください。

使用方法

複合文CALLでは、INパラメータとして任意の値式の受入れが可能です。単一文CALLは、数値リテラルおよび文字列リテラルに限定され、複合文内には存在できません。
複合文CALLで使用されるパラメータのデータ型は、コール先のプロシージャで使用されるデータ型と互換性を持つ必要があります。
複合文CALL内のパラメータの数は、コール先のプロシージャ内のパラメータの数と一致する必要があります。
OUTパラメータおよびINOUTパラメータは、更新可能な変数または他のOUTパラメータおよびINOUTパラメータに対応している必要があります。
複合文CALLの前に設定されたSQLCODEおよびSQLSTATEの値は、GET DIAGNOSTICS文を使用してコール先プロシージャにより確認できます。コール先プロシージャの実行時に、最後の文のSQLCODEおよびSQLSTATEのステータス・パラメータの値はコール元に返され、GET DIAGNOSTICS文を使用して取得できます。
複合文CALLをストアド・プロシージャまたはストアド・ファンクション内で使用して、別のストアド・プロシージャをコールできます。ネストされたCALLで例外が発生した場合、そのプロシージャまたはファンクションおよびすべてのコール側ルーチンは最上位コール元に返されます。
現在のCALL文で使用中のストアド・プロシージャはコールできません。再帰型は許可されていません。
Oracle Rdbにより、複合文中のCALL文では、プロシージャ定義でDEFAULT値が定義されていた後続INモード・パラメータを省略できます。パラメータの明示的な値を置換するDEFAULTキーワードもサポートされています。
ただし、この方法は（BEGIN ENDブロックの外部で使用されている）単一CALL文には適用されず、パラメータおよび値の完全セットが必要です。これは、コール側ルーチンで渡されコール先ルーチンで使用されるパラメータ・ブロックがパラメータ数およびデータ型に関して完全に一致するよう、この型のCALL文についてパラメータ・シグネチャが計算されるためです。
これは、単一CALL文の永続制限です。
次の例は、部分的なパラメータ・リストが単一CALL文ではなく、複合的に使用されたCALL文により完全にサポートされていることを示しています。
SQL> ATTACH 'FILENAME db$:scratch'; SQL> CREATE MODULE mmm cont> PROCEDURE mmm_p (IN :a INTEGER DEFAULT 0, IN :b INTEGER DEFAULT 1); cont> TRACE :a, :b; cont> END MODULE; SQL> SET FLAGS 'Trace'; SQL> CALL mmm_p (10,20); ~Xt: 10 20 SQL> CALL mmm_p (10); %SQL-F-ARGCOUNT, Procedure MMM_P expected 2 parameters, was passed 1 SQL> call MMM_P (); %SQL-F-ARGCOUNT, Procedure MMM_P expected 2 parameters, was passed 0 SQL> begin cont> CALL mmm_p (10,20); cont> CALL mmm_p (10); cont> call mmm_p (); cont> END; ~Xt: 10 20 ~Xt: 10 1 ~Xt: 0 1
最大限に柔軟に対応するには、部分的なパラメータ・リスト、DEFAULTキーワード、およびパラメータ引数の完全値式をサポートする複合文内でCALL文を使用します。

例

例1: 複合文内の外部ルーチンのコール

BEGIN
   DECLARE :param1 INTEGER;
   CALL extern_routine (:param1, 3);
END;

例2: ストアド・ファンクションからのストアド・プロシージャのコール

SQL> CREATE MODULE utility_functions
cont>   LANGUAGE SQL
cont> --
cont>   PROCEDURE trace_date (:dt DATE);
cont>      BEGIN
cont>         TRACE :dt;
cont>      END;
cont> --
cont>   FUNCTION mdy (IN :dt DATE) RETURNS CHAR(10)
cont>   COMMENT 'Returns the date in month/day/year format';
cont>      BEGIN
cont>         IF :dt IS NULL THEN
cont>            RETURN '**/**/****';
cont>         ELSE
cont>            CALL trace_date (:dt);
cont>            RETURN CAST(EXTRACT(MONTH FROM :dt) AS VARCHAR(2)) || '/' ||
cont>                   CAST(EXTRACT(DAY FROM :dt) AS VARCHAR(2)) || '/' ||
cont>                   CAST(EXTRACT(YEAR FROM :dt) AS VARCHAR(4));
cont>         END IF;
cont>      END;
cont> END MODULE;

CASE（検索済）制御文

複数文プロシージャの複合文で代替文ブロックのいずれかのシーケンスを実行します。単一CASE制御文とは異なり、検索済CASE制御文では変数およびパラメータ参照を含むことができるWHEN句の任意の条件がサポートされています。

環境

複数文プロシージャの複合文の検索済CASE制御文は、次の環境で使用できます。

対話型SQL内
プリコンパイル対象のホスト言語プログラムに埋め込まれる場合
SQLモジュールのプロシージャの一部として
動的SQLで動的に実行される文として

形式

引数

ELSE compound-use-statement
WHEN句がFALSEまたはUNKNOWNに評価される場合に実行される一連のSQL文を指定します。
THEN compound-use-statement
WHEN句の値がTRUEに評価される場合に実行される一連のSQL文を指定します。
WHEN predicate
THEN句の複合使用文を実行するか、ELSE句の複合使用文を実行するかを決定します。条件がTRUEに評価される場合は、THEN句の複合使用文が実行されます。条件がFALSEまたはUNKNOWNに評価される場合は、ELSE句の複合使用文が実行されます。

使用方法

CASE値式で一致するWHEN句が見つからない場合、次のいずれのアクションが実行されます。

オプションのELSE句が含まれている場合、SQLではELSE句に関連付けられた一連の文が実行されます。
ELSE句が存在しない場合、例外が発生します。

例

例1: 変数参照による条件の指定

SQL> CREATE TABLE T (C INT);
SQL> BEGIN
cont> DECLARE :V INTEGER = 10;
cont> DECLARE :X INTEGER = 0;
cont> CASE
cont>   WHEN :V = 1 THEN INSERT INTO T(C) VALUES (:X + 1);
cont>   WHEN :V = 2 THEN INSERT INTO T(C) VALUES (:X + 2);
cont>   WHEN :V > 3 THEN INSERT INTO T(C) VALUES (:X);
cont>   ELSE INSERT INTO T(C) VALUES (-1);
cont> END CASE;
cont> END;

CASE（単一）制御文

複数文プロシージャの複合文で代替文ブロックのいずれかのシーケンスを実行します。

環境

複数文プロシージャの複合文の単一CASE制御文は、次の環境で使用できます。

対話型SQL内
プリコンパイル対象のホスト言語プログラムに埋め込まれる場合
SQLモジュールのプロシージャの一部として
動的SQLで動的に実行される文として

形式

引数

CASE value-expr
単一値に評価される式です。一致する値が見つかるまでCASE句の値式とWHEN句に含まれる各WHEN句のリテラル値が比較されます。
値式には、列選択式の一部ではない列指定を含めることはできません。
SQLで提供される様々な値式の詳細は、第2.6節を参照してください。
ELSE compound-use-statement
CASE句の値式に一致するWHEN句が見つからない場合、一連のSQL文を実行します。
複合文で有効なSQL文の詳細は、「複合文」を参照してください。
THEN compound-use-statement
引数値がCASE値式と一致する最初のWHEN句と関連付けられた一連のSQL文を実行します。
WHEN literal

WHEN NULL
SQLによりCASE句の値式と比較されるWHEN句のリテラル値またはNULL値です。ほとんどのCASE制御文には、一連のWHEN句が含まれています。
WHEN句およびCASE句の値が一致すると、そのWHEN句に関連付けられたSQL文が実行されます。制御がCASE制御文からEND CASE句まで無効になり、次のSQL文で戻ります。

使用方法

CASE値式で一致するWHEN句が見つからない場合、次のいずれのアクションが実行されます。

オプションのELSE句が含まれている場合、SQLではELSE句に関連付けられた一連の文が実行されます。
ELSE句が存在しない場合、例外が発生します。

CASE値式のデータ型およびWHEN句のリテラル値のデータ型は同等であることが必要です。
CASE制御文のWHEN句のリテラル値は一意であることが必要です。結果的に、CASE制御文の2つのWHEN句ではNULL値を指定できません。単一CASE文により、あらゆるWHEN句のリテラル値のリストを指定できます。

例

例1: CASE制御文の使用

char x[11];
long x_ind;
   EXEC SQL
      DECLARE ALIAS FOR FILENAME personnel ;

   EXEC SQL
      BEGIN
         CASE :x  INDICATOR :x_ind
            WHEN 'Abrams' THEN
               DELETE FROM employees WHERE . . . ;
            WHEN NULL THEN
               DELETE FROM employees WHERE . . . ;
            ELSE
               DELETE FROM employees WHERE . . . ;
         END CASE ;
      END ;

例2: Case文によるリテラル値のリストの使用

SQL> DECLARE :CODE CHAR(4);
SQL> BEGIN
cont>    JOB_LOOP:
cont>        FOR :JOBFOR
cont>          AS EACH ROW OF
cont>          SELECT * FROM JOBS JOB
cont>          DO
cont>          SET :CODE = :jobfor.JOB_CODE;
cont>            CASE :CODE
cont>              WHEN 'ASCK' THEN
cont>                 UPDATE JOBS
cont>                 SET MINIMUM_SALARY=10000
cont>                 WHERE JOB_CODE = :code;
cont>              WHEN 'ADMN', 'JNTR', 'SCTR' THEN
cont>                 UPDATE JOBS
cont>                 SET MINIMUM_SALARY=15000
cont>                 WHERE JOB_CODE = :code;
cont>              ELSE
cont>                 UPDATE JOBS
cont>                 SET MINIMUM_SALARY=:jobfor.MINIMUM_SALARY*1.1
cont>                 WHERE JOB_CODE=:code;
cont>            END CASE;
cont>        END FOR;
cont> END;
SQL>

CLOSE文

オープンしているカーソルをクローズします。

環境

CLOSE文は次の環境で使用できます。

対話型SQL内
プリコンパイル対象のホスト言語プログラムに埋め込まれる場合
SQLモジュールのプロシージャの一部として

形式

引数

cursor-name

cursor-name-parameter
クローズするカーソルの名前です。カーソル名により参照されたカーソルが拡張された動的DECLARE CURSOR文で実行時に宣言された場合、パラメータを使用します。動的DECLARE CURSOR文で使用されたものと同じカーソル名パラメータを指定します。
CLOSE文が動的カーソルにアクセスしている場合のみ、パラメータを使用してカーソル名を参照できます。

使用方法

オープンしていないカーソル、またはDECLARE CURSOR文で指定されなかったカーソルはクローズできません。
カーソルをクローズしてから再度オープンする場合、SQLでは結果表の最初の行の前にカーソルが配置されます。
SQL CLOSE文を使用してカーソルを個別にクローズしたり、sql_close_cursors()ルーチンを使用してオープンしているすべてのカーソルをクローズできます。sql_close_cursors()ルーチンは引数を取りません。このルーチンの例は、「sql_close_cursors」を参照してください。

例

例1: PL/Iプログラムで宣言されたカーソルのクローズ
このプログラムの一部では埋め込まれたDECLARE CURSOR文、OPEN文およびFETCH文が使用され、マネージャの名前と部門が取得および出力されます。CLOSE文は、（SQLCODEが100に設定されている場合）FETCH文が失敗して結果表でそれ以上の行が検出されなくなってからカーソルをクローズします。
/* Declare the cursor: */ EXEC SQL DECLARE MANAGER CURSOR FOR SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.EMPLOYEE_ID = D.MANAGER_ID ; /* Open the cursor: */ EXEC SQL OPEN MANAGER; /* Start a loop to process the rows of the cursor: */ DO WHILE (SQLCODE = 0); /* Retrieve the rows of the cursor and put the value in host language variables: */ EXEC SQL FETCH MANAGER INTO :FNAME, :LNAME, :DNAME; /* Print the values in the variables: */ . . . END; /* Close the cursor: */ EXEC SQL CLOSE MANAGER;

COMMENT ON文

次のデータベース・オブジェクトに関するコメントを追加または変更します。

カタログ
照合順番
列
制約
ドメイン
データベース
ファンクション
索引
索引パーティション
モジュール
アウトライン
プロシージャ
プロファイル
ロール
スキーマ
順序
記憶域マップ
記憶域マップ・パーティション
シノニム
表
トリガー
ユーザー
ビュー

SQLでは、SHOW文の発行時にこれらのオブジェクトに関するコメントが表示されます。

環境

COMMENT ON文は次の環境で使用できます。

対話型SQL内
プリコンパイル対象のホスト言語プログラムに埋め込まれる場合
SQLモジュールのプロシージャの一部として
動的SQLで動的に実行される文として

形式

引数

CATALOG catalog-name
コメントを作成するカタログの名前を付けます。カタログがデフォルト・スキーマに存在しない場合、COMMENT ON文のカタログ名を別名で修飾する必要があります。
COLLATING SEQUENCE column-sequence-name
コメントを作成する照合順番の名前を付けます。照合順番がデフォルト・スキーマに存在しない場合、COMMENT ON文の照合順番名を別名で修飾する必要があります。
COLUMN column-name
コメントを作成する列の名前を付けます。列名は表名で修飾する必要があります。列がデフォルト・スキーマの表に存在しない場合、COMMENT ON文の列名を表名と別名の両方で修飾する必要があります。
CONSTRAINT constraint-name
コメントを作成する制約の名前を付けます。制約がデフォルト・スキーマに存在しない場合、COMMENT ON文の制約名を別名で修飾する必要があります。
DATABASE
この文は、データベースに対する新規コメントを記述します。ALIAS句が省略された場合、デフォルト・データベースが使用されます。alias-nameには、このセッション中にATTACH文またはCONNECT文で指定された別名を指定する必要があります。
DOMAIN domain-name
コメントを作成するドメインの名前を付けます。ドメインがデフォルト・スキーマに存在しない場合、COMMENT ON文のドメイン名を別名で修飾する必要があります。
FUNCTION function-name
コメントを作成するファンクションの名前を付けます。ファンクションがデフォルト・スキーマに存在しない場合、COMMENT ON文のファンクション名を別名で修飾する必要があります。
INDEX index-name

PARTITION partition-name
コメントを作成する索引、およびオプションで指定した索引のパーティションの名前を付けます。索引がデフォルト・スキーマに存在しない場合、COMMENT ON文の索引名を別名で修飾する必要があります。
IS 'string'
コメントを指定します。SQLでは、対話型SQLでのSHOW文の実行時にテキストが表示されます。コメントは一重引用符（'）で囲み、コメント内の複数の行はスラッシュ（/）で区切ります。
MODULE module-name
コメントを作成するモジュールの名前を付けます。モジュールがデフォルト・スキーマに存在しない場合、COMMENT ON文のモジュール名を別名で修飾する必要があります。
PROCEDURE procedure-name
コメントを作成するプロシージャの名前を付けます。プロシージャがデフォルト・スキーマに存在しない場合、COMMENT ON文のプロシージャ名を別名で修飾する必要があります。
PROFILE profile-name
コメントを作成するプロファイルの名前を付けます。プロファイルがデフォルト・スキーマに存在しない場合、COMMENT ON文のプロファイル名を別名で修飾する必要があります。
ROLE role-name
コメントを作成するロールの名前を付けます。ロールがデフォルト・スキーマに存在しない場合、COMMENT ON文のロール名を別名で修飾する必要があります。
SCHEMA schema-name
コメントを作成するスキーマの名前を付けます。最初にスキーマを作成する必要があります。スキーマがデフォルト・スキーマに存在しない場合、COMMENT ON文のスキーマ名を別名で修飾する必要があります。
SEQUENCE sequence-name
コメントを作成する順序の名前を付けます。順序がデフォルト・スキーマに存在しない場合、COMMENT ON文の順序名を別名で修飾する必要があります。
STORAGE MAP map-name

PARTITION partition-name
コメントを作成する記憶域マップ、およびオプションでその記憶域マップ内の垂直パーティションまたは水平パーティションの名前を付けます。記憶域マップがデフォルト・スキーマに存在しない場合、COMMENT ON文の記憶域マップ名を別名で修飾する必要があります。
SYNONYM synonym-name
これはALTER SYNONYM ... COMMENT IS構文と同じ機能を実行します。synonym-nameは、既存のシノニムの名前である必要があります。デフォルト・データベースの別名以外のデータベース別名を使用して、データベースを選択できます。
TABLE table-name
コメントを作成する表の名前を付けます。表がデフォルト・スキーマに存在しない場合、COMMENT ON文の表名を別名で修飾する必要があります。
table-name col-name
コメントを作成する表およびその表内の列の名前を付けます。
TRIGGER trigger-name
コメントを作成するトリガーの名前を付けます。トリガーがデフォルト・スキーマに存在しない場合、COMMENT ON文のトリガー名を別名で修飾する必要があります。
USER user-name
コメントを作成する（CREATE USER文で作成された）ユーザーの名前を付けます。ユーザーがデフォルト・スキーマに存在しない場合、COMMENT ON文のユーザー名を別名で修飾する必要があります。
VIEW view-name
コメントを作成するビューの名前を付けます。ビューがデフォルト・スキーマに存在しない場合、COMMENT ON文のビュー名を別名で修飾する必要があります。

使用方法

オブジェクトに対するALTER権限、または制約およびトリガーの場合は親オブジェクトに対するALTER権限が必要です。
CREATE DATABASE文では、COMMENT ON文を指定できません。
SQL> CREATE DATABASE FILENAME TEST cont> CREATE TABLE TEST_TABLES (COL1 REAL) cont> COMMENT ON TABLE TEST_TABLES IS 'This will not work'; COMMENT ON TABLE TEST_TABLES IS 'This will not work'; ^ %SQL-W-LOOK_FOR_STT, Syntax error, looking for: %SQL-W-LOOK_FOR_CON, GRANT, CREATE, ;, %SQL-F-LOOK_FOR_FIN, found COMMENT instead
コメントのそれぞれの文字列リテラルの最大長は1024文字です。コメントの長さは合計で2GB未満の必要があります。
COMMENT ON TABLE文では、DATA DEFINITIONモードで予約された表を参照できます。

例

例1: 列および表のコメントの指定

SQL> -- Change the comment for the WORK_STATUS table:
SQL> COMMENT ON TABLE WORK_STATUS IS
cont> 'Links a status code with 1 of 3 statuses' ;
SQL> SHOW TABLE WORK_STATUS
Information for table WORK_STATUS

Comment on table WORK_STATUS: Links a status code with 1 of 3 statuses
   .
   .
   .
SQL> -- Create a comment for the DEPARTMENT_CODE
SQL> -- column in the DEPARTMENTS table:
SQL> COMMENT ON COLUMN DEPARTMENTS.DEPARTMENT_CODE IS
cont> 'Also used in JOB_HISTORY table';
SQL> SHOW TABLE DEPARTMENTS
Information for table DEPARTMENTS

Comment on table DEPARTMENTS:
information about departments in corporation

Columns for table DEPARTMENTS:
Column Name                     Data Type        Domain
-----------                     ---------        ------
DEPARTMENT_CODE                 CHAR(4)          DEPARTMENT_CODE_DOM
 Comment:       Also used in JOB_HISTORY table
   .
   .
   .

例2: 複数の文字列リテラルを含むコメントの指定

SQL> COMMENT ON COLUMN EMPLOYEES.EMPLOYEE_ID IS
cont> '1: Used in SALARY_HISTORY table as Foreign Key constraint' /
cont> '2: Used in JOB_HISTORY table as Foreign Key constraint';
SQL> SHOW TABLE (COL) EMPLOYEES;
Information for table EMPLOYEES

Columns for table EMPLOYEES:
Column Name                     Data Type        Domain
-----------                     ---------        ------
EMPLOYEE_ID                     CHAR(5)          ID_DOM
 Comment:       1: Used in SALARY_HISTORY table as Foreign Key constraint
                2: Used in JOB_HISTORY table as Foreign Key constraint
 Primary Key constraint EMPLOYEES_PRIMARY_EMPLOYEE_ID
LAST_NAME                       CHAR(14)         LAST_NAME_DOM
FIRST_NAME                      CHAR(10)         FIRST_NAME_DOM
MIDDLE_INITIAL                  CHAR(1)          MIDDLE_INITIAL_DOM
ADDRESS_DATA_1                  CHAR(25)         ADDRESS_DATA_1_DOM
ADDRESS_DATA_2                  CHAR(20)         ADDRESS_DATA_2_DOM
CITY                            CHAR(20)         CITY_DOM
STATE                           CHAR(2)          STATE_DOM
POSTAL_CODE                     CHAR(5)          POSTAL_CODE_DOM
SEX                             CHAR(1)          SEX_DOM
BIRTHDAY                        DATE VMS         DATE_DOM
STATUS_CODE                     CHAR(1)          STATUS_CODE_DOM

例3: トリガーへのコメントの追加

SQL> COMMENT ON TRIGGER EMPLOYEE_ID_CASCADE_DELETE IS
cont> 'When an employee is deleted from EMPLOYEES, delete'/
cont> 'corresponding records from the other tables in the'/
cont> 'database.';
SQL> SHOW TRIGGER EMPLOYEE_ID_CASCADE_DELETE
     EMPLOYEE_ID_CASCADE_DELETE
  Source:
 EMPLOYEE_ID_CASCADE_DELETE
                    BEFORE DELETE ON EMPLOYEES
                    (DELETE FROM DEGREES D WHERE D.EMPLOYEE_ID =
                     EMPLOYEES.EMPLOYEE_ID)
                       FOR EACH ROW
                    (DELETE FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID =
                     EMPLOYEES.EMPLOYEE_ID)
                       FOR EACH ROW
                    (DELETE FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID =
                     EMPLOYEES.EMPLOYEE_ID)
                       FOR EACH ROW
                  -- Also, if an employee is terminated and that employee
                  -- is the manager of a department, set the manager_id
                  -- null for that department.
                    (UPDATE DEPARTMENTS D  SET D.MANAGER_ID = NULL
                     WHERE D.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID)
                       FOR EACH ROW.
   .
   .
   .
Comment:       When an employees is deleted from EMPLOYEES, delete
               corresponding records from the other tables in the
               database.

例4: 表内の複数列へのコメントの追加

SQL> COMMENT ON JOBS (JOB_CODE is 'Required column',
cont> WAGE_CLASS is 'Valid values are: 1, 2, 3, or 4');
SQL> SHOW TABLE (COLUMNS) JOBS;
Information for table JOBS
Columns for table JOBS:
Column Name                     Data Type        Domain
-----------                     ---------        ------
JOB_CODE                        CHAR(4)          JOB_CODE
 Comment:       Required column
 Missing Value: None
WAGE_CLASS                      CHAR(1)          WAGE_CLASS
 Comment:       Valid values are: 1, 2, 3, or 4
JOB_TITLE                       CHAR(20)         JOB_TITLE
 Missing Value: None
MINIMUM_SALARY                  INTEGER(2)       SALARY
MAXIMUM_SALARY                  INTEGER(2)       SALARY