Section 12: Introduction to Stored Programs

Procedures and Functions: part 1

This section is an introduction to Stored Programs in SQL.

SQL examples in this section will be using PostgreSQL syntax unless otherwise noted. There are no live in-browser examples as SQLite does not support stored procedures.

§1 Introduction to Stored Programs #

Stored programs (also known as “stored procedures” or “stored functions”) are executable code units that are stored within the database itself. They allow you to encapsulate complex SQL logic and execute it as a single unit. These programs are written using database-specific procedural languages. Unlike application-level programming logic, stored programs execute directly on the database server, close to the data, which can offer significant performance and security advantages.

§1.1 Database-Specific Procedural Languages #

Each major database management system (DBMS) typically has its own procedural language for writing stored programs, a few examples include:

All of these languages extend SQL with procedural constructs like variables, conditional statements (IF/ELSE), loops, and error handling. The syntax and specific features can differ significantly between each DBMS. This makes stored programs largely non-portable between different DBMS.

§1.2 Pros and Cons of Stored Programs #

Pros:

Cons:

§2 Stored Functions #

A stored function is a named block of code that is stored and executed on the database server. It performs a specific task and returns a single value (scalar) or a table, which can be used in SQL queries or other database operations.

§2.1 Syntax for CREATE FUNCTION #

The syntax for creating a function is similar to a procedure, but it includes a RETURNS clause to specify the data type of the value the function will return.

CREATE FUNCTION function_name (
  [ parameter_name data_type [ DEFAULT value ], ... ]
)
RETURNS data_type -- Specifies the data type of the return value
LANGUAGE plpgsql -- Optional Specify which procedural language is being used
AS $$
DECLARE -- Optional
  -- Can optionally declare temporary variables here
  var_name data_type;
BEGIN
  -- SQL statements and procedural logic go here
  RETURN return_value;
END;
$$;

§2.2 Example: Creating a Simple Function #

Let’s create a function that calculates the full name of an employee.

CREATE FUNCTION GetFullName (
  p_first_name VARCHAR(50),
  p_last_name VARCHAR(50)
)
RETURNS VARCHAR(101) -- Max length of first_name + space + last_name
LANGUAGE plpgsql
AS $$
DECLARE
  full_name VARCHAR(101);
BEGIN
  -- SELECT ... INTO declared_var, lets you store data in a temporary variable
  SELECT p_first_name || ' ' || p_last_name INTO full_name; 
  -- || is the SQL operator for string concatenation
  RETURN full_name;
END;
$$;

This function is named GetFullName, and is called with two VARCHAR(50) parameters, it returns a single VARCHAR(101).

§2.3 Calling Functions in SQL Queries #

Functions can be called directly within SQL queries, just like built-in functions such as COUNT() or SUM().

SELECT
  employee_id,
  GetFullName(first_name, last_name) AS full_name,
  email
FROM Employees;

 employee_id | full_name  |         email          
-------------+------------+------------------------
           1 | John Doe   | john.doe@example.com  
           2 | Jane Smith | jane.smith@example.com

§2.4 Example: Function for Salary Bonus Calculation #

This function calculates a “bonus amount” based on an employee’s salary and a given bonus percentage:

CREATE FUNCTION CalculateBonus (
  p_salary DECIMAL(10, 2),
  p_bonus_percentage DECIMAL(5, 2)
)
RETURNS DECIMAL(10, 2)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN ROUND(p_salary * (p_bonus_percentage / 100), 2);  -- function logic
END;
$$;

SELECT
  employee_id,
  first_name,
  last_name,
  salary,
  CalculateBonus(salary, 10.00) AS bonus_amount -- use function, Calculate a 10% bonus
FROM Employees;

 employee_id | first_name | last_name |  salary  | bonus_amount 
-------------+------------+-----------+----------+--------------
           1 | John       | Doe       | 60000.00 |      6000.00
           2 | Jane       | Smith     | 75000.00 |      7500.00

Here, the stored function receives two parameters: salary, and a hardcoded value 10.00. Any value that matches the function parameter data type can be utilized when calling the stored function.

§2.5 Updating an Existing function #

You can update an existing function by using a CREATE OR REPLACE statement. The CREATE statement will always fail if a stored function with the same name and parameters already exists.

CREATE OR REPLACE FUNCTION CalculateBonus ( --creates function or updates an existing function
  p_salary DECIMAL(10, 2),
  p_bonus_percentage DECIMAL(5, 2)
)
RETURNS DECIMAL(10, 2)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN ROUND(p_salary * (p_bonus_percentage / 100), 4); -- Calculate a 10% bonus, rounded to 4 decimals
END;
$$;

 employee_id | first_name | last_name |  salary  | bonus_amount 
-------------+------------+-----------+----------+--------------
           1 | John       | Doe       | 60000.00 |    6000.0000
           2 | Jane       | Smith     | 75000.00 |    7500.0000

§2.6 Function Overloading #

Function overloading is a feature in some database systems (like PostgreSQL, Oracle, and Microsoft SQL Server) that allows you to create multiple stored functions (or procedures) with the same name but with different input parameters.

The database system distinguishes between overloaded functions based on their signature, which includes the function’s name and the number, order, and data types of its input parameters. When a function is called, the database determines which specific version to execute by matching the provided arguments to the defined parameter lists.

-- Function 1: Calculates bonus based on a percentage
CREATE OR REPLACE FUNCTION CalculateBonus (
  p_salary DECIMAL(10, 2),
  p_bonus_percentage DECIMAL(5, 2)
)
RETURNS DECIMAL(10, 2)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN ROUND(p_salary * (p_bonus_percentage / 100), 2);
END;
$$;

-- Function 2: Calculates a fixed bonus amount
CREATE OR REPLACE FUNCTION CalculateBonus (
  p_fixed_amount DECIMAL(10, 2)
)
RETURNS DECIMAL(10, 2)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN ROUND(p_fixed_amount * (10 / 100), 2);
END;
$$;

Calling overloaded functions:

-- Calls the version with (DECIMAL, DECIMAL) parameters
SELECT CalculateBonus(50000.00, 15.00) AS bonus_by_percentage;

-- Calls the version with (DECIMAL) parameter
SELECT CalculateBonus(2500.00) AS fixed_bonus;

Note: Not all database systems support function overloading. For instance: MySQL requires all stored functions and procedures within the same schema to have unique names regardless of their parameter lists.

§3 Stored Procedures #

A Stored Procedure is a named block of SQL statements that performs a specific task. Procedures can accept input parameters and can return output parameters, but they do not necessarily return a single value like a function. They are primarily used for performing actions or manipulating data.

§3.1 Syntax for CREATE PROCEDURE #

The basic syntax for creating a stored procedure involves specifying the procedure’s name, its parameters (if any), and the body of the procedure. The procedure is written in the database’s specific procedural language. Unlike most standardized SQL queries/statements, procedures are highly specific to the database engine being used.

CREATE PROCEDURE procedure_name (
  [ parameter_name [ IN | OUT | INOUT ] data_type [ DEFAULT value ], ... ]
)
LANGUAGE plpgsql -- Or other procedural language like T-SQL, PL/SQL
AS $$
BEGIN
  -- SQL statements and procedural logic here
END;
$$;

Parameters:

§3.2 Example: Creating a Simple Procedure #

Let’s create a procedure to add a new employee.

CREATE TABLE Employees (
  employee_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  hire_date DATE DEFAULT CURRENT_DATE,
  salary DECIMAL(10, 2) CHECK (salary >= 0)
);

-- create the stored procedure
CREATE PROCEDURE AddEmployee (
  IN p_first_name VARCHAR(50),
  IN p_last_name VARCHAR(50),
  IN p_email VARCHAR(100),
  IN p_salary DECIMAL(10, 2)
)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO Employees (first_name, last_name, email, salary)
  VALUES (p_first_name, p_last_name, p_email, p_salary);
END;
$$;

In this example, we created a procedure to add a new employee. This procedure is named AddEmployee, and takes an employee’s first name, last name, email, and salary as input and inserts this information into the Employees table.

§3.3 Executing Simple Procedures #

Procedures are executed using the CALL statement (in PostgreSQL, MySQL, and Microsoft SQL Server 2016+). Other server versions may use EXEC or EXECUTE.

CALL AddEmployee('John', 'Doe', 'john.doe@example.com', 60000.00);
CALL AddEmployee('Jane', 'Smith', 'jane.smith@example.com', 75000.00);

SELECT * FROM Employees;

 employee_id | first_name | last_name |         email          | hire_date  |  salary  
-------------+------------+-----------+------------------------+------------+----------
           1 | John       | Doe       | john.doe@example.com   | 2025-11-08 | 60000.00
           2 | Jane       | Smith     | jane.smith@example.com | 2025-11-08 | 75000.00

§3.4 Example: Procedure with OUT Parameter #

An OUT procedure is mostly useful when used with client environment, such as psql or calling from application code (such as Python). It allows the procedure to return a value back to the calling application.

Note: Do not worry if you do not understand the python code samples in these sections, these are just demonstrations of application code calling the stored procedures.

CREATE PROCEDURE GetEmployeeCount (
  OUT p_employee_count INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT COUNT(*) INTO p_employee_count FROM Employees;
END;
$$;

Here is a basic demonstration of calling a procedure with an OUT parameter from a Python application, using psycopg2 (a common PostgreSQL adapter):

import psycopg2

# Establish a connection to your PostgreSQL database
conn = psycopg2.connect(
  host="localhost",
  database="your_database_name",
  user="your_username",
  password="your_password"
)
cur = conn.cursor()

try:
  # Call the procedure. For OUT parameters, psycopg2 returns them as a tuple.
  cur.callproc('GetEmployeeCount')
  employee_total = cur.fetchone()[0] # Fetch the result, which is a tuple containing the OUT parameter value

  print(f"Total Employees: {employee_total}")

except Exception as e:
  print(f"An error occurred: {e}")
finally:
  cur.close()
  conn.close()

Total Employees: 2

§3.5 Example: Procedure with IN Parameter #

An IN parameter allows you to pass values into a procedure to influence its execution. This is the most common type of parameter.

CREATE OR REPLACE PROCEDURE UpdateEmployeeSalary (
  IN p_employee_id INTEGER,
  IN p_new_salary DECIMAL(10, 2)
)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE Employees
  SET salary = p_new_salary
  WHERE employee_id = p_employee_id;
END;
$$;

Demonstration calling a procedure with IN parameters from a Python application using psycopg2. the line that uses the procedure is highlighted:

import psycopg2

# Establish a connection to your PostgreSQL database
conn = psycopg2.connect(
  host="localhost",
  database="your_database_name",
  user="your_username",
  password="your_password"
)
cur = conn.cursor()

try:
  # Call the procedure with IN parameters.
  # The parameters are passed as a tuple after the procedure name.
  employee_id_to_update = 1
  new_salary_value = 65000.00
  cur.callproc('UpdateEmployeeSalary', (employee_id_to_update, new_salary_value))
  conn.commit() # Commit the transaction to save changes

except Exception as e:
  conn.rollback() # Rollback in case of error
  print(f"An error occurred: {e}")
finally:
  cur.close()
  conn.close()

This example has no output, the UpdateEmployeeSalary procedure has no return values.

§4 Procedures vs. Functions #

While both stored procedures and functions encapsulate SQL logic, they have distinct characteristics and typical use cases.

FeatureStored ProcedureStored Function
Return ValueDoes not necessarily return a value; can “return” multiple values via OUT parameters or result sets.Must return a single scalar value or a table.
Usage in SQLCannot be directly called within SELECT, WHERE, or HAVING clauses. Executed using CALL (or EXEC).Can be called directly within SELECT, WHERE, or HAVING clauses, and other SQL expressions.
DML OperationsCan perform Data Manipulation Language (DML) operations (e.g., INSERT, UPDATE, DELETE).Generally, functions are designed to be side-effect free (e.g., not perform DML). Some DBMS allow DML in functions, but it’s often discouraged or restricted (PostgreSQL requires VOLATILE or STABLE for DML).
Transaction ControlCan contain transaction control statements (e.g., COMMIT, ROLLBACK).Typically cannot contain transaction control statements.
ParametersCan have IN, OUT, and INOUT parameters.Typically only have IN parameters.

§4.1 When to use Stored Procedures vs. Stored Functions: #

Use Stored Procedures for tasks that involve data modification, complex business logic, or when you need to return multiple result sets or OUT parameters. A few examples:

Use Stored Functions for calculations, data transformations, or when you need to use the result directly within a SQL query (e.g., in a SELECT list or WHERE clause). Examples:

§4.2 When NOT to use Stored Procedures/Functions: #

There is no explicit rule for when you can or can not use stored programs, but they are generally not recommended for situations such as:

§5 SQL Procedure/Function Comparison with Application Code: #

From a conceptual standpoint, the distinction between procedures and functions in SQL has parallels in how you might design application code (such as Python or Java):

SQL Procedures ≈ Code functions with side effects (or multiple return values):

A application code function that modifies global state, writes to a file, or updates a database (without returning a single value) is analogous to a stored procedure. If a Python function returns multiple values, it often does so via a tuple or a dictionary, similar to how a procedure might use OUT parameters or result sets.

# Python equivalent of a procedure (modifies data, no single return)
def update_user_profile(user_id, new_email):
  # ...Logic to update user email
  print(f"User {user_id} email updated to {new_email}")
  # no return data

SQL Functions ≈ Pure code functions (single return value, no side effects):

A code function that takes inputs, performs a calculation, then returns a single result without modifying any external state is comparable to a stored function.

# Python equivalent of a function (calculates and returns a single value)
def calculate_tax(amount, tax_rate):
  return amount * tax_rate

The key difference is that SQL stored programs execute within the database server, while your client-code executes on the application server. This distinction has significant impact on performance and security as discussed in the “Benefits” section.

§6 More Resources #

Stored Procedures

Stored Functions

Specific Database Procedural Languages

Questions #

  1. Name two advantages and two disadvantages of using stored programs (procedures or functions).
  1. What is the primary difference between a stored function and a stored procedure?
  1. Which procedural language is used to write stored programs in PostgreSQL?
  1. Can stored programs written for PostgreSQL be directly used in MySQL? Why or why not?
  1. Explain the concept of function overloading in the context of stored functions. Do all DBMS support this concept?
  1. When would you typically choose to use a stored procedure over a stored function? Provide an example.
  1. Provide one scenario where a stored function is preferable to a stored procedure.
  1. What is the SQL statement to run a stored procedure named my_procedure() in PostgreSQL, provide the full SQL statement.
  1. Explain the purpose of the IN, OUT, and INOUT parameters used in stored procedures.
  1. How do you update an existing stored function in PostgreSQL (without dropping it)?

For the remaining questions, assume you are working with a database containing two tables:

CREATE TABLE accounts (
  account_id   SERIAL PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL,
  balance      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_date DATE DEFAULT CURRENT_DATE,
  status       VARCHAR(20) DEFAULT 'active' -- e.g., 'active', 'frozen', 'closed'
);
INSERT INTO accounts (account_id, customer_name, balance, created_date, status)
VALUES
(1, 'Alice Johnson', 5000.00, '2024-01-15', 'active'),
(2, 'Bob Smith', 12000.00, '2023-03-22', 'active');

CREATE TABLE transactions (
  transaction_id SERIAL PRIMARY KEY,
  account_id     INT REFERENCES accounts(account_id),
  txn_type       VARCHAR(10) CHECK (txn_type IN ('deposit', 'withdrawal', 'transfer_out', 'transfer_in')),
  amount         DECIMAL(12,2) NOT NULL,
  txn_date       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO transactions (account_id, txn_type, amount, txn_date)
VALUES
-- Alice's transactions
(1, 'deposit', 5000.00, '2024-01-15 09:30:00'),
(1, 'withdrawal', 1200.00, '2024-02-01 14:20:00'),
(1, 'deposit', 800.00, '2024-03-10 11:05:00'),
-- Bob's transactions
(2, 'deposit', 12000.00, '2023-03-22 10:00:00'),
(2, 'withdrawal', 2500.00, '2024-01-05 16:45:00');

You can use the empty database from Assignment 6 to test your stored procedures/functions.

For each question, im expecting a SQL statement, not the output. you may use ... OR REPLACE ... for all answers to make testing easier.

Answer each question in isolation, do not re-use functionality defined for other questions unless explicitly requested.

  1. Create a function named get_balance that takes an account_id and returns the current balance as DECIMAL(12,2).
  1. Create a function named deposit that:

HINT: use a temporary variable

  1. Create a function named withdraw that:
  1. Create a stored procedure called transfer_funds that accepts:
  1. Write the SQL command to execute the transfer_funds procedure to transfer $300.00 from account 1 to account 2.
  1. Create a function get_interest_amount which takes p_balance DECIMAL(12,2) and returns NUMERIC(5,4), calculate interest as 0.25%.
  1. Create a procedure named apply_interest which takes account_id INT, and adds 0.02% to the balance