Search This Blog

Monday, November 14, 2011

Package persistent stage - Example

Package persistent stage - Example
--------------------------------------------------------------------------------------- Every time ( in the same session ) that a procedure EMP is executed it returns the next 30 rows from table employees until the cursor returns all rows.

CREATE OR REPLACE PACKAGE EMP AS

/* TODO enter package declarations (types, exceptions, methods etc) here */
cursor c is select * from employees;
return_rows number:=30;
procedure PRINTEMPLOYEES ;
END EMP;
----------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY EMP AS

procedure PRINTEMPLOYEES
as
cr c%ROWTYPE;
fetched number:=0;
BEGIN

IF NOT c%ISOPEN THEN
OPEN c;
END IF;
loop
fetch c into cr;
EXIT WHEN c%ROWCOUNT > return_rows OR c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(cr.last_name);
end loop;
return_rows:=return_rows+30;
END PRINTEMPLOYEES;

END EMP;
/
----------------------------------------------------------------------------------------