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 STATUS listed procedures; SHOW CREATE PROCEDURE produced a syntax error. Keep creation scripts in version control instead of relying on that export command.

  • In the same probe, information_schema.ROUTINES did not list the created procedures. An empty result is not evidence that there are no procedures.

  • Consume all CALL result sets through the driver API before reusing the connection. DELIMITER is 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.