这段sql语句块能直接在pl/sql中执行吗

xiedi1209 2011-01-05 05:42:36



declare 

cursor overtime_corsor is select overtimesum,applyno from lhcovertimedetail where overtimedate='20110103' and type=1;

overtimesum lhcovertimedetail.overtimedate%type;

applyno lhcovertimedetail.applyno%type;



begin 

open overtime_corsor;

loop

    fetch overtime_corsor into overtimesum,applyno;

    EXIT WHEN overtime_corsor%NOTFOUND;  

    update UserOverTimeInfo set holidaysum=(holidaysum+overtimesum) where applyno=applyno;

    update UserOverTimeInfo set generalsum=(UserOverTimeInfo-overtimesum) where applyno=applyno;

end loop;

close overtime_corsor;

我想把这段直接拷贝在sql window中执行，可以吗？而且这段代码好像也有错误，比如在我



update UserOverTimeInfo set holidaysum=(holidaysum+overtimesum) where applyno=applyno;

=号右边的overtimesum和applyno是不是也不能这么直接用阿？求指教，平时很少用到语句块

...全文

152 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

xiedi1209 2011-01-05

打赏
举报

[Quote=引用 3 楼 minitoy 的回复:]
sql windows是可以执行匿名块的
[/Quote]

受教了谢谢

xiedi1209 2011-01-05

打赏
举报

[Quote=引用 2 楼 wkc168 的回复:]
引用 1 楼 wkc168 的回复:
引用楼主 xiedi1209 的回复:
SQL code

declare
cursor overtime_corsor is select overtimesum,applyno from lhcovertimedetail where overtimedate='20110103' and type=1;
overtimesum lhcover……
[/Quote]

仔细想想表面上很多是粗心大意实际想想还是语法不熟悉阿调试成功了

minitoy 2011-01-05

打赏
举报

sql windows是可以执行匿名块的

心中的彩虹 2011-01-05

打赏
举报

[Quote=引用 1 楼 wkc168 的回复:]
引用楼主 xiedi1209 的回复:
SQL code

declare
cursor overtime_corsor is select overtimesum,applyno from lhcovertimedetail where overtimedate='20110103' and type=1;
overtimesum lhcovertimedetail.overtimed……
[/Quote]

generalsum=(UserOverTimeInfo-v_overtimesum) UserOverTimeInfo类型的匹配问题有否

心中的彩虹 2011-01-05

打赏
举报

[Quote=引用楼主 xiedi1209 的回复:]
SQL code

declare
cursor overtime_corsor is select overtimesum,applyno from lhcovertimedetail where overtimedate='20110103' and type=1;
overtimesum lhcovertimedetail.overtimedate%type;
applyno l……
[/Quote]















declare 

cursor overtime_corsor is select overtimesum,applyno from lhcovertimedetail where overtimedate='20110103' and type=1;

v_overtimesum lhcovertimedetail.overtimedate%type;

v_applyno lhcovertimedetail.applyno%type;



begin 

open overtime_corsor;

loop

    fetch overtime_corsor into v_overtimesum,v_applyno;

    EXIT WHEN overtime_corsor%NOTFOUND;  

    update UserOverTimeInfo set holidaysum=(holidaysum+v_overtimesum) where applyno=v_applyno;

    update UserOverTimeInfo set generalsum=(UserOverTimeInfo-v_overtimesum) where applyno=v_applyno;

end loop;

close overtime_corsor;





















---or

update UserOverTimeInfo set holidaysum=(holidaysum+v_overtimesum) where applyno=v_applyno;

    update UserOverTimeInfo set generalsum=(UserOverTimeInfo-v_overtimesum) where applyno=v_applyno;



改成下面也可以



update UserOverTimeInfo set holidaysum=(holidaysum+v_overtimesum),generalsum=(UserOverTimeInfo-v_overtimesum)  where applyno=v_applyno;