给你一个例子
create or replace procedure test
as
type ref_cursor is ref cursor;
your_cursor ref_cursor;
yourvar varchar2(8);
begin
open your_cursor for select 1 from dual;
loop
fetch your_cursor into yourvar;
exit when your_cursor%notfound;
end loop;
close your_cursor;
end test;
/
create or replace procedure test
as
type ref_cursor is ref cursor;
your_cursor ref_cursor;
yourvar varchar2(8);
begin
open your_cursor for select 1 from dual;
loop
fetch your_cursor into yourvar;
exit when your_cursor%notfound;
end loop;
close your_cursor;
end test;
/
as
type t_sor is ref cursor;
end;
/
create procedure pro(p_table in varchar2,p_col in varchar2,p_rc out test_age.t_sor)
as
str varvhar2(100);
begin
str:='select '||p_col||' from '||p_table;
open p_rc for str;
end;
/
....
v_CursorID integer;
v_SumCount integer;
v_Sql varchar2(1024);
v_column1 varchar2(15);
v_column2 varchar2(15);
begin
......
for i in 0..10 loop
v_Sql:='select column1,column2'
||' from table'||to_char(i)
||' where id='||VarID
v_CursorID :=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_CursorID,v_Sql,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(v_CursorID,1 , v_column1,15);
DBMS_SQL.DEFINE_COLUMN(v_CursorID,2 , v_column2,15);
v_SumCount:=DBMS_SQL.EXECUTE(v_CursorID);
loop
if DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE(v_CursorID,1 , v_column1);
DBMS_SQL.COLUMN_VALUE(v_CursorID,2 , v_column2);
.......
.......
end loop;
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
end loop;
......
end;