Play Store Application link – SQL in 18 steps – App on Google Play

Stored procedures and functions help automate and reuse SQL queries. Think of them as reusable scripts that can execute database operations efficiently. Instead of writing the same query multiple times, you can call a stored procedure or function when needed.

1. What Are Stored Procedures?

A stored procedure is a set of SQL statements that are saved in the database and can be executed when needed.

Real-world example: Imagine a banking application where you frequently need to transfer money between accounts. Instead of writing complex SQL queries repeatedly, you can create a stored procedure that takes from_account, to_account, and amount as input and executes the transaction.

2. Creating and Using Stored Procedures

MySQL Example:

DELIMITER $$
CREATE PROCEDURE TransferMoney(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    START TRANSACTION;
    
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
    
    COMMIT;
END $$
DELIMITER ;

Executing the Stored Procedure:

CALL TransferMoney(101, 102, 500.00);

3. SQL Functions

Functions return a value and can be used inside queries. They can be Scalar (returning a single value), Aggregate (working on multiple rows), or Table-Valued (returning a table).

Scalar Function (Returns a Single Value)

CREATE FUNCTION GetAccountBalance(acc_id INT) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE balance DECIMAL(10,2);
    SELECT balance INTO balance FROM accounts WHERE account_id = acc_id;
    RETURN balance;
END;

Usage:

SELECT GetAccountBalance(101) AS balance;

Aggregate Functions (Operate on Multiple Rows)

SELECT COUNT(*) FROM customers; -- Counts total customers
SELECT AVG(salary) FROM employees; -- Finds average salary

Table-Valued Function (Returns a Table – SQL Server)

CREATE FUNCTION GetHighSalaryEmployees()
RETURNS TABLE
AS
RETURN (
    SELECT * FROM employees WHERE salary > 50000
);

Usage:

SELECT * FROM GetHighSalaryEmployees();

4. Database-Specific Variations

PL/SQL in Oracle:

CREATE OR REPLACE PROCEDURE UpdateSalary(emp_id INT, new_salary DECIMAL)
AS
BEGIN
    UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;
    COMMIT;
END;

Execution:

BEGIN
    UpdateSalary(105, 70000);
END;
/

T-SQL in SQL Server:

CREATE PROCEDURE UpdateSalary @emp_id INT, @new_salary DECIMAL(10,2)
AS
BEGIN
    UPDATE employees SET salary = @new_salary WHERE employee_id = @emp_id;
END;

Execution:

EXEC UpdateSalary 105, 70000;

Conclusion

Stored procedures and functions make SQL powerful by allowing reusable, efficient, and optimized database operations. While stored procedures help execute multiple statements, functions return specific values or tables. Understanding their usage in different databases like MySQL, Oracle (PL/SQL), and SQL Server (T-SQL) helps in writing better database-driven applications.