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

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
9天前
|
存储 JSON 关系型数据库
mysql中find_in_set()函数用法详解及增强函数
总结而言,`FIND_IN_SET()`是MySQL中处理由逗号分隔的字符串列表的一种便捷方法,尤其适用于列表相对较短且不经常更改的场景。然而,对于更为复杂的需要高性能和可扩展性的数据库设计,它可能不是最优选择,应考虑使用更加正规化的数据库结构。
13 2
mysql中find_in_set()函数用法详解及增强函数
|
13天前
|
关系型数据库 MySQL
mysql使用 CONCAT(字段,字段) 函数拼接
mysql使用 CONCAT(字段,字段) 函数拼接
|
29天前
|
JSON 关系型数据库 MySQL
MySQL常用函数解读:从基础到进阶的全方位指南
MySQL常用函数解读:从基础到进阶的全方位指南
|
29天前
|
SQL 关系型数据库 MySQL
mysql中GROUP_CONCAT函数详解
mysql中GROUP_CONCAT函数详解
20 0
|
19天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
17天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
17天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
990 7
Mysql 数据库主从复制
|
7天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用合集之如何实现类似mysql实例中的数据库功能
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
8天前
|
消息中间件 DataWorks 关系型数据库
DataWorks产品使用合集之遇到无法连接到本地 MySQL 数据库的问题,该如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
9天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
41 3