oracle中关于for循环循环游标问题

--用Scott用户的emp表为数据源，编写分页的存储过程，要求传入第几页和每一页显示数量，输出游标。
--（提示：可在程序包中定义游标类型，再声明游标变量作为输出参数）
--定义
create or replace package mypack
is
       type mytype is ref cursor;
       procedure alls(e_row number,e_page number,mycur out mytype);
end;

--主体

create or replace package body mypack
is
       
       procedure alls(e_row number,e_page number,mycur out mytype)
        is
        begin
         open mycur for select * from (select rownum rn,emp.* from emp)e where rn>=(e_page-1)*e_row+1 and rn<=e_page*e_row;
        end;
end;


--调用
declare 
 my mypack.mytype;
 
 
begin
 mypack.alls(3,3,my);
 
     for eno in my  --(这里提示my不是过程或尚未定义,但我认为my是一个游标)
     loop     
       dbms_output.put_line(eno.ename);
     end loop;
end;

8 个解决方案

#1

type mytype is ref cursor;
ref cursor用于定义游标变量,所以mytype是游标变量
my mypack.mytype;
所以my 也是游标变量
楼主你到底想问什么呢...

#2

create or replace procedure emp_page_proc(
  i_page in number default 1,     -- 页码
  i_pagesize in number default 5, -- 页尺寸
  i_deptno in emp.deptno%type,    -- 部门号参数(为空时，输出所有部门的员工)
  o_cur out sys_refcursor
)
as
  v_sql varchar2(4000);
begin
  v_sql := 'select t.empno, t.ename, t.job, t.mgr, t.hiredate, t.sal, t.comm, t.deptno from ( ';
  v_sql := v_sql ||' select row_number() over(order by deptno, empno) as rcn, empno, ename, job, mgr, hiredate, sal, comm, deptno from emp ';
  v_sql := v_sql ||' where deptno=:i_deptno or :i_deptno is null) t where t.rcn>(:v_page -1) * :v_pagesize and rcn<=:v_page * :v_pagesize ';
  open o_cur for v_sql using i_deptno, i_deptno, i_page, i_pagesize, i_page,i_pagesize;
end;
/


set serveroutput on;
var c_cur refcursor;
execute emp_page_proc(1,2,30,:c_cur);
print c_cur;

#3

-- 贴一个用包返回结果集的例子给你，敬请参考：


create or replace package pkg_mobileFrends_op
as
  type myrctype is ref cursor; --定义游标用以 查询 返回好友手机号
  procedure mobileFrends_in_proc(v_mobile in varchar2,v_frendsMobile in clob,v_frendsName clob);   --添加 好友手机号
  procedure mobileFrends_de_proc(v_mobile in varchar2,v_frendsMobile in clob);   --删除 部分好友手机号
  procedure mobileFrends_deall_proc(v_mobile in varchar2);                       --删除 所有好友手机号
  procedure mobileFrends_se_proc(v_mobile in varchar2,p_rc out myrctype);        --查询 好友手机号
  procedure mobileFrends_seb_proc(v_fromMobile in varchar2, v_toMobile in varchar2, p_rc out myrctype);           --查询 好友手机号(按 手机号 区间) 
  procedure mobileFrends_se2_proc(v_mobile in varchar2,p_rc out myrctype);       --查询 好友手机号，包括其好友状态
  procedure mobileFrends_se2b_proc(v_fromMobile in varchar2, v_toMobile in varchar2, p_rc out myrctype);          --查询 好友手机号，包括其好友状态(按 手机号 区间) 
  procedure mobileFrends_se3_proc(v_frendmobile in varchar2,p_rc out myrctype);  --根据好友手机号 查询 宿主手机号(查看有哪些人将这个手机号加为了好友)
  procedure mobileFrends_se3b_proc(v_fromFrendMobile in varchar2, v_toFrendMobile in varchar2, p_rc out myrctype); --根据好友手机号 查询 宿主手机号(查看有哪些人将这个手机号加为了好友)(按 手机号 区间)
end pkg_mobileFrends_op;
/

------------------------------------------------

create or replace package body pkg_mobileFrends_op
as
  --添加 好友手机号
  procedure mobileFrends_in_proc(v_mobile varchar2,v_frendsMobile clob,v_frendsName clob)
  is
    v_frendsMobile_str clob;
    v_frendsName_str clob;
    v_frendMobile varchar2(20);
    v_frendName varchar2(100);
    v_dot_var1 NUMBER(18,0);
    v_dot_var2 NUMBER(18,0);
  begin
    v_frendsMobile_str := v_frendsMobile||',';
    v_frendsName_str := v_frendsName||',';
    while length(v_frendsMobile_str)>1 loop
    begin
      v_dot_var1 := instr(v_frendsMobile_str,',',1,1);
      v_dot_var2 := instr(v_frendsName_str,',',1,1);
      v_frendMobile := substr(v_frendsMobile_str,1,v_dot_var1-1);
      v_frendName := substr(v_frendsName_str,1,v_dot_var2-1);
      v_frendsMobile_str := substr(v_frendsMobile_str,v_dot_var1+1,length(v_frendsMobile_str)-v_dot_var1);
      v_frendsName_str := substr(v_frendsName_str,v_dot_var2+1,length(v_frendsName_str)-v_dot_var2);
      insert into mobileFrends_tmp_proc(mobile,frendMobile,frendName) values(v_mobile,v_frendMobile,v_frendName);
    end;
    end loop;
  insert into mobileFrends(mobile,frendMobile,frendName)
  select t.mobile, t.frendMobile, t.frendName
    from mobileFrends_tmp_proc t
   where t.frendMobile is not null
     and not exists ( select 1
		        from mobileFrends m
		       where m.mobile=t.mobile
		         and m.frendMobile=t.frendMobile );
   commit;
  end mobileFrends_in_proc;

  --删除 部分好友手机号
  procedure mobileFrends_de_proc(v_mobile varchar2,v_frendsMobile clob)
  is
    v_frendsMobile_str clob;
    v_frendMObile varchar2(20);
    v_dot_var NUMBER(18,0);
  begin
    v_frendsMobile_str := v_frendsMobile||',';
    while length(v_frendsMobile_str)>1 loop
    begin
      v_dot_var := instr(v_frendsMobile_str,',',1,1);
      v_frendMobile := substr(v_frendsMobile_str,1,v_dot_var-1);
      v_frendsMobile_str := substr(v_frendsMobile_str,v_dot_var+1,length(v_frendsMobile_str)-v_dot_var);
      insert into mobileFrends_tmp_proc(mobile,frendMobile) values(v_mobile,v_frendMobile);
    end;
    end loop;
  delete from mobileFrends
   where mobile=v_mobile
     and frendMobile in ( select t.frendMobile from mobileFrends_tmp_proc t );
   commit;
  end mobileFrends_de_proc;

  --删除 所有好友手机号
  procedure mobileFrends_deall_proc(v_mobile varchar2)
  is
    sqlstr VARCHAR2(400);
  begin
    if v_mobile is not null then
      sqlstr := 'delete from mobileFrends where mobile = :v_mobile';
      execute immediate sqlstr using v_mobile;
      commit;
    end if;
  end mobileFrends_deall_proc;

  --查询 好友手机号
  procedure mobileFrends_se_proc(v_mobile in varchar2,p_rc out myrctype)
  is
    sqlstr VARCHAR2(400);
  begin
    sqlstr := 'SELECT frendmobile FROM mobileFrends WHERE mobile = :v_mobile';
    OPEN p_rc FOR sqlstr USING v_mobile;
  end mobileFrends_se_proc;
  --查询 好友手机号2(按 手机号区间)
  procedure mobileFrends_seb_proc(v_fromMobile in varchar2, v_toMobile in varchar2, p_rc out myrctype)
  is
    sqlstr VARCHAR2(400);
  begin
    sqlstr := 'SELECT frendmobile FROM mobileFrends WHERE mobile >= :v_fromMobile AND mobile <= :v_toMobile';
    OPEN p_rc FOR sqlstr USING v_fromMobile, v_toMobile;
  end mobileFrends_seb_proc;

  --查询 好友手机号，包括其好友状态
  procedure mobileFrends_se2_proc(v_mobile in varchar2,p_rc out myrctype)
  is
    sqlstr VARCHAR2(400);
  begin
    sqlstr := 'SELECT m.mobile,m.frendmobile,nvl(u.state,-1) as u_state,u.sign,u.md5 FROM mobileFrends m left join u_state u on m.frendmobile=u.mobile WHERE m.mobile = :v_mobile';
    OPEN p_rc FOR sqlstr USING v_mobile;
  end mobileFrends_se2_proc;
  --查询 好友手机号，包括其好友状态2(按 手机号区间)
  procedure mobileFrends_se2b_proc(v_fromMobile in varchar2, v_toMobile in varchar2, p_rc out myrctype)
  is
    sqlstr VARCHAR2(400);
  begin
    sqlstr := 'SELECT m.mobile,m.frendmobile,nvl(u.state,-1) as u_state,u.sign,u.md5,u.CUSTOMIZE_STATUS FROM mobileFrends m left join u_state u on m.frendmobile=u.mobile WHERE m.mobile >= :v_fromMobile AND m.mobile <= :v_toMobile';
    OPEN p_rc FOR sqlstr USING v_fromMobile, v_toMobile;
  end mobileFrends_se2b_proc;

  --根据好友手机号 查询 宿主手机号(查看有哪些人将这个手机号加为了好友)
  procedure mobileFrends_se3_proc(v_frendmobile in varchar2,p_rc out myrctype)
  is
    sqlstr VARCHAR2(400);
  begin
    sqlstr := 'SELECT m.mobile,m.frendmobile FROM mobileFrends m WHERE m.frendmobile = :v_frendmobile';
    OPEN p_rc FOR sqlstr USING v_frendmobile;
  end mobileFrends_se3_proc;
  --根据好友手机号 查询 宿主手机号2(查看有哪些人将这个手机号加为了好友) (按 手机号区间)
  procedure mobileFrends_se3b_proc(v_fromFrendMobile in varchar2, v_toFrendMobile in varchar2, p_rc out myrctype)
  is
    sqlstr VARCHAR2(400);
  begin
    sqlstr := 'SELECT m.mobile,m.frendmobile FROM mobileFrends m WHERE EXISTS ( SELECT t.mobile FROM u_state t WHERE t.mobile=m.frendmobile AND t.mobile >= :v_fromFrendMobile AND t.mobile <= :v_toFrendMobile )';
    OPEN p_rc FOR sqlstr USING v_fromFrendMobile, v_toFrendMobile;
  end mobileFrends_se3b_proc;

end pkg_mobileFrends_op;
/

-------------------------------------------------- 测试 ：------------------------
var p_rc refcursor;

exec pkg_mobileFrends_op.mobileFrends_se2b_proc('13834570306','13834570399',:p_rc);
print p_rc;

#4

我想问的是它为什么会在
 for eno in my --(这里提示my不是过程或尚未定义,但我认为my是一个游标)

这里报错，因为我也认为my是一个游标呀，它出错了

#5

for  in 的语法是使用某个sql语句打开一个cursor，你的my是一个已经打开的cursor，所以不能用for in。
使用loop
    fetch my into 变量列表;
    exit when my%nofound;
    处理过程;
    end loop;
看看。

#6

declare  
  my mypack.mytype;
  V_RN       NUMBER(10);
  V_EMPNO    NUMBER(10); 
  V_ENAME    VARCHAR2(3000);
  V_JOB      VARCHAR2(3000);
  V_MGR      NUMBER(10); 
  V_HIREDATE DATE;      
  V_SAL      NUMBER(7,2);
  V_COMM     NUMBER(7,2);
  V_DEPTNO   NUMBER(2);
 
 
begin
 mypack.alls(3,3,my);
  
  loop  
  FETCH my INTO V_RN,V_EMPNO,V_ENAME,V_JOB,V_MGR,V_HIREDATE,V_SAL,V_COMM,V_DEPTNO;
  EXIT WHEN my%NOTFOUND; 
  dbms_output.put_line(V_ENAME);
  end loop;
end;

#7

SQL> declare
  2    my mypack.mytype;
  3    V_RN       NUMBER(10);
  4    V_EMPNO    NUMBER(10);
  5    V_ENAME    VARCHAR2(3000);
  6    V_JOB      VARCHAR2(3000);
  7    V_MGR      NUMBER(10);
  8    V_HIREDATE DATE;
  9    V_SAL      NUMBER(7,2);
 10    V_COMM     NUMBER(7,2);
 11    V_DEPTNO   NUMBER(2);
 12  
 13  
 14  begin
 15   mypack.alls(3,3,my);
 16  
 17    loop
 18    FETCH my INTO V_RN,V_EMPNO,V_ENAME,V_JOB,V_MGR,V_HIREDATE,V_SAL,V_COMM,V_DEPTNO;
 19    EXIT WHEN my%NOTFOUND;
 20    INSERT INTO tt1 VALUES(v_ename);
 21    end loop;
 22  end;
 23  /
 
PL/SQL procedure successfully completed
 
SQL> select * from tt1;
 
ENAME
--------------------
CLARK
SCOTT
KING
 
SQL> 

#8

引用 3 楼 luoyoumou 的回复:

SQL code
-- 贴一个用包返回结果集的例子给你，敬请参考：


create or replace package pkg_mobileFrends_op
as
  type myrctype is ref cursor; --定义游标用以 查询 返回好友手机号
  procedure mobileFrends_in_proc(v_mobile in varchar2,v_f……

大俠 我頂
學習來了