mysql 1337 游标嵌套_MySQL笔记（六）游标练习

意义不明的几道练习，留着备用。

感觉不好写，而且难以调试。。不知道以后会不会有实际的应用场景。

1、利用游标查找所有女业务员的基本情况

DROP PROCEDURE IF EXISTSshow_female_details;

DELIMITER//

CREATE PROCEDUREshow_female_details()BEGIN

--用于保存 FETCH 结果的变量

DECLARE employee_no VARCHAR(8);DECLARE employee_name VARCHAR(10);DECLARE sex CHAR(1);DECLAREbirthday DATE;DECLARE address VARCHAR(50);DECLARE telephone VARCHAR(20);DECLAREhiredate DATE;DECLARE department VARCHAR(30);DECLARE headship VARCHAR(10);DECLARE salary DECIMAL(8,2);DECLARE cnt INT DEFAULT 0;--循环变量

DECLARE i INT DEFAULT 0;--声明游标

DECLARE employee_details CURSOR FOR SELECT * FROMemployee;OPENemployee_details;--ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration

--employee 表的元组数

SELECT COUNT(*) FROM employee INTO cnt; --如果放在游标声明之前将报错。

WHILE i < cnt DO --意外发现：TAB控制缩进报错，空格缩进不报错

SET i:=i+1;FETCHemployee_detailsINTOemployee_no, employee_name, sex, birthday, address, telephone, hiredate, department, headship, salary;IF sex = 'F' THEN

SELECTemployee_no, employee_name, sex, birthday, address, telephone, hiredate, department, headship, salary;END IF;END WHILE;

END //DELIMITER ;

CALL show_female_details();--imooc 为什么使用存储过程 补充

/

2、利用游标修改orderMaster表中的Ordersum的值

DROP PROCEDURE IF EXISTScal_sum;

DELIMITER//

CREATE PROCEDUREcal_sum()BEGIN

--用于保存 FETCH 结果的变量

DECLARE order_num VARCHAR(12);DECLARE done INT DEFAULT 0;--当抛出 not find 的时候把 done 改成 true

--什么时候抛出 not find 呢？在游标 FETCH 不到数据的时候

DECLARE order_nos CURSOR FOR SELECT order_no FROMorder_master;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;--ERROR 1338 (42000): Cursor declaration after handler declaration

--ERROR 1337 (42000): Variable or condition declaration after cursor or handler declaration

--总之 cursor 的声明位置很苛刻。。。

OPENorder_nos;WHILE (NOTdone) DO--SELECT VERSION();

--SET done:=1; -- 测试专用

FETCH order_nos INTOorder_num;UPDATEorder_masterSET order_sum =(SELECT SUM(quantity*price)FROMorder_detailGROUP BYorder_detail.order_noHAVING order_detail.order_no =order_num

)WHERE order_master.order_no =order_num;END WHILE;END //DELIMITER ;

CALL cal_sum();

/

3、请使用游标和循环语句编写一个存储过程proSearchCustomer，根据客户编号，查询该客户的名称、地址以及所有与该客户有关的销售记录，销售记录按商品分组输出。

DROP PROCEDURE IF EXISTSproSearchCustomer;

DELIMITER//

CREATE PROCEDUREproSearchCustomer(IN customer_no_in varchar(9)

)BEGIN

DECLARE customer_name_temp varchar(40);DECLARE address_temp varchar(40);DECLARE order_no_temp varchar(12);DECLARE product_no_temp varchar(9);DECLARE quantity_temp int(11);DECLARE price_temp decimal(7,2);DECLARE done INT DEFAULT 0;DECLARE sales_records CURSOR FOR

SELECTcustomer_name,

address,

y.order_no,

product_no,

quantity,

priceFROMcustomer xLEFT JOIN order_master y ON x.customer_no =y.customer_noLEFT JOIN order_detail z ON y.order_no =z.order_noWHEREx.customer_no=customer_no_inORDER BYproduct_no;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;OPENsales_records;WHILE (NOTdone) DOFETCHsales_recordsINTOcustomer_name_temp, address_temp, order_no_temp, product_no_temp, quantity_temp, price_temp;SELECTcustomer_name_temp, address_temp, order_no_temp, product_no_temp, quantity_temp, price_temp;END WHILE;END //DELIMITER ;

CALL proSearchCustomer('c2005001');

4、随便放在这儿。。

--第三题

DROP PROCEDURE IF EXISTSgetInfo;

DELIMITER//

CREATE PROCEDUREgetInfo()BEGIN

DECLARE no_ INT;DECLARE name_ VARCHAR(50);DECLARE done INT DEFAULT 0;DECLARE infos CURSOR FOR SELECT id, name FROMcustomer;DECLARE EXIT HANDLER FOR NOT FOUND SET done = 1;OPENinfos;WHILE (NOTdone) DOFETCH infos INTOno_, name_;SELECTno_, name_;END WHILE;END //DELIMITER ;

/

5、印象中在《MYSQL CRASH COURSE》里有提到一张表最多可以有 6 个触发器，现在的版本已经能够在一张表上建立数量不限个触发器：

CREATE TRIGGER ins_transaction BEFORE INSERT ONaccount

FOREACH ROW PRECEDES ins_sum

SET

@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),

@withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);

PRECEDES 和 FOLLOWS 都是书上没提到的东西，规定了触发动作、触发时间都相同的触发器的执行顺序。旧版本不能用哦：

ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'