MySQL Administrator's Bible- P7

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

161
lượt xem 39
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL Administrator's Bible- P7: Với tập trung đặc biệt vào việc phát hành lớn tiếp theo của MySQL, nguồn tài nguyên này cung cấp một khuôn khổ vững chắc cho bất cứ ai mới vào MySQL hoặc chuyển từ một nền tảng cơ sở dữ liệu, cũng như các quản trị MySQL kinh nghiệm. Các bộ đôi tác giả cao hồ sơ cung cấp bảo hiểm thiết yếu của các nguyên tắc cơ bản của phạm vi bảo hiểm cơ sở dữ liệu quản lý, bao gồm cả cách tiếp cận độc đáo MySQL của các tính năng cơ sở...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: MySQL Administrator's Bible- P7

Stored Routines, Triggers, and Events 7 continued Whether or not a stored routine is deterministic is important due to binary logging. A deterministic stored routine will replicate without a problem; however, a stored routine that is not deterministic may have problems replicating. If binary logging is set as statement-based (see Chapter 16), the binary log contains the statements that change data, so they can be replayed during an incremental restore or during replication. A stored routine that is not deterministic may have a different output given the same input, which means that an incremental restore or slave will not have data that matches the original data. Be very careful when using a stored routine that is not deterministic, because statement-based binary logging may not be adequate to store the data changes. If data integrity is a concern, use row-based binary logging or a deterministic stored routine instead. Unfortunately, at the time of this writing, the DETERMINISTIC and NOT DETERMINISTIC options serve only as comments, and are not veriﬁed by mysqld. There are no warnings or errors if a non-deterministic routine is set as DETERMINISTIC. MySQL will throw an error and refuse to create a stored function if binary logging of that function may be unsafe: ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_ creators variable) The best way to ﬁx this problem is to declare the function to be DETERMINISTIC, NO SQL, or READS DATA. The next best way is to have a user with the SUPER privilege deﬁne the function. Turning binary logging off is another way to ﬁx this issue, though it may not be possible. Setting log_bin_trust_function_creators to 1 will also ﬁx this issue, though it is less safe because you may end up attempting to replicate a function that is not safe for replication. SQL usage The remaining option is what SQL statements the stored routine uses. These values are for informational purposes only, and do not affect the way mysqld handles the stored routine. The possible values are: ■ MODIFIES SQL DATA — The stored routine may update data (with a DELETE, INSERT, or UPDATE command, for instance). ■ READS SQL DATA — The stored routine does not contain SQL to write data (as in MODI- FIES SQL DATA) but does contain SQL that reads data (that is, SELECT statements). The store_offerings stored procedure is an example of a stored routine that qualiﬁes as READS SQL DATA. ■ CONTAINS SQL — The stored routine does not read or write data in the database. The curr_time and increment_counter stored procedures are examples of stored routines that qualify as CONTAINS SQL. ■ NO SQL — The stored routine has no SQL statements in it. 267
Part II Developing with MySQL The default is CONTAINS SQL. Any changes to the default are done manually by the user; MySQL does not check whether or not the option is correct. For example, the store_offerings stored procedure is created with the default of CONTAINS SQL even though READS SQL DATA is the correct option. Because this is for informational purposes only, there is no harm in having this option be incorrect. However, if you intend to use the information to guide decisions, make sure you set the appropriate option. Full CREATE PROCEDURE syntax The full CREATE PROCEDURE syntax is: CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE p_name ([parameter[, . . . ]]) [SQL SECURITY {DEFINER | INVOKER}] [option . . . ] {statement} option is one or more of: SQL SECURITY {DEFINER | INVOKER} COMMENT ’comment string’ LANGUAGE SQL [NOT] DETERMINISTIC {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} parameter is: [IN|OUT|INOUT] name data_type Note that the CREATE ROUTINE privilege is needed in order to create a stored procedure. See Chapter 14 for more information about managing privileges. Creating a basic stored function A stored function outputs only one scalar value, so there is no such thing as an OUT parame- ter in a stored function. Therefore, the requirements for arguments to a stored function are that each input variable is named and the type deﬁned. You must also specify the type of the return value, and most of the time, create a local variable to store the value to be returned. The differ- ences between CREATE PROCEDURE and CREATE FUNCTION are: ■ All arguments to a function are input parameters; arguments to a procedure may be input parameters, output variables, or INOUT variables. ■ A function must use the RETURNS keyword after the input parameters to specify the type of the scalar value to be returned. ■ A function must use the RETURN keyword in its body to specify the value to be returned. 268
Stored Routines, Triggers, and Events 7 The following deﬁnes a basic stored function to get the store_id of a staff member from the staff table given a staff_id: mysql> DELIMITER | mysql> CREATE FUNCTION get_store_id (f_staff_id TINYINT UNSIGNED) RETURNS TINYINT UNSIGNED -> READS SQL DATA -> BEGIN -> DECLARE f_store_id TINYINT UNSIGNED; -> SELECT store_id INTO f_store_id FROM staff WHERE staff_id=f_staff_id; -> RETURN f_store_id; -> END -> | Query OK, 0 rows affected (0.03 sec) mysql> DELIMITER ; Full CREATE FUNCTION syntax The full syntax for CREATE FUNCTION is: CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION f_name ([parameter[, . . . ]]) RETURNS type [option . . . ] {statement} option is one or more of: SQL SECURITY {DEFINER | INVOKER} COMMENT ’comment string’ LANGUAGE SQL [NOT] DETERMINISTIC {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} parameter is: name data_type Note that both the CREATE ROUTINE and SUPER privileges are needed in order to create a stored function. See Chapter 14 for more information about managing privileges. Invoking a stored function A stored function is invoked just as a standard MySQL function is invoked — by using the function name and passing input parameters: mysql> SELECT get_store_id(1); 269
Part II Developing with MySQL +-----------------+ | get_store_id(1) | +-----------------+ | 1 | +-----------------+ 1 row in set (0.20 sec) Note that you need the EXECUTE privilege in order to invoke the function; the creator of the stored function is given this privilege automatically. Changing a stored routine MySQL supports the ALTER PROCEDURE and ALTER FUNCTION statements, by which you can change the SQL usage, SQL SECURITY option, and COMMENT of a stored routine. To change more than one of these in one ALTER command, separate the options by a space: mysql> ALTER PROCEDURE increment_counter COMMENT "increments the INOUT variable by 1" CONTAINS SQL; Query OK, 0 rows affected (0.00 sec) Note that you need the ALTER ROUTINE privilege in order to change the stored routine; the cre- ator of the stored routine is given this privilege automatically. The automatic_sp_privileges system variable can be set to 0 to change the default behavior of a stored routine’s creator getting automatic ALTER ROUTINE privilege. To change any other part of a stored routine (the DEFINER or the code body, for example), you must drop and re-create the stored routine. To see details of a stored procedure, use the SHOW CREATE PROCEDURE command: mysql> SHOW CREATE PROCEDURE increment_counter\G *************************** 1. row *************************** Procedure: increment_counter sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCE- DURE `increment_counter`(INOUT p_count INT UNSIGNED) COMMENT ’increments the INOUT variable by 1’ SET p_count:=p_count+1 character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.03 sec) To see details of a stored function, use the SHOW CREATE FUNCTION command: mysql> SHOW CREATE FUNCTION get_store_id\G 270
Stored Routines, Triggers, and Events 7 *************************** 1. row *************************** Function: get_store_id sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `get_store_id`(f_staff_id TINYINT UNSIGNED) RETURNS tinyint(3) unsigned READS SQL DATA BEGIN DECLARE f_store_id TINYINT UNSIGNED; SELECT store_id INTO f_store_id FROM staff WHERE staff_id=f_staff_id; RETURN f_store_id; END character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) There is no shortcut to ﬁnd all the stored routines associated with a database. To use SQL to ﬁnd what stored routines are in the system, query the ROUTINES table in the INFORMATION_SCHEMA database: mysql> SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA=’sakila’; +----------------------------+--------------+ | ROUTINE_NAME | ROUTINE_TYPE | +----------------------------+--------------+ | film_in_stock | PROCEDURE | | film_not_in_stock | PROCEDURE | | get_customer_balance | FUNCTION | | inventory_held_by_customer | FUNCTION | | inventory_in_stock | FUNCTION | | rewards_report | PROCEDURE | +----------------------------+--------------+ 6 rows in set (0.09 sec) For more about the INFORMATION_SCHEMA database, see Chapter 21. Naming: stored routines Stored routines can be named using reserved words. This is an extremely bad idea to do on purpose. Because it is possible you may accidentally give your stored routine a name that is a reserved word, we will explain how to name a stored routine using a reserved word. To name a stored routine with a reserved word, there must be whitespace between the routine name (reserved word) and the opening parenthesis used for the parameters: mysql> DELIMITER | mysql> CREATE PROCEDURE count(INOUT p_count INT UNSIGNED) 271
Part II Developing with MySQL -> COMMENT ’this fails because count is a reserved word and no space exists between count and (’ -> BEGIN -> SET p_count:=p_count+1; -> END -> | ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’count (INOUT p_count INT UNSIGNED) COMMENT ’this will fail because count is a res’ at line 1 mysql> CREATE PROCEDURE count (INOUT p_count INT UNSIGNED) -> COMMENT ’this succeeds because of the space between count and (, but is a bad idea’ -> BEGIN -> SET p_count:=p_count+1; -> END -> | Query OK, 0 rows affected (0.25 sec) mysql> DELIMITER ; The same holds true for using a stored routine named with a reserved word: there must be a space between the routine name and the opening parenthesis used for the parameters: mysql> set @count:=123; Query OK, 0 rows affected (0.00 sec) mysql> CALL count(@count); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’count (@count)’ at line 1 mysql> CALL count (@count); Query OK, 0 rows affected (0.11 sec) mysql> select @count; +--------+ | @count | +--------+ | 124 | +--------+ 1 row in set (0.00 sec) mysql> -- dropping the procedure right away for sanity’s sake mysql> DROP PROCEDURE IF EXISTS count; Query OK, 0 rows affected (0.03 sec) Note that the sql_mode named IGNORE_SPACE has no bearing on this rule — IGNORE_SPACE does not apply to stored routines. 272
Stored Routines, Triggers, and Events 7 If you get an SQL syntax error when attempting to execute a stored routine, check the name of the stored routine — you may have accidentally given your stored routine a name that is a reserved word! Stored procedure result sets A stored procedure can return information by way of OUT and INOUT variables; however, a stored procedure can also return information with SQL statements inside the stored procedure. For example, a SELECT statement inside a stored procedure will execute as it would on a command line, and when invoking the stored procedure you would see that result set. Ziesel created the store_offerings stored procedure because she wanted to get the count of movies offered by a given store_id, whether or not that movie was in stock. To return the count, she took advantage of an OUT variable: DELIMITER | CREATE PROCEDURE store_offerings ( IN p_store_id INT, OUT p_count INT ) SELECT COUNT(*) INTO p_count FROM inventory WHERE store_id = p_store_id; | DELIMITER ; However, Ziesel could have also returned the count by issuing a simple SELECT statement, send- ing the result set back — the same way a result set is sent back when a user directly issues a SELECT statement: mysql> DROP PROCEDURE IF EXISTS store_offerings; Query OK, 0 rows affected (0.02 sec) mysql> DELIMITER | mysql> CREATE PROCEDURE store_offerings ( IN p_store_id INT ) -> SELECT COUNT(*) -> FROM inventory -> WHERE store_id = p_store_id; -> | Query OK, 0 rows affected (0.05 sec) mysql> DELIMITER ; mysql> CALL store_offerings (1); +----------+ | COUNT(*) | +----------+ | 2270 | +----------+ 1 row in set (0.25 sec) Query OK, 0 rows affected (0.25 sec) 273
Part II Developing with MySQL Ziesel modiﬁed the stored procedure, changing the SELECT COUNT(*) INTO query to a SELECT COUNT(*) query, eliminating the need for an OUT variable to store the result in. The result is sent back to the client program that invoked the stored procedure (in this case, the mysql command-line client). Sending Back Result Sets tored procedures can send information back in two ways: by updating a variable and by running S queries that send back result sets. There is no need to choose only one method — a single stored procedure can update variables and run queries that send back results. There may be times when you want to minimize using user-deﬁned variables; in these cases you may prefer to send back a result set. In other cases, perhaps when you do not want the overhead of processing a returned result set, user-deﬁned variables are preferable. The programs that call the stored procedure need to be able to handle all of the returned information, whether or not it is returned via a variable. OUT variables must be speciﬁed when the procedure is invoked, otherwise the command will fail. However, there is no error or warning from MySQL if information is returned via a result set and your application does not process that result set, or processes it incorrectly (perhaps by assuming the result set has exactly one row). In the store_offerings example, only one SELECT query is part of the body. However, a stored procedure can issue many queries, including issuing many queries that return result sets to the client. Make sure your application can handle all possible output, including multiple result sets, errors, warnings, and unexpected result sets (empty result sets, or result sets with more rows than expected). Stored routine errors and warnings If you did not want to allow pct_increase to accept negative values for p_incr, you would have deﬁned the input parameter as IN p_incr INT UNSIGNED: mysql> DROP PROCEDURE IF EXISTS pct_increase; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER | mysql> CREATE PROCEDURE pct_increase (INOUT p_int INT, IN p_incr INT UNSIGNED, OUT p_pct_incr DECIMAL (5,2)) -> BEGIN -> DECLARE p_int_new INT; -> SET p_int_new:=p_int+p_incr; -> SET p_pct_incr:=(p_int_new-p_int)/p_int*100; -> SET p_int:=p_int_new; -> END -> | Query OK, 0 rows affected (0.00 sec) 274
Stored Routines, Triggers, and Events 7 mysql> DELIMITER ; mysql> SET @num:=100; Query OK, 0 rows affected (0.00 sec) mysql> CALL pct_increase (@num, -10, @pct); ERROR 1264 (22003): Out of range value for column ’p_incr’ at row 1 mysqld handles stored routine errors and warnings as if the client ran the commands interac- tively. A user invoking pct_increase with a negative number for the second argument receives the error Out of range value as seen in the preceding code. The error message is accurate, but it is confusing. There is no indication that p_incr refers to the second argument (-10). The error message speciﬁes column ’p_incr’, which is confusing because there are no columns being referenced, only variables and scalar values. The user will have to examine the stored routine to ﬁgure out the problem. There is no debug- ger for stored routines, so logical errors can be difﬁcult to detect and ﬁx. One way to debug a stored procedure is to use a SELECT statement to print variables at certain positions. A SELECT statement before each line mimics stepping through the stored procedure and checking variable values at each step. By the way, data type incompatibilities and overﬂows in a stored routine are treated the same as on the command line. Warnings or errors are generated, depending on the sql_mode. This holds true for arguments as well as local variables. Conditions and handlers Though the error handling in stored routines is not very advanced, MySQL allows you to spec- ify handlers that can trap conditions. This allows you to handle known exceptions. Using the DECLARE syntax, you can create a handler to specify what to do when a condition is met. You can also create a condition using the DECLARE syntax for use in a handler. You can ﬁnd a full list of MySQL error codes, SQL states, and messages in the manual at http://dev.mysql.com/doc/refman/6.0/en/error-messages-server.html. Handlers The stored procedure pct_increase deﬁned an OUT variable as a signed decimal with two dec- imal places. What happens if the OUT variable requires more precision? mysql> SHOW CREATE PROCEDURE pct_increase\G *************************** 1. row *************************** Procedure: pct_increase sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`localhost` PROCE- DURE `pct_increase` (INOUT p_int INT, IN p_incr INT UNSIGNED, OUT p_pct_incr DECI- MAL (5,2)) 275
Part II Developing with MySQL BEGIN DECLARE p_int_new INT; SET p_int_new:=p_int+p_incr; SET p_pct_incr:=(p_int_new-p_int)/p_int*100; SET p_int:=p_int_new; END character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) mysql> SET @num:=100; Query OK, 0 rows affected (0.00 sec) mysql> CALL pct_increase (@num, 10, @pct); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @num, @pct; +------+-------+ | @num | @pct | +------+-------+ | 110 | 10.00 | +------+-------+ 1 row in set (0.00 sec) mysql> CALL pct_increase (@num, 10, @pct); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +-------+------+-------------------------------------------------+ | Level | Code | Message | +-------+------+-------------------------------------------------+ | Note | 1265 | Data truncated for column ’p_pct_incr’ at row 1 | +-------+------+-------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @num, @pct; +------+------+ | @num | @pct | +------+------+ | 120 | 9.09 | +------+------+ 1 row in set (0.00 sec) There was data truncated for a variable, and a warning was generated. If the stored procedure had an sql_mode that was set to TRADITIONAL, there would have been an error generated. Note that the warning included at row 1, so there is no way of knowing which line in the 276
Stored Routines, Triggers, and Events 7 stored procedure the truncation occurred at. Looking at the stored procedure deﬁnition, there is only one line that could have thrown this warning: SET p_pct_incr:=(p_int_new-p_int)/p_int*100; When the stored procedure was invoked, p_int was given a value of @num, which had a value of 110. p_int_new was set to p_int+p_incr, and had been given 10 as a value of p_incr. So p_int_new had a value of 120. What did the calculation actually produce? mysql> SELECT (120-110)/110*100; +-------------------+ | (120-110)/110*100 | +-------------------+ | 9.0909 | +-------------------+ 1 row in set (0.00 sec) p_pct_incr was deﬁned as a DECIMAL(5,2), and the stored procedure tried to put 9.0909 into p_pct_incr. The warning is justiﬁed, as data was truncated. However, you might not care about data truncation in this calculation. To not be notiﬁed, create a HANDLER for the warning. A handler is speciﬁed with the syntax: DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR condition statement condition is one of the following values: ■ SQLWARNING — The SQL state of all warnings. ■ NOT FOUND — The SQL state that occurs when a cursor has reached the end of a data set. For more information on cursors, see the ‘‘Using Cursors’’ section later in this chapter. ■ SQLEXCEPTION — Any SQL state other than OK, SQLWARNING, and NOT FOUND. ■ mysql_error_code — Replace mysql_error_code with the error number (that is, 1265) to handle. ■ condition_name — A user-deﬁned condition name. See the ‘‘Conditions’’ subsection for more information. ■ SQLSTATE [VALUE] sqlstate — Replace sqlstate with the SQL state to handle (that is, SQLSTATE VALUE 01000). statement is the SQL statement to run when the condition is met. In the example, if data truncation happens, the stored procedure should continue. There is no need to exit or undo anything done previously in the stored procedure. Thus, specify CON- TINUE. There are two different ways to specify the condition to handle — either SQLWARNING or 1265, the error code for data truncation. Because the SQLWARNING condition covers all warnings, not just data truncation, it is a good idea to limit the handler only to the condition where data is truncated. Note that the handler for error code 1265 will be invoked when any data is truncated, not just for the p_pct_incr variable. 277
Part II Developing with MySQL The following example increments a user variable when the data truncation handler is invoked. The scope of the user variable is outside of the stored routine, so the stored procedure will dis- play how many times the warning was issued. mysql> DROP PROCEDURE IF EXISTS pct_increase; Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER | mysql> CREATE PROCEDURE pct_increase (INOUT p_int INT, IN p_incr INT, OUT p_pct_ incr DECIMAL (5,2)) -> BEGIN -> DECLARE p_int_new INT UNSIGNED; -> DECLARE CONTINUE HANDLER FOR 1265 SET @warn_count:=@warn_count+1; -> SET p_int_new:=p_int+p_incr; -> SET p_pct_incr:=(p_int_new-p_int)/p_int*100; -> SET p_int:=p_int_new; -> SELECT p_int, p_pct_incr, @warn_count; -> END -> | Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; Note the last SELECT query to show the variable values. This is for teaching purposes only. There is no need to see the values of the variables by calling SELECT @num, @pct, @warn_count. When debugging a stored routine, use SELECT statements to print out information to help ﬁnd the problem. Here is an example of using the stored procedure with the handler for data truncation: mysql> SET @warn_count:=0; Query OK, 0 rows affected (0.00 sec) mysql> SET @num:=100; Query OK, 0 rows affected (0.00 sec) mysql> CALL pct_increase (@num, 10, @pct); +-------+------------+-------------+ | p_int | p_pct_incr | @warn_count | +-------+------------+-------------+ | 110 | 10.00 | 0 | +-------+------------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) 278
Stored Routines, Triggers, and Events 7 mysql> CALL pct_increase (@num, 10, @pct); +-------+------------+-------------+ | p_int | p_pct_incr | @warn_count | +-------+------------+-------------+ | 120 | 9.09 | 1 | +-------+------------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALL pct_increase (@num, 10, @pct); +-------+------------+-------------+ | p_int | p_pct_incr | @warn_count | +-------+------------+-------------+ | 130 | 8.33 | 2 | +-------+------------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) No warnings are showing up, and the @warn_count global user variable is being incremented each time the stored procedure is called. Is @warn_count being incremented only when a warn- ing would happen? mysql> SET @num:=0; Query OK, 0 rows affected (0.00 sec) mysql> CALL pct_increase (@num, 10, @pct); +-------+------------+-------------+ | p_int | p_pct_incr | @warn_count | +-------+------------+-------------+ | 10 | NULL | 2 | +-------+------------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALL pct_increase (@num, 10, @pct); +-------+------------+-------------+ | p_int | p_pct_incr | @warn_count | +-------+------------+-------------+ | 20 | 100.00 | 2 | +-------+------------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALL pct_increase (@num, 10, @pct); 279
Part II Developing with MySQL +-------+------------+-------------+ | p_int | p_pct_incr | @warn_count | +-------+------------+-------------+ | 30 | 50.00 | 2 | +-------+------------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALL pct_increase (@num, 10, @pct); +-------+------------+-------------+ | p_int | p_pct_incr | @warn_count | +-------+------------+-------------+ | 40 | 33.33 | 3 | +-------+------------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) This is the ﬁrst time @warn_count has incremented since @num was set to 0. And it is the ﬁrst time that the percentage @num increased by a precision greater than two decimal places. mysql> CALL pct_increase (@num, 10, @pct); +-------+------------+-------------+ | p_int | p_pct_incr | @warn_count | +-------+------------+-------------+ | 50 | 25.00 | 3 | +-------+------------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALL pct_increase (@num, 10, @pct); +-------+------------+-------------+ | p_int | p_pct_incr | @warn_count | +-------+------------+-------------+ | 60 | 20.00 | 3 | +-------+------------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALL pct_increase (@num, 10, @pct); +-------+------------+-------------+ | p_int | p_pct_incr | @warn_count | +-------+------------+-------------+ | 70 | 16.67 | 4 | +-------+------------+-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) 280
Stored Routines, Triggers, and Events 7 Indeed, the handler is handling the condition of data truncation (MySQL error code 1265) appropriately. To truly ignore the condition and not do any action, you could have speciﬁed an empty statement: DECLARE CONTINUE HANDLER FOR 1265 BEGIN END; Conditions In the previous example, the ‘‘data truncated’’ error code (1265) was handled with this DECLARE statement: DECLARE CONTINUE HANDLER FOR 1265 SET @warn_count=@warn_count+1; The DECLARE syntax can be used to create and name a custom condition for use in a handler. The syntax is: DECLARE condition_name CONDITION FOR { mysql_error_code | SQLSTATE [VALUE] sqlstate } To name error code 1265 instead of creating a handler that speciﬁes the error code directly, use the following syntax: DECLARE data_truncation CONDITION FOR 1265; And the handler declaration changes to: DECLARE CONTINUE HANDLER FOR data_truncation SET @warn_count=@warn_count+1; This may seem like extra work; however, appropriate naming of conditions can make error handling much easier. Consider the difference between HANDLER FOR data_truncation and HANDLER FOR 1265 — which is more readable? If a stored routine has 100 error handlers, should they be declared by number? Named conditions are extremely useful when conditions are named appropriately. Conditions named error_handler and ignore_me are not useful. Consider a database policy of always naming conditions explicitly and appropriately. Order of DECLARE Statements ll DECLARE conditions must occur before any DECLARE handler. That makes sense; if a handler A uses a condition, the condition must be deﬁned before the handler is. MySQL takes this one step further and requires all conditions be deﬁned before any handlers are deﬁned. If a handler or condition uses a local variable, the local variable must be deﬁned ﬁrst. MySQL requires that all DECLARE variable statements must occur before any DECLARE condition statement. In short, the required order of DECLARE statements is: variables, conditions, handlers. If a stored routine deﬁnition violates these requirements, mysqld raises an error and rejects the stored routine deﬁnition with the following error: ERROR 1337 (42000): Variable or condition declaration after cursor or han- dler declaration 281
Part II Developing with MySQL Stored routine ﬂow control Many common ﬂow control statements are supported in stored routines. These statements may be nested to create blocks of code and code loops. The ﬂow control syntax is documented here, though advanced usage of ﬂow control is out of the scope of this book. For more information about using ﬂow control in stored routines, refer to Guy Harrison’s book MySQL Stored Proce- dure Programming (O’Reilly, ISBN 0-596-10089-2). Many commands discussed in this chapter require a BEGIN . . . END block to allow multiple statements. However, most ﬂow control statements allow multiple statements without using a BEGIN . . . END block. When more than one statement can be deﬁned in this manner, statement_list will denote that more than one statement can be used. For ﬂow control statements that allow a statement list without a BEGIN . . . END block, the end of the statement list is implied — for example, in an IF statement, the end of the statement list is implied by the next ELSEIF, ELSE, or END IF. IF The syntax for the IF statement is: IF condition THEN statement_list [ELSEIF condition THEN statement_list] . . . [ELSE statement_list] END IF The IF ﬂow control statement is different from the IF ﬂow control function. The IF ﬂow control statement described here is a procedural device. The IF ﬂow control function has the syntax IF(test_expr, expr_if_true, expr_if_false). CASE As with other procedural code, it is often desirable to replace a nested IF statement with a CASE statement. The CASE statement has two acceptable syntaxes: CASE expr WHEN condition THEN statement_list [ . . . ] [ELSE statement_list] END CASE This ﬁrst syntax takes an expression that evaluates to a value, compares it using one or more conditions, evaluating the appropriate statements if a condition matches, and ending with the optional catch-all ELSE. The second syntax is similar, except that the condition includes the value: CASE WHEN condition THEN statement_list [ . . . ] [ELSE statement_list] END CASE 282
Stored Routines, Triggers, and Events 7 The CASE ﬂow control statement and the CASE ﬂow control function have the same syntax. To clarify, using the ﬁrst syntax to see whether the ﬂag is set: CASE @flag WHEN 0 THEN SELECT "flag is 0" WHEN 1 THEN SELECT "flag is 1" ELSE SELECT "flag is not 0 or 1, it is ", @flag; END CASE; And using the second syntax: CASE WHEN @flag=0 THEN SELECT "flag is 0" WHEN @flag=1 THEN SELECT "flag is 1" ELSE SELECT "flag is not 0 or 1, it is ", @flag; END CASE; In this case, either syntax can be used. There are times when only one of the syntaxes can express the conditional statement you want — when comparing different values, for example, the second syntax is required. CASE statements must match on a condition; ELSE is the catch-all condition to use if you believe there might be a condition that does not match. If a condition is not matched — which means that none of the WHEN blocks matched and there is no ELSE block — an error is generated: ERROR 1339 (20000): Case not found for CASE statement WHILE The most familiar way to create a loop in MySQL is with the WHILE statement. The syntax for WHILE is: [label:] WHILE condition statement_list END WHILE [label] REPEAT REPEAT is similar to what many language call ‘‘until.’’ In fact, the syntax contains the keyword UNTIL: [label:] REPEAT statement_list UNTIL condition END REPEAT [label] Similar to other procedural languages, the biggest difference between WHILE and REPEAT (’’until’’) is that with REPEAT the code executes at least once, because the test condition is after the code. 283
Part II Developing with MySQL The REPEAT block does not have to be labeled. However, the labels at the beginning and end of the loop must match, and if the label at the end is deﬁned, the label at the beginning must also be deﬁned. LOOP A loop allows for manual creation of loops, without using the WHILE or REPEAT statements. It is deﬁned very simply: [label:] LOOP statement_list END LOOP [label] The loop does not have to be labeled — labels are required for use with the LEAVE or ITER- ATE statements. However, the labels at the beginning and end of the loop must match, and if the label at the end of the loop is deﬁned, the label at the beginning of the loop must also be deﬁned. Note that the name is somewhat misleading — a LOOP is simply a code block. To actually form a loop, other statements must be used to go back to the beginning of a LOOP and exit a LOOP without reaching END LOOP. In essence, LOOP . . . END LOOP is just a label. To make a loop actu- ally loop, use the ITERATE and LEAVE commands. ITERATE The ITERATE command is used to go to the beginning of a labeled block of code: ITERATE label LEAVE To exit any labeled ﬂow control construct, use the LEAVE statement. The syntax is: LEAVE label LEAVE can be used to exit LOOP, REPEAT, and WHILE statements. Recursion MySQL allows recursive stored procedures, but not recursive stored functions. The parameter max_sp_recursion_depth limits the number of times a procedure can be called recursively. This parameter is set to 0 by default, which disables the use of recursive stored procedures. If the value of max_sp_recursion_depth is not changed, a recursive stored procedure may be created, but when attempting to execute the stored procedure an error will occur: ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine your_routine_name 284
Stored Routines, Triggers, and Events 7 The maximum value for max_sp_recursion_depth is 255. Note that this uses space in the thread stack, so increasing this value may require an increase of the thread_stack variable as well. See Chapter 10 for more information about the thread_stack system variable. Stored routines and replication The row-based and mixed replication modes write data changes to the binary logs, and are the safest way to obtain a copy of your data. In statement-based replication mode, statements that may change data are written to the binary logs, and the statements are applied to the data on the slave server. A small discrepancy in the data on a slave and master can have disastrous results in statement-based replication. There are several important properties of stored routines combined with replication. If a stored routine is not replicating as you think it should, consider the following points: ■ The CREATE, ALTER, and DROP statements for stored procedures and functions are written to the binary logs. ■ Be careful when using statement-based replication — deterministic stored routines may not replicate properly. mysqld tries to prevent the creation of non-deterministic routines when binary logging is disabled, but is not always successful. ■ Stored procedures and functions that call other stored procedures and functions may not replicate how you think they should using statement-based replication. ■ A stored procedure is not invoked on a slave with a CALL statement; it replicates the actual SQL used by the stored procedure. The exception is when a stored procedure is called by a stored function (see previous point). If that happens, the stored procedure CALL is indeed replicated. ■ If a function is deﬁned differently on the master and on the slave, and statement-based replication is being used, there may be different results on the master and on the slave. ■ Though a user invokes a stored function on a master, a slave using statement-based repli- cation will invoke the stored function with full privileges. Stored function limitations Unlike stored procedures, stored functions cannot: ■ Return a result set ■ Be recursive ■ Call statements that do a COMMIT or ROLLBACK ■ COMMIT ■ ROLLBACK ■ START TRANSACTION 285
Part II Developing with MySQL ■ LOCK TABLES and UNLOCK TABLES when there were locked tables ■ SET AUTOCOMMIT=1 when it was not already set to 1 ■ TRUNCATE TABLE ■ Most ALTER, CREATE, DROP, and RENAME commands cause an implicit COMMIT (see Chapter 4) ■ Most CREATE commands cause an implicit COMMIT (see Chapter 4) Stored routine backup and storage Stored routines are stored in the proc table of the mysql database. Manual manipulation of the proc table is not advised. Stored routine backup can be done by using mysqldump, and during hot and cold backups. For more information on backing up stored routines, see Chapter 13. It is possible, but not recommended, to see a list of stored routines by querying the proc table: mysql> SELECT db,name,type FROM mysql.proc; +--------+----------------------------+-----------+ | db | name | type | +--------+----------------------------+-----------+ | sakila | check_actors | PROCEDURE | | sakila | film_in_stock | PROCEDURE | | sakila | film_not_in_stock | PROCEDURE | | sakila | get_customer_balance | FUNCTION | | sakila | increment_counter | PROCEDURE | | sakila | inventory_held_by_customer | FUNCTION | | sakila | inventory_in_stock | FUNCTION | | sakila | rewards_report | PROCEDURE | | sakila | store_offerings | PROCEDURE | +--------+----------------------------+-----------+ 9 rows in set (0.00 sec) We recommend using the ROUTINES table of the INFORMATION_SCHEMA database to get this information, because using the proc table of the mysql database sets a bad example. mysql> SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE -> FROM INFORMATION_SCHEMA.ROUTINES; +----------------+----------------------------+--------------+ | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | +----------------+----------------------------+--------------+ | sakila | check_actors | PROCEDURE | | sakila | film_in_stock | PROCEDURE | | sakila | film_not_in_stock | PROCEDURE | | sakila | get_customer_balance | FUNCTION | | sakila | increment_counter | PROCEDURE | | sakila | inventory_held_by_customer | FUNCTION | | sakila | inventory_in_stock | FUNCTION | | sakila | rewards_report | PROCEDURE | | sakila | store_offerings | PROCEDURE | +----------------+----------------------------+--------------+ 6 rows in set (0.01 sec) 286