Procedures and Functions in Oracle Database, Schemes and Mind Maps of Law

Download Procedures and Functions in Oracle Database and more Schemes and Mind Maps Law in PDF only on Docsity!

PROCEDURES

Definition

• A Procedure or Function is a logically grouped

set of SQL and PL/SQL statement that perform

a specific task.

• A stored procedure or function is a named

PL/SQL code block that have been compiled

and stored in one of the Oracle engine’s

system tables.

• (^) Procedures and Functions are stored in the

Oracle database. They are invoked or called by

the PL/SQL block that appears within an

application.

Where do Stored Procedures and Functions Reside?

How the Oracle engine creates a Procedure/Function?

• (^) When a Procedure is created, the Oracle engine automatically

performs the following steps:

1. Compiles the Procedure or Function.

2. Stores the Procedure or Function in the database.

The Oracle engine compiles the PL/SQL code block. If an error occurs

when the procedure or function an invalid procedure or function is

created. The Oracle engine displays a message after creation that the

procedure or function was created with compilation errors.

It does not display the errors. These errors can be viewed using the

SELECT statement.

SELECT * FROM user_errors;

Advantages of Using a Procedure or Function

1. Security: Stored procedures can help enforce data security. 2. Performance: It improves database performance in the following ways:

Amount of information sent over a network is less.

No compilation step is required to execute the code.

3. Memory Allocation: The amount of memory used reduces as stored procedures have shared memory capabilities. Only one copy of procedure needs to be loaded for execution by multiple users. Once a copy of the procedure is opened in the Oracle engine’s memory, other users who have permissions may access them when required. 4. Productivity: By writing procedures redundant coding can be avoided, increasing productivity. 5. Integrity: A procedure needs to be tested only once to guarantee that it returns an accurate result. Since procedures are stored in the Oracle engine’s they become a part of the engine’s resource. Hence the responsibility of maintaining their integrity rests with the Oracle Engine.

Procedures VS Functions

• A Function must return a value back to the

caller. A function can return only one value to

the calling PL/SQL code block.

• BY defining multiple OUT parameters in a

procedure, multiple values can be passed to

the caller. The OUT variable being global by

nature , its value is accessible by any PL/SQL

code block including the calling PL/SQL block.

How To DELETE A Stored Procedure?

• (^) Syntax:

DROP PROCEDURE ;

• (^) Example:

DROP PROCEDURE proc_update;

Example

• (^) The current example deals with the Sales Order Processing system. The tables involved with this processing are Product_Master, Sales_Order_Details, Challan_Header and Challan_Details.
• (^) Each time a entry is made in the Challan_Details , there is an actual reduction in inventory as goods delivered to the customers. So, an entry needs to be made in the Product_Master table that will reduce the qty_in_hand by the quantity for which the challan was generated. Also the status of the order whether Fully Processed (FP), In Process (IP) or Not Processed (NP) needs to be updated in the Sales_Order table.

Table: Challan_Details Column Name Data Type Size Attributes Challan_no Varchar2 6 PK/FK references Challan_no of Challan_header Table Product_no Varchar2 20 PK/FK references product_no of product_master Table Qty_disp Number 10,2 Not Null Table: Challan_Header Column Name Data Type Size Attributes Challan_no Varchar2 6 PK / First two letters must start with ‘CH’. Order_no Varchar2 20 FK references s_order_no of sales_order Table. Challan_Date Date Not Null. Billed_yn Char 1 Values (‘Y’,’N’). Default ‘N’

Table: Sales_Order Column Name Data Type Size Attributes Order_no Varchar2 6 PK / First two letters must start with ‘O. Order_date date Client_no Number 10,2 FK references client_no of client_master table. Dely_Addr Varchar2 25 Salesman_No Varchar2 6 FK references salesman_no of salesman_master table Dely_Type Char 1 Billed_yn Char 1 Dely_date Date can’t be less than order date Order_Status Varchar2 10 Values (In Process, Fulfilled, BackOrder or Cancelled)

Creating Procedure for Use:

• In order to simplify the job of updating the two related

tables(sales_order and Product_master) each time an

insert is made in the challan_retails table a procedure is

created. This procedure is then called in the PL/SQL

block while inserting a record in the challan_details

table.

• A procedure called proc_update is created and stored in

the database. This procedure when called in a PL/SQL

block updates the Qty_in_hand in the product_master. It

also updates the order_status in the sales_order table.

CREATE OR REPLACE PROCEDURE proc_update (vproductno IN char, vsorderno IN char, quantity IN number) IS Total_qty_ordered number (8); Total_qty_disp number (8); BEGIN UPDATE product_master SET qty_in_hand = qty_in_hand-quantity (/Updating the qty_in_hand in product_master table/) WHERE product_no = vproductno; SELECT sum(qty_ordered), sum (qty_disp) (/Checking in the sales_order_details table the total INTO total_qty_ordered, total_qty_disp quantity ordered and the total quantity dispatched FROM sales_order_details for a certain sales_order and stuffing the values WHERE delorder_no=vsorderno; into memory variables /) IF total_qty_ordered = total_qty_disp THEN UPDATE sales_order SET order_status = ‘Fulfilled’(/ Comparing the total_quantity ordered with WHERE order_no = vsorderno; the total quantity dispatched and updating the ELSIF total_qty_disp = 0 THEN order_status in sales_order table/) UPDATE sales_order SET order_status = ‘Backorder’ WHERE order_no = vsorderno; Else UPDATE sales_order SET order_status = ‘In Process’ WHERE order_no = vsorderno; END IF; END;

The functionality of the PL/SQL block of code will be as follows:

• (^) The above PL/SQL when fired will open a cursor called

c_mast_check that shall fetch the challan_no and s_order_no from

the challan_header table. The challan_no fetched is then compared

to the challan_no entered by the user. This is done to check for a

corresponding master record in the challan_header table so that a

detail record can be inserted in the challan_details table.

• (^) Only when these two challan_no’s match, is an insert operation

performed in the challan_details table. Also, a call is made to the

procedure proc_update and the values for product_no, s_order_no

and quantity are passed as an IN value to the procedure.

• (^) The procedure is then executed and updates the product_master

and the sales_order table. In case the challan_no entered by the

user does not match the one fetched by the cursor a message is

displayed to the user and there is an exit from the PL/SQL block.