我写了一个存储过程P_1(IDGroup => v_IDGroup,csr_1 => v_csr_1) 返回一个游标 该游标是 open csr_1 for select * from Temp_LIST1;(那个表Temp_LIST1是一个临时表)我又写了一个存储过程P_2(IDGroup2 => v_IDGroup2,csr_2 => v_csr_2) 这个存储过程调用了P_1 将P_1返回的游标给了v_csr_2然后我给P_2写测试语句 声明了一个变量 v_row Temp_LIST1%rowtype; v_csr_test sys_refcursor;
结果测试的时候调用P_2
在fetch v_csr_test into v_row; 这句说结果集变量或查询返回的类型不匹配
可是我在那两个存储过程里面跟踪写语句 都是Temp_LIST1%rowtype 都可以取的出行啊 参数都给的 in out
怎么在测试语句就不行了?是不是因为多次嵌套调用游标就会有问题???
结果测试的时候调用P_2
在fetch v_csr_test into v_row; 这句说结果集变量或查询返回的类型不匹配
可是我在那两个存储过程里面跟踪写语句 都是Temp_LIST1%rowtype 都可以取的出行啊 参数都给的 in out
怎么在测试语句就不行了?是不是因为多次嵌套调用游标就会有问题???
你这个过程怎么实现的?
是不是 p_1(v_IDGroup2, v_csr_2);
begin
open p_cur for
select * from emp;
end;
create or replace procedure p_2(p_cur out sys_refcursor) as
l_cur sys_refcursor;
begin
p_1(l_cur);
p_cur := l_cur;
end;9iSQL> var l_mycur refcursor;
9iSQL> exec p_2(:l_mycur);PL/SQL procedure successfully completed.9iSQL> print l_mycur; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 1101 17-DEC-80 800 20
7499 ALLEN SALESMAN 1101 20-FEB-81 1600 300 30
7521 WARD SALESMAN 1101 22-FEB-81 1250 500 30
7566 JONES MANAGER 1101 02-APR-81 2975 20
7654 MARTIN SALESMAN 1101 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 1101 01-MAY-81 2850 30
7782 CLARK MANAGER 1101 09-JUN-81 2450 10
7788 SCOTT ANALYST 1101 19-APR-87 3000 20
l_emprow emp%rowtype;
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
l_mycur emp_cur_type;
begin
p_2(l_mycur); loop
fetch l_mycur
into l_emprow;
exit when l_mycur%notfound;
dbms_output.put_line(l_emprow.empno);
end loop;
end;
我和他都用的ORACLE 10G 我在P_2 存储过程里面循环游标结果就没问题 可以显示出来
但是在最外的测试语句执行就报那个错误 那应该是传递问题吧?
难道v_csr_test不是和v_csr_2一样都是指向那个游标结果集的吗?
v_csr_test sys_refcursor; 看我上面的例子:TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE; 这样,打印出来就没有问题,你可以在你的scott下试运行一下
create or replace procedure P_Global_HeadList
(
Head_ID_Group in out varchar2, --指标集
csr_HeadList in out sys_refcursor --返回结果集
)
is
StartIndex NUMBER;
EndIndex NUMBER;
IDValue NUMBER;
begin
if (Head_ID_Group is null) then
return;
end if;
StartIndex :=1;
EndIndex :=0;
if(substr(Head_ID_Group,-1,1) <> ',') then
Head_ID_Group := Head_ID_Group || ',';
end if;
EndIndex := INSTR(Head_ID_Group,',');
commit;
while(EndIndex >0) LOOP
begin
IDValue := to_number(substr(Head_ID_Group,StartIndex,EndIndex-StartIndex));
execute immediate 'Insert into Temp_HEADLIST(HEAD_ID,TABLE_HEAD,HEAD_NAME,HEAD_ALIAS_NAME) select HEAD_ID,TABLE_HEAD,HEAD_NAME,HEAD_ALIAS_NAME from TBL_Config_HeadList where HEAD_ID=' || IDValue;
StartIndex := EndIndex + 1;
EndIndex := INSTR(Head_ID_Group,',',StartIndex+1);
end;
end loop;
open csr_HeadList for select * from Temp_HEADLIST;
end P_Global_HeadList;
create or replace procedure P_Func_GetIDList
(
IDFlag in out varchar2 --ID类型:HEAD,COLUMN
,TableHead in out varchar2 --表头
,varType in out varchar2 --类型
,csr_list in out sys_refcursor --返回游标集合
)
is
IDGroup varchar2(2000);
--v_row temp_headlist%rowtype;
begin if (IDFlag='HEAD') then
goto lbl_HEAD;
end if;
if (IDFlag='COLUMN') then
goto lbl_COLUMN;
end if; return;<<lbl_HEAD>>
select ID_Group into IDGroup from TBL_Config_PageHeadList where Table_Head=TableHead and OBJ_Type=varType;
p_global_headlist(head_id_group => IDGroup,
csr_headlist => csr_list);
/*
loop
begin
fetch csr_list into v_row;
exit when csr_list%notfound;
dbms_output.put_line(v_row.HEAD_NAME);
dbms_output.put_line(v_row.HEAD_ALIAS_NAME);
end;
end loop;
*/
return;
<<lbl_COLUMN>>
select ID_Group into IDGroup from TBL_Config_PageColumnList where Table_Head=TableHead and OBJ_Type=varType;
p_global_columnlist(column_id_group => IDGroup,
csr_idlist => csr_list);
return;
end P_Func_GetIDList;