sql 存储过程 游标_简述在MySQL中使用游标的原因

MySQL存储过程与游标 　　1、存储过程简介 　　（1）存储过程：是为以后使用而保存的一条或多条SQL语句或函数。可以将它视为批文件，不过它的作用不仅仅限于批处理；通常被称为函数或子程序。 　　（2）支持存储过程的DBMS：Oracle、SQL Server、MySQL 5及更高版本；而Microsoft Access和SQLite不支持存储过程。 　　（3）存储过程的优点：简单、安全、高性能 把处理封装在一个易用的单中，简化了复杂的操作，实现过程化编程 不用反复建立一系列处理步骤，保证数据的一致性，防止错误 简化对变动的管理，以达到安全性；通过存储过程限制对基础数据的访问，减少了数据讹误的机会 存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能；也即是说存储过程的语句已经保存在数据库里了，语句已经被解析过了，以可执行格式存在。 存储过程可以利用SQL素和特性来编写功能更强大更灵活的代码 　　（4）存储过程的缺陷： 不同DBMS中的存储过程语法有所不同，编写真正的可移植存储过程几乎是不可能的，不过存储过程的自我调用可以相对保持可移植 编写存储过程比编写基本SQL语句复杂，需要更高的技能，更丰富的经验 　　（5）存储过程的创建 　　create procedure procedure_name(in arg1 type1, out argN typeN) begin … end 　　存储过程可以定义输入参数，用in关键字来表示；也可以定义输出参数，用out关键字来表示。存储过程的业务代码都放置在begin和end语句中，并且每天语句的结束法默认是分号；通过declare来声明变量，并且所有的变量声明都要放在代码块中的开头；通过set或者select…into来给变量赋值；此外，也可以通过select语句显示返回的值。整体来说，重要掌握了存储过程的相关语法（可以参考该链接），撰写业务逻辑代码还是不难的。 　　（6）存储过程的执行 　　– MySQL 执行存储过程 CALL procedure_name([param1 [, …]]) — SQL Server 执行存储过程 EXECUTE [ @RETURN STATUS =] procedure_name [[[@param1_name = ] VALUE | [@param2_name = ] @VARIABLE [ OUTPUT ]] [WITH RECOMPILE] — Oracle 执行存储过程 EXECUTE [ @RETURN STATUS =] procedure_name [[[@param1_name = ] VALUE | [@param2_name = ] @VARIABLE [ OUTPUT ]] [WITH RECOMPILE] 　　在MySQL中执行存储过程的方式是：call database_name.procedure_name(arg1,…,argN)，其实也可以不用数据库名，直接用存储过程名，即call procedure_name(arg1,…,argN) 　　（7）代码注释 　　在写存储过程中应该添加适当的注释，这样更容易地理解和更安全地修改代码；增加注释不影响性能，也不存在缺陷。对代码行进行注释的标准方式是在之前放置两个连字符（–），注意了连字符和注释内容之间至少要隔一个空格；所有的DBMS都支持–连字符进行注释。MySQL还支持井号（#）进行注释。 　　（8）触发器 　　触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的insert、update和delete操作（或组合）相关联。在大多数情况下，触发器是很不错的函数，但是它会导致更多的I/O开销。 存储过程只是简单的存储SQL语句，触发器与单个的表相关联。 触发器的内容不能修改，只能替换或者重新创建它。 不同的DBMS，它们的触发器操作时机可能不同，有的是在特定操作执行之前执行，有的是在特定操作执行之后执行。 触发器内的代码数据访问权： insert操作中的所有新数据 update操作中的所有新数据和旧数据 delete操作中删除的数据 触发器的用途 保证数据一致。 基于某个表的变动在其他表上执行活动。 进行额外的验证并根据需要回退数据。 计算列的值或更新时间戳。 一般来说，约束的处理比触发器快，应尽量使用约束。 　　2、游标简介 　　（1）游标：也称为光标，是一个存储在DBMS服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。 　　（2）用途：对检索出来的数据进行行前进或者后退操作，主要用于交互式应用，如用户滚动屏幕上的数据 　　（3）特性： 能够标记游标为只读，使数据能读取，但不能更新和删除 能控制可以执行的定向操作(向前、向后、第一、最后、绝对位置、相对位置等) 能标记某些列为可编辑的，某些列为不可编辑的 规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问 只是DBMS对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化 　　（4）支持游标的DBMS：DB2、MariaDB、MySQL 5、SQL Server、SQLite、Oracle和PostgreSQL，而Microsoft Access不支持 　　（5）游标对基于Web的应用用处不大（ASP、ASP.NET、ColdFusion、PHP、Python、Ruby、JSP等），大多数Web应用开发人员不使用游标 　　（6）使用： 声明游标： DECLARE cursor_name CURSOR FOR SELECT * FROM table_name;  // 还没有检索数据 　　– MySQL游标的声明 DECLARE cursor_name CURSOR FOR select_statement — SQL Server游标的声明 DECLARE cursor_name CURSOR FOR select_statement [FOR [READ ONLY | UPDATE {[co lumn_list]}]] — Oracle游标的声明 DECLARE CORSOR cursor_name IS {select_statement} 打开游标：OPEN cursor_name; // 开始检索数据，即指定游标的SELECT语句被执行，并且查询的结果集被保存在内存里的特定区域。 　　– MySQL打开游标 OPEN cursor_name — SQL Server打开游标 OPEN cursor_name — Oracle打开游标 OPEN cursor_name [param1 [, param2]] 数据：FETCH cursor_name into var1,var2,…,varn； // 当游标cursor_name检索完数据后，只有等到下一次fetch时才会触发结束的标志 　　– MySQL游标数据 FETCH cursor_name INTO var1_name [, var2_name] … — SQL Server游标数据 FETCH NEXT FROM cursor_name [INTO fetch_list] — Oracle游标数据 FETCH cursor_name {INTO : host_var1 [[INDICATOR] : indicator_var1] [, : host_var2 [[INDICATOR] : indicator_var2]] | USING DESCRIPTOR DESCRIPTOR} 关闭游标：CLOSE cursor_name; 　　– MySQL关闭游标，会主动释放资源，所以不需要DEALLOCATE语句 CLOSE cursor_name — SQL Server关闭游标和释放资源 CLOSE cursor_name DEALLOCATE cursor_name — Oracle关闭游标，会主动释放资源，所以不需要DEALLOCATE语句 CLOSE cursor_name 　　3、存储过程代码示例 　　（1）主存储过程：该存储过程的名称为main_procedure_name，并且在该存储过程中调用另外一个存储过程，其名称为child_procedure_name 　　CREATE PROCEDURE `database_name`.`main_procedure_name`() begin declare tmp_id varchar(15); declare done int default false; declare tmp_cursor cursor for select distinct pk_id from database_name.table_name where `some_field` = ‘some_value’; // 根据实际情况添加限定条件 declare continue handler for not found set done = true; open tmp_cursor; pk_id_loop:loop fetch tmp_cursor into tmp_id; if done then leave pk_id_loop; end if; call database_name.child_procedure_name(tmp_id); end loop pk_id_loop; close account_cursor; end 　　（2）子存储过程：该存储过程的名称为child_procedure_name，其伪代码流程如下所示 　　CREATE PROCEDURE `database_name`.`child_procedure_name`(in input_param varchar(15)) begin declare counts int default 0; declare cmp_result int default 0; declare cur_id varchar(20); declare cur_value varchar(30); declare pre_id varchar(20); declare pre_value varchar(30); declare next_id varchar(20); declare next_value varchar(30); declare done int default false; declare cursor_name cursor for select id, value from database_name.table_name where some_field = input_param order by another_field desc; declare continue handler for not found set done = true; select count(*) into counts from database_name.table_name where some_field = input_param; if counts = 1 then open cursor_name; fetch cursor_name into cur_id, cur_value; … close cursor_name; elseif credit_count = 2 then open cursor_name; fetch cursor_name into cur_id, cur_value; fetch cursor_name into pr_id, pre_value; … close cursor_name; elseif credit_count > 2 then open cursor_name; info_loop:loop fetch cursor_name into cur_id, cur_value; fetch cursor_name into pre_id, pre_value; … set next_id = pre_id; set next_value = pre_value; leave info_loop; end loop info_loop; close cursor_name;; end if; end 　　子存储过程的改造，不用区分总记录数为1、2和更多条的情况，以下是改造后的伪代码流程： 　　CREATE PROCEDURE `database_name`.`child_procedure_name`(in input_param varchar(15)) begin declare counts int default 0; declare cmp_result int default 0; declare cur_id varchar(20); declare cur_value varchar(30); declare pre_id varchar(20); declare pre_value varchar(30); declare next_id varchar(20); declare next_value varchar(30); declare flag int default 0; declare done int default false; declare cursor_name cursor for select id, value from database_name.table_name where some_field = input_param order by another_field desc; declare continue handler for not found set done = true; select count(*) into counts from database_name.table_name where some_field = input_param; set flag = counts % 2; open cursor_name; info_loop:loop fetch cursor_name into cur_id, cur_value; fetch cursor_name into pre_id, pre_value; if done then # 只有一条记录数据 # 记录数据为偶数条 # 记录数据为奇数条 leave info_loop; end if; … set next_id = pre_id; set next_value = pre_value; leave info_loop; end loop info_loop; close cursor_name;; end 　　– 注意了，在跳出循环的地方需要进行commit提交操作 　　子存储过程再次改造，每次循环只一条数据，跟上一次保存的数据进行比较，伪代码如下所示： 　　CREATE PROCEDURE `database_name`.`child_procedure_name`(in input_param varchar(15)) begin declare cmp_result int default 0; declare cur_id varchar(20); declare cur_value varchar(30); declare pre_id varchar(20); declare pre_value varchar(30); declare next_id varchar(20); declare next_value varchar(30); declare done int default false; declare cursor_name cursor for select id, value from database_name.table_name where some_field = input_param order by another_field desc; declare continue handler for not found set done = true; open cursor_name; info_loop:loop fetch cursor_name into cur_id, cur_value; if done then … leave info_loop; end if; … set next_id = pre_id; set next_value = pre_value; leave info_loop; end loop info_loop; close cursor_name;; end 　　上述的存储过程代码只是一个业务逻辑思路过程，可供参考，比如可以用来循环处理或者比较相邻两条记录的数据。在业务处理模块中，每次循环取一条数据的逻辑代码要简单些，但是循环的次数较多，具体的循环次数为总记录数+1；而每次循环取两条数据的逻辑代码要复杂些，不过循环的次数是单条数据循环次数的一般，具体的循环次数为总记录数 / 2 + 1，时间效率更高些。