Oracle存储过程迁移到Postgresql

Question

如何迁移Oracle存储过程的这一部分

FOR t IN (WITH temp_transaction(str) AS (SELECT i_transaction_id FROM dual)

到一个PostgreSQL函数？

Procedure sp_update
(
 i_mem_id IN WLVENDOR.REWARD_TRX_DETAIL.WL_MEM_ID%TYPE,
 i_rebate_detail_id  IN WLVENDOR.REWARD_TRX_DETAIL.WL_REBATE_DETAIL_ID%TYPE,
 i_transaction_id IN VARCHAR,
 i_limit_days IN NUMBER,
 o_rows_affected OUT NUMBER
)
IS
    err_msg   VARCHAR(500);
  BEGIN
   FOR t IN (WITH temp_transaction(str) AS (
     SELECT i_transaction_id FROM dual
   )
   SELECT CAST(regexp_substr(str, '(.*?)(,|$)', 1, level, NULL, 1) as Number) transactionId
   FROM temp_transaction
   CONNECT BY regexp_substr(str, '(.*?)(,|$)', 1, level) is not null) LOOP
    // UPDATE Statement
    END LOOP;
    SELECT COUNT(*) into o_rows_affected FROM WLVENDOR.REWARD_TRX_DETAIL WHERE wl_rebate_detail_id = i_rebate_detail_id;

END sp_update;

Answer 1

显然，在oracle中使用select查询将逗号分隔的字符串拆分成行。

在Postgres中，有一种技术使用string_to_array将其转换为数组，并使用unnest函数将其转换为行。

对于您的情况，您可以使用FOREACH遍历数组本身

因此，您的过程( postgres函数将如下所示

create or replace function sp_update
..  --other 
..  --statements

DECLARE
    transactionId   numeric;
    ..--other declarations.
  BEGIN
   FOREACH transactionId IN ARRAY string_to_array(i_transaction_id,',')::numeric[] as
    LOOP
    -- UPDATE Statement
    END LOOP;
  .. --other statements
  ..
END $$;

推荐的替代方法是传递一个包含多个ids的ARRAY，而不是发送逗号分隔的i_transaction_id。此外，请尝试将update查询转换为能够作为单个语句运行，而不是使用循环。