存储过程向其中的游标传值的难解问题，单独执行可以取到数据，而放在存储过程中进行传值取不到数据

Question

CREATE OR REPLACE PROCEDURE proc_test(                             
   a              IN       INTEGER,  
   b              IN       VARCHAR2,      
   errnum         OUT      NUMBER
)
AS
   v_form_rec   tabletest%ROWTYPE;
   retval       NUMBER (6);
   
BEGIN
   retval := 0;
   DECLARE
      CURSOR rowtable_cursor
      IS
         SELECT * FROM tabletest  WHERE id= a AND id_code IN (
         SELECT id_code FROM tabletest1  WHERE SUBSTR (idNum, b, 1) = '1') 
         ORDER BY id;
   BEGIN
      OPEN rowtable_cursor;      FETCH rowtable_cursor
       INTO v_form_rec;      WHILE (rowtable_cursor%FOUND)
      LOOP
          //some method
      end loop;
end proc_test;
就是说通过传值后的rowtable_cursor中的sql语句放在sql editor里是有数据的，但是放在存储过程中却取不到数据，不能执行loop循环，望达人相助！解决即给分！

Answer 1

CREATE OR REPLACE PROCEDURE proc_test(                             
   a              IN       INTEGER,  
   b              IN       VARCHAR2,      
   errnum         OUT      NUMBER
)
AS
   v_form_rec   tabletest%ROWTYPE;
   retval       NUMBER (6);   CURSOR rowtable_cursor
     IS
     SELECT *
       FROM tabletest
      WHERE id = a
        AND id_code IN (SELECT id_code FROM tabletest1  WHERE SUBSTR (idNum, b, 1) = '1') 
      ORDER BY id;   
BEGIN
   retval := 0;
      OPEN rowtable_cursor;
      LOOP
      FETCH rowtable_cursor
       INTO v_form_rec;      EXIT WHEN rowtable_cursor%NOTFOUND;
          //some method
      end loop;
      close rowtable_cursor;
end proc_test;

Answer 2

SELECT * FROM tabletest  WHERE id= a AND id_code IN (
         SELECT id_code FROM tabletest1  WHERE SUBSTR (idNum, b, 1) = '1') 
         ORDER BY id;
分步跟踪，到游标这里我将a和b替换成跟踪的值，然后在sql－editor里执行是有数据，而存储过程里却没有得到数据，晕，我的oracle是10g

Answer 3

是不是在sql－editor里才添加的数据,没有提交呢?

Answer 4

这样说吧，我传进去的a是2，b是1，语句SELECT * FROM tabletest  WHERE id= 2 AND id_code IN (
         SELECT id_code FROM tabletest1  WHERE SUBSTR (idNum, 1, 1) = '1') 
         ORDER BY id;是能查出来值的，但是通过存储过程传值进去游标就查不出来了，？？？？？？？

Answer 5

在while循环体内还需要fetch,否则之执行一次

Answer 6

楼主贴的代码没有循环执行体,看不出意图,必须知道你循环里要做什么才好说;
注意在循环中必须要执行fetch操作循环才会继续下去;
注意循环退出时v_form_rec为空,如果你是要在循环后取最后一条的某个要素进行操作的话,那么这样的写法永远都取不到值,必须再取一个变量在循环中传递值才行;
oracle10g中类似楼主的语法写存储过程没问题的,我写过n多了,实在不行建议你简化一下操作跟踪一下,或者把建表sql和过程sql都贴出来看看

Answer 7

以下脚本实现将a字段为输入参数的最大的b字段的值插入到测试表中的例子,楼主可以自己贴到sqlplus或pl/sql的command window下看结果
create table test_001(a number,b number);
insert into test_001 select 1,11 from dual;
insert into test_001 select 1,22 from dual;
commit;
select * from test_001;
create procedure pro_test(x number)
is
  cursor cur_test is select b from test_001 where a = x order by b;
  v_n1 number;
  v_n2 number;
begin
  loop
   fetch cur_test into v_n1;
   exit when cur_test%notfound;
    v_n2 := v_n1;
  end loop;
  insert into test_001 select 2,v_n2 from dual;
  commit;
end;
/
execute pro_test(1);
select * from test_001;

Answer 8

错了,应该这样:
create table test_001(a number,b number);
insert into test_001 select 1,11 from dual;
insert into test_001 select 1,22 from dual;
commit;
select * from test_001;
create procedure pro_test(x number)
is
  cursor cur_test is select b from test_001 where a = x order by b;
  v_n1 number;
  v_n2 number;
begin
  open 
  loop cur_test ;
   fetch cur_test into v_n1;
   exit when cur_test%notfound;
    v_n2 := v_n1;
  end loop;
  close cur_test;
  insert into test_001 select 2,v_n2 from dual;
  commit;
end;
/
execute pro_test(1);
select * from test_001;

Answer 9

CREATE OR REPLACE PROCEDURE proc_test(                             
   a              IN       INTEGER,  
   b              IN       VARCHAR2,      
   errnum         OUT      NUMBER
)
AS
   v_form_rec   tabletest%ROWTYPE;
   retval       NUMBER (6);
   RefCur    IS REF CURSOR;
   rowtable_cursor RefCur;
   
BEGIN
   retval := 0;
/*
   DECLARE
      CURSOR rowtable_cursor
      IS
         SELECT * FROM tabletest  WHERE id= a AND id_code IN (
         SELECT id_code FROM tabletest1  WHERE SUBSTR (idNum, b, 1) = '1') 
         ORDER BY id;
   BEGIN
      OPEN rowtable_cursor;
*/
      OPEN rowtable_cursor FOR 'SELECT * FROM tabletest  WHERE id= :a AND id_code IN (' ||
                               ' SELECT id_code FROM tabletest1  WHERE SUBSTR (idNum, :b, 1) = ''1'') ' ||
                               ' ORDER BY id'
                           USING a, b;
      FETCH rowtable_cursor
       INTO v_form_rec;      WHILE (rowtable_cursor%FOUND)
      LOOP
          //some method
      end loop;
end proc_test;