I previously wrote that I am studying for the MySQL 5.6 exam, and that I’m less confident in my skills as a database developer. When I went through the list of topics in the exam, one thing I knew I would have to study is stored programs.
So first, some definitions. From the manual:Stored programs include these objects:
- Stored routines, that is, stored procedures and functions. A stored procedure is invoked using the CALL statement. A procedure does not have a return value but can modify its parameters for later inspection by the caller. It can also generate result sets to be returned to the client program. A stored function is used much like a built-in function. you invoke it in an expression and it returns a value during expression evaluation.
- Triggers. A trigger is a named database object that is associated with a table and that is activated when a particular event occurs for the table, such as an insert or update.
- Events. An event is a task that the server runs according to schedule.
So a stored program is the broader name applied to code that runs in the database. A procedure is different to a function because it is executed via a call statement and can modify input parameters rather than just returning a value. Check.
The next point I have to look at is creation and executing stored procedures and functions. That’s covered on this manual page:
mysql> delimiter // mysql> CREATE PROCEDURE dorepeat(p1 INT) -> BEGIN -> SET @x = 0; -> REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL dorepeat(1000); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x; +------+ | @x | +------+ | 1001 | +------+ 1 row in set (0.00 sec) mysql> CREATE FUNCTION hello (s CHAR(20)) mysql> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT hello('world'); +----------------+ | hello('world') | +----------------+ | Hello, world! | +----------------+ 1 row in set (0.00 sec)
I also need to know how to implement error handling within a stored procedures. This is actually improved a lot in 5.6 with the addition of
GET DIAGNOSTICS. Using the example from the manual page:
CREATE PROCEDURE do_insert(value INT) BEGIN -- Declare variables to hold diagnostics area information DECLARE code CHAR(5) DEFAULT '00000'; DECLARE msg TEXT; DECLARE rows INT; DECLARE result TEXT; -- Declare exception handler for failed insert DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT; END; -- Perform the insert INSERT INTO t1 (int_col) VALUES(value); -- Check whether the insert was successful IF code = '00000' THEN GET DIAGNOSTICS rows = ROW_COUNT; SET result = CONCAT('insert succeeded, row count = ',rows); ELSE SET result = CONCAT('insert failed, error = ',code,', message = ',msg); END IF; -- Say what happened SELECT result; END;
DECLARE A CONTINUE HANDLER (manual pages here and here) and then optionally use
GET DIAGNOSTICS for more fine-grained error information. We can also return an error using the
SIGNAL functionality (manual page here).