mysql存储过程游标和while循环 mysql存储过程游标循环性能

1. 存储过程

迄今为止，大多数SQL语句都是针对一个或多个表的单条语句。有一些操作会有一个完整的操作需要多条语句才能完成。

存储过程简单来说就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

 

存储过程的优点：

通过把处理封装在容易使用的单元中，简化复杂的操作；由于不要求反复建立一系列处理步骤，保证了数据的完整性，防止错误的发生可能；

简化对变动的管理，安全性；提高性能；存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

即简单、安全、高性能。

存储过程的缺点：

比较复杂，需要更高的技能；可能没有创建存储过程的安全访问权限，许多数据库管理员限制存储过程的创建权限。

 

执行存储过程：

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。

　　CALL productpricing( @pricelow, @pricehigh, @priceaverage);

其中，执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

存储过程可以显示结果，也可以不显示结果。

 

创建存储过程：

CREATE PROCEDURE productpricing()
　　BEGIN
　　　　SELECT AVG(prod_price) AS priceaverage
　　　　FROM products;
　　END;

此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义。

如果存储过程接收参数，它们将在()中列举出来。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句。

在MySQL处理这段代码时，它创建一个新的存储过程productpricing，没有返回数据，因为这段代码并未调用存储过程，这里只是为以后使用而创建它。

 

由上面看出，存储过程中出现了多个;，如果使用mysql命令行，命令行的语句分隔符也是;，需要一种方法来避免出现错误。

DELIMITER //
　　CREATE PROCEDURE productpricing()
　　BEGIN
　　　　SELECT AVG(prod_price) AS priceaverage　　
　　　　FROM products;
　　END //
　　DELIMITER;

DELIMITER告诉了1命令行使用程序使用//作为新的语句结束分隔符。后一个DELIMITER后接 ; 表示恢复为原来的分隔符。

然后可以CALL这个存储过程：

　　CALL productpricing();

执行刚创建的存储过程并显示返回的结果。因为存储过程实际上是一种函数，所以存储过程名后需要有()符号，即使无参数也要有。

 

删除存储过程：

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。删除命令从服务器中删除存储过程。

删除存储过程可用DROP PROCEDURE：

　　DROP PROCEDURE productpricing;

这条语句删除刚创建的存储过程。这是不用加上()。

 

查看所有的存储过程：

show procedure status;

查看存储过程结构：

show create procedure procname;

 

存储过程使用参数：

变量variable：内存中一个特定的位置，用来临时存储数据。

CREATE PROCEDURE productpricing(
　　　　OUT pl DECIMAL(8,2),
　　　　OUT ph DECIMAL(8,2),
　　　　OUT pa DECIMAL(8,2)
　　)
　　BEGIN
　　　　SELECT MIN(prod_price)
　　　　INTO pl
　　　　FROM products;
　　　　SELECT MAX(prod_price)
　　　　INTO ph
　　　　FROM products;
　　　　SELECT AVG(prod_price)
　　　　INTO pa
　　　　FROM products;
　　END;

此存储过程接受3个参数。每个参数必须具有指定的类型。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数。

存储过程的代码位于BEGIN和END语句内，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量中（通过INTO关键字）。

为了调用此修改的存储过程，必须指定3个变量名：

　　CALL productpricing(@pricelow, @pricehigh, @priceaverage);

由于此存储过程要求3个参数，因此必须正好传递3个参数，不多也不少。这个例子中是存储过程将保存结果的3个变量的名字。

在调用时，这条语句不显示任何数据，它返回以后可以显示（或在其它处理中使用）的变量。

　　SELECT @priceaverage;

　　SELECT @pricehigh, @pricelow, @priceaverage;

 

变量名：所有MySQL变量都必须以@开始。
 
　　CREATE PROCEDURE ordertotal(
　　　　IN onumber INT,
　　　　OUT ototal DECIMAL(8,2)
　　)
　　BEGIN
　　　　SELECT SUM(item_price*quantity)　　
　　　　FROM orderitems
　　　　WHERE order_num = onumber
　　　　INTO ototal;
　　END;
 
　　CALL ordertotal(20005, @total);

必须传递给这个存储过程两个参数。

 

使用智能存储：

-- Name: ordertotal
　　--
　　CREATE PROCEDURE ordertotal(
　　　　IN onumber INT,
　　　　IN taxable BOOLEAN,
　　　　OUT ototal DECIMAL(8,2)
　　)COMMENT 'Obtain order total, optionally adding tax'
　　BEGIN
　　　　DECLARE total DECIMAL(8,2);
　　　　DECLARE taxrate INT DEFAULT 6;
　　　　SELECT SUM(item_price*quantity)
　　　　FROM orderitems
　　　　WHERE order_num = onumber
　　　　INTO total;
　　　　
　　　　IF taxable THEN
　　　　　　SELECT total+(total/100*taxrate) INTO total;
　　　　END IF;
　　　　
　　　　SELECT total INTO ototal;
　　END;

此存储过程：增加了注释（前面放置--）。用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值，用DEFAULT跟着类型后面。

IF语句检查条件是否为真，如果为真怎么做，否则怎么做。

COMMENT关键字：其将在SHOW PROCEDURE STATUS的结果中显示。

IF语句：IF语句还支持ELSEIF和ELSE子句。

 

检查存储过程：

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：

　　SHOW CREATE PROCEDURE ordertotal;

获得了包括何时、由谁创建等详细信息的存储过程列表。

 

 

2. 使用游标

游标cursor：是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

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

 

只能用于存储过程：MySQL游标只能用于存储过程（和函数）。

 

使用游标：

在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。

一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。

对于填有数据的游标，根据需要取出（检索）各行。

在结束游标使用时，必须关闭游标。

 

创建游标：

游标用DECLARE语句创建。DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其它子句。

CREATE PROCEDURE processorders()
　　BEGIN
　　　　DECLARE ordernumbers CURSOR
　　　　FOR
　　　　SELECT order_num FROM orders;
　　END;

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

 

打开和关闭游标：

游标用OPEN CURSOR语句来打开：

　　OPEN ordernumbers;

在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。

 

　　CLOSE ordernumbers;

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。

在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。

隐含关闭：如果不明确关闭游标，MySQL将会在到达END语句时自动关闭它。

 

使用游标数据：

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部指针，使下一条FETCH语句检索下一行（不重复读取同一行）。

 

循环检索数据，从第一行到最后一行：

CREATE PROCEDURE processorders()
　　BEGIN
　　　　DECLARE done BOOLEAN DEFAULT 0;
　　　　DECLARE o INT;
　　　　DECLARE ordernumbers CURSOR
　　　　FOR
　　　　SELECT order_num FROM orders;
　　　　DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
 
　　　　OPEN ordernumbers;
　　　　
　　　　REPEAT
　　　　　　FETCH ordernumbers INTO o;
　　　　UNTIL done END REPEAT;
　　　　CLOSE ordernumbers;
　　END;

REPEAT重复到条件为真（UNTIL done END REPEAT），此处的done为BOOLEAN条件，当done为真时打破重复。

DECLARE CONTINUE HANDLER声明了done设为1时的条件。CONTINUE HANDLER是在条件出现时被执行的代码。

 

 

3. 触发器

MySQL语句在需要时被执行，存储过程也是如此。但是，有时会想要某条语句（或某些语句）在事件发生时自动执行。

触发器：是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）：

　　DELETE；INSERT；UPDATE。

其它MySQL语句不支持触发器。

 

创建触发器：

在创建触发器时，需要给出4条信息：

唯一的触发器名；触发器关联的表；触发器应该响应的活动（DELETE、INSERT或UPDATE）；触发器何时执行（处理之前或之后）。

触发器名必须在每个表中唯一，但不是在每个数据库中唯一。这表示同一数据库中的两个表可具有相同名字的触发器。

触发器用CREATE TRIGGER语句创建：

　　CREATE TRIGGER newproduct AFTER INSERT ON products

　　FOR EACH ROW SELECT 'Product added';

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。

 

只有表才支持触发器，视图不支持（临时表也不支持）。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条SELECT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以，如果需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

 

触发器失败：如果BEFORE触发器失败，则MySQL将不执行请求的操作。如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器。

 

删除触发器：

DROP TRIGGER语句：

DROP TRIGGER newproduct;

触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建。

 

使用触发器：

INSERT触发器：在INSERT语句执行之前或之后执行。

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；

　　在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；

　　对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

　　

　　CREATE TRIGGER neworder AFTER INSERT ON orders

　　FOR EACH ROW SELECT NEW.order_num;

此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。FOR EACH ROW对于每一行，即每插入一行到表orders之后就执行SELECT NEW.order_num语句，这里的NEW指的是orders表。

 

 

DELETE触发器：

DELETE触发器在DELETE语句执行之前或之后执行。

在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行；

　　

　　CREATE TRIGGER neworder AFTER DELETE ON orders

　　FOR EACH ROW SELECT OLD.order_num;

 

UPDATE触发器：

CREATE TRIGGER neworder AFTER UPDATE ON order FOR EACH ROW
　　BEGIN
　　　　...
　　END;