数据库-存储过程及游标

---

一、实验目的与要求

1、掌握存储过程的工作原理、定义及操作方法
2、掌握函数的工作原理、定义及操作方法
3、掌握游标的工作原理、定义及操作方法

二、实验内容

1. 创建函数，用来自动统计给定订单号的订单总金额

源码：

SET GLOBAL log_bin_trust_function_creators=1;
CREATE FUNCTION t_price(onum INT) 
RETURNS DECIMAL(8,2)
	RETURN (
	SELECT SUM(quantity*item_price) 
	FROM 
	orderitems 
	WHERE o_num=onum
	);

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

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字段用ENCODE函数加密，密码统一设置为’123456’，密钥是’hello’; remark字段内容是‘customer’或’supplier’
源码：
添加客户表帐号：

CREATE PROCEDURE add_ciduser()
BEGIN
	#定义游标
	DECLARE u_id INT;
	DECLARE done TINYINT DEFAULT 0;
	DECLARE add_cidcur CURSOR FOR SELECT c_id FROM customers;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

	#打开游标
	OPEN add_cidcur;
	read_cur:LOOP
		FETCH add_cidcur 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_cidcur;
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．实验中遇到的问题及解决过程
在创建客户表账号的时候，我之前用的可视化界面是SQLyog，就一直报错一个语法，然后我问了同学他们拿我的代码测试没问题，我就换成Native的可视化界面，结果发现没问题了。

2.实验中产生的错误及原因分析

发生了这样的错误，后来问了同学后发现要这样

在第一次的实验中，把账号和密码一起放进了游标，导致没能达到全自动的要求，
修改的时候，就把账号和密码从游标中取出，放到插入的时候再写即可。
3．实验体会和收获。
首先是理论和实践存在一定的距离。
第二就是在写函数之前，要先开函数功能，
第三就是游标存储过程的使用方法。