Some logic belongs next to the data: a query that ten services repeat, or a multi-step change that must always happen the same way. MySQL offers views for saved queries, and stored procedures and functions for saved programs. This lesson builds one of each for Orbit.
Views
A view is a named SELECT. It stores no data; querying it runs the underlying query. Orbit’s API shows notes with their author and tags in several places, so give that query a name:
CREATE VIEW note_list AS
SELECT n.id,
n.title,
u.name AS author,
n.pinned,
n.created_at,
GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS tags
FROM notes n
JOIN users u ON u.id = n.user_id
LEFT JOIN note_tags nt ON nt.note_id = n.id
LEFT JOIN tags t ON t.id = nt.tag_id
GROUP BY n.id;
SELECT id, title, author, tags FROM note_list ORDER BY id;
+----+------------------+------------+-----------------+
| id | title | author | tags |
+----+------------------+------------+-----------------+
| 1 | Shopping list | Ana Silva | personal |
| 2 | Deploy checklist | Ana Silva | work |
| 3 | Book ideas | Ben Carter | personal |
| 4 | MySQL tips | Ben Carter | databases, work |
| 5 | Meeting notes | Ana Silva | work |
+----+------------------+------------+-----------------+
5 rows in set (0.00 sec)
Views help in three ways:
- Simplicity: callers write
SELECT ... FROM note_list WHERE author = 'Ana Silva'instead of four joins. - Consistency: the definition of “a note with its tags” lives in one place.
- Security: grant an account access to a view and not to the base tables, and it only sees the columns the view exposes (lesson 18).
SHOW CREATE VIEW note_list\G prints the stored definition, CREATE OR REPLACE VIEW changes it, RENAME TABLE note_list TO notes_overview renames it and DROP VIEW note_list removes it. A view and a table cannot share a name, and SHOW FULL TABLES lists views with Table_type = VIEW.
How MySQL runs a view
MySQL chooses an algorithm for each view:
MERGE: the view’s text is merged into your query, so yourWHEREconditions reach the base tables and their indexes. Fast.TEMPTABLE: the view is computed into a temporary table first, then your query reads it. Used when the view containsGROUP BY,DISTINCT, aggregates,UNIONorLIMIT.
note_list contains GROUP BY, so it uses TEMPTABLE. MySQL 8 can often still push conditions such as WHERE id = 4 down into the derived result, but check with EXPLAIN that queries on aggregating views do not compute the whole result every time.
Updatable views and WITH CHECK OPTION
A simple view over one table, without aggregates or DISTINCT, is updatable: you can INSERT, UPDATE and DELETE through it.
CREATE VIEW pinned_notes AS
SELECT id, user_id, title, pinned FROM notes WHERE pinned
WITH CHECK OPTION;
UPDATE pinned_notes SET pinned = FALSE WHERE id = 1;
ERROR 1369 (HY000): CHECK OPTION failed 'orbit.pinned_notes'
WITH CHECK OPTION rejects changes that would make the row disappear from the view. Without it, the update would succeed and the note would silently leave pinned_notes. With views built on other views, CASCADED (the default) checks the conditions of every underlying view; LOCAL only checks this one.
DROP VIEW pinned_notes;
Stored procedures
A stored procedure is a named program stored in the database and run with CALL. Its body contains several statements separated by ;, which would confuse the mysql client into sending the definition early. Change the client’s delimiter while you define it:
DELIMITER //
CREATE PROCEDURE notes_by_user(IN p_email VARCHAR(255))
BEGIN
SELECT n.id, n.title
FROM notes n
JOIN users u ON u.id = n.user_id
WHERE u.email = p_email
ORDER BY n.id;
END //
DELIMITER ;
CALL notes_by_user('ben@orbit.dev');
+----+------------+
| id | title |
+----+------------+
| 3 | Book ideas |
| 4 | MySQL tips |
+----+------------+
2 rows in set (0.00 sec)
DELIMITER is a client command, not SQL: GUI tools and drivers send the whole CREATE PROCEDURE in one call and do not need it.
Parameters and variables
Parameters are IN (passed in, the default), OUT (returned to the caller) or INOUT (both). Inside BEGIN ... END, declare local variables with DECLARE at the top:
DELIMITER //
CREATE PROCEDURE user_stats(IN p_user_id INT UNSIGNED, OUT p_notes INT, OUT p_label VARCHAR(20))
BEGIN
DECLARE v_pinned INT DEFAULT 0;
SELECT COUNT(*), IFNULL(SUM(pinned), 0) INTO p_notes, v_pinned
FROM notes WHERE user_id = p_user_id;
IF p_notes = 0 THEN
SET p_label = 'new';
ELSEIF v_pinned > 0 THEN
SET p_label = 'organised';
ELSE
SET p_label = 'active';
END IF;
END //
DELIMITER ;
CALL user_stats(1, @notes, @label);
SELECT @notes, @label;
+--------+-----------+
| @notes | @label |
+--------+-----------+
| 3 | organised |
+--------+-----------+
1 row in set (0.00 sec)
Name parameters and variables with prefixes like p_ and v_. If a variable has the same name as a column, MySQL uses the variable, a classic source of confusing bugs.
Control flow
| Construct | Syntax |
|---|---|
| Condition | IF ... THEN ... ELSEIF ... ELSE ... END IF; |
| Multi-branch | CASE WHEN ... THEN ...; ELSE ...; END CASE; |
| Loop with exit | label: LOOP ... IF done THEN LEAVE label; END IF; END LOOP; |
| While | WHILE i < 10 DO ... END WHILE; |
| Repeat | REPEAT ... UNTIL i >= 10 END REPEAT; |
| Next iteration | ITERATE label; |
A loop that fills a test database with generated notes:
DELIMITER //
CREATE PROCEDURE fill_test_notes(IN p_count INT)
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < p_count DO
INSERT INTO notes (user_id, title) VALUES (1 + i % 3, CONCAT('Test note ', i));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
Run it on a test copy, not on the data you will query in the next lessons. Row-by-row loops are slow; whenever a single set-based statement can do the job (INSERT ... SELECT from a recursive CTE, for example), prefer it.
Errors, SIGNAL and transactions
A procedure that changes several tables should be all-or-nothing. Orbit’s create_note checks its input, raises a clear error with SIGNAL, and rolls back if anything fails:
DELIMITER //
CREATE PROCEDURE create_note(
IN p_user_id INT UNSIGNED,
IN p_title VARCHAR(200),
IN p_tag VARCHAR(50),
OUT p_note_id INT UNSIGNED
)
BEGIN
DECLARE v_tag_id SMALLINT UNSIGNED;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
IF NOT EXISTS (SELECT 1 FROM users WHERE id = p_user_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unknown user';
END IF;
START TRANSACTION;
INSERT INTO notes (user_id, title) VALUES (p_user_id, p_title);
SET p_note_id = LAST_INSERT_ID();
INSERT INTO tags (name) VALUES (p_tag)
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
SET v_tag_id = LAST_INSERT_ID();
INSERT INTO note_tags (note_id, tag_id) VALUES (p_note_id, v_tag_id);
COMMIT;
END //
DELIMITER ;
Key pieces:
DECLARE EXIT HANDLER FOR SQLEXCEPTIONruns when any error occurs, then leaves the procedure. Here it rolls back and usesRESIGNALto pass the original error on to the caller. Handlers can also target one error code (FOR 1062) orSQLWARNINGandNOT FOUND, andCONTINUEhandlers carry on after handling.SIGNAL SQLSTATE '45000'raises a custom error.45000means “unhandled user-defined exception”.ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)is the classic trick to get the id of a row that already existed:LAST_INSERT_ID(expr)sets the value the nextLAST_INSERT_ID()returns.
CALL create_note(3, 'First note', 'personal', @id);
SELECT title, author, tags FROM note_list WHERE id = @id;
+------------+----------+----------+
| title | author | tags |
+------------+----------+----------+
| First note | Chen Wei | personal |
+------------+----------+----------+
1 row in set (0.00 sec)
CALL create_note(99, 'Nobody', 'work', @id);
ERROR 1644 (45000): Unknown user
Remove Chen’s test note so the data matches the next lessons: DELETE FROM notes WHERE title = 'First note';.
Cursors
A cursor walks a result row by row inside a procedure: DECLARE cur CURSOR FOR SELECT ...;, a CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;, then OPEN cur;, a loop with FETCH cur INTO ...; and CLOSE cur;. Cursors are read-only and forward-only. Use them only when each row truly needs procedural work; set-based SQL is much faster.
Stored functions
A stored function returns one value and can be used in any expression:
DELIMITER //
CREATE FUNCTION initials(p_name VARCHAR(100))
RETURNS VARCHAR(4)
DETERMINISTIC
NO SQL
BEGIN
RETURN UPPER(CONCAT(LEFT(p_name, 1), LEFT(SUBSTRING_INDEX(p_name, ' ', -1), 1)));
END //
DELIMITER ;
SELECT name, initials(name) AS avatar FROM users ORDER BY id;
+------------+--------+
| name | avatar |
+------------+--------+
| Ana Silva | AS |
| Ben Carter | BC |
| Chen Wei | CW |
+------------+--------+
3 rows in set (0.00 sec)
When binary logging is on (the default), MySQL requires a function to declare whether it is DETERMINISTIC (same input, same output) and whether it reads or modifies data (NO SQL, READS SQL DATA, MODIFIES SQL DATA). Otherwise creation fails with error 1418, unless an administrator sets log_bin_trust_function_creators = ON. Declare honestly: a function marked deterministic that is not can break replication.
Note: Functions called in a WHERE clause run once per row and hide the expression from the optimizer, so an index cannot help. Keep them for presentation and small calculations.
Prepared statements and dynamic SQL
PREPARE compiles a statement with ? placeholders that you fill at EXECUTE time:
PREPARE by_user FROM 'SELECT title FROM notes WHERE user_id = ? ORDER BY id';
SET @u = 2;
EXECUTE by_user USING @u;
DEALLOCATE PREPARE by_user;
+------------+
| title |
+------------+
| Book ideas |
| MySQL tips |
+------------+
2 rows in set (0.00 sec)
Inside procedures this is the way to build SQL with a table or column name chosen at run time. Placeholders cannot stand for identifiers, so validate any name against information_schema or a fixed list before concatenating it, or you open the door to SQL injection.
Security: DEFINER and INVOKER
Views and routines run with the privileges of their definer by default (SQL SECURITY DEFINER). A reporting account with EXECUTE on a procedure can therefore change tables it cannot touch directly, exactly through the controlled path the procedure offers. SQL SECURITY INVOKER makes the object run with the caller’s own privileges instead. If the definer account is dropped, its objects stop working, so define shared objects with a dedicated, non-personal account.
Managing routines
SHOW PROCEDURE STATUS WHERE Db = 'orbit';
SHOW FUNCTION STATUS WHERE Db = 'orbit';
SHOW CREATE PROCEDURE create_note\G
SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'orbit';
DROP PROCEDURE IF EXISTS fill_test_notes;
ALTER PROCEDURE only changes characteristics such as COMMENT or SQL SECURITY. To change the body, drop and recreate the routine. Keep routine definitions in version control alongside your migrations.
Note: MySQL Enterprise Edition 9.x can also run stored programs written in JavaScript (LANGUAGE JAVASCRIPT) through its Multilingual Engine component. The Community edition supports SQL routines only.
Quick reference
| Task | SQL |
|---|---|
| Save a query | CREATE OR REPLACE VIEW v AS SELECT ...; |
| Protect a filtered view | ... WITH CHECK OPTION; |
| Define a procedure | DELIMITER // CREATE PROCEDURE p(IN a INT, OUT b INT) BEGIN ... END // DELIMITER ; |
| Run it | CALL p(1, @b); SELECT @b; |
| Raise an error | SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...'; |
| Roll back on error | DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; |
| Define a function | CREATE FUNCTION f(x INT) RETURNS INT DETERMINISTIC NO SQL RETURN x * 2; |
| Dynamic SQL | PREPARE s FROM '...?...'; EXECUTE s USING @v; |
| List routines | SHOW PROCEDURE STATUS WHERE Db = 'orbit'; |
Exercise
Why do you change the DELIMITER before CREATE PROCEDURE in the mysql client?
Show answer
Answer: C. The client splits input at the delimiter. Changing it lets the whole CREATE PROCEDURE, with its internal semicolons, reach the server as one statement.
Exercise
A view is defined as SELECT … FROM notes WHERE pinned WITH CHECK OPTION. What happens with UPDATE view SET pinned = FALSE?
Show answer
Answer: A. WITH CHECK OPTION rejects inserts and updates that produce rows the view’s WHERE clause would not return.
Exercise
Creating a stored function fails with error 1418 on a server with binary logging. What is the proper fix?
Show answer
Answer: D. With binary logging on, MySQL needs to know whether a function is deterministic and how it uses data, to keep replication safe. Declaring them correctly resolves the error.
Key takeaways
- Views name reusable queries; aggregating views use
TEMPTABLE, so check their plans. WITH CHECK OPTIONkeeps writes through a filtered view inside the view.- Procedures need
DELIMITERin the mysql client; useIN/OUTparameters, prefixed variables and handlers. - Raise errors with
SIGNAL SQLSTATE '45000'and roll back in anEXIT HANDLERwithRESIGNAL. - Stored functions must declare
DETERMINISTICand their data access; avoid them inWHEREon large tables.
FAQ
Should business logic live in stored procedures or the application?
Most teams keep it in the application, where it is easier to test, version and scale. Procedures shine for data-heavy steps that would otherwise need many round trips, and for enforcing one controlled write path for several applications.
Can a view have indexes?
No. A view has no storage of its own; queries use the indexes of the base tables. For precomputed results, maintain a summary table yourself, for example with an event (lesson 16).
How do I debug a stored procedure?
Add SELECT statements with intermediate values while developing, or insert them into a log table. Error handlers can capture details with GET DIAGNOSTICS CONDITION 1 @msg = MESSAGE_TEXT;.
Do procedures make queries faster because they are precompiled?
Not meaningfully. MySQL does not cache plans for procedures across connections the way some other databases do. The speed benefit comes from avoiding network round trips, not from compilation.