mysql query stored procedure

MySQL is an open source relational database management system that supports multiple programming languages. When developing applications, we often need to execute some complex SQL query statements. At this time, we can use MySQL stored procedures to complete the work.

A stored procedure is a collection of SQL statements that can accept parameters and return a result set. Compared with executing SQL statements alone, using stored procedures has the following advantages:

1. Simplifies the application code, making the program easier to maintain and update.
2. Stored procedures run faster than executing SQL statements alone.
3. Stored procedures can ensure data consistency and security.

Let’s look at some common examples of MySQL stored procedures.

(1) Create a simple stored procedure

CREATE PROCEDURE get_person(IN id INT)
BEGIN

SELECT * FROM person WHERE id = id;

END;

The above code creates a stored procedure named get_person, with an input parameter id, and returns all the records with the specified id in the person table information. The parameter type can be IN, OUT, or INOUT, representing input, output, and input/output types respectively.

The statement to execute the stored procedure is:

CALL get_person(1);

This statement will return the id# in the person table ## is a record of 1.

(2) Create a stored procedure with output parameters

CREATE PROCEDURE

get_status(IN id INT, OUT status VARCHAR(50))BEGIN

SELECT status FROM order WHERE id = id INTO status;

END;

This stored procedure accepts an input parameter

id, and will order the # corresponding to id in the table ##status The field value is returned to the status variable. The statement to execute the stored procedure is:

CALL get_status(1, @status);

SELECT @status;

This statement will output

order

The status field value of the record with id in the table is 1. (3) Create a stored procedure with a cursor

CREATE PROCEDURE

get_persons_by_age

(IN age INT)BEGIN

DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE pid INT;
DECLARE pname VARCHAR(50);
DECLARE persons CURSOR FOR SELECT id, name FROM person WHERE age = age;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN persons;
repeat_row: LOOP
    FETCH persons INTO pid, pname;
    IF done THEN
        LEAVE repeat_row;
    END IF;
    SELECT pid, pname;
END LOOP;
CLOSE persons;

END;

This stored procedure accepts an input parameter

age

and returns all records in the person table where age is equal to age . This stored procedure uses a cursor to traverse the result set and output the

id

and name field values ​​for each record. The statement to execute the stored procedure is:

CALL get_persons_by_age(20);

This statement will return all

age in the

person table is 20 records, and output their id and name values. In short, using MySQL stored procedures can easily complete some complex database operations and improve the efficiency and security of the application. Developers can design appropriate stored procedures based on actual needs to achieve optimal performance and maintainability.

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