ADVANCED ADVANCED lesson-15

Stored Procedures, Functions & Transactions

🕑 35 min 📚 Lesson 15 of 15 ✅ 5 quiz questions

The final lesson. Stored procedures and functions let you encapsulate SQL logic on the server. Transactions ensure multi-step operations either fully succeed or fully roll back. Triggers automate actions on data changes.

Section 1

Stored Procedures — Reusable SQL Routines

A stored procedure is a named block of SQL saved on the database server. Call it with CALL. It can accept IN parameters (input) and OUT parameters (output).

SQL — Stored Procedure

DELIMITER //
CREATE PROCEDURE GetStudentsByGrade(IN grade_param VARCHAR(5))
BEGIN
  SELECT name, age, email
  FROM students
  WHERE grade = grade_param
  ORDER BY name;
END //
DELIMITER ;

-- Call it:
CALL GetStudentsByGrade('A');
CALL GetStudentsByGrade('B+');

Section 2

OUT Parameters

OUT parameters allow a procedure to return a value back to the caller. Store the result in a session variable prefixed with @.

SQL — OUT Parameter

DELIMITER //
CREATE PROCEDURE CountByGrade(
  IN  grade_param   VARCHAR(5),
  OUT student_count INT
)
BEGIN
  SELECT COUNT(*) INTO student_count
  FROM students
  WHERE grade = grade_param;
END //
DELIMITER ;

CALL CountByGrade('A', @result);
SELECT @result AS students_with_A;

Section 3

Stored Functions

A stored function is like a procedure but it returns a single value and can be called inside a SELECT statement. The DETERMINISTIC keyword tells MySQL the same inputs always produce the same output.

SQL — Stored Function

DELIMITER //
CREATE FUNCTION GetLetterGrade(score INT)
RETURNS VARCHAR(2) DETERMINISTIC
BEGIN
  IF score >= 90 THEN RETURN 'A+';
  ELSEIF score >= 80 THEN RETURN 'A';
  ELSEIF score >= 70 THEN RETURN 'B';
  ELSEIF score >= 60 THEN RETURN 'C';
  ELSE RETURN 'F';
  END IF;
END //
DELIMITER ;

SELECT name, score, GetLetterGrade(score) AS letter FROM exam_results;

Section 4

ACID Transactions

A transaction groups multiple SQL statements into an atomic unit — either all succeed (COMMIT) or all are undone (ROLLBACK). The ACID properties define what makes a transaction reliable:

Atomicity

All steps succeed or none do — no partial updates

Consistency

DB moves from one valid state to another — constraints always hold

Isolation

Concurrent transactions don't see each other's uncommitted changes

Durability

Once committed, changes survive crashes and restarts

SQL — Transactions & Savepoints

START TRANSACTION;

-- Transfer money: debit account A, credit account B
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- If both succeed:
COMMIT;
-- If any error: ROLLBACK;

-- Savepoints for partial rollback
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (5, 250.00);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 99);
-- Something went wrong with items, rollback to savepoint
ROLLBACK TO after_order;
-- Keep the order, discard the bad item insert
COMMIT;

Section 5

Triggers — Auto-Firing SQL

A trigger automatically executes a block of SQL before or after an INSERT, UPDATE, or DELETE on a table — without any explicit CALL.

SQL — Trigger

-- Auto-update a timestamp on row change
DELIMITER //
CREATE TRIGGER before_student_update
BEFORE UPDATE ON students
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END //
DELIMITER ;

📋

Stored Procedure

Reusable SQL routine called with CALL; can have IN/OUT params

CALL

⚙️

Stored Function

Returns a single value; usable inside SELECT expressions

RETURN

⚡

Trigger

Auto-fires on INSERT, UPDATE, or DELETE events on a table

Auto-fire

🔒

Transaction

Atomic multi-step operation; COMMIT or ROLLBACK

ACID

📍

SAVEPOINT

Named checkpoint inside a transaction for partial rollback

Partial

📅

Event

Scheduled SQL job run automatically at defined intervals

Scheduled

🏻 Knowledge Check — Procedures & Transactions

1. What does ACID stand for?

Correct! ACID = Atomicity (all or nothing), Consistency (valid states), Isolation (no interference), Durability (permanent after commit).

2. CALL executes a…

Correct! CALL is used to execute stored procedures. Stored functions are called inline in SELECT statements.

3. What is the key difference between a procedure and a function?

Correct! A stored function must return exactly one value (RETURNS clause). A procedure can do anything but isn't required to return a value.

4. ROLLBACK TO SAVEPOINT…

Correct! ROLLBACK TO savepoint_name undoes only the changes made since that savepoint — statements before the savepoint are preserved.

5. A TRIGGER fires…

Correct! Triggers are automatic — you never explicitly call them. They fire in response to data modification events on the specified table.