原来我用的是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
insert into tablea
select .. from在tablea上建立数据窗口
create or replace package types
as
type cursorType is ref cursor;
end;
-- 功能:定义一个游标类型CREATE OR REPLACE PROCEDURE proc_sample(p_curosr OUT TYPES.CURSORTYPE) AS
BEGIN
OPEN p_curosr FOR
select * from table;
END proc_sample;
--功能:返回一个表的结果集