[TOC] ## 存储过程的介绍 预编译的sql语句，可以放很多sql语句，里边可以写条件，循环，可以把一些逻辑放到存储过程里边处理，比如转账。 优点： tn2>1：减少sql语句长度，本身需要很多条sql才能解决的问题，现在只需要使用存储过程名称代替。 2：提高查询效率，因为存储是预编译的sql语句，存储过程创建的时候会变成二进制，下次在执行的时候不需要重新编译直接执行（*****）。 3：封装，把很多逻辑封装起来。 4：安全性。 缺点： tn2>维护困难，有些公司喜欢把一些复杂的逻辑写到存储过程，调试麻烦，出错之后不好找问题。 ## 存储过程的创建，执行，查看，添加 #### 创建存储过程 ```sql -- 创建存储过程 create procedure proc_select() begin select * from account; end ``` tn>注意事务里边每句语句最好加上分号结尾，不然很容易出现语法错误，不像sqlserver对这类的语法要求没有这么严格。 #### 执行存储过程 ``` -- 执行存储过程 call proc_select(); ``` #### 查看存储过程 ```sql -- 查看存储过程 select * from information_schema.ROUTINES where routine_schema = 'oa1211' show create procedure proc_select ``` #### 删除存储过程 ```sql -- 删除存储过程 drop procedure proc_select drop procedure if exists proc_select ``` tn> mysql变量，mysql编程的使用 https://www.tnblog.net/aojiancc2/article/details/7270 ### 存储过程和MySql编程结合使用 #### 创建一个包含if的存储过程。计算分数等级 ``` -- 创建存储过程 create procedure proc_select2() begin declare score int default 58; declare result varchar(10); if score>=85 then set result:='优秀'; elseif score>=60 then set result:='及格'; else set result:='不及格'; end if; select result; end call proc_select2(); ``` #### 包含一个输入参数的存储过程。根据参数计算分数等级 ``` create procedure proc_select3(in score int) begin declare result varchar(10); if score>=90 then set result:='优秀'; elseif score>=60 then set result:='及格'; else set result:='不及格'; end if; select result; end call proc_select3(99); ``` #### 存储过程配合if使用，根据传递的参数不同进行不同的运算 编写一个存储过程。要求三个输入参数，其中两个数参与运算，另外一个参数是运算的法则。 法则包含+,-,。 也就是说如果传递3,6,+。那么存储过程的结果应该是9。如果传递3，6，-那么结果就是 -3。如果传递3，6，那么存储的结果就是18。如果传递的是其他运算法则提示暂不支持该运算。 ``` create procedure proc_sum_type(in number1 int,in number2 int,in type char(1)) begin declare result int default 0 ; if type = '+' then set result = number1+number2; elseif type = '-' then set result = number1-number2; elseif type = '*' then set result = number1*number2; else select '暂不支持该运算'; end if; select result; end call proc_sum_type(6,6,'*'); ``` #### 计算两数之和的存储过程。包含两个输入参数 ``` -- 存储过程计算两数之和 drop procedure proc_sum create procedure proc_sum(in number1 int,in number2 int) begin declare result int; set result = number1+number2; select result; end call proc_sum(9,6); ``` #### 计算两数之和的存储过程。包含一个输入输出参数 ``` -- 存储过程计算两数之和。包含一个输出参数 drop procedure proc_sum create procedure proc_sum_2(in number1 int,in number2 int,out result int) begin set result = number1+number2; end ``` 调用该存储过程：需要定义一个变量接收 ``` set @result = 0; call proc_sum_2(9,9,@result); select @result ``` #### 求平方的存储过程。既是输入参数又是输入参数 ``` create procedure proc_square(inout number int) begin set number = number*number; end ``` 调用 ``` set @result = 9; call proc_square(@result); select @result ``` #### 存储过程配合循环使用，计算1-n之和 ``` -- 求和循环 create procedure pro_total(in n int) begin declare i int default 1; declare total int default 0; while i<=n do set total = total+i; set i = i+1; end while; select total; end -- 调用存储过程 call pro_total(10); ``` #### MySQL存储过程的退出 mysql 存储过程不支持quit, exit或return的方式退出 编写存储过程时，为了业务规则需要，我们可能需要提前退出存储过程。我们可以利用leave label方式模拟实现quit退出的效果应用示例。  #### MySQL存储过程配合实现实现一个简单的转账 代码如下：故意把验证接收放账户是否存在放到后面是为了针对性测试事务回滚什么的。 ``` -- 存储过程实现简单的转账，三个参数，转入账户，转出账户，金额 create procedure proc_transfer(in myaccount varchar(64),in toaccount varchar(64),in money int) label:begin -- 记录查询到的转入账户条数 declare _myaccount_count int default 0; -- 记录查询到的转入账户条数 declare _toaccount_count int default 0; -- 记录转入账户的余额 declare _mymoney int default 0; start transaction; ## 1: 验证转入账号是否存在 select count(*) into _myaccount_count from account where AccountNumber = myaccount; if _myaccount_count = 0 then select '你的账号输入错误，转账失败！'; commit; # 提交事务让事务完整 leave label; # 退出存储过程 end if; ## 2:查询账户余额是否充足 select MyMoney into _mymoney from account where AccountNumber = myaccount; if _mymoney<money then select '你的余额不足，转账失败！'; commit; # 提交事务让事务完整 leave label; # 退出存储过程 end if; ## 3：开始转账，转账人减去对应的钱 update account set MyMoney = MyMoney-1000 where AccountNumber = myaccount; ## 4：验证接收放账户是否存在 select count(*) into _toaccount_count from account where AccountNumber = toaccount; if _toaccount_count = 0 then select '接收账号输入错误，转账失败！'; rollback; # 提交事务让事务完整 leave label; # 退出存储过程 end if; ## 5：接收方添加上对应的钱 update account set MyMoney = MyMoney+1000 where AccountNumber = toaccount; ## 6：其实还可以验证 commit; select '转账成功！'; end; -- 调用存储过程 call proc_transfer('小红','小芳',1000); ``` ## MySQL函数 ### 求和函数 ``` -- 简单的求和函数 create function fun_sum(number1 int,number2 int) returns int deterministic begin declare sum int default 0; set sum:=number1+number2; return sum; end ; ``` 调用： ``` select fun_sum(9,6) ``` ### 根据用户名查询工资的函数。如果在用户表中没有该人则提示需要查询的用户不存在 很简单的和存储过程几乎一样的，外面壳子不一样而已 ``` create function func_salary(usernamep varchar(64)) returns varchar(32) DETERMINISTIC begin declare _salary int default 0; declare _count int default 0; select count(*) into _count from students where username = usernamep; if _count=0 then return '查询的用户不存在'; end if; select salary into _salary from students where username = usernamep; return _salary; end select func_salary('诸葛亮') ``` ### 自己实现一个简单的四舍五入函数 只考虑一位小数的情况 第1步:可以先取一下小数后面的第一位的值 ``` -- 分析截取最后一位小数 set @numberTemp = substring(113.67,instr(113.67,'.')+1,1); select @numberTemp ``` 第2步:可以通过函数先取一下小数后面的第一位的值 ``` create function func_round(numberp varchar(12)) returns varchar(12) deterministic begin declare _salary int default 0; declare _numberTemp varchar(12); set _numberTemp = substring(numberp,instr(numberp,'.')+1,1); return _numberTemp; end; select func_round(3.64) ``` 赋值使用select into也可以,如下 ``` create function func_round(number varchar(32)) returns int DETERMINISTIC begin declare val varchar(32); select substring(number,instr(number,'.')+1,1) into val; return val; end ``` 第3步:还可以这样先判断输出一下应该进一，还是去尾 ``` create function func_round(numberp varchar(12)) returns varchar(12) deterministic begin declare _salary int default 0; declare _numberTemp varchar(12); set _numberTemp = substring(numberp,instr(numberp,'.')+1,1); if _numberTemp >5 then return '应该进一'; else return '应该去尾'; end if; return _numberTemp; end; select func_round(3.34) ``` 第4步:然后把返回值换一下，把进一去尾具体实现一下就ok