在网上看过很多方法,都不适用,最后使用游标实现!
CREATE OR REPLACE PROCEDURE PROC_LONG_CALL AS
v_stu_count int; --统计信息数量
V_ERROR_INFO VARCHAR2(4000);
BEGIN
DECLARE
CURSOR contract_cur IS
SELECT "LSBH" as LSBH, "ZYNR" as ZYNR
from VW_CRMJXSHT@dblink视图名称 l;
BEGIN
--循环数据
for contract_row in contract_cur loop
select count(*)
into v_stu_count
from FSP_CMS.cms_agency_contract v
where v.serial_number = contract_row.LSBH;
--判断是否有相应数据,如果有则update此记录,否则insert数据。
if v_stu_count = 1 then
update FSP_CMS.cms_agency_contract v
set v.CRMHTGL_ZYNR = contract_row.ZYNR
where v.serial_number = contract_row.LSBH;
end if;
end loop;
end;
commit;
--异常
EXCEPTION
WHEN OTHERS THEN
NULL;
ROLLBACK;
END;