create or replace procedure proc_report
(
p_startDate varchar2,
p_endDate varchar2,
p_wfms out varchar2,
p_province out varchar2,
p_provinces out varchar2,
p_subtotal out varchar2,
p_subtota2 out varchar2,
p_hpzl out varchar2,
p_hpzl_xx out varchar2,
p_total out varchar2
)
as
begin
select
t.wfms ,w.Province, w.Provinces,(w.Province+Provinces) ,
w.HPZL , HPZL_xx ,(w.HPZL+w.HPZL_xx), (w.Province+Provinces+w.HPZL+w.HPZL_xx)
into p_wfms,p_province ,p_provinces,p_subtotal,p_subtota2,p_hpzl,p_hpzl_xx,p_total
from
tb_codewfdm t,
(
select
wfxw,
sum(case when jc ='粤' then 1 else 0 end) Province,
sum(case when jc !='粤' then 1 else 0 end) Provinces,
sum(case when HPZL ='16' then 1 else 0 end) HPZL,
sum(case when HPZL ='02' then 1 else 0 end) HPZL_xx
from tb_vio_surveil_bd
where
WFSJ between to_date(p_startDate,'YYYY-MM-DD HH24:MI:SS')
and to_date(p_endDate,'YYYY-MM-DD HH24:MI:SS')
group by wfxw
) w
where t.wfxw=w.wfxw;
end proc_report;
/
5 个解决方案
#1
将sql放到cursor中,返回Sys_Refcursor类型数据集
#2
create or replace procedure proc_report
(
p_startDate varchar2,
p_endDate varchar2
)
as
p_wfms varchar2(50);
p_province varchar2(5);
p_provinces varchar2(5);
p_subtotal varchar2(5);
p_subtota2 varchar2(5);
p_hpzl varchar2(5);
p_hpzl_xx varchar2(5);
p_total varchar2(5);
cursor cur_a as
select
t.wfms ,w.Province, w.Provinces,(w.Province+Provinces) ,
w.HPZL , HPZL_xx ,(w.HPZL+w.HPZL_xx), (w.Province+Provinces+w.HPZL+w.HPZL_xx)
from
tb_codewfdm t,
(
select
wfxw,
sum(case when jc ='粤' then 1 else 0 end) Province,
sum(case when jc !='粤' then 1 else 0 end) Provinces,
sum(case when HPZL ='16' then 1 else 0 end) HPZL,
sum(case when HPZL ='02' then 1 else 0 end) HPZL_xx
from tb_vio_surveil_bd
where
WFSJ between to_date(p_startDate,'YYYY-MM-DD HH24:MI:SS')
and to_date(p_endDate,'YYYY-MM-DD HH24:MI:SS')
group by wfxw
) w
where t.wfxw=w.wfxw;
begin
open cur_a
loop
feach cur_a
into
p_wfms,p_province ,p_provinces,p_subtotal,p_subtota2,p_hpzl,p_hpzl_xx,p_total ;
exit when cur_a%notfound ;
end loop;
/
你再帮我看一下,我这么放有什么错误?
#3
begin
OPEN CUR_A;
LOOP
FEACH CUR_A
INTO P_WFMS, P_PROVINCE, P_PROVINCES, P_SUBTOTAL, P_SUBTOTA2, P_HPZL, P_HPZL_XX, P_TOTAL;
EXIT WHEN CUR_A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('输出的内容。。。');
END LOOP;
CLOSE CUR_A;
end;
#4
谢谢你hidanger521,解决了
#5
还可以这样实现:
create or replace package pack_cursor as
type cursorType is ref cursor;
end pack_cursor;
/
create or replace procedure proc_report
(
p_startDate varchar2,
p_endDate varchar2,
p_Information out pack_cursor.cursorType
)
as
begin
open p_Information for
select
t.wfms ,w.Province, w.Provinces,(w.Province+Provinces) ,
w.HPZL , HPZL_xx ,(w.HPZL+w.HPZL_xx), (w.Province+Provinces+w.HPZL+w.HPZL_xx)
from
tb_codewfdm t,
(
select
wfxw,
sum(case when jc ='粤' then 1 else 0 end) Province,
sum(case when jc !='粤' then 1 else 0 end) Provinces,
sum(case when HPZL ='16' then 1 else 0 end) HPZL,
sum(case when HPZL ='02' then 1 else 0 end) HPZL_xx
from tb_vio_surveil_bd
where
WFSJ between to_date(p_startDate,'YYYY-MM-DD HH24:MI:SS')
and to_date(p_endDate,'YYYY-MM-DD HH24:MI:SS')
group by wfxw
) w
where t.wfxw=w.wfxw;
end proc_report;
#1
以下存储过程只能返回一条记录的数据:
结果集比如:
要是返回多条记录的数据该如何修改?????
结果集比如:
create or replace procedure proc_report
(
p_startDate varchar2,
p_endDate varchar2,
p_wfms out varchar2,
p_province out varchar2,
p_provinces out varchar2,
p_subtotal out varchar2,
p_subtota2 out varchar2,
p_hpzl out varchar2,
p_hpzl_xx out varchar2,
p_total out varchar2
)
as
begin
select
t.wfms ,w.Province, w.Provinces,(w.Province+Provinces) ,
w.HPZL , HPZL_xx ,(w.HPZL+w.HPZL_xx), (w.Province+Provinces+w.HPZL+w.HPZL_xx)
into p_wfms,p_province ,p_provinces,p_subtotal,p_subtota2,p_hpzl,p_hpzl_xx,p_total
from
tb_codewfdm t,
(
select
wfxw,
sum(case when jc ='粤' then 1 else 0 end) Province,
sum(case when jc !='粤' then 1 else 0 end) Provinces,
sum(case when HPZL ='16' then 1 else 0 end) HPZL,
sum(case when HPZL ='02' then 1 else 0 end) HPZL_xx
from tb_vio_surveil_bd
where
WFSJ between to_date(p_startDate,'YYYY-MM-DD HH24:MI:SS')
and to_date(p_endDate,'YYYY-MM-DD HH24:MI:SS')
group by wfxw
) w
where t.wfxw=w.wfxw;
end proc_report;
/
将sql放到cursor中,返回Sys_Refcursor类型数据集
#2
以下存储过程只能返回一条记录的数据:
结果集比如:
要是返回多条记录的数据该如何修改?????
结果集比如:
create or replace procedure proc_report
(
p_startDate varchar2,
p_endDate varchar2,
p_wfms out varchar2,
p_province out varchar2,
p_provinces out varchar2,
p_subtotal out varchar2,
p_subtota2 out varchar2,
p_hpzl out varchar2,
p_hpzl_xx out varchar2,
p_total out varchar2
)
as
begin
select
t.wfms ,w.Province, w.Provinces,(w.Province+Provinces) ,
w.HPZL , HPZL_xx ,(w.HPZL+w.HPZL_xx), (w.Province+Provinces+w.HPZL+w.HPZL_xx)
into p_wfms,p_province ,p_provinces,p_subtotal,p_subtota2,p_hpzl,p_hpzl_xx,p_total
from
tb_codewfdm t,
(
select
wfxw,
sum(case when jc ='粤' then 1 else 0 end) Province,
sum(case when jc !='粤' then 1 else 0 end) Provinces,
sum(case when HPZL ='16' then 1 else 0 end) HPZL,
sum(case when HPZL ='02' then 1 else 0 end) HPZL_xx
from tb_vio_surveil_bd
where
WFSJ between to_date(p_startDate,'YYYY-MM-DD HH24:MI:SS')
and to_date(p_endDate,'YYYY-MM-DD HH24:MI:SS')
group by wfxw
) w
where t.wfxw=w.wfxw;
end proc_report;
/
将sql放到cursor中,返回Sys_Refcursor类型数据集
create or replace procedure proc_report
(
p_startDate varchar2,
p_endDate varchar2
)
as
p_wfms varchar2(50);
p_province varchar2(5);
p_provinces varchar2(5);
p_subtotal varchar2(5);
p_subtota2 varchar2(5);
p_hpzl varchar2(5);
p_hpzl_xx varchar2(5);
p_total varchar2(5);
cursor cur_a as
select
t.wfms ,w.Province, w.Provinces,(w.Province+Provinces) ,
w.HPZL , HPZL_xx ,(w.HPZL+w.HPZL_xx), (w.Province+Provinces+w.HPZL+w.HPZL_xx)
from
tb_codewfdm t,
(
select
wfxw,
sum(case when jc ='粤' then 1 else 0 end) Province,
sum(case when jc !='粤' then 1 else 0 end) Provinces,
sum(case when HPZL ='16' then 1 else 0 end) HPZL,
sum(case when HPZL ='02' then 1 else 0 end) HPZL_xx
from tb_vio_surveil_bd
where
WFSJ between to_date(p_startDate,'YYYY-MM-DD HH24:MI:SS')
and to_date(p_endDate,'YYYY-MM-DD HH24:MI:SS')
group by wfxw
) w
where t.wfxw=w.wfxw;
begin
open cur_a
loop
feach cur_a
into
p_wfms,p_province ,p_provinces,p_subtotal,p_subtota2,p_hpzl,p_hpzl_xx,p_total ;
exit when cur_a%notfound ;
end loop;
/
你再帮我看一下,我这么放有什么错误?
#3
begin
OPEN CUR_A;
LOOP
FEACH CUR_A
INTO P_WFMS, P_PROVINCE, P_PROVINCES, P_SUBTOTAL, P_SUBTOTA2, P_HPZL, P_HPZL_XX, P_TOTAL;
EXIT WHEN CUR_A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('输出的内容。。。');
END LOOP;
CLOSE CUR_A;
end;
#4
谢谢你hidanger521,解决了
#5
还可以这样实现:
create or replace package pack_cursor as
type cursorType is ref cursor;
end pack_cursor;
/
create or replace procedure proc_report
(
p_startDate varchar2,
p_endDate varchar2,
p_Information out pack_cursor.cursorType
)
as
begin
open p_Information for
select
t.wfms ,w.Province, w.Provinces,(w.Province+Provinces) ,
w.HPZL , HPZL_xx ,(w.HPZL+w.HPZL_xx), (w.Province+Provinces+w.HPZL+w.HPZL_xx)
from
tb_codewfdm t,
(
select
wfxw,
sum(case when jc ='粤' then 1 else 0 end) Province,
sum(case when jc !='粤' then 1 else 0 end) Provinces,
sum(case when HPZL ='16' then 1 else 0 end) HPZL,
sum(case when HPZL ='02' then 1 else 0 end) HPZL_xx
from tb_vio_surveil_bd
where
WFSJ between to_date(p_startDate,'YYYY-MM-DD HH24:MI:SS')
and to_date(p_endDate,'YYYY-MM-DD HH24:MI:SS')
group by wfxw
) w
where t.wfxw=w.wfxw;