数据库实验四：存储过程及游标

一、实验目的与要求

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.实验体会和收获

学会了存储过程、函数、游标的工作原理、定义及操作方法。
通过学习更多数据库功能的综合运用，能够更加便捷地使用数据库系统。同时也了解了更多数据库在实际生活中的应用。