MySQL存储过程及语法详解
mycodes · 105浏览 · 发布于2022-08-04 +关注

这篇文章主要介绍了MySQL存储过程及语法详解,存储过程,也叫做存储程序,是一条或者多条SQL语句的集合,可以视为批量处理,但是其作用不仅仅局限于批量处理


    1. 存储过程基本用法

    1.1 概念

    • 存储过程,也叫做存储程序,是一条或者多条SQL语句的集合,可以视为批量处理,但是其作用不仅仅局限于批量处理。

    • 其中针对存储过程也有多种操作:如何创建存储过程,以及如何调用、查看、修改、删除存储过程。存储过程也可以调用其他存储过程。(类似于Java函数之间的相互调用)

    • 存储过程和函数是:事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,减少数据库和应用之间的传输,对于提高数据处理的效率是非常有好处的。

    函数:是一个有返回值的过程;过程:是一个没有返回值的函数

    存储过程和自定义函数的区别:

    • 存储过程实现的功能要复杂一些;而函数的针对性更强。

    • 存储过程可以返回多个值;函数只能有一个返回值。

    • 存储过程一般独立的来执行;而函数可以作为其他SQL语句的组成部分实现出来。

    1.2 创建存储过程

    1.2.1 语法格式

    # 这个地方其实是用来声明SQL语句的结束符号的
    delimiter //
    # 这个地方此时真正的用来创建一个存储过程的
    create procedure 存储过程名称(参数列表)
    begin
        -- sql语句
    end//
    # 当创建完一个存储过程之后再将分隔符替换为分号,为了不影响其他的操作
    delimiter ;

    2.2.2 语法介绍

    • delimiter : 用于设置sql语句分割符,默认为分号。因为在MySQL中每一条SQL语句都必须以 ;进行结束,当我们换行的时候就会执行这条SQL语句,但是我们此时的存储过程并没有结束,就会造成直接执行没有写完的存储过程造成报错,所以此时需要声明其他的结束符,不让其使用默认的分隔符结束SQL语句。

    • sql语句 :在这个部分编写sql语句,编写的语句需要以分号结尾,此时回车会直接执行,所以要创建存储过程前需要指定其他符号作为分割符,此时使用 // , 也可以使用其它字符。

    • // : 声明结束符号,这个符号可以是任意的,是自定义的。相当于就是把 分号替换为 //

    创建存储过程查询学生信息

    # 将SQL语句的结束符号分隔符替换为//
    delimiter //
    create procedure proc_stu()
    begin
    select * from students; # 此时这个分号并不会结束这个语句,存储过程中的SQL语句还是用分隔符进行分隔
    end // # 这里使用这个结束符号代表这个存储过程创建完成
    delimiter; # 创建完一个存储过程之后将结束符号替换为分号,防止进行其他操作的时候有问题

    1.3 调用存储过程

    语法格式:

    call proc_stu(); # 调用的时候需要加上括号,因为可能存在参数

    1.4 查看存储过程

    语法格式:

    # 查询 studnet 数据库中的所有的存储过程
    select name from mysql.proc where db='studnet';
    # 查询存储过程的状态信息
    show procedure status;

    1.5 删除存储过程

    语法格式:

    drop procedure proc_stu; # 删除的时候不要加小括号,直接给定存储过程的名字即可。
    drop procedure if exists proc_stu; # 如果存储删除,不存在不删除并且不会报错

    2. 存储过程中的语法结构

    • 存储过程是可以编程的,意味着可以使用变量、表达式、控制语句来完成比较复杂的功能。

    2.1 变量的声明以及赋值

    2.1.1 DECLARE 声明变量

    DECLARE : 通过 DECLARE 关键字可以定义一个局部变量,该变量的作用范围只能在 BEGIN..,END 块中。

    语法格式:

    DECLARE 变量名[,...] type [DEFAULT value]

    注意:声明变量的时候可以一次性声明多个,使用逗号隔开。

    示例:

    delimiter $
    create procedure proc_stu()
    begin
        declare num int default 5;
        select num + 10; # 输出结果为15
    end $
    delimiter ;


    2.1.2 SET 变量赋值

    SET : 直接赋值使用SET关键字,可以赋常量或者是表达式,具体语法如下:

    SET 变量名 = 变量值 [,变量名 = 变量值] ...

    注意:一次可以给多个变量赋值,中间使用逗号隔开。

    delimiter $
    create procedure proc_stu()
    begin
        declare name varchar(20);  # 可以指定变量类型以及变量的范围
        set name = 'MySQL';  # 给变量直接赋值
        select name; # 输出结果为:MySQL
    end $
    delimiter ;

    2.1.3 select...into 赋值

    delimiter $
    create procedure proc_student()
    begin
        declare count_num int(10);
        select count(*) into count_num from student;
        select count_num;
    end $
    delimiter ;

    2.2 条件判断

    2.2.1 if条件判断

    语法结构:

    # 只有满足差选条件才会执行 then 后面的SQL语句
    if search_condition(查询条件) then statement_list(SQL语句)
        [else if search_condition(查询条件) then statement_list(SQL语句)]...
        [else statement_list(SQL语句)]
    end if;

    需求:

    根据身高,判断当前身高所属的身材类型
        180及以上 --------> 身材高挑
        170 - 180 --------> 标准身材
        170以下 ----------> 一般身材

    实现这个简单的逻辑:

    delimiter $
    create procedure pro_figure()
    begin
        # 定义一个身高的变量
        declare height int(11) default 175;
        # 定义一个存储身高类型的变量
        declare figure varchar(50) default '';
        # 使用 if 语句判断身材类型
        if height >= 180 then
            set figure = '身材高挑';
        else if height < 180 and height >= 170 then
            set figure = '标准身材';
        else set figure = '一般身材';
        end if;
        # 输出结果
        select concat(height + '身高的身材为:' + figure);
    end $
    delimiter ;

    2.3 传递参数

    语法格式:

    delimiter $
    # 我们可以不指定 [in/out/inout] , 默认为 in,输入参数 
    create procedure pro_name([in/out/inout]参数名 参数类型)
    begin
        -- sql语句
    end $
    delimiter ;
     # in : 该参数可以作为输入,调用该存储过程需要传入的值,默认
    # out : 该参数作为输出,调用该存储过程之后返回的值。
    # inout : 既可以作为输入参数也可以作为输出参数。

    2.3.1 IN - 输入参数

    需求:根据输入的身高变量的值,判断当前身高对应的身材类型

    实现:

    delimiter $
    # 此时调用者在调用这个存储过程的时候必须传递身高的变量值
    create procedure pro_name(in height int(11))
    begin
        # 定义一个存储身高类型的变量
        declare figure varchar(50) default '';
        # 使用 if 语句判断身材类型
        if height >= 180 then
            set figure = '身材高挑';
        else if height < 180 and height >= 170 then
            set figure = '标准身材';
        else set figure = '一般身材';
        end if;
        # 输出结果
        select concat(height + '身高的身材为:' + figure);
    end $
    delimiter ;

    调用:

    # 调用该存储过程。需要传递其中的身高值
    call pro_name(175);  # 输出结果为:

    2.3.2 out - 输出参数

    需求:根据输入的身高,返回当前身高所处的身材类型

    实现:

    delimiter $
    create procedure pro_output(in height int(11) , out figure varchar(100))
    begin
        # 使用 if 语句判断身材类型
        if height >= 180 then
            set figure = '身材高挑';
        else if height < 180 and height >= 170 then
            set figure = '标准身材';
        else set figure = '一般身材';
        end if;
    end $
    delimiter ;

    调用:

    # @标识符:在MySQL中代表的就是用户定义的一个变量,这里我们使用这个变量来接收这个存储过程的返回值
    call pro_output(175 , @figure);
    # 查看存储过程返回的结果
    select @figure;

    @标识符的作用

    • @figure :这种在变量名前面加上”@“符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似于全局变量一样。当前会话就是代表的,比如我们在命令提示窗口中给好多带有 @ 符号变量进行赋值,此时这些变量的值只作用于当前的会话,当我们把这个窗口关闭的时候,此时这些变量的值就会释放掉。

    • @@global : 这种在变量名前加上 "@@" 符号,叫做系统变量。

    2.4 case 结构

    语法格式:

    # 方式一
    case case_value(判断的值)
        when when_value(比较的值) then statement_list(SQL语句)
        [when when_value(比较的值) then statement_list(SQL语句)]...
        [else statement_list(SQL语句)]
    end case;
    # 方式二
    case
        when search_condition(查询条件) then statement_list(SQL语句)
        [when search_condition(查询条件) then statement_list(SQL语句)]...
        [else statement_list(SQL语句)]
    end case;

    需求:给定一个月份,判断该月份所属的季度

    实现:

    delimiter $
    create procedure pro_quarter(in mon int(11))
    begin
        # 定义存储季度的变量
        declare result varchar(10);
        case
            when mon >= 1 and mon <= 3 then
                set result = '第一季度';
            when mon >= 4 and mon <= 6 then
                set result = '第一季度';
            when mon >= 7 and mon <= 9 then
                set result = '第一季度';
            else
                set result = '第四季度';
            end case;
        # 输出结果
        select result; 
    end $
    delimiter ;

    2.5 while循环

    有条件的循环控制语句,当满足条件的时候进入循环,不满足条件的时候退出循环。

    语法结构:

    # 只要查询条件一直成立就会一直指定do后面的SQL语句,当查询条件不成立的时候直接跳出while循环
    while search_condition(查询条件) do
        statement_list(SQL语句)
    end while;

    需求:计算从1加到n的值

    实现:

    delimiter $
    create procedure pro_sum(in num int(11))
    begin
        # 定义存储总数的变量
        declare total int(255) default 0;
        # 定义存储循环次数的数量
        declare number int(255) default 1;
        while number <= num do
            set total = total + number;
            set number = number + 1;
        end while;
        select total;
    end $
    delimiter ;

    2.6 repeat循环

    有条件的循环控制语句,当不满足条件的时候进入循环,满足条件的时候跳出循环。他和while循环是反着的

    语法结构:

    repeat
        statement_list(SQL语句)
        until search_condition(查询添加)
    end repeat;

    需求:计算从1加到n的值

    实现:

    delimiter $
    create procedure pro_sum(in num int(11))
    begin
        # 定义存储总数的变量
        declare total int(255) default 0;
        repeat
            set total = total + number;
            set num = num - 1;
            # 注意:这个 unti 后的查询条件不要加分号,加分号会报错。
            until num = 0
        end repeat;
        select total;
    end $
    delimiter ;

    2.7 loop循环

    loop实现简单的循环,退出循环的条件需要使用其他的语句定义,通常可以使用leave语句实现,具体语法如下:

    语法格式:

    [begin_label:] loop
        statement_list
    end loop [end_label]

    如果不在statement_list中增加退出循环的语句,那么loop语句可以永安里实现简单的死循环。

    2.8 leave语句

    用来从标注的流程构造中退出,通常和 begin...end 或循环一起使用。下面是一个使用loop和leave的简单例子,退出循环:

    需求:计算从1加到n的值 ---> 使用loop...leave的形式进行退出循环

    实现:

    delimiter $
    create procedure pro_sum(in num int(11))
    begin
        # 定义存储总数的变量
        declare total int(255) default 0;
             c(该循环的别名):loop
            set total = total + num;
            set num = num - 1;
            # 借助leave组织退出条件
            if num <= 0 then
                leave c;
            end if;
        end loop c;
        select total;
    end $
    delimiter ;

    到此这篇关于MySQL存储过程及语法详解的文章就介绍到这了


    相关推荐

    部署MySQL延迟从库的几个好处

    吴振华 · 440浏览 · 2019-05-14 21:57:51
    使用SELECT语句检索数据

    奔跑的男人 · 552浏览 · 2019-06-03 09:33:43
    Oracle开启和关闭的几种模式

    qq2360248666 · 482浏览 · 2019-06-04 10:18:47
    加载中

    0评论

    评论
    我从事编程工作,现在在一家网络公司上班,偶尔也是发布博客,逛论坛等,希望可以在这里交到志同道合的朋友。
    分类专栏
    小鸟云服务器
    扫码进入手机网页