What are Procedures ?
Procedures are named PL/SQL blocks that can take parameters, perform an action and can be invoked.Procedures are make up of
- A declarative para
- An executable part and
- An optional exception-handling part.
Declarative part:
The declarative part may contain declarations of cursors, constraint, variables, exception and subprograms. These objects are local to the procedure. The objects become invalid once you exit from it.Executable part:
The executable part contains a PL/SQL block consisting of statements that assign values, control execution and manipulate Oracle data. The action to be performed is coded here and data that is to be returned back to the calling environment is also returned from here. Variables declared are put to use in this block.Exception Handling part:
This part contains code that performs an action to deal with exceptions raised during the exception of the Executable part. This block can be used to handle Oracle's own exceptions or the exceptions that are declared in the Declarative part. One cannot transfer the flow of execution from the Exceptions Handling part to the Executable part or vice versa.
WHERE DO PROCEDURE RESIDE ?
Procedures in ORACLE are classed stored procedures. Procedures are stored in the database and are invoked or called by any anonymous block (the PL/SQL block that appears within an application). Before the procedures is create, ORACLE parsed the procedure. Then this parsed procedure is stored in the database.
HOW ORACLE CREATE A PROCEDURE ?
When a procedure is created, ORACLE automatically performs the following steps :
- Compiles the procedure.
- Stores the compiled code.
- Stores the procedure in the database.
AN APPLICATION USING A PROCEDURE:
Focus:
Perform insert, update and delete for the table itemmast on the basis of the table itemtrans table.- Create the following tables
itemid number(4) Primary key via which we shall seek data in the table
description varchar2(20) The item description.
bal_stock number(3) The balance stock for an item.
Table Name: ItemTran
itemid number(4) Which we shall seek data in the table/
Description varchar2(30) item description kind of operation on the itemMast table i.e Insert
Operation Update, Delete.
Qty number(3) The Qty sold.
Status varchar2(30) The status of the Operation.
Based on the value in the operation column of table itemtran the records for table itemmast is either inserted, updated or deleted. On the basis of success / failure of insert, update and delete operation the status column in the table itemtran is updated with appropriate text indication successes or reason for failure.
1) If Operation = ' I ' then the itemid along with description and qty is inserted into the required column of the table itemmast. If insert is successful then the status field of itemtran table is updated to ' SUCCESSFUL ' else it is updated as 'ITEM ALREADY EXISTS'.
2)If Operation ='U' then the qty is added to bal_stock column of the table itemmast where itemid of table itemmast is same as that of itemtran. If update is successful then the status column of itemtran table is updated to 'SUCCESSFUL' else it is updated as 'ITEM NOT PRESENT'.
3) If Operation ='D' then a row from itemmast is deleted whose itemid is the same as the itemid in the table itemtran with the operation column having the value 'D' 'SUCCESSFUL' else it is updated as 'ITEM NOT PRESENT'.
Write a database procedure which will check for the existence of item_id in the table itemmast. The procedure must have one argument which receives a value for which a matching pattern for item_id in the table itemmast and another which will return value indicating whether a match has been found or not. This value returned by the procedure can be used to make a decision to perform further processing or not.
Creating A Procedure for use:
To create a procedure to perform an item_id check operation P_itemidchk is the name of the procedure which accepts a variable itemid and returns a variable valexists to the host environment. The value of valexists changes from 0 (item does not exist) to / (itemid exists) depending on the records retrived.CREATE or replace Procedure p_itemidchk(vitemidno IN number,
valexists OUT NUMBER) AS
/* variable that hold data from the itemmast table */
dummyitem number(4);
BEGIN
select itemid into dummyitem from itemmast
where itemid=vitemidno;
/* if the select statement retrives data, valexists is set to 1 */
valexists :=1;
EXCEPTION
/* if the select statement does not retrieve data, valexists is set to 0 */
when no_data_found then
valexists:=0;
END;
Any PL/SQL block can be used to call this procedure to perform the check. To do this the contents of the variable vitemidno is passed on as an argument to the procedure p_itemidchk. The return value is then checked and appropriate action is taken.
DECLARE
/* Cursor scantable retrieves all the records of table itemtran */
cursor scandtable is
select itemi,operation,qty,description from itemtran;
/* variables that hold data from the cursor scantable */
vitemidno number(4);
descrip varchar2(30);
oper char(1);
quantity number(3);
/* variable that 1 or 0. It is set in the procedure p_itemidchk */
valexists number(1);
BEGIN
open scantable
loop
fetch scantable into vitemidno,oper,quantity,descrip;
/* Call procedure p_itemidchkk to check if item_id is present in itemmast table */
p_itemidchk(vitemidno,valexists);
/* if itemid does not exists */
if valexists = 0 then
/* if mode is insert then
insert a record in itemmast table and set the status in the itemtran table to 'SUCCESSFUL'*/
if oper=T then
insert into itemmast (itemid,bal_stock,description)
values(vitemidno,quantity,descrip);
update itemtran
set itemtran.status ='SUCCESSFUL'
where itemid = vitemidno;
/* if the record is not found and the operation is update/delete then set the status to
'Item Not Present' */
elsif oper = 'U' or 'D' then
update itemtran
set itemtran.status='ITEM NOT PRESENT'
where itemid = vitemidno;
end if;
