Summary
This input is a brief blog on stored procedures in MySQL. At the same time, it's a description on our knowledge with porting stored procedures written in SQL PL (DB2) to MySQL. To be effective, such a port needs some sort of standardized language across stages. From now the initial section on the SQL/PSM standard.
SQL and procedural program logic
The SQL language is intended primarily to query and update data in a relational database. It is a 4th generation language which incomes that it is declarative not imperative like 3rd generation languages such as Java. Data management declarations SELECT, DELETE, INSERT, UPDATE don't need variables nor explicit repetitions or other control flow concepts.
Occasionally, however, it makes intelligence to combine pure data manipulation language (DML) with procedural logic and variables, e.g. to do some non-database action on each row of a result table, or to store transitional results for later use, or to apply a certain timetable or interdependency of several DML declarations for example insert into two tables, then commit.
In a relational database, stored procedures are the usual ampule for procedural program logic. They allow a mix of DML declarations with sequential logic and flow concepts (similar IF ... ELSE or WHILE ...) and they can be called over the database server from any SQL client subsequently "CALL procedure_name" is a normal SQL statement. Question remains: which 3rd generation programming language to use to device stored procedures?
Regrettably, till about 10 years ago there was no standard to be followed, so all RDBMS stages had their own registered solution. Oracle was one of the first systems to join in SQL and procedural logic over PL/SQL which is syntactically grounded on Ada and Pascal. Microsoft's SQL Server lent the syntax of Sybase's Transact-SQL. IBM's DB2 was comparatively late with integrating procedural logic into SQL only in the late 1990s and came up with their SQL PL. PostgreSQL first technologically advanced PL/pgSQL a language like Oracle's PL/SQL but later swapped to PL/PSM. Preceding in the row, MySQL delayed until version 5 released in 2005 to provision stored procedures.
By that time, the SQL:2003 ISO & ANSI standard was prepared. It further likened to the earlier SQL:1999 standard a chapter on procedural program logic, named SQL/PSM, "Persistent Stored Modules". Fortunately the MySQL 5.0 development team intelligently obvious to follow the ISO/ANSI standard with respect to their programming language for stored procedures and, by the means, also for triggers, user defined functions, and events. Actually, both the DB2 (SQL PL) and PostgreSQL selections are also fully well-matched with the SQL:2003 standard SQL/PSM. Inappropriately, PL/SQL (Oracle) and t-SQL (SQL Server) aren't.
Stored procedures, triggers and user definite functions printed for DB2 should thus work on MySQL without any change, and vice versa. At tiniest, if those procedures stick to the SQL/PSM typical syntax and don't use any DB2 or MySQL nonstandard allowances. But... is this actually the case? Can you just drift any DB2 stored procedure to MySQL? We thru the workout at GOPLARDB, so read on to study what we initiate out!
SQL/PSM in action in MySQL
Seemingly, a lot of MySQL users and particularly database designers seem to have wasted some of the recent progresses and remained in the version 4 era: new structures of version 5 while offered since more than 7 years seem to keep on unused -- and unidentified! The MySQL procedural objects and the SQL/PSM syntax appear to be among the best kept surprises of the last period! All being well this article will get you "triggered" to start discovering this great world ...
SQL/PSM can only be used limited a procedural object: a stored procedure, a trigger, a function, or an event. I'll only guise at stored procedures here. They are valuable in an RDBMS context since they let database designers and administrators to summarize certain "business logic" into a database object which sits exclusive the server and can be named from any SQL client program. This evades code repetition on the clients, avoids needless network data traffic among server and client, lets for improved data security, and make simpler data and application design with definite data consistency.
What's a procedure? Fine, it should have a name, a possibly blank list of parameters, and a "body" which covers one or more SQL declarations. These can be a mix of "outdated" DML and extra SQL/PSM syntax for local variables, error handling, loop control, and IF conditions. The subsequent SQL creates a procedure with some of these fixings:
The names used in this instance (proc1, parm1, parm2, var1, table1) would of sequence be not well selected for a "actual life" procedure; I'll use such "general" names to strain the fact that my instances are not meant to do valuable things but only try to exemplify the SQL/PSM syntax in MySQL. Also sign the remarks (/* ... */): use this to paper your procedures!
Once a procedure called "proc1" is formed, it can be called from any client by delivering an SQL declaration of the form
or with any other numeric constant or expression among the parentheses as an alternative of 32. Meanwhile the second parameter in this case is a "return" field (keyword "OUT"), the way to require it in the CALL statement rest on on the client setting in use. The "@var" syntax would be the way to require it when using the "mysql" command-line client interface. When using ODBC or JDBC, or from inside PHP, a question dent would be wanted as parameter indicator:
When the CALL get ahead, the statements inside the process body should have been performed affording to the program flow in the case, the two side things should be that table1 now has an additional row containing 'GOPLARDB', and that the client obtains the text 'Procedure proc1 ran successfully' into the parameter indicator for the second parameter.
Variables inside a procedure are unseen to the caller: their scope is restricted to the procedure body. Scope is restricted by any BEGIN ... END block encircling the variable statement. The next procedure has two such nested blocks:
Two of the three variables in this sample procedure are both named x1 which is seamlessly legal: the choice of the "inner" x1 is restricted to the inner BEGIN ... END block, while that of the outer x1 and x2 are restricted to the outer BEGIN ... END, i.e., the full procedure. So x2 is still "visible" inside the inner block, while the outer x1 is of course made unseen by the declaration of an identically named variable exclusive that inner block.
Unnecessary to say, that, when called, this procedure proceeds the text contents 'inner' and 'outer' as the two parameter symbol values, in that order.
Loops and cursors
The most frequently wanted DML statement is SELECT; but since it usually returns a result table, while variables and parameters can only be "scalar", a cursor will be wanted to iterate through the pay back table rows one by one. What if we want to obtain the comma-separated, concatenated values of the first 100 rows of a two-column table t1 (column lengths 32):
This instance exemplifies the WHILE syntax, and at the same time the distinctive use of a cursor: DECLARE, OPEN, FETCH (recurrently), and CLOSE. Note that cursor statements must be placed after all variable statements but before any executable declarations. The only unusual feature is the detail that the loop in this sample doesn't traverse the cursor until the end of the result set but stops after 100 iterations. Discontinuing at end-of-result needs error handling: since trying to FETCH after an end-of-table is an "exception" reimbursed by the database engine, the procedural logic should openly catch that exception and finish the WHILE loop at that fact. Here is a distinctive way to do this with SQL/PSM:
"Endure handlers" are "triggered" once an exception is thrown from inside the database engine. Each likely type of exception has a related SQLSTATE which is standardized across RDBMS platforms; SQL state "02000" stances for "end-of-data". The procedural logic will remain where it left off when an exception is catches with a handler, but only after the handler body declaration is accomplished. Such a statement can of sequence be a BEGIN...END block, if wanted.
By default, if no continue handler would have been acknowledged, the thrown exception would have been approved on to the caller of the procedure, but with an implied rollback of whatever occurred inside the procedure. This would consent p1 at NULL.
MySQL's SQL/PSM against DB2's SQL PL
To bargain out how "standardized" the MySQL application of the SQL/PSM requirement really is, we tried to port all our DB2 stored procedures to MySQL. First, we competed into DB2 non-standard postponements of SQL PL that were used in our present procedures. There was only one such extension that was used thoroughly in all our DB2 procedures: the SQLCODE variable a DB2 modified of SQLSTATE into which the database engine writes a specific value for each exception, but deprived of causing the procedure to be intermittent, even if no handler is present.
So, we first rewrote our DB2 procedures --in DB2--, creation sure not to use SQLCODE any longer. In its place we had to introduce the equivalent continue handler(s), thereby presenting an additional "flag" variable like the end_indicator variable in the sample. This functioned out fine: the new procedures ran flawlessly in DB2.
New effort to blindly run the DB2 "create procedure" declarations on MySQL. Now we detected some syntactic variances, fortunately not in the body of the procedures but in the elective clauses stated just before the body: DB2 lets choices like "LANGUAGE SQL", "QUALIFIER", "DISABLE DEBUG MODE", "WITH EXPLAIN", "ISOLATION LEVEL", "VALIDATE BIND", and "DATE FORMAT EUR". For use with MySQL, "VALIDATE BIND" had to be swapped by "SECURITY DEFINER"; the substitute "VALIDATE RUN" would have to be swapped by "SECURITY INVOKER". For the other choices, MySQL uses "environmental settings" which it receives from the situation performing the CREATE statement. For instance, the "ISOLATION LEVEL" setting is initiate in the tx_isolation session variable of the MySQL client. And the DATE FORMAT setting links to MySQL's date_format session variable.
As with DB2, all related settings are stored organized with the procedure, in the database collection, hence will be re-activated when somebody calls the procedure, thereby for the time being overriding that user's matching session variables.
The only option section which we could keep was the "LANGUAGE SQL": required in DB2, optional with MySQL.
After these small alterations, the CREATE PROCEDURE declarations from DB2 operated on MySQL! But did they run correctly? To verify this, we had to create matching tables on both systems, have the same test data in together, and migrate then run the unit test programs from DB2 on MySQL. And certainly: it turned out that MySQL functioned precisely as anticipated!
Conclusion
For a "port" of surroundings applications, tables, data, ... to be fruitful, standardization is crucial. SQL and particularly its DML statements have been standardized for relatively some time now, but the SQL/PSM standardization is comparatively new.
Three relational database systems have comprised this new standard as their preferred language for applying triggers and stored procedures: DB2, PostgreSQL, and MySQL.
Our knowledge with porting stored procedures from DB2 to MySQL displays positive: it suits to get rid of known non-standard concepts like the use of SQLCODE with DB2, and to recognize the way to organize the situation, in order to effectively port a procedure from one stage to the other.