mysql sql stored procedure

MySQL SQL Stored Procedure

MySQL is a commonly used relational database management system, in which stored procedures are one of its important features. A stored procedure is a set of SQL statements that are encapsulated in the database and can be called and executed multiple times. Because stored procedures are compiled and optimized at compile time, you can improve database performance and efficiency.

In MySQL, the syntax of stored procedures is similar to functions and procedures in other programming languages. The following is a simple stored procedure example:

DELIMITER //

CREATE PROCEDURE select_products ()
BEGIN
    SELECT * FROM products;
END //

DELIMITER ;

In this stored procedure, we define a stored procedure named "select_products" to query all data in the "products" table. DELIMITER is used to set a new delimiter because semicolons (;) are used in stored procedures. When defining a stored procedure, we use the BEGIN and END keywords to define the code block, and the actual SQL statement is within the curly brackets.

Stored procedures can use input parameters and output parameters for more flexible processing. The following is an example of a stored procedure with input parameters:

DELIMITER //

CREATE PROCEDURE select_products_by_category (IN category VARCHAR(50))
BEGIN
    SELECT * FROM products WHERE category = category;
END //

DELIMITER ;

In this stored procedure, we use an input parameter IN category, which represents the incoming product category. We then query the corresponding product data based on that category.

Stored procedures also support conditional branch statements and loop statements, such as IF, CASE, WHILE, LOOP, etc. The following is an example of a stored procedure using CASE:

DELIMITER //

CREATE PROCEDURE update_product_price (IN product_id INT, IN price DECIMAL(10,2))
BEGIN
    UPDATE products
    SET price = CASE
        WHEN id = product_id THEN price
        ELSE price
    END
    WHERE id = product_id;
END //

DELIMITER ;

In this stored procedure, we use an input parameter IN product_id to specify the product ID to be updated, and an input parameter IN price to set the new value of the product. price. We then use the CASE statement to update the corresponding price based on the product ID.

The stored procedures in MySQL can also perform exception handling to handle some unexpected situations. The following is a simple exception handling example:

DELIMITER //

CREATE PROCEDURE insert_product (IN product_name VARCHAR(50), IN price DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error: Unable to insert product' AS message;
    END;
    
    START TRANSACTION;
    
    INSERT INTO products (name, price) VALUES (product_name, price);
    
    COMMIT;
    
    SELECT 'Product inserted successfully' AS message;
END //

DELIMITER ;

In this stored procedure, we use the DECLARE EXIT HANDLER FOR SQLEXCEPTION statement to declare exception handling. If an abnormal situation is encountered, we will execute the ROLLBACK statement and output an error message.

In addition to the actual code block, stored procedures also contain some other elements, such as parameters, return values, and comments.

Parameters refer to the data passed in the stored procedure. We can declare parameter types and delivery methods through IN, OUT and INOUT parameters. The return value refers to the execution result of the stored procedure. You can use the RETURN statement to return one value or multiple values. Comments refer to text that explains and illustrates a stored procedure, which can enhance the readability and maintainability of the code.

Summary

The stored procedure in MySQL is an important feature that can improve the performance and efficiency of the database. Stored procedures are used to encapsulate a set of SQL statements and can be called multiple times for execution. Stored procedures can use input and output parameters for more flexible processing. Stored procedures also support conditional branch statements and loop statements, as well as exception handling. In MySQL, the stored procedure needs to set a new delimiter through DELIMITER to distinguish the SQL statements and commands in the stored procedure. Stored procedures also contain other elements such as parameters, return values, and comments. Mastering the use of MySQL stored procedures can effectively improve the efficiency and quality of database management and application development.

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