oracle 游标内存自动释放,oracle游标释放问题求教

LZ， 我根据你目前的代码试图用一条SQL该了一下， 你测试一下如下代码是否完全符合要求， 结果是否完全正确， 如果完全没有问题的话， 再测试一下代码的性能如何！

目前如下的代码是根据10g写得， 如果你的数据库是<10g的， 那么merge语句的"when not matched"加上即可！

CREATE OR REPLACE PROCEDURE WDKCDZ(v_ksrq  date,

v_jzrq  date,

v_CK    VARCHAR2,

v_CAOZZ VARCHAR2)

IS

BEGIN

merge into wdfbckkcdz_file t_a

using(select t1.tc_ima001,

t1.v_jsl1,

t2.v_jsl2

from (SELECT nvl(SUM(TC_OGB12), 0) v_jsl1,

a.tc_ima001

FROM TC_OGB_FILE,

tc_oga_file,

wdfbckkcdz_file a

WHERE tc_ogb01 = tc_oga01

and tc_ogapost = 'Y'

and tc_ogb04 = a.tc_ima001

and a.caozz = v_CAOZZ

AND tc_ogb09 = v_CK

AND TC_OGA02 BETWEEN TO_DATE(to_char(v_ksrq, 'yyyy-mm-dd'), 'YYYY-MM-DD') AND TO_DATE(to_char(v_jzrq, 'yyyy-mm-dd'), 'YYYY-MM-DD')

group by a.tc_ima001) t1,

(SELECT nvl(SUM(OHB12), 0) v_jsl2,

a.tc_ima001

FROM OHA_FILE,

OHB_file,

wdfbckkcdz_file a

WHERE OHB01 = OHA01

and OHAPOST = 'Y'

and OHB04 = a.tc_ima001

and a.caozz = v_CAOZZ

AND OHB09 IN (v_CK, '4' || substr(v_CK, 2, 2))

AND OHA02 BETWEEN TO_DATE(to_char(v_ksrq, 'yyyy-mm-dd'), 'YYYY-MM-DD') AND TO_DATE(to_char(v_jzrq, 'yyyy-mm-dd'), 'YYYY-MM-DD')

group by a.tc_ima001) t2

where t1.tc_ima001 = t2.tc_ima001) t_b

on(t_a.tc_ima001 = t_b.tc_ima001)

when matched then

update set t_a.jsl1 = t_b.v_jsl1, t_a.jsl2 = t_b.v_jsl2;

COMMIT;

END WDKCDZ;

[本帖最后由 bell6248 于 2011-4-25 13:45 编辑]