PL/SQLで動的なSQLを実行する
Oracle10gでFunctionを作っている際にパラメーターに応じて動的に検索条件を付与したSQLを実行するものを作った。
通常通りにカーソル変数にSQLを設定するのではなく、動的にSQL文字列を構築した上でカーソル変数に設定するといける。
以下つくったものを簡単にしたサンプル。
CREATE OR REPLACE FUNCTION FNC_GetValue(param in VARCHAR2) RETURN VARCHAR2 IS
--静的SQL
STATIC_SQL CONSTANT VARCHAR2(1000) := "SELECT table1.hoge, table2.hoge FROM table1, table2 WHERE table1.key = table2.key";
--辺値格納
retVal VARCHAR(100);
--SQL格納用
sql VARCHAR2(1000);
--カーソル、レコード定義
TYPE CurTyp IS REF CURSOR;
TYPE ResultRec IS RECORD (
hoge1 table1.hoge%TYPE,
hoge2 table2.hoge%TYPE
);
csr CurTyp;
rec ResultRec;
BEGIN
--パラメータによってSQL文字列を動的に構築
sql := STATIC_SQL;
IF param IS NOT NULL THEN
sql := sql || 'AND table1.key = ' || param;
END IF;
-- SQLの結果を基に辺値を作成する
OPEN csr FOR sql;
LOOP
FETCH csr INTO rec;
EXIT WHEN csr%NOTFOUND;
-- retValを組み立てる(割愛)
END LOOP;
CLOSE csr;
RETURN retVal;
END;