20/23 FORMS - REF CURSOR

Introduction

You have been assigned to complete the process for basing a block on a stored procedure for “single block operations.”

What is a REF Cursor?

REF cursors hold cursors in the same way that VARCHAR2 variables hold strings. This is an added feature that comes with PL/SQL v2.2. A REF cursor allows a cursor to be opened on the server and passed to the client as a unit, rather than one row at a time. One can use a Ref cursor as a target of assignments and can be passed as parameters to the Program Units. Ref cursors are opened with an OPEN FOR statement and in all other ways, they are the same as regular cursors.

What is a table of records?

A table of records is a new feature added in PL/SQL v2.3. It is the equivalent of a database table in memory. If you structure the PL/SQL table of records with a primary key (an index) you can have array-like access to the rows.

Why base a block on a PL/SQL Table versus a Ref Cursor?

A table of records fetches all the rows from the table. A reference cursor fetches only those rows that match your query criteria. If you are planning to filter the rows with a where clause or your query returns only few records out of many, you can choose the ref cursor rather than the table of records. Note the block properties for the number of records set and the buffered affected blocks, based on stored procedures.

Assignments

Your assignments are:

Create a table

Create a package spec at the database level

Create the package body

Create the Form Block

Create following procedure

· query,

· insert,

· query,

· lock,

· update,

· delete, and

· count procedures.

These are required steps to complete the process for basing a block on a stored procedure for single block operations. You have been assigned to complete the process for basing a block on a stored procedure for “single block operations.”

Hands-On

Connect to SQLPLUS using ISELF/SCHOOLING user.

SQL> CONNECT iself/schooling

Create a Table
Let's, first create a table that contains all the manager names.

SQL> CREATE TABLE managers (
empno NUMBER PRIMARY KEY,
ename VARCHAR2(50))
/

Create a Package Specification

Create a package specification at the database level. Read the following procedures and functions very carefully. At this level, we assume that you know how to write a PACKAGE SPECIFICATION and BODY.

(Procedure Builder)
CREATE OR REPLACE PACKAGE managers_pkg IS
TYPE managers_rec IS

RECORD(
empno managers.empno%TYPE,
ename managers.ename%TYPE);

TYPE c_managers IS REF CURSOR RETURN managers_rec;

TYPE t_mgrtab IS TABLE OF managers_rec

INDEX BY BINARY_INTEGER;

PROCEDURE managers_refcur(managers_data IN OUT c_managers);

PROCEDURE managers_query(managers_data IN OUT t_mgrtab);

PROCEDURE managers_insert(r IN managers_rec);

PROCEDURE managers_lock(s IN managers.empno%TYPE);

PROCEDURE managers_update(t IN managers_rec);

PROCEDURE managers_delete(t IN managers_rec);

FUNCTION count_query_ RETURN number;

END managers_pkg;
/

Note that you can use either a Ref Cursor or a Table of Records on the FORM Builder to perform the query operation.

Create a Package Body

(Procedure Builder)
/*
The next page is a package body that contains the source code
of the procedures and function in the package.

You are encouraged to the movie and take notes about
the package body.

In the next Hands-On you will learn how to use the FORM Builder
tool to call the package and use its procedures and function to
insert, delete, update, lock and count the managers table.

You may use the managers_refcur or managers_query procedures
in the FORM Builder tool to perform the query operation.
*/

CREATE OR REPLACE PACKAGE BODY managerS_pkg

IS

PROCEDURE managers_query(managers_data IN OUT t_mgrtab)

IS
ii NUMBER;
CURSOR manager_select IS
SELECT empno, ename from managers;

BEGIN
for v_managers_select in manager_select loop
ii := 1;
managers_data( ii ).empno := v_managers_select.empno;
managers_data( ii ).ename := v_managers_select.ename;
ii := ii + 1;
END LOOP;
END managers_query;

PROCEDURE managers_refcur(managers_data IN OUT c_managers)

IS
BEGIN
OPEN managers_data FOR SELECT empno, ename
FROM managers;
END managers_refcur;

PROCEDURE managers_insert(r IN managers_rec)

IS
BEGIN
INSERT INTO managers VALUES(r.empno, r.ename);
END managers_insert;

PROCEDURE managers_lock(s IN managers.empno%TYPE)

IS
v_rownum NUMBER;
BEGIN
SELECT empno INTO v_rownum FROM managers
WHERE empno=s FOR UPDATE OF ename;
END managers_lock;

PROCEDURE managers_update(t IN managers_rec)

IS
BEGIN
UPDATE managers SET ename=t.ename
WHERE empno=t.empno;
END managers_update;

PROCEDURE managers_delete(t IN managers_rec)

IS
BEGIN
DELETE FROM managers WHERE empno=t.empno;
END managers_delete;

FUNCTION count_query_ RETURN NUMBER

IS
r NUMBER;
BEGIN
SELECT COUNT(*) INTO r FROM managers;
RETURN r;
END count_query_;

END managers_pkg;
/

Now, you are ready to create the FORM Block along with the Transactional Triggers. Let’s go to the Procedure Builder tool to view the package specification and body.

Questions:

Q: What is a REF Cursor?

Q: What is a table of records?