MySQL Stored Procedure Programming Best Practices
Technical Specification & Development Guidelines
Version: 3.0
Purpose: Establish standard development practices and guidelines for team members writing MySQL stored programs.
Document History
- 11/10/2015 (v3.0): Updated guidelines and examples (Denis)
- 09/21/2015 (v1.0): Initial draft (Denis)
References
1. Lightweight Debugging Interface
When dedicated commercial or open-source debugging tools are unavailable, use a simple logging approach consisting of a debug table and logging routines (see Appendix A).
- Setup: Create a debug table and logging procedures (Appendix A).
- Usage: Call the debug_msg or debug_msg_f routine inside stored programs to log output (Appendices B and C).
2. Unit Testing via Command-Line Client
For every stored program, maintain a corresponding SQL script containing unit tests executable via the MySQL command-line client. Only integrate routines into application code (e.g., Java) after all command-line tests pass successfully.
For example, a test script for get_geocode_by_zip (Appendix B) can be structured as follows:
//*
Test script: get_geocode_by_zip.tst
Procedure signature:
CREATE PROCEDURE myapp_admin.get_geocode_by_zip(
IN p_zip VARCHAR(10),
IN p_range VARCHAR(10),
OUT p_geocode_o VARCHAR(20)
)
*/
SET @p_geocode_o = '0';
-- Test 1: Valid inputs
CALL myapp_admin.get_geocode_by_zip('01066', '0601', @p_geocode_o);
SELECT @p_geocode_o;
-- Test 2: Null range parameter (allowed)
CALL myapp_admin.get_geocode_by_zip('01096', NULL, @p_geocode_o);
SELECT @p_geocode_o;
-- Test 3: Null zip parameter (invalid input validation check)
CALL myapp_admin.get_geocode_by_zip(NULL, NULL, @p_geocode_o);
SELECT @p_geocode_o;
Executing this script produces console debug output confirming behavior:
$$ mysql -u root myapp_admin < get_geocode_by_zip.tst
** DEBUG:
** p_range is 0601
@p_geocode_o
US2501500000
** DEBUG:
** p_range is NULL
@p_geocode_o
US2501500000
** DEBUG:
** p_range is NULL
@p_geocode_o
NULL
3. Exception Handling
In MySQL 5.6 and later, leverage GET DIAGNOSTICS within exception handlers to extract detailed error metadata:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
@sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@text = MESSAGE_TEXT;
SET @full_error = CONCAT('ERROR ', @errno, ' (', @sqlstate, '): ', @text);
SELECT @full_error;
-- CALL debug_msg(@enabled, @full_error);
END;
4. Package Emulation using Dedicated Schemas
Because MySQL does not natively support Oracle PL/SQL packages, emulate package logical grouping by creating a dedicated schema (database) to group related procedures, functions, and shared objects.
5. Naming Conventions & Style Guide
- Casing: All database, table, column, procedure, and function identifiers must be in lowercase.
- Word Separation: Use snake_case (underscores) to improve readability (e.g., get_geocode_by_zip).
- Parameters: Prefix with p_:
- Input parameters: p_<name>
- Output parameters: p_<name>_o
- Input/Output parameters: p_<name>_io
- Local Variables: Prefix with l_ (e.g., l_geocode).
- Cursors: Maintain consistency across the project by using either a cur_ prefix or a _cur suffix (e.g., cur_customer or customer_cur).
- Functions: Prefix function names with f_ (e.g., f_geocode_by_zip).
- Loop Labels: Append _loop or prepend loop_ (e.g., dept_loop).
- Temporary Tables: Use _gtt for global temporary tables shared across multiple routines, and _tmp for tables scoped to a single routine.
- Header Comments: Include a standard header comment block for every routine containing Purpose, Inputs, Outputs, Dependencies, and Modifications.
- Code Formatting: Use standard SQL formatters (e.g., Toad for MySQL) to maintain consistent indentation and layout.
6. Best Practices
- Reset Cursor Handlers: Always reset the NOT FOUND flag variable after completing a cursor loop.
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched = 1;
OPEN cursor1;
cursor_loop: LOOP
FETCH cursor1 INTO l_customer_name, l_contact_surname, l_contact_firstname;
IF l_last_row_fetched = 1 THEN
LEAVE cursor_loop;
END IF;
END LOOP cursor_loop;
CLOSE cursor1;
-- Always reset the loop termination state flag after closing
SET l_last_row_fetched = 0;
- Avoid Shadowing: Do not override or shadow outer variable declarations inside nested blocks.
- Strict Mode: Ensure stored programs are developed and executed in SQL strict mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES) to prevent silent data truncation or invalid inputs.
- Bind Parameters in Dynamic SQL: Use parameter placeholders (?) instead of concatenating variables directly into dynamic SQL strings to prevent SQL injection and improve plan caching.
CREATE PROCEDURE update_anything(
IN p_table VARCHAR(60),
IN p_where_col VARCHAR(60),
IN p_set_col VARCHAR(60),
IN p_where_val VARCHAR(60),
IN p_set_val VARCHAR(60)
)
BEGIN
SET @dyn_sql = CONCAT(
'UPDATE ', p_table,
' SET ', p_set_col, ' = ?',
' WHERE ', p_where_col, ' = ?'
);
PREPARE s1 FROM @dyn_sql;
SET @where_val = p_where_val;
SET @set_val = p_set_val;
EXECUTE s1 USING @set_val, @where_val;
DEALLOCATE PREPARE s1;
END;
- Encapsulate Business Rules: Hide complex logical expressions and calculations behind named deterministic functions (e.g., validation checks or tax calculations).
- Clean Codebase: Regularly audit stored code to remove unused variables, unreachable blocks, and dead code.
- Exhaustive CASE Statements: Ensure CASE structures cover all possible conditional paths, or include an ELSE clause to trap unhandled cases.
- Guaranteed Loop Termination: Verify that all loops reach explicit termination conditions under every execution branch.
- Single Exit Point in Loops: Prefer using a single LEAVE statement per loop construct to maintain structured control flow.
- Concurrence Control: Use SELECT ... FOR UPDATE when fetching rows that will be modified in subsequent steps.
- Modularization: Limit execution body size to approximately 50–60 lines per routine by breaking down larger tasks into smaller subroutines.
Appendix A: Debug Infrastructure Setup
`msg_text` varchar(255) DEFAULT NULL,
--- 1. Create debug log table
CREATE TABLE `debug_tab` (
`seq` BIGINT(20) NOT NULL AUTO_INCREMENT,
`msg_time` DATETIME DEFAULT NULL,
`cid` INT(11) DEFAULT NULL,
`msg_text` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`seq`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
-- 2. Debug procedure for stored procedures
DROP PROCEDURE IF EXISTS debug_msg;
DELIMITER //
CREATE PROCEDURE debug_msg(
IN p_enabled INTEGER,
IN p_msg VARCHAR(255)
)
label1: BEGIN
/*
| Purpose: Display or save debug message
| Inputs : p_enabled - 0: Off, 1: Console, 2: Table, 3: Both
| Note : Result sets are prohibited in stored functions; use debug_msg_f instead.
*/
IF p_enabled = 0 THEN
LEAVE label1;
ELSEIF p_enabled = 1 THEN
SELECT CONCAT('** ', p_msg) AS '** DEBUG:';
ELSEIF p_enabled = 2 THEN
INSERT INTO debug_tab
SELECT NULL, CURRENT_TIMESTAMP, CONNECTION_ID(), p_msg;
ELSEIF p_enabled = 3 THEN
SELECT CONCAT('** ', p_msg) AS '** DEBUG:';
INSERT INTO debug_tab
SELECT NULL, CURRENT_TIMESTAMP, CONNECTION_ID(), p_msg;
END IF;
END label1 //
DELIMITER ;
-- 3. Debug procedure for stored functions
DROP PROCEDURE IF EXISTS debug_msg_f;
DELIMITER //
CREATE PROCEDURE myapp_admin.debug_msg_f(
IN p_enabled INTEGER,
IN p_msg VARCHAR(255)
)
BEGIN
/*
| Purpose: Save debug message to debug_tab (function-compatible)
| Inputs : p_enabled - 0: Off, 2: Table logging
*/
IF p_enabled = 2 THEN
INSERT INTO debug_tab
SELECT NULL, CURRENT_TIMESTAMP, CONNECTION_ID(), p_msg;
END IF;
END //
DELIMITER ;
Appendix B: Sample Stored Procedure Using Debugging
DROP PROCEDURE IF EXISTS myapp_admin.get_geocode_by_zip;
CREATE PROCEDURE myapp_admin.get_geocode_by_zip(
IN p_zip VARCHAR(10),
IN p_range VARCHAR(10),
OUT p_geocode_o VARCHAR(20)
)
BEGIN
/*
| Purpose: Retrieve geocode concatenated from country, state, county, and block
| Inputs : p_zip - ZIP code; p_range - High range offset
| Outputs: p_geocode_o - Formatted geocode string (e.g., US2501500000)
| Table : v_tax_plus4
*/
SET @enabled = 1; -- Debug mode: 0: Off, 1: Console, 2: Table, 3: Both
SET p_geocode_o = NULL;
IF p_range IS NULL THEN
CALL debug_msg(@enabled, 'p_range is NULL');
SELECT CONCAT(
IFNULL(TRIM(country), ''),
IFNULL(TRIM(state), ''),
IFNULL(TRIM(county), ''),
IFNULL(TRIM(block), '')
)
INTO p_geocode_o
FROM v_tax_plus4
WHERE zip = p_zip AND main_range = 1;
ELSE
CALL debug_msg(@enabled, CONCAT('p_range is ', p_range));
SELECT CONCAT(
IFNULL(TRIM(country), ''),
IFNULL(TRIM(state), ''),
IFNULL(TRIM(county), ''),
IFNULL(TRIM(block), '')
)
INTO p_geocode_o
FROM v_tax_plus4
WHERE zip = p_zip
AND p_range BETWEEN low_range AND high_range
AND main_range = 0;
END IF;
END;
Appendix C: Sample Stored Function Using Debugging
DROP FUNCTION IF EXISTS myapp_admin.f_geocode_by_zip;
CREATE FUNCTION myapp_admin.f_geocode_by_zip(
p_zip VARCHAR(10),
p_range VARCHAR(10)
) RETURNS VARCHAR(10) CHARSET latin1
DETERMINISTIC
BEGIN
/*
| Purpose: Return geocode concatenated string from v_tax_plus4
| Inputs : p_zip - ZIP code; p_range - High range offset
| Outputs: Returns geocode string
*/
DECLARE l_geocode VARCHAR(10);
SET @enabled = 2; -- Debug mode: 2: Table log, 0: Off
SET l_geocode = NULL;
CALL debug_msg_f(@enabled, 'Inside function f_geocode_by_zip');
IF p_range IS NULL THEN
SELECT CONCAT(
IFNULL(TRIM(country), ''),
IFNULL(TRIM(state), ''),
IFNULL(TRIM(county), ''),
IFNULL(TRIM(block), '')
)
INTO l_geocode
FROM v_tax_plus4
WHERE zip = p_zip AND main_range = 1;
ELSE
CALL debug_msg_f(@enabled, CONCAT('p_range is ', p_range));
SELECT CONCAT(
IFNULL(TRIM(country), ''),
IFNULL(TRIM(state), ''),
IFNULL(TRIM(county), ''),
IFNULL(TRIM(block), '')
)
INTO l_geocode
FROM v_tax_plus4
WHERE zip = p_zip
AND p_range BETWEEN low_range AND high_range
AND main_range = 0;
END IF;
RETURN l_geocode;
END;
