Explore the output of MySQL stored procedures

In the use of MySQL database, the concept of stored procedure (Stored Procedure) has gradually become important. A stored procedure is a piece of precompiled SQL code that allows users to customize specific functions and optimize the performance and security of the database. Stored procedures can receive parameters and return values, and the heap operation database provides a lot of convenience. This article mainly discusses the output of MySQL stored procedures.

Output of stored procedures

Output is a very important function of stored procedures. Through the output of the stored procedure, the execution results of the stored procedure can be reflected in the user interface that calls the stored procedure. Otherwise, the actual effect of the stored procedure cannot be seen. In MySQL's stored procedures, there are many types of output content, including strings, numbers, dates, etc. There are several ways to output the stored procedure, which will be introduced separately below.

Use SELECT statement output

In the stored procedure, the simplest output method is to output the result set through the SELECT statement. In a stored procedure, you can use the SELECT statement to obtain the result set. Before the function returns, assign the result set to the output parameter of the stored procedure, and retrieve the result when the stored procedure is called. For the way to use the SELECT statement to output the result set in the stored procedure, the example is as follows:

DELIMITER //
CREATE PROCEDURE proc_out(IN param1 INT, OUT out_param VARCHAR(20))
BEGIN

DECLARE result VARCHAR(100);
--执行查询
SELECT result INTO out_param FROM tbl_name WHERE id = param1;

END //
DELIMITER ;

In this stored procedure, the SELECT statement is used to query the data corresponding to an id in the table tbl_name, and the query results are stored in the result variable. Finally, assign the result to the output parameter out_param. Through this stored procedure, the value of out_param can be obtained in the program interface.

Use SET statement output

In addition to the SELECT statement, you can also use the SET statement to store the results in variables and set the variables as output parameters. This is a simpler way. The implementation method is as follows:

DELIMITER //
CREATE PROCEDURE proc_out_2(IN param1 INT, OUT out_param VARCHAR(20))
BEGIN

DECLARE var VARCHAR(100);
--设置变量
SET var = (SELECT name FROM tbl_name WHERE id = param1);
--将变量赋值给输出参数
SET out_param = var;

END //
DELIMITER;

In this stored procedure, first use the SET statement to store the queried data into the variable var, and then assign var to the output parameter out_param. Finally, just get the value of out_param in the program interface.

Use CURSOR cursor output

In MySQL's stored procedure, you can also use CURSOR cursor to store data, and obtain the output results in the stored procedure through CALL. The specific method is as follows:

DELIMITER //
CREATE PROCEDURE proc_out_3(IN param1 INT, OUT out_param VARCHAR(20))
BEGIN

DECLARE done INT DEFAULT FALSE;
DECLARE var VARCHAR(100);
DECLARE cur1 CURSOR FOR SELECT name FROM tbl_name WHERE id = param1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
--打开游标
OPEN cur1;
get_result: LOOP
    --获取游标中的数据
    FETCH cur1 INTO var;
    --结束标志
    IF done THEN
        LEAVE get_result;
    END IF;
END LOOP get_result;
--关闭游标
CLOSE cur1;
--将游标中的数据赋值给输出参数
SET out_param = var;

END //
DELIMITER;

In this stored procedure, the cursor is used to traverse the found result set, the results are stored in the var variable in turn, and finally the value of var is assigned to the output parameter out_param. Finally, the stored procedure is called in the program interface to obtain out_param.

Summary

Through the above three output methods, you can better master the output and application of MySQL stored procedures. In actual use, different output methods can be selected according to different needs to realize the output function of the stored procedure. Mastering the output method of stored procedures is of great significance for improving the performance and security of MySQL database.

The above is the detailed content of Explore the output of MySQL stored procedures. For more information, please follow other related articles on the PHP Chinese website!