AnalyticDB:Oracle アプリケーションから AnalyticDB for PostgreSQL への移行

REVERSE を伴う FOR LOOP

PL/SQL と PL/pgSQL では、REVERSE キーワードの動作が異なります。

• PL/SQL：FOR i IN REVERSE 1..3 は 3 から 1 へカウントダウンします

• PL/pgSQL：FOR i IN REVERSE 1..3 は 1 から 3 へカウントダウンします（逆方向）

変換時にループ境界値を入れ替えます。

Oracle：

FOR i IN REVERSE 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(i));
END LOOP;

AnalyticDB for PostgreSQL：

-- 境界値を入れ替え：1..3 → 3..1；DBMS_OUTPUT.PUT_LINE → RAISE
FOR i IN REVERSE 3..1 LOOP
    RAISE '%', i;
END LOOP;

PRAGMA 文

PRAGMA 文はサポートされていません。すべての PRAGMA 文を削除します。

トランザクション制御

AnalyticDB for PostgreSQL の関数では、関数本体内で BEGIN、COMMIT、ROLLBACK を使用できません。以下のいずれかの戦略を適用します。

• 関数本体からトランザクション制御文を削除し、関数呼び出しの外側で実行します。

• COMMIT または ROLLBACK の境界ごとに、関数を個別の関数に分割します。

EXECUTE（動的 SQL）

AnalyticDB for PostgreSQL では動的 SQL がサポートされますが、Oracle との違いがあります。

• USING 構文はサポートされていません。パラメーターを SQL 文字列に直接連結します。

• データベース識別子は quote_ident で、文字列値は quote_literal で囲みます。

Oracle：

EXECUTE 'UPDATE employees_temp SET commission_pct = :x' USING a_null;

AnalyticDB for PostgreSQL：

-- USING を quote_literal を使った文字列連結に置き換え
EXECUTE 'UPDATE employees_temp SET commission_pct = ' || quote_literal(a_null);

PIPE ROW 関数

PIPE ROW 関数は、RETURNS SETOF <type> および RETURN NEXT を使用したテーブル関数に置き換えます。

Oracle：

TYPE pair IS RECORD(a int, b int);
TYPE numset_t IS TABLE OF pair;

FUNCTION f1(x int) RETURN numset_t PIPELINED IS
DECLARE
    v_p pair;
BEGIN
    FOR i IN 1..x LOOP
        v_p.a := i;
        v_p.b := i+10;
        PIPE ROW(v_p);
    END LOOP;
    RETURN;
END;

SELECT * FROM f1(10);

AnalyticDB for PostgreSQL：

-- RECORD 型 → CREATE TYPE；PIPE ROW → RETURN NEXT；PIPELINED → RETURNS SETOF
CREATE TYPE pair AS (a int, b int);

CREATE OR REPLACE FUNCTION f1(x int) RETURNS SETOF pair AS
$$
DECLARE
    rec pair;
BEGIN
    FOR i IN 1..x LOOP
        rec := row(i, i+10);
        RETURN NEXT rec;
    END LOOP;
    RETURN;
END
$$
LANGUAGE plpgsql;

SELECT * FROM f1(10);

例外処理

• 例外をスローするには RAISE ステートメントを使用します。

• 例外がキャッチされた後、関数内ではトランザクションをロールバックできません。ロールバックはユーザー定義関数の外側でのみ許可されます。

サポートされるエラーコードについては、「PostgreSQL エラーコード リファレンス」をご参照ください。

RETURN および OUT 引数を備えた関数

関数は、RETURN 引数と OUT 引数を同時に使用できません。RETURN 引数を追加の OUT 引数に変換し、SELECT * FROM test_func(...) INTO rec を使用して戻り値を取得します。

Oracle：

CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int) RETURNS varchar(10)
AS $body$
BEGIN
    out_id := id + 1;
    RETURN name;
END
$body$
LANGUAGE PLPGSQL;

AnalyticDB for PostgreSQL：

-- RETURN 値を OUT 引数（out_name）に変換
CREATE OR REPLACE FUNCTION test_func(id int, name varchar(10), out_id out int, out_name out varchar(10))
AS $body$
BEGIN
    out_id := id + 1;
    out_name := name;
END
$body$
LANGUAGE PLPGSQL;

-- 両方の出力値を取得
SELECT * FROM test_func(1, '1') INTO rec;

動的 SQL 文字列連結におけるシングルクォート

文字列変数にシングルクォートが含まれている場合（例：adb'-'pg）、直接連結すると、ハイフンが演算子と解釈されて構文解析エラーが発生します。quote_literal を使用して値を安全に埋め込みます。

Oracle：

sql_str := 'SELECT * FROM test1 WHERE col1 = ' || param1 || ' AND col2 = ''' || param2 || ''' AND col3 = 3';

AnalyticDB for PostgreSQL：

-- quote_literal により param2 が安全にラップされ、埋め込まれたクォートが処理されます
sql_str := 'SELECT * FROM test1 WHERE col1 = ' || param1 || ' AND col2 = ' || quote_literal(param2) || ' AND col3 = 3';

2 つのタイムスタンプ間の日数

Oracle：

SELECT to_date('2019-06-30 16:16:16') - to_date('2019-06-29 15:15:15') + 1 INTO v_days FROM dual;

AnalyticDB for PostgreSQL：

-- extract() を使用して間隔から整数日数を取得
SELECT extract('days' FROM '2019-06-30 16:16:16'::timestamp - '2019-06-29 15:15:15'::timestamp + '1 days'::interval)::int INTO v_days;

RECORD 型

Oracle の RECORD 型は、複合型に変換します。

Oracle：

TYPE rec IS RECORD (a int, b int);

AnalyticDB for PostgreSQL：

CREATE TYPE rec AS (a int, b int);

ネストテーブルおよび可変サイズ配列

PL 変数として、NESTED TABLE および VARRAY は、PostgreSQL の ARRAY 型に対応します。

Oracle：

DECLARE
    TYPE Roster IS TABLE OF VARCHAR2(15);
    names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
BEGIN
    FOR i IN names.FIRST .. names.LAST LOOP
        IF names(i) = 'J Hamil' THEN
            DBMS_OUTPUT.PUT_LINE(names(i));
        END IF;
    END LOOP;
END;

AnalyticDB for PostgreSQL：

-- TABLE OF → varchar[]；配列インデックス：names(i) → names[i]；FIRST..LAST → 1..array_length
CREATE OR REPLACE FUNCTION f1() RETURNS VOID AS
$$
DECLARE
    names varchar(15)[] := '{"D Caruso", "J Hamil", "D Piro", "R Singh"}';
    len int := array_length(names, 1);
BEGIN
    FOR i IN 1..len LOOP
        IF names[i] = 'J Hamil' THEN
            RAISE NOTICE '%', names[i];
        END IF;
    END LOOP;
    RETURN;
END
$$
LANGUAGE plpgsql;

SELECT f1();

ネストテーブルが関数の戻り値として使用される場合は、テーブル関数（RETURNS SETOF <type>）を使用します。

連想配列

AnalyticDB for PostgreSQL には Oracle の連想配列に対応する機能はありません。この構文は設計の見直しが必要です。

グローバル変数

AnalyticDB for PostgreSQL ではグローバル変数はサポートされていません。パッケージレベルのグローバル変数は一時テーブルに格納し、アクセサ関数を定義します。

-- グローバル変数を一時テーブルに格納
-- 注：id 列は分散キーであり、変更できません
CREATE TEMPORARY TABLE global_variables (
    id int,
    g_count int,
    g_set_id varchar(50),
    g_err_code varchar(100)
);

INSERT INTO global_variables VALUES(0, 1, null, null);

-- ゲッター関数
CREATE OR REPLACE FUNCTION get_variable() RETURNS SETOF global_variables AS
$$
DECLARE
    rec global_variables%rowtype;
BEGIN
    EXECUTE 'SELECT * FROM global_variables' INTO rec;
    RETURN NEXT rec;
END;
$$
LANGUAGE plpgsql;

-- セッター関数
CREATE OR REPLACE FUNCTION set_variable(IN param varchar(50), IN value anyelement) RETURNS void AS
$$
BEGIN
    EXECUTE 'UPDATE global_variables SET ' || quote_ident(param) || ' = ' || quote_literal(value);
END;
$$
LANGUAGE plpgsql;

グローバル変数を変更するには：

-- 呼び出し元関数内で tmp_rec レコードを宣言
SELECT * FROM set_variable('g_err_code', 'error'::varchar) INTO tmp_rec;

グローバル変数を読み取るには：

SELECT * FROM get_variable() INTO tmp_rec;
error_code := tmp_rec.g_err_code;

CONNECT BY

階層クエリのための CONNECT BY 句は、AnalyticDB for PostgreSQL には直接対応する SQL 構文がありません。階層を反復的に走査する PL/pgSQL 関数として再記述します。

Oracle：

SELECT emp_id, lead_id, emp_name, PRIOR emp_name AS lead_name, salary
FROM employee
START WITH lead_id = 0
CONNECT BY PRIOR emp_id = lead_id;

AnalyticDB for PostgreSQL（反復的走査関数）：

CREATE OR REPLACE FUNCTION f1(tablename text, lead_id int, nocycle boolean) RETURNS SETOF employee AS
$$
DECLARE
    idx int := 0;
    res_tbl varchar(265) := 'result_table';
    prev_tbl varchar(265) := 'tmp_prev';
    curr_tbl varchar(256) := 'tmp_curr';
    current_result_sql varchar(4000);
    tbl_count int;
    rec record;
BEGIN
    EXECUTE 'TRUNCATE ' || prev_tbl;
    EXECUTE 'TRUNCATE ' || curr_tbl;
    EXECUTE 'TRUNCATE ' || res_tbl;
    LOOP
        -- 現在の階層レベルをクエリし、tmp_curr に挿入
        current_result_sql := 'INSERT INTO ' || curr_tbl || ' SELECT t1.* FROM ' || tablename || ' t1';

        IF idx > 0 THEN
            current_result_sql := current_result_sql || ', ' || prev_tbl || ' t2 WHERE t1.lead_id = t2.emp_id';
        ELSE
            current_result_sql := current_result_sql || ' WHERE t1.lead_id = ' || lead_id;
        END IF;
        EXECUTE current_result_sql;

        -- nocycle が false の場合、既に走査済みの行を削除
        IF nocycle IS FALSE THEN
            EXECUTE 'DELETE FROM ' || curr_tbl || ' WHERE (lead_id, emp_id) IN (SELECT lead_id, emp_id FROM ' || res_tbl || ')';
        END IF;

        -- 行がない場合に終了
        EXECUTE 'SELECT count(*) FROM ' || curr_tbl INTO tbl_count;
        EXIT WHEN tbl_count = 0;

        -- 現在の結果を result および prev テーブルに昇格
        EXECUTE 'INSERT INTO ' || res_tbl || ' SELECT * FROM ' || curr_tbl;
        EXECUTE 'TRUNCATE ' || prev_tbl;
        EXECUTE 'INSERT INTO ' || prev_tbl || ' SELECT * FROM ' || curr_tbl;
        EXECUTE 'TRUNCATE ' || curr_tbl;
        idx := idx + 1;
    END LOOP;

    FOR rec IN EXECUTE 'SELECT * FROM ' || res_tbl LOOP
        RETURN NEXT rec;
    END LOOP;
    RETURN;
END
$$
LANGUAGE plpgsql;

ROWNUM

結果件数の制限：

Oracle：

SELECT * FROM t WHERE rownum < 10;

AnalyticDB for PostgreSQL：

SELECT * FROM t LIMIT 10;

行番号の生成：

Oracle：

SELECT rownum, * FROM t;

AnalyticDB for PostgreSQL：

SELECT row_number() OVER() AS rownum, * FROM t;

DUAL テーブル

オプション 1 — `FROM DUAL` を削除：

Oracle：

SELECT sysdate FROM dual;

AnalyticDB for PostgreSQL：

SELECT current_timestamp;

オプション 2 — `dual` テーブルを作成：

CREATE TABLE dual (dummy varchar(1));
INSERT INTO dual VALUES ('X');

SELECT ステートメント内のユーザー定義関数

AnalyticDB for PostgreSQL では、SQL ステートメントを含むユーザー定義関数を SELECT 内で呼び出すことはできません。実行するとセグメントエラーが発生します。

ERROR: function cannot execute on segment because it accesses relation "public.t2"

このような関数は、SQL 式またはサブクエリに変換します。

Oracle：

CREATE OR REPLACE FUNCTION f1(arg int) RETURN int IS
    v int;
BEGIN
    SELECT b INTO v FROM t2 WHERE a = arg;
    RETURN v;
END;

SELECT a, f1(b) FROM t1;

AnalyticDB for PostgreSQL：

-- 関数ロジックを JOIN としてインライン化
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.b = t2.a;

OUTER JOIN (+)

2 テーブルの外部結合：

Oracle：

SELECT * FROM a, b WHERE a.id = b.id(+);

AnalyticDB for PostgreSQL：

SELECT * FROM a LEFT JOIN b ON a.id = b.id;

3 テーブルの外部結合：

まず 2 つのテーブルを CTE（共通テーブル式）で結合し、その後 RIGHT OUTER JOIN と coalesce を適用します。

Oracle：

SELECT * FROM test1 t1, test2 t2, test3 t3
WHERE t1.col1(+) BETWEEN NVL(t2.col1, t3.col1) AND NVL(t3.col1, t2.col1);

AnalyticDB for PostgreSQL：

WITH cte AS (
    SELECT t2.col1 AS low, t2.col2, t3.col1 AS high, t3.col2 AS c2
    FROM t2, t3
)
SELECT * FROM t1
RIGHT OUTER JOIN cte ON t1.col1 BETWEEN coalesce(cte.low, cte.high) AND coalesce(cte.high, cte.low);

MERGE INTO

ほとんどの場合、MERGE INTO を INSERT ON CONFLICT に置き換えます。INSERT ON CONFLICT で対応できない場合は、ストアドプロシージャを使用します。

• INSERT ON CONFLICT のドキュメント

• ストアドプロシージャのドキュメント

シーケンス

Oracle：

CREATE SEQUENCE seq1;
SELECT seq1.nextval FROM dual;

AnalyticDB for PostgreSQL：

CREATE SEQUENCE seq1;
SELECT nextval('seq1');

カーソル

基本的なカーソル走査 はサポートされています。OPEN／FETCH／CLOSE パターンを使用します。

Oracle：

FUNCTION test_func() IS
    CURSOR data_cursor IS SELECT * FROM test1;
BEGIN
    FOR i IN data_cursor LOOP
        -- process i
    END LOOP;
END;

AnalyticDB for PostgreSQL：

CREATE OR REPLACE FUNCTION test_func()
AS $body$
DECLARE
    data_cursor CURSOR FOR SELECT * FROM test1;
    i record;
BEGIN
    OPEN data_cursor;
    LOOP
        FETCH data_cursor INTO i;
        IF NOT FOUND THEN
            EXIT;
        END IF;
        -- process i
    END LOOP;
    CLOSE data_cursor;
END;
$body$
LANGUAGE PLPGSQL;

再帰関数内での同名カーソル はサポートされていません。代わりに FOR i IN <query> を使用します。

Oracle：

FUNCTION test_func(level IN number) IS
    CURSOR data_cursor IS SELECT * FROM test1;
BEGIN
    IF level > 5 THEN RETURN; END IF;
    FOR i IN data_cursor LOOP
        -- process i
        test_func(level + 1);
    END LOOP;
END;

AnalyticDB for PostgreSQL：

-- 同名カーソルを削除；再帰をサポートするためにインラインクエリを使用
CREATE OR REPLACE FUNCTION test_func(level int) RETURNS void
AS $body$
DECLARE
    i record;
BEGIN
    IF level > 5 THEN
        RETURN;
    END IF;
    FOR i IN SELECT * FROM test1 LOOP
        -- process i
        PERFORM test_func(level + 1);
    END LOOP;
END;
$body$
LANGUAGE PLPGSQL;