MySQL高级——MySQL的储存过程

不止萩

于 2024-08-01 19:28:28 发布

阅读量482

点赞数 25

分类专栏：数据库高级文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_66919047/article/details/140837719

版权

数据库高级专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、什么是储存过程

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

特性：

1.有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；

2.函数的普遍特性：模块化，封装，代码复用；

3.速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

创建储存过程语法格式：

delimiter 自定义结束符号

create procedure 储存名([ in ,out ,inout ] 参数名数据类型...)

begin

sql语句...

end 自定义的结束符合

delimiter ;

-- 调用存储过程

call proc01();

二、变量定义

2.1 局部变量

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

语法：

语法：声明变量 declare var_name var_type [default var_value];

举例：declare nickname varchar(32);

示例：

delimiter $$            
create procedure proc02()
begin
    declare var_name01 varchar(20) default ‘aaa’;  -- 定义局部变量
    set var_name01 = ‘zhangsan’;-- 修改变量的值  
    select var_name01;
end $$
delimiter ;
-- 调用存储过程
call proc02();

MySQL 中还可以使用 SELECT....INTO 语句为变量赋值。顾名思义，可以简单的理解为在原表col_name查到的字段然后赋值到var_name里面，被赋值的就相当于一个新表。其基本语法如下：

select col_name [...] into var_name[,...]

from table_name

where condition

其中：

col_name 参数表示查询的字段名称；

var_name 参数是变量的名称；

table_name 参数指表的名称；

condition 参数指查询条件。

注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列或单行多列。

2.2 用户变量（会话变量）

用户自定义，当前会话（连接）有效。类比java的成员变量

语法：

语法：

@var_name

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

示例：

delimiter $$
create procedure proc04()
begin
    set @var_name01  = 'ZS';
end $$
delimiter;
call proc04() ;
select @var_name01  ;  --可以看到结果

2.3 系统变量

介绍

1）系统变量又分为全局变量与会话变量

2）全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。

3）会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。

4）也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。

5）有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

2.3.1 全局变量

由系统提供，在整个数据库有效。

语法：

语法：

@@global.var_name

示例：

-- 查看全局变量 
show global variables; 
-- 查看某全局变量 
select @@global.auto_increment_increment; 
-- 修改全局变量的值 
set global sort_buffer_size = 40000;

2.3.2 会话变量

由系统提供，当前会话（连接）有效

语法：

语法：

@@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 输出参数:该值可在存储过程内部被改变，并向外输出

inout 输入输出参数，既能输入一个值又能传出来一个值)

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

2） out 表示从存储过程内部传值给调用者，传递的是变量,等待存储过程运算后给这个变量赋值。

3）inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）。传递的是有值得变量,存储过程要用你传递的值,加工后返回给你的新值。

四、分支流程

4.1 if判断

IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句，与编程语言中的if、else if、else语法类似，其语法格式如下：

-- 语法

if search_condition_1 then statement_list_1

[elseif search_condition_2 then statement_list_2] ...

[else statement_list_n]

end if

示例：

4.2 case分支

CASE是另一个条件判断的语句，类似于编程语言中的switch语法

-- 语法一（类比java的switch）：固定值筛选

case case_value

when when_value then statement_list

[when when_value then statement_list] ...

[else statement_list]

end case

-- 语法二：条件分支

case

when search_condition then statement_list

[when search_condition then statement_list] ...

[else statement_list]

end case

示例：

五、循环流程

循环是一段在程序中只出现一次,但可能会连续运行多次的代码。循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环。

循环分类： while repeat loop

5.1 循环-while

语法：

【标签:】while 循环条件 do

循环体;

end while【标签】;

示例：

5.2 循环-repeat

语法：

[标签:]repeat

循环体;

until 条件表达式

end repeat [标签];

示例：

5.3 循环-loop

语法：

[标签:] loop

循环体;

if 条件表达式 then

leave [标签];

end if;

end loop;

loop循环需配合leave才能停止，不然就会形成死循环。

六、游标

游标(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

示例：

use mysql7_procedure;
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

MySql存储过程也提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现。

官方文档：https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html

注意：在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

语法：

DECLARE handler_action HANDLER

FOR condition_value [, condition_value] ...

statement handler_action: {

CONTINUE

| EXIT

| UNDO

}

condition_value: {

mysql_error_code

| condition_name

| SQLWARNING

| NOT FOUND

| SQLEXCEPTION

不止萩

关注

25
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
MySQL高级——MySQL的储存过程

MySQL 5.0 版本开始支持存储过程。简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法，类似Python中的函数；存储过程就是数据库 SQL 语言层面的代码封装与重用。1.有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；2.函数的普遍特性：模块化，封装，代码复用；3.速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；
复制链接

扫一扫

专栏目录