那就建立个过程返回游标:
//返回游标
CREATE OR REPLACE PACKAGE test
AS
TYPE myrctype IS REF CURSOR;
END test;
/create procedure pro(c out test.myrctype)
as
str varchar2(200);
begin
str:='select * from table_name where id =.....';
open c for str;
end;
/
//返回游标
CREATE OR REPLACE PACKAGE test
AS
TYPE myrctype IS REF CURSOR;
END test;
/create procedure pro(c out test.myrctype)
as
str varchar2(200);
begin
str:='select * from table_name where id =.....';
open c for str;
end;
/
DECLARE insert_cursor CURSOR FOR
select * from mr_user where month=@yf
OPEN insert_cursor
FETCH NEXT FROM insert_cursor INTO @value
WHILE @@FETCH_STATUS = 0
BEGIN FETCH NEXT FROM insert_cursor INTO @value
END
CLOSE insert_cursor
DEALLOCATE insert_cursor
DECLARE
C_SQL ingeger;
CURSOR C_CONS IS select * from mr_user where month=&yf;
begin
C_SQL := DBMS_SQL.OPEN_CURSOR;
OPEN C_CONS;
LOOP
BEGIN
FETCH C_CONS INTO col1,col2...;
EXIT WHEN C_CONS%NOTFOUND;
insert into table_name values (col1,col2...);
end loop;
CLOSE C_CONS;
DBMS_SQL.CLOSE_CURSOR(C_SQL);
end;
/