Stored procedures: parameters, writes, and failure rollback¶
Complete standalone deployment and use a test account allowed to create databases and procedures. Run the examples in isolated test databases. In v4.2.5 the body is a string: internal semicolons remain part of one CREATE PROCEDURE request.
Parameters and control flow¶
CREATE DATABASE mo_procedure_lab;
USE mo_procedure_lab;
CREATE PROCEDURE sum_to(IN n INT, OUT total INT, INOUT acc INT)
'begin DECLARE i INT DEFAULT 1; SET total = 0; WHILE i <= n DO SET total = total + i; SET i = i + 1; END WHILE; IF total > 0 THEN SET acc = acc + total; END IF; end';
SET @acc = 10;
CALL sum_to(3, @total, @acc);
-- Expected-Values: 6, 16
SELECT @total AS total, @acc AS acc;
DROP PROCEDURE sum_to;
DROP DATABASE mo_procedure_lab;
Expect total=6 and acc=16. The input of @acc is 10; the loop sums 1, 2, and 3. The output returns 6 and the input/output parameter returns 16. Invoke and read session variables on the same connection.
Write two tables in one operation¶
CREATE DATABASE mo_proc_write_lab;
USE mo_proc_write_lab;
CREATE TABLE target_rows(id INT PRIMARY KEY);
CREATE TABLE ingest_control(id INT PRIMARY KEY, watermark INT);
INSERT INTO ingest_control VALUES(1, 0);
CREATE PROCEDURE ingest_once(IN new_id INT)
'begin INSERT INTO target_rows VALUES(new_id); UPDATE ingest_control SET watermark=new_id WHERE id=1; end';
CALL ingest_once(1);
-- Expected-Value: 1
SELECT COUNT(*) FROM target_rows;
-- Expected-Value: 1
SELECT watermark FROM ingest_control WHERE id=1;
Fail deliberately and inspect earlier writes¶
The third statement below references a missing table. The CALL must fail. Keep that expected error and continue with both checks; do not stop the experiment before inspecting the data.
CREATE PROCEDURE ingest_fail()
'begin INSERT INTO target_rows VALUES(2); UPDATE ingest_control SET watermark=2 WHERE id=1; INSERT INTO missing_proc_lab_table VALUES(1); end';
-- Expected-Success: false
CALL ingest_fail();
-- Expected-Value: 1
SELECT COUNT(*) FROM target_rows;
-- Expected-Value: 1
SELECT watermark FROM ingest_control WHERE id=1;
DROP PROCEDURE ingest_fail;
DROP PROCEDURE ingest_once;
DROP DATABASE mo_proc_write_lab;
With v4.2.5 and autocommit=1, the failed SQL CALL rolls back its inserted row and watermark update; the earlier successful call remains committed. This experiment covers DML and an unhandled SQL error, not DDL, explicit transactions, or handled Starlark errors.
Starlark and application calls¶
See the procedure reference for Starlark arithmetic and output, replacement, deletion, and compatibility boundaries. Send each complete creation statement as one driver request. Consume every CALL result set before reading outputs. When needed, change delimiters in the MySQL client; never send DELIMITER through JDBC or PyMySQL.
Verified on 2026-10-14 with a single-CN v4.2.5 instance in autocommit mode.