存储过程的介绍
预编译的sql语句,可以放很多sql语句,里边可以写条件,循环,可以把一些逻辑放到存储过程里边处理,比如转账。
优点:
1:减少sql语句长度,本身需要很多条sql才能解决的问题,现在只需要使用存储过程名称代替。
2:提高查询效率,因为存储是预编译的sql语句,存储过程创建的时候会变成二进制,下次在执行的时候不需要重新编译直接执行(*)。
3:封装,把很多逻辑封装起来。
4:安全性。
缺点:
维护困难,有些公司喜欢把一些复杂的逻辑写到存储过程,调试麻烦,出错之后不好找问题。
存储过程的创建,执行,查看,添加
创建存储过程
-- 创建存储过程
create procedure proc_select()
begin
select * from account;
end
注意事务里边每句语句最好加上分号结尾,不然很容易出现语法错误,不像sqlserver对这类的语法要求没有这么严格。
执行存储过程
-- 执行存储过程
call proc_select();
查看存储过程
-- 查看存储过程
select * from information_schema.ROUTINES where routine_schema = 'oa1211'
show create procedure proc_select
删除存储过程
-- 删除存储过程
drop procedure proc_select
drop procedure if exists proc_select
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
欢迎加群讨论技术,1群:677373950(满了,可以加,但通过不了),2群:656732739