实验10.1　创建并执行存储过程

实验10.1　创建并执行存储过程

【实验目的】

①掌握创建存储过程的方法。

②掌握执行存储过程的方法。

③掌握游标的使用方法。

【实验内容】

①创建存储过程proc_Qcustomer，通过顾客的cid查询顾客的姓名、城市和折扣。

②执行存储过程proc_Qcustomer，当输入cid为“c002”时，显示这个顾客的姓名、城市和折扣。

③创建存储过程proc_Sumdol，从orders表中查询订单中某一顾客订购的商品总金额。

④执行存储过程proc_Sumdol，查询并显示顾客c001订购商品总金额。

⑤创建存储过程proc_S_Qty，要求根据代理商的名字查询每个代理商为顾客订购各类产品的总数量。

⑥执行存储过程proc_S_Qty，查询代理商Smith代理的所有各类产品的总数量。

⑦创建存储过程proc_ALL_Qty，要求根据产品名，查询所有订购该产品的数量信息，包括：代理商号aid，代理商名字，顾客cid，顾客名字，产品数量qty，产品的价格dollars，结果按产品数量的降序排列。

⑧执行存储过程proc_ALL_Qty，查询产品razor的订购情况。

⑨创建存储过程get_count_by_limit_total_dollar，实现累加订单金额最高的几个订单的订单金额，直到订单金额总和达到给定的值时，返回累加的订单数。

⑩分别执行存储过程get_count_by_limit_total_dollar，查询订单金额总和达到3 000和30 000的累加的订单数。

【实验步骤】

（1）创建存储过程proc_Qcustomer

创建存储过程proc_Qcustomer，通过顾客的cid查询顾客的姓名、城市和折扣。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口中输入如下SQL语句：

USE `sales`;
DROP PROCEDURE IF EXISTS `proc_Qcustomer`;

DELIMITER $$
USE `sales`$$
CREATE PROCEDURE `proc_Qcustomer` (
IN in_cid char(4),
OUT out_cname varchar(30),
OUT out_city varchar(50),
OUT out_discnt float)
BEGIN
SELECT cname, city, discnt into out_cname, out_city, out_discnt FROM customers WHERE cid = in_cid;
END$$
DELIMITER ;

③单击工具栏中的  按钮，执行上面的SQL语句，如图10.1所示。

（2）执行存储过程proc_Qcustomer

执行存储过程proc_Qcustomer，当输入cid为“c002”时，显示这个顾客的姓名、城市和折扣。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口中输入如下SQL语句：

SET @inCid = 'c002';
SET @outCname = '';
SET @outCity = '';
SET @outDiscnt = 0;
CALL `sales`.`proc_Qcustomer`(@inCid, @outCname, @outCity,
 @outDiscnt);
SELECT @outCname, @outCity, @outDiscnt;

③单击工具栏中的 按钮，执行上面的SQL语句，如图10.2所示。

（3）创建存储过程proc_Sumdol

创建存储过程proc_Sumdol，从orders表中查询订单中某一顾客订购的商品总金额。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口中输入如下SQL语句：

USE `sales`;
DROP PROCEDURE IF EXISTS `proc_Sumdol`;

DELIMITER $$
USE `sales`$$
CREATE PROCEDURE `proc_Sumdol` (
IN in_cid char(4),
OUT dol_sum float)
BEGIN
SELECT SUM(dollars) into dol_sum FROM orders WHERE cid = in_cid;
END$$
DELIMITER ;

③单击工具栏中的  按钮，执行上面的SQL语句，如图10.3所示。

（4）执行存储过程proc_Sumdol

执行存储过程proc_Sumdol，查询并显示顾客c001订购商品的总金额。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口中输入如下SQL语句：

SET @in_cid = 'c001';
SET @dol_sum = 0;
CALL `sales`.`proc_Sumdol`(@in_cid, @dol_sum);
SELECT @dol_sum;

③单击工具栏中的  按钮，执行上面的SQL语句，如图10.4所示。

（5）创建存储过程proc_S_Qty

创建存储过程proc_S_Qty，要求根据代理商的名字查询每个代理商为顾客订购各类产品的总数量。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口中输入如下SQL语句：

USE `sales`;
DROP PROCEDURE IF EXISTS `proc_S_Qty`;

DELIMITER $$
USE `sales`$$
CREATE PROCEDURE `proc_S_Qty`(IN in_aname char(30))
BEGIN
    SELECT aname,pname,sum(qty) total
    FROM agents,orders,products WHERE agents.aid=orders.aid
        AND products.pid=orders.pid AND aname=in_aname
        GROUP BY aname,pname;
END$$
DELIMITER ;(https://www.daowen.com)

③单击工具栏中的  按钮，执行上面的SQL语句，如图10.5所示。

（6）执行存储过程proc_S_Qty

执行存储过程proc_S_Qty，要求查询代理商Smith代理的所有各类产品的总数量。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口中输入如下SQL语句：

SET @in_aname = 'Smith';
CALL proc_S_Qty(@in_aname);

③单击工具栏中的  按钮，执行上面的SQL语句，如图10.6所示。

（7）创建存储过程proc_ALL_Qty

创建存储过程proc_ALL_Qty，要求根据产品名，查询所有订购该产品的数量信息，包括：代理商号aid，代理商名字，顾客cid，顾客名字，产品数量qty，产品的价格dollars，结果按产品数量的降序排列。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口中输入如下SQL语句：

USE `sales`;
DROP PROCEDURE IF EXISTS `proc_ALL_Qty`;

DELIMITER $$
USE `sales`$$
CREATE PROCEDURE `proc_ALL_Qty` (IN in_pname char(30))
BEGIN
    SELECT agents.aid,aname,customers.cid,cname,qty,dollars
    FROM agents,customers,products,orders
    WHERE agents.aid=orders.aid AND customers.cid=orders.cid
    AND products.pid=orders.pid AND pname=in_pname ORDER BY qty desc;
END$$
DELIMITER ;

③单击工具栏中的  按钮，执行上面的SQL语句，如图10.7所示。

（8）执行存储过程proc_ALL_Qty

执行存储过程proc_ALL_Qty，要求查询产品razor的订购情况。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口中输入如下SQL语句：

SET @in_pname = 'razor';
CALL proc_ALL_Qty(@in_pname);

③单击工具栏中的  按钮，执行上面的SQL语句，如图10.8所示。

（9）创建存储过程get_count_by_limit_total_dollar

创建存储过程get_count_by_limit_total_dollar，要求实现累加订单金额最高的几个订单的订单金额，直到订单金额总和达到给定的值时，返回累加的订单数。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口中输入如下SQL语句：

USE `sales`;
DROP PROCEDURE IF EXISTS `get_count_by_limit_total_dollar`;
DELIMITER &&
CREATE PROCEDURE get_count_by_limit_total_dollar(IN limit_total_dollar DOUBLE,OUT total_count INT)
BEGIN
DECLARE sum_dollar DOUBLE DEFAULT 0; -- 记录累加的购物金额
DECLARE cursor_dollar DOUBLE DEFAULT 0; -- 记录某一笔订单的购物金额
DECLARE order_count INT DEFAULT 0;  -- 记录循环个数
DECLARE flag INT DEFAULT 0; -- flag变量判断记录是否全部取出，1代表全部取出，0代表还有记录。
DECLARE order_cursor CURSOR FOR SELECT dollars FROM orders ORDER BY dollars DESC;  -- 定义游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1; -- 设置结束条件，当没有记录的时候抛出 NOT FOUND 异常，并设置 flag 等于1
OPEN order_cursor; -- 打开游标
REPEAT
FETCH order_cursor INTO cursor_dollar;  -- 使用游标（从游标中获取数据）
SET sum_dollar = sum_dollar + cursor_dollar;
SET order_count =order_count + 1;
UNTIL sum_dollar>= limit_total_dollar or flag=1
END REPEAT;
CLOSE order_cursor; -- 关闭游标
IF (flag=0) then
SET total_count = order_count;
ELSE
SET total_count = -1;
END IF;
END &&
DELIMITER ;

③单击工具栏中的  按钮，执行上面的SQL语句，如图10.9所示。

（10）执行存储过程get_count_by_limit_total_dollar

分别执行存储过程get_count_by_limit_total_dollar，要求查询订单金额总和达到3 000和30 000的累加的订单数。

①在图标菜单中单击第一个图标 ，新建一个查询窗口。

②在查询窗口中输入如下SQL语句：

CALL get_count_by_limit_total_dollar(3000,@total_count1);
CALL get_count_by_limit_total_dollar(30000,@total_count2);
SELECT @total_count1,@total_count2

③单击工具栏中的  按钮，执行上面的SQL语句，如图10.10所示。