Oracleデータベース
CREATE PROCEDURE search_test(h_where CLOB) AS
str_sql CLOB;
v_cnt INTEGER;
v_array DBMS_SQL.VARCHAR2A;
v_cur INTEGER;
v_smpid INTEGER;
v_smpnm VARCHAR2(20);
v_addbuff VARCHAR2(20);
v_smpage INTEGER;
errcd INTEGER;
length INTEGER;
ret INTEGER;
BEGIN
str_sql := 'SELECT smpid, smpnm FROM smp_tbl WHERE ' || h_where || ' ORDER BY smpid';
v_smpid := 0;
v_smpnm := '';
v_smpage := 0;
v_cur := DBMS_SQL.OPEN_CURSOR;・・・(1)
v_cnt :=
CEIL(DBMS_LOB.GETLENGTH(str_sql)/1000);
FOR idx IN 1 .. v_cnt LOOP
v_array(idx) :=
DBMS_LOB.SUBSTR(str_sql,
1000,
(idx-1)*1000+1);
END LOOP;
DBMS_SQL.PARSE(v_cur, v_array, 1, v_cnt, FALSE, DBMS_SQL.NATIVE);・・・(2)
DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_smpid);
DBMS_SQL.DEFINE_COLUMN(v_cur, 2, v_smpnm, 10);
ret := DBMS_SQL.EXECUTE(v_cur);
LOOP
v_addbuff := '';
IF DBMS_SQL.FETCH_ROWS(v_cur) = 0 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_smpid, errcd, length);・・・(3)
IF errcd = 1405 THEN・・・(3)
DBMS_OUTPUT.PUT_LINE('smpid = (NULL)');
ELSE
DBMS_OUTPUT.PUT_LINE('smpid = ' || v_smpid);
END IF;
DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_smpnm, errcd, length);
IF errcd = 1406 THEN
v_addbuff := '... [len=' || length || ']';
END IF;
IF errcd = 1405 THEN
DBMS_OUTPUT.PUT_LINE('v_smpnm = (NULL)');
ELSE
DBMS_OUTPUT.PUT_LINE('v_smpnm = ' || v_smpnm || v_addbuff );
END IF;
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
DBMS_OUTPUT.NEW_LINE;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cur);・・・(4)
RETURN;
END;
/
Set serveroutput on
call search_test('smpid < 100');FUJITSU Enterprise Postgres
CREATE FUNCTION search_test(h_where text) RETURNS void AS $$
DECLARE
str_sql text;
v_cur INTEGER;
v_smpid INTEGER;
v_smpnm VARCHAR(20);
v_addbuff VARCHAR(20);
v_smpage INTEGER;
errcd INTEGER;
length INTEGER;
ret INTEGER;
BEGIN
PERFORM DBMS_OUTPUT.SERVEROUTPUT(TRUE);
str_sql := 'SELECT smpid, smpnm FROM smp_tbl WHERE ' || h_where || ' ORDER BY smpid';
v_smpid := 0;
v_smpnm := '';
v_smpage := 0;
v_cur := DBMS_SQL.OPEN_CURSOR();・・・(1)
PERFORM DBMS_SQL.PARSE(v_cur, str_sql, 1);・・・(2)
PERFORM DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_smpid);
PERFORM DBMS_SQL.DEFINE_COLUMN(v_cur, 2, v_smpnm, 10);
ret := DBMS_SQL.EXECUTE(v_cur);
LOOP
v_addbuff := '';
IF DBMS_SQL.FETCH_ROWS(v_cur) = 0 THEN
EXIT;
END IF;
PERFORM DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
SELECT value,column_error,actual_length
INTO v_smpid, errcd, length
FROM DBMS_SQL.COLUMN_VALUE(v_cur,
1,
v_smpid);・・・(3)
IF errcd = 22002 THEN・・・(3)
PERFORM DBMS_OUTPUT.PUT_LINE('smpid = (NULL)');
ELSE
PERFORM DBMS_OUTPUT.PUT_LINE('smpid = ' || v_smpid);
END IF;
SELECT value,column_error,actual_length INTO v_smpnm, errcd, length FROM DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_smpnm);
IF errcd = 22001 THEN
v_addbuff := '... [len=' || length || ']';
END IF;
IF errcd = 22002 THEN
PERFORM DBMS_OUTPUT.PUT_LINE('v_smpnm = (NULL)');
ELSE
PERFORM DBMS_OUTPUT.PUT_LINE('v_smpnm = ' || v_smpnm || v_addbuff );
END IF;
PERFORM DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
PERFORM DBMS_OUTPUT.NEW_LINE();
END LOOP;
v_cur := DBMS_SQL.CLOSE_CURSOR(v_cur);・・・(4)
RETURN;
END;
$$
LANGUAGE plpgsql;
SELECT search_test('smpid < 100');DBMS_OUTPUTパッケージのNEW_LINEと同じです。記述差異および、記述差異に伴う移行手順については、DBMS_OUTPUTパッケージのNEW_LINEを参照してください。
DBMS_SQL.PARSE(第１引数, 第２引数, 第３引数, 第４引数, 第５引数, 第６引数)
SQL文を文字列の表タイプ(VARCHAR2A型、VARCHAR2S型)で指定することができます。これは、第２引数に指定します。
SQL文を処理する方法の指定にDBMS_SQL.NATIVE、DBMS_SQL.V6、DBMS_SQL.V7を指定することができます。
SQL文を文字列の表タイプでは指定できません。
SQL文を処理する方法の指定にDBMS_SQL.NATIVE、DBMS_SQL.V6、DBMS_SQL.V7を指定することができません。
以下の手順で移行してください。
“DBMS_SQL.PARSE”というキーワードでストアドプロシジャ内を検索し、呼び出し箇所を特定します。
第２引数(例のv_array)に指定されたSQL文のデータ型を確認します。
データ型がDBMS_SQL.VARCHAR2A型、またはDBMS_SQL.VARCHAR2S型である場合は、表タイプによる指定です。3.の手順から移行処理を継続してください。
データ型がDBMS_SQL.VARCHAR2A型、またはDBMS_SQL.VARCHAR2S型でない場合は、文字列による指定です。7.の手順から移行処理を継続してください。
DBMS_SQL.VARCHAR2A型、およびDBMS_SQL.VARCHAR2S型に分割する前のSQL文(例のstr_sql)を確認します。
DBMS_SQL.VARCHAR2A型、およびDBMS_SQL.VARCHAR2S型にSQLを分割している一連の処理(例のFOR idx付近の処理)を削除します。
第２引数を2.で確認した分割する前のSQL文(例のstr_sql)に置き換えます。
第３引数～第５引数(例のv_cnt, FALSE, DBMS_SQL.NATIVE)までを削除します。
DBMS_SQL.NATIVE、DBMS_SQL.V6、DBMS_SQL.V7が指定されている場合は、第３引数を数定数 1 に置き換えます。
DBMS_SQL.VARCHAR2A型、またはDBMS_SQL.VARCHAR2S型を使用している場合は第６引数が該当します。
DBMS_SQL.VARCHAR2A型、またはDBMS_SQL.VARCHAR2S型を使用していない場合は第３引数が該当します。
DBMS_SQL.COLUMN_VALUE(第１引数, 第２引数, 第３引数, 第４引数, 第５引数)
column_errorに対して以下のエラーコードを戻します。
1406 : 取得した値が切り捨てられている
1405 : 取得した値の内容がNULL値
column_errorに対して以下のエラーコードを戻します。
22001 : 取得した値が切り捨てられている
22002 : 取得した値の内容がNULL値
取得する値は、引数に指定した変数で受け取ります。
取得する値は、DBMS_SQL.COLUMN_VALUEの検索結果なので、SELECT文のINTO句に指定した変数で受け取ります。
以下の手順で移行してください。
“DBMS_SQL.COLUMN_VALUE”というキーワードでストアドプロシジャ内を検索し、呼び出し箇所を特定します。
DBMS_SQL.COLUMN_VALUEの呼び出し箇所を、SELECT INTO文に置き換えます。
DBMS_SQL.COLUMN_VALUEの第３引数(例のv_smpid)以降に指定している引数の数を確認します(例の場合は、v_smpid, errcd, length の3個)。
選択リストに、先で確認した引数の数に応じてそれぞれvalue、column_error、actual_lengthの順で指定します。(例えば、第３引数のみ指定されている場合はvalueのみを指定します)
INTO句に、DBMS_SQL.COLUMN_VALUEに設定していた第３引数～第５引数(例のv_smpid, errcd, length)を同じ順番で指定します。
FROM句にDBMS_SQL.COLUMN_VALUEを記載します。引数は修正前の第１引数～第３引数(例のv_cur, 1, v_smpid)までを指定します。
第４引数(例のcolumn_errorの値)を使用している場合は、対象の変数(例のerrcd)を使用している箇所を確認します。
確認した箇所で判定などの処理を行っている場合は、判定する際の値を以下の通り修正します。
1406 ⇒ 22001
1405 ⇒ 22002
DBMS_SQL.CLOSE_CURSOR(第１引数)
クローズすると、引数に指定したカーソルがNULL値になります。
クローズすると、DBMS_SQL.CLOSE_CURSORの復帰値がNULL値になりますので、代入文に指定したカーソルで受け取ります。
以下の手順で移行してください。
“DBMS_SQL.CLOSE_CURSOR”というキーワードでストアドプロシジャ内を検索し、呼び出し箇所を特定します。
DBMS_SQL.CLOSE_CURSORの呼び出し箇所を、値の代入(:=)に置き換え、カーソルがNULL値となるようにします。
左辺にDBMS_SQL.CLOSE_CURSORに指定していた引数(例のv_cur)を指定します。
右辺にDBMS_SQL.CLOSE_CURSORを記載します。引数は修正前と同じ値(例のv_cur)を指定します。