Understanding MySQL stored procedures and functions, mysql stored procedure functions

Understanding MySQL stored procedures and functions, mysql stored procedure functions

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

Ii. 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 the 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; # Run 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 is used to explain that some statements are SQL statements. 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 "definite"; otherwise, it is "uncertain. If neither DETERMINISTIC nor not deterministic is specified, the default value is not deterministic (uncertain) contains SQL: a statement indicating that a subroutine does NOT contain read or write data.

No SQL: indicates that the subprogram 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': remarks, which is 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 you do not specify these statuses explicitly, 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;

In addition, 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 function type
1) DETERMINISTIC
2) no SQL statement does not exist, and data will not be modified.
3) reads SQL DATA only READS DATA and does not modify the DATA.
For example: 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

Copy codeThe Code is as follows: 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.

Articles you may be interested in:

• Mysql stored procedures and functions
• Mysql statements used to query stored procedures and functions in a database
• In-depth explanation of mysql user-defined functions and stored procedures
• Introduction to mysql Database Import and Export, functions, and stored procedures
• Differences between Mysql stored procedures and functions
• MySql stored procedures and functions
• Tutorials on basic functions and triggers in MySQL stored procedures