Stored Procedures and Functions

Introduction

Stored procedures and functions save SQL and logic inside the database—useful for batch jobs, consistent reporting, or DBA tooling. Application code often stays in app layers (JDBC, Spring); this chapter teaches syntax so you can read and maintain them when your team uses them.

Prerequisites

Transactions Basics
Group By and Aggregates
mysql client (procedures use DELIMITER)

DELIMITER Change

Semicolon ends statement inside procedure body—temporarily change delimiter:

sql

DELIMITER //
 
CREATE PROCEDURE sp_post_count_by_user(IN p_user_id INT UNSIGNED)
BEGIN
  SELECT COUNT(*) AS post_count
  FROM posts
  WHERE user_id = p_user_id;
END //
 
DELIMITER ;

Code explanation:

DELIMITER // tells client // ends command until reset to ;
GUI tools may not need delimiter change—use tool's procedure editor

Call Procedure

sql

CALL sp_post_count_by_user(1);

IN, OUT, INOUT Parameters

sql

Call with user variable for OUT:

sql

CALL sp_transfer(1, 2, 25.00, @status);
SELECT @status;

Param	Direction
IN	Input only
OUT	Returned to caller
INOUT	Read and write

Stored Functions

Must return single value; use in expressions:

sql

Use:

sql

SELECT display_name, fn_post_count(id) AS posts
FROM users;

Code explanation:

DETERMINISTIC / READS SQL DATA—required characteristics for replication safety in many setups

Flow Control

sql

Loops:

sql

WHILE i < 10 DO
  SET i = i + 1;
END WHILE;
 
REPEAT
  SET i = i + 1;
UNTIL i >= 10 END REPEAT;

List and Drop

sql

SHOW PROCEDURE STATUS WHERE Db = 'blog_dev';
 
SHOW FUNCTION STATUS WHERE Db = 'blog_dev';
 
SHOW CREATE PROCEDURE sp_post_count_by_user\G
 
DROP PROCEDURE IF EXISTS sp_post_count_by_user;
DROP FUNCTION IF EXISTS fn_post_count;

When to Use Procedures

Good fit	Poor fit
Scheduled DB maintenance	Core business rules only in DB
Batch ETL inside server	Unit testing difficulty
Stable reporting SQL	Frequent logic changes in app deploy

Tip

Prefer App Layer for Business Logic

hello_code app tracks (Spring, Node) keep logic in code with tests—procedures for DBA/ops tasks.

FAQ

DELIMITER in Workbench?

Use procedure tab or execute whole script block.

Procedure permissions?

Need CREATE ROUTINE, EXECUTE—grant carefully.

Return result sets?

Procedures can SELECT multiple result sets—JDBC handles with getMoreResults().

Function vs procedure?

Function returns scalar in SQL expressions; procedure called with CALL.

SQL injection in dynamic SQL?

PREPARE / EXECUTE with bound params—never concat user input raw.

Version in repo?

Store .sql migration files in Git—Git track.