Stored Procedures and Functions in PL-SQL

Procedures and Functions are examples of Named blocks in PL/SQL.

What is stored procedure?

• A piece of code stored inside the DBMS
• Can be called from other procedures, functions, triggers, or standalone
• SQL allows you to define procedures and functions and store them inside DBMS

---

Advantages of Stored Procedures and Functions:

• Reusability: do not need to write the code again and again
• Programming language-like environment
• Assignment, Loop, For, IF statements
• Call it whenever needed
• Can be called from the select statement, another procedure, or another function.

---

Difference between Function and Procedure:

• Procedure—A series of statements accepting and/or returning zero or more variables.
• Function—A series of statements accepting zero or more variables that returns one value

---

Structure of a Stored Procedure:

• The general syntax of a stored procedure is as follows:

CREATE [OR REPLACE] PROCEDURE <procedureName> (<paramList>) [IS| AS]

<local Declarations>

Begin

<procedure Body>;

End;

• A parameter in the paramList is specified as:

           <name> <mode> <type>

          Mode:

1. 1. IN for input parameter  (default)
   2. OUT for output parameter
   3. INOUT for input and output parameter

Calling a Stored Procedure:

• The syntax for calling a stored procedure is  SQL> exec <procedureName> [(<paramList>)];
• The sample stored procedure for removing an employee from the employees database and its sample execution is

 

CREATE PROCEDURE remove_emp(id NUMBER) AS
tot_emps NUMBER;
BEGIN 
DELETE FROM employees WHERE employees.id = remove_emp.id;
tot_emps:=tot_emps-1;
END;

SQL > exec remove_emp (10);

•  The above procedure removes an employee with given id from employees table. It also reduces the number of employees after deleting that particular employee table.
• It uses tot_emps variable for decreasing the count of employees in employees table by one

---

Stored Functions:

• Similar to stored procedures except that they return value
• Can be called from other procedures, functions, triggers and even from the SELECT statement
• The syntax of a stored function is

CREATE [OR REPLACE] FUNCTION <functionName>

RETURN <type> [(<paramList>)] AS

<local Declarations>

<function Body>;

 

• All features in stored procedures are valid in in stored functions
• Functions have an extra ‘Return’ statement
• The sample program for stored function to find account balance for an bank database is

CREATE FUNCTION get_bal(acc_no IN NUMBER)RETURN NUMBER IS acc_bal NUMBER(11,2);
BEGIN
SELECT order_totalINTO acc_bal FROM orders WHERE customer_id = acc_no;
RETURN(acc_bal);
END;

• The above function returns the balance of person whose account number is the input to the function.
• The function uses a select statement to store balance of required customer into acc_bal and returns it as output.

Hope now you are clear with the description of Stored Procedure and Function in PL-SQL.