8.5.3 DBMS

概要: PL/pgSQLから動的SQLを実行することができます。

機能

機能	説明
BIND_VARIABLE	SQL文内のホスト変数に値を設定します。
CLOSE_CURSOR	カーソルをクローズします。
COLUMN_VALUE	FETCH_ROWSを実行して取り出した選択リストの列の値を取得します。
DEFINE_COLUMN	値を取り出す列と格納先を定義します。
EXECUTE	SQL文を実行します。
FETCH_ROWS	指定したカーソルの次の行に位置付け、行から値を取り出します。
OPEN_CURSOR	新規カーソルをオープンします。
PARSE	SQL文を解析します。

注意

DBMS_SQLでは、利用者が動的SQLを実行する上でデータ型を意識する必要があり、使えるデータ型が限られます。以下のデータ型をサポートします。
- INTEGER
- DECIMAL
- NUMERIC
- REAL
- DOUBLE PRECISION
- CHAR(注1)
- VARCHAR(注1)
- NCHAR(注1)
- NCHAR VARYING(注1)
- TEXT
- DATE
- TIMESTAMP WITHOUT TIME ZONE
- TIMESTAMP WITH TIME ZONE
- INTERVAL(注2)
- SMALLINT
- BIGINT
  注1)
  CHAR型、VARCHAR型、NCHAR型、NCHAR VARYING型のホスト変数は、文字列関数の引数や戻り値と合わせるためにTEXT型として扱います。文字列関数の詳細については、“PostgreSQL文書”の“SQL言語”の“文字列関数と演算子”を参照してください。
  Oracleデータベース互換機能のNVLおよびDECODEの引数に指定する場合は、引数間のデータ型が同じになるように、ホスト変数に対してCASTによりデータ型を変換してください。
  例
  探索条件にNVLを指定し、NVLの引数としてNCHAR型のホスト変数を期待する場合。
  col1:NCHAR型の列
  h1:NCHAR型のホスト変数
```
PERFORM DBMS_SQL.PARSE(cursor, 'SELECT col2 FROM t3 WHERE NVL(col1,CAST(:h1 AS NCHAR(3))) = N''あいう''', 1);
PERFORM DBMS_SQL.BIND_VARIABLE(cursor, ':h1', N'あいう');
```
  注2)
  選択リストに指定したINTERVAL型の値をCOLUMN_VALUEで取得する場合、選択リストの時間隔修飾子と同じか時間隔修飾子を指定しないなど範囲の広いINTERVAL型の変数を指定してください。狭い範囲の時間隔修飾子の変数を指定すると、その時間隔修飾子の範囲の値を取得しますが、範囲外の値が切り捨てられたというエラーは発生しません。
  例
  選択リストにINTERVAL値を返す値式を設定し、その結果をCOLUMN_VALUEで受け取る場合。なお、例に記載したSQL文の演算結果はINTERVAL DAY TO SECONDの範囲の値を返します。
  [悪い例]
  変数(v_interval)がINTERVAL DAY TO HOURのため、MINUTE以降の値が切り捨てられます。
```
    v_interval     INTERVAL DAY TO HOUR;
・・・
    PERFORM DBMS_SQL.PARSE(cursor, 'SELECT CURRENT_TIMESTAMP - ''2010-01-01'' FROM DUAL', 1);
・・・
    SELECT value INTO v_interval FROM DBMS_SQL.COLUMN_VALUE(cursor, 1, v_interval);
    結果：1324 days 09:00:00
```
  [良い例]
  変数(v_interval)をINTERVALとすることで、値を正しく受け取ります。
```
    v_interval     INTERVAL;
・・・
    PERFORM DBMS_SQL.PARSE(cursor, 'SELECT CURRENT_TIMESTAMP - ''2010-01-01'' FROM DUAL', 1);
・・・
    SELECT value INTO v_interval FROM DBMS_SQL.COLUMN_VALUE(cursor, 1, v_interval);
    結果：1324 days 09:04:37.530623
```

記述形式

DBMS_SQLの各機能について説明します。

BIND_VARIABLE

BIND_VARIABLEは、SQL文内のホスト変数に値を設定します。
カーソル番号は処理対象のカーソル番号です。
ホスト変数名はSQL文内のホスト変数の名前を文字列で指定します。
値式はホスト変数に設定する値です。ホスト変数は、値式のデータ型になります。そして、SQL文中の指定場所に応じて暗黙的に型変換されます。暗黙の型変換については、“A.3 暗黙の型変換”を参照してください。
文字列長は値式が文字列型の場合の文字数です。文字列長の指定がない場合は、文字列全体の長さとなります。
SQL文のホスト変数は、ホスト変数を識別するために先頭にコロンを付ける必要があります。BIND_VARIABLEで指定するホスト変数名はコロンを付加してもしなくても構いません。以下にSQL文で指定するホスト変数名とBIND_VARIABLEで指定するホスト変数名の例を示します。
```
PERFORM DBMS_SQL.PARSE(cursor, 'SELECT emp_name FROM emp WHERE sal > :x', 1);
```
この例では、BIND_VARIABLEは次のようになります。
```
PERFORM DBMS_SQL.BIND_VARIABLE(cursor, ':x', 3500); 
```
または、
```
PERFORM DBMS_SQL.BIND_VARIABLE(cursor, 'x', 3500);
```
ホスト変数名の長さは、30バイト以下(コロンを除く)である必要があります。
設定する値のデータ型が文字列の場合、第４引数として列値の有効サイズを指定します。

例

設定する値のデータ型が文字列以外の場合

PERFORM DBMS_SQL.BIND_VARIABLE(cursor, ':NO', 1);

設定する値のデータ型が文字列の場合

PERFORM DBMS_SQL.BIND_VARIABLE(cursor, ':NAME', h_memid, 5);

CLOSE_CURSOR

CLOSE_CURSORは、カーソルをクローズします。
カーソル番号は処理対象のカーソル番号です。
戻り値はNULL値です。

例

cursor := DBMS_SQL.CLOSE_CURSOR(cursor);

COLUMN_VALUE

COLUMN_VALUEは、FETCH_ROWSを実行して取り出した選択リストの列の値を取得します。
カーソル番号は処理対象のカーソル番号です。
列位置はSELECT文の選択リストの列の位置です。最初の列の位置は1です。
変数名は、格納先の変数名を指定します。
取り出した情報は、SELECT文でvalue, column_error, actual_lengthの列の値として取得します。
valueは列位置で指定した列の値を返却します。変数名のデータ型は列のデータ型に合わせてください。PARSEに指定したSELECT文の選択リストの列がDBMS_SQLに対応していないデータ型の場合、CASTを使って対応するデータ型に型変換してください。
column_errorはNUMERIC型です。列の値が、valueに正しく設定できなかった場合に、0以外の値を返却します。
22001 : 取り出した文字列が切り捨てられている
22002 : 取り出した値の内容がNULL値
actual_lengthはINTEGER型です。取り出した値が文字列型の場合は、文字数を返却します。切り捨てられている場合、切り捨てられる前の文字数を返却します。文字列型でない場合は、バイト数を返却します。

例

列の値、エラーコード、列値の実際の長さを取得する場合

SELECT value, column_error, actual_length INTO v_memid, v_col_err, v_act_len FROM DBMS_SQL.COLUMN_VALUE(cursor, 1, v_memid);

列の値だけを取得する場合

SELECT value INTO v_memid FROM DBMS_SQL.COLUMN_VALUE(cursor, 1, v_memid);

DEFINE_COLUMN

DEFINE_COLUMNは、値を取り出す列と格納先を定義します。
カーソル番号は処理対象のカーソル番号です。
列位置はSELECT文の選択リストの列の位置です。最初の列の位置は1です。
変数名は、格納先を指定します。格納先のデータ型は、値を取り出す列のデータ型に合わせてください。PARSEに指定したSELECT文の選択リストの列がDBMS_SQLに対応していないデータ型の場合、CASTを使って対応するデータ型に型変換してください。
文字列長は文字列型の列に対する列値の最大の文字数です。
列値のデータ型が文字列の場合、第４引数として列値の有効サイズを指定します。

例

列値のデータ型が文字列以外の場合

PERFORM DBMS_SQL.DEFINE_COLUMN(cursor, 1, v_memid);

列値のデータ型が文字列の場合

PERFORM DBMS_SQL.DEFINE_COLUMN(cursor, 1, v_memid, 10);

EXECUTE

EXECUTEは、SQL文を実行します。
カーソル番号は処理対象のカーソル番号です。
戻り値は、INTEGER型で、INSERT文、UPDATE文、DELETE文の場合のみ有効で、処理した行数です。それ以外の場合は無効です。

例

ret := DBMS_SQL.EXECUTE(cursor);

FETCH_ROWS

FETCH_ROWSは、次の行に位置付け、行から値を取り出します。
カーソル番号は処理対象のカーソル番号です。
戻り値は、INTEGER型で、取り出した行数です。すべて取り出した場合は0を返却します。
取り出した情報はCOLUMN_VALUEで取得します。

例

LOOP
    IF DBMS_SQL.FETCH_ROWS(cursor) = 0 THEN
        EXIT;
    END IF;
・
・
・
END LOOP;

OPEN_CURSOR

OPEN_CURSORは、新規のカーソルをオープンします。
パラメータ１はOracleデータベースとの互換のためのパラメータであり、Symfoware Serverでは目的を持たないパラメータです。INTEGER型が指定可能で、指定値は無視されます。指定値に意味はありませんが、指定する場合は1を指定してください。なお、Oracleデータベースから移行する場合、指定値を変更する必要はありません。
不要になったカーソルはCLOSE_CURSORを実行してクローズしてください。
戻り値は、INTEGER型で、カーソル番号です。

例

cursor := DBMS_SQL.OPEN_CURSOR();

PARSE

PARSEは、動的SQL文の解析を行います。
カーソル番号は処理対象のカーソル番号です。
SQL文は解析するSQL文です。
パラメータ１、パラメータ２、パラメータ３およびパラメータ４は、Oracleデータベースとの互換のためのパラメータであり、Symfoware Serverでは目的を持たないパラメータです。指定値は無視されます。指定値に意味はありませんが、指定する場合は以下の値を指定してください。
- パラメータ１はINTEGER型で、1を指定してください。
- パラメータ２およびパラメータ３はTEXT型で、指定する場合はNULLを指定してください。
- パラメータ４はBOOLEAN型で、指定する場合はTRUEを指定してください。
なお、Oracleデータベースから移行する場合、パラメータ２、パラメータ３およびパラメータ４は、指定値を変更する必要はありません。
SQL文のホスト変数は、先頭にコロンを付けます。
DDL文はPARSEを発行した時点で実行されます。DDL文のEXECUTEは不要です。
オープンしているカーソルに対して再度PARSEを呼び出した場合は、カーソル内のデータ領域の内容をリセットし、新たにSQL文の解析を行います。

例

PERFORM DBMS_SQL.PARSE(cursor, 'SELECT memid, memnm FROM member WHERE memid = :NO', 1);

DBMS_SQLの各機能のフローと使用例を示します。

DBMS_SQLのフロー

使用例

CREATE FUNCTION smp_00()
RETURNS INTEGER
AS $$
DECLARE
    str_sql     VARCHAR(255);
    cursor      INTEGER;
    h_smpid     INTEGER;
    v_smpid     INTEGER;
    v_smpnm     VARCHAR(20);
    v_smpage    INTEGER;
    errcd       INTEGER;
    length      INTEGER;
    ret         INTEGER;
BEGIN
    str_sql     := 'SELECT smpid, smpnm, smpage FROM smp_tbl WHERE smpid < :H_SMPID ORDER BY smpid';
    h_smpid     := 3;
    v_smpid     := 0;
    v_smpnm     := '';
    v_smpage    := 0;

    cursor := DBMS_SQL.OPEN_CURSOR();

    PERFORM DBMS_SQL.PARSE(cursor, str_sql, 1);

    PERFORM DBMS_SQL.BIND_VARIABLE(cursor, ':H_SMPID', h_smpid);

    PERFORM DBMS_SQL.DEFINE_COLUMN(cursor, 1, v_smpid);
    PERFORM DBMS_SQL.DEFINE_COLUMN(cursor, 2, v_smpnm, 10);
    PERFORM DBMS_SQL.DEFINE_COLUMN(cursor, 3, v_smpage);

    ret := DBMS_SQL.EXECUTE(cursor);
    loop
        if DBMS_SQL.FETCH_ROWS(cursor) = 0 then
            EXIT;
        end if;

        SELECT value,column_error,actual_length INTO v_smpid,errcd,length FROM DBMS_SQL.COLUMN_VALUE(cursor, 1, v_smpid);
        RAISE NOTICE '--------------------------------------------------------';
        RAISE NOTICE '--------------------------------------------------------';
        RAISE NOTICE 'smpid       = %', v_smpid;
        RAISE NOTICE 'errcd       = %', errcd;
        RAISE NOTICE 'length      = %', length;

        SELECT value,column_error,actual_length INTO v_smpnm,errcd,length FROM DBMS_SQL.COLUMN_VALUE(cursor, 2, v_smpnm);
        RAISE NOTICE '--------------------------------------------------------';
        RAISE NOTICE 'smpnm       = %', v_smpnm;
        RAISE NOTICE 'errcd       = %', errcd;
        RAISE NOTICE 'length      = %', length;

        select value,column_error,actual_length INTO v_smpage,errcd,length FROM DBMS_SQL.COLUMN_VALUE(cursor, 3, v_smpage);
        RAISE NOTICE '--------------------------------------------------------';
        RAISE NOTICE 'smpage      = %', v_smpage;
        RAISE NOTICE 'errcd       = %', errcd;
        RAISE NOTICE 'length      = %', length;
        RAISE NOTICE '';
    end loop;

    cursor := DBMS_SQL.CLOSE_CURSOR(cursor);
    RETURN 0;
END;
$$ LANGUAGE plpgsql;

8.5.3 DBMS_SQL

8.5.3.1 機能説明

8.5.3.2 使用例