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

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

2024-06-11 41

版权

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

本文涉及的产品

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

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： 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