Mysql note-Use of Stored Procedures,

Mysql note-Use of Stored Procedures,

In the past, my understanding and application of mysql was only at the stage of adding, deleting, modifying, and querying. I recently learned mysql-related content and read a book called "MySQL must know". after reading this, I have a certain understanding of the advanced usage of MySQL. The following content is only for Reading Notes.

If any reprint is available, please indicate the source ~

Use stored procedure execution Stored Procedure

CALL productpricing()

Execute the Stored Procedure named productpricing

Create a stored procedure

CREATE PROCEDURE productpricing()BEGINSELECT avg(prod_price) AS priceaverageFROM products;END;

Note: If you use the mysql command line program, ";" is a separator and also exists in the stored procedure; "this will cause a syntax error in the SQL statement used in the stored procedure, the solution is to temporarily change the statement separator:

DELIMITER //....DELIMITER ;

Any character except \ can be used as a statement separator.

Delete stored procedure

DROP PROCEDURE productpring;

Only the name of the stored procedure is provided ()

Use of stored procedure parameters

-- Create procedure ordertotal (IN onumber INT, OUT ototal DECIMAL (8, 2) BEGINSELECT sum (item_price * quantity) FROM orderitemsWHERE order_num = onumberINTO ototal; END; -- CALL ordertotal (234567, @ total) -- display result SELECT @ total

Smart Stored Procedure

The previous stored procedure is only used for understanding and learning. In actual application, the stored procedure is not as simple as above. The following is a complex stored procedure:

-- CREATE a stored procedure named ordertotal -- parameter: onumber-Order Number taxable-whether to perform tax (0, 1 is not required, required) ototal-total returned create procecure ordertotal (IN onumber INT, IN taxable BOOLEAN, OUT ototal DECIMAL (8, 2) COMMENT 'obtain order total, optionally adding tax 'BEGIN -- defines the Temporary Variable total-stores the total queried DECLARE total DECIMAL (8, 2) -- temporary variable taxrate-tax point DECLARE taxrate int default 6; -- get the total query SELECT Sum (item_price * quantity) FROM orderitemsWHERE order_num = onumberINTO total; -- determine whether tax collection is required IF taxable THEN -- yes, add the tax collection part to the total SELECT total + (total/100 * taxrate) INTO total; end if; -- return the final total to SELECT total INTO ototal; END;