mysql stored procedure function

MySQL is a common database management system that has the functions of stored procedures and functions. This article will introduce the concepts, usage, and examples of stored procedures and functions in MySQL.

1. Stored procedure

1. Concept

A stored procedure is a set of precompiled SQL statements that can be saved in the database and executed repeatedly . It is often used to perform complex database operations such as inserting, updating, deleting data, etc. Stored procedures can receive parameters and can return one or more result sets.

2. Create a stored procedure

Use the CREATE PROCEDURE statement to create a stored procedure. The syntax is as follows:

CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name datatype [(size)]) [NOT DETERMINISTIC] [SQL SECURITY {DEFINER | INVOKER}] [COMMENT ‘string’]
BEGIN
-- 存储过程内部语句
END;

Among them, the parameters are optional: IN represents the input parameter , OUT represents the output parameter, INOUT represents both the input parameter and the output parameter. datatype represents the data type, size represents the length, NOT DETERMINISTIC represents determinism, SQL SECURITY represents the access control level, COMMENT represents the comment; between BEGIN and END is the set of SQL statements inside the stored procedure.

For example:

CREATE PROCEDURE select_all_users ()
BEGIN
SELECT * FROM users;
END;

The stored procedure is named select_all_users, has no parameters, and internally executes the SELECT * FROM users statement, that is, queries all data in the users table.

3. Call stored procedures

Call stored procedures using the CALL statement, the syntax is as follows:

CALL procedure_name(param1, param2, ...);

For example:

CALL select_all_users();

This statement The select_all_users stored procedure created earlier will be executed.

4. Delete stored procedures

Use the DROP PROCEDURE statement to delete stored procedures. The syntax is as follows:

DROP PROCEDURE IF EXISTS procedure_name;

For example:

DROP PROCEDURE IF EXISTS select_all_users;

This statement will delete the stored procedure named select_all_users.

5. Stored procedure example

The following is a simple stored procedure example, which receives an input parameter, multiplies the parameter by 2 and outputs:

CREATE PROCEDURE double_num (IN num INT, OUT result INT)
BEGIN
SET result = num * 2;
END;

Call the stored procedure:

CALL double_num(5, @result);
SELECT @result;

The output result is 10.

2. Function

1. Concept

A function is a special stored procedure that returns a value and can receive zero or more Input parameters. Unlike stored procedures, functions cannot execute data definition statements (CREATE, ALTER, DROP) or transaction control statements (COMMIT, ROLLBACK).

2. Create function

Use the CREATE FUNCTION statement to create a function, the syntax is as follows:

CREATE FUNCTION function_name ([parameter_name datatype [(size)]] [, ...]) RETURNS return_datatype
BEGIN
-- 函数内部语句
END;

Among them, parameter_name and datatype represent the name and type of the input parameter, size represents the length; return_datatype represents the data type of the return value; between BEGIN and END is the set of SQL statements inside the stored procedure.

For example:

CREATE FUNCTION double_num (num INT) RETURNS INT
BEGIN
RETURN num * 2;
END;

The function is named double_num, the input parameter is num, the return type is INT, and the num value is multiplied by 2 and returned.

3. Call the function

Call the function using the SELECT statement and query the result:

SELECT double_num(5);

The output result is 10.

4. Delete function

Use the DROP FUNCTION statement to delete the function. The syntax is as follows:

DROP FUNCTION IF EXISTS function_name;

For example:

DROP FUNCTION IF EXISTS double_num;

This statement will The function named double_num will be removed.

5. Function example

The following is a simple function example that finds the factorial of a number:

CREATE FUNCTION factorial (n INT) RETURNS INT
BEGIN
IF n <= 0 THEN
RETURN 1;
ELSE
RETURN n * factorial(n - 1);
END IF;
END;

Call the function:

SELECT factorial(5);

The output result is 120.

3. The difference between stored procedures and functions

The main differences between stored procedures and functions are:

1. Different return types: stored procedures do not need to return values, but Functions need to return values.
2. Different processing methods: stored procedures are usually used to perform some operations, such as insertion, update, deletion, etc.; functions are often used for mathematical calculations and string processing.
3. Different parameter passing methods: stored procedures can use IN, OUT and INOUT parameter passing methods; functions only allow the use of IN parameter passing methods.
4. The calling methods are different: stored procedures are called using the CALL statement; functions can be called in the SELECT, WHERE and HAVING clauses.

4. Summary

This article introduces the concepts, usage and examples of stored procedures and functions in MySQL. Stored procedures and functions are important tools for database management, and they can improve the efficiency and security of the database. In actual development, stored procedures or functions should be selected according to specific needs.

The above is the detailed content of mysql stored procedure function. For more information, please follow other related articles on the PHP Chinese website!