Manuscript: 15

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

-- Hands-On 12 (What is a REF CURSOR)
DROP TABLE oracle.managers
/


SET ECHO ON
CLEAR SCR 
-- Connect to SQL*PLUS as the oracle user.
--
pause

CONNECT oracle/learning
pause

CLEAR SCR
-- Let's, create a table that contains all the manager names.
--
pause

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

CLEAR SCR
-- 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. 
pause

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.

pause 

CLEAR SCR
-- Create the PACKAGE BODY. 
--
pause


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

You are encouraged to pause 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. 
*/

pause

CLEAR SCR
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;
/
pause
-- Now, you are ready to create the FORM Block along with 
-- the Transactional Triggers.

-- Now, let's go to the Procedure Builder tool to view the 
-- package specification and body.

pause

CLEAR SCR
-- This time don't drop the packages since in the next Hands-On
-- you are going to learn how to use it in FORM BLOCK and the
-- transactional triggers using the FORM builder tool.
--
pause
pause


CLEAR SCR
-- Now, practice this Hands-On over and over
-- again until you become a master at it.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck!
Pause