Home >Database >Mysql Tutorial >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:
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.
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
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;
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
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 theperson 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!