mysql uses stored procedures

MySQL is currently the most popular relational database management system. It uses stored procedures to better maintain and manage the execution of SQL statements and programs. Stored procedures are precompiled SQL code blocks that can encapsulate commonly used SQL statements to improve efficiency during multiple executions and reduce runtime resource usage.

Use of stored procedures in MySQL

1. Create stored procedures

Fundamentally speaking, creating a stored procedure is to write a SQL statement block and create it in the system database Save this code block. The following is a specific example:

CREATE PROCEDURE `procedure_name` (`parameter_list`)
BEGIN
    -- SQL 语句块
END

Among them, procedure_name is the name of the stored procedure, paramter_list is the parameter list of the stored procedure, which can include input parameters and output parameters. , or it can contain no parameters.

For example, we create a stored procedure to obtain user information:

CREATE PROCEDURE `get_user_info`(user_id INT)
BEGIN
    SELECT * FROM `user` WHERE `user_id` = user_id;
END

In the SQL statement block of the stored procedure, we can use all SQL statements supported by MySQL, such as SELECT, INSERT, UPDATE , DELETE, etc. You can also use control flow statements such as IF, WHILE, LOOP, etc. to achieve more complex data logic processing.

2. Call the stored procedure

After creating the stored procedure, we can call the stored procedure through the CALL statement and pass parameters to it:

CALL procedure_name(param1, param2, ...)

For example, we can press Call the get_user_info stored procedure defined above in the following way:

CALL `get_user_info`(1);

3. Delete the stored procedure

If over time, a stored procedure is no longer needed, you can Use the DROP PROCEDURE statement to delete it:

DROP PROCEDURE `procedure_name`;

For example:

DROP PROCEDURE `get_user_info`;

4. Stored procedure instance

Let's look at a complete stored procedure instance. Suppose we have a score table that stores students' test scores, and we need to calculate the total score and average score of each student, and arrange them from high to low according to the total score. At this time, you can use a stored procedure to achieve:

CREATE PROCEDURE `calc_stu_score`()
BEGIN
     DECLARE `total_score` INT;
     DECLARE `avg_score` FLOAT;
     DECLARE `stu_id` INT DEFAULT 0;
     DECLARE `stu_name` VARCHAR(255);
     DECLARE `total` INT DEFAULT 0;
    DECLARE `cursor_stu_id` CURSOR FOR SELECT `stu_id` FROM `score` GROUP BY `stu_id`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;

    DROP TEMPORARY TABLE IF EXISTS `tmp_score`;
    CREATE TEMPORARY TABLE `tmp_score` (
        `stu_id` INT NOT NULL,
        `stu_name` VARCHAR(255) NOT NULL,
        `total_score` INT NOT NULL,
        `avg_score` FLOAT NOT NULL,
        PRIMARY KEY (`stu_id`)
    );

    OPEN cursor_stu_id;

    stu_loop: LOOP
        FETCH cursor_stu_id INTO stu_id;
        IF finished = TRUE THEN 
            LEAVE stu_loop;
        END IF;

        SELECT `name` INTO stu_name FROM `student` WHERE `stu_id` = stu_id;

        SELECT SUM(`sorce`), COUNT(*) INTO total_score, total FROM `score` WHERE `stu_id` = stu_id;

        SET avg_score = total_score / NULLIF(total, 0);

        INSERT INTO `tmp_score` (`stu_id`, `stu_name`, `total_score`, `avg_score`) VALUES (stu_id, stu_name, total_score, avg_score);

    END LOOP;

    CLOSE cursor_stu_id;

    SELECT * FROM `tmp_score` ORDER BY `total_score` DESC;

    DROP TEMPORARY TABLE `tmp_score`;

END

In this stored procedure, we first define some variables that need to be used, including total_score represents the total score of a student, avg_score represents the average score of a certain student, stu_id represents the number of a certain student, stu_name represents the name of a certain student, and there is a temporary table tmp_score .

Then we used the DECLARE CURSOR statement to declare a cursor variable cursor_stu_id, which is used to query the student number in the student table. In the loop, we query the score table based on the student number, calculate the student's total score and average score, and save them to a temporary table. Finally, we use the SELECT statement to query the temporary table, sort the total score from high to low, and finally use the DROP TABLE statement to delete the temporary table.

Finally, we can use the CALL statement to call this stored procedure:

CALL `calc_stu_score`();

Summary

Stored procedures are a very powerful tool in MySQL , it can greatly simplify our database operation process and improve the performance and security of the database. In practical applications, we can use stored procedures to complete many complex data processing operations to better meet business needs, improve efficiency, and reduce costs.

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