Understanding Mysql stored procedures and functions _mysql

I. Overview

A reference to the stored procedure may lead to another topic is the advantages and disadvantages of stored procedures, there is no discussion, the general people ask me to answer you think it is good you use it. Because the syntax of stored procedures and functions in MySQL is very close, so put together, the main difference is that the function must have a return value (returns), and the function of the parameter only in the type and stored procedures have in, out, inout of these three types.

Second, the grammar

Creating 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

The grammar comes from the official reference manual, the characteristic grammar block is the place which needs to notice, first uses one example to introduce.

Example:

#创建数据库
DROP DATABASE IF EXISTS dpro;
CREATE DATABASE dpro
CHARACTER SET UTF8
;

Use Dpro;

#创建部门表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT not NULL PRIMARY key COMMENT ' primary key ',
 name VARCHAR not null COMMENT ' person name ',
 DEP ID INT not NULL COMMENT ' Department id '
);

#插入测试数据
INSERT into Employee (id,name,depid) VALUES (1, ' Chen ', 100), (2, ' Wang ', 101), (3, ' Zhang ', 101), (4, ' Lee ', 102), (5, ' Guo ', 103 );

#创建存储过程
DROP PROCEDURE IF EXISTS pro_employee;
DELIMITER $$
CREATE PROCEDURE pro_employee (in Pdepid VARCHAR (m), out Pcount INT)
reads SQL DATA
SQL secur ity invoker
BEGIN
SELECT COUNT (ID) into Pcount from Employee WHERE depid=pdepid;

end$$
DELIMITER;

#执行存储过程 call
Pro_employee (@pcount);

SELECT @pcount;

Syntax Explanation:

When you create a stored procedure, you usually use delimiter$$ ... end$$ DELIMITER in the beginning and end, the purpose is to avoid MySQL putting the stored procedure inside the ";" interpreted as a closing symbol, and finally passed "DELIMITER;" To tell the stored procedure to end.

Main explanation characteristic part:

LANGUAGE sql: used to describe a statement that is part of an SQL statement, and may support other types of statements in the future.

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

No sql: indicates that the subroutine does not contain SQL statements.

reads SQL DATA: A statement that indicates that a subroutine contains read data, but does not contain a statement that writes data.

modifies SQL DATA: A statement that indicates that a subroutine contains write data.

SQL Security Definer: indicates that the execution of a program in a stored procedure is performed by the permissions of the user who created the stored procedure.

SQL Security Invoker: indicates that the execution of a program in a stored procedure is performed by the permissions of the user who invoked the stored procedure. (for example, the above stored procedure I write is executed by the permissions of the user who invoked the stored procedure, and the current stored procedure is used to query the employee table, and if my current executing stored procedure does not have permission to query the employee table, it returns an error with insufficient permissions. If you change to definer if the stored procedure is created by the root user, then any user login call stored procedure can execute because the permission to execute the stored procedure becomes root.

COMMENT ' string ': notes, same as field notes to create a table.

Note: When writing stored procedures and functions, it is recommended that the state of the characteristic section above be explicitly specified, especially if there is a replicated environment, if the creation function does not explicitly specify that these states will error. Migrating a database with functions from a non-replication environment to a machine on a replicated environment without explicitly specifying deterministic, no SQL, or reads SQL data, the three states also have an error.

Example of an error

Error code:1418. This function has none of the deterministic, NO SQL, or reads SQL DATA in the its declaration and binary logging are enabled (you * Might* want to use the less safe log_bin_trust_function_creators variable)
This error is the above note that part of the problem. It turns out that the binary log option is turned on in the two MySQL servers that are copied from the master. Log-bin,slave will copy the data, and some operations, such as function results may be different on master and slave, so there are potential security risks. Therefore, the creation of a function is blocked next time by default.

There are two ways to solve this problem:

1. Set the log_bin_trust_function_creators parameter to on so that the Log-bin MySQL server is opened to create a function at will. There are potential data security issues, unless it is clear that the created function is exactly the same behavior on master and slave.
You can set this parameter to restart the server either dynamically or by specifying the parameter to start the database server or modify the configuration file. It is important to note that the dynamic settings are invalidated after the server restarts.

Mysql> Show variables like ' log_bin_trust_function_creators ';
 mysql> set global Log_bin_trust_function_creators=1;

In addition, if you are creating a function on master, and you want to copy the function to slave via master-slave copying, you also need to set the value of the above variable to on in the slave on which the log-bin is turned on (the setting of the variable is not copied from master to the Slave), Otherwise the master and slave copy will be an error.

2. Clearly indicate the type of function
1), deterministic uncertain
2), no SQL No SQL statements, of course, will not modify the data
3, reads SQL data just read the data, of course, will 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 equates to a clear notification to the MySQL server that the function does not modify the data, so it can be created securely on the Log-bin server and replicated to the slave on the Open log-bin.

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

viewing stored procedures and functions

1. View stored Procedure Status

Show {PROCEDURE | FUNCTION} status [like ' mode '] show
procedure STATUS like ' Pro_employee ' \g

2. View the creation syntax for stored procedures and functions

Show CREATE {PROCEDURE | FUNCTION} sp_name show

CREATE PROCEDURE pro_employee \g;

3. View stored procedures and function details

Copy Code code as follows:

SELECT * from INFORMATION_SCHEMA. Routines WHERE routine_name= ' Pro_employee ' \g;

Summarize

  Stored procedures and functional syntax are not difficult to understand, but often stored procedures not only include this simple query syntax, but also nested loop statements, variables, error processing, transactions, and so on, the next article will be a separate variable, the knowledge of the variable to add to the stored procedures, including variable declarations and error handling, Welcome attention.