实际工作场景为 业务顾问给出的逻辑是Oracle库的游标,现在需要在SAP HANA中做一样的游标以更新一张自定义表。HANA SQL与 ORACLE SQL有些许的不同之处,记录与此。
oracle cursor:
declare cursor sap_company_cursor is
select distinct b.proc_inst_id,
m.approvable,
m.approval_comment,
b.status,m.act_name
from mss_ds.wf_message_done m, mss_ds.wf_biz_proc_inst b
where m.proc_inst_id = b.proc_inst_id
;
begin
for mc in sap_company_cursor loop
if (instr(mc.act_name,'会计') > 0 and mc.approvable=0) then
insert into temp_gu_bank
(dan, hao, yijian, ben,jingjishixiang)
values
(mc.proc_inst_id, mc.approvable, mc.approval_comment, mc.status,mc.act_name);
end if;
end loop;
end;
HANA cursor:
DO
BEGIN
DECLARE CURSOR sap_company_cursor FOR
select distinct b.proc_inst_id,
m.approvable,
m.approval_comment,
b.status,m.act_name
from mss_bzpt_gd_p.wf_message_done m, mss_bzpt_gd_p.wf_biz_proc_inst b
where m.proc_inst_id = b.proc_inst_id
;
FOR mc AS sap_company_cursor DO
IF (LOCATE(mc.act_name,'会计') > 0 AND mc.approvable=0) THEN
insert into "XXXX"."TEMP_GU_BANK"
(dan, hao, yijian, ben,jingjishixiang)
values
(mc.proc_inst_id, mc.approvable, mc.approval_comment, mc.status,mc.act_name);
END IF;
END FOR;
END;