Shulou(Shulou.com)06/03 Report--

This article mainly introduces the three types and function handouts of MySQL stored procedures, hoping to add and update some knowledge. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

The mention of stored procedures may lead to another topic, which is the advantages and disadvantages of stored procedures. I will not discuss them here. I usually answer when people ask me that you think it is good and you use it. Because the syntax of stored procedures and functions in mysql are very similar, they are put together. The main difference is that the function must have a return value (return), and the parameters of the function have only IN types while stored procedures have three types: IN, OUT, and INOUT.

Grammar

Create stored procedures and function syntax

CREATE PROCEDURE sp_name ([proc_parameter [,...]]) [characteristic...] Routine_body CREATE FUNCTION sp_name ([func_parameter [,...]]) RETURNS type [characteristic...] Routine_body proc_parameter: [IN | OUT | INOUT] param_name type func_parameter: param_name type type: Any valid MySQL data type characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER | INVOKER} | COMMENT 'string' routine_body: Valid SQL procedure statement or statements

Grammar comes from the official reference manual, characteristic syntax block is a place to pay attention to, first with an example to introduce.

Example:

# create database DROP DATABASE IF EXISTS Dpro;CREATE DATABASE DproCHARACTER SET utf8;USE Dpro;# create department table DROP TABLE IF EXISTS Employee;CREATE TABLE Employee (id INT NOT NULL PRIMARY KEY COMMENT 'primary key', name VARCHAR (20) NOT NULL COMMENT 'person name', depid INT NOT NULL COMMENT 'department id') # insert test data INSERT INTO Employee (id,name,depid) VALUES (1m 'Chen', 100), (2) IN pdepid VARCHAR 'Wang, 101), (3)' Zhang', 101), (4)'Li', 102), (5) 'Guo', # create a stored procedure DROP PROCEDURE IF EXISTS Pro_Employee;DELIMITER $CREATE PROCEDURE Pro_Employee (IN pdepid VARCHAR (20), OUT pcount INT) READS SQL DATASQL SECURITY INVOKERBEGINSELECT COUNT (id) INTO pcount FROM Employee WHERE depid=pdepid;END$$DELIMITER # execute stored procedure CALL Pro_Employee (101 dint pcount); SELECT @ pcount

Grammatical explanation:

DELIMITER$$.END$$ DELIMITER is usually used when creating a stored procedure; the purpose of putting it at the beginning and end is to prevent mysql from interpreting the ";" inside the stored procedure as a closing symbol, and finally using "DELIMITER;" to tell the stored procedure to end.

The main explanation part of characteristic:

LANGUAGE SQL: used to indicate that the statement part is a SQL statement and other types of statements may be supported in the future.

[NOT] DETERMINISTIC: if a program or thread always produces the same result for the same input parameter, it is considered to be "certain", otherwise it is "indeterminate". If there is neither a given DETERMINISTIC nor a given NOT DETERMINISTIC, the default is NOT DETERMINISTIC (indeterminate) CONTAINS SQL: indicates that the subroutine does not contain statements that read or write data.

NO SQL: indicates that the subroutine does not contain a SQL statement.

READS SQL DATA: indicates that a subroutine contains statements that read data, but not statements that write data.

MODIFIES SQL DATA: indicates that the subroutine contains statements to write data.

SQL SECURITY DEFINER: indicates that a program executing a stored procedure is executed by the user who created the stored procedure.

SQL SECURITY INVOKER: indicates that a program executing a stored procedure is executed by the user who called the stored procedure. (for example, the above stored procedure I wrote is executed by the permission of the user who called the stored procedure. The current stored procedure is used to query the Employee table. If my current user executing the stored procedure does not have the permission to query the Employee table, it will return an error of insufficient permissions. If replaced by DEFINER, if the stored procedure is created by a ROOT user, then any user who logs in to call the stored procedure can execute it. Because the permission to execute the stored procedure becomes root)

COMMENT 'string': comments, the same as the field comments that create the table.

Note: when writing stored procedures and functions, it is recommended to specify the status of the above characteristic section, especially in the replication environment. If the creation function does not specify these states, an error will be reported. If you migrate the database with the function from a non-replication environment to the machine in the replication environment, you will also report an error if you do not specify DETERMINISTIC, NO SQL and or READS SQL DATA.

Example of reporting an error

Error Code: 1418. 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)

This error report is the problem mentioned in the attention section above. It turns out that the binary log option log-bin,slave will copy data from master when enabled in the two master-slave replicated MySQL CVMs, while some operations, such as function, may have different results on master and slave, so there are potential security risks. Therefore, the creation of function is blocked by default.

There are two solutions:

1. Set the log_bin_trust_function_creators parameter to ON, so that the MySQL Server with log-bin turned on can create a function at will. There are potential data security issues here, unless it is clear that the created function behaves exactly the same on master and slave. You can set this parameter dynamically or specify this parameter to start the database CVM or restart the CVM after modifying the configuration file. It should be noted that the dynamic setting will fail after the server is restarted. Mysql > show variables like 'log_bin_trust_function_creators'; mysql > set global log_bin_trust_function_creators=1; in addition, if you create a function on master and want to copy the function to slave by master-slave copy, you should also set the value of the above variable to ON in the slave with log-bin enabled (the setting of the variable will not be copied from master to slave, which should be noted), otherwise master-slave replication will report an error. two。 2 NO SQL which clearly specifies the type of function 1 DETERMINISTIC does not have SQl statements, certainly does not modify data 3 READS SQL DATA just reads data, and certainly does not modify data such as: CREATE DEFINER= `username` @ `% `READS SQL DATA FUNCTION `fn_ getitemclock` (i_itemid bigint,i_clock int,i_pos int) RETURNS int (11). This is tantamount to explicitly telling the MySQL server that this function will not modify the data, so it can be securely created on the server with log-bin enabled and copied to the slave with log-bin enabled.

Modify stored procedure function syntax

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic...] Characteristic: {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | SQL SECURITY {DEFINER | INVOKER} | COMMENT 'string'

Delete stored procedure function syntax

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

View stored procedures and functions

1. View stored procedure status

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern'] show procedure status like' Pro_Employee'\ G

two。 View the creation syntax of stored procedures and functions

SHOW CREATE {PROCEDURE | FUNCTION} sp_nameSHOW CREATE PROCEDURE Pro_Employee\ G

3. View details of stored procedures and functions

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='Pro_Employee'\ G

Read the above three types of MySQL stored procedures and function handouts about, hope to give you some help in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.