目前在接觸DB2，在此將DB2中使用游標和if else ,while等收集起來進行匯總。

?

SET SCHEMA = "AUTEK";CREATE PROCEDURE "AUTEK"."SP_ADDRESS" ( OUT "V_MESSAGE" VARCHAR(50) )SPECIFIC "SQL111214105311700"LANGUAGE SQLNOT DETERMINISTICNO EXTERNAL ACTIONMODIFIES SQL DATACALLED ON NULL INPUTINHERIT SPECIAL REGISTERS BEGIN --聲明一些變量declare v_accno varchar(32); declare v_cusid varchar(32); declare v_count int; declare v_count1 int; declare v_addrseqno varchar(10); declare v_modidate varchar(15); declare v_count2 int; declare v_count3 int; select count(distinct cusid) into v_count from tempdaybak2;begindeclare v_cur cursor for select distinct cusid from tempdaybak2 for read only;open v_cur;while(v_count>0) DOFETCH v_cur into v_cusid;--通過cusno客戶號 去從dc_ci_addr_all表中查詢結(jié)果集select count(*) into v_count1 from dc_ci_addr_all where cusno=v_cusid;if (v_count1=1) then update tempdaybak2 set address=(select addr1 from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid) , mobphone=(select mobphone from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid), post=(select postcode from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid) where cusid=v_cusid; else select min(addrseqno) into v_addrseqno from dc_ci_addr_all where cusno=v_cusid;select count(*) into v_count2 from dc_ci_addr_all where cusno=v_cusid and addrseqno=v_addrseqno;if(v_count2=1) thenupdate tempdaybak2 set address=(select addr1 from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid and dc_ci_addr_all.addrseqno=v_addrseqno) , mobphone=(select mobphone from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid and dc_ci_addr_all.addrseqno=v_addrseqno), post=(select postcode from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid and dc_ci_addr_all.addrseqno=v_addrseqno) where cusid=v_cusid; elseselect max(modidate) into v_modidate from dc_ci_addr_all where cusno=v_cusid and addrseqno=v_addrseqno;select count(*) into v_count3 from dc_ci_addr_all where cusno=v_cusid and addrseqno=v_addrseqno and modidate=v_modidate;if(v_count3=1)thenupdate tempdaybak2 set address=(select addr1 from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid and dc_ci_addr_all.addrseqno=v_addrseqno and modidate=v_modidate) , mobphone=(select mobphone from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid and dc_ci_addr_all.addrseqno=v_addrseqno and modidate=v_modidate), post=(select postcode from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid and dc_ci_addr_all.addrseqno=v_addrseqno and modidate=v_modidate) where cusid=v_cusid; else--取最后一個update tempdaybak2 set address=(select addr1 from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid and dc_ci_addr_all.addrseqno=v_addrseqno and modidate=v_modidate order by autoid desc fetch first 1 rows only),mobphone=(select mobphone from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid and dc_ci_addr_all.addrseqno=v_addrseqno and modidate=v_modidate),post=(select postcode from dc_ci_addr_all where dc_ci_addr_all.cusno=v_cusid and dc_ci_addr_all.addrseqno=v_addrseqno and modidate=v_modidate) where cusid=v_cusid; end if;end if;end if;set v_count=v_count-1;end while;end; END;GRANT EXECUTE ON PROCEDURE "AUTEK"."SP_ADDRESS"( VARCHAR(50) ) TO USER "AUTEK" WITH GRANT OPTION;

總結(jié)

以上是生活随笔為你收集整理的DB2存储过程使用游标的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。