Post
Ask Question

Improved Performance by STORED PROCEDURES

Stored Procedures

Stored procedures are one of the important features in relational database management systems (RDBMS). It encapsulates frequently used or Complex Logic SQL Statements in a block to allow them to be executed in a single call.

Stored Procedures

Characteristics of Stored Procedures

  1. Unique Name: It should have a unique name in a database.
  2. Input parameters: One can pass parameters to get dynamic Output.
  3. Pass n number of SQL Statements: Passing SQL statements in stored procedures allows dynamic execution of SQL within the procedure. This technique is often referred to as dynamic SQL.
  4. Compiled Once and Call Multiple Times: Stored procedures are compiled once and stored in the database. This can lead to performance improvements since the SQL code does not need to be parsed and compiled every time it is executed.
  5. Return Values with the help of OUT Keyword: Using the OUT keyword in stored procedures allows you to return values from the procedure to the caller. This is useful for returning multiple values or computed results.
  6. Can add Procedural Logic like If-else, for, and while loop: Can add procedural logic such as IF-ELSE statements, FOR loops, and WHILE loops in stored procedures. It allows you to create more complex and versatile stored procedures.

Merits of Stored Procedures

  1. Code Reusability
  2. High Quality and Performance
  3. More Secure: Limiting Direct Access to the tables
  4. Maintainability: Business logic can be centralized in stored procedures, making it easier to manage and update.
  5. Reduced Network Traffic: By executing multiple SQL statements in a single call, stored procedures reduce the amount of data sent over the network.

Create a Database and Table

CREATE DATABASE ddb4;
USE ddb4;
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department_id INT
);
INSERT INTO employees VALUES 
(1, 'Gaurav', 'Gupta', 678907, 121),
(2, 'Gayatri', 'Shukla', 60000, 121),
(3, 'Vani', 'Gupta', 578907, 121),
(4, 'Ravi', 'Gupta', 478907, 122),
(5, 'Gauri', 'Tavan', 888907, 122),
(6, 'Avni', 'Gupta', 778907, 123);

Create a Simple Procedure

delimiter //  --Starting block of Procedure
Create Procedure proc()
Begin
    Select * from employees;
end //  --Ending of block 

call proc();  --Calling of Stored Procedure

It is a simple procedure to start with a delimiter to create a procedure named proc() consisting of a SQL statement. Once it is compiled, it can be called multiple times by call function.

Create a Parameterized Procedure

delimiter //
Create Procedure proc1(deptid int, sal decimal)
Begin
    Select * from employees where department_id = deptid;
    update employees set salary = sal * salary where department_id = deptid;
end //
call proc1(121, 1.1);

Passing two parameters such as deptid and sal and consists of two SQL Statements. It is a procedure to update the salary of all the employees who are working in a particular department.

Create a Procedure using Logic like If-else, for and while loop

delimiter //
create Procedure proc2(empid int)
begin
    declare emp_sal decimal(10,2);
    select salary into emp_sal from employees where employee_id = empid; 
    if emp_sal > 70000 then
        select "High_Status" as status;
    else
        select "Low_Status" as status;
    end if;
end //
call proc2(4);

It is a parameterized procedure using logic statements such as the if-else block of code. It declares a variable emp_sal to store value from the table such as salary. If the salary is greater than 70000, then the employee is of “High_Status” else of “Low_Status”.

Create a Procedure to return the value using the OUT keyword

delimiter //
Create procedure proc3(IN dept int, OUT count int)
Begin
    Set count = (Select count(*) from employees where department_id = dept);
end //
call proc3(122, @count);
select @count;

It is a procedure consisting of two parameters of different types: one is the Input parameter using IN keyword and another is an Output parameter using the OUT keyword. It is used to count the number of employees who are working in a particular department.

Stored procedures are efficient, maintainable, and scalable. This concept will improve the overall performance of your database applications and provide a robust framework for managing complex business logic.


Similar Articles