实验四存储过程及函数-优快云博客

本文链接：https://blog.youkuaiyun.com/A37644548/article/details/147181165

一、实验目的与要求：

1、掌握存储过程的工作原理、定义及操作方法

2、掌握函数的工作原理、定义及操作方法

3、掌握游标的工作原理、定义及操作方法

二、实验内容：

1. 创建存储过程，用来自动统计给定订单号的订单总金额

CREATE PROCEDURE CalculateOrderTotal(IN order_num INT, OUT total_amount DECIMAL(10,2))
BEGIN
    SELECT SUM(quantity * item_price) INTO total_amount
    FROM orderitems
    WHERE o_num = order_num;
    
    IF total_amount IS NULL THEN
        SET total_amount = 0.00;
    END IF;
END 
-- 调用存储过程
CALL CalculateOrderTotal(50010, @total);

-- 查看结果
SELECT @total AS '订单50010总金额';

2.创建存储过程，自动搜索并添加客户及供货商帐号信息到新建的用户信息表。

①增加用户表信息user

CREATE TABLE user (
    id INT(11) AUTO_INCREMENT NOT NULL,
    u_id INT(11) NOT NULL,
    pwd BLOB NOT NULL,
    remark VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY (u_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

② 创建两个存储过程，分别把客户表的c_id和供货商表s_id的字段自动添加到用户信息表,补充pwd和remark字段。

要求：id字段自动增加，u_id 字段即客户或供货商的编号，pwd字段用AES_ENCRYPT函数加密，密码统一设置为用户编号u_id的值连接123456（如在当前表中u_id为10001，则其密码是10001123456），密钥是'hello'; remark字段内容是‘customer'或'supplier’

添加客户表帐号：

CREATE PROCEDURE AddCustomersToUser()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE customer_id INT;
    DECLARE cur CURSOR FOR SELECT c_id FROM customers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO customer_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO user (u_id, pwd, remark)
        VALUES (
            customer_id,
            AES_ENCRYPT(CONCAT(customer_id, '123456'), 'hello'),
            'customer'
        );
    END LOOP;
    
    CLOSE cur;
END

添加供货商帐号：

CREATE PROCEDURE AddSuppliersToUser()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE supplier_id INT;
    DECLARE cur CURSOR FOR SELECT s_id FROM suppliers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO supplier_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        INSERT INTO user (u_id, pwd, remark)
        VALUES (
            supplier_id,
            AES_ENCRYPT(CONCAT(supplier_id, '123456'), 'hello'),
            'supplier'
        );
    END LOOP;
    
    CLOSE cur;
END

3.创建存储过程或函数来批量修正订单详情表orderitems中的水果价格与水果表fruits中的价格一致。

提示：用游标

CREATE PROCEDURE UpdateOrderItemPrices()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE fruit_id VARCHAR(10);
    DECLARE current_price DECIMAL(8,2);
    
    -- 声明游标，获取所有水果ID和价格
    DECLARE fruit_cursor CURSOR FOR 
        SELECT f_id, f_price FROM fruits;
    
    -- 声明异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 打开游标
    OPEN fruit_cursor;
    
    -- 开始循环处理每一条水果记录
    read_loop: LOOP
        -- 获取当前水果的ID和价格
        FETCH fruit_cursor INTO fruit_id, current_price;
        
        -- 如果没有更多记录则退出循环
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 更新orderitems表中对应水果的价格
        UPDATE orderitems 
        SET item_price = current_price
        WHERE f_id = fruit_id;
        
    END LOOP;
    
    -- 关闭游标
    CLOSE fruit_cursor;
    
    -- 输出完成信息
    SELECT CONCAT('全部更新') AS Message;
END

CALL UpdateOrderItemPrices();

-- 查看更新后的订单详情
SELECT * FROM orderitems;

-- 对比水果表中的价格
SELECT f_id, f_price FROM fruits;