🍲 Introduction: The Microwave Popcorn Button
Imagine you want to make microwave popcorn:
- The Manual Way: Open the microwave door, calculate weight, set heat level to 70%, type in
2:30minutes, hit start. If you make popcorn 5 times a day, repeating these steps gets annoying! - The Microwave Way (Preset): You put the bag inside and press the single button labeled "Popcorn". The microwave runs all the complex heat and timer calculations automatically.
In databases, Stored Procedures and Functions are these popcorn buttons! They let you save a long block of complex SQL statements directly in the database engine. Instead of sending 20 SQL lines from your web app over the network, you just type: CALL cook_popcorn()!
⚙️ Stored Procedures vs. Functions
While both represent stored code, they have very different rules:
Stored Procedure (CALL proc_name) User-Defined Function (SELECT func_name)
+----------------------------------+ +----------------------------------+
| - Can run multiple SQL updates | | - Must return a single value |
| - Supports transactions | | - No transactions allowed |
| - Does not return a single val | | - Can be used directly in SELECT |
+----------------------------------+ +----------------------------------+
| Feature | Stored Procedure | User-Defined Function (UDF) |
|---|---|---|
| Call style | Invoked using the CALL keyword. | Called inline within standard queries (like SUM()). |
| Return Value | Optional. Can return multiple outputs. | Must return exactly one value. |
| Transaction Control | Can start, commit, or rollback transactions. | Cannot manage transactions (read-only transactions). |
| Usage | Used to group large business logic writes. | Used for mathematical or string formatting tasks. |
💻 Code Examples
Let's write a stored function to calculate a tax rate and a procedure to complete a transfer.
SQL Setup & Queries
-- 1. Create a Stored Function (UDF)
CREATE FUNCTION get_tax(price DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
RETURN price * 0.08;
END;
$$ LANGUAGE plpgsql;
-- Call the function directly in a SELECT statement!
SELECT name, get_tax(price) FROM products;
-- 2. Create a Stored Procedure
CREATE PROCEDURE process_purchase(user_id INT, amount INT) AS $$
BEGIN
UPDATE accounts SET balance = balance - amount WHERE id = user_id;
INSERT INTO audits (user_id, action) VALUES (user_id, 'Purchase completed');
COMMIT;
END;
$$ LANGUAGE plpgsql;
-- Execute the procedure
CALL process_purchase(101, 50);Multi-Language Execution
Python (SQLite doesn't support procedures, but UDFs are super easy!)
import sqlite3
def run_udf_example():
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# Define a custom python function to register as UDF
def get_tax(price):
return price * 0.08
# Register the function under a SQL name
conn.create_function("get_tax", 1, get_tax)
cursor.execute("CREATE TABLE items (name TEXT, price REAL)")
cursor.execute("INSERT INTO items VALUES ('Book', 10.00)")
# Query calling the custom SQL function
cursor.execute("SELECT name, get_tax(price) FROM items")
name, tax = cursor.fetchone()
print(f"Item: {name}, Tax: ${tax:.2f}")
conn.close()
run_udf_example()⚠️ Common Mistakes
1. Putting Transaction blocks inside Functions
Trying to run COMMIT or ROLLBACK inside a user-defined function. Functions are meant to be pure arithmetic calculations; they cannot control database transaction boundaries! Use a Stored Procedure instead.
2. Overusing Procedures for simple updates
Wrapping simple queries in procedures. This makes database schemas heavy and difficult to deploy under version control systems. Keep logic in your application unless you explicitly need database-level performance or security isolation.
🔍 Interview Corner
Q1: What is the main difference between a Stored Procedure and a User-Defined Function (UDF)?
- User-Defined Functions must return a single value and can be called directly inside queries (e.g., in a
SELECTorWHEREclause). They cannot modify database state or run transactions. - Stored Procedures do not need to return a value, are called using
CALL, and can manage transactions (COMMIT/ROLLBACK).
Q2: Why are stored procedures considered good for security?
Procedures allow users to execute specific business actions (like transfer_funds) without giving them direct SELECT or UPDATE permissions on the raw tables (like the accounts table). This prevents SQL injection attacks and enforces secure execution policies.
🔍 Practice Links
Explore related coding challenges on the platform:
📝 Summary
- Functions return exactly one value and can run inline within SQL queries.
- Stored Procedures run multiple SQL lines and support transactions via the
CALLstatement. - Stored code reduces network traffic between your app and the database.