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

Editor to share with you what stored procedures and stored functions refer to in mysql. I hope you will gain a lot after reading this article. Let's discuss it together.

In mysql, stored procedures and stored functions are collections of SQL statements defined in the database. Among them, the stored function can return the function value through the return statement, which is mainly used to calculate and return a value, while the stored procedure does not return a value directly and is mainly used to perform operations.

Stored procedures in mysql

Writing stored procedures is not a simple thing, but using stored procedures can simplify the operation and reduce redundant operation steps. At the same time, it can also reduce errors in the operation process and improve efficiency. Therefore, you should learn to use stored procedures as much as possible.

The following is mainly about how to create stored procedures.

You can use CREATE PROCEDURE statements to create stored procedures in the following syntax format:

CREATE PROCEDURE ([process parameters [,...] ])

[process parameters [,...] ] format

[IN | OUT | INOUT]

The syntax is as follows:

1) procedure name

The name of the stored procedure, created by default in the current database. If you need to create a stored procedure in a specific database, add the name of the database, that is, db_name.sp_name, before the name.

It is important to note that the name should avoid choosing the same name as the MySQL built-in function, otherwise an error will occur.

2) process parameters

A list of parameters for the stored procedure. Where is the name of the parameter and the type of the parameter (which can be any valid MySQL data type). When there are multiple parameters, the parameter list is separated from each other by a comma. A stored procedure can have no parameters (at this point the name of the stored procedure still needs to be followed by a pair of parentheses), 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, identified by the IN, OUT, and INOUT keywords, respectively. Among them, the input parameter can be passed to a stored procedure, the output parameter is used in the case where the stored procedure needs to return an operation result, and the input / output parameter can act as both input parameter and output parameter.

It is important to note that the parameter name is not the same as the column name of the data table, otherwise, although no error message is returned, the SQL statement of the stored procedure treats the parameter name as a column name, causing unpredictable results.

3) process body

The body of a stored procedure, also known as the body of a stored procedure, contains 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 body of the stored procedure, the BEGIN-END flag can be omitted.

In the creation of stored procedures, a very important MySQL command, the DELIMITER command, is often used, especially for users who operate the MySQL database through the command line.

In MySQL, the server processes SQL statements with semicolons as the end flag by default. However, when creating a stored procedure, the body of the stored procedure may contain multiple SQL statements. If these SQL statements still use a semicolon as the statement Terminator, then the MySQL server will use the semicolon at the end of the first SQL statement encountered as the Terminator of the whole program, instead of dealing with the later SQL statements in the stored procedure body.

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

DELIMITER $$

The syntax is as follows:

$is a user-defined Terminator, which can usually be some special symbol, such as two "?" Or two "¥" and so on.

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

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

Mysql > DELIMITER?

After the successful execution of this SQL statement, the closing flag of any command, statement, or program is replaced by two question marks. Yes.

If you want to change back to the default semicolon ";" as the closing flag, enter the following statement on the MySQL command line client:

Mysql > DELIMITER

Note: there must be a space between the DELIMITER and the semicolon ";". When you create a stored procedure, you must have CREATE ROUTINE permission.

Storage function in mysql

In MySQL, you use the CREATE FUNCTION statement to create a stored function with the following syntax:

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

Where:

Sp_name parameter: indicates the name of the storage function

Func_parameter: represents the parameter list of the storage function

RETURNS type: specifies the type of return value

Characteristic parameter: specifies the property of the stored function. The value of this parameter is the same as the stored procedure.

The routine_body parameter: represents the content of the SQL code, and you can use BEGIN...END to indicate the beginning and end of the SQL code.

Note: when creating a function, the function name cannot be the same as the existing function name. In addition to the above requirements, it is recommended that the function name (identifier) be function_xxx or func_xxx.

Func_parameter can consist of multiple parameters, each of which consists of a parameter name and a parameter type, which is in the form of [IN | OUT | INOUT] param_name type.

Where:

IN for input parameters, OUT for output parameters, and INOUT for both input and output

The param_name parameter is the parameter name of the storage function

The type parameter specifies the parameter type of the storage function, which can be any data type of the MySQL database.

Example 1

Use CREATE FUNCTION to create a function that queries the name of a student in the tb_student table. The SQL statement and execution process are as follows:

Mysql > USE test;Database changedmysql > DELIMITER / / mysql > CREATE FUNCTION func_student (id INT (11))-> RETURNS VARCHAR (20)-> COMMENT 'query a student's name'-> BEGIN-> RETURN (SELECT name FROM tb_student WHERE tb_student.id = id);-> END / / Query OK, 0 rows affected (0.10 sec) mysql > DELIMITER

In the above code, the func_student function is created, which has a parameter id of type INT (11) and a return value of type VARCHAR (20). The SELECT statement queries the tb_student table for records whose id field value is equal to the passed parameter id value, and returns the name field value of the record.

Creating a function is the same as creating a stored procedure, you need to change the Terminator of the SQL statement from ";" to "/ /" through the command DELIMITER / /, and finally through the command DELIMITER; change the closing symbol to the default closing symbol in the SQL statement.

If the RETURN statement in the stored function returns a value of a type different from that specified in the function's RETURNS clause, the return value will be forced to the appropriate type. For example, if a function returns an ENUM or SET value, but the RETURN statement returns an integer, for the corresponding ENUM member of the SET member set, the value returned from the function is a string.

Expand reading

Since the view, modification, deletion, and other operations of stored functions and stored procedures are almost the same, we will not explain how to manipulate stored functions in detail.

The syntax for viewing the storage function is as follows:

SHOW FUNCTION STATUS LIKE storage function name; SHOW CREATE FUNCTION storage function name; SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME= storage function name

You can see that the difference between an operation stored function and an operation stored procedure is that PROCEDURE is replaced with FUNCTION. Similarly, the syntax for modifying the storage function is as follows:

ALTER FUNCTION stores the function name [feature.]

The characteristics of stored functions are basically the same as those of stored procedures.

After reading this article, I believe you have a certain understanding of what stored procedures and stored functions refer to in mysql. If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!

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.