CREATE PROCEDURE / CALL / DROP PROCEDURE¶
MatrixOne v4.2.5 supports SQL and Starlark stored procedures: named database programs invoked with CALL. Procedures, Python UDFs, triggers, and SQL Tasks are separate capabilities.
Syntax¶
CREATE [OR REPLACE] PROCEDURE [database_name.]procedure_name (
[ [IN | OUT | INOUT] parameter_name parameter_type [, ...] ]
) [LANGUAGE 'sql' | LANGUAGE 'starlark'] 'procedure_body';
CALL [database_name.]procedure_name([argument [, ...]]);
DROP PROCEDURE [IF EXISTS] [database_name.]procedure_name;
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE condition];
Omitting LANGUAGE selects SQL. The body must be a string; SQL procedures normally put BEGIN ... END inside that string. Double embedded single quotes, for example ''done''. Submitting MySQL’s unquoted CREATE PROCEDURE p() BEGIN ... END produces a syntax error in v4.2.5.
IN supplies input, OUT returns output, and INOUT does both. Pass session variables such as @result for OUT and INOUT, then read them on the same connection. OR REPLACE replaces an existing procedure; DROP ... IF EXISTS supports cleanup.
SQL procedures¶
SQL procedures support local DECLARE variables, SET, conditions, and loops. This independent example verifies IN, OUT, INOUT, WHILE, and IF:
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. See the stored procedure tutorial for writes and a failure rollback experiment.
Starlark procedures¶
Starlark is a separate language, distinct from Python UDFs. Parameters become input variables; write outputs to out_<parameter_name>. Preserve line breaks and indentation, and send the complete CREATE PROCEDURE as one driver request.
CREATE DATABASE mo_starlark_lab;
USE mo_starlark_lab;
CREATE OR REPLACE PROCEDURE twice(IN i INT, OUT o INT)
LANGUAGE 'starlark' '
out_o = i + i
';
CALL twice(7, @o);
-- Expected-Value: 14
SELECT @o AS result;
DROP PROCEDURE twice;
DROP DATABASE mo_starlark_lab;
The result is 14. mo.sql() executes SQL and returns a result and an error. On failure, the error is a string; mo.errno() and mo.sqlstate() expose the error number and SQLSTATE. Handle returned errors explicitly: receiving an error does not by itself stop the entire program. See Starlark SQL error reporting.
Migration and operational boundaries¶
In the v4.2.5 probe,
SHOW PROCEDURE STATUSlisted procedures;SHOW CREATE PROCEDUREproduced a syntax error. Keep creation scripts in version control instead of relying on that export command.In the same probe,
information_schema.ROUTINESdid not list the created procedures. An empty result is not evidence that there are no procedures.Consume all
CALLresult sets through the driver API before reusing the connection.DELIMITERis a MySQL client command; do not send it to the server or a driver.These examples do not promise full compatibility for MySQL cursors, exception handlers, recursion, privileges, or every procedure option. Validate business scripts individually.
The tutorial verifies one SQL procedure’s DML rollback. Do not extend that result to all DDL, explicit transactions, Starlark error handling, or multi-statement Task bodies.
Examples and boundaries were verified on 2026-10-14 against a single-CN 8.0.30-MatrixOne-v4.2.5 instance.