存储过程向其中的游标传值的难解问题，单独执行可以取到数据，而放在存储过程中进行传值取不到数据
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循环，望达人相助！解决即给分！
[解决办法]
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;
[解决办法]
在while循环体内还需要fetch,否则之执行一次
------解决方案--------------------
楼主贴的代码没有循环执行体,看不出意图,必须知道你循环里要做什么才好说;
注意在循环中必须要执行fetch操作循环才会继续下去;
注意循环退出时v_form_rec为空,如果你是要在循环后取最后一条的某个要素进行操作的话,那么这样的写法永远都取不到值,必须再取一个变量在循环中传递值才行;
oracle10g中类似楼主的语法写存储过程没问题的,我写过n多了,实在不行建议你简化一下操作跟踪一下,或者把建表sql和过程sql都贴出来看看
[解决办法]
以下脚本实现将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;
[解决办法]
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;