mysql 9_MySQL笔记9

游标

1)如果你前面看过mysql函数，会发现无法使用返回多行结果的语句。但如果你又确实想要使用时，就需要使用到游标，游标可以帮你选择出某个结果(这样就可以做到返回单个结果)。

2)另外，使用游标也可以轻易的取出在检索出来的行中前进或后退一行或多行的结果。

3)游标可以遍历返回的多行结果。

——————————————————————————————————————————————————————————————————————

4)所以综上可知，游标是一个存储在MySQL服务器上的数据库查询，他不是一条select语句，而是被select语句检索出来的结果集。

5)在存储了游标之后，可以根据需要滚动或浏览其中的数据。

6)游标主要用于交互式应用，用户可以滚动屏幕上的数据，并对数据进行浏览或做出更改。

7)需要特别注意的是：【游标只能用于存储过程和函数】

创建游标

# 使用DECLARE创建游标，并定义相应的select语句，根据需要可以带上where和其他子句

# 如下，定义了名为ordernumbers的游标，使用了检索所有订单号的select语句

DELIMITER //

CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

END //

DELIMITER ;

这个存储过程并没有做太多事情，declare语句用来定义和命名游标，存储过程处理完成后，游标就消失，因为游标局限于存储过程。

打开和关闭游标

DELIMITER //

CREATE PROCEDURE processorders()

BEGIN

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

# 添加的两行程序，分别为打开、关闭游标

OPEN ordernumbers;

CLOSE ordernumbers;#如果不明确关闭游标，则运行到后面end语句，自动关闭

END //

DELIMITER ;

需要注意的是，这个存储过程声明、打开、关闭了一个游标，但对检索出的数据什么也没做。

使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问他的每一行。FETCH指定检索什么数据(所需的列)，检索的数据存储在什么地方。它还向前移动游标中的指针

#使用fetch检索当前order_num到声明的名为o的变量中

#但是这个例子中的fetch是在repeat内，因此反复执行到done为真，为了使它起作用，用一个default 0定义变量done。那么done怎样才能在结束的时候被设置为真呢，下面解释

DELIMITER //

CREATE PROCEDURE processorders()

BEGIN

#声明局部变量

DECLARE done BOOLEAN DEFAULT 0;

DECLARE o INT;

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

#这条语句定义了continue handler，它是在条件出现时被执行的代码。意思如下

#当SQLSTATE '02000'出现时，SET done=1

#SQLSTATE '02000'当repeat没有更多的行供循环而不能被继续的时候，出现这个条件

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

OPEN ordernumbers;

REPEAT

FETCH ordernumbers INTO o;

UNTIL done END REPEAT;

CLOSE ordernumbers;

END //

DELIMITER ;

#如果调用这个存储过程，就会定义几个变量和一个continue handler，定义并打开一个游标，重复读取所有行，然后关闭游标

注意： 1)declare语句的定义存在特定的次序 2)用declare定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标后定义，否则报错 3)所以说定义的顺序是：局部变量->游标->句柄

下面进行一个更进一步的版本，对取出的数据进行某种实际的处理

DELIMITER //

CREATE PROCEDURE processorders()

BEGIN

#声明局部变量

DECLARE done BOOLEAN DEFAULT 0;

DECLARE o INT;

DECLARE t DECIMAL(8,2);

DECLARE ordernumbers CURSOR

FOR

SELECT order_num FROM orders;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

#创建一个新表，用来保存存储过程产生的数据

CREATE TABLE IF NOT EXISTS order_totals(order_num INT, total DECIMAL(8,2));

OPEN ordernumbers;

REPEAT

FETCH ordernumbers INTO o;

CALL order_totals(o,1,t);

INSERT INTO order_totals(order_num, total)

VALUES(o,t);

UNTIL done END REPEAT;

CLOSE ordernumbers;

END //

DELIMITER ;

#前面的程序执行没有报错，但这个语句执行有错误，问题应该是没有创建出来新表，暂时不知道怎么解决

SELECT * FROM ordertotals;

MySQL触发器

不建议使用触发器，只简单介绍

#创建一个触发器+名+插入orders表之后执行+对于插入的每一行执行，into @ee是mysql5之后需要添加的，否则不能创建触发器

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num INTO @ee;

#使用触发器，当插入一条数据时，触发器执行，输出自增的订单号(NEW.order_num)

INSERT INTO orders(order_date,cust_id)

VALUES(NOW(),10001);

#删除触发器

DROP TRIGGER neworder;

管理事务处理

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

1)在MySQL 中只有使用了Innodb数据库引擎的数据库或表才支持事务

2)事务处理可以用来维护数据库的完整性，保证成批的SQL 语句要么全部执行，要么全部不执行

3)事务用来管理 insert,update,delete 语句

使用ROLLBACK撤销MySQL语句

SELECT * FROM customers;# 先检索一下当前表中的数据

# 以下三行选中一块执行

START TRANSACTION;#开始执行一个事务

DELETE FROM customers;#删除表中数据，需要注意的是，如果要删除表中的数据，这个表和其他表不要有关联关系，否则报错，这里删除customers表就报错，换成一个单独的表，可以正常执行

SELECT * FROM customers;#检查一下表中的数据是否被删除，正常是被清空的

ROLLBACK;回退到START TRANSACTION之前的情形

SELECT * FROM customers;#再次检查发现表中又有数据了

COMMINT明确提交

一般MySQL中的提交或保存操作是自动执行的，也就是所谓的“隐含提交”；但是在事务处理块中，提交不会隐含进行，所以需要commit语句明确提交。

#从系统完全删除订单20010，因为涉及到删除两个数据表中的内容，所以使用事务块处理来保证订单不被部分删除，

START TRANSACTION;

DELETE FROM orderitems WHERE order_num=20010;

DELETE FROM orders WHERE order_num=20010;

COMMIT;#最后commit语句只在不出错的情况下提交更改，事务块中的任何一个语句错误，都不会做出更改

SAVEPOINT使用保留点

使用ROLLBACK和COMMIT就可以写入或撤销整个事务处理，但是只能对简单的事务处理才能这么做，更复杂的事务处理可能需要部分提交或回退。

savepoint 是在数据库事务处理中实现“子事务”(subtransaction)，也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。

SAVEPOINT point1; // 声明一个savepoint

ROLLBACK TO point1; // 回滚到point1