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); ・・・(3)
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);・・・(4)
IF errcd = 1405 THEN・・・(4)
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・・・(4)
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);・・・(5)
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_smpnm_max_length INTEGER;
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)
CALL DBMS_SQL.PARSE(v_cur, str_sql);・・・(2)
CALL DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_smpid); ・・・(3)
CALL DBMS_SQL.DEFINE_COLUMN(v_cur, 2, v_smpnm);
v_smpnm_max_length := 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('--------------------------------------------------------');
CALL DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_smpid);・・・(4)
errcd := 0; ・・・(4)
IF v_smpid IS NULL THEN
errcd := 1405;
END IF;
IF errcd = 1405 THEN
PERFORM DBMS_OUTPUT.PUT_LINE('smpid = (NULL)');
ELSE
PERFORM DBMS_OUTPUT.PUT_LINE('smpid = ' || v_smpid);
END IF;
CALL DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_smpnm);・・・(4)
errcd := 0;
length := 0;
IF v_smpnm IS NULL THEN
errcd := 1405;
length := 0;
ELSE;
length := LENGTH(v_smpnm);
IF length > v_smpnm_max_length THEN
errcd := 1406;
length := v_smpnm_max_length;
v_smpnm := LEFT(v_smpnm, v_smpnm_max_length);
END IF;
END IF;
IF errcd = 1406 THEN
v_addbuff := '... [len=' || length || ']';
END IF;
IF errcd = 1405 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;
CALL DBMS_SQL.CLOSE_CURSOR(v_cur);・・・(5)
v_cur := NULL;
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.DEFINE_COLUMN(第１引数, 第２引数, 第３引数, 第４引数)
第４引数に返される文字列データの最大長を指定します。指定された場合は、後続のDBMS_SQL.COLUMN_VALUEで文字列データを取得した際に、指定した長さで文字列が切り捨てられます。
また、切り捨てが発生したかどうかを、DBMS_SQL.COLUMN_VALUEのエラーコードで確認できます。
第４引数に返される文字列データの最大長を指定します。指定された場合は、後続のDBMS_SQL.COLUMN_VALUEで文字列データを取得した際に、指定した長さで文字列が切り捨てられます。
ただし、切り捨てが発生したかどうかはDBMS_SQL.COLUMN_VALUEの実行時には確認できません。
以下の手順で移行してください。
“DBMS_SQL.DEFINE_COLUMN”というキーワードでストアドプロシージャ内を検索し、呼び出し箇所を特定します。
第４引数に数値を指定しているかを確認します。
第４引数に数値を指定している場合は、後続のDBMS_SQL.COLUMN_VALUEの実行の際に、エラーコード 1406を評価しているかを確認します。評価している場合は、同じ評価を行えるように、以下の対応を行います。
DBMS_SQL.DEFINE_COLUMNの第４引数を退避しておく、INTEGER変数の宣言を追加します。
DBMS_SQL.COLUMN_VALUEの第４引数の指定値を上記の変数に設定し、DBMS_SQL.DEFINE_COLUMNの第４引数は削除します。
後続のDBMS_SQL.COLUMN_VALUEの実行後に、上記のINTEGER変数を使って、文字列の切り捨てや、エラーコード 1406の評価を行います。詳細は“(4) COLUMN_VALUE”を参照してください。
DBMS_SQL.COLUMN_VALUE(第１引数, 第２引数, 第３引数, 第４引数, 第５引数)
第４引数に以下のエラーコードを戻します。
1405 : 取得した値の内容がNULL値
1406 : 取得した値が切り捨てられている
第４引数、 第５引数が指定できません。
そのため、第４引数を利用した上記のエラーコードの判断ができず、また、第５引数に設定されるべき、取得した値の長さがわかりません。
取得する値は、引数に指定した変数で受け取ります。
取得する値は、引数に指定した変数で受け取ります。
ただし、第４引数、第５引数が指定できないため、処理結果を元に、指定できなかった変数に適切な値を設定しなおすことで、既存の処理を変えずに移行します。
以下の手順で移行してください。
“DBMS_SQL.COLUMN_VALUE”というキーワードでストアドプロシージャ内を検索し、呼び出し箇所を特定します。
COLUMN_VALUEの第４引数の値が1405かを判断している場合、取得したデータ値がNULL値かどうかを確認する処理を行っています。
以下の第４引数への対応により、既存の処理が実行できるようにします。
第４引数の判定処理の直前に、取得したデータ値がNULL値かどうかの確認処理を追加します。その際に、NULL値だった場合は、第４引数の変数に1405を設定します。
COLUMN_VALUEの第４引数の値が1406かを判断している場合、取得したデータ値に切り捨てが行われたかどうかを確認する処理を行っています。
取得したデータ値の切り捨てが起きるのは、DBMS_SQL.DEFINE_COLUMNの第４引数に、返される文字列データの最大長を指定している場合なため、COLUMN_VALUE だけではなく、DBMS_SQL.DEFINE_COLUMNにも同時に対応する必要があります。
DBMS_SQL.DEFINE_COLUMNへの対応
文字列データの最大長の退避などが必要です。詳細は、“(3) DEFINE_COLUMN”を参照してください。
返却文字の切り捨てが行われるべきかの確認
上記のDBMS_SQL.DEFINE_COLUMNの対応により、文字列の切り捨ては行われずに返却されます。そのため、第４引数が1406かどうかの確認処理の前に、返却値の長さ（LENGTH関数で取得）とDBMS_SQL.DEFINE_COLUMNから退避した、文字列データの最大長を比較し、本来は返却文字の切り捨てが行われるべきだったかを確認します。
返却値の長さの方が大きい場合は、第４引数に1406を設定し、後続の既存の判定処理が行われるように対応します。この時、LEFT関数を使って、文字列データの最大長の長さに切り捨ても行う必要もあります。
COLUMN_VALUEの第５引数が指定されている場合は、返却された文字列の長さを設定する必要があります。
COLUMN_VALUEの実行後に、LENGTH関数で返却値の長さを取得し、第５引数の変数に代入します。
DBMS_SQL.CLOSE_CURSOR(第１引数)
クローズすると、引数に指定したカーソルがNULL値になります。
クローズしても、引数に指定したカーソルがNULL値になりません。カーソルをNULLに設定しなおしてください。
以下の手順で移行してください。
“DBMS_SQL.CLOSE_CURSOR”というキーワードでストアドプロシージャ内を検索し、呼び出し箇所を特定します。
DBMS_SQL.CLOSE_CURSORの呼び出し後に、カーソルがNULL値となるようにします。