Part VI: Advanced SQL Concepts Chapter 13: Views, Stored Procedures, and Functions

Chapter 13: Views, Stored Procedures, and Functions

As your database needs grow more sophisticated, embedding business logic and reusable patterns directly into the database saves time, reduces errors, and simplifies application code. In this chapter, you’ll learn how to:

Define views to encapsulate complex queries and standardize data access
Write stored procedures that automate multi-step tasks and maintain transactional integrity
Create user-defined functions for reusable calculations and transformations
Manage parameters, implement error handling, and assign permissions for safe, controlled execution

Harnessing these programmability features turns your database from a simple data store into a powerful, self-documenting service layer.

1. Defining Views for Reusable Query Patterns

A view is a virtual table—essentially a named SELECT statement—that you can query as if it were a real table. Views simplify queries, enforce security, and provide a stable interface when underlying tables evolve.

1.1 Creating a Basic View

sql

CREATE VIEW active_customers AS
SELECT
  customer_id,
  first_name || ' ' || last_name AS full_name,
  email,
  signup_date
FROM customers
WHERE status = 'active';

Name: active_customers becomes a reusable object.
Logic Encapsulation: Filters out inactive accounts, concatenates names, and hides columns you don’t want exposed.

1.2 Benefits of Using Views

Simplicity: Application developers can SELECT * FROM active_customers without rewriting filters or joins.
Security: Grant SELECT on views rather than base tables to restrict access to sensitive columns.
Abstraction: Underlying table structures can change (column renames, schema reorganizations) while the view stays consistent.

1.3 Updatable vs. Read-Only Views

Read-only views (with aggregates, DISTINCT, or joins) cannot be modified through INSERT, UPDATE, or DELETE.
Updatable views map directly to a single base table without complex transformations:

sql

CREATE VIEW customer_emails AS
SELECT customer_id, email
FROM customers
WHERE email IS NOT NULL;

You can update email addresses via UPDATE customer_emails SET email = ....

1.4 Materialized Views for Performance

Some databases (PostgreSQL, Oracle, SQL Server’s Indexed Views) support materialized views that physically store results:

sql

CREATE MATERIALIZED VIEW monthly_sales_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
  DATE_TRUNC('month', order_date) AS sale_month,
  SUM(total_amount)               AS total_revenue
FROM orders
GROUP BY sale_month;

Use case: Results don’t change frequently and are costly to compute on the fly.
Trade-off: Requires storage and refresh strategies but yields dramatic read-performance gains.

2. Writing Stored Procedures to Automate Tasks

Stored procedures bundle multiple SQL statements and procedural logic into a single callable object. They reduce network chatter and centralize business rules.

2.1 Basic Syntax

A typical stored procedure in PostgreSQL’s PL/pgSQL:

sql

CREATE OR REPLACE PROCEDURE transfer_inventory(
  p_product_id   INT,
  p_from_location INT,
  p_to_location   INT,
  p_quantity      INT
)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE inventory
  SET stock = stock - p_quantity
  WHERE product_id = p_product_id
    AND location_id = p_from_location;

  INSERT INTO inventory_log(product_id, from_location, to_location, quantity, log_date)
  VALUES (p_product_id, p_from_location, p_to_location, p_quantity, NOW());

  UPDATE inventory
  SET stock = stock + p_quantity
  WHERE product_id = p_product_id
    AND location_id = p_to_location;
END;
$$;

Parameters: IN parameters by default.
Transaction Scope: All statements run in one transaction; rollback on error.

2.2 Control Flow and Error Handling

Enhance robustness with exception blocks:

sql

CREATE PROCEDURE safe_transfer(
  p_product INT, p_from INT, p_to INT, p_qty INT
)
LANGUAGE plpgsql
AS $$
BEGIN
  BEGIN
    PERFORM transfer_inventory(p_product, p_from, p_to, p_qty);
  EXCEPTION WHEN others THEN
    RAISE NOTICE 'Transfer failed: %', SQLERRM;
    ROLLBACK;  -- undo partial changes
    RETURN;
  END;
  RAISE NOTICE 'Transfer succeeded.';
END;
$$;

Nested BEGIN…EXCEPTION: Catches errors at the procedure level.
SQLERRM: Provides error message details.

2.3 Calling Procedures

sql

CALL transfer_inventory(1001, 1, 2, 50);

Procedures use CALL instead of SELECT.
Can return OUT parameters if defined.

3. Creating User-Defined Functions for Custom Calculations

User-defined functions (UDFs) return values and integrate seamlessly into queries.

3.1 Scalar Functions

Return a single value, used in SELECT, WHERE, or ORDER BY:

sql

CREATE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN ROUND(amount * 0.08, 2);
END;
$$;

Use inline:

sql

SELECT order_id, calculate_tax(total_amount) AS tax
FROM orders;

3.2 Table-Valued Functions

Return a set of rows, useful for encapsulating complex joins:

sql

CREATE FUNCTION orders_by_customer(p_cust_id INT)
RETURNS TABLE(order_id INT, order_date DATE, amount NUMERIC)
LANGUAGE sql
AS $$
  SELECT order_id, order_date, total_amount
  FROM orders
  WHERE customer_id = p_cust_id;
$$;

Query like:

sql

SELECT * FROM orders_by_customer(12345);

3.3 Deterministic vs. Non-Deterministic

Deterministic: Same input → same output (e.g., mathematical calculations).
Non-deterministic: Can vary (e.g., NOW(), random generators).

Deterministic functions can be indexed in computed columns and used in persisted expressions.

4. Managing Parameters, Error Handling, and Permissions

4.1 Parameter Modes

IN (default): Input values only.
OUT: Returns values to the caller.
INOUT: Acts as both input and output.

sql

CREATE PROCEDURE multi_return(
  IN p_customer INT,
  OUT total_orders INT,
  OUT last_order DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT COUNT(*), MAX(order_date)
    INTO total_orders, last_order
  FROM orders
  WHERE customer_id = p_customer;
END;
$$;

Invoke and fetch:

sql

CALL multi_return(123, total, last_date);

4.2 Error Handling Strategies

Use EXCEPTION blocks to trap and log errors.
Rethrow exceptions or return error codes/flags to the calling application.
Centralize logging to an audit table for troubleshooting.

4.3 Granting and Revoking Permissions

Control who can execute these programmable objects:

sql

-- Grant execute on procedure
GRANT EXECUTE ON PROCEDURE transfer_inventory(INT,INT,INT,INT) TO inventory_user;

-- Revoke
REVOKE EXECUTE ON FUNCTION calculate_tax(NUMERIC) FROM public;

Limit access to only necessary roles.
Combine with role-based security for robust protection.

5. Best Practices for Database Programmability

Modular Design: Keep each view, procedure, or function focused on a single responsibility.
Version Control: Store all DDL in Git or your SCM to track changes and enable rollbacks.
Documentation: Comment publicly visible parameters, side effects, and return values.
Testing: Validate logic in development/staging before deploying to production.
Performance Monitoring: Use execution plans and logs to identify slow routines; optimize indexing or rewrite inefficient logic.
Error Logging: Persist exception messages, stack traces, and input parameters for post-mortem analysis.

Conclusion

Views, stored procedures, and user-defined functions elevate your database from a passive data container to an active business logic engine. Encapsulating common queries in views, centralizing workflows in stored procedures, and exposing reusable calculations through functions reduces application complexity, enforces consistency, and delivers performant, maintainable solutions. With careful parameter management, robust error handling, and strict permission controls, you’ll harness the full power of database programmability to drive your organization’s data initiatives forward.

Big Data and AI

Search This Blog