实验四:存储过程及游标
- 一、实验目的与要求
- 二、实验内容
- 1. 创建函数,用来自动统计给定订单号的订单总金额
- 2.创建存储过程,自动搜索并添加客户及供货商帐号信息到新建的用户信息表
- 三、实验小结
- 1.实验中遇到的问题及解决过程
- 2.实验中产生的错误及原因分析
- 3.实验体会和收获
一、实验目的与要求
1、掌握存储过程的工作原理、定义及操作方法
2、掌握函数的工作原理、定义及操作方法
3、掌握游标的工作原理、定义及操作方法
二、实验内容
1. 创建函数,用来自动统计给定订单号的订单总金额
SET GLOBAL log_bin_trust_function_creators=1;
CREATE FUNCTION `sum_price`(s INT)
RETURNS DECIMAL(6,2)
RETURN(SELECT SUM(quantity*item_price)
FROM orderitems WHERE o_num=s);
SELECT sum_price(30008);
运行测试结果截图(输入订单号’30008’测试结果):
2.创建存储过程,自动搜索并添加客户及供货商帐号信息到新建的用户信息表
①增加用户表信息user
#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字段用ENCODE函数加密,密码统一设置为用户编号u_id的值连接123456(如在当前表中u_id为10001,则其密码是10001123456),密钥是’hello’; remark字段内容是‘customer’或’supplier’
添加客户表帐号:
#添加客户表帐号:
CREATE PROCEDURE customer_user()
BEGIN
DECLARE u_id INT;
DECLARE done TINYINT DEFAULT 0;
#DECLARE done INT DEFAULT FALSE;
#DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=true;
DECLARE user_cur CURSOR FOR SELECT c_id FROM customers;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN user_cur;
#开始循环
read_loop:LOOP
FETCH user_cur INTO u_id;
IF done THEN
LEAVE read_loop;
END IF;
INSERT IGNORE INTO USER(u_id,pwd,remark)VALUES(u_id,AES_ENCRYPT('123456', 'hello'),'customer');
END LOOP read_loop;
CLOSE user_cur;
END
运行测试结果截图:
添加供货商帐号:
#添加供货商帐号
CREATE PROCEDURE add_siduser()
BEGIN
#定义游标
DECLARE u_id INT;
DECLARE done TINYINT DEFAULT 0;
DECLARE add_sidcur CURSOR FOR SELECT s_id FROM suppliers;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
#打开游标
OPEN add_sidcur;
read_cur:LOOP
FETCH add_sidcur INTO u_id;
IF done THEN
LEAVE read_cur;
END IF;
INSERT IGNORE INTO USER(u_id,pwd,remark)VALUES(u_id,AES_ENCRYPT('123456', 'hello'),'customer');
END LOOP read_cur;
CLOSE add_sidcur;
END
运行测试结果截图:
三、实验小结
1.实验中遇到的问题及解决过程
遇到的问题:
构造函数的时候报错:
“This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA…”
解决过程:
这是由于开启了bin-log,要求为function指定一个参数。
可以在创建函数前加一行:
set global log_bin_trust_function_creators=TRUE;
2.实验中产生的错误及原因分析
产生的错误:
原因分析:
定义变量必须放在游标之前,而我放在了游标后面,所以导致此错误。
3.实验体会和收获
学会了存储过程、函数、游标的工作原理、定义及操作方法。
通过学习更多数据库功能的综合运用,能够更加便捷地使用数据库系统。同时也了解了更多数据库在实际生活中的应用。