MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（3）-阿里云开发者社区

MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（3）

2024-06-11 98

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，集群系列 2核4GB

云数据库 RDS MySQL，高可用系列 2核4GB

简介： MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】

MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（2）https://developer.aliyun.com/article/1534302

3、存储过程

3.1、存储过程介绍

什么是存储过程：

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

特性：

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

其实也就是把可以实现某一功能的一堆 SQL 封装起来，等到需要用的时候直接调用即可，大大提高了SQL 的复用。

3.2、入门案例

为了区分普通的 SQL ，我们需要自定义 SQL 的结束符。下面我们看看存储过程的基本语法：

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

自定义存储过程：

-- 自定义存储过程
DELIMITER $$
CREATE PROCEDURE func01()
BEGIN
    SELECT * FROM emp;
END $$
DELIMITER ;
 
-- 调用存储过程
call func01();

3.3、存储过程的使用

3.3.1、局部变量的定义

局部变量是用户自定义，在 begin/end 快中有效

1）使用 declare 声明局部变量

declare 变量名 变量类型 [default 默认值]

测试：

DELIMITER $$
CREATE PROCEDURE func2()
BEGIN
    declare tmp int default 1;
    set tmp = 2;
    SELECT tmp;
end $$
DELIMITER ;
 
call func2();    -- 2

2）使用 select into 语句

注意：select into 只能给变量赋值，变量依然需要使用 declare 声明！

语法：

-- 把查询结果赋值给变量
SELECT col1, col2 into var1, var2 from table where condition;

注意：返回结果只能是单行结果！

测试：

DELIMITER $$
CREATE PROCEDURE func03()
BEGIN
    declare eid int;
    declare ename varchar(20);
    declare esalary double;
    declare dname varchar(20);
    SELECT emp_id, emp_name, salary, department
    INTO eid,ename,esalary,dname
    FROM emp WHERE emp_id=1;
    SELECT eid,ename,esalary,dname;
END $$
DELIMITER ;
 
call func03();

注意：局部变量名不能和表字段相同，否则查询为 null （字段类型可以不同）。

3.3.2、用户变量的定义

语法：

@@变量名

注意：

不需要像局部变量一样提前声明！（要声明也可以直接 set @变量名 = xxx）
生命周期是当前会话

DELIMITER $$
CREATE PROCEDURE func04()
BEGIN
    SET @result = 1;
end $$
delimiter ;
 
call func04();
 
SELECT @result; -- 必须调用之后才能被初始化赋值 否则为null

3.3.3、系统变量

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

1. 全局变量

注意：全局变量由系统提供，整个数据库有效。

语法：

@@global.变量名

查看全局变量：

show global variables ;

查看某个全局变量的值：

SELECT @@global.binlog_format;

设置全局变量（两种方式）：

set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000;

2. 会话变量

语法：

@@session.变量名

查看会话变量：

show session variables;

查看某个会话变量的值：

SELECT @@session.auto_increment_increment;

修改会话变变量的值（两种方法）：

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

3.3.4、存储过程传参 in

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

-- in 传参
delimiter $$
create procedure func05(in id int)
begin
    select * from emp where emp.emp_id = id;
end $$
delimiter ;
 
call func05(1); -- 查询 emp_id = 1 的员工信息

3.3.5、存储过程传参 out

out 表示从存储过程内部传值给调用者（内部值指的是用户变量），用户变量作用于整个会话，所以调用这个方法时，用户变量就会被初始化并赋值。

delimiter $$
create procedure func06(in id int,out ename varchar(20))
begin
    select emp_name into ename from emp where emp_id=id;
end $$
delimiter;
 
call func06(1,@ename); -- 这里的@name是用户变量所以并不需要声明
 
select @ename; -- 张晶晶

3.3.6、存储过程传参 inout

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

说人话就是：这个参数既可以当做普通参数用，也可以当做用户变量对它进行赋值

delimiter $$
create procedure func07(inout id int,inout name varchar(20))
begin
    update emp set emp_name = name where emp_id = id;
end $$
delimiter ;
 
set @name = '李大喜';
set @id = 1;
call func07(@id,@name);
 
select * from emp where emp_id = 1; -- 张晶晶变成了李大喜

3.3.7、存储过程传参总结

in 输入参数，意思说你的参数要传到存过过程的过程里面去，在存储过程中修改该参数的值不能被返回
out 输出参数:该值可在存储过程内部被改变，并向外输出
inout 输入输出参数，既能输入一个值又能传出来一个值

3.3.8、存储过程流程控制 - if 语句

DELIMITER $$
CREATE PROCEDURE func08(in id int)
begin
    declare num int default 0;
    declare result varchar(50);
    select count(*) into num from emp;
    if id < 1 || id > num
        then set result = '超出范围';
    else
        set result = (select emp_name from emp where emp_id = id);
    end if;
    select result;
end $$
delimiter ;
 
call func08(1);

3.3.9、存储过程控制流程 - case 语句

需要注意的是 case 语句有两种语法：一种是匹配变量是否等于某个值，一种是匹配变量是否符合某种条件。

匹配条件：

DELIMITER $$
CREATE PROCEDURE func09(in salary int)
BEGIN
    DECLARE grade VARCHAR(3);
    CASE
        WHEN salary > 5000 THEN SET grade = '高';
        WHEN salary > 3000 THEN SET grade = '中';
        ELSE SET grade = '低';
    END CASE;
    SELECT grade;
END $$
DELIMITER ;
 
CALL func09(8000); -- 高

匹配值：

delimiter $$
create procedure func10(in grade varchar(1))
begin
    declare result varchar(20);
    case grade
        when 'A' then set result = '90~100';
        when 'B' then set result = '80~90';
        when 'C' then set result = '70~80';
        when 'D' then set result = '60~70';
        when 'E' then set result = '0~60';
        else set result = '参数错误';
    end case;
    select result;
end $$
delimiter ;
 
call func10('B');

MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（4）https://developer.aliyun.com/article/1534308

MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（3）

3、存储过程

3.1、存储过程介绍

3.2、入门案例

3.3、存储过程的使用

3.3.1、局部变量的定义

1）使用 declare 声明局部变量

2）使用 select into 语句

3.3.2、用户变量的定义

3.3.3、系统变量

1. 全局变量

2. 会话变量

3.3.4、存储过程传参 in

3.3.5、存储过程传参 out

3.3.6、存储过程传参 inout

3.3.7、存储过程传参总结

3.3.8、存储过程流程控制 - if 语句

3.3.9、存储过程控制流程 - case 语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】（3）

3、存储过程

3.1、存储过程介绍

3.2、入门案例

3.3、存储过程的使用

3.3.1、局部变量的定义

1）使用 declare 声明局部变量

2）使用 select into 语句

3.3.2、用户变量的定义

3.3.3、系统变量

1. 全局变量

2. 会话变量

3.3.4、存储过程传参 in

3.3.5、存储过程传参 out

3.3.6、存储过程传参 inout

3.3.7、存储过程传参总结

3.3.8、存储过程流程控制 - if 语句

3.3.9、存储过程控制流程 - case 语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像