Views, Stored Procedures, and Functions

What Is It?

Views, Stored Procedures, and Functions: The Database's Abstractions

Once a schema grows past a dozen tables, your application starts repeating the same joins and filters in dozens of places. Three database objects let you stop repeating yourself: views turn a query into a reusable virtual table, stored procedures bundle multi-step logic into a named routine you can CALL, and functions behave like ordinary SQL functions (UPPER, ABS) that you wrote yourself.

-- A view is a saved SELECT that looks like a table
CREATE VIEW active_customers AS
SELECT id, name, email, city
FROM customers
WHERE status = 'ACTIVE' AND deleted_at IS NULL;

SELECT * FROM active_customers WHERE city = 'Pune';

-- A stored procedure is a named block of SQL you call
DELIMITER $
CREATE PROCEDURE transfer_money(
    IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2)
)
BEGIN
    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    COMMIT;
END$
DELIMITER ;

CALL transfer_money(101, 102, 500.00);

A view does not store rows; it stores a query. Every time you read from the view, MySQL re-runs the underlying SELECT. A stored procedure, on the other hand, is compiled code that lives inside the database and can contain variables, loops, IF/ELSE, transactions, and even cursors. Functions are a third flavour: they take parameters, return a single value, and can be called from inside any SELECT.

Why Does It Matter?

Why These Three Objects Matter

1. Hiding Schema Complexity from the Application

A frontend engineer writing a dashboard for Aarav does not care that active_customers actually requires joining customers, subscriptions, and user_flags while filtering on three status columns. A view hides all that. The application writes SELECT * FROM active_customers and the schema behind the view is free to change.

2. Security via Column and Row Filtering

You can grant a read-only analyst access to a view that exposes only five columns out of a 30-column employees table — no salary, no Aadhaar, no manager notes. The underlying table remains inaccessible. This is how production systems keep PII away from BI tools.

3. Atomic, Reusable Business Logic

A money transfer is not one UPDATE; it is debit + credit + audit log + balance check, all inside a transaction. If this logic lives in the application, every service that transfers money must re-implement it (and eventually one of them will do it wrong). A stored procedure enforces the sequence in one place.

4. Performance and Round Trips

A procedure that runs ten queries incurs one network round trip. The same ten queries from an application server means ten TCP round trips. For bulk jobs (monthly billing, nightly reconciliation) this difference is measured in hours.

5. Computed Columns Across the Database

If Priya defines a function gst_amount(price, state), every query in the warehouse can use it: SELECT order_id, gst_amount(total, ship_state) FROM orders. Tax logic lives in one place. When the rates change, one ALTER FUNCTION and every report is correct.

Detailed Explanation

Detailed Explanation

1. Creating and Using Views

A view is defined by a SELECT statement. When you query the view, MySQL substitutes the underlying SELECT at query-planning time (this is called view merging).

CREATE VIEW top_cities AS
SELECT city, COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 100;

-- Use it like any table
SELECT * FROM top_cities ORDER BY customer_count DESC;

-- Replace an existing view (safer than DROP + CREATE)
CREATE OR REPLACE VIEW top_cities AS
SELECT city, state, COUNT(*) AS customer_count
FROM customers
GROUP BY city, state
HAVING COUNT(*) > 100;

-- Drop
DROP VIEW IF EXISTS top_cities;

2. Updatable vs Non-Updatable Views

A view is updatable (supports INSERT/UPDATE/DELETE) only when MySQL can map every row back to exactly one row in one base table. These rules make a view non-updatable: aggregation (SUM, GROUP BY), DISTINCT, set operations (UNION), subqueries in the SELECT list, joins (in most cases), and window functions.

-- Updatable: simple filter on one table
CREATE VIEW pune_customers AS
SELECT id, name, email FROM customers WHERE city = 'Pune';

UPDATE pune_customers SET email = 'aarav@new.com' WHERE id = 5;
-- This actually updates the customers table

-- NOT updatable: has GROUP BY
CREATE VIEW city_stats AS
SELECT city, COUNT(*) AS n FROM customers GROUP BY city;

UPDATE city_stats SET n = 0 WHERE city = 'Pune';
-- ERROR 1288: The target table city_stats of the UPDATE is not updatable

3. WITH CHECK OPTION

By default, you can INSERT rows through an updatable view even if the new row would not appear in the view. WITH CHECK OPTION forbids this.

CREATE VIEW pune_customers AS
SELECT id, name, city FROM customers WHERE city = 'Pune'
WITH CHECK OPTION;

INSERT INTO pune_customers VALUES (99, 'Rohan', 'Mumbai');
-- ERROR 1369: CHECK OPTION failed 'pune_customers'

INSERT INTO pune_customers VALUES (99, 'Rohan', 'Pune');
-- OK

4. Materialized Views: The MySQL Workaround

A materialized view stores the query result on disk and refreshes it on schedule. PostgreSQL and Oracle support them natively. MySQL does not. The standard workaround is a real table plus an event that refreshes it.

-- The "materialized view" is actually a plain table
CREATE TABLE mv_daily_sales (
    sale_date DATE PRIMARY KEY,
    total_sales DECIMAL(12,2),
    order_count INT
);

-- A procedure that rebuilds it
DELIMITER $
CREATE PROCEDURE refresh_daily_sales()
BEGIN
    TRUNCATE TABLE mv_daily_sales;
    INSERT INTO mv_daily_sales
    SELECT DATE(created_at), SUM(total), COUNT(*)
    FROM orders
    GROUP BY DATE(created_at);
END$
DELIMITER ;

-- A scheduled event to refresh hourly
CREATE EVENT refresh_daily_sales_event
ON SCHEDULE EVERY 1 HOUR
DO CALL refresh_daily_sales();

You trade freshness for query speed. If reads run a thousand times per minute and the underlying data changes every few hours, this pattern pays for itself.

5. Stored Procedures: The Basics

A procedure is a named block of SQL that supports variables, flow control, and transactions. Because MySQL statements end with ;, you must temporarily change the statement delimiter while defining one.

DELIMITER $

CREATE PROCEDURE get_customer_count()
BEGIN
    SELECT COUNT(*) AS total FROM customers;
END$

DELIMITER ;

CALL get_customer_count();
-- Output: total = 1247

6. Parameters: IN, OUT, INOUT

• IN (default): caller passes a value in; the procedure reads it.
• OUT: the procedure writes a value out; the caller reads it after.
• INOUT: both directions.

DELIMITER $
CREATE PROCEDURE get_balance(
    IN  acct_id INT,
    OUT bal DECIMAL(10,2)
)
BEGIN
    SELECT balance INTO bal FROM accounts WHERE id = acct_id;
END$
DELIMITER ;

CALL get_balance(101, @b);
SELECT @b;  -- 5000.00

-- INOUT example: apply 10% interest
DELIMITER $
CREATE PROCEDURE add_interest(INOUT bal DECIMAL(10,2), IN rate DECIMAL(5,2))
BEGIN
    SET bal = bal * (1 + rate/100);
END$
DELIMITER ;

SET @x = 1000.00;
CALL add_interest(@x, 10);
SELECT @x;  -- 1100.00

7. DECLARE: Local Variables

DELIMITER $
CREATE PROCEDURE apply_bonus(IN emp_id INT)
BEGIN
    DECLARE current_salary DECIMAL(10,2);
    DECLARE bonus DECIMAL(10,2);

    SELECT salary INTO current_salary FROM employees WHERE id = emp_id;
    SET bonus = current_salary * 0.10;

    UPDATE employees SET salary = salary + bonus WHERE id = emp_id;
END$
DELIMITER ;

Local variables must be declared at the start of a BEGIN...END block, before any executable statement.

8. Control Flow: IF, CASE, WHILE, REPEAT, LOOP

-- IF / ELSEIF / ELSE
IF current_salary < 30000 THEN
    SET hike = 0.20;
ELSEIF current_salary < 60000 THEN
    SET hike = 0.15;
ELSE
    SET hike = 0.10;
END IF;

-- CASE
CASE level
    WHEN 'JUNIOR' THEN SET bonus = 5000;
    WHEN 'SENIOR' THEN SET bonus = 15000;
    ELSE SET bonus = 0;
END CASE;

-- WHILE
DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
    INSERT INTO numbers VALUES (i);
    SET i = i + 1;
END WHILE;

-- REPEAT (exit-condition loop)
SET i = 1;
REPEAT
    INSERT INTO numbers VALUES (i);
    SET i = i + 1;
UNTIL i > 10
END REPEAT;

-- LOOP with LEAVE (break)
my_loop: LOOP
    SET i = i + 1;
    IF i > 10 THEN LEAVE my_loop; END IF;
END LOOP my_loop;

9. Cursors: Row-by-Row Processing

A cursor lets you iterate over a result set one row at a time. Use them sparingly — a set-based UPDATE is usually 100x faster than a cursor loop. Cursors make sense when each row requires a distinct side effect (sending an email, calling another procedure).

DELIMITER $
CREATE PROCEDURE give_raise_to_top_performers()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE emp_rating INT;

    DECLARE cur CURSOR FOR
        SELECT id, rating FROM employees WHERE rating >= 4;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO emp_id, emp_rating;
        IF done = 1 THEN LEAVE read_loop; END IF;

        IF emp_rating = 5 THEN
            UPDATE employees SET salary = salary * 1.20 WHERE id = emp_id;
        ELSE
            UPDATE employees SET salary = salary * 1.10 WHERE id = emp_id;
        END IF;
    END LOOP;
    CLOSE cur;
END$
DELIMITER ;

The CONTINUE HANDLER FOR NOT FOUND sets the done flag when the cursor runs out of rows — this is the standard idiom.

10. User-Defined Functions

A function is like a procedure but returns exactly one value and can be called from a SELECT. Functions cannot modify data (in MySQL, they can, but it is strongly discouraged and requires DETERMINISTIC or READS SQL DATA qualifiers).

DELIMITER $
CREATE FUNCTION gst(price DECIMAL(10,2), rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(price * rate / 100, 2);
END$
DELIMITER ;

SELECT product, price, gst(price, 18) AS tax FROM products;

Keywords you must know:

• DETERMINISTIC: same inputs always produce the same output (needed for replication safety).
• READS SQL DATA: the function runs SELECTs.
• MODIFIES SQL DATA: the function runs INSERT/UPDATE/DELETE (rarely appropriate).
• NO SQL: pure computation, no table access.

11. Procedures vs Functions: The Real Differences

12. DROP, ALTER, SHOW

-- List everything
SHOW PROCEDURE STATUS WHERE Db = 'shop';
SHOW FUNCTION STATUS WHERE Db = 'shop';
SHOW CREATE PROCEDURE transfer_money;
SHOW CREATE VIEW active_customers;

-- Drop
DROP PROCEDURE IF EXISTS transfer_money;
DROP FUNCTION  IF EXISTS gst;
DROP VIEW      IF EXISTS active_customers;

-- You cannot ALTER a procedure's body; drop and recreate.
-- ALTER PROCEDURE only changes characteristics like SQL SECURITY.

13. SQL SECURITY: DEFINER vs INVOKER

By default a procedure runs with the privileges of the user who defined it (SQL SECURITY DEFINER). This lets you grant CALL permission to users who do not have direct access to the underlying tables — a common security pattern. Use SQL SECURITY INVOKER to run with the caller's privileges instead.

CREATE PROCEDURE view_salary(IN emp_id INT)
SQL SECURITY INVOKER
BEGIN
    SELECT salary FROM employees WHERE id = emp_id;
END;

Code Examples

-- Base table
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(15),
    aadhaar VARCHAR(12),
    status VARCHAR(10),
    deleted_at DATETIME NULL
);

INSERT INTO customers VALUES
(1, 'Aarav Sharma',  'aarav@mail.com',  '9876500001', '111122223333', 'ACTIVE',  NULL),
(2, 'Priya Nair',    'priya@mail.com',  '9876500002', '222233334444', 'ACTIVE',  NULL),
(3, 'Rohan Kapoor',  'rohan@mail.com',  '9876500003', '333344445555', 'INACTIVE',NULL),
(4, 'Anika Desai',   'anika@mail.com',  '9876500004', '444455556666', 'ACTIVE',  '2026-03-01 10:00:00');

-- View exposes only non-PII columns and only live, active rows
CREATE VIEW active_customers AS
SELECT id, name, email
FROM customers
WHERE status = 'ACTIVE' AND deleted_at IS NULL;

SELECT * FROM active_customers;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    total DECIMAL(10,2),
    region VARCHAR(20)
);

INSERT INTO orders (customer_id, total, region) VALUES
(1, 1500.00, 'WEST'),
(2,  800.00, 'WEST'),
(3, 2100.00, 'NORTH');

CREATE VIEW west_orders AS
SELECT id, customer_id, total, region
FROM orders
WHERE region = 'WEST'
WITH CHECK OPTION;

-- This would silently succeed without CHECK OPTION
-- With it, MySQL blocks the insert
INSERT INTO west_orders (customer_id, total, region)
VALUES (4, 500.00, 'EAST');
-- ERROR 1369 (HY000): CHECK OPTION failed 'shop.west_orders'

INSERT INTO west_orders (customer_id, total, region)
VALUES (4, 500.00, 'WEST');
-- OK

SELECT * FROM west_orders;

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    holder VARCHAR(50),
    balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts VALUES
(101, 'Aarav', 5000.00),
(102, 'Priya', 2000.00);

DELIMITER $
CREATE PROCEDURE transfer_money(
    IN  from_id INT,
    IN  to_id   INT,
    IN  amount  DECIMAL(10,2)
)
BEGIN
    DECLARE from_balance DECIMAL(10,2);

    START TRANSACTION;

    SELECT balance INTO from_balance
    FROM accounts WHERE id = from_id FOR UPDATE;

    IF from_balance < amount THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;

    COMMIT;
END$
DELIMITER ;

CALL transfer_money(101, 102, 1500.00);
SELECT * FROM accounts;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept VARCHAR(20),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, 'Aarav',  'ENG', 90000),
(2, 'Priya',  'ENG', 75000),
(3, 'Rohan',  'SAL', 60000),
(4, 'Anika',  'ENG', 110000);

DELIMITER $
CREATE PROCEDURE avg_salary_by_dept(
    IN  dept_name VARCHAR(20),
    OUT avg_sal   DECIMAL(10,2)
)
BEGIN
    SELECT AVG(salary) INTO avg_sal
    FROM employees WHERE dept = dept_name;
END$
DELIMITER ;

CALL avg_salary_by_dept('ENG', @avg);
SELECT @avg AS engineering_avg;

CREATE TABLE numbers (n INT PRIMARY KEY);

DELIMITER $
CREATE PROCEDURE fill_numbers(IN up_to INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= up_to DO
        INSERT INTO numbers VALUES (i);
        SET i = i + 1;
    END WHILE;
END$
DELIMITER ;

CALL fill_numbers(5);
SELECT * FROM numbers ORDER BY n;

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10,2),
    gst_rate DECIMAL(5,2)
);

INSERT INTO products VALUES
(1, 'Notebook',    120.00,  5.00),
(2, 'Headphones', 1999.00, 18.00),
(3, 'T-shirt',     499.00, 12.00);

DELIMITER $
CREATE FUNCTION gst(price DECIMAL(10,2), rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(price * rate / 100, 2);
END$
DELIMITER ;

SELECT name, price, gst_rate,
       gst(price, gst_rate) AS tax,
       price + gst(price, gst_rate) AS total
FROM products;

CREATE TABLE employees_bonus (
    id INT PRIMARY KEY,
    emp_id INT,
    bonus DECIMAL(10,2)
);

DELIMITER $
CREATE PROCEDURE compute_yearly_bonus()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE cur_id INT;
    DECLARE cur_salary DECIMAL(10,2);
    DECLARE cur_dept VARCHAR(20);
    DECLARE bonus_amt DECIMAL(10,2);

    DECLARE emp_cur CURSOR FOR
        SELECT id, salary, dept FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN emp_cur;
    emp_loop: LOOP
        FETCH emp_cur INTO cur_id, cur_salary, cur_dept;
        IF done = 1 THEN LEAVE emp_loop; END IF;

        IF cur_dept = 'ENG' THEN
            SET bonus_amt = cur_salary * 0.15;
        ELSE
            SET bonus_amt = cur_salary * 0.10;
        END IF;

        INSERT INTO employees_bonus (emp_id, bonus)
        VALUES (cur_id, bonus_amt);
    END LOOP;
    CLOSE emp_cur;
END$
DELIMITER ;

CALL compute_yearly_bonus();
SELECT * FROM employees_bonus;

Common Mistakes

CREATE PROCEDURE greet()
BEGIN
    SELECT 'Hello';
    SELECT 'World';
END;
-- MySQL client thinks the first ; ends the whole CREATE statement.
-- Result: syntax error, or a half-built procedure.

DELIMITER $

CREATE PROCEDURE greet()
BEGIN
    SELECT 'Hello';
    SELECT 'World';
END$

DELIMITER ;

CALL greet();

CREATE VIEW dept_totals AS
SELECT dept, SUM(salary) AS total
FROM employees
GROUP BY dept;

UPDATE dept_totals SET total = 0 WHERE dept = 'ENG';
-- ERROR 1288 (HY000): The target table dept_totals of the UPDATE is not updatable

-- If you want to update the underlying salaries, target the base table
UPDATE employees SET salary = 0 WHERE dept = 'ENG';

-- A view with aggregation is read-only by definition. Any view that
-- uses GROUP BY, DISTINCT, UNION, a subquery in SELECT, or most JOINs
-- becomes non-updatable.

DELIMITER $
CREATE PROCEDURE bad_order()
BEGIN
    SET @x = 5;                        -- user variable, fine
    SELECT COUNT(*) FROM employees;    -- executable statement
    DECLARE i INT DEFAULT 0;           -- too late!
END$
DELIMITER ;
-- ERROR 1064: syntax error near 'DECLARE i INT DEFAULT 0'

DELIMITER $
CREATE PROCEDURE good_order()
BEGIN
    DECLARE i INT DEFAULT 0;           -- all DECLAREs first
    SELECT COUNT(*) INTO i FROM employees;
    SET @x = i;
END$
DELIMITER ;

DELIMITER $
CREATE PROCEDURE broken_cursor()
BEGIN
    DECLARE cur CURSOR FOR SELECT id FROM employees;
    DECLARE emp_id INT;
    OPEN cur;
    my_loop: LOOP
        FETCH cur INTO emp_id;   -- raises NOT FOUND when empty
        -- No handler! The procedure errors out at the end.
    END LOOP my_loop;
    CLOSE cur;
END$
DELIMITER ;

DELIMITER $
CREATE PROCEDURE working_cursor()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE cur CURSOR FOR SELECT id FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    my_loop: LOOP
        FETCH cur INTO emp_id;
        IF done = 1 THEN LEAVE my_loop; END IF;
        -- do something with emp_id
    END LOOP my_loop;
    CLOSE cur;
END$
DELIMITER ;

DELIMITER $
CREATE FUNCTION gst(price DECIMAL(10,2), rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
BEGIN
    RETURN ROUND(price * rate / 100, 2);
END$
DELIMITER ;
-- ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
-- or READS SQL DATA in its declaration and binary logging is enabled

DELIMITER $
CREATE FUNCTION gst(price DECIMAL(10,2), rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(price * rate / 100, 2);
END$
DELIMITER ;

Summary