What are stored procedures and stored functions in mysql?

In mysql, stored procedures and stored functions are a collection of SQL statements defined in the database. Among them, stored functions can return function values ​​through return statements and are mainly used to calculate and return a value; while stored procedures do not directly return values ​​and are mainly used to perform operations.

(Recommended tutorial: mysql video tutorial)

Stored procedures in mysql

Writing stored procedures is not a simple matter, but using stored procedures can simplify operations and reduce redundant operating steps. At the same time, it can also reduce errors during operations and improve efficiency. , so you should learn to use stored procedures as much as possible.

The following mainly introduces how to create a stored procedure.

You can use the CREATE PROCEDURE statement to create a stored procedure. The syntax format is as follows:

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>

[Process parameters[,…]] Format

[ IN | OUT | INOUT ] <参数名> <类型>

The syntax description is as follows:

1) Procedure name

The name of the stored procedure, created in the current database by default. If you need to create a stored procedure in a specific database, prepend the name with the name of the database, db_name.sp_name.

It should be noted that the name should try to avoid choosing the same name as the MySQL built-in function, otherwise an error will occur.

2) Process parameters

The parameter list of the stored procedure. Among them, is the parameter name, and is the type of the parameter (can be any valid MySQL data type). When there are multiple parameters, separate them with commas in the parameter list. A stored procedure can have no parameters (in this case, a pair of parentheses still need to be added after the name of the stored procedure), or it can have one or more parameters.

MySQL stored procedures support three types of parameters, namely input parameters, output parameters and input/output parameters, which are identified by the three keywords IN, OUT and INOUT respectively. Among them, input parameters can be passed to a stored procedure, output parameters are used when the stored procedure needs to return an operation result, and input/output parameters can serve as both input parameters and output parameters.

It should be noted that the parameter name should not be the same as the column name of the data table. Otherwise, although no error message will be returned, the SQL statement of the stored procedure will regard the parameter name as a column name, causing an error. Predictable results.

3) Procedure body

The main part of the stored procedure, also called the stored procedure body, contains the SQL statements that must be executed when the procedure is called. This section begins with the keyword BEGIN and ends with the keyword END. If there is only one SQL statement in the stored procedure body, the BEGIN-END flag can be omitted.

In the creation of stored procedures, a very important MySQL command is often used, namely the DELIMITER command. Especially for users who operate the MySQL database through the command line, they must learn to use this command. Order.

In MySQL, when the server processes SQL statements, the semicolon is used as the end mark of the statement by default. However, when creating a stored procedure, the stored procedure body may contain multiple SQL statements. If these SQL statements still use a semicolon as the statement end character, the MySQL server will end with the first SQL statement encountered during processing. The semicolon is used as the terminator of the entire program, and the subsequent SQL statements in the stored procedure body are no longer processed. This is obviously not possible.

To solve the above problems, the DELIMITER command is usually used to modify the end command to other characters. The syntax format is as follows:

DELIMITER $$

The syntax description is as follows:

• $$ is the user-defined terminator, usually this The symbol can be some special symbols, such as two "?" or two "￥", etc.

• When using the DELIMITER command, you should avoid using the backslash "\" character because it is the MySQL escape character.

Enter the following SQL statement in the MySQL command line client.

mysql > DELIMITER ??

After successfully executing this SQL statement, the end mark of any command, statement or program will be replaced by two question marks "??".

If you want to change back to the default semicolon ";" as the end mark, enter the following statement in the MySQL command line client:

mysql > DELIMITER ;

Note: DELIMITER and semicolon ";" There must be a space between them. When creating a stored procedure, you must have CREATE ROUTINE permission.

Stored functions in mysql

In MySQL, use the CREATE FUNCTION statement to create a stored function. The syntax is as follows:

CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type
[characteristic ...] routine_body

Among them:

• sp_name parameter: represents the name of the stored function;

• func_parameter: represents the parameter list of the stored function;

• RETURNS type: Specify the type of return value;

• characteristic parameter: Specify the characteristics of the stored function. The value of this parameter is the same as that of the stored procedure. ;

• routine_body Parameter: Indicates the content of the SQL code. You can use BEGIN...END to mark the beginning and end of the SQL code.

注意：在具体创建函数时，函数名不能与已经存在的函数名重名。除了上述要求外，推荐函数名命名（标识符）为 function_xxx 或者 func_xxx。

func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：
[IN | OUT | INOUT] param_name type;

其中：

• IN 表示输入参数，OUT 表示输出参数，INOUT 表示既可以输入也可以输出；

• param_name 参数是存储函数的参数名称；

• type 参数指定存储函数的参数类型，该类型可以是 MySQL 数据库的任意数据类型。

例 1

使用 CREATE FUNCTION 创建查询 tb_student 表中某个学生姓名的函数，SQL 语句和执行过程如下：

mysql> USE test;
Database changed
mysql> DELIMITER //
mysql> CREATE FUNCTION func_student(id INT(11))
    -> RETURNS VARCHAR(20)
    -> COMMENT '查询某个学生的姓名'
    -> BEGIN
    -> RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);
    -> END //
Query OK, 0 rows affected (0.10 sec)
mysql> DELIMITER ;

上述代码中，创建了 func_student 函数，该函数拥有一个类型为 INT(11) 的参数 id，返回值为 VARCHAR(20) 类型。SELECT 语句从 tb_student 表中查询 id 字段值等于所传入参数 id 值的记录，同时返回该条记录的 name 字段值。

创建函数与创建存储过程一样，需要通过命令 DELIMITER // 将 SQL 语句的结束符由“;”修改为“//”，最后通过命令 DELIMITER ; 将结束符号修改成 SQL 语句中默认的结束符号。

如果在存储函数中的 RETURN 语句返回一个类型不同于函数的 RETURNS 子句中指定类型的值，返回值将被强制为恰当的类型。比如，如果一个函数返回一个 ENUM 或 SET 值，但是 RETURN 语句返回一个整数，对于 SET 成员集的相应的 ENUM 成员，从函数返回的值是字符串。

拓展阅读

由于存储函数和存储过程的查看、修改、删除等操作几乎相同，所以我们不再详细讲解如何操作存储函数了。

查看存储函数的语法如下：

SHOW FUNCTION STATUS LIKE 存储函数名;
SHOW CREATE FUNCTION 存储函数名;
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=存储函数名;

可以发现，操作存储函数和操作存储过程不同的是将 PROCEDURE 替换成了 FUNCTION。同样，修改存储函数的语法如下：

ALTER FUNCTION 存储函数名 [ 特征 ... ]

存储函数的特征与存储过程的基本一样。

The above is the detailed content of What are stored procedures and stored functions in mysql?. For more information, please follow other related articles on the PHP Chinese website!