Views and Stored Procedures

Views and procedures keep complex SQL on the database where every application can reuse it.

Views

A view is a saved SELECT. It behaves like a table for reading and hides complexity from the application.

CREATE VIEW active_students AS
SELECT id, name, level, email
FROM students
WHERE is_active = 1;

SELECT * FROM active_students WHERE level = 'SS2';

Stored Procedures

A procedure groups statements and can accept parameters. Use them for repetitive maintenance tasks, not for every query.

DELIMITER //

CREATE PROCEDURE promote_students(IN target_level VARCHAR(20))
BEGIN
    UPDATE students
    SET level = target_level
    WHERE is_active = 1
      AND level = 'SS1';
END //

DELIMITER ;

CALL promote_students('SS2');

Trade-offs

Views are simple and safe to read. Procedures can be harder to version and test. Keep business logic in application code when the team is small.

Practice

1. Create a view of published courses.
2. Write a procedure that accepts a student id and marks them inactive.
3. Explain one downside of putting business logic in procedures.

Hands-on exercise

Create a view called course_catalogue that returns id, title and price for active courses, ordered by title. Then select from it.

Recap

Views save reusable reads and hide complexity. Procedures group writes but add maintenance cost.