怎样定义这个游标？太急了

怎么搜索不到REF CURSOR的用法？请举例说明！！多谢！！up，studying......

type ref_type_cursor is REF CURSOR return tab1%rowtype;   --tab1为表名
  cur ref_type_cursor;
  rec cur_date%rowtype;
--上面是声明部分if 第一季度 then
  open cur for
    select * from tab1
elsif 第二季度 then
  ...
end if
  loop
    ...
    ...
  end loop;
这样就可以了啊。

create or replace procedure D_Proc_ToOrderMonthBalance(i_Period In Varchar) Is       type ref_type_cursor is REF CURSOR return adms_adcdr001%rowtype;
       c_GetOrder ref_type_cursor;      -- Cursor c_GetOrder Is select distinct adorderid from adms_adcdr001 group by adorderid having count(distinct adorderid)>0;
       v_OrderID Varchar(20);
begin
     if(D_Func_Getorderdetailtab(i_Period)='001') then
            open c_GetOrder for select distinct adorderid from adms_adcdr001 group by adorderid having count(distinct adorderid)>0;
     elsif(D_Func_Getorderdetailtab(i_Period)='002') then
            open c_GetOrder for select distinct adorderid from adms_adcdr002 group by adorderid having count(distinct adorderid)>0;
     elsif(D_Func_Getorderdetailtab(i_Period)='003') then
            open c_GetOrder for select distinct adorderid from adms_adcdr003 group by adorderid having count(distinct adorderid)>0;
     elsif(D_Func_Getorderdetailtab(i_Period)='004') then
            open c_GetOrder for select distinct adorderid from adms_adcdr004 group by adorderid having count(distinct adorderid)>0;
     end if;

     Fetch  c_GetOrder Into v_OrderID;
     Loop
     If c_GetOrder%Found Then
     D_Proc_OrderMonth(v_OrderID,i_Period);
     Fetch c_GetOrder Into v_OrderID;
     Elsif c_GetOrder%Notfound Then
     Exit;
     End If;
     End Loop;
     If c_GetOrder%Isopen Then Close c_GetOrder;
     End If;
end D_Proc_ToOrderMonthBalance;legumer(纳豆人)，我错在哪里？

PROCEDURE ADMS01.D_PROC_TOORDERMONTHBALANCE 编译错误错误: PLS-00382: expression is of wrong type
行: 10
文本: open c_GetOrder for select distinct adorderid from adms_adcdr001 group by adorderid having count(distinct adorderid)>0;错误: PL/SQL: SQL Statement ignored
行: 10
文本: open c_GetOrder for select distinct adorderid from adms_adcdr001 group by adorderid having count(distinct adorderid)>0;错误: PLS-00382: expression is of wrong type
行: 12
文本: open c_GetOrder for select distinct adorderid from adms_adcdr002 group by adorderid having count(distinct adorderid)>0;错误: PL/SQL: SQL Statement ignored
行: 12
文本: open c_GetOrder for select distinct adorderid from adms_adcdr002 group by adorderid having count(distinct adorderid)>0;错误: PLS-00382: expression is of wrong type
行: 14
文本: open c_GetOrder for select distinct adorderid from adms_adcdr003 group by adorderid having count(distinct adorderid)>0;错误: PL/SQL: SQL Statement ignored
行: 14
文本: open c_GetOrder for select distinct adorderid from adms_adcdr003 group by adorderid having count(distinct adorderid)>0;错误: PLS-00382: expression is of wrong type
行: 16
文本: open c_GetOrder for select distinct adorderid from adms_adcdr004 group by adorderid having count(distinct adorderid)>0;错误: PL/SQL: SQL Statement ignored
行: 16
文本: open c_GetOrder for select distinct adorderid from adms_adcdr004 group by adorderid having count(distinct adorderid)>0;错误: PLS-00394: wrong number of values in the INTO list of a FETCH statement
行: 18错误: PL/SQL: SQL Statement ignored
行: 18错误: PLS-00394: wrong number of values in the INTO list of a FETCH statement
行: 22
文本: D_Proc_OrderMonth(v_OrderID,i_Period);错误: PL/SQL: SQL Statement ignored
行: 22
文本: D_Proc_OrderMonth(v_OrderID,i_Period);

晕，
REF CURSOR比较适合用于一个表的应该。像你这样的用了好多表的，你可以根据传入的参数，判断属于哪个季度的，打开游标
if i_Period='001' then
  open...
  close...
elsif i_Period='002' then
  open..
  close..
...
end if;
这样虽然写得比较多，还是蛮清楚的。：）

纳豆人，这样是不是要定义4个游标，然后根据需要，使用其中的一个？其他3个不用？
不过我自己又有了一个其他的方法，你看对不对
create or replace procedure D_Proc_ToOrderMonthBalance(i_Period In Varchar) Is
begin
     if(D_Func_Getorderdetailtab(i_Period)='001') then
       for c_GetOrder in (select distinct adorderid from adms_adcdr001 where  to_char(visittime,'yyyymm')=i_Period  group by adorderid having count(distinct adorderid)>0 )
       loop

           D_Proc_OrderMonth(c_GetOrder.adorderid,i_Period);
       end loop;
     elsif(D_Func_Getorderdetailtab(i_Period)='002') then
       for c_GetOrder in (select distinct adorderid from adms_adcdr002 where  to_char(visittime,'yyyymm')=i_Period group by adorderid having count(distinct adorderid)>0)
       loop
           D_Proc_OrderMonth(c_GetOrder.adorderid,i_Period);
       end loop;
     elsif(D_Func_Getorderdetailtab(i_Period)='003') then
       for c_GetOrder in (select distinct adorderid from adms_adcdr003 where  to_char(visittime,'yyyymm')=i_Period group by adorderid having count(distinct adorderid)>0)
       loop
           D_Proc_OrderMonth(c_GetOrder.adorderid,i_Period);
       end loop;
     elsif(D_Func_Getorderdetailtab(i_Period)='004') then
       for c_GetOrder in (select distinct adorderid from adms_adcdr004 where  to_char(visittime,'yyyymm')=i_Period group by adorderid having count(distinct adorderid)>0)
       loop
           D_Proc_OrderMonth(c_GetOrder.adorderid,i_Period);
       end loop;
     end if;
end D_Proc_ToOrderMonthBalance;不管怎么讲，好像能行

就用你自己的方法就可以了，因为用cursor总归不大好。
赞同你的意见。正好学习一下。：）

create or replace procedure D_Proc_ToOrderMonthBalance(i_Period In Varchar)
Is
type c_GetOrder is ref cursor;
v_GetOrder c_GetOrder;
str varchar2(1000);
v_OrderID Varchar(20);
begin
str:='select distinct adorderid from decide('||i_period||',''1'',tab1,''2'',tab2,...) group by adorderid having count(distinct adorderid)>0';
open v_GetOrder for str;
loop

     Fetch  v_GetOrder Into v_OrderID;
     exit when v_GetOrder%notfound;

     D_Proc_OrderMonth(v_OrderID,i_Period);


     End Loop;
     Close v_GetOrder;

end D_Proc_ToOrderMonthBalance;

beckhambobo(beckham),我测试后发现不行create or replace procedure D_Proc_ToOrderMonthBalance(i_Period In Varchar)
Is
type c_GetOrder is ref cursor;
v_GetOrder c_GetOrder;
str varchar2(1000);
v_OrderID Varchar(20);
v_Season  varchar(3);
begin
v_Season:=d_Func_Getorderdetailtab(i_Period);--该函数是我决定用那个表的
str:='select distinct adorderid from decide('||v_Season||',''001'',adms_adcdr001,''002'',adms_adcdr002,''003'',adms_adcdr003,''004'',adms_adcdr004) group by adorderid having count(distinct adorderid)>0';
dbms_output.put_line(str);
open v_GetOrder for str;--该行出错了，错误代码：00933，SQL command not properly ended,自己不懂
loop

     Fetch  v_GetOrder Into v_OrderID;
     exit when v_GetOrder%notfound;

     D_Proc_OrderMonth(v_OrderID,i_Period);


     End Loop;
     Close v_GetOrder;

end D_Proc_ToOrderMonthBalance;传递的参数：i_Period=200403
str='select distinct adorderid from decide(001,'001',adms_adcdr001,'002',adms_adcdr002,'003',adms_adcdr003,'004',adms_adcdr004) group by adorderid having count(distinct adorderid)>0
';
decide()这个函数是怎么用的？我用过decode(),和他差不多吧，再过程中把 decide换成decode也不行，谢谢！

这个就是decode啊。不是decide。写错了。

我以前也做过类似的东西，我用union连接起来四个表，我把我写过的贴出来，希望对你有帮助。CURSOR c_MaterialID1 is
select distinct Workshop, MaterialID from Yl_fans_YarningOutSh, Yl_fans_YarningOutShDetails where Yl_fans_YarningOutSh.SheetID=Yl_fans_YarningOutShDetails.SheetID and DepotOutDate>=v_BeginDate and DepotOutDate<=v_EndDate and AuditFlag=1 union
select distinct Workshop, MaterialID from Yl_fans_YarningInSh, Yl_fans_YarningInShDetails where Yl_fans_YarningInSh.SheetID=Yl_fans_YarningInShDetails.SheetID and DepotInDate>=v_BeginDate and DepotInDate<=v_EndDate and AuditFlag=1 union
select distinct Workshop, MaterialID from Yl_fans_YarningWasteSh, Yl_fans_YarnWasteShDetails where Yl_fans_YarningWasteSh.SheetID=Yl_fans_YarnWasteShDetails.SheetID and SheetDate>=v_BeginDate and SheetDate<=v_EndDate and AuditFlag=1;

legumer(纳豆人),decode()也一样的错误，
beckhambobo(beckham)，早点出来解释一下，多谢了！jialin0626(新月冰蓝) 的方法肯定行了，但是还是想多学一点了，还是想学会beckhambobo(beckham)的方法

在出错的地方，加断点调试,比较简单的SQL语句.

leecooper0918(爱一个人好难),我都已经把那句sql打印出来了str='select distinct adorderid from decide(001,'001',adms_adcdr001,'002',adms_adcdr002,'003',adms_adcdr003,'004',adms_adcdr004) group by adorderid having count(distinct adorderid)>0
';
open v_GetOrder for str;--该行出错了，错误代码：00933，SQL command not properly ended,自己不懂

sorry, to lisoft2002,本人正测试也有些问题，测试正确后告知

str='select adorderid from decode(001,'001',adms_adcdr001,'002',adms_adcdr002,'003',adms_adcdr003,'004',adms_adcdr004) group by adorderid having count(adorderid)>0';

我怎么测试就通不过呢，我用的Oracle9.2.0.4
在 sqlplus中：
连接到:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - ProductionSQL> select adorderid from decode(001,'001',adms_adcdr001,'002',adms_adcdr002,'0
03',adms_adcdr003,'004',adms_adcdr004);
select adorderid from decode(001,'001',adms_adcdr001,'002',adms_adcdr002,'003',a
dms_adcdr003,'004',adms_adcdr004)
*
ERROR 位于第 1 行:
ORA-00933: SQL command not properly ended

调试易

怎样定义这个游标？太急了

解决方案 »