DBMS_SQL - PolarDB - Alibaba Cloud ドキュメントセンター

DBMS_SQL パッケージでは、PolarDB for PostgreSQL (Oracle互換) で動的 SQL クエリがサポートされています。アプリケーションの実行時にクエリを作成できます。

PolarDB for PostgreSQL (Oracle 互換) では、動的 SQL 文がネイティブでサポートされ、Oracle データベース互換の動的 SQL 文を使用できます。

表 1. DBMS_SQL 関数とストアドプロシージャ

関数またはストアドプロシージャ	タイプ	戻り値のデータ型	説明
BIND_VARIABLE(c, name, value [, out_value_size ])	ストアドプロシージャ	非該当	値を変数にバインドします。
BIND_VARIABLE_CHAR(c, name, value [, out_value_size ])	ストアドプロシージャ	非該当	CHAR 値を変数にバインドします。
BIND_VARIABLE_RAW(c, name, value [, out_value_size ])	ストアドプロシージャ	非該当	RAW 値を変数にバインドします。
CLOSE_CURSOR(c IN OUT)	ストアドプロシージャ	非該当	カーソルを無効化します。
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]])	ストアドプロシージャ	非該当	列の値を変数に返します。
COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT ]])	ストアドプロシージャ	非該当	CHAR 列の値を変数に返します。
COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]])	ストアドプロシージャ	非該当	RAW 列の値を変数に返します。
DEFINE_COLUMN(c, position, column [, column_size ])	ストアドプロシージャ	非該当	SELECT リストの列を定義します。
DEFINE_COLUMN_CHAR(c, position, column, column_size)	ストアドプロシージャ	非該当	SELECT リストの CHAR 列を定義します。
DEFINE_COLUMN_RAW(c, position, column, column_size)	ストアドプロシージャ	非該当	SELECT リストの RAW 列を定義します。
DEFINE_ARRAY(c,position,table_variable,cnt, lower_bnd)	ストアドプロシージャ	非該当	行を配列としてフェッチする列を定義します。
DESCRIBE_COLUMNS	ストアドプロシージャ	非該当	カーソル結果セットを保持する列を定義します。
EXECUTE(c)	関数	INTEGER	カーソルを実行します。
EXECUTE_AND_FETCH(c [, exact ])	関数	INTEGER	カーソルを実行し、単一の行をフェッチします。
FETCH_ROWS(c)	関数	INTEGER	カーソルから行をフェッチします。
IS_OPEN(c)	関数	BOOLEAN	カーソルが有効かどうかを確認します。
LAST_ROW_COUNT	関数	INTEGER	フェッチされた行の累積数を返します。
OPEN_CURSOR	関数	INTEGER	カーソルを有効化します。
PARSE(c, statement, language_flag)	ストアドプロシージャ	非該当	文を解析します。

PolarDB の DBMS_SQL パッケージは、Oracle DBMS_SQL パッケージと比較して部分的に実装されています。 PolarDB では、前の表にリストされている関数とストアドプロシージャのみサポートされています。

下表に、DBMS_SQL パッケージで使用できるパブリック変数を示します。

表 2. DBMS_SQL のパブリック変数

パブリック変数	データ型	値	説明
native	INTEGER	1	Oracle 構文との互換性のために使用されます。詳細については、DBMS_SQL.PARSE をご参照ください。
V6	INTEGER	2	Oracle 構文との互換性のために使用されます。詳細については、DBMS_SQL.PARSE をご参照ください。
V7	INTEGER	3	Oracle 構文との互換性のために使用されます。詳細については、DBMS_SQL.PARSE をご参照ください。

BIND_VARIABLE

BIND_VARIABLE ストアドプロシージャは、SQL 文の IN または IN OUT バインド変数に値をバインドするために使用されます。

BIND_VARIABLE(c INTEGER, name VARCHAR2,
  value { BLOB | CLOB | DATE | FLOAT | INTEGER | NUMBER |
          TIMESTAMP | VARCHAR2 }
  [, out_value_size INTEGER ])

パラメーター

パラメーター	説明
c	バインド変数を含む SQL 文に対するカーソルの ID です。
name	SQL 文でのバインド変数の名前です。
value	割り当てられる値です。
out_value_size	`name` が IN OUT 変数の場合、このパラメーターは出力値の最大長を定義します。このパラメーターを指定しない場合には、現在の値の長さはデフォルトで最大長になります。

例

以下の匿名ブロックは、バインド変数を使用して emp テーブルに行を挿入します。

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(150) := 'INSERT INTO emp VALUES ' ||
                        '(:p_empno, :p_ename, :p_job, :p_mgr, ' ||
                        ':p_hiredate, :p_sal, :p_comm, :p_deptno)';
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_mgr           emp.mgr%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    v_empno    := 9001;
    v_ename    := 'JONES';
    v_job      := 'SALESMAN';
    v_mgr      := 7369;
    v_hiredate := TO_DATE('13-DEC-07','DD-MON-YY');
    v_sal      := 8500.00;
    v_comm     := 1500.00;
    v_deptno   := 40;
    DBMS_SQL.BIND_VARIABLE(curid,':p_empno',v_empno);
    DBMS_SQL.BIND_VARIABLE(curid,':p_ename',v_ename);
    DBMS_SQL.BIND_VARIABLE(curid,':p_job',v_job);
    DBMS_SQL.BIND_VARIABLE(curid,':p_mgr',v_mgr);
    DBMS_SQL.BIND_VARIABLE(curid,':p_hiredate',v_hiredate);
    DBMS_SQL.BIND_VARIABLE(curid,':p_sal',v_sal);
    DBMS_SQL.BIND_VARIABLE(curid,':p_comm',v_comm);
    DBMS_SQL.BIND_VARIABLE(curid,':p_deptno',v_deptno);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

以下のような出力が表示されます。

Number of rows processed: 1

BIND_VARIABLE_CHAR

BIND_VARIABLE_CHAR ストアドプロシージャは、CHAR 値を SQL 文の IN または IN OUT バインド変数にバインドするために使用されます。

BIND_VARIABLE_CHAR(c INTEGER, name VARCHAR2, value CHAR
  [, out_value_size INTEGER ])

パラメーター

パラメーター	説明
c	バインド変数を含む SQL 文に対するカーソルの ID です。
name	SQL 文でのバインド変数の名前です。
value	割り当てられる CHAR 型の値です。
out_value_size	name が IN OUT 変数の場合、このパラメーターは出力値の最大長を定義します。このパラメーターを指定しない場合には、現在の値の長さはデフォルトで最大長になります。

BIND_VARIABLE_RAW

BIND_VARIABLE_RAW ストアドプロシージャは、RAW 値を SQL 文の IN または IN OUT バインド変数にバインドするために使用されます。

BIND_VARIABLE_RAW(c INTEGER, name VARCHAR2, value RAW
  [, out_value_size INTEGER ])

パラメーター

パラメーター	説明
c	バインド変数を含む SQL 文に対するカーソルの ID です。
name	SQL 文でのバインド変数の名前です。
value	割り当てられる CHAR 型の値です。
out_value_size	`name` が IN OUT 変数の場合、このパラメーターは出力値の最大長を定義します。このパラメーターを指定しない場合には、現在の値の長さはデフォルトで最大長になります。

CLOSE_CURSOR

CLOSE_CURSOR ストアドプロシージャは、カーソルを無効化するために使用されます。カーソルが無効化されると、カーソルに割り当てられていたリソースがリリースされ、カーソルは使用できなくなります。

CLOSE_CURSOR(c IN OUT INTEGER)

パラメーター

パラメーター	説明
c	カーソルの ID です。

例

以下の例では、カーソルを無効化する方法を示しています。

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
            .
            .
            .
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE

COLUMN_VALUE ストアドプロシージャは、カーソルから値を受け取る変数を定義するために使用されます。

COLUMN_VALUE(c INTEGER, position INTEGER, value OUT { BLOB |
  CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

パラメーター

パラメーター	説明
c	定義されている変数にデータを返すカーソルの ID です。
position	カーソル内の戻りデータの位置です。カーソルの最初の値は位置 1 です。
value	前の FETCH 呼び出しによってカーソルで返されたデータを受け取る変数です。
column_error	エラーが生じた場合、このパラメーターは列に関連付けられたエラーコードを示します。
actual_length	切り捨て前のデータの実際の長さです。

例

以下の例では、COLUMN_VALUE ストアドプロシージャを使用してカーソルから値を受け取る匿名ブロックの一部を示しています。

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
            .
            .
            .
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE_CHAR

COLUMN_VALUE_CHAR ストアドプロシージャは、カーソルから CHAR 値を受け取る変数を定義するために使用されます。

COLUMN_VALUE_CHAR(c INTEGER, position INTEGER, value OUT CHAR
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

パラメーター

パラメーター	説明
c	定義されている変数にデータを返すカーソルの ID です。
position	カーソル内の戻りデータの位置です。カーソルの最初の値は位置 1 です。
value	前の FETCH 呼び出しによってカーソルで返されたデータを受け取るデータ型が CHAR の変数です。
column_error	エラーが生じた場合、このパラメーターは列に関連付けられたエラーコードを示します。
actual_length	切り捨て前のデータの実際の長さです。

COLUMN_VALUE_RAW

COLUMN_VALUE_RAW ストアドプロシージャは、カーソルから RAW 値を受け取る変数を定義するために使用されます。

COLUMN_VALUE_RAW(c INTEGER, position INTEGER, value OUT RAW
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

パラメーター

パラメーター	説明
c	定義されている変数にデータを返すカーソルの ID です。
position	カーソル内の戻りデータの位置です。カーソルの最初の値は位置 1 です。
value	前の FETCH 呼び出しによってカーソルで返されたデータを受け取るデータ型が RAW の変数です。
column_error	エラーが生じた場合、このパラメーターは列に関連付けられたエラーコードを示します。
actual_length	切り捨て前のデータの実際の長さです。

DEFINE_COLUMN

DEFINE_COLUMN ストアドプロシージャは、カーソルで返されフェッチされる SELECT リスト内の列または式を定義するために使用されます。

DEFINE_COLUMN(c INTEGER, position INTEGER, column { BLOB |
  CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }
  [, column_size INTEGER ])

パラメーター

パラメーター	説明
c	SELECT 文に関連付けられたカーソルの ID です。
position	定義されている SELECT リスト内の列または式の位置です。
column	SELECT 結果セットの指定された位置にある列または式のデータ型と一致する変数です。
column_size	戻りデータの最大長です。列のデータ型が VARCHAR2 の場合は、column_size パラメーターを指定する必要があります。 column_size を超える戻りデータは、column_size パラメーターで指定された最大長に切り捨てられます。

例

以下の例では、DEFINE_COLUMN ストアドプロシージャを使用して、emp テーブルの empno、ename、hiredate、sal、および comm 列を定義する方法を示しています。

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);
            .
            .
            .
END;

以下の例では、前述の例とまったく同じ結果を生成する別の方法を示しています。データ型の長さは無関係であることにご注意ください。 empno、sal、および comm 列は、v_num が NUMBER(1) として定義されていた場合でも、NUMBER(4) および NUMBER(7,2) と同等のデータを返します。 ename 列は、DEFINE_COLUMN 呼び出しの length パラメーターで定義されているとおり、最大 10 文字の長さのデータを返します。 v_varchar に対して宣言されたデータ型 VARCHAR2(1) で示されている長さは無視されます。戻りデータの実際のサイズは、DEFINE_COLUMN ストアドプロシージャによって決定されます。

DECLARE
    curid           INTEGER;
    v_num           NUMBER(1);
    v_varchar       VARCHAR2(1);
    v_date          DATE;
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_num);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_varchar,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_date);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_num);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_num);
            .
            .
            .
END;

DEFINE_COLUMN_CHAR

DEFINE_COLUMN_CHAR ストアドプロシージャは、カーソルで返されフェッチされる SELECT リスト内の CHAR 列または式を定義するために使用されます。

DEFINE_COLUMN_CHAR(c INTEGER, position INTEGER, column CHAR, column_size INTEGER)

パラメーター

パラメーター	説明
c	SELECT 文に関連付けられたカーソルの ID です。
position	定義されている SELECT リスト内の列または式の位置です。
column	CHAR 変数です。
column_size	戻りデータの最大長です。 column_size を超える戻りデータは、column_size 文字に切り捨てられます。

DEFINE_COLUMN_RAW

DEFINE_COLUMN_RAW ストアドプロシージャは、カーソルで返されフェッチされる SELECT リスト内の RAW 列または式を定義するために使用されます。

DEFINE_COLUMN_RAW(c INTEGER, position INTEGER, column RAW,
  column_size INTEGER)

パラメーター

パラメーター	説明
c	SELECT 文に関連付けられたカーソルの ID です。
position	定義されている SELECT リスト内の列または式の位置です。
column	RAW 変数です。
column_size	戻りデータの最大長です。 column_size を超える戻りデータは、column_size 文字に切り捨てられます。

DEFINE_ARRAY

DEFINE_ARRAY ストアドプロシージャは、行を配列としてフェッチする列を定義します。構文：

DEFINE_ARRAY (
   c           IN INTEGER,
   position    IN INTEGER,
   <table_variable>    IN <datatype>,
   cnt         IN INTEGER,
   lower_bnd   IN INTEGER);

パラメーター

パラメーター	説明
c	配列にバインドされるカーソルの ID です。
position	配列内の列の相対位置です。
table_variable	<datatype> として宣言されている変数です。 <datatype> の有効値： varchar2_table clob_table binary_float_table binary_double_table blob_table date_table number_table timestamp_table
cnt	フェッチされた行の数です。 0 より大きい整数である必要があります。
lower_bnd	結果は、この下限インデックスから始めて配列にコピーされます。

例

以下の匿名ブロックは、名前が t のテーブルを作成し、t テーブルから 2 行のデータを取得します。

create table t as select i as a,2 * i as b,3 * i as c from generate_series(1,3) i;

DECLARE
  c      INTEGER;
  d      NUMBER;
  n_tab  dbms_sql.varchar2_Table;
  n_tab1  dbms_sql.varchar2_Table;
BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,
                 'select * from t',
                 dbms_sql.native);
  dbms_sql.define_array(c,1,n_tab,2,1);
  d := dbms_sql.execute(c);
  d := dbms_sql.fetch_rows(c);
  dbms_output.put_line('fetch rows is ' || d);
  dbms_sql.column_value(c,
                          1,
                          n_tab1);
    FOR i IN 1 .. d LOOP
      dbms_output.put_line(n_tab1(i));
    END LOOP;
  dbms_sql.close_cursor(c);
END;

以下のような出力が表示されます。

fetch rows is 2
1
2

DESCRIBE_COLUMNS

DESCRIBE_COLUMNS ストアドプロシージャは、カーソルによって返される列を記述するために使用されます。

DESCRIBE_COLUMNS(c INTEGER, col_cnt OUT INTEGER, desc_t OUT
  DESC_TAB);

パラメーター

パラメーター	説明
c	カーソルの ID です。
col_cnt	カーソル結果セットの列数です。
desc_tab	カーソルによって返される各列の記述を含むテーブルです。記述は DESC_REC 型で、以下のテーブルの値が含まれます。

列名	タイプ
col_type	INTEGER
col_max_len	INTEGER
col_name	VARCHAR2(128)
col_name_len	INTEGER
col_schema_name	VARCHAR2(128)
col_schema_name_len	INTEGER
col_precision	INTEGER
col_scale	INTEGER
col_charsetid	INTEGER
col_charsetform	INTEGER
col_null_ok	BOOLEAN

EXECUTE

EXECUTE 関数は、解析された SQL 文または SPL ブロックを実行するために使用されます。

status INTEGER EXECUTE(c INTEGER)

パラメーター

パラメーター	説明
c	実行される解析済み SQL 文または SPL ブロックのカーソル ID です。
status	SQL 文が DELETE、INSERT、または UPDATE の場合、このパラメーターは処理されたレコードの数を示します。このパラメーターは、他の文に対しては意味がありません。

例

以下の匿名ブロックは、dept テーブルに行を挿入します。

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(50);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'INSERT INTO dept VALUES (50, ''HR'', ''LOS ANGELES'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

EXECUTE_AND_FETCH

EXECUTE_AND_FETCH 関数は、解析された SELECT 文を実行し、1 行をフェッチするために使用されます。

status INTEGER EXECUTE_AND_FETCH(c INTEGER
  [, exact BOOLEAN ])

パラメーター

パラメーター	説明
c	SELECT 文に対するカーソルの ID です。
exact	このパラメーターが FALSE に設定されている場合、例外は発生しません。デフォルト値は FALSE です。このパラメーターが TRUE に設定されている場合、結果セットの行数が 1 でない場合に例外が発生します。このパラメーターが TRUE に設定され、結果セットにレコードが含まれていない場合、NO_DATE_FOUND 例外が発生します。このパラメーターが TRUE に設定され、結果セットに複数のレコードが含まれている場合、TOO_MANY_ROWS 例外が発生します。
status	フェッチされた行が 1 行の場合、1 が返されます。フェッチされた行がない場合、0 が返されます。例外が発生した場合、値は返されません。

例

以下のストアドプロシージャは、EXECUTE_AND_FETCH 関数と従業員の名前を使用して 1 人の従業員を取得します。従業員が見つからない場合、または同じ名前の従業員が複数見つかった場合は、例外が発生します。

CREATE OR REPLACE PROCEDURE select_by_name(
    p_ename         emp.ename%TYPE
)
IS
    curid           INTEGER;
    v_empno         emp.empno%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_dname         dept.dname%TYPE;
    v_disp_date     VARCHAR2(10);
    v_sql           VARCHAR2(120) := 'SELECT empno, hiredate, sal, ' ||
                                     'NVL(comm, 0), dname ' ||
                                     'FROM emp e, dept d ' ||
                                     'WHERE ename = :p_ename ' ||
                                     'AND e.deptno = d.deptno';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.BIND_VARIABLE(curid,':p_ename',UPPER(p_ename));
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_comm);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_dname,14);
    v_status := DBMS_SQL.EXECUTE_AND_FETCH(curid,TRUE);
    DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
    DBMS_SQL.COLUMN_VALUE(curid,2,v_hiredate);
    DBMS_SQL.COLUMN_VALUE(curid,3,v_sal);
    DBMS_SQL.COLUMN_VALUE(curid,4,v_comm);
    DBMS_SQL.COLUMN_VALUE(curid,5,v_dname);
    v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY');
    DBMS_OUTPUT.PUT_LINE('Number    : ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name      : ' || UPPER(p_ename));
    DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date);
    DBMS_OUTPUT.PUT_LINE('Salary    : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm);
    DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname);
    DBMS_SQL.CLOSE_CURSOR(curid);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || p_ename || ' not found');
        DBMS_SQL.CLOSE_CURSOR(curid);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Too many employees named, ' ||
            p_ename || ', found');
        DBMS_SQL.CLOSE_CURSOR(curid);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
        DBMS_SQL.CLOSE_CURSOR(curid);
END;

以下のような出力が表示されます。

EXEC select_by_name('MARTIN')

Number    : 7654
Name      : MARTIN
Hire Date : 09/28/1981
Salary    : 1250
Commission: 1400
Department: SALES

FETCH_ROWS

FETCH_ROWS 関数は、カーソルから行をフェッチするために使用されます。

status INTEGER FETCH_ROWS(c INTEGER)

パラメーター

パラメーター	説明
c	行のフェッチに使用されるカーソルの ID です。
status	フェッチされた行が 1 行の場合、1 が返されます。フェッチされた行がない場合、0 が返されます。

例

以下の例では、emp テーブルから行がフェッチされ、結果が表示されます。

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME       HIREDATE    SAL       COMM');
    DBMS_OUTPUT.PUT_LINE('-----  ----------  ----------  --------  ' ||
        '--------');
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

以下のような出力が表示されます。

EMPNO  ENAME       HIREDATE    SAL       COMM
-----  ----------  ----------  --------  --------
7369   SMITH       1980-12-17    800.00       .00
7499   ALLEN       1981-02-20  1,600.00    300.00
7521   WARD        1981-02-22  1,250.00    500.00
7566   JONES       1981-04-02  2,975.00       .00
7654   MARTIN      1981-09-28  1,250.00  1,400.00
7698   BLAKE       1981-05-01  2,850.00       .00
7782   CLARK       1981-06-09  2,450.00       .00
7788   SCOTT       1987-04-19  3,000.00       .00
7839   KING        1981-11-17  5,000.00       .00
7844   TURNER      1981-09-08  1,500.00       .00
7876   ADAMS       1987-05-23  1,100.00       .00
7900   JAMES       1981-12-03    950.00       .00
7902   FORD        1981-12-03  3,000.00       .00
7934   MILLER      1982-01-23  1,300.00       .00

IS_OPEN

IS_OPEN 関数は、指定されたカーソルが有効かどうかを確認するために使用されます。

status BOOLEAN IS_OPEN(c INTEGER)

パラメーター

パラメーター	説明
c	チェックするカーソルの ID です。
status	カーソルが有効な場合、このパラメーターは TRUE に設定されます。カーソルが無効な場合、このパラメーターは FALSE に設定されます。

LAST ROW COUNT

LAST_ROW_COUNT 関数は、フェッチされた行の合計を返すために使用されます。

rowcnt INTEGER LAST_ROW_COUNT

パラメーター

パラメーター	説明
rowcnt	フェッチされた行の合計です。

例

以下の例では、LAST_ROW_COUNT 関数を使用して、クエリでフェッチされた行の合計を表示しています。

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME       HIREDATE    SAL       COMM');
    DBMS_OUTPUT.PUT_LINE('-----  ----------  ----------  --------  ' ||
        '--------');
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Number of rows: ' || DBMS_SQL.LAST_ROW_COUNT);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

以下のような出力が表示されます。

EMPNO  ENAME       HIREDATE    SAL       COMM
-----  ----------  ----------  --------  --------
7369   SMITH       1980-12-17    800.00       .00
7499   ALLEN       1981-02-20  1,600.00    300.00
7521   WARD        1981-02-22  1,250.00    500.00
7566   JONES       1981-04-02  2,975.00       .00
7654   MARTIN      1981-09-28  1,250.00  1,400.00
7698   BLAKE       1981-05-01  2,850.00       .00
7782   CLARK       1981-06-09  2,450.00       .00
7788   SCOTT       1987-04-19  3,000.00       .00
7839   KING        1981-11-17  5,000.00       .00
7844   TURNER      1981-09-08  1,500.00       .00
7876   ADAMS       1987-05-23  1,100.00       .00
7900   JAMES       1981-12-03    950.00       .00
7902   FORD        1981-12-03  3,000.00       .00
7934   MILLER      1982-01-23  1,300.00       .00
Number of rows: 14

OPEN_CURSOR

OPEN_CURSOR 関数は、カーソルを作成するために使用されます。動的 SQL 文を解析して実行するには、カーソルを使用する必要があります。有効化すると、同じまたは異なる SQL 文でカーソルを再利用できます。カーソルを再び無効化して有効化する必要はありません。

c INTEGER OPEN_CURSOR

パラメーター

パラメーター	説明
c	新しく作成されるカーソルの ID です。

例

以下の例では、新しいカーソルを定義する方法を示しています。

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
            .
            .
            .
END;

PARSE

PARSE ストアドプロシージャは、SQL 文または SPL ブロックを解析するために使用されます。 DDL 文の場合はすぐに実行されるため、EXECUTE 関数を呼び出す必要はありません。

PARSE(c INTEGER, statement VARCHAR2, language_flag INTEGER)

パラメーター

パラメーター	説明
c	有効化されたカーソルの ID です。
statement	解析する SQL 文または SPL ブロックです。 SQL コマンドはセミコロン (;) で終了できません。 SPL ブロックはセミコロン (;) で終了する必要があります。
language_flag	Oracle 構文との互換性のために使用されます。有効な値：DBMS_SQL.V6、DBMS_SQL.V7、および DBMS_SQL.native。すべての構文は PolarDB for PostgreSQL (Oracle 互換) 形式であると見なされるため、このフラグは無視しても構いません。

例

以下の匿名ブロックは、名前が job のテーブルを作成します。 DDL 文は PARSE ストアドプロシージャによってすぐに実行され、EXECUTE 関数を呼び出す必要がないことにご注意ください。

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid, 'CREATE TABLE job (jobno NUMBER(3), ' ||
        'jname VARCHAR2(9))',DBMS_SQL.native);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

以下のコードスニペットは、2 つの行を job テーブルに挿入します。

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(50);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'INSERT INTO job VALUES (100, ''ANALYST'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    v_sql := 'INSERT INTO job VALUES (200, ''CLERK'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

以下のような出力が表示されます。

Number of rows processed: 1
Number of rows processed: 1

以下の匿名ブロックは、DBMS_SQL パッケージを使用して、2 つの INSERT 文を含むブロックを実行します。ブロックの終端にはセミコロン (;) が含まれていますが、OPEN_CURSOR の例では、個々の INSERT 文にはセミコロン (;) がないことにご注意ください。

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(100);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'BEGIN ' ||
               'INSERT INTO job VALUES (300, ''MANAGER''); '  ||
               'INSERT INTO job VALUES (400, ''SALESMAN''); ' ||
             'END;';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;