Stored Programs
What the domain tests
Stored Programs covers procedures, functions, triggers, and events: creation syntax, parameter modes, local variables, cursors, handlers, and execution characteristics. Questions test delimiters, return values, trigger timing, and the event scheduler.
Core rules
- DELIMITER changes the client statement terminator so a compound body can contain semicolons. It is a mysql client command, not server SQL.
- CREATE PROCEDURE supports IN, OUT, and INOUT parameters. CREATE FUNCTION must return a value and, when binary logging is enabled, needs a characteristic such as DETERMINISTIC, NO SQL, or READS SQL DATA.
- Inside programs use DECLARE for local variables, cursors, and handlers. Declarations must appear before executable statements in a block.
- A CONTINUE handler resumes after the error. An EXIT handler leaves the block. UNDO is not supported by MySQL stored-program handlers.
- Triggers fire BEFORE or AFTER INSERT, UPDATE, or DELETE. BEFORE triggers can modify NEW values; AFTER triggers cannot change the row. MySQL does not support statement-level triggers.
- Events run on a schedule but only when event_scheduler is ON. CREATE EVENT ... ON SCHEDULE EVERY ... DO ... defines recurring work.
- SIGNAL raises a user-defined error. RESIGNAL re-raises inside a handler.
Worked example
A procedure declares CONTINUE HANDLER FOR NOT FOUND SET done = 1; a cursor loop then exits when the cursor fetch finds no more rows. The reasoning is that the NOT FOUND condition is raised by the fetch, and the handler sets the loop flag instead of terminating the procedure.
Traps
- Sending a procedure body with semicolons without changing DELIMITER.
- Declaring a variable after an executable statement in the same block.
- Assuming a function may freely return result sets or ignore execution characteristics.
- Creating a trigger that tries to modify the table that fired it, which MySQL restricts.
- Expecting events to run with the scheduler disabled.