请教怎么在Oracle存储过程返回结果集？

Question

CREATE OR REPLACE procedure IBCS.PR_QianFeiQingDan(
    s_st in varchar, --参数只指定类型，不指定大小
    s_tjfs in varchar,
    s_YM in varchar
)
is
    orderCondition varchar(128);
    joinCondition varchar(128);
    field varchar(30);
    condition varchar(128);
    s_sql varchar(4000); --sql是oracle保留字，不能用作变量名称
    ym varchar(32);
    s_YM1 varchar(30);
    s_st1 varchar(30);
    --变量声明放在is的后面begin的前面
BEGIN
    condition:='';
    joinCondition:='';
    s_YM1:=s_YM;
    s_st1:=s_st;
    
    if s_YM is null then
        begin
            s_YM1:=to_char(sysdate,'yyyymm');
        end; --分号
    end if;    if s_st is null then
        begin
            s_st1:='00';
        end; --分号
    end if;
    if s_st<>'00' then
        begin
            condition:=condition||' and a.s_st='||s_st1||'';
        end;--分号
    end if;    if s_tjfs='0' then--收费方式
        begin
            field:=',S_ShouFeiMC s_sffs';
            orderCondition:='order by S_ShouFeiMC';
            joinCondition:=' left join KG_YongHuSFFS c on a.i_sffs=c.I_ShouFeiBH ';
        end;--分号
    end if;
    if s_tjfs='1' then--用水性质
        begin
            field:=',S_FeiLeiMC s_sffs';
            orderCondition:='order by S_FeiLeiMC';
            joinCondition:=' left join JG_YongShuiFL c on a.I_JH=c.I_JH and a.I_TJH=c.I_TiaoJiaH ';
        end;--分号
    end if;    if s_tjfs='2' then--册本
        begin
            field:=',S_CH s_sffs';
            orderCondition:=' order by S_CH ';
        end;--分号
    end if;    if s_tjfs='3' then--抄表员
        begin
            field:=',Name s_sffs';
            orderCondition:=' order by Name ';
            joinCondition:=' left join kg_cebenxx c on a.S_Ch=c.S_Ch
                left join CB_ChaoBiaoYXX e on S_ChaoBiaoYBH=e.S_YuanGongBH';
        end;--分号
    end if;
    
    
    ym:=substr(s_YM1,1,4)||'年'||substr(s_YM1,5,2)||'月';--分号
    s_sql:='select  a.s_cid'||field||'
                                   ,a.s_hm
                                   ,a.s_dz
                                   ,to_char(i_y*100+i_m) ym
                                   ,a.n_je,a.N_YingShouZNJ,b.S_LianXiDH
                                   ,nvl(a.n_je,0) count
                                   ,''||ym||'' time
                                   , case  when S_ZhanDianMC is null then ''供水总公司'' else S_ZhanDianMC end st
                                   ,row_number() over('||orderCondition||') id
                ,case '''||s_tjfs||''' when ''0'' then ''收费方式'' when ''1'' then ''用水性质''
                        when ''2'' then ''册本号'' else ''抄表员'' end tjfs
                        from zw_yingyez a
                        left join kg_kehuxx b on a.s_cid=b.s_cid '||joinCondition||'
                        left join sx_zhandianxx d on d.s_st='||s_st1||'
                        where a.i_jlzt=0 and a.N_JE>0 and i_xiaozhang=0 and to_char(i_y*100+i_m)='||s_YM1||''||condition;--分号
     dbms_output.put_line(s_sql);
     execute immediate s_sql;--分号,用execute immediate
     
END PR_QianFeiQingDan;
/

Answer 1

create or replace procedure prc
AS
sqlstr varchar2(3000);
type resultcur is ref cursor;
l_cursor resultcur ;
begin
sqlstr := 'select * from table名 where 条件';
open l_cursor for sqlstr; ---此处报错
......
end prc; 

Answer 2

-- 给个例子给你：
-- i_address 是存储过程的输入参数，o_cur是存储过程的输出游标参数，用以获取返回的结果集!
CREATE OR REPLACE PROCEDURE userinfo_proc(i_address VARCHAR2, o_cur OUT SYS_REFCURSOR)
IS
  sqlstr VARCHAR2(200); -- 定义变量，用以存放SQL语句
BEGIN
  sqlstr := 'SELECT Id, Name, Sex, Age, Address FROM userinfo WHERE Address = :i_address'; -- 给SQL变量赋值，其中 :i_address 是绑定变量，以提高执行效率!
  OPEN o_cur FOR sqlstr USING i_address; -- 给游标变量赋值
END;
/

Answer 3

Oracle返回数据集使用cursor
请问我上面的存储过程，怎么改动一下，可以返回数据集呢？

Answer 4

CREATE OR REPLACE procedure IBCS.PR_QianFeiQingDan(
    s_st in varchar, --参数只指定类型，不指定大小
    s_tjfs in varchar,
    s_YM in varchar,
    o_cur OUT SYS_REFCURSOR
)
is
    orderCondition varchar(128);
    joinCondition varchar(128);
    field varchar(30);
    condition varchar(128);
    s_sql varchar(4000); --sql是oracle保留字，不能用作变量名称
    ym varchar(32);
    s_YM1 varchar(30);
    s_st1 varchar(30);
    --变量声明放在is的后面begin的前面
BEGIN
    condition:='';
    joinCondition:='';
    s_YM1:=s_YM;
    s_st1:=s_st;
    
    if s_YM is null then
        begin
            s_YM1:=to_char(sysdate,'yyyymm');
        end; --分号
    end if;    if s_st is null then
        begin
            s_st1:='00';
        end; --分号
    end if;
    if s_st<>'00' then
        begin
            condition:=condition||' and a.s_st='||s_st1||'';
        end;--分号
    end if;    if s_tjfs='0' then--收费方式
        begin
            field:=',S_ShouFeiMC s_sffs';
            orderCondition:='order by S_ShouFeiMC';
            joinCondition:=' left join KG_YongHuSFFS c on a.i_sffs=c.I_ShouFeiBH ';
        end;--分号
    end if;
    if s_tjfs='1' then--用水性质
        begin
            field:=',S_FeiLeiMC s_sffs';
            orderCondition:='order by S_FeiLeiMC';
            joinCondition:=' left join JG_YongShuiFL c on a.I_JH=c.I_JH and a.I_TJH=c.I_TiaoJiaH ';
        end;--分号
    end if;    if s_tjfs='2' then--册本
        begin
            field:=',S_CH s_sffs';
            orderCondition:=' order by S_CH ';
        end;--分号
    end if;    if s_tjfs='3' then--抄表员
        begin
            field:=',Name s_sffs';
            orderCondition:=' order by Name ';
            joinCondition:=' left join kg_cebenxx c on a.S_Ch=c.S_Ch
                left join CB_ChaoBiaoYXX e on S_ChaoBiaoYBH=e.S_YuanGongBH';
        end;--分号
    end if;
    
    
    ym:=substr(s_YM1,1,4)||'年'||substr(s_YM1,5,2)||'月';--分号
    s_sql:='select  a.s_cid'||field||'
                                   ,a.s_hm
                                   ,a.s_dz
                                   ,to_char(i_y*100+i_m) ym
                                   ,a.n_je,a.N_YingShouZNJ,b.S_LianXiDH
                                   ,nvl(a.n_je,0) count
                                   ,''||ym||'' time
                                   , case  when S_ZhanDianMC is null then ''供水总公司'' else S_ZhanDianMC end st
                                   ,row_number() over('||orderCondition||') id
                ,case '''||s_tjfs||''' when ''0'' then ''收费方式'' when ''1'' then ''用水性质''
                        when ''2'' then ''册本号'' else ''抄表员'' end tjfs
                        from zw_yingyez a
                        left join kg_kehuxx b on a.s_cid=b.s_cid '||joinCondition||'
                        left join sx_zhandianxx d on d.s_st='||s_st1||'
                        where a.i_jlzt=0 and a.N_JE>0 and i_xiaozhang=0 and to_char(i_y*100+i_m)='||s_YM1||''||condition;--分号
     dbms_output.put_line(s_sql);
     OPEN o_cur FOR s_sql;--分号,用execute immediate
     
END PR_QianFeiQingDan;
/

Answer 5

------------------------ 测试：---------------------------
set serveroutput on;
var c_cur refcursor;
exec IBCS.PR_QianFeiQingDan('参数1','参数2', '参数3', :c_cur); 
print c_cur;

Answer 6

Oracle就懂一点语法
多向你学习
多指教

Answer 7

照着画了，最后这句OPEN o_cur FOR s_sql；画错了。^_^

Answer 8

-- 我不是把测试代码都给你了吗？-- 没有输出，那表示没有符合要求的记录行呗!------------------------ 测试：---------------------------
set serveroutput on;
var c_cur refcursor;
exec IBCS.PR_QianFeiQingDan('参数1','参数2', '参数3', :c_cur); 
print c_cur;

Answer 9

-- 你用同样条件的SQL语句去执行看看，看有没有符合要求的记录行嘛!
-- 见过悲哀的，没你过你这样悲哀的!
-- 呵呵：打击你一下下......

Answer 10

select  a.s_cid,S_ShouFeiMC s_sffs
                                   ,a.s_hm
                                   ,a.s_dz
                                   ,to_char(i_y*100+i_m) ym
                                   ,a.n_je,a.N_YingShouZNJ,b.S_LianXiDH
                                   ,nvl(a.n_je,0) count
                                   ,'2010年05月' time
                                   , case  when S_ZhanDianMC is null then '供水总公司' else S_ZhanDianMC end st
                                   ,row_number() over(order by S_ShouFeiMC) id
                ,case '0' when '0' then '收费方式' when '1' then '用水性质'
                        when '2' then '册本号' else '抄表员' end tjfs
                        from zw_yingyez a
                        left join kg_kehuxx b on a.s_cid=b.s_cid  left join KG_YongHuSFFS c on a.i_sffs=c.I_ShouFeiBH 
                        left join sx_zhandianxx d on d.s_st=10
                        where a.i_jlzt=0 and a.N_JE>0 and i_xiaozhang=0 and to_char(i_y*100+i_m)=201005 and a.s_st=10
打印的这条SQL，执行是有结果集的。

Answer 11

这些代码拷贝执行就会报错：ORA-00922:选项确实或无效