Stored Procedures in MySQL

Peter Gulutzan

Did you notice that we added a new chapter in the online MySQL Reference Manual last month: "Chapter 18 Stored Procedures and Functions" http://www.mysql.com/doc/en/Stored_Procedures.html (http://www.mysql.com/doc/en/Stored_Procedures.html))? The new chapter has all the syntax descriptions and examples that you need to make stored procedures go once you have version 5.0. Rather than repeating what the manual says, I'll describe why we did it the way that we did it.

Look at this procedure definition and invocation:

DELIMITER // [1]

CREATE PROCEDURE payment [2]
(payment_amount DECIMAL(6,2),
payment_seller_id INT)
BEGIN
DECLARE n DECIMAL(6,2);
SET n = payment_amount - 1.00;
INSERT INTO Moneys VALUES (n, CURRENT_DATE);
IF payment_amount > 1.00 THEN
UPDATE Sellers
SET commission = commission + 1.00
WHERE seller_id = payment_seller_id;
END IF;
END;
//

DELIMITER ;

CALL payment (5.00, 13); [3]

[1] We had to invent the DELIMITER statement so that we could use semicolons inside the CREATE PROCEDURE. The MySQL client ordinarily thinks that ";" means end of statement. By saying "we'll use something else for end-of-statement marker" we make multi-statement procedures possible. Incidentally, the MySQL Reference Manual suggests | as a delimiter, but I prefer // because I sometimes need to use || as an operator within an SQL expression.

[2] We had no choice about the syntax of the CREATE PROCEDURE statement because the ANSI/ISO standards committee defines how we can use parameters, declare variables, assign values, or include regular SQL statements. We determined from the start that we'd follow "SQL:2003" period. Comparing with other DBMSs:
-- With IBM's DB2 (tm), the CREATE PROCEDURE would be exactly the same because DB2 also follows the standard, as do a few of the smaller vendors
-- With Oracle (tm), there would be a keyword AS and no DECLARE, and the assignment would start with "commission := " rather than "SET commission = "
-- With Microsoft's SQL Server (tm), the parameters would not be in parentheses, names would start with at-signs (e.g. @payment_amount), there would be a keyword 'AS' before the DECLARE, the IF statement wouldn't contain THEN or END IF, and the BEGIN / END would be absent.

[3] The benefit of the stored procedure is apparent when you consider how simple it is to say "CALL ... (parameter-list)". This involves only one message from the client to the server (think how much faster it might go than if you had to do the INSERT and the UPDATE separately). Plus, whenever you change the business rules for what to do with a payment, you just have to replace the procedure. Until now you had to change scripts or programs in host languages, and it's so easy to miss something when not everything is inside the database.

MySQL 5 is a young alpha, and I have to say there are "issues".

One kind of "issue" is the lack of nice features. You cannot refer to a table inside a function, so "CREATE FUNCTION f () RETURN (SELECT MAX(column1) FROM Table1);" is still a no-no. The error messages are vague and don't explain the context. You can't get firm security until we have GRANT EXECUTE, and have nailed down "invokers rights" and "definers rights" (we'll support both kinds). My personal peeve is that the PATH statement -- which is something like PATH on your Windows or Linux command line -- isn't there yet. Doubtless other people will have different yearnings.

Another kind of "issue" is, of course, bugs. This month I've seen nine statements that crash the server and several that don't work as advertised. However, the reason I know that is: there's a bug tracking system. So when the inevitable moment comes that your stored-procedure attempt fouls up, go to http://bugs.mysql.com, search the bugs database with a keyword like "stored" to see if anyone has already reported the problem, and if not ... click "Report a bug". MySQL 5.0.0 is a preview so be gentle and ignore small things.

But please do report significant problems, because we cannot fix bugs that we do not know about.

We should now clap hands for the team that got stored procedures going under the direction of Mr Per-Erik Martin. We're changing the version number from "4.x" to "5.x" which emphasizes that the change is big. Unfortunately that makes it difficult to say exactly when Version 5 will change from "alpha" to "beta" and then to "gamma" and then to "production". Sorry folks, the only thing we know right now is that we must fix every reported problem before we release.