Data Block Based On Procedures

Using a Stored Procedure with Forms

This sample demonstrates how a block can be based on stored procedures.
This feature can be implemented in two ways:

1- A REF CURSOR
2- A PL/SQL table

In addition to that, there are also two different locations where to implement this functionality:

1- The ON-xxx triggers
2- The transactionnal triggers

In this sample uses a REF CURSOR with the ON-xxx triggers

Step1: Create a table named Bonus

CREATE TABLE BONUS
(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(50),
JOB VARCHAR2(20),
SAL NUMBER,
COMM NUMBER

);

Step2: Create a package spec at the database level

create or replace PACKAGE bonus_pkg
IS
TYPE bonus_rec IS RECORD

(
empno bonus.empno%TYPE,
ename bonus.ename%TYPE,
job bonus.job%TYPE,
sal bonus.sal%TYPE,
comm bonus.comm%TYPE
);

TYPE b_cursor IS REF CURSOR RETURN bonus_rec;

-- Statement below needed if block is based on Table of Records

TYPE bontab IS TABLE OF bonus_rec INDEX BY BINARY_INTEGER;

-- Statement below needed if using Ref Cursor

PROCEDURE bonus_refcur ( bonus_data IN OUT b_cursor );

-- Statement below needed if using Table of Records

PROCEDURE bonus_query ( bonus_data IN OUT bontab );

--Statements below needed for both Ref Cursor and Table of Records

PROCEDURE bonus_insert ( r IN bonus_rec );

PROCEDURE bonus_lock ( s IN bonus.empno%TYPE );

PROCEDURE bonus_update ( t IN bonus_rec);

PROCEDURE bonus_delete ( t IN bonus_rec);

-- If this last function is not included you cannot use the
-- Query -> count hits from the default menu of the forms and
-- will get error frm-41003 Function cannot be performed here.

FUNCTION count_query_ RETURN NUMBER;

END bonus_pkg;

Step 3. Create the package body

create or replace PACKAGE BODY bonus_pkg
IS


PROCEDURE bonus_query ( bonus_data IN OUT bontab)
IS
ii NUMBER;
CURSOR bonselect IS SELECT empno, ename, job, sal, comm FROM bonus;
BEGIN

OPEN bonselect;
ii := 1;
LOOP
FETCH bonselect INTO
bonus_data( ii ).empno,
bonus_data( ii ).ename,
bonus_data( ii ).job,
bonus_data( ii ).sal,
bonus_data( ii ).comm;
EXIT WHEN bonselect%NOTFOUND;
ii := ii + 1;
END LOOP;
END bonus_query;

PROCEDURE bonus_refcur ( bonus_data IN OUT b_cursor)
IS
BEGIN
OPEN bonus_data FOR SELECT empno, ename, job, sal, comm FROM bonus;

END bonus_refcur;

PROCEDURE bonus_insert ( r IN bonus_rec)
IS
BEGIN
INSERT INTO bonus VALUES (r.empno, r.ename, r.job, r.sal, r.comm );

END bonus_insert;

PROCEDURE bonus_lock ( s IN bonus.empno%TYPE )
IS
v_rownum NUMBER;
BEGIN
SELECT empno INTO v_rownum FROM bonus WHERE empno=s FOR UPDATE OF ename;
END bonus_lock;

PROCEDURE bonus_update ( t IN bonus_rec)
IS
BEGIN
UPDATE bonus
SET ename =t.ename,
job =t.job,
sal =t.sal,
comm =t.comm
WHERE empno=t.empno;
END bonus_update;

PROCEDURE bonus_delete ( t IN bonus_rec)
IS
BEGIN
DELETE FROM bonus WHERE empno=t.empno;
END bonus_delete;

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

END bonus_pkg;

Step 4. Create the Form Block

Build a block using the Data Block Wizard with type of data b lock as "Table or View" based on the Bonus table. Now, open the block property sheet to base the block on the stored procedures as follows:In the block property sheet,* Set the Query Data Source Type as Procedure.

* Set the Query Data Source Name with the appropriate stored procedure. "bonus_pkg.bonus_refcur"

Leave all other properties under the Advanced Database section blank.

*You do not need to set anything in the "Query Data Source Columns"

* Set the Query Data Source Arguments

"bonus_data"

"Refcursor"

* Set Mode to "IN/OUT"

Step 5. Create Transactional Triggers
Transactional triggers must be created a the block level as follows:

* On-insert trigger

DECLARE
r bonus_pkg.bonus_rec;
BEGIN
r.empno := :bonus.empno;

r.ename := :bonus.ename;

r.job := :bonus.job;

r.sal := :bonus.sal;

r.comm := :bonus.comm;

bonus_pkg.bonus_insert(r);

END;

* On_lock trigger
bonus_pkg.bonus_lock(:bonus.empno);

* On-update trigger

DECLARE
t bonus_pkg.bonus_rec;
BEGIN
t.empno := :bonus.empno;

t.ename := :bonus.ename;

t.job := :bonus.job;

t.sal := :bonus.sal;

t.comm := :bonus.comm;

bonus_pkg.bonus_update(t);
END;

* On-delete trigger

DECLARE
t bonus_pkg.bonus_rec;

BEGIN
t.empno := :bonus.empno;

t.ename := :bonus.ename;

t.job := :bonus.job;

t.sal := :bonus.sal;

t.comm := :bonus.comm;

bonus_pkg.bonus_delete(t);
END;

* On-count trigger (optional)Note. Because you have based your block on a stored procedure, Form's default processing will not return the number of query hits. This trigger takes the place of the default processing and will return the number of query hits.
DECLARE

recs NUMBER;

BEGIN

recs := bonus_pkg.count_query_;

SET_BLOCK_PROPERTY('bonus', query_hits,recs);

END;

You now have completed the process for basing a block on a stored procedure for single block operations.

انشاء الله سوف ارفع ملفات بالكود والصورة وقواعد البيانات