Returning Rows Call : Select « Select Query « Oracle PL / SQL

1. Oracle PL / SQL
2. Select Query
3. Select

Returning Rows Call

    
SQL>
SQL> CREATE TABLE emp (
  2     empID INT NOT NULL PRIMARY KEY,
  3     Name      VARCHAR(50) NOT NULL);

Table created.

SQL> INSERT INTO emp (empID,Name) VALUES (1,'Tom');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (2,'Jack');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (3,'Mary');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (4,'Bill');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (5,'Cat');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (6,'Victor');

1 row created.

SQL>
SQL> CREATE OR REPLACE PACKAGE emp_pkg
  2  AS
  3  TYPE studCur IS REF CURSOR;
  4  PROCEDURE Getemp(o_StudCur OUT studCur);
  5  END emp_pkg;
  6  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY emp_pkg
  2  AS
  3     PROCEDURE Getemp(o_StudCur OUT studCur)
  4     IS
  5     BEGIN
  6        OPEN o_StudCur FOR
  7           SELECT empID, Name FROM emp;
  8     END Getemp;
  9  END emp_pkg;
 10  /

Package body created.

SQL> SET SERVEROUT ON
SQL> DECLARE
  2     TYPE studCurType IS REF CURSOR;
  3     mycur studCurType;
  4     studrow emp%ROWTYPE;
  5  BEGIN
  6     emp_pkg.Getemp(mycur);
  7     FETCH mycur INTO studrow;
  8     WHILE mycur%FOUND
  9     LOOP
 10        dbms_output.put_line(studrow.empID || '   ' ||
 11                             studrow.Name);
 12        FETCH mycur INTO studrow;
 13     END LOOP;
 14  END;
 15  /
1   Tom
2   Jack
3   Mary
4   Bill
5   Cat
6   Victor

PL/SQL procedure successfully completed.

SQL>
SQL> drop table emp;

Table dropped.

   
    
    
    
  

Related examples in the same category