oracle存储过程通过游标输出Sql结果集

create or replace procedure testcur(STATIONID IN VARCHAR2,out_result_cursor out sys_refcursor)

is

v_sql varchar(200);

begin

v_sql:='select a.stationid,a.boxid from RPT_TVMCASHBOX a

where a.station|STATIONID;

open out_result_cursor for v_sql;

end;

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

declare

c_result sys_refcursor;

c c_result%type;

arr test_table%rowtype;

begin

testcur(c);

loop

fetch c into arr;

dbms_output.put_line(arr.boxid);

exit when c%notfound;

end loop;

close c;

end;

并发程序报错：ORA-01007: variable not in select list（选择列表中没有变量） 以及相关动态指针写法

网上的解决方法：

Cause: A reference was made to a variable not listed in the SELECT clause. In OCI, this can occur if the number passed for the position parameter is less than one or greater than the number of variables in the SELECT clause in any of the following calls: DESCRIBE, NAME, or DEFINE. In SQL*Forms or SQL*Report, specifying more variables in an INTO clause than in the SELECT clause also causes this error.

Action: Determine which of the problems listed caused the problem and take appropriate action.

select语句本身没有问题，错误原因是：在构造游标时数据个数不一致。比如，一个表TA有3个域A，B，C。我们构造查询语句为：SELECT A FROM TA WHERE B='12' AND C='12'。但是在构造游标时，使用了EXEC SQL FETCH table_cursor INTO :A,:B,:C;，而不是EXEC SQL FETCH table_cursor INTO :A;。这样变量数目就不一致，会导致错误。