create or replace procedure test
IS
Type MyRefCur IS Ref Cursor;
vRefCur MyRefCur;
vtemp vRefCur%ROWTYPE;
Begin
OPEN vRefCur FOR Select * from cmcs_res_bts_area;
Close vRefCur;
end test;
Error: PLS-00320: 此表达式的类型说明不完整或格式不正确
Line: 5
Text: vtemp vRefCur%ROWTYPE;Error: PL/SQL: Item ignored
Line: 5
Text: vtemp vRefCur%ROWTYPE;Error: Hint: Variable 'vtemp' is declared but never used in 'test'
Line: 5
Text: vtemp vRefCur%ROWTYPE;
IS
Type MyRefCur IS Ref Cursor;
vRefCur MyRefCur;
vtemp vRefCur%ROWTYPE;
Begin
OPEN vRefCur FOR Select * from cmcs_res_bts_area;
Close vRefCur;
end test;
Error: PLS-00320: 此表达式的类型说明不完整或格式不正确
Line: 5
Text: vtemp vRefCur%ROWTYPE;Error: PL/SQL: Item ignored
Line: 5
Text: vtemp vRefCur%ROWTYPE;Error: Hint: Variable 'vtemp' is declared but never used in 'test'
Line: 5
Text: vtemp vRefCur%ROWTYPE;
IS
Type MyRefCur IS Ref Cursor;
vRefCur MyRefCur;
Begin
OPEN vRefCur FOR Select * from cmcs_res_bts_area;
Close vRefCur;
end test; 这样就可以了
如果不定义vtemp vRefCur%ROWTYPE;我怎么来循环使用它呢?
create or replace procedure test
IS
Type MyRefCur IS Ref Cursor;
vRefCur MyRefCur;
v_id NUMBER;
--vtemp vRefCur%ROWTYPE;
--vtemp varchar2(4000);
Begin
OPEN vRefCur FOR Select area_id from cmcs_res_bts_area;
Loop
FETCH vRefCur INTO v_id;
EXIT WHEN vRefCur%NOTFOUND;
DBMS_OUTPUT.put_line(v_id);
end LOOP;
Close vRefCur;
end test;我的目地就是定义为%ROWTYPE
type type_refcursor is table of cmcs_res_bts_area%rowtype;
v_refcursor type_refcursor;
在fetch的时候,就可以直接用这个type变量
举个列子:
create or replace procedure test
IS
Type MyRefCur IS Ref Cursor;
vRefCur MyRefCur;
type type_refcursor is table of cmcs_res_bts_area%rowtype;
v_refcursor type_refcursor;
Begin
OPEN vRefCur FOR Select * from cmcs_res_bts_area;
fetch vRefCur bulk collect into v_refcursor;
Close vRefCur;
for i in 1..v_refcursor.count loop
DBMS_OUTPUT.put_line(v_refcursor(i).area_id);
end loop;
end test; 仅供参考!
动态游标是不能做到真正的结果集动态的如果需要做到返回动态结果集,只能用多维数组来实现了。动态数组的方式可以参考
http://blog.csdn.net/inthirties/archive/2009/08/02/4400905.aspx
或者
http://www.inthirties.com/?p=840
type type_refcursor is table of cmcs_res_bts_area%rowtype;