STORED
PROCEDURES
STORED PROCEDURES
▪ A stored procedure is a collection of precompiled SQL statements that are saved in the
database.
▪ A stored procedure can be invoked by triggers, other stored procedures, and applications such
as Java, Python, PHP.
▪ Stored procedures are an important element of database management systems because they
provide a quick and fast way to perform repetitive or complicated activities.
▪ It offers modularity, reusability, and increased security by encapsulating SQL statements into
a single process.
WORKING OF STORED PROCEDURES
STORED PROCEDURES ADVANTAGES
▪ Stored procedures help increase the performance of the applications. Once created, stored
procedures are compiled and stored in the database.
▪ Stored procedures help reduce the traffic between application and database server because
instead of sending multiple lengthy SQL statements, the application has to send only the name
and parameters of the stored procedure.
▪ Stored procedures are reusable and transparent to any applications.
▪ Stored procedures are secure. The database administrator can grant appropriate permissions to
applications that access stored procedures in the database without giving any permissions on the
underlying database tables.
STORED PROCEDURES DISADVANTAGES
▪ If you use many stored procedures, the memory usage of every connection that is using those
stored procedures will increase substantially.
▪ Stored procedure constructs are not designed for developing complex and flexible business
logic.
▪ It is difficult to debug stored procedures. Only a few database management systems allow you to
debug stored procedures.
▪ It is not easy to develop and maintain stored procedures. Developing and maintaining stored
procedures often requires a specialized skill set that not all application developers possess.
Stored Procedure
• block-structured language that enables developers to combine the
power of SQL with procedural statements
• subprogram unit that consists of a group of PL/SQL statements
• They can be called or referred inside the other blocks also
• has its own unique name
How to create Stored Procedure
Parameter:
• variable or placeholder of any valid PL/SQL datatype through which the
PL/SQL subprogram exchange the values with the main code
• This parameter allows to give input to the subprograms and to extract from
these subprograms
• defined along with the subprograms at the time of creation.
• included in the calling statement
• Types:
• IN
• OUT
• IN OUT
Parameter:
• IN
• For giving input to subprogram
• Read-only variable
• In the calling statement, these parameters can be a variable or a
literal value or an expression
• By default, the parameters are of IN type
Parameter:
• OUT
• getting output from the subprograms RETURN
• instructs the
• read-write variable inside the subprograms
compiler to switch
• In the calling statement, these parameters should
the control from the
always be a variable to hold the value from the
current subprograms
subprogram to the
calling statement
• IN OUT
• both giving input and for getting output
• read-write variable
• In the calling statement, these parameters should
always be a variable
Procedure- Example
How to call Procedure?
Procedure- Example
If you want to get only details of products
by giving specific product id
Use IN parameter
How to call Procedure?
Procedure- Example
If you want find the total price of all
products and display it
Use OUT parameter
How to call Procedure?
Procedure and Function
Create procedure example
▪ In this example, we are going to insert record in user table. So you need to create user table first.
▪ Table creation:
▪ Now write the procedure code to insert record in user table.
▪ Procedure Code: Example 1
Output:
▪ Example 2
CREATE OR REPLACE PROCEDURE get_emp_name (
emp_id IN employees.employee_id%TYPE)
IS
emp_name employees.employee_name%TYPE;
BEGIN
SELECT employee_name INTO emp_name
FROM employees
WHERE employee_id = emp_id;
dbms_output.put_line('Employee Name: ' || emp_name);
END;
▪ Calling a Stored Procedure [ for Example 1]
To call the calculate_employee_bonus procedure for employee ID 101, the following statement would be
used:
▪ Calling a Stored Procedure [ for Example 2]
Use EXECUTE keyword:
EXECUTE get_emp_name(100);
▪ Input Parameters
• Specify parameter mode - IN, OUT, IN OUT
IN - pass value to procedure
CREATE PROCEDURE get_bonus( emp_id IN employees.employee_id%TYPE, bonus OUT NUMBER)
OUT - get updated value from procedure
Output Parameters
Assign output variable in procedure
bonus := salary * 0.1;
Pass variable in calling statement
DECLARE
b NUMBER;
BEGIN
get_bonus(100, b);
END;
▪ PL/SQL Drop Procedure
Syntax for drop procedure
EXAMPLE: CREATING A STORED PROCEDURE TO CALCULATE EMPLOYEE BONUS
▪ Consider the following stored procedure that calculates the bonus for each employee based on their salary:
EXAMPLE: CREATING A STORED PROCEDURE TO CALCULATE EMPLOYEE BONUS
▪ Consider the following stored procedure that calculates the bonus for each employee based on their salary:
• This stored procedure takes an employee ID as input and calculates the
corresponding bonus based on their salary.
• It retrieves the employee's salary, calculates the bonus, updates the
employee's bonus in the database, and finally exits the procedure.