Stored Procedures and SQL Functions

Real-World Analogy

Imagine you're at a bank:

Stored procedure is like a bank's "standard business process" — the teller follows predefined steps, and you just say "I want to make a deposit," and the entire process runs automatically.
Function is like a function key on a calculator — input parameters, return a result, and it can be embedded in larger calculations.

Core Concepts

What Is a Stored Procedure

A Stored Procedure is a precompiled set of SQL statements stored in the database that can:

Accept input parameters
Execute complex business logic
Return result sets or status codes
Improve performance (precompiled, reduced network traffic)

💡 SQLite does not support stored procedures. The examples below demonstrate concepts and MySQL/PostgreSQL syntax. SQLite users can use custom functions as an alternative.

MySQL Stored Procedure Syntax

SQL

-- MySQL stored procedure basic structure
DELIMITER //
CREATE PROCEDURE procedure_name (parameter_list)
BEGIN
    -- Variable declarations
    -- Business logic
    -- Return results
END //
DELIMITER ;

PostgreSQL Stored Procedure Syntax

SQL

-- PostgreSQL stored procedure basic structure
CREATE OR REPLACE FUNCTION procedure_name(parameter_list)
RETURNS return_type AS $$
DECLARE
    -- Variable declarations
BEGIN
    -- Business logic
    -- RETURN statement
END;
$$ LANGUAGE plpgsql;

SQLite Custom Functions

SQLite does not support stored procedures, but it supports creating user-defined functions through programming languages:

SQL

-- SQLite itself does not support CREATE FUNCTION
-- You need to register custom functions via Python, C, or other programming languages

-- Python example (using sqlite3 module)
import sqlite3

def calculate_bonus(salary, rate):
    return salary * rate

conn = sqlite3.connect('company.db')
conn.create_function('calculate_bonus', 2, calculate_bonus)

-- After registration, you can use it in SQL
SELECT name, salary, calculate_bonus(salary, 0.1) AS bonus
FROM employees;

Flow Control Statements

IF/ELSE Conditional Logic

SQL

-- MySQL example
DELIMITER //
CREATE PROCEDURE check_salary(IN emp_id INT)
BEGIN
    DECLARE emp_salary DECIMAL(10,2);
    
    SELECT salary INTO emp_salary
    FROM employees WHERE id = emp_id;
    
    IF emp_salary > 10000 THEN
        SELECT 'High salary' AS status;
    ELSEIF emp_salary > 5000 THEN
        SELECT 'Medium salary' AS status;
    ELSE
        SELECT 'Low salary' AS status;
    END IF;
END //
DELIMITER ;

WHILE Loop

SQL

-- MySQL example: calculate the sum from 1 to N
DELIMITER //
CREATE PROCEDURE calculate_sum(IN n INT, OUT total INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET total = 0;
    
    WHILE i <= n DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

LOOP Loop

SQL

-- MySQL example: LOOP with LEAVE
DELIMITER //
CREATE PROCEDURE process_employees()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_name VARCHAR(100);
    
    DECLARE cur CURSOR FOR SELECT name FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- Process each record
        SELECT CONCAT('Processing: ', emp_name);
    END LOOP;
    
    CLOSE cur;
END //
DELIMITER ;

Cursor Concepts

Cursors are used to process query result sets row by row:

SQL

-- Basic cursor usage flow
-- 1. Declare the cursor
DECLARE cur CURSOR FOR SELECT column FROM table;

-- 2. Open the cursor
OPEN cur;

-- 3. Fetch data
FETCH cur INTO variable;

-- 4. Process data (usually in a loop)

-- 5. Close the cursor
CLOSE cur;

Stored Functions

Stored functions are similar to stored procedures but have important differences:

SQL

-- MySQL stored function
DELIMITER //
CREATE FUNCTION get_annual_salary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN monthly_salary * 12;
END //
DELIMITER ;

-- Use the function
SELECT name, get_annual_salary(salary) AS annual_salary
FROM employees;

Stored Procedures vs Functions

Feature	Stored Procedure	Stored Function
Calling method	CALL statement	Called directly in SQL
Return value	Can return multiple result sets	Must return a single value
Use case	Complex business logic	Calculation and transformation
Transaction control	Can manage transactions	Cannot manage transactions
In SQL statements	Cannot be used in SELECT	Can be used in SELECT

Basic Syntax

Creating a Stored Procedure (MySQL)

SQL

-- Create a stored procedure with parameters
DELIMITER //
CREATE PROCEDURE get_employees_by_dept(
    IN dept_name VARCHAR(50),
    IN min_salary DECIMAL(10,2)
)
BEGIN
    SELECT e.name, e.salary, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    WHERE d.department_name = dept_name
    AND e.salary >= min_salary
    ORDER BY e.salary DESC;
END //
DELIMITER ;

-- Call the stored procedure
CALL get_employees_by_dept('Tech', 8000);

Creating a Stored Function (PostgreSQL)

SQL

-- Create a function to calculate bonus
CREATE OR REPLACE FUNCTION calculate_bonus(
    base_salary NUMERIC,
    performance_rating INTEGER
)
RETURNS NUMERIC AS $$
DECLARE
    bonus_rate NUMERIC;
BEGIN
    CASE performance_rating
        WHEN 1 THEN bonus_rate := 0.10;
        WHEN 2 THEN bonus_rate := 0.15;
        WHEN 3 THEN bonus_rate := 0.20;
        WHEN 4 THEN bonus_rate := 0.25;
        WHEN 5 THEN bonus_rate := 0.30;
        ELSE bonus_rate := 0.05;
    END CASE;
    
    RETURN base_salary * bonus_rate;
END;
$$ LANGUAGE plpgsql;

-- Use the function
SELECT 
    name,
    salary,
    performance_rating,
    calculate_bonus(salary, performance_rating) AS bonus
FROM employees;

💡 Tip: Stored procedures are suitable for encapsulating complex business logic, while functions are suitable for reusable calculations. Choose the appropriate approach based on actual needs.

Examples

Example: Creating an Employee Onboarding Stored Procedure

Create a stored procedure to handle the new employee onboarding process, including assigning an employee number, setting initial salary, and recording onboarding information.

SQL

-- MySQL version
DELIMITER //
CREATE PROCEDURE hire_employee(
    IN p_name VARCHAR(100),
    IN p_department_id INT,
    IN p_position VARCHAR(50),
    IN p_salary DECIMAL(10,2),
    OUT p_employee_id INT,
    OUT p_message VARCHAR(200)
)
BEGIN
    DECLARE dept_exists INT DEFAULT 0;
    DECLARE max_id INT DEFAULT 0;
    
    -- Check if the department exists
    SELECT COUNT(*) INTO dept_exists
    FROM departments WHERE id = p_department_id;
    
    IF dept_exists = 0 THEN
        SET p_message = 'Error: The specified department does not exist';
        SET p_employee_id = -1;
    ELSE
        -- Get the maximum employee ID
        SELECT COALESCE(MAX(id), 0) INTO max_id FROM employees;
        SET p_employee_id = max_id + 1;
        
        -- Insert the new employee record
        INSERT INTO employees (id, name, department_id, position, salary, hire_date)
        VALUES (p_employee_id, p_name, p_department_id, p_position, p_salary, CURDATE());
        
        SET p_message = CONCAT('Success: Employee ', p_name, ' has been onboarded with ID ', p_employee_id);
    END IF;
END //
DELIMITER ;

-- Call example
CALL hire_employee('John Doe', 1, 'Junior Engineer', 8000.00, @new_id, @msg);
SELECT @new_id AS employee_id, @msg AS message;

▶ Try it Yourself

Example: PostgreSQL Version — Employee Salary Adjustment Function

SQL

-- PostgreSQL version
CREATE OR REPLACE FUNCTION adjust_salary(
    p_employee_id INTEGER,
    p_adjustment_type VARCHAR(10),  -- 'RAISE' or 'REDUCE'
    p_percentage NUMERIC
)
RETURNS TABLE(
    employee_name VARCHAR,
    old_salary NUMERIC,
    new_salary NUMERIC,
    adjustment_amount NUMERIC
) AS $$
DECLARE
    v_old_salary NUMERIC;
    v_new_salary NUMERIC;
    v_name VARCHAR;
BEGIN
    -- Get the current salary
    SELECT name, salary INTO v_name, v_old_salary
    FROM employees WHERE id = p_employee_id;
    
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Employee ID % does not exist', p_employee_id;
    END IF;
    
    -- Calculate the new salary
    IF p_adjustment_type = 'RAISE' THEN
        v_new_salary := v_old_salary * (1 + p_percentage / 100);
    ELSE
        v_new_salary := v_old_salary * (1 - p_percentage / 100);
    END IF;
    
    -- Update the database
    UPDATE employees SET salary = v_new_salary WHERE id = p_employee_id;
    
    -- Return the result
    employee_name := v_name;
    old_salary := v_old_salary;
    new_salary := v_new_salary;
    adjustment_amount := v_new_salary - v_old_salary;
    
    RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

-- Usage example
SELECT * FROM adjust_salary(1, 'RAISE', 10);

▶ Try it Yourself

Application Scenarios

Scenario 1: Batch Data Processing

SQL

-- MySQL: batch update order status
DELIMITER //
CREATE PROCEDURE update_order_status()
BEGIN
    DECLARE affected_rows INT DEFAULT 0;
    
    -- Start transaction
    START TRANSACTION;
    
    -- Cancel orders that have been pending for over 30 days
    UPDATE orders 
    SET status = 'cancelled', 
        updated_at = NOW()
    WHERE status = 'pending' 
    AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
    
    SET affected_rows = ROW_COUNT();
    
    -- Log the processing
    INSERT INTO process_log (process_name, affected_rows, executed_at)
    VALUES ('update_order_status', affected_rows, NOW());
    
    -- Commit the transaction
    COMMIT;
    
    SELECT CONCAT('Cancelled ', affected_rows, ' expired orders') AS result;
END //
DELIMITER ;

Scenario 2: Complex Report Generation

SQL

-- PostgreSQL: generate a department salary report
CREATE OR REPLACE FUNCTION generate_salary_report()
RETURNS TABLE(
    department_name VARCHAR,
    employee_count BIGINT,
    avg_salary NUMERIC,
    min_salary NUMERIC,
    max_salary NUMERIC,
    total_salary NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        d.department_name,
        COUNT(e.id) AS employee_count,
        ROUND(AVG(e.salary), 2) AS avg_salary,
        MIN(e.salary) AS min_salary,
        MAX(e.salary) AS max_salary,
        SUM(e.salary) AS total_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.department_name
    ORDER BY total_salary DESC;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT * FROM generate_salary_report();

❓ FAQ

Q: SQLite doesn't support stored procedures at all — what should I do? A: SQLite is designed as a lightweight embedded database and doesn't support stored procedures. You can implement business logic using programming languages (Python, Java, etc.), or use views (VIEW) to encapsulate complex queries.

Q: What's the difference between a stored procedure and a view? A: A view is a virtual table, primarily used to simplify queries; a stored procedure is a program that can contain complex business logic, flow control, and data modification operations.

Q: When should I use stored procedures? A: Use them when you need to encapsulate complex business logic, reduce network traffic, improve performance, or unify data access interfaces. Simple queries are better suited for views.

Q: How do I debug a stored procedure? A: MySQL can use SELECT statements to output intermediate results; PostgreSQL can use RAISE NOTICE to output debug information; you can also use the debugging features of database management tools.

📖 Summary

In this lesson we learned:

The concepts and purpose of stored procedures
MySQL and PostgreSQL stored procedure syntax
SQLite's custom function alternatives
Flow control statements (IF/ELSE, WHILE, LOOP)
Cursor concepts and usage
The difference between stored functions and stored procedures
Application in real business scenarios

📝 Exercises

Basic Exercise: Create a stored procedure that accepts a department ID as a parameter and returns the top 3 highest-paid employees in that department.
Intermediate Exercise: Create a function that calculates seniority pay based on the employee's years of service (from hire_date to the current date):
- 1–3 years: 5% of base salary
- 3–5 years: 10% of base salary
- Over 5 years: 15% of base salary
Thinking Question: Compare the pros and cons of processing business logic in application-layer code vs database stored procedures.

Next Lesson → 23-triggers-events.md

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

Stored Procedures and SQL Functions

Stored Procedures and SQL Functions

Real-World Analogy

Core Concepts

What Is a Stored Procedure

MySQL Stored Procedure Syntax

PostgreSQL Stored Procedure Syntax

SQLite Custom Functions

Flow Control Statements

IF/ELSE Conditional Logic

WHILE Loop

LOOP Loop

Cursor Concepts

Stored Functions

Stored Procedures vs Functions

Basic Syntax

Creating a Stored Procedure (MySQL)

Creating a Stored Function (PostgreSQL)

Examples

Example: Creating an Employee Onboarding Stored Procedure

Example: PostgreSQL Version — Employee Salary Adjustment Function

Application Scenarios

Scenario 1: Batch Data Processing

Scenario 2: Complex Report Generation

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好