MYSQL的存储过程入门游标

1.存储过程简介

我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。 

一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。 

存储过程通常有以下优点： 

(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。 

(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。 

(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。 

(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。 

(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。 

2. 关于MySQL的存储过程 

存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。 

3.游标

首先先介绍循环的分类： 
(1)WHILE ... END WHILE 
(2)LOOP ... END LOOP 
(3)REPEAT ... END REPEAT 
(4)GOTO 

这里介绍三种标准的循环方式：WHILE循环，LOOP循环以及REPEAT循环。

(1)WHILE ... END WHILE 

 
CREATE PROCEDURE p14() 
BEGIN 
DECLARE v INT; 
SET v = 0; 
WHILE v < 5 DO 
INSERT INTO t VALUES (v); 
SET v = v + 1; 
END WHILE; 
END; 

这是WHILE循环的方式。它跟IF语句相似，使用"SET v = 0;"语句使为了防止一个常见的错误，如果没有初始化，默认变量值为NULL，而NULL和任何值操作结果都为NULL。

(2)REPEAT ... END REPEAT 

  
CREATE PROCEDURE p15 () 
BEGIN 
DECLARE v INT; 
SET v = 0; 
REPEAT 
INSERT INTO t VALUES (v); 
SET v = v + 1; 
UNTIL v >= 5 
END REPEAT; 
END; 
这是REPEAT循环的例子，功能和前面WHILE循环一样。区别在于它在执行后检查结果，而WHILE则是执行前检查。类似于do while语句。注意到UNTIL语句后面没有分号，在这里可以不写分号，当然你加上额外的分号更好。 

(3)LOOP ... END LOOP

 
CREATE PROCEDURE p16 () 
BEGIN 
DECLARE v INT; 
SET v = 0; 
loop_label: LOOP 
INSERT INTO t VALUES (v); 
SET v = v + 1; 
IF v >= 5 THEN 
LEAVE loop_label; 
END IF; 
END LOOP; 
END; 

 
以上是LOOP循环的例子。LOOP循环不需要初始条件，这点和WHILE循环相似，同时它又和REPEAT循环一样也不需要结束条件。 
ITERATE 迭代 
如果目标是ITERATE（迭代）语句的话，就必须用到LEAVE语句 

 
CREATE PROCEDURE p20 () 
BEGIN 
DECLARE v INT; 
SET v = 0; 
loop_label: LOOP 
IF v = 3 THEN 
SET v = v + 1; 
ITERATE loop_label; 
END IF; 
INSERT INTO t VALUES (v); 
SET v = v + 1; 
IF v >= 5 THEN 
LEAVE loop_label; 
END IF; 
END LOOP; 
END; 

4.存储过程和游标简单应用

BEGIN
#Routine body goes here...

/*定义游标*/
 DECLARE _mark bigint(20) default 0 ;

/*游标循环到末尾时给定义的常量赋值*/
 DECLARE _website_id CURSOR FOR
 SELECT @website_id :=website_id FROM t_website WHERE istatus=5;
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _mark = -1;//定义一个循环结束的值

/*开启游标*/
 OPEN _website_id;

/*游标赋值*/
 FETCH _website_id INTO _mark;

/* 循环体 */
 WHILE (_mark!=-1) DO
  
 SELECT @major_event_count :=COUNT(major_event_id) FROM t_major_event WHERE website_id=_mark AND istatus=istatus_pr AND create_time BETWEEN begin_time_pr AND end_time_pr;
 SELECT @illegal_event_count :=COUNT(illegal_event_id) FROM t_illegal_event WHERE illegal_website_id=_mark AND istatus>=istatus_pa AND create_time BETWEEN begin_time_pr AND end_time_pr;
 SELECT @task_website_count :=COUNT(id) FROM t_task_website WHERE website_id=_mark AND task_status=istatus_pb AND create_time BETWEEN begin_time_pr AND end_time_pr;
 SELECT @social_report_count :=COUNT(social_report_id) FROM t_social_report WHERE website_id=_mark AND istatus=istatus_pc AND create_time BETWEEN begin_time_pr AND end_time_pr;
 SELECT @web_rewards_punishment_cout :=COUNT(rewards_punishment_id) FROM t_web_rewards_punishment WHERE website_id=_mark AND istatus=istatus_pd  AND create_time BETWEEN begin_time_pr AND end_time_pr;
 SELECT @web_rewards_punishment_reward :=COUNT(rewards_punishment_id) FROM t_web_rewards_punishment WHERE website_id=_mark AND istatus=istatus_pd AND nature=nature_pa AND create_time BETWEEN begin_time_pr AND end_time_pr;
 SELECT @web_rewards_punishment_punish :=COUNT(rewards_punishment_id) FROM t_web_rewards_punishment WHERE website_id=_mark AND istatus=istatus_pd AND nature=nature_pb AND create_time BETWEEN begin_time_pr AND end_time_pr;
 INSERT INTO t_web_monthly_report (event_report_count,Illegal_handle_count,task_finish_count,social_report_count,rewards_punishment_count,website_id,create_time) 
VALUES(@major_event_count ,@illegal_event_count,@task_website_count ,@social_report_count , @web_rewards_punishment_cout ,_mark,NOW() );

/*读取下一行的数据*/
FETCH _website_id INTO _mark;

/*结束循环*/
END WHILE;

/*关闭游标*/
CLOSE _website_id;
END