type c_result is ref cursor;
sqlStr := 'select * from table';open c_result is sqlStr;
for c1 in c_result loop
dbms_output.put(c1.id);
end loop;为什么系统一直提示c_result未定义呢?难道for loop 不支持ref cursor吗?
忘高手指教
sqlStr := 'select * from table';open c_result is sqlStr;
for c1 in c_result loop
dbms_output.put(c1.id);
end loop;为什么系统一直提示c_result未定义呢?难道for loop 不支持ref cursor吗?
忘高手指教
declare
type c_result is ref cursor;
c1 table_name%rowtype;
sqlStr varchar2(100):= 'select * from table';
begin
open c_result for sqlStr;
loop
fetch c_result into c1;
exit when c_result%notfound;
dbms_output.put(c1.id);
end loop;
close c_result;
end;
当你用for...时候,由于前面c_result已经被open打开了,此时就会爆c_result未定义
我是这么理解的
type c_result is ref cursor;--这个定义的是ref cursor类型
c_cur c_result;--这个才是游标变量
/*
--或者
cursor c_cur;--这个是游标变量
*/
sqlStr := 'select * from table';
open c_cur is sqlStr;
--或者使用这个for c1 in select * from table loop 前面就可以省了
for c1 in c_cur loop
dbms_output.put(c1.id);
end loop;
cur c_result;
sqlStr := 'select * from table';
rec1 table%type;
begin
open cur for sqlStr;
fetch cur into rec1.id;
while cur%found loop
dbms_output.put(cur.id);
fetch cur into rec1.id;
end loop;
end;
type c_result is ref cursor;
cur c_result;
sqlStr := 'select * from table';
rec1 table%type;
begin
open cur for sqlStr;
fetch cur into rec1.id;
while cur%found loop
dbms_output.put(cur.id);
fetch cur into rec1.id;
end loop;
close cur;
end;
c_result_cursor c_result
sqlStr := 'select * from table';open c_result_cursor for sqlStr;
for c1 in c_result_cursor loop
dbms_output.put(c1.id);
end loop;
close c_result_cursor;
会报c_result_cursor未定义的.
--你的问题用这个方法应该是可以的
for c1 in (select * from table) loop
dbms_output.put(c1.id);
end loop;
type cursorType is ref cursor;
procedure get(v_str1 out varchar2);
end TEST;create or replace package body TEST is
procedure get( v_str1 out varchar2) is
cur1 cursorType;
begin
open cur1 for select student_code from student;
for cur2 in cur1 loop
dbms_output.put(cur2.student_code);
end loop;
end get;
end TEST;我把我的代码贴出来了,报的错误如下:
Error: PLS-00221: 'CUR1' 不是过程或尚未定义 Line: 24
declare
cursor v_cur is select ename from emp;
begin
for v_name in v_cur loop
dbms_output.put_line(v_name.ename);
end loop;
end;SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLERPL/SQL 过程已成功完成。
declare
type c_result is ref cursor;
v_cur c_result;
v_name emp.ename%type;
sqlstr varchar2(100):= 'select ename from emp';
begin
open v_cur for sqlstr;
loop
fetch v_cur into v_name;
exit when v_cur%notfound;
dbms_output.put_line(v_name);
end loop;
close v_cur;
end;SQL> /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLERPL/SQL 过程已成功完成。
loop
fetch xxx into ....;
exit when xxx%NOTFOUND;
DBMS_OUTPUT.PUT_LINE();
end loop;