Detailed introduction to mysql stored procedures (code example)

This article brings you a detailed introduction (code example) about mysql stored procedures. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

1. What is a stored procedure?

A collection of one or more MySQL statements saved for later use.

The concept of stored procedures is code encapsulation and reuse at the SQL language level of the database.

(Related recommendations: MySQL Tutorial)

2. Why use stored procedures

1. Encapsulate processing in easy-to-use units, simplifying complex operations

2. Prevent errors and ensure data consistency

3. Simplify the response to changes manage. (Modify the corresponding table name, column name, etc. to modify the code of the corresponding stored procedure. People who use it do not need to know the changes)

4. Improve performance

5. flexible

In general, it is simple, safe and high-performance
Disadvantages:

1. It is more complicated to write than SQL statements

2. Permission problem (may not have permission, generally use stored procedures, do not have permission to create stored procedures)

3. Create stored procedures

CREATE  PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END

Note: Problems entered in the command line

mysql> delimiter //
mysql> CREATE PROCEDURE productpricing()
    -> BEGIN
    -> SELECT Avg(prod_price) AS priceaverage
    -> FROM products;
    -> END //

4. Use stored procedures

The stored procedure is actually a function

CALL productpricing();

4. Delete the stored procedure

    drop procedure productpricing；
    drop procedure if EXISTS productpricing；

5. Use parameters

Generally, the stored procedure does not display the results, but returns the results to the variable you specify
Variable (variable) A specific location in memory used to temporarily store data.

CREATE PROCEDURE productpricing(
    OUT p1 DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
SELECT MIN(prod_price)
INTO p1
FROM products;
SELECT MAX(prod_price)
INTO ph
FROM products;
SELECT avg(prod_price)
INTO pa
FROM products;
END;

The keyword OUT indicates that the corresponding parameter is used to pass a value from the stored procedure (returned to the caller).
MySQL supports IN (passed to stored procedures),
OUT (passed from stored procedures, as used here)
INOUT (passed in and out of stored procedures) type parameters.

Variable name All MySQL variables must start with @.
Call stored procedure

call productpricing(@pricelow,@pricehign,@priceaverage);

Query

SELECT @priceaverage;

SELECT @priceaverage,@pricehign,@pricelow;

Use in and out
Create

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;

Calling

call ordertotal(20005,@total);

Query

select @total;

6. Establishing intelligent stored procedures

All stored procedures used so far are basically encapsulated MySQL simple SELECT statements. Although they are all valid examples of stored procedures, they can do what you can directly use these encapsulated statements (if they can bring anything more, it is to make things more complicated). The power of stored procedures truly becomes apparent only when they include business rules and intelligent processing within them.

   考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么，你需要做下面几件事情：
   1、获得合计（和以前一样）
   2、把营业税有条件的添加到合计
   3、返回合计（带或不带税的）

We enter the following code:

-- Name: ordertotal        //   添加注释
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxtable
--             ototal = order total variable

CREATE     PROCEDURE ordertotal (
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
    
        -- Declare variable for total
        DECLARE total DECIMAL(8,2);     //   声明变量   
        -- Declare tax percentage
        DECLARE taxrate INT DEFAULT 6;
        
        -- Get the order total
        SELECT Sum(item_price*quantity)
        FROM orderitems
        WHERE order_num = onumber
        INTO total;
        
        -- Is this taxable?
        IF taxable THEN
            -- yes,so add taxrate to the total
            SELECT total+(total/100*taxrate) INTO total;
        END IF;
        --  And finally, save to out variable
        SELECT total INTO ototal;
END;

此存储过程有很大的变动。首先，增加了注释（前面放置 --）。在存储过程复杂性增加时，这样做特别重要。  
添加了另外一个参数 taxable，它是一个布尔值（如果要增加税则为真，否则为假）。  
在存储过程体中，用 DECLARE语句定义了两个局部变量。 DECLARE要求指定变量名和数据类型，
它也支持可选的默认值（这个例子中的 taxrate的默认被设置为 6%）。SELECT 语句变，因此其结果存储到 total（局部变量）而不是 ototal。  
IF 语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量 total。

最后，用另一SELECT语句将total（它增加或许不增加营业税）保存到 ototal。  
注意：COMMENT关键字 ，本例子中的存储过程在 CREATE PROCEDURE语句中包含了一个 COMMENT值。  
它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

这显然是一个更高级，功能更强的存储过程。为试验它，请用以下两条语句：  
第一条：

call ordertotal(20005, 0, @total);
SELECT @total;

输出：

+--------+
| @total |
+--------+
|  38.47 |
+--------+

第二条：

call ordertotal(20009, 1，@total);
SELECT @total;

输出：

+--------+
| @total |
+--------+
|  36.21 |
+--------+

BOOLEAN值指定为1 表示真，指定为 0表示假（实际上，非零值都考虑为真，只有 0被视为假）。通过给中间的参数指定 0或1 ，可以有条件地将营业税加到订单合计上。

This example gives the basic usage of MySQL's IF statement. The IF statement also supports ELSEIF and ELSE clauses (the former also uses the THEN clause, the latter does not). We will see other uses of IF (and other flow control statements) in future chapters.

7. Check the stored procedure

To display the CREATE statement used to create a stored procedure

show create PROCEDURE ordertotal;

To obtain detailed information about the stored procedure including when and by whom it was created. List

show procedure status;

There are many tables, use like to filter

show procedure status like 'ordertotal';

The above is the detailed content of Detailed introduction to mysql stored procedures (code example). For more information, please follow other related articles on the PHP Chinese website!