Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE create or replace procedure P_GetPerSumData
as
con_num number default 1;
cursor v_test is select a.dprtid,a.dprtname,b.no4,b.no5,b.no6,b.no7,b.no8,b.no9,b.no10,b.no11,b.no12,b.no13,b.no14,b.no15,b.no16--游标内容,读取数据
from dprtcode a,bsperpay b
where a.dprtid=b.dprtid and b.rownumber=1 order by a.dprtid;
v_dno dprtcode.dprtid%type;--游标返回的参数
v_dname dprtcode.dprtname%type;
con_no4 bsperpay.no4%type;
con_no5 bsperpay.no5%type;
con_no6 bsperpay.no6%type;
con_no7 bsperpay.no7%type;
con_no8 bsperpay.no8%type;
con_no9 bsperpay.no9%type;
con_no10 bsperpay.no10%type;
con_no11 bsperpay.no11%type;
con_no12 bsperpay.no12%type;
con_no13 bsperpay.no13%type;
con_no14 bsperpay.no14%type;
con_no15 bsperpay.no15%type;
con_no16 bsperpay.no16%type;
begin
open v_test;--打开游标
fetch v_test into v_dno,v_dname,con_no4,con_no5,con_no6,con_no7,con_no8,con_no9,con_no10,con_no11,con_no12,con_no13,con_no14,con_no15,con_no16;
/*提取游标内容*/
while v_test%found loop--循环输出、读取游标数据
DBMS_OUTPUT.ENABLE (200000);/*增大buffer值,防止ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes*/
dbms_output.put_line(con_num||', ' ||v_dno||', '||v_dname||', '||con_no4||', '||con_no5||', '||con_no6||', '||con_no7||', '||con_no8||', '||con_no9||', '||con_no10||', '||con_no11||', '||con_no12||', '||con_no13||', '||con_no14||', '||con_no15||', '||con_no16);--输出
fetch v_test into v_dno,v_dname,con_no4,con_no5,con_no6,con_no7,con_no8,con_no9,con_no10,con_no11,con_no12,con_no13,con_no14,con_no15,con_no16;
con_num:=con_num+1;
end loop;
close v_test;
end;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24432966/viewspace-723523/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24432966/viewspace-723523/