"The pursuit of
happiness is a most ridiculous phrase; if you pursue happiness
you'll never find it." C. P. Snow (1905 - 1980) |
Read
first then play the video:
FRM-VIDEO -What
is a REF CURSOR
FRM-VIDEO -FORM
BLOCK and TRANSACTIONAL TRIGGERS
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.
"The optimist
proclaims that we live in the best of all possible worlds, and
the pessimist fears this is true." - James Branch Cabell |
Questions:
Q: What is a REF Cursor?
Q: What is a table of
records?
|