【数据库原理与运用|MySQL】MySQL存储过程（详细超全）

小小程序员 • 2023-01-02 • 云技术社区 • 263 阅读

云数据库https://cloud.tencent.com/product/cdb

目录

编辑

MySQL储存过程

存储过程的介绍及其特性

存储过程的介绍

存储过程的特性

格式

变量

局部变量

系统变量

运行结果

存储过程传参-in -out

in

out

inout

流程控制

if-else

case

循环

while

repeat

loop

游标

异常处理

存储过程中的handler

MySQL储存过程

存储过程的介绍及其特性

存储过程的介绍

  • MySQL 5.0 版本开始支持存储过程。
  • 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
  • 存储过就是数据库 SQL 语言层面的代码封装与重用。

存储过程的特性

  • 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
  • 函数的普遍特性：模块化，封装，代码复用；
  • 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

格式

delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
sql语句
end 自定义的结束符号
delimiter ;

delimiter $$ 

create procedure proc02()
    
    begin

    declare var_name01 varchar(20) default ‘aaa’; -- 定义局部变量

    set var_name01 = ‘zhangsan’;

    select var_name01;

    end $$

delimiter ;

-- 调用存储过程

call proc02();

变量

局部变量

用户自定义，在begin/end块中有效

declare var_name01 varchar(20) default ‘aaa’; -- 定义局部变量

用户变量

语法：

@var_name

不需要提前声明，使用即声明


delimiter $$

create procedure proc04()

begin

set @var_name01 = 'ZS';

end $$

delimiter;

call proc04() ;

select @var_name01 ; --可以看到结果

系统变量

  • 系统变量又分为全局变量与会话变量
  • 全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
  • 会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
  • 也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
  • 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
  • 有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

全局变量

@@global.var_name
整个数据库有效

会话变量

@@session.var_name
当前会话有效 

-- 查看会话变量

show session variables;

-- 查看某会话变量

select @@session.auto_increment_increment;

-- 修改会话变量的值

set session sort_buffer_size = 50000;

set @@session**.sort_buffer_size =** 50000 ;

存储过程传参-in -out

in

in 表示传入的参数， 可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。

-- 封装有参数的存储过程，传入员工编号，查找员工信息

delimiter $$

create procedure dec_param01 (in param_empno varchar (20))

begin

select * from emp where empno = param_empno;

end $$

delimiter ;

call dec_param01('1001');

由于表格被我不小心删除了，所以显示表格emp不存在

out

out 表示从存储过程内部传值给调用者

use mysql7_procedure;

-- 封装有参数的存储过程，传入员工编号，返回员工名字

delimiter $$

create procedure proc08(in empno int ,out out_ename varchar(50) )

begin

select ename into out_ename from emp where emp.empno = empno;

end $$

delimiter ;

call proc08(1001, @o_ename);

select @o_ename;

同样，创建没有问题，但由于表格被我删除，所以查询不到，大家在使用时可以更改存储过程表格名称和变量

编辑

inout

inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）

-- 传入员工名，拼接部门号，传入薪资，求出年薪

delimiter $$

create procedure proc10(**inout** inout_ename varchar(50),**inout** inout_sal int)

begin

select concat**(deptno,"_",inout_ename)** into inout_ename from emp where ename = inout_ename;

set inout_sal = inout_sal * 12;

end $$

delimiter ;

set @inout_ename = '关羽';

set @inout_sal = 3000;

call proc10(@inout_ename, @inout_sal) ;

select @inout_ename ;

select @inout_sal ;

编辑

流程控制

if-else

-- 语法

if search_condition_1 then statement_list_1

[else if search_condition_2 then statement_list_2] ...

[else statement_list_n]

end if


--例子

delimiter $$

create procedure proc_12_if(in score int)

begin

if score < 60 then select '不及格';

elseif 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 ;

case

-- 语法一

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(4);

执行结果

编辑

编辑

-- 语法二

delimiter $$

create procedure proc_15_case(in score int)

begin

case

when score < 60 then select '不及格';

when 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

-- -------存储过程-while

delimiter $$

create procedure proc16_while1(in insertcount int)

begin

declare i int default 1;

label:while i<=insertcount do

insert into user(uid,username,password) values**(i,concat('user-',i),'123456');

set i=i+1;

end while label;

end $$

delimiter ;

call proc16_while(10);

repeat

-- -------存储过程-循环控制-repeat

use mysql7_procedure;

truncate table user;

delimiter $$

create procedure proc18_repeat(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 > insertCount

end repeat label;

select '循环结束';

end $$

delimiter ;

call proc18_repeat(100);

loop

truncate table user;

delimiter $$

create procedure proc19_loop(in insertCount int)

begin

declare i int default 1;

label:loop

insert into user(uid, username, password) values(i,concat('user-',i),'123456');

set i = i + 1;

if i > 5

then

leave label;

end if;

end loop label;

select '循环结束';

end $$

delimiter ;

call proc19_loop(10);

leave 类似于 break，跳出，结束当前所在的循环

iterate类似于 continue，继续，结束本次循环，继续下一次

游标

游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE.

-- 声明语法

declare cursor_name cursor for select_statement

-- 打开语法

open cursor_name

-- 取值语法

fetch cursor_name into var_name [, var_name] ...

-- 关闭语法

close cursor_name
delimiter $$

create procedure proc20_cursor(in in_dname varchar(50))

begin

-- 定义局部变量

declare var_empno varchar(50);

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;

-- 通过游标获取每一行数据

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

-- 调用存储过程

call proc20_cursor('销售部');

异常处理

存储过程中的handler

-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标

delimiter $$

create procedure proc20_cursor(in in_dname 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,salb from dept a, emp b where a.deptno = b.deptno and a.dname = in_dname;

-- 定义句柄，当数据未发现时将标记位设置为0

declare continue handler for NOT FOUND set flag = 0;

-- 打开游标

open my_cursor;

-- 通过游标获取值

label:loop

fetch my_cursor into var_empno, var_ename,var_sal;

-- 判断标志位

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 proc21_cursor_handler('销售部');
本站文章资源均来源自网络，除非特别声明，否则均不代表站方观点，并仅供查阅，不作为任何参考依据！
如有侵权请及时跟我们联系，本站将及时删除！
如遇版权问题，请查看 本站版权声明
THE END
分享
二维码
海报
【数据库原理与运用|MySQL】MySQL存储过程（详细超全）
云数据库https://cloud.tencent.com/product/cdb
<<上一篇
下一篇>>