MySQL必知必会学习笔记第二十三章使用存储过程

迈不过友情╰ 2022-12-29 12:56 154阅读 0赞

MySQL 5中增加了存储过程的支持。

一个操作需要多条SQL语句才能完成时，可以创建存储过程，其中保存一条或多条SQL语句。

使用存储过程的理由：  
1.通过把处理封装在容易使用的单元中，简化复杂的操作。  
2.所有人都使用同一存储过程代替某复杂操作，可防止错误，保证了所有人使用的代码相同，保证了数据一致性。  
3.简化对变动的管理，如需要更改表名、列名等，只需在存储过程中更改即可，使用它的人甚至不需要知道这些变化。  
4.提高性能，使用存储过程比使用单独的SQL要快。  
5.有一些只能用在单个请求中的特性，存储过程可用它们编写更强更灵活的代码。

存储过程缺陷：  
1.编写比SQL复杂。  
2.可能没有创建存储过程的安全访问权限。很多DBA限制存储过程创建权限，但允许用户使用存储过程。

执行存储过程：

CALL procedureName(parameterList);

创建存储过程：

CREATE PROCEDURE procedureName()
    BEGIN
        存储过程内容
    END;

使用命令行客户端时，默认的MySQL语句分隔符为`;`，在命令行中创建存储过程时，存储过程中的SQL语句的分隔符`;`会变成存储过程创建过程的终止符，从而出现语法错误。解决方法是临时更改语句分隔符：

DELIMITER // # 将分隔符改为//
    
    CREATE PROCEDURE procedureName()
    BEGIN
        存储过程内容
    END//
    
    DELIMITER ;

除了\\，任何字符都可用作语句分隔符。

删除存储过程：

DROP PROCEDURE procedureName;

如果此存储过程不存在，会报错，如不想报错而是用警告代替：

DROP PROCEDURE IF EXISTS procedureName;

以下是出售信息表：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70]  
创建一个存储过程获取上表最低、最高、平均价格：

CREATE PROCEDURE productpricing(
        OUT pl DECIMAL(8,2),
        OUT ph DECIMAL(8,2),
        OUT pa DECIMAL(8,2)
    )
    BEGIN
        SELECT MIN(amount), MAX(amount), AVG(amount)
        INTO pl, ph, pa    # 将三个聚集函数的结果赋值给参数
        FROM payment;
    END;

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）、INOUT(对存储过程传入和传出）三种类型参数。

存储过程的参数允许的数据类型与列允许的数据类型相同。返回类型不允许是记录集，因此不能通过一个参数返回多个行和列。

调用以上存储过程：  
![在这里插入图片描述][20201223180350835.png]  
MySQL中变量以@开头。

获取变量：  
![在这里插入图片描述][20201223180445807.png]  
复杂的存储过程：

-- NAME: ordertotal
    -- Patameters: onumber = order number
    -- taxable = 0 if not taxable, 1 if taxable
    -- 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 pricentage
        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 our variable
        SELECT total INTO ototal;
    END;

上例中，增加了以"-- "开头的注释，MySQL支持三种形式的注释，还支持`#`和`/*...*/`。

上例还用DECLARE语句定义了两个局部变量，要求指定变量名和数据类型，它支持可选的默认值。

上例的IF检查taxable是否为真，还支持ELSEIF（要使用THEN子句）和ELSE（不使用THEN子句）子句。

COMMENT关键字可在`SHOW PROCEDURE STATUS`中显示，此命令还能显示何时、由谁创建等详细信息的存储过程列表，可使用LIKE筛选存储过程名：

SHOW PROCEDURE STATUS LIKE 'pattern';

显示存储过程的创建语句：

SHOW CREATE PROCEDURE procedureName;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3R1czAwMDAw_size_16_color_FFFFFF_t_70]: /images/20221120/8b0061d4595243e284dcef6cce9d4ae3.png
[20201223180350835.png]: /images/20221120/8b2252672af6450f8660e41fb8be6e63.png
[20201223180445807.png]: /images/20221120/abd31ccc3e094e5ca1be1ede942a4f8f.png