How to return result set in MySQL stored procedure

MySQL stored procedure is a set of predefined SQL statements that can be called and executed when needed. Stored procedures can make code reusable and improve database performance, while also improving development security.

In MySQL, stored procedures can return result sets. In many cases, using stored procedures to return result sets can make the code more concise and clear, and can also improve query performance. This article will introduce how to return a result set in a MySQL stored procedure.

Create a stored procedure with a result set

Before using a stored procedure to return a result set, we need to understand how to create a stored procedure with a result set. Here is an example of creating a simple stored procedure with a result set:

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

In the above example, we created a stored procedure named get_all_users(). When the get_all_users() stored procedure is called, it will return all the data rows in the users data table.

Note that before returning the result set in the stored procedure, we need to define the result set first. There are two ways to define the result set in MySQL:

1. Define the output parameters and return the result set
2. Use the SELECT statement to return the result set

The two methods will be introduced below.

Method 1: Define output parameters and return the result set

To define output parameters in the stored procedure, you can use the OUT and INOUT modifiers. Parameters defined using the OUT modifier indicate that the parameter has one more function than the input parameters when the stored procedure is executed. It will additionally be used to store the result set of the stored procedure.

In the following example, we use the OUT modifier to define a parameter named results:

CREATE PROCEDURE get_all_users_2(OUT results VARCHAR(255))
BEGIN
    SELECT * FROM users;
    INTO results; 
END

In the above example, We use the SELECT INTO statement to save the query results into the results parameter.

The call is as follows:

CALL get_all_users_2(@results);

SELECT @results;

In the above example, we first call the stored procedure get_all_users_2() and store the results in @results in variables. Then, we access the @results variable in the SELECT statement to obtain the result set returned by the stored procedure.

Method 2: Use the SELECT statement to return a result set

Another way to use a stored procedure to return a result set is to use the SELECT statement to Return the result set. This approach is particularly useful when we need to return multiple result sets.

In the following example, we define a stored procedure with two SELECT statements:

CREATE PROCEDURE get_all_users_3()
BEGIN
    SELECT * FROM users WHERE age > 18;
    SELECT * FROM users WHERE age <= 18;
END

In the above example, we use two SELECT statement to return all data rows in the users table whose age is greater than 18 years old and less than or equal to 18 years old.

After calling this stored procedure, we can obtain the row data of each result set by calling the mysql_store_result() and mysql_fetch_row() functions multiple times.

mysql_query("CALL get_all_users_3()");
MYSQL_RES *res = mysql_store_result(&mysql);
MYSQL_ROW row;

while ((row = mysql_fetch_row(res))) {
    printf("%s %d\n", row[1], stoi(row[2]));
}

mysql_next_result(&mysql);

res = mysql_store_result(&mysql);

while ((row = mysql_fetch_row(res))) {
    printf("%s %d\n", row[1], stoi(row[2]));
}

The above code shows how to get the result set by calling the stored procedure in the mysql_query() function, and how to use the mysql_store_result() function and mysql_fetch_row() Function to obtain and process our result set data.

Conclusion

In MySQL, stored procedures can return result sets. We can store the result set of the stored procedure by defining output parameters, or we can directly use the SELECT statement to return the result set in the stored procedure. Either way, query performance and code clarity are better improved.

The above is the detailed content of How to return result set in MySQL stored procedure. For more information, please follow other related articles on the PHP Chinese website!