some Form question & answer

来源：互联网发布：小当家水浒卡淘宝编辑：程序博客网时间：2024/05/02 05:19

Why base a block on a stored procedure?

---------------------------------------

Basing a block on a stored procedure is an advanced operation to do the

following:

1. Reduce network traffic through array processing as the sql statements are

processed by the pl/sql engine on the server side.

2. Perform complex computations

3. Update and query multiple tables

4. Perform validation and DML on server-side.

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.

Table of records differ from arrays in that they are not bound by a fixed

lower

or higher limit. Nor do they require consecutive index numbers as arrays

do.

Consult a PL/SQL reference manual (version 2.3 or higher) for further

explanation. There are three steps involved in creating a table of records.

The are:

1. Declare a record type that the table is going to contain.

2. Declare a new type for the table of record.

3. Finally, declare a variable using the new type.

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 matches 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

table of records. Note that the block properties for number of records set

and

buffered affect blocks based on stored procedures.

CODE EXAMPLES

==============

This note explains how to use Table of Records or Ref Cursors as the data

query

source and for DML operations using transactional triggers like On-insert,

On-update and On-lock triggers. This note provides two examples of basing a

block on a stored procedure. The first example will provide sample code for

single block operations. The second example will follow with code for

performing multi-block operations with a master-detail relationship.

EXAMPLE A. Single Block Operations.

Use a table with a Primay key. Avoid using Rowid with any select statement.

(Reason explained later). Follow through and complete each of the 5 steps

below.

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

---------------------------------------------------

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

--------------------------------

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 block as "Table

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.

In this example, for refcur you will enter "bonus_pkg.bonus_refcur" and

for table of records you will enter "bonus_pkg.bonus_query".

Note: One can use either Ref Cursor or Table of Records to perform this

query operation. For this example, either use the procedure

bonus_pkg.bonus_query or bonus_pkg.bonus_refcur.

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

property, as Forms has already done this because you began by

creating

the block with data block as a Table.

* Set the Query Data Source Arguments with the appropriate argument name

for

that query.

In this example, "bonus_data" is the argument name for both refcur and

table of records.

* Set the Type to "Table" for table of records or "Refcursor" for

referenced

cursor.

* Set a Name.

In this example, we can use "bonus_pkg.bontab" for table of records

(packagename.table_name) or you can use either "bonus_pkg.b_cursor"

or "bonus_data.b_cursor" for a ref cursor.

* Set Mode to "IN/OUT" as the data is flowing between the client and

server

and viceversa.

* Set Value (optional)

If you skip to set typename, you will hit an error. The possible compilation

error will be PL/SQL error 103 in the QUERY-PROCEDURE TRIGGER.

When you use the "table of records" as the source of query, Forms

automatically

creates a trigger like Query-Procedure to populate the values that are sent

from the database through the stored procedure.

* Set the DML target type as "Transactional triggers" under the Advanced

Database section. This step is important. You must specify

"transactional triggers" to avoid getting an error:

FRM-40743: THIS OPERATION WITH NO BASE TABLE REQUIRES THE %S TRIGGER.

at runtime.

Leave all other properties under the Advanced Database section blank.

Note: You must use transactional triggers to perform all DML processing

as your block is based on stored procedures and not a table or

view.

If you do not provide these triggers (see code in Step 5) you

will

receive runtime error: Frm-40401 No Changes To Save when after

performing DML operations like insert, delete or update.

One more general example of setting the Query Data Source Arguments in the

block property palette could be,

ARGUMENTNAME TYPE TYPENAME MODE VALUE

------------- ---- -------- ---- -----

bonus_data REFCURSOR bonus_pkg.b_cursor IN OUT (leave blank)

Or bonus_data.b_cursor

bonus_data TABLE bonus_pkg.bontab IN OUT (leave blank)

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.

============================================================================

EXAMPLE B. Multi-block opererations with master-detail relationship

Basing a block on a stored procedure (Multi block operation, having master

detail relationship).

Suppose you are passing a value from master block to the detail block to

perform query operation, perform the following steps:

Step1: verification

--------------------

BEFORE ATTEMPTING TO DO THIS, VERIFY THE PRESENCE OF INTEGRITY CONSTRAINTS

FOR THE TABLES INVOLVED IN THIS OPERATION.

Verify if the tables have Primary key and Foreign Key relationship.

Your Physical Database design is very important. Otherwise, you will be

getting errors like

ORA-6502 Pl/sql: Numeric value error or

ORA-4098 Trigger 'X' is invalid and failed revalidation.

ORA-4068 If any column name is not properly defined in the select

statement of the stored procedure.

see note:2007482.6 for an explanation on how to do this.

Step2: Create a package spec at the database level

---------------------------------------------------

PACKAGE emp_pkg IS

TYPE emprec is RECORD(

empno emp.empno%type,

ename emp.ename%type,

job emp.job%type,

mgr emp.mgr%type,

hiredate emp.hiredate%type,

sal emp.sal%type,

comm emp.comm%type,

deptno emp.deptno%type);

TYPE empcur IS REF CURSOR RETURN emprec;

TYPE emptab IS TABLE OF emprec INDEX BY BINARY_INTEGER;

-- This procedure uses refcursor for query operation, it sends the data

-- from the stored procedure to the client.

PROCEDURE empquery_refcur(emp_data IN OUT empcur,

v_dno IN NUMBER);

-- This procedure uses table of records for query operation.

-- One can use either ref cursor or table of records for query operation.

PROCEDURE empquery(emp_data IN OUT emptab,

v_dno IN NUMBER);

-- This procedures inserts the data passed as a record from the emp block

-- to the emp table.

PROCEDURE emp_insert(r IN emprec);

-- Empno is getting passed from emp block to the stored procedure, this

-- procedure is to lock that specific row that has that empno.

PROCEDURE emp_lock(s IN emp.empno%TYPE);

PROCEDURE emp_update(t IN emprec);

PROCEDURE emp_delete(t IN emprec);

-- 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 emp_pkg;

Step 3. Create the package body

--------------------------------

PACKAGE BODY emp_pkg IS

PROCEDURE empquery(emp_data IN OUT emptab,

v_dno IN NUMBER) IS

ii NUMBER;

CURSOR empselect IS

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno

FROM emp

WHERE deptno = nvl(v_dno, deptno);

BEGIN

OPEN empselect;

ii := 1;

LOOP

FETCH empselect INTO

emp_data(ii).empno,

emp_data(ii).ename,

emp_data(ii).job,

emp_data(ii).mgr,

emp_data(ii).hiredate,

emp_data(ii).sal,

emp_data(ii).comm,

emp_data(ii).deptno;

EXIT WHEN empselect%NOTFOUND;

ii := ii + 1;

END LOOP;

END empquery;

PROCEDURE empquery_refcur(emp_data IN OUT empcur,

v_dno IN NUMBER) AS

BEGIN

OPEN emp_data FOR

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno

FROM emp

WHERE deptno = nvl(v_dno, deptno);

END empquery_refcur;

PROCEDURE emp_insert(r IN emprec) IS

BEGIN

INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES (r.empno, r.ename, r.job, r.mgr, r.hiredate, r.sal,

r.comm, r.deptno);

COMMIT;

END emp_insert;

PROCEDURE emp_lock(s IN emp.empno%TYPE) IS

v_rownum NUMBER;

BEGIN

SELECT empno

INTO v_rownum

FROM emp

WHERE empno = s

FOR UPDATE OF ename;

END emp_lock;

PROCEDURE emp_update(t IN emprec) IS

BEGIN

UPDATE emp

SET ename = t.ename,

job = t.job,

mgr = t.mgr,

hiredate = t.hiredate,

sal = t.sal,

comm = t.comm,

deptno = t.deptno

WHERE empno = t.empno;

END emp_update;

PROCEDURE emp_delete(t IN emprec) IS

BEGIN

DELETE

FROM emp

WHERE empno = t.empno;

END emp_delete;

FUNCTION count_query_ RETURN NUMBER IS

r NUMBER;

BEGIN

SELECT count(*)

INTO r

FROM emp;

RETURN r;

END count_query_;

END emp_pkg;

Step 4. Create the Form Block

------------------------------

Build the block with datablock type as "table or view" for both the master

(dept) and detail block (emp). Later the block property sheet will be

adjusted

for the detail block to be based on stored procedure.

The blocks can be built manually also, but, using wizard is preferrable.

Here,

you have the choice of having tab canvas.

Create the relationship between these blocks either explicitly or at the

time

of creating the block using wizard, refer the later part of this note for

information on this.

In the detail block propertysheet, (emp block)

* set the Query Data Source Type as Procedure.

* set the Query Data Source Name with the appropriate name of the stored

procedure.

In this case, One can use either the Ref Cursor or Table of Records to

perform this query operation. So, in this example, either use the

procedure

emp_pkg.empquery

emp_pkg.empquery_refcur

Because you began creating the block with data block as Table or view, you

not need to set anything in the Query Data Source Columns as the Forms does

that job.

* set the Query Data Source Arguments with the appropriate argument name

for

that query.

In this case, emp_data is the argument name for both ref cursor and

table

of records

* set the Type to table or refcursor, depending on the procedure you have

chosen. This example uses the "Table".

* set the Type Name to the appropriate type, this will be emp_pkg.emptab

If you choose the ref cursor, it would have been emp_pkg.empcur or

emp_data.refcur

* set Mode to "IN/OUT" as the data is flowing between the client and

server

and viceversa.

* set Value (optional)

* repeat above steps for the other argument of the procedure, deptno.

The appropriate values can be found in the table below

Yet another general example could be,

ARGUMENTNAME TYPE TYPENAME MODE VALUE

------------ ---- -------- ---- -----

emp_data REFCURSOR emp_pkg.empcur IN OUT (leave it

blank)

emp_data TABLE emp_pkg.emptab IN OUT

AND

v_dno NUMBER dept.deptno%type IN :dept.deptno

If you skip to set typename, you will hit an error. The possible compilation

error will be:

PL/SQL error 103 in the QUERY-PROCEDURE TRIGGER.

When you use the "table of records" as the source of query, Forms

automatically

creates a trigger like Query-Procedure to populate the values that are sent

from the database through the stored procedure.

* Set the DML target type as "Transactional triggers" under the Advanced

Database section. You must specify "transactional triggers" to avoid

getting error:

FRM-40743: THIS OPERATION WITH NO BASE TABLE REQUIRES THE %S TRIGGER.

at runtime. Leave all other properties under the Advanced Database

section

blank.

Note: You must use transactional triggers to perform all DML processing

your block is based on stored procedures and not a table or view.

If you do not provide these triggers (see code in Step 5) you will

receive runtime error:

Frm-40401 No Changes To Save

after performing DML operations like insert, delete or update.

Step 5. Create Transactional Triggers

--------------------------------------

Then, from the forms at detail block level (emp), you have to create the

following triggers:

* On-insert trigger

DECLARE

r emp_pkg.emprec;

BEGIN

r.empno := :emp.empno;

r.ename :=:emp.ename;

r.job := :emp.job;

r.mgr := :emp.mgr;

r.hiredate := :emp.hiredate;

r.sal := :emp.sal;

r.comm := :emp.comm;

r.deptno := :emp.deptno;

emp_pkg.emp_insert(r);

END;

* On_lock trigger

emp_pkg.emp_lock(:emp.empno);

* On-update trigger

DECLARE

t emp_pkg.emprec;

BEGIN

t.empno := :emp.empno;

t.ename := :emp.ename;

t.job := :emp.job;

t.mgr := :emp.mgr;

t.hiredate := :emp.hiredate;

t.sal := :emp.sal;

t.comm := :emp.comm;

t.deptno := :emp.deptno;

emp_pkg.emp_update(t);

END;

* On-delete trigger

DECLARE

t emp_pkg.emprec;

BEGIN

t.empno := :emp.empno;

t.ename := :emp.ename;

t.job := :emp.job;

t.mgr := :emp.mgr;

t.hiredate := :emp.hiredate;

t.sal := :emp.sal;

t.comm := :emp.comm;

t.deptno := :emp.deptno;

emp_pkg.emp_delete(t);

END;

* On-count trigger

DECLARE

recs NUMBER;

BEGIN

recs := emp_pkg.count_query_;

set_block_property('emp', query_hits, recs);

END;

-- This On-Count trigger is needed. The forms default

-- processing will not return the query hits as you have

-- based the block on a stored procedure.

Step 6. Change the delete record behavoiur

-------------------------------------------

Make sure the "delete record behaviour" property of the relation is set to

isolated (non-isolated is the default in Developer 6).

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

for

Master-Detail operations.

============================================================================

===

Questions:

1. What will happen if you change the "delete record behaviour" from

isolated

to non-isolated or cascading?

If you change the "Delete record behaviour" from isolated to

non-isolated,

the On-Check-Delete-Master trigger will be created by forms in the master

block. This will not understand the stored procedure you have used as a

query data source for the detail block. As a result, you will get a

Compilation error in the On-Check-Delete-Master trigger that will be

like:

Pls-201 procedure name must be declared.

Similarly, if you change the "Delete record behaviour" from isolated to

cascading, the forms generates the Pre-Delete trigger in the master block

which will give a compilation error as well, as the master block will not

understand the procedure for the query data source on which the detail

block

is based on.

It will be costly to have integrity constraint at form level also as the

pre-delete trigger repeats the same job as the constraints declared at

database side.

So, do not try to change the "Delete Record Behaviour".