postgresql - 使用 Postgresql 获取 BULK COLLECT INTO
问题描述
我们正在将 ORacle 迁移到 Postgresql 并进行转换,我使用 AWS 工具并在 FETCH BULK COLLECT INTO 子句中,它要求我们执行手动转换 - 不确定这在 Postgresql 中如何工作,因为它对我来说很新。你能帮帮我吗?
这是代码:
CREATE OR REPLACE PROCEDURE abc_schemaname.USP_Std_SSIS(p_file_id text)
LANGUAGE plpgsql
AS $procedure$
/*
PROCEDURE abc_schemaname.USP_Std_SSIS
*/
/* Declare Cursor */
DECLARE
aws_oracle_ext$array_id$temporary BIGINT;
activity_cur CURSOR FOR
/* Fetch records from activity dump ssis Table Based On file_id */
SELECT
*
FROM abc_schemaname.tbl_activity_dump_ssis
WHERE file_id = p_file_id;
l_ACTIVITY VARCHAR(100) := 'l_activity';
BEGIN
/* Open the Cursor. */
aws_oracle_ext$array_id$temporary := aws_oracle_ext.array$create_array('l_activity', 'abc_schemaname.USP_Std_SSIS');
PERFORM aws_oracle_ext.array$add_fields_to_array(aws_oracle_ext$array_id$temporary, '[{"file_id":"CHARACTER VARYING(128)"},{"data_vendor_id":"CHARACTER VARYING(32)"},{"data_vendor_sub_id":"CHARACTER VARYING(32)"},{"base_platform_num":"DOUBLE PRECISION"},{"activity_code":"CHARACTER VARYING(32)"},{"activity_name":"CHARACTER VARYING(64)"},{"effective_date":"TIMESTAMP(0) WITHOUT TIME ZONE"},{"activity_level1":"CHARACTER VARYING(1024)"},{"activity_level2":"CHARACTER VARYING(1024)"},{"activity_level3":"CHARACTER VARYING(1024)"},{"activity_level4":"CHARACTER VARYING(1024)"},{"activity_level5":"CHARACTER VARYING(1024)"},{"participant_id":"CHARACTER VARYING(32)"},{"data_vendor_alt_id":"CHARACTER VARYING(32)"},{"file_creation_date":"TIMESTAMP(0) WITHOUT TIME ZONE"},{"inc_value":"DOUBLE PRECISION"}]');
OPEN activity_cur;
/* fill the cursor */
/*
[5647 - Severity CRITICAL - FETCH BULK COLLECT INTO is not supported. Perform a manual conversion.]
FETCH ACTIVITY_CUR
BULK COLLECT INTO l_ACTIVITY
*/
/* loop through the cursor */
fetch from activity_cur INTO l_ACTIVITY;
raise notice 'ACT_CURSOR VALUE - %', l_ACTIVITY;
FOR INDX IN 1..aws_oracle_ext.array$count('l_activity', 'abc_schemaname.USP_Std_SSIS')
loop
-- raise notice 'base_platform-num - %',l_ACTIVITY[indx].base_platform_num;
BEGIN
CALL abc_schemaname.usp_ins_standard_act(l_ACTIVITY[indx].file_id, l_ACTIVITY[indx].base_platform_num, l_ACTIVITY[indx].participant_id /* eci */, l_ACTIVITY[indx].data_vendor_id, l_ACTIVITY[indx].activity_level5, l_ACTIVITY[indx].activity_code, aws_oracle_ext.TRUNC(l_ACTIVITY[indx].effective_date), aws_oracle_ext.TRUNC(l_ACTIVITY[indx].file_creation_date), l_ACTIVITY[indx].inc_value);
END;
END LOOP;
*/;
/* close the cursor */
CLOSE activity_cur;
RAISE DEBUG USING MESSAGE = CONCAT_WS('', 'No Of Records Processed :', aws_oracle_ext.array$count('l_ACTIVITY', 'abc_schemaname.USP_Std_SSIS'));
/* clear the dump table for the file in question */
DELETE FROM abc_schemaname.tbl_activity_dump_ssis
WHERE file_id = p_file_id;
PERFORM aws_oracle_ext.array$clear_procedure('abc_schemaname.USP_Std_SSIS');
END;
$procedure$
;
解决方案
推荐阅读
- python-3.x - 算法和运行时分析
- java - 将模型类加载到 Kotlin 类
- firebase - 有没有办法在点击建议筹码时暂停媒体对象 - 而媒体对象正在播放?
- wordpress - WordPress 搜索小部件原生在相关页面上构建不同的查询字符串
- mysql - 从 MySQL json 行获取统计信息
- javascript - 如果另一个功能处于活动状态,则启动一个功能并仅激活一些复选框 - JQuery
- google-maps - 绘制路线并根据以角度 6 绘制的路径自动放大/缩小 Google 地图
- windows - 使用 Apache 在 Windows 上托管 ASP.NET Core
- c# - 如何以编程方式将样式添加到 C# 中的 UserControl 资源?
- windows - 从字符串中批量删除双引号的正确方法