数据库4——存储过程及游标

学习内容

一、实验目的与要求：
1、掌握存储过程的工作原理、定义及操作方法
2、掌握函数的工作原理、定义及操作方法
3、掌握游标的工作原理、定义及操作方法

二、实验内容：
1.创建存储过程，用来自动统计给定订单号的订单总金额
源码：

创建函数
CREATE PROCEDURE getprice(order_id INT)
BEGIN
    DECLARE total DECIMAL(10, 2);
    SELECT SUM(item_price * quantity) INTO total
    FROM orderitems
    WHERE o_num = order_id;
    SELECT total;
END;

测试函数
CALL getprice(50010);

运行测试结果截图（输入订单号’50010’测试结果）：

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

源码：

CREATE TABLE user (
    id INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
    u_id INT(11) NOT NULL UNIQUE,
    pwd BLOB NOT NULL,
    remark VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
);

② 创建两个存储过程，分别把客户表的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 `customers_users`()
BEGIN
    DECLARE label INT;
    DECLARE u_id INT(11);
    DECLARE user_cur CURSOR FOR SELECT c_id FROM customers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET label = 1;

    SET label = 0;
    OPEN user_cur;

    FETCH NEXT FROM user_cur INTO u_id;

    WHILE(label = 0) DO
        INSERT INTO `user`(u_id, pwd, remark)
        VALUES(u_id, AES_ENCRYPT(CONCAT(u_id, '123456'), 'hello'), 'customer');

        FETCH NEXT FROM user_cur INTO u_id;
    END WHILE;

    CLOSE user_cur;
END

测试procedure:
CALL customers_users();

运行测试结果截图：

添加供货商帐号：

创建存储过程：
CREATE PROCEDURE `suppliers_users`()
BEGIN
    DECLARE label INT;
    DECLARE u_id INT(11);
    DECLARE use_cur CURSOR FOR SELECT s_id FROM suppliers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET label=1;
    
    SET label=0;
    OPEN use_cur;
    FETCH NEXT FROM use_cur INTO u_id;
    WHILE(label=0) DO
        INSERT INTO `user`(u_id,pwd,remark) 
        VALUES (u_id,AES_ENCRYPT(CONCAT(u_id,'123456'),'hello'),'supplier');

        FETCH NEXT FROM use_cur INTO u_id;
    END WHILE;
    CLOSE use_cur;

END

测试代码：
CALL suppliers_users();

运行测试结果截图：

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

创建set_same()函数：
CREATE PROCEDURE `set_same`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE field_value decimal(8,2);
    DECLARE label CHAR(10);
    DECLARE cur CURSOR FOR SELECT f_price,f_id FROM fruits;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

    OPEN cur;
    FETCH NEXT FROM cur INTO field_value,label;
    WHILE(done=FALSE) DO
        UPDATE orderitems SET orderitems.item_price=field_value WHERE orderitems.f_id=label;
        FETCH NEXT FROM cur INTO field_value,label;
    END WHILE;
    CLOSE cur;

END
	
测试代码：
CALL set_same();

运行测试结果截图:
fruits表

orderitems表

三、实验小结
1．实验中遇到的问题及解决过程
(1)问题：在计算订单号的订单总金额时，使用函数计算的订单总价与实际不符。
解决过程：在原本的函数中返回值写的是int，而正确的函数最后应该返回decimal值。
(2)问题：将 fruits 表中的 f_price 更新为 orderitems 表中的 item_price，这与实际的逻辑关系不符。
解决过程：应该在循环中更新 orderitems 表中的 item_price，使其与 fruits 表中的 f_price 保持一致。

2.实验中产生的错误及原因分析
(1)在创建 user 表时，实验要求设置密码为用户编号 u_id 与固定字符串 “123456” 的连接结果（如 u_id=10001 时，密码应为 10001123456）。但在实际 SQL 语句中误用了加法操作 u_id + 123456，导致系统将其当作数值加法处理，计算结果为 112457，并非期望的拼接字符串。
原因分析：SQL 中的 + 是数值加法运算符，不能用于字符串拼接。正确做法是使用 CONCAT(u_id, ‘123456’) 函数将用户编号与字符串连接，从而生成符合格式的初始密码。该问题体现了对 SQL 中数据类型操作的理解不足，尤其在处理字符串与数字混合场景时需格外注意类型转换机制。
(2) 在使用游标进行数据处理时，若未在进入 WHILE 循环之前执行第一次 FETCH，或者未正确设置对 done 标志变量的判断，将导致循环体内部的数据尚未初始化，从而无法进入有效的迭代更新流程。
原因分析：游标的使用依赖于逐行提取数据的机制。若未在 OPEN 游标后立即进行首次 FETCH，就会直接进入循环体，但此时变量尚未被赋值，容易导致逻辑错误或空值操作。同时，必须通过 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; 结合 FETCH 操作设置终止标志 done，才能确保循环能够正常终止。这一问题体现了对游标控制流程的理解不够，应重视游标生命周期的初始化和终止条件设置。

学习感受

通过本次实验，我深入理解并掌握了存储过程、函数和游标的定义及其使用方法。尤其在多表联动操作和批量处理数据时，存储过程展现出强大的自动化能力，提高了操作的效率与准确性。
在实验过程中，我学会了如何使用 CURSOR 结合 HANDLER 控制游标读取流程，同时运用加密函数 AES_ENCRYPT 实现用户密码的加密存储，这对于实际项目中的数据安全管理非常重要。此外，调试过程中也让我意识到细节的重要性，例如数据类型的选择、变量拼写、循环控制条件等，都是保证程序正常运行的关键。
总体而言，本次实验不仅增强了我对 MySQL 高级特性的理解，也提升了我分析问题和独立解决问题的能力，对日后数据库开发和管理工作有很大帮助。