涉及存储过程的问题

存储过程（特定功能的sql语句集）

　　一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中一个重要对象。

1.创建存储过程：

　　create procedure 存储过程名称 ([存储过程参数列表]) [存储过程特性] SQL代码内容

1 例：delimiter  //
2         create procedure proc()
3         begin
4         select * from userinfo;
5       end  //
6       delimiter;

2.创建存储函数：

　　create function 存储函数名称 ([存储过程参数列表]) returns type [存储函数特性] SQL代码内容

1 例：delimiter //
2         create function NameByZip()
3         returns char(50)
4         return (select username from userinfo where userage="23");
5       //
6       delimiter;

3.变量的使用

　　1）定义变量：declare 局部变量名称,...数据类型 [default value];

　　　　例：declare myparam int default 100;

　　2）为变量赋值：set 局部变量名称=expr,var_name=expr,...;

　　　　例：declare var1,var2 int;

　　　　　　set var1=10,var2=10;

4.定义条件和处理程序

　　1）定义条件：declare 条件名称 condition for  sqlstate "xxxx";

　　　　　　　　  declare 条件名称 condition for xxxx;

　　2）定义处理程序：捕获 sqlstate_value: delcare continue handler for sqlstate "xxxx" set @info="no_such_table";

　　　　　　　　　　  捕获 Mysql_error_code: declare continue handler for xxxx set @info="no_such_table";

5.光标的使用

　　1）声明光标：declare 光标名称 cursor for SQL语句;

　　2）打开光标：open 光标名称;

　　3）使用光标：fetch 光标名称 into 字段名,...;

　　4）关闭光标：close 光标名称;

6.流程控制语句

　　if语句：if val is null then select "val is null";　　

　　　　　　else select "val is not null";　　

　　　　　 end if;

　　case语句：case val

　　　　　　　　when 1 then select "val is 1";

　　　　　　　　when 2 then select "val is 2";

　　　　　　　　else select "val is not 1 or 2";

　　　　　　　end case;

　　loop语句：declare id int default 0;

　　　　　　　add_loop:loop;

　　　　　　　set id = id + 1;　　

　　　　　　　　if id>=10 then leave add_loop;

　　　　　　　　end if;

　　　　　　　end loop add_loop;

　　leave语句：退出循环语句

　　iterate语句：重新执行循环

　　repeat语句：declare id int default 0;

　　　　　　　　repeat

　　　　　　　　set id = id + 1;

　　　　　　　　until id >= 10;　　##先执行后判断

　　　　　　　　end repeat;

　　while语句：declare id int default 0;

　　　　　　　while id >=10 do　　##先判断后执行

　　　　　　　set id = id+1;

　　　　　　　end while;

7.调用存储过程和调用存储函数

　　call 存储过程名称（存储过程定义的参数）

　　select 存储函数名称（存储过程定义的参数）

8.查看存储过程和函数

　　show {procedure | function} status like "x%" 语句查看存储过程和函数的状态

　　show create {procedure | function} 存储函数名称　语句查看存储过程和函数的定义

9.修改存储过程和函数

　　alter {procedure | function}存储函数名称 （存储过程特性）

10.删除存储过程和函数

　　drop {procedure | function} [if exists] 存储函数名称

存储过程优缺点：

　　优点：

　　　　1）存储过程因为SQL语句已经预编译过了，因此运行的速度比较快；

　　　　2）存储过程在服务器端运行，减少了客户端的压力。

　　　　3）允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次，类似方法的复用；

　　　　4）减少网络流量，客户端调用存储过程只需要传存储过程名和相关参数即可，与传输SQL语句相比自然数据量少了很多；

　　　　5）增强了使用的安全性，充分利用系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。程序员直接调用存储过程根本不知道表结构是什么，有什么字段，没有直接暴露表名以及字段名给程序员。

　　缺点：

　　　　调试麻烦（至少没有像开发程序那样容易），可移植性不灵活（因为存储过程是依赖于具体的数据库）。

存储过程优化思路：

　　1）尽量利用一些SQL语句来代替一些小循环，例如聚合函数、求平均函数等。

　　2）中间结果存放于临时表，加索引；

　　3）少使用游标。SQL是一个集合语言，对于集合运算具有较高的性能。而cursors是过程运算，比如对一个100万行数据进行查询，游标需要读表100万次，而不是使用游标则只需要少量几次读取。

　　4）事务越短越好，SQL Server支持并发操作，如果事务过多过长或隔离级别过高，都会造成并发阻塞、死锁。导致查询极慢，CPU占用率极低。

　　5）使用try...catch处理异常；

　　6）查询语句尽量不要放在循环内；