Let’s talk about the return value of mysql stored procedure

In the MySQL database, a stored procedure is a program composed of one or more SQL statements. These programs are compiled and stored in the database for reuse. Stored procedures can accept parameters and return values. In this article, we will focus on the return value of mysql stored procedure.

The return value of MySQL stored procedure can be used to determine the success or failure of program execution, or to return the results of program execution to the caller. In MySQL, there are three types of return values ​​from stored procedures: integers, floating point numbers, and strings.

Return value of integer type
In mysql, you can use the RETURN statement to return a return value of integer type. The return value of integer type can be any integer value, such as 0, 1, 2, etc. If the stored procedure executes successfully, 0 is returned; if the stored procedure fails, a non-zero integer value is returned. If you want to return a custom integer value, you can use the RETURN statement to specify the value in the stored procedure.

The following is an example of a stored procedure that returns an integer type return value:

DELIMITER $$
CREATE PROCEDURE test()
BEGIN
DECLARE res INT;
SELECT COUNT(*) INTO res FROM users;
IF res>0 THEN
SELECT res;
ELSE
SELECT 0;
END IF;
RETURN 1;
END $$
DELIMITER ;

In the above example, the stored procedure test counts the number of records in the users table and saves the results to the res variable. If res is greater than 0, output the result, otherwise output 0. After the stored procedure is executed, the return value is 1.

Return value of floating point type
The stored procedure in mysql also supports return value of floating point type. To return a floating-point value, use the RETURN statement. The following is an example of a stored procedure that returns a floating-point return value:

DELIMITER $$
CREATE PROCEDURE test2()
BEGIN
DECLARE res FLOAT;
SELECT SUM(amount) INTO res FROM orders;
IF res>0 THEN
SELECT res;
ELSE
SELECT 0;
END IF;
RETURN 1.0;
END$$
DELIMITER ;

In the above example, the stored procedure test2 calculates the total amount of all orders in the orders table and saves the result to the res variable. If res is greater than 0, output the result, otherwise output 0. After the stored procedure is executed, the return value is 1.0.

String type return value
The stored procedure in mysql also supports string type return value. To return a string value, use the RETURN statement. The following is an example of a stored procedure that returns a string type return value:

DELIMITER $$
CREATE PROCEDURE test3()
BEGIN
DECLARE res VARCHAR(100);
SELECT name INTO res FROM users WHERE id=1;
IF res IS NOT NULL THEN
SELECT res;
ELSE
SELECT 'unknown';
END IF;
RETURN 'Hello, World ';
END$$
DELIMITER ;

In the above example, the stored procedure test3 obtains the name of the user with id=1 from the users table and saves the result to the res variable. If res is not empty, the result is output, otherwise "unknown" is output. After the stored procedure is executed, the return value is "Hello, World".

Summary
The return value of MySQL stored procedure can be used to determine the success or failure of program execution, or to return the results of program execution to the caller. In MySQL, there are three types of return values ​​from stored procedures: integers, floating point numbers, and strings. To return a return value, use the RETURN statement and specify the appropriate value.

The above is a detailed introduction to the return value of mysql stored procedure. Stored procedures can improve the execution efficiency of SQL statements and code reuse rate, so learning stored procedures is very helpful to improve the efficiency of database development.

The above is the detailed content of Let’s talk about the return value of mysql stored procedure. For more information, please follow other related articles on the PHP Chinese website!