Tài liệu MySQL Administrator''''s Bible- P7 pdf

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 verified 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 fix 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 define the function.

Turning binary logging off is another way to fix this issue, though it may not be possible. Setting

log_bin_trust_function_creators to 1 will also fix 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 qualifies 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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

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 defined. 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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Stored Routines, Triggers, and Events 7

The following defines 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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

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 find all the stored routines associated with a database. To use

SQL to find 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

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.