原来我用的是sqlserver数据库,要是做报表的话一般的数据都在sql的存储过程算完然后select * from 表反回给pb的数据窗口,但现在用oracle了,我这么写返回值oracle的存储过程语法都不通过啊! 下面的是sql的,在存储过程中可以直接返回一个数据集,pb用这个存储过程做数据源就可以形成一个报表! 谁能给我讲讲在oracle中怎么实现存储过程返回数据集!
CREATE PROCEDURE cw_ywhzb @ldt_sdate datetime,
@ldt_edate datetime,
@ls_lx varchar(1)
AS
BEGIN
--------------------分类汇总部分------------------
if @ls_lx='1'
begin
SELECT (select fymc from jc_fylbzd where fybh = jc_fyzd.lsfybh) as fymc,
sum(zje) as je
FROM jc_fylbzd,
jc_fyzd,
mz_fpdj,
mz_cfmx
WHERE (mz_fpdj.fph = mz_cfmx.fph) and
(jc_fylbzd.fybh = mz_cfmx.fygl) and
(jc_fylbzd.fybh = jc_fyzd.fybh) and
((jc_fyzd.lbbh = '1') and
(sfsj >= @ldt_sdate and
sfsj <= @ldt_edate))
GROUP BY jc_fyzd.lsfybh
return
endif @ls_lx='2'
begin
SELECT (select fymc from jc_fylbzd where fybh = jc_fyzd.lsfybh) as fymc,
sum(zje) as je
FROM jc_fylbzd,
jc_fyzd,
zy_cfdj,
zy_cfmx
WHERE (zy_cfdj.cfh = zy_cfmx.cfh) and
(jc_fylbzd.fybh = zy_cfmx.fygl) and
(jc_fylbzd.fybh = jc_fyzd.fybh) and
((jc_fyzd.lbbh = '2') and
(hjsj >= @ldt_sdate and
hjsj <= @ldt_edate))
GROUP BY jc_fyzd.lsfybh
return
endreturn
ENDGO
CREATE PROCEDURE cw_ywhzb @ldt_sdate datetime,
@ldt_edate datetime,
@ls_lx varchar(1)
AS
BEGIN
--------------------分类汇总部分------------------
if @ls_lx='1'
begin
SELECT (select fymc from jc_fylbzd where fybh = jc_fyzd.lsfybh) as fymc,
sum(zje) as je
FROM jc_fylbzd,
jc_fyzd,
mz_fpdj,
mz_cfmx
WHERE (mz_fpdj.fph = mz_cfmx.fph) and
(jc_fylbzd.fybh = mz_cfmx.fygl) and
(jc_fylbzd.fybh = jc_fyzd.fybh) and
((jc_fyzd.lbbh = '1') and
(sfsj >= @ldt_sdate and
sfsj <= @ldt_edate))
GROUP BY jc_fyzd.lsfybh
return
endif @ls_lx='2'
begin
SELECT (select fymc from jc_fylbzd where fybh = jc_fyzd.lsfybh) as fymc,
sum(zje) as je
FROM jc_fylbzd,
jc_fyzd,
zy_cfdj,
zy_cfmx
WHERE (zy_cfdj.cfh = zy_cfmx.cfh) and
(jc_fylbzd.fybh = zy_cfmx.fygl) and
(jc_fylbzd.fybh = jc_fyzd.fybh) and
((jc_fyzd.lbbh = '2') and
(hjsj >= @ldt_sdate and
hjsj <= @ldt_edate))
GROUP BY jc_fyzd.lsfybh
return
endreturn
ENDGO
或者你可以把结果集保存到一个临时表里,再从临时表里读出来就可以了,记得每次要把临时表清空。