Mega Code Archive
Cursor variable in a package
<![CDATA[
SQL>
SQL> create table company_site(
2 site_no number(4) not null,
3 site_descr varchar2(20) not null
4 );
Table created.
SQL> insert into company_site values (1,'New York');
1 row created.
SQL> insert into company_site values (2,'Washington');
1 row created.
SQL> insert into company_site values (3,'Chicago');
1 row created.
SQL> insert into company_site values (4,'Dallas');
1 row created.
SQL> insert into company_site values (5,'San Francisco');
1 row created.
SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage
2 IS
3 PRAGMA SERIALLY_REUSABLE;
4 CURSOR cursor_site IS
5 SELECT * from company_site ORDER BY site_no;
6 PROCEDURE displaySites;
7 END myPackage;
8 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 IS
3 PRAGMA SERIALLY_REUSABLE;
4 PROCEDURE displaySites
5 IS
6 site_rec company_site%ROWTYPE;
7 BEGIN
8 OPEN cursor_site;
9 FETCH cursor_site INTO site_rec;
10 dbms_output.put_line(TO_CHAR(site_rec.site_no)||' '||site_rec.site_descr);
11 FETCH cursor_site INTO site_rec;
12 dbms_output.put_line(TO_CHAR(site_rec.site_no)||' '||site_rec.site_descr);
13 END displaySites;
14 END myPackage;
15 /
Package body created.
SQL> BEGIN
2 myPackage.displaySites;
3 END;
4 /
1 New York
2 Washington
PL/SQL procedure successfully completed.
SQL>
SQL> drop table company_site;
Table dropped.
]]>