MySQL存储过程的概念与用法实例
作者:吕维尧 发布时间:2024-01-20 07:45:30
概念
MySQL5.0版本开始支持存储过程,存储过程就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法,存储过就是数据库SQL与层层面的代码封装与重用
特性
1.有输入输出参数,可以声明变量,有if/else/case/while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能
2.函数的普通特性:模块化,封装,代码复用
3.速度快,只有首次执行需要经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤
存储过程通常有以下优点
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
(4).存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
格式
创建存储过程
-- 创建存储过程
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
-- 调用存储过程
call proc01;
定义变量
局部变量
用户自定义,在begin/end块中有效
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default 'aaa'; -- 声明/定义变量
set var_naem01 = 'zhangsan'; -- 给变量赋值
select var_name01; -- 输出变量的值
end $$
dilimiter ;
call proc02();
用户变量
用户自定义,当前会话(连接)有效.类比java的成员变量
delimiter $$
create procedure proc04()
begin
set @var_name01 = 'beijing';
select @var_name01;
end $$
dilimiter ;
call proc04();
select @var_name01; -- 外部也是可以使用用户变量
系统变量
1.系统变量有分为全局变量与会话变量
2.全局变量在MYSQL启动的时候由服务器自动将他的初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改
3.会话变量在每次建立一个新的连接的时候,由MYSQL来初始化,MYSQL会将当前所有全局变量的值复制一份,来作为会话变量
4.也就是说,如果在建立会话以后,没有手动更改过会话变量与全局变量的值,那所有这些变量的值都是一样的
5.全局变量与会话变量的区别在于,对全局变量的修改会影响整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)
6.有些系统变量的值是可以利用语句来动态进行更改的,但是有些系统变量的值缺是只读的,对于那些可以更改的系统变量,我们可以利用set语句进行更改
系统变量-全局变量
由系统系统,在整个数据库中有效
存储过程
存储过程传参-in
in表示传入的参数,可以传入数值或者变量,即使传入变量,并不会改变变量的值,可以内部更改,仅仅作用在函数范围内
delimiter $$
create procedure proc06(in param_empno int)
begin
select*from emp where empno = param_empno
end $$
delimiter ;
call proc06(1001);
多个参数传参
存储过程传参-out
out表示从存储过程内部传值给调用者
存储过程传参-inout
inout表示从外部传入的参数经过修改后可以返回的变量,既可以使用传入变量的值也可以修改变量的值(即使函数执行完)
流程控制
流程控制-判断
IF语句包含多个条件判断,根据结果为TURE和FALSE执行语句,与编程语言中的if、else if、else语法类似,其语法格式如下
-- 存储过程-if
-- 案例1
-- 输入学生的成绩,来判断成绩的级别
/*
score<60:不及格
score>=60 , score<80 :及格
score>=80 , score<90 :良好
score>=90 , score<=100 :优秀
score>100 :成就错误
*/
delimiter $$
create procedure proc_12_if (in score int)
begin
if score<60 then select '不及格';
elseif score>=60 and score<80 then select '及格';
elseif score>=80 and score<90 then select '良好';
elseif score>=90 and score<=100 then select '优秀';
else select '成绩错误'
end if;
end $$
delimiter ;
set @score=55;
call proc_12_if(@score)
-- 输入员工的名字,判断工资的情况
/*
sal<10000 :适用薪资
sal>=10000 and sal<20000 :转正薪资
sal>=20000 :元老薪资
*/
delimiter $$
create procedure proc_13_if(in in_ename varchar)
begin
declare var_sal decimal(7,2); -- 定义局部变量,7个长度,2个小数
declare result varchar(20);
select sal into var_sal from emp where ename=in_eanme; -- 将值赋给局部变量
if var_sal <10000 then set result ='适用薪资';
elseif var_sal <20000 then set result ='转正薪资';
else set result ='元老薪资';
end if;
select result;
end$$
delimiter ;
-- 调用
call proc_13_if('刘备');
流程控制语句-case
CASE是另一个条件判断语句,类似于编程语言中的switch语法
语法一:当case后面的语句与when后面的语句相等时,则会执行then后面的语句,如果均不匹配则执行else
语法二:直接写case 当when后面的条件成立则执行then后面的语句,如果都不成立,则执行else
-- 流程控制语句:case
/*
支付方式:
1.微信支付
2.支付宝支付
3.银行卡支付
4.其他支付
*/
-- 格式1
delimiter $$
create procedure proc14_case(in pay_type int)
begin
case pay_type
when 1 then select '微信支付';
when 2 then select '支付宝支付';
when 3 then select '银行卡支付';
else select '其他方式支付';
end case;
end$$
delimiter ;
call proc14_case(2);
call proc14_case(3);
-- 语法二
-- 输入学生的成绩,来判断成绩的级别
/*
score<60:不及格
score>=60 , score<80 :及格
score>=80 , score<90 :良好
score>=90 , score<=100 :优秀
score>100 :成就错误
*/
delimiter $$
create procedure proc_15_case (in score int)
begin
case
when score<60 then select '不及格';
when score>=60 and score<80 then select '及格';
when score>=80 and score<90 then select '良好';
when score>=90 and score<=100 then select '优秀';
else select '成绩错误'
end case;
end $$
delimiter ;
call proc_15_case(88);
控制流程-循环
概述
循环是一段在程序中只出现一次,但可能会连续运行多次的代码
循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环
循环分类
while、repeat、loop
循环控制
leave 类似于break,跳出,结束当前所在的循环
iteater 类似于continue,继续,结束本次循环,继续下一次
while循环
-- 需求:向表中添加指定条数据
-- while
delimiter $$
create procedure proc16_while(in insertCount int)
begin
declare i int default 1; -- 设置循环的增量
label:while i<=insertCount do insert into user(id,username,password) values(i,concat('user-',i),'123456');
set i=i+1;
end while label;
end$$
delimiter ;
call proc16_while(10);
-- 需求:向表中添加指定条数据
-- while+leave
delimiter $$
create procedure proc16_while(in insertCount int)
begin
declare i int default 1; -- 设置循环的增量
label:while i<=insertCount do insert into user(id,username,password) values(i,concat('user-',i),'123456');
if i=5 then leave label; -- 如果i=5则跳出循环
set i=i+1;
end while label;
end$$
delimiter ;
call proc16_while(10);
repeat循环
格式
-- 存储过程-循环控制-repeat
delimiter $$
create procedure proc18_repaet(in insertCount int)
begin
declare i int default 1;
label:repeat
insert into user(uid,username,password)values(i,concat('user-',i),'123456');
set i = i+1;
until i>inserCount -- 这里不能写; 表示跳出循环
end repeat label;
select '循环结束';
end$$
delimiter ;
call proc18_repeat(100);
loop循环
-- 存储过程-循环控制-loop
delimiter $$
create procedure proc19_loop(in insertCount int)
begin
declare i default 1;
label:loop insert into user (uid,username,password) values (i,concat('user-')i,'123456');
set i= i+1;
if i>insertCount then leave label;
end if;
end loop label;
end$$
delimiter ;
call proc19_loop()
游标
游标(cursor)是庸才存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环的处理,光标的使用包括光标的声明、OPEN、FETCH、CLOSE
-- 操作游标(cursor)
-- 声明游标
-- 打开游标
-- 通过游标获取值
-- 关闭游标
-- 需求:输入一个部门名,查询该部门员工的编号、名字、薪资,将查询的结果集添加游标
delimiter $$
create procedure proc19_cursor(in in_danme varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 声明游标
declare my_cursor cursor for select empno,ename,sal from dept a,emp b where a.deptno=b.deptno and a.dname=in_dname;
-- 打开游标
open my_cursor;
-- 通过游标获取值
-- fetch my_cursor into var_empno,var_ename,var_sal;
-- select var_empno,var_ename,var_sal; -- 注意:以上两句只可以获取一个数据,因为游标是一次一行,所以采用循环
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
select var_empno,var_ename,var_sal;
end loop label;
-- 关闭游标
close my_cursor;
end$$
delimiter ;
call proc19_cursor('销售部');
异常处理-HANDLER句柄
MySQL存储过程也提供了对异常处理的功能:通过定义HANDLER来完成异常声明的实现
格式
在语法中,变量声明、游标声明、handler声明必须按照先后顺序书写的,否则创建存储过程出错
DECALARE handler_action 创建句柄
FOR 后面为条件值,为什么异常提供处理
statment 当没有数据发现这个异常发生时采取什么措施
handler_action :{
CONTINUE 继续执行
EXIT 退出
UNDO 取消(目前MySQL不支持)
}
condition_value :{
mysql_error_code 错误码
condition 条件名
SQLWARNING SQL警告
NOT FOUND 数据没有发现
SQLEXCEPTION SQL异常
}
/*用于测试句柄,改造上面游标代码会出现1329错误代码的情况*/
-- 操作游标(cursor)
-- 声明游标
-- 打开游标
-- 通过游标获取值
-- 关闭游标
-- 需求:输入一个部门名,查询该部门员工的编号、名字、薪资,将查询的结果集添加游标
delimiter $$
create procedure proc21_cursor_handler(in in_danme varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 定义标记值
declare flag int default 1;
-- 声明游标
declare my_cursor cursor for select empno,ename,sal from dept a,emp b where a.deptno=b.deptno and a.dname=in_dname;
-- 定义句柄:定义异常的处理方式
/*
1:异常处理完之后,程序应该如何执行
continue :继续执行剩余代码
exit:直接终止程序
undo:不支持
2:触发条件
条件码:1329.....
条件名:
SQLWARNING
NOT FOUND
SQLEXCEPTION
3:异常触发之后执行什么代码
设置flag的值 -> 0
*/
declare continue handler for 1329 set flag=0 ;
-- 打开游标
open my_cursor;
-- 通过游标获取值
-- fetch my_cursor into var_empno,var_ename,var_sal;
-- select var_empno,var_ename,var_sal; -- 注意:以上两句只可以获取一个数据,因为游标是一次一行,所以采用循环
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
-- 判断flag的值如果为1则执行,否则不执行
if flag =1 then
select var_empno,var_ename,var_sal;
else leave label;
end if;
end loop label;
-- 关闭游标
close my_cursor;
end$$
delimiter ;
call proc19_cursor('销售部');
来源:https://blog.csdn.net/weixin_60589106/article/details/122704881
猜你喜欢
- IDEA版本:2020.25. 用户名中文问题导致Jrebel用不了 - 解决方案5.1 重装系统 - 下次不要用中文啦5.2 改注册表,并
- 为了得到更好的网络,学习率通常是要调整的,即刚开始用较大的学习率来加快网络的训练,之后为了提高精确度,需要将学习率调低一点。如图所示,步长(
- 本文通过将同一个数据集在三种不同的简便项窗口部件中显示。三个窗口的数据得到实时的同步,数据和视图分离。当添加或删除数据行,三个不同的视图均保
- 编程零基础,可以学习 Python 吗”,这是很多初学者经常问我的一个问题。当然,在计算机方面的基础越好,对学习任何一门新的编程语言越有利。
- 今天给大家分享在Python3.74+PyCharm2020.1 x64中安装使用Kivy的教程,具体内容如下所示:我实验环境是Python
- 一、概述网络爬虫(Web crawler),又称为网络蜘蛛(Web spider)或网络机器人(Web robot),主要用来爬取目标网站内
- 一、导言导语:在计算机进行数据交换时,常常会有一个进制转换的过程,我们知道计算机只认0 和 1.在内存系统中,基本基于二进制进行运算的,但是
- 这是一个由加油站油罐传感器测量的油罐高度数据和出油体积,根据体积和高度的倒数,用截面积来描述油罐形状,求出拟合曲线,再用标准数据,求积分来验
- 本文实例讲述了Python使用re模块正则提取字符串中括号内的内容操作。分享给大家供大家参考,具体如下:直接上代码吧:# -*- codin
- # -*- coding: cp936 -*-import socketfrom threading import Thread,activ
- 一、_func 单下划线开头 --口头私有变量1.1、在模块中使用单下划线开头在Python中,通过单下划线_来实现模块级别的私有化,变量除
- 变量方法举例备注用变量存储值name = "xxx"print("name ", name )小写字
- NumPy数组(2、数组的操作)基本运算数组的算术运算是按元素逐个运算。数组运算后将创建包含运算结果的新数组。>>> a=
- Python2>>> >>> isinstance(b'abc', bytes)True
- Tkinter是python的GUI模块,内含各种窗口控件,利用其中messagbox可以制作各种信息弹出窗口。以下是制作信息提示框的代码:
- 描述Python rindex() 方法返回子字符串最后一次出现在字符串中的索引位置,该方法与 rfind() 方法一样
- 写在前面题目所说的并不是目的,主要是为了更详细的了解网站的反爬机制,如果真的想要提高博客的阅读量,优质的内容必不可少。了解网站的反爬机制一般
- 目的:同步本地和服务器的全部或者部分文件本地debug,服务器跑实验需要条件:服务器上已经创建好虚拟环境你本地已经安装好pycharm1.1
- 本文实例讲述了Python素数检测的方法。分享给大家供大家参考。具体如下:该程序实现了素数检测器功能,如果结果是true,则是素数,如果结果
- 长期以来我就有对几年来交互设计的心得进行总结整理的想法。回到中国来亲身体会到不少同行,主要是交互设计师和视觉设计师对于交互设计的困惑,以及其