在函数中使用批量收集时，在从预言机到postgres的数据库迁移期间发出

Question

在数据库迁移期间批量收集和键入postgres函数的替代方法应该是什么？

甲骨文功能：-

create or replace 
FUNCTION FN_SL_PERF_WITH_CRIT_ANO()
RETURN NUMBER  AS
var_decl_cnt NUMBER ;
var_inter_cnt NUMBER ;
Type declaration_table is table of varchar2(100);
var_declarationId_table declaration_table;
cursor cursor1 is
SELECT distinct(DECLARATION_ID) 
FROM
(SELECT DECL.DECLARATION_ID AS DECLARATION_ID FROM T_FLAG_NRVL FLAG.DECL;
);
BEGIN
var_inter_cnt := 0;
open cursor1;
fetch cursor1 bulk collect into var_declarationId_table;
close cursor1;
var_decl_cnt := var_declarationId_table.count;
RETURN NVL(var_decl_cnt,0);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
RETURN NVL(VAR_DECL_CNT,0);
END FN_SL_PERF_WITH_CRIT_ANO;

Answer 1

经过一些搜索和语法，我使用两种方式在PostgreSQL中转换了oracle查询：-1( 使用光标 2(用于for循环

对于循环示例：-

CREATE OR REPLACE FUNCTION fn_sl_perf_without_crit_ano()
RETURNS bigint AS
$BODY$
DECLARE
var_decl_cnt bigint := 0 ;
var_inter_cnt bigint := 0 ;
v_sql_dynamic varchar :=  'SELECT distinct(DECLARATION_ID)  
FROM
(
SELECT DECL.DECLARATION_ID AS DECLARATION_ID FROM T_FLAG_NRVL FLAG.DECL;)anr';
v_sql_dynamic_count varchar :='select count(*)  from ('||v_sql_dynamic||')an1 ';
myvar  RECORD;
BEGIN
EXECUTE v_sql_dynamic_count into var_decl_cnt;
raise notice 'var_decl_cnt%',var_decl_cnt ;  
if var_decl_cnt > 0 then 
FOR myvar IN EXECUTE v_sql_dynamic
LOOP
select count(1) into var_inter_cnt from T_ANOMALY_NRVL ANOMALY where ANOMALY.DECLARATION_ID = myvar.DECLARATION_ID  and ANOMALY.RISK_LEVEL IN ('3','4'); 
if var_inter_cnt > 0  THEN
raise notice 'var_inter_cnt%',var_inter_cnt ;  
var_decl_cnt := var_decl_cnt-1;
END IF;
END LOOP;
end if ;
if(var_countdec != 0) then
var_decl_cnt:= round((var_decl_cnt/var_countdec) *100,2);
end if;
raise notice 'var_decl_cnt%',var_decl_cnt ;  
RETURN coalesce(var_decl_cnt,0) ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

希望此示例有助于转换查询。