SQL Stored Procedures & Functions MySQL & PostgreSQL

Introduction

SQL Stored Procedures and Functions are powerful database tools used to store reusable SQL logic inside the database itself. In simple terms, they allow you to write a block of SQL code once and execute it multiple times whenever needed.

For Pakistani students learning databases for the first time, especially in universities in Lahore, Karachi, and Islamabad, understanding sql stored procedures, postgresql functions, mysql stored procedure concepts is extremely important. These features are widely used in software houses, banking systems, e-commerce platforms like Daraz-style applications, and government databases.

Stored procedures help you automate tasks like salary calculation, student result processing, and bank transaction handling. Functions, on the other hand, are used to return values and perform calculations inside queries.

In this tutorial, you will learn how MySQL and PostgreSQL handle procedures and functions, with real-world Pakistani examples and hands-on code.

Prerequisites

Before learning SQL Stored Procedures & Functions, you should already understand:

• Basic SQL commands: SELECT, INSERT, UPDATE, DELETE
• Tables, rows, and relationships
• Primary keys and foreign keys
• Basic database concepts
• Simple query writing skills

If you are not confident in these topics, it is recommended to first complete a beginner SQL tutorial on theiqra.edu.pk such as MySQL Tutorial or PostgreSQL Tutorial.

Core Concepts & Explanation

Difference Between Stored Procedures and Functions

A stored procedure is a reusable SQL program that performs actions like inserting, updating, or deleting data. It may or may not return a value.

A function always returns a value and is mostly used inside SELECT queries.

Example:

• Procedure → "Register a student in a class"
• Function → "Calculate student GPA"

MySQL Stored Procedure Basics

In MySQL, a stored procedure is created using:

DELIMITER //

CREATE PROCEDURE GetStudentInfo()
BEGIN
    SELECT * FROM students;
END //

DELIMITER ;

Line-by-line explanation:

• DELIMITER // → Changes statement ending so MySQL understands full procedure block
• CREATE PROCEDURE GetStudentInfo() → Defines procedure name
• BEGIN → Starts SQL block
• SELECT * FROM students; → Fetches all student records
• END → Ends procedure
• DELIMITER ; → Restores default delimiter

To execute:

CALL GetStudentInfo();

PostgreSQL Functions Overview

PostgreSQL uses functions more commonly than procedures.

CREATE FUNCTION get_total_students()
RETURNS INTEGER AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM students);
END;
$$ LANGUAGE plpgsql;

Line-by-line explanation:

• CREATE FUNCTION → Defines function
• RETURNS INTEGER → Specifies return type
• BEGIN → Starts logic block
• COUNT(*) → Counts total students
• RETURN → Sends result back
• LANGUAGE plpgsql → Specifies PostgreSQL procedural language

Call function:

SELECT get_total_students();

Key Concept: Input and Output Parameters

Stored procedures can accept parameters.

Example:

• Input: student_id = 101
• Output: student name, marks, grade

MySQL example:

CREATE PROCEDURE GetStudentByID(IN sid INT)
BEGIN
    SELECT * FROM students WHERE id = sid;
END;

Practical Code Examples

Example 1: Student Grade Calculation (MySQL Procedure)

Suppose a school in Lahore wants to calculate student grades automatically.

CREATE PROCEDURE CalculateGrade(IN marks INT)
BEGIN
    IF marks >= 80 THEN
        SELECT 'A Grade' AS Result;
    ELSEIF marks >= 60 THEN
        SELECT 'B Grade' AS Result;
    ELSE
        SELECT 'C Grade' AS Result;
    END IF;
END;

Line-by-line explanation:

• CREATE PROCEDURE CalculateGrade → Creates procedure
• IN marks INT → Accepts marks as input
• IF marks >= 80 → Checks condition for A grade
• SELECT 'A Grade' → Returns result
• ELSEIF → Handles second condition
• ELSE → Default case
• END IF → Ends condition block

Call it:

CALL CalculateGrade(75);

Example 2: Real-World Banking System (PostgreSQL Function)

A bank in Karachi needs to calculate account balance after interest.

CREATE FUNCTION calculate_balance(balance NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN balance + (balance * 0.05);
END;
$$ LANGUAGE plpgsql;

Line-by-line explanation:

• balance NUMERIC → Input account balance
• RETURNS NUMERIC → Output type
• balance * 0.05 → 5% interest
• RETURN → Returns final balance

Usage:

SELECT calculate_balance(10000);

Result: 10500 PKR

Common Mistakes & How to Avoid Them

Mistake 1: Forgetting Delimiters in MySQL

Many beginners forget to change delimiters when writing procedures.

Wrong:

CREATE PROCEDURE test()
BEGIN
SELECT * FROM students;
END;

Correct:

DELIMITER //
CREATE PROCEDURE test()
BEGIN
SELECT * FROM students;
END //
DELIMITER ;

Fix: Always use DELIMITER in MySQL procedures.

Mistake 2: Confusing Functions with Procedures

Students often use procedures where functions are required.

• Procedure → cannot be used inside SELECT
• Function → can be used inside SELECT

Wrong approach:

SELECT CALL my_procedure();

Correct:

CALL my_procedure();

Practice Exercises

Exercise 1: Employee Salary Bonus

Problem: Create a procedure that adds 10% bonus to salary if salary < 50000 PKR.

Solution:

CREATE PROCEDURE AddBonus(IN salary INT)
BEGIN
    IF salary < 50000 THEN
        SELECT salary + (salary * 0.10) AS NewSalary;
    ELSE
        SELECT salary AS NewSalary;
    END IF;
END;

Exercise 2: Student Pass/Fail Function

Problem: Create a PostgreSQL function that returns PASS if marks ≥ 40 else FAIL.

Solution:

CREATE FUNCTION check_result(marks INT)
RETURNS TEXT AS $$
BEGIN
    IF marks >= 40 THEN
        RETURN 'PASS';
    ELSE
        RETURN 'FAIL';
    END IF;
END;
$$ LANGUAGE plpgsql;

Frequently Asked Questions

What is a stored procedure in SQL?

A stored procedure is a saved SQL program that performs a specific task in the database. It can be executed using a CALL statement in MySQL or similar commands in PostgreSQL.

What is the difference between MySQL and PostgreSQL functions?

MySQL uses stored procedures more frequently, while PostgreSQL strongly supports functions with return values. PostgreSQL functions can be used inside SELECT queries.

Why should Pakistani students learn stored procedures?

They are widely used in software houses in Pakistan for payroll systems, banking applications, and e-commerce platforms, making them highly valuable for job preparation.

Can stored procedures return multiple values?

Yes, stored procedures can return multiple values using OUT parameters, while functions typically return a single value.

Are stored procedures faster than queries?

Yes, in many cases stored procedures are faster because they are precompiled and reduce repeated parsing of SQL statements.

Summary & Key Takeaways

• Stored procedures store reusable SQL logic inside the database
• Functions always return a value and are used in queries
• MySQL uses CALL to execute procedures
• PostgreSQL strongly supports functions with RETURNS
• Procedures are best for actions; functions are best for calculations
• Widely used in real-world systems like banking and payroll in Pakistan

Next Steps & Related Tutorials

Now that you understand SQL Stored Procedures & Functions, you should continue your learning journey with advanced database topics.

Recommended tutorials:

• Learn relational database design in MySQL Tutorial on theiqra.edu.pk
• Master advanced queries in PostgreSQL Tutorial
• Understand automation with SQL Triggers Tutorial
• Explore performance optimization in Database Indexing Guide

Continue practicing with real-world examples like student management systems, payroll systems, and banking applications to strengthen your skills.