MySQL的存储过程——输入参数(in)、输出参数(out)、输入输出参数(inout)

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: MySQL的存储过程——输入参数(in)、输出参数(out)、输入输出参数(inout)

什么是存储过程?

MySQL 5.0 版本开始支持存储过程。

简单的说,存储过程就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法;Python里面的函数;

存储过就是数据库 SQL 语言层面的代码封装与重用。

image.png


特点

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

函数的普遍特性:模块化,封装,代码复用;



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


语法格式

delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
  sql语句
end 自定义的结束符合
delimiter ;
delimiter $$
create procedure proc01()
begin
  select empno,ename from emp; 
end  $$
delimiter ;
-- 调用存储过程
call proc01();

注意创建的时候需要指定delimiter

(自定义的,但是通常使用这个),然后使用createprocedure储存名(有输入参数输出参数)然后记住有begin和end之间就写SQL语句,并且需要在end后面加上

(自定义的,但是通常使用这个),然后使用createprocedure储存名(有输入参数输出参数)然后记住有begin和end之间就写SQL语句,并且需要在end后面加上

,最后用delimiter ;


局部变量

用户自定义,在begin/end块中有效 ,也就是相对于在函数内部中声明变量


语法: 声明变量 declare var_name type [default var_value]; 
举例:declare nickname varchar(32);

关键词:declare 变量名 类型(长度)可以加默认值——类似于我们python或者java中的 int a=10;


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

定义变量之后,使用set给变量赋值


MySQL 中还可以使用 SELECT..INTO 语句为变量赋值。其基本语法如下:


select col_name [...] into var_name[,...] 
from table_name wehre condition 
其中:
col_name 参数表示查询的字段名称;
var_name 参数是变量的名称;
table_name 参数指表的名称;
condition 参数指查询条件。
注意:当将查询结果赋值给变量时,该查询语句的返回结果只能是单行单列(单元格值)
delimiter $$
create procedure proc03()
begin
  declare my_ename varchar(20) ;
  select ename into my_ename from emp where empno=1001;
  select my_ename;
end $$
delimiter ;
-- 调用存储过程
call proc03();

如果是在内部声明的变量,并且进行了赋值之后,那么在内部调用显示的时候,可以直接select就可以实现展示数据的效果


用户变量

用户自定义,当前会话(连接)有效。类比java的成员变量


语法:


@var_name


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


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

这里使用@进行声明,前提不需要对其类型进行声明,直接可以赋值,但是调用的时候也需要@变量名


声明一般有两种方法:


1、declare 变量名 类型(长度)


然后使用set 变量名 = 值 接收


或者使用新值 into 变量名


2、直接使用@变量名=?


但是这个在整个会话都有作用,调用的时候需要使用@变量名


系统变量

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


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


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


也就是说,如果在建立会话以后,没有手动更改过会话变量与全局变量的值,那所有这些变量的值都是一样的。


全局变量与会话变量的区别就在于,对全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)。


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


查看修改全局变量

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

查看修改会话变量

-- 查看会话变量
show session variables;
-- 查看某会话变量 
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000; 
set @@session.sort_buffer_size = 50000 ;

传入参数

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


注意:这里参入参数如果和字段名一样的话,一般不要命名一样的名字,如果不小心命名一样的,那么也需要在表中指定,表.字段


而且需要注意的是声明输入参数:in 参数名 类型(长度)


-- 封装有参数的存储过程,传入员工编号,查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
        select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');
-- 封装有参数的存储过程,可以通过传入部门名和薪资,查询指定部门,并且薪资大于指定值的员工信息
delimiter $$
create procedure dec_param0x(in dname varchar(50),in sal decimal(7,2),)
begin
        select * from dept a, emp b where b.sal > sal and a.dname = dname;
end $$
delimiter ;
call dec_param0x('学工部',20000);

通过上述的案例,我们也可以发现,一般如果传入参数的时候,是字符串就用varchar(),如果是数值就用decimal(),当然有时候也可以使用数值类型,字符类型。


输出参数

out 表示从存储过程内部传值给调用者


-- ---------传出参数:out---------------------------------
use mysql7_procedure;
-- 封装有参数的存储过程,传入员工编号,返回员工名字
delimiter $$
create procedure proc08(in empno int ,out out_ename varchar(50) )
begin
  select ename into out_ename from emp where emp.empno = empno;
end $$
delimiter ;
call proc08(1001, @o_ename);
select @o_ename;

输出的时候,需要在存储方法中指定,并@变量名,最后才能让这个变量接收到数值


其次输出参数的定义的时候,也和输入参数一样的,out 参数名 类型(长度)


-- 封装有参数的存储过程,传入员工编号,返回员工名字和薪资
delimiter $$
create procedure proc09(in empno int ,out out_ename varchar(50) ,out out_sal decimal(7,2))
begin
  select ename,sal into out_ename,out_sal from emp where emp.empno = empno;
end $$
delimiter ;
call proc09(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;

内部接收的时候就需要使用 into 变量名,变量名……


注意:也就是说,在存储过程中使用out声明输出参数,在内部视图into 进行赋值之后,最后在调用存储的时候需要在里面自定义一个变量进行接收 但是必须要 使用@变量名


修改传入参数值

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


-- 传入员工名,拼接部门号,传入薪资,求出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(50),inout inout_sal int)
begin
  select  concat(deptno,"_",inout_ename) into inout_ename from emp where ename = inout_ename;
  set inout_sal = inout_sal * 12;  
end $$
delimiter ;
set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc10(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;

相对于,如果我们使用inout关键词,这个参数可以作为输入参数,也可以作为输出参数,输出参数时候,可以在内部的语句进行修改,然后覆盖原来的值,最后调用的时候可以定义一样的变量名,也可以不一样。


in 输入参数,意思说你的参数要传到存过过程的过程里面去,在存储过程中修改该参数的值不能被返回


out 输出参数:该值可在存储过程内部被改变,并向外输出


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


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
9天前
|
存储 缓存 关系型数据库
Mysql/etc/my.cnf参数详解
以上只是 `/etc/my.cnf`中的部分参数,实际上,`/etc/my.cnf`中的参数非常多,可以根据具体的应用需求进行调整。
10 0
|
1月前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
22天前
|
存储 关系型数据库 分布式数据库
PolarDB产品使用问题之在将RDS迁移到PolarDB后,原先由root用户创建的视图、存储过程等是否可以继续使用的
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
26天前
|
存储 SQL 关系型数据库
在MySQL中使用存储过程返回更新前的记录
在MySQL中使用存储过程返回更新前的记录
15 0
|
1月前
|
存储 关系型数据库 MySQL
索引的威力--记一次MySQL存储过程优化
在MySQL存储过程中,一个`INSERT INTO SELECT`语句起初执行超过130秒,优化后,执行时间降低到1秒内,实现了100倍的性能提升。问题在于`NOT IN`子查询导致的慢查询,最终通过创建单列索引获得了最佳效果。文章还介绍了索引创建的基本语法,并讨论了单列索引与组合索引的优缺点。作者强调,随着数据量增加,索引对于查询性能的重要性,计划未来采用读写分离来进一步优化处理大量插入和查询的场景。
|
14天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
12天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
983 6
Mysql 数据库主从复制
|
12天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
2天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用合集之如何实现类似mysql实例中的数据库功能
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
4天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
24 3

推荐镜像

更多