数据库存储过程

　　数据库存储过程详解

　　存储过程可以说是测试开发都绕不开的结，作为测试我们在验证某个列表查出来数据对不对时，我们在界面新增一条数据，删除一条数据时，以及修改某个信息时，都可能调用了数据库里边某个存储过程，今天我们一起来接起存储过程的详细面纱。

　　1.存储过程定义

　　存储过程（Stored Procedure）是在大型数据库系统 <https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B3%BB%E7%BB%9F/215176>中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译 <https://baike.baidu.com/item/%E7%BC%96%E8%AF%91/1258343>后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

　　2.存储过程的特点

　　1.能够完成较复杂的判断和运算，同时执行速率更快：对于很简单的sql，存储过程没有什么优势。对于复杂的业务逻辑，因为在存储过程创建的时候，数据库已经对其进行了一次解析和优化。存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通sql快。??

　　2.减少网络流量：存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。如果通过网络发送的SQL多行代码到你的SQL Server中，这会影响网络性能。

　　3.安全：用户无需执行任何直接的语句可以执行存储过程。提高代码安全，防止?SQL注入。

　　4.可维护性：的存储过程有些时候比程序更容易维护，这是因为可以实时更新DB端的存储过程。??有些bug，直接改存储过程里的业务逻辑，就搞定了。

　　5.可扩展性：应用程序和数据库操作分开，独立进行，而不是相互在一起。方便以后的扩展和DBA维护优化。

　　3.存储过程语法

　　3.1创建存储过程的简单语法

　　1.create procedure procedure_name()

　　2.begin     #存储过程开始

　　3..........  #存储过程内容

　　4.End       #存储过程结束

　　3.2创建一个简单的存储过程

　　1.create procedure testa()

　　2.begin

　　3.    select * from ecs_users;

　　4.end

　　3.3调用存储过程

　　call testa();?

　　4.变量定义

　　4.1变量分类

　　存储过程中的变量分为全局变量、局部变量、用户变量。全局变量是指变量在多个begin与end块中都起作用，要放在块之前声明；局部变量是指该变量只在存储过程的begin与end之间起作用，end后变量就失去作用；用户变量是在存储过程外边定义的变量。

　　4.2 变量声明

　　局部变量和全局变量声明语法：

　　DECLARE variable_name datatype [DEFAULT value];

　　DECLARE：声明关键字，一句declare只声明一个变量，变量必须先声明后使用

　　variable_name：变量名

　　datatype：数据类型，变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能制定默认值、字符集和排序规则等；

　　DEFAULT value：默认值，可以缺省

　　用户变量声明语法：

　　Set @variable_name1=0;

　　Set @variable_name2=``;

　　注意：用户变量前边要加@符号。

　　4.3 变量赋值

　　赋值语法：

　　Set a=123;

　　Set b=”zhangsan”;

　　或者：

　　Select  user_name into b from ecs_users where user_id=1;

　　这种赋值方法是将Select  user_name from ecs_users where user_id=1;查出来的值付给变量b。

　　需要返回变量值直接使用select 变量名；

　　实例：

　　CREATE DEFINER=`root`@`localhost` PROCEDURE `testa`()

　　BEGIN

　　DECLARE  b VARCHAR(10) DEFAULT null;

　　set b="Jack";  #声明全局变量

　　BEGIN

　　DECLARE userscount int default 0; #声明局部变量

　　select count(*) into userscount from student;  #给变量赋值

　　select userscount;   #返回变量值

　　END;

　　BEGIN

　　DECLARE  avg_score int DEFAULT 0; # 声明局部变量

　　select avg(score) into avg_score from student st ,sc where st.sno=sc.sno and sname=b;        #给变量赋值，同时使用了全局变量b

　　select avg_score;  #返回变量值

　　END;

　　END

　　运行结果1：

　　运行结果2：

　　5.存储过程参数

　　存储过程在定义时可以设置其参数类型，一共有四种情况：

　　create procedure testa()                           #不传递参数

　　create procedure testa(in 参数名称 参数类型)       #传入参数

　　create procedure testa(out 参数名称 参数类型)      #返回参数

　　create procedure testa(inout 参数名称 参数类型)    #可变参数

　　5.1 不传递参数的存储过程

　　不传参的存储过程调用时直接为call procedure_name();

　　实例：

　　CREATE DEFINER=`root`@`localhost` PROCEDURE `DELETE_users`()

　　BEGIN

　　DELETE from ecs_users where alias="zhangsan";

　　END

　　运行结果：

　　5.2 in类型

　　in类型的参数表示在调用存储过程时必须给该存储过程传入一个值，如果存储过程没有指定参数类型也默认为in类型。IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回。

　　实例：

　　CREATE DEFINER=`root`@`localhost` PROCEDURE `test1`(in username VARCHAR(20))

　　BEGIN

　　DECLARE  avg_score int DEFAULT 0;

　　select avg(score) into avg_score from student st ,sc where st.sno=sc.sno and sname=username;

　　select avg_score;

　　END

　　运行结果：

　　5.3 out类型

　　out类型的参数在调用的存储过程时也必须指定，但只能的是变量，不能是常量，只能用于传出值，不能用于传入值，在调用存储过程中可以改变其值，并返回。

　　实例：

　　CREATE DEFINER=`root`@`localhost` PROCEDURE `test1`(in username VARCHAR(20),out max_score int)

　　BEGIN

　　select max(score) into max_score from student st ,sc where st.sno=sc.sno and sname=username;

　　END

　　运行结果：

　　5.4 inout类型

　　Inout类型参数集合了in和out类型的所有特征，调用时既可以传入参数值，也可修改参数值，还能返回值。调用时传入参数值只能传入变量，不能是常量。

　　实例：

　　CREATE DEFINER=`root`@`localhost` PROCEDURE `test2`(inout username VARCHAR(20),inout min_score int)

　　BEGIN

　　set username="Jack";

　　select min(score) into min_score from student st ,sc where st.sno=sc.sno and sname=username;

　　END

　　运行结果：

　　6.条件语句

　　条件语句主要有if语句和和case语句两种:

　　6.1 if语句

　　If语句分为单条件语句和多条件语句：

　　单条件语句语法：

　　If（  ） then   #如果条件成立执行下边语句

　　……

　　……

　　Else            #如果条件不成立执行下边语句

　　……

　　……

　　End if;         #if语句结束

　　多条件语句基本语法：

　　If（  ） then          #如果满足这个条件执行下边语句

　　……

　　Else if ( )  then      #如果满足这个条件执行下边语句

　　……

　　Else                    #如果以上条件都不满足执行下边语句

　　……

　　End if;                 #if语句结束

　　实例1：（调用存储过程传入参数值如果大于30，则把大于等于该该年龄的学生删除，否则输出等于该参数值的学生）

　　CREATE DEFINER=`root`@`localhost` PROCEDURE `test3`(in age int)

　　BEGIN

　　if (age >30) then

　　DELETE from student where sage>=age;

　　else

　　select * from student where sage=age;

　　end if;

　　END

　　运行结果：

　　入参为9结果如下：（执行了else部分）

　　入参49结果如下：（执行了if部分）

　　实例2：

　　CREATE DEFINER = CURRENT_USER PROCEDURE `test4`(in iname VARCHAR(20))

　　BEGIN

　　DECLARE avgscore int DEFAULT 0;

　　select avg(score) into avgscore from  student st ,sc where st.sno=sc.sno and sname=iname;

　　if (avgscore>80) then

　　update student set type="优秀" where sname=iname;

　　else if (avgscore>60)

　　then

　　update student set type="合格" where sname=iname;

　　else

　　update student set type="不合格" where sname=iname;

　　COMMIT;

　　end if;

　　select type from student where sname=iname;

　　end

　　运行结果

　　6.2 case语句

　　Case语句语法结构：

　　Case variable

　　When 0 then    #当variable值等于0时执行这一条

　　……

　　……

　　When 1 then    #当variable值等于1时执行这一条

　　……

　　……

　　When 2 then    #当variable值等于2时执行这一条

　　……

　　……

　　Else            #当variable值不等于以上任意一条时执行这一条

　　……

　　……

　　End case;     #case语句结束

　　实例：（输入员工姓名，如果查出来性别为0，则在员工性别表种将该员工工号、姓名、性别male插入；如果查出来性别为1，则在员工性别表种将该员工工号、姓名、性别female插入。）

　　CREATE DEFINER=`root`@`localhost` PROCEDURE `test5`(in empname VARCHAR(20))

　　BEGIN

　　DECLARE ssex int DEFAULT 0;

　　select  sex into ssex from emp where ename=empname;

　　case ssex

　　when 0 then

　　INSERT into emp_sex select empno,ename,'male' from emp where ename=empname;

　　when 1 then

　　INSERT into emp_sex select empno,ename,'female' from emp where ename=empname;

　　end case;

　　COMMIT;

　　select * from emp_sex where ename=empname;

　　END

　　运行结果：

　　7.循环语句

　　7.1  while循环语句

　　基本语法如下：

　　While (条件) do    #先判断是否满足循环条件，如果满足执行下边循环体

　　……

　　……

　　End while;

　　实例：现在测试员正在做并发测试需要模拟一千个不用户同时登录系统的操作，但是系统里现在并没有这么多用户，一条一条的新增和插入显然是不合实际的。但使用存储过程这个问题就能轻易解决。存储过程如下：

　　CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_users`()

　　BEGIN

　　DECLARE num int;

　　DECLARE num1 int;

　　DECLARE emails VARCHAR(100) default '';

　　select user_name+1 into num from ecs_users where user_id=(select max(user_id) from ecs_users);

　　set num1=num+100;

　　WHILE num<num1 DO

　　SELECT CONCAT(num,"@qq.com") as em into emails;

　　INSERT INTO ecs_users ( email, user_name, `password`, alias, msn, qq, office_phone, home_phone, mobile_phone, credit_line )

　　VALUES( emails, num, 123456, 'zhangsan', 123456, 123456, 123456, 123456, 123456, 0.00 );

　　set  num=num+1;

　　END WHILE;

　　END

　　运行结果：

　　7.2 repeat循环语句

　　基本语法：

　　Repeat      #先执行下边循环体

　　……

　　……

　　Untill (条件)  #然后判断是否满足条件，满足就结束循环，不满足再次执行上边循环体

　　End repeat;

　　注意：while是先判断是否满足条件，满足条件执行循环体，不满足条件结束循环体。而repeat是先执行循环体，然后判断是否满足条件，满足条件结束循环体，不满足条件再次重复执行循环体，两者恰恰相反。

　　实例：

　　我们把7.1的实例改为repeat格式如下：

　　CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_users`()

　　BEGIN

　　DECLARE num int;

　　DECLARE num1 int;

　　DECLARE emails VARCHAR(100) default '';

　　select user_name+1 into num from ecs_users where user_id=(select max(user_id) from ecs_users);

　　set num1=num+100;

　　Repeat

　　SELECT CONCAT(num,"@qq.com") as em into emails;

　　INSERT INTO ecs_users ( email, user_name, `password`, alias, msn, qq, office_phone, home_phone, mobile_phone, credit_line )

　　VALUES( emails, num, 123456, 'zhangsan', 123456, 123456, 123456, 123456, 123456, 0.00 );

　　set  num=num+1;

　　untill num>=num1

　　END WHILE;

　　END

　　运行结果和7.1中类似。