MySQL stored procedures and functions

MySQL stored procedures and functions
Overview

When it comes to stored procedures, the advantages and disadvantages of stored procedures may be introduced. We will not discuss them here. If someone asks me, I will answer this question and you will use it if you think it is good. Because the stored procedure in mysql is very close to the syntax of the function, the main difference is that the function must have a return value ), IN addition, function parameters only have the IN type, while stored procedures have the IN, OUT, And INOUT types.

Syntax

Create stored procedure 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

The syntax comes from the official reference manual. The characteristic syntax block is worth attention. First, we will introduce it with an example.

Example:

# CREATE a database drop database if exists Dpro; create database DproCHARACTER SET utf8; USE Dpro; # CREATE a 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 'personal name', depid int not null comment 'department id ');
# INSERT test data insert into Employee (id, name, depid) VALUES (1, 'chen ', 100), (2, 'King', 101), (3, 'zhang ', 101), (4, 'lil', 102), (5, 'Guo', 103 );
# 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 the Stored Procedure CALL Pro_Employee (101, @ pcount); SELECT @ pcount;

Syntax explanation:

DELIMITER $ ..... END $ DELIMITER; put it at the beginning and END to avoid mysql interpreting the ";" in the stored procedure as the END symbol, and finally telling the END of the stored procedure through "DELIMITER.

This section mainly explains characteristic:

Language SQL:It indicates that the statement is an SQL statement. Other types of statements may be supported in the future.

[NOT] DETERMINISTIC:If the program or thread always produces the same result for the same input parameter, it is regarded as "OK", otherwise it is "uncertain. If neither DETERMINISTIC nor NOT terministic is specified, the default DETERMINISTIC is NOT (uncertain)Contains SQL:A statement that does not contain read or write data.

No SQL:Indicates that the subroutine does not contain SQL statements.

Reads SQL DATA:A subprogram contains a read statement but does not contain a Write statement.

Modifies SQL DATA:A subprogram contains a statement for writing data.

SQL SECURITY DEFINER:Indicates that the stored procedure is executed by the user who created the stored procedure.

SQL SECURITY INVOKER:Indicates that the stored procedure is executed by the user who calls the stored procedure. (For example, the preceding stored procedure is executed by the user who calls the stored procedure. The current stored procedure is used to query the Employee table, if the user executing the stored procedure does not have the permission to query the Employee table, the system will return an error of insufficient permissions, if it is replaced by DEFINER, if the stored procedure is created by the ROOT user, any user can log on to call the stored procedure, because the permission to execute the Stored Procedure becomes root)

COMMENT 'string ':The remarks are the same as the field remarks for creating a table.

Note: When writing stored procedures and functions, we recommend that you specify the state of the characteristic part above, especially in a replication environment. If the created function does not explicitly specify these statuses, an error is returned, if the DETERMINISTIC, no SQL, or reads SQL DATA status is not explicitly specified on a machine that migrates a database with a function to a replication environment, an error is returned.

Error example

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 is the problem mentioned above. It turns out that the binary log option log-bin is enabled on the two MySQL servers of master-slave replication, and slave will copy data from the master, and some operations, for example, the results of function may be different on the master and slave, so there are potential security risks. Therefore, function creation 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 enabled can create functions at will. There are potential data security issues here, unless you know exactly that the created function acts exactly the same on the master and slave. You can set this parameter dynamically or specify this parameter to start the database server or modify the configuration file and then restart the server. It should be noted that the dynamic setting method will expire after the server is restarted. Mysql> show variables like 'Log _ bin_trust_function_creators '; mysql> set global log_bin_trust_function_creators = 1; if you create a function on the master, to copy a function to slave by means of master-slave replication, you also need to set the value of the above variable to ON in the slave with log-bin enabled (the variable setting will not be copied from the master to the slave., this must be noted), otherwise the master-slave replication will report an error. 2. specify the type of the function 1 DETERMINISTIC 2 no SQL does not have an SQL statement, of course, will not modify the DATA 3 reads SQL DATA only read DATA, of course, will not modify the DATA such: create definer = 'username' @ '%' reads SQL data function' fn _ getitemclock' (I _itemid bigint, I _clock int, I _pos int) RETURNS int (11 )... this is equivalent to explicitly notifying the MySQL server that this function will not modify data. Therefore, you can securely create and copy the function 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 the Stored Procedure status

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

show procedure status like 'Pro_Employee' \G

2. view the creation Syntax of stored procedures and functions

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

3. View stored procedure and function details

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

Summary

Stored Procedures and function syntax are not difficult to understand, but often stored procedures not only contain this simple query syntax, but also nested loop statements, variables, error handling, transactions, etc, the next article will talk about variables separately and add the knowledge of variables to the stored procedure, including variable declaration and error handling.

 

 

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion