How to use MYSQL stored procedures and stored functions

1. What are stored procedures and stored functions

• A stored procedure (Stored Procedure) is a collection of SQL statements that are stored in the database. By encapsulating business logic, stored procedures can improve database execution efficiency and data access security.

• Stored Function (Stored Function) refers to a set of executed SQL statements stored in a database. The difference from stored procedures is that stored functions have a return value.

2. Create a stored procedure

CREATE PROCEDURE procedure_name([IN/OUT] parameter_name data_type)
BEGIN
    SQL Statement;
END;

Suppose we already have an employee table named employee, and now we need to create a stored procedure, you can Query the employee's name and salary based on the employee's job number:

DELIMITER //
CREATE PROCEDURE get_employee_info_by_id(IN emp_id INT)
BEGIN
    SELECT name, salary FROM employee WHERE id = emp_id;
END //
DELIMITER ;

3. Create a storage function

CREATE FUNCTION function_name([IN/OUT] parameter_name data_type) RETURNS data_type
BEGIN
    DECLARE variable_name data_type;
    SQL Statement;
    RETURN variable_name;
END;

Suppose we already have a product table named product, now You need to create a stored function that can query the unit price of the product based on the product number:

DELIMITER //
CREATE FUNCTION get_product_price_by_id(IN product_id INT) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE price DECIMAL(10,2);
    SELECT unit_price INTO price FROM product WHERE id = product_id;
    RETURN price;
END //
DELIMITER ;

4. Use of stored procedures and stored functions

• Calling the stored procedure:

CALL procedure_name([parameter_name]);

• Calling the stored function:

SELECT function_name([parameter_name]);

Using the get_employee_info_by_id stored procedure created above can be called like this :

CALL get_employee_info_by_id(1);

Using the get_product_price_by_id stored function created above can be called like this:

SELECT get_product_price_by_id(1001);

The following are examples of some common stored procedures and stored functions:

5. Stored procedure with if statement

Suppose we already have an employee table named employee, and now we need to create a stored procedure to query the employee’s name and salary. If the salary If it is greater than 5000, add a note to the result: "High Income".

DELIMITER //
CREATE PROCEDURE get_employee_info_with_note()
BEGIN
    SELECT name, salary, IF(salary > 5000, '高收入', '') AS note FROM employee;
END //
DELIMITER ;

6. Stored procedure with loop statement

Assume that we already have a product table named product, and now we need to create a stored procedure to store the product All unit prices are multiplied by 1.1.

DELIMITER //
CREATE PROCEDURE update_all_product_price()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE pid INT;
    DECLARE price DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT id, unit_price FROM product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO pid, price;
        IF done THEN
            LEAVE read_loop;
        END IF;
        UPDATE product SET unit_price = price * 1.1 WHERE id = pid;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

7. Stored procedures with transactions

Suppose we already have an order table named order and an order table named order_item Order details table, now you need to create a stored procedure to insert a record into these two tables.

DELIMITER //
CREATE PROCEDURE insert_order(IN order_id INT, IN item_name VARCHAR(50), IN item_price DECIMAL(10,2), IN item_quantity INT)
BEGIN
    START TRANSACTION;
    INSERT INTO `order`(id) VALUES(order_id);
    SET @last_order_id = LAST_INSERT_ID();
    INSERT INTO order_item(order_id, item_name, item_price, item_quantity) VALUES(@last_order_id, item_name, item_price, item_quantity);
    COMMIT;
END //
DELIMITER ;

8. Storage function with cursor

Suppose we already have a product table named product, and now we need to create a storage function to query the product table the maximum unit price.

DELIMITER //
CREATE FUNCTION get_max_product_price() RETURNS DECIMAL(10,2)
BEGIN
    DECLARE max_price DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT unit_price FROM product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET max_price = 0;
    OPEN cur;
    FETCH cur INTO max_price;
    read_loop: LOOP
        FETCH cur INTO max_price;
        IF max_price IS NULL THEN
            LEAVE read_loop;
        END IF;
        IF max_price > @max_price THEN 
            SET @max_price = max_price;
        END IF;
    END LOOP;
    CLOSE cur;
    RETURN max_price;
END //
DELIMITER ;

9. Advantages of stored procedures and stored functions

• Code can be reused to avoid repeatedly writing SQL statements;

• Flow control statements can be used in stored procedures and stored functions to handle complex logic;

• Database operations can be encapsulated through stored procedures and stored functions to improve efficiency and security.

The above is the detailed content of How to use MYSQL stored procedures and stored functions. For more information, please follow other related articles on the PHP Chinese website!