应无所住,而生其心
排名
1
文章
846
粉丝
110
评论
162
net core webapi post传递参数
庸人 : 确实坑哈,我也是下班好了好几次,发现后台传递对象是可以的,但...
百度编辑器自定义模板
庸人 : 我建议换个编辑器,因为现在百度富文本已经停止维护了,用tinymec...
ICP备案 :渝ICP备18016597号-1
网站信息:2018-2025TNBLOG.NET
技术交流:群号656732739
联系我们:contact@tnblog.net
公网安备:50010702506256
欢迎加群交流技术

mysql存储过程与函数

4835人阅读 2022/5/9 19:38 总访问:4889791 评论:0 收藏:0 手机
分类: 数据库

存储过程的介绍

预编译的sql语句,可以放很多sql语句,里边可以写条件,循环,可以把一些逻辑放到存储过程里边处理,比如转账。

优点:


1:减少sql语句长度,本身需要很多条sql才能解决的问题,现在只需要使用存储过程名称代替。
2:提高查询效率,因为存储是预编译的sql语句,存储过程创建的时候会变成二进制,下次在执行的时候不需要重新编译直接执行(*)。
3:封装,把很多逻辑封装起来。
4:安全性。

缺点:


维护困难,有些公司喜欢把一些复杂的逻辑写到存储过程,调试麻烦,出错之后不好找问题。

存储过程的创建,执行,查看,添加

创建存储过程

  1. -- 创建存储过程
  2. create procedure proc_select()
  3. begin
  4. select * from account;
  5. end

注意事务里边每句语句最好加上分号结尾,不然很容易出现语法错误,不像sqlserver对这类的语法要求没有这么严格。

执行存储过程

  1. -- 执行存储过程
  2. call proc_select();

查看存储过程

  1. -- 查看存储过程
  2. select * from information_schema.ROUTINES where routine_schema = 'oa1211'
  3. show create procedure proc_select

删除存储过程

  1. -- 删除存储过程
  2. drop procedure proc_select
  3. drop procedure if exists proc_select

存储过程和MySql编程结合使用

创建一个包含if的存储过程。计算分数等级

  1. -- 创建存储过程
  2. create procedure proc_select2()
  3. begin
  4. declare score int default 58;
  5. declare result varchar(10);
  6. if score>=85 then
  7. set result:='优秀';
  8. elseif score>=60 then
  9. set result:='及格';
  10. else
  11. set result:='不及格';
  12. end if;
  13. select result;
  14. end
  15. call proc_select2();

包含一个输入参数的存储过程。根据参数计算分数等级

  1. create procedure proc_select3(in score int)
  2. begin
  3. declare result varchar(10);
  4. if score>=90 then
  5. set result:='优秀';
  6. elseif score>=60 then
  7. set result:='及格';
  8. else
  9. set result:='不及格';
  10. end if;
  11. select result;
  12. end
  13. call proc_select3(99);

存储过程配合if使用,根据传递的参数不同进行不同的运算

编写一个存储过程。要求三个输入参数,其中两个数参与运算,另外一个参数是运算的法则。
法则包含+,-,。 也就是说如果传递3,6,+。那么存储过程的结果应该是9。如果传递3,6,-那么结果就是
-3。如果传递3,6,那么存储的结果就是18。如果传递的是其他运算法则提示暂不支持该运算。

  1. create procedure proc_sum_type(in number1 int,in number2 int,in type char(1))
  2. begin
  3. declare result int default 0 ;
  4. if type = '+' then
  5. set result = number1+number2;
  6. elseif type = '-' then
  7. set result = number1-number2;
  8. elseif type = '*' then
  9. set result = number1*number2;
  10. else
  11. select '暂不支持该运算';
  12. end if;
  13. select result;
  14. end
  15. call proc_sum_type(6,6,'*');

计算两数之和的存储过程。包含两个输入参数

  1. -- 存储过程计算两数之和
  2. drop procedure proc_sum
  3. create procedure proc_sum(in number1 int,in number2 int)
  4. begin
  5. declare result int;
  6. set result = number1+number2;
  7. select result;
  8. end
  9. call proc_sum(9,6);

计算两数之和的存储过程。包含一个输入输出参数

  1. -- 存储过程计算两数之和。包含一个输出参数
  2. drop procedure proc_sum
  3. create procedure proc_sum_2(in number1 int,in number2 int,out result int)
  4. begin
  5. set result = number1+number2;
  6. end

调用该存储过程:需要定义一个变量接收

  1. set @result = 0;
  2. call proc_sum_2(9,9,@result);
  3. select @result

求平方的存储过程。既是输入参数又是输入参数

  1. create procedure proc_square(inout number int)
  2. begin
  3. set number = number*number;
  4. end

调用

  1. set @result = 9;
  2. call proc_square(@result);
  3. select @result

存储过程配合循环使用,计算1-n之和

  1. -- 求和循环
  2. create procedure pro_total(in n int)
  3. begin
  4. declare i int default 1;
  5. declare total int default 0;
  6. while i<=n do
  7. set total = total+i;
  8. set i = i+1;
  9. end while;
  10. select total;
  11. end
  12. -- 调用存储过程
  13. call pro_total(10);

MySQL存储过程的退出

mysql 存储过程不支持quit, exit或return的方式退出
编写存储过程时,为了业务规则需要,我们可能需要提前退出存储过程。我们可以利用leave label方式模拟实现quit退出的效果应用示例。

MySQL存储过程配合实现实现一个简单的转账

代码如下:故意把验证接收放账户是否存在放到后面是为了针对性测试事务回滚什么的。

  1. -- 存储过程实现简单的转账,三个参数,转入账户,转出账户,金额
  2. create procedure proc_transfer(in myaccount varchar(64),in toaccount varchar(64),in money int)
  3. label:begin
  4. -- 记录查询到的转入账户条数
  5. declare _myaccount_count int default 0;
  6. -- 记录查询到的转入账户条数
  7. declare _toaccount_count int default 0;
  8. -- 记录转入账户的余额
  9. declare _mymoney int default 0;
  10. start transaction;
  11. ## 1: 验证转入账号是否存在
  12. select count(*) into _myaccount_count from account where AccountNumber = myaccount;
  13. if _myaccount_count = 0 then
  14. select '你的账号输入错误,转账失败!';
  15. commit; # 提交事务让事务完整
  16. leave label; # 退出存储过程
  17. end if;
  18. ## 2:查询账户余额是否充足
  19. select MyMoney into _mymoney from account where AccountNumber = myaccount;
  20. if _mymoney<money then
  21. select '你的余额不足,转账失败!';
  22. commit; # 提交事务让事务完整
  23. leave label; # 退出存储过程
  24. end if;
  25. ## 3:开始转账,转账人减去对应的钱
  26. update account set MyMoney = MyMoney-1000 where AccountNumber = myaccount;
  27. ## 4:验证接收放账户是否存在
  28. select count(*) into _toaccount_count from account where AccountNumber = toaccount;
  29. if _toaccount_count = 0 then
  30. select '接收账号输入错误,转账失败!';
  31. rollback; # 提交事务让事务完整
  32. leave label; # 退出存储过程
  33. end if;
  34. ## 5:接收方添加上对应的钱
  35. update account set MyMoney = MyMoney+1000 where AccountNumber = toaccount;
  36. ## 6:其实还可以验证
  37. commit;
  38. select '转账成功!';
  39. end;
  40. -- 调用存储过程
  41. call proc_transfer('小红','小芳',1000);

MySQL函数

求和函数

  1. -- 简单的求和函数
  2. create function fun_sum(number1 int,number2 int)
  3. returns int deterministic
  4. begin
  5. declare sum int default 0;
  6. set sum:=number1+number2;
  7. return sum;
  8. end ;

调用:

  1. select fun_sum(9,6)

根据用户名查询工资的函数。如果在用户表中没有该人则提示需要查询的用户不存在

很简单的和存储过程几乎一样的,外面壳子不一样而已

  1. create function func_salary(usernamep varchar(64))
  2. returns varchar(32) DETERMINISTIC
  3. begin
  4. declare _salary int default 0;
  5. declare _count int default 0;
  6. select count(*) into _count from students where username = usernamep;
  7. if _count=0 then
  8. return '查询的用户不存在';
  9. end if;
  10. select salary into _salary from students where username = usernamep;
  11. return _salary;
  12. end
  13. select func_salary('诸葛亮')

自己实现一个简单的四舍五入函数

只考虑一位小数的情况

第1步:可以先取一下小数后面的第一位的值

  1. -- 分析截取最后一位小数
  2. set @numberTemp = substring(113.67,instr(113.67,'.')+1,1);
  3. select @numberTemp

第2步:可以通过函数先取一下小数后面的第一位的值

  1. create function func_round(numberp varchar(12))
  2. returns varchar(12) deterministic
  3. begin
  4. declare _salary int default 0;
  5. declare _numberTemp varchar(12);
  6. set _numberTemp = substring(numberp,instr(numberp,'.')+1,1);
  7. return _numberTemp;
  8. end;
  9. select func_round(3.64)

赋值使用select into也可以,如下

  1. create function func_round(number varchar(32))
  2. returns int DETERMINISTIC
  3. begin
  4. declare val varchar(32);
  5. select substring(number,instr(number,'.')+1,1) into val;
  6. return val;
  7. end

第3步:还可以这样先判断输出一下应该进一,还是去尾

  1. create function func_round(numberp varchar(12))
  2. returns varchar(12) deterministic
  3. begin
  4. declare _salary int default 0;
  5. declare _numberTemp varchar(12);
  6. set _numberTemp = substring(numberp,instr(numberp,'.')+1,1);
  7. if _numberTemp >5 then
  8. return '应该进一';
  9. else
  10. return '应该去尾';
  11. end if;
  12. return _numberTemp;
  13. end;
  14. select func_round(3.34)

第4步:然后把返回值换一下,把进一去尾具体实现一下就ok


欢迎加群讨论技术,1群:677373950(满了,可以加,但通过不了),2群:656732739

评价

mysql主从复制+读写分离

使用Mysql读写分离和主从复制的原因:*单台MySQL服务器的安全性,高可用性,高并发都是不满足实际的生产需要。介绍:1.MySQ...

mysql控制台插入中文数据报错1366解决

计算机配置: windows系统.数据库:MySQL5.5.如图 : 下图为表结构信息:通过 show full columns from userinfo;查询所得 na...

mysql 游标创建

游标:可以来存储一个结果集,可以通过遍历来访问到每一个数据需求:分表数据。 把一张表的数据根据需求分离,创建不同的表...

mysql 存储过程报错:delimiter

最近刚学sql,学到了存储过程,每当练习的时候就一直出现问题。 现在记录CREATEPROCEDUREproc_out(OUTstuidINT) begin se...

mysql 视图的(增删改 查)

要显示视图的定义,需要在SHOWCREATEVIEW子句之后指定视图的名称, 我们先来创建几张表,完事后在进行演示:--用户信息表...

mysql概述与安装配置

1996年首次发行,对于初学者来说,MySQL很容易上手MySQL数据库MySQL 是开源的,所以你不需要支付额外的费用。MySQL 支持大...

mysql存储引擎和数据类型

一 了解MySQL存储引擎什么是MySQL存储引擎?MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技...

本地mysql重置密码

原理:通过跳过表授权,跳过密码直接在表里更新密码1. 找到my.ini一般在C:\Program Files\MySQL\MySQL Server 5.7\bin12345...

mysql数据库基础

1 数据库和表操作 2 数据操作 3 高级查询 4 mysql管理1 数据库和表操作 数据库 创建:create database 数据库名 [default ...

mysql基础

1.创建、修改和删除 表是数据库存储数据的基本单位、一个表包含若干个字段或记录、表的操作包括创建新表、修改表和删除表。...

EF连接mysql连接mysql数据库操作中文数据乱码解决方法

修改数据库的连接字符器在连接字符器添加charset=gb2312。注意事项:(1)字符编码不支持UTF8。(2)必须使用规定编码。示例:&...

EF连接mysql数据库

注意事项准备工作(1)下载MySQL数据库软件6.6.6版本。 (2)下载MySQL数据库mysql-connector-net-6.6.6两者不一样都不能少。(...

VS2017安装mysql加载驱动

部署安装环境运行MySQL和VS2017软件下载MySQL-for-visualstudio-msiMySQL-connector-net-6.6.6-msi安装版本6.10.5和6.9.9在...

shell脚本强制修改mysql --root密码

话不多说直接拿去撸;PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/binexport PATHread -p &quot...

Navicat 连接mysql时报错代码1405

好久不见我们又见面了今天给大家分享的是Navicat连接mysql时报1405的错 话不多说直接上图片遇到这个错时第一种情况:看一...

mysql未能加载文件或程序集“Ubiety.Dns.Core”或它的某一个依赖项 问题的解决

在VS2019中使用Nuget的方式添加了最新的MySQL包 MySql.Data 8.0.19 ,默认添加了个Ubiety.Dns.Core,不知道有什么用,但是...