create or replace procedure myProc as temp table%rowType; mycur as select * from table; begin open mycur; fetch mycur to temp; while mycur%notfount loop DBMS_oupput.put_Line(temp.colName); end loop; close mycur; end;
--修正一下 CREATE OR new PACKAGE SELECT_EMPLOYEES_JOBS AS TYPE T_CURSOR IS REF CURSOR; PROCEDURE GetEmployeesAndJobs ( cur_Employees OUT T_CURSOR, cur_Jobs OUT T_CURSOR ); END SELECT_EMPLOYEES_JOBS;包正文如下所示:CREATE OR new PACKAGE BODY SELECT_EMPLOYEES_JOBS AS PROCEDURE GetEmployeesAndJobs ( cur_Employees OUT T_CURSOR, cur_Jobs OUT T_CURSOR ) IS BEGIN -- return all EMPLOYEES records OPEN cur_Employees FOR SELECT * FROM Employees;-- return all JOBS records OPEN cur_Jobs FOR SELECT * FROM Jobs; END GetEmployeesAndJobs; END SELECT_EMPLOYEES_JOBS;
create or replace procedure procedure_name ( --define in/out parameter) as --define var begin--return result connection open rcursor for select * from table_name where conditiion.............;end;
create or replace procedure myProc
as
temp table%rowType;
mycur as select * from table;
begin
open mycur;
fetch mycur to temp;
while mycur%notfount loop
DBMS_oupput.put_Line(temp.colName);
end loop;
close mycur;
end;
--修正一下
CREATE OR new PACKAGE SELECT_EMPLOYEES_JOBS AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployeesAndJobs (
cur_Employees OUT T_CURSOR,
cur_Jobs OUT T_CURSOR
);
END SELECT_EMPLOYEES_JOBS;包正文如下所示:CREATE OR new PACKAGE BODY SELECT_EMPLOYEES_JOBS AS
PROCEDURE GetEmployeesAndJobs
(
cur_Employees OUT T_CURSOR,
cur_Jobs OUT T_CURSOR
)
IS
BEGIN
-- return all EMPLOYEES records
OPEN cur_Employees FOR
SELECT * FROM Employees;-- return all JOBS records
OPEN cur_Jobs FOR
SELECT * FROM Jobs;
END GetEmployeesAndJobs;
END SELECT_EMPLOYEES_JOBS;
(
--define in/out parameter)
as
--define var
begin--return result connection
open rcursor for
select * from table_name where conditiion.............;end;