Lesson 12 • Expert
Views & Stored Procedures
Create reusable database objects — views for simplified queries and procedures for encapsulated logic.
🎯 What You'll Learn
- Create and use views as virtual tables
- Security views to restrict data access
- Stored procedures with IN and OUT parameters
- Stored functions for reusable calculations
- When to use views vs. procedures vs. functions
Database Views
A view is a saved query that acts like a virtual table. It doesn't store data — it runs the underlying query each time you access it.
🪟 Real-World Analogy
A view is like a window into your data. The data lives in tables (the rooms), but each view shows you a different perspective — one might show only sales data, another only customer profiles.
✅ Simplification
Hide complex 10-table JOINs behind a simple name
✅ Security
Expose only the columns/rows users should see
✅ Consistency
Everyone uses the same query logic
Creating & Using Views
Build virtual tables from complex queries
-- Create a view: virtual table from a query
CREATE VIEW customer_summary AS
SELECT
c.customer_id,
c.name,
COUNT(o.order_id) AS total_orders,
COALESCE(SUM(o.total), 0) AS total_spent,
MAX(o.order_date) AS last_order
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
-- Use it like a regular table
SELECT * FROM customer_summary
WHERE total_orders > 5
ORDER BY total_spent DESC;
-- Update the view definition
CREATE OR REPLACE
...Security & Reporting Views
Restrict data access and simplify reporting
-- Security view: expose only safe columns
CREATE VIEW public_employees AS
SELECT employee_id, first_name, department, job_title
FROM employees;
-- Salary, SSN, and personal info are hidden!
-- Row-level security view
CREATE VIEW my_orders AS
SELECT * FROM orders
WHERE customer_id = CURRENT_USER_ID();
-- Each user only sees their own orders
-- Simplified reporting view
CREATE VIEW monthly_revenue AS
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS order_count,
ROUND(SU
...Stored Procedures
Stored procedures are precompiled SQL programs stored in the database. They accept parameters, contain logic, and can execute multiple statements.
💡 Pro Tip
Stored procedures run on the database server, reducing network round-trips. Instead of sending 5 separate queries from your app, call one procedure.
⚠️ Common Mistake
Putting too much business logic in stored procedures. They're hard to version-control, test, and debug compared to application code. Use them for data-centric operations, not business rules.
Stored Procedures
Create procedures with input and output parameters
-- MySQL stored procedure with parameters
DELIMITER //
CREATE PROCEDURE GetCustomerOrders(
IN cust_id INT,
IN min_total DECIMAL(10,2)
)
BEGIN
SELECT order_id, order_date, total
FROM orders
WHERE customer_id = cust_id
AND total >= min_total
ORDER BY order_date DESC;
END //
DELIMITER ;
-- Call the procedure
CALL GetCustomerOrders(42, 100.00);
-- Procedure with output parameter
DELIMITER //
CREATE PROCEDURE GetTotalRevenue(
IN start_date DATE,
OUT total_rev D
...Stored Functions
Create reusable functions you can call inside queries
-- Stored function: returns a single value
CREATE FUNCTION calculate_discount(
price DECIMAL(10,2),
discount_pct INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN ROUND(price * (1 - discount_pct / 100.0), 2);
END;
-- Use in queries like a built-in function
SELECT
product_name,
price,
calculate_discount(price, 15) AS sale_price
FROM products;
-- PostgreSQL function syntax
CREATE OR REPLACE FUNCTION get_employee_count(dept TEXT)
RETURNS INTEGER AS $$
SELECT COUN
...When to Use What
| Use | When |
|---|---|
| View | Simplify complex SELECTs, provide security, create reporting layers |
| Procedure | Multi-step data modifications, batch operations, transactions |
| Function | Reusable calculations you need inside SELECT queries |
📘 Quick Reference
| Statement | Purpose |
|---|---|
| CREATE VIEW name AS SELECT ... | Create a virtual table |
| CREATE OR REPLACE VIEW | Update view definition |
| CREATE PROCEDURE name(...) | Create stored procedure |
| CALL procedure_name(args) | Execute a procedure |
| CREATE FUNCTION name(...) | Create reusable function |
🎉 Lesson Complete!
You can now create views, stored procedures, and functions for reusable database logic. Next, you'll learn about transactions and ACID — the foundation of data integrity!
Sign up for free to track which lessons you've completed and get learning reminders.