变量包括局部变量、用户变量、系统变量,参数传递包括in、out、inout。

变量

局部变量

用户自定义,在begin/end块中有效

语法:

declare var_name type [default var_value]; 

declare个叫var_name的局部变量,type是它的类型,可以给这个变量设定默认值var_value。

举例:

declare nickname varchar(32);  -- 定义一个nickname,类型为varchar(32),没有默认值

操作:

delimiter $$
create procedure proc02()
begin
    declare var_name01 varchar(20) default 'aaa';  -- 定义局部变量
    set var_name01 = 'zhangsan';                   -- 赋值
    select var_name01;                             -- 输出
end $$
delimiter ;

-- 调用存储过程
call proc02();
-- 运行结果

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

select col_name [,...] into var_name[,...] 
from table_name wehre condition 
  • col_name 参数表示查询的字段名称;

  • var_name 参数是变量的名称;

  • table_name 参数指表的名称;

  • condition 参数指查询条件。

注意:当将查询结果赋值给变量时,该查询语句的返回结果只能是单行单列。

示例:

-- 使用 SELECT..INTO 语句为变量赋值
delimiter $$
create procedure proc03()
begin
  declare my_ename varchar(20) ;                           -- 声明变量
  select ename into my_ename from emp where empno=1001;    -- 变量赋值(select...into...)
  select my_ename;                                         -- 输出变量值
end $$
delimiter ;
-- 注意:当将查询结果赋值给变量时,该查询语句的返回结果只能是单行单列。
-- 调用存储过程
call proc03();
-- 运行结果:
-- my_ename
-- 甘宁

用户变量

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

有效范围比局部变量要广。

语法:

-- 语法: 
@var_name
-- 不需要提前声明,使用即声明

示例:

-- 定义用户变量
delimiter $$
create procedure proc04()
begin
    set @var_name01  = 'ZS';        
end $$
delimiter;

call proc04() ;        -- 运行存储
select @var_name01  ;  -- 可以看到结果
# 用户变量可以在外面用

系统变量

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

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

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

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

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

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

全局变量

由系统提供,在整个数据库有效

语法:

@@global.var_name

操作:

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

会话变量

由系统提供,当前会话(连接)有效。

语法:

@@session.var_name

操作:

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

参数传递

in -- 传入参数

简单来说,就是类似C语言里面可以传入参数的函数

-- -------------传入参数: in -------------
-- 封装有参数的存储过程,传入员工编号,查找员工信息
delimiter $$
create procedure dec_param01(in param_empno int)   
-- in后面跟的参数的名字param_empno,以及数据类型int
-- 参数的数据类型可以在“设计表”中查看
begin
   select * from emp where empno = param_empno;
end $$
delimiter ;
-- call的时候在后面写上参数的值
call dec_param01(1001);
-- 结果:1001  甘宁  文员  1013    2000-12-17  8000.00     20
call dec_param01(1002);
-- 结果:1002  黛绮丝 销售员 1006    2001-02-20  16000.00    3000.00 30

还可以传入多个参数:

-- 可以传入多个参数
-- 封装有参数的存储过程,可以通过传入部门名和薪资,查询指定部门,并且薪资大于指定值的员工信息
-- 直说就是:查询“某一个部门(任意)”薪资大于“某个数”的人
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);  -- 查询 学工部 里面 薪资大于20000 的
call dec_param0x('财务部',30000);  -- 查询 财务部 里面 薪资大于30000 的

out -- 传出参数

简单来说,就是类似C语言里面函数的返回值

-- -------------传出参数: out -------------
use mydb7_procedure;
-- 封装有参数的存储过程,传入员工编号,返回员工名字
delimiter $$
create procedure proc08(in in_empno int ,out out_ename varchar(50))
begin
  select ename into out_ename from emp where empno = in_empno;   -- 用select...into...给参数赋值
end $$   
delimiter ;
call proc08(1001, @o_ename);  -- 定义一个用户变量来临时存放返回值
select @o_ename;              -- 显示返回值

inout -- 传入,修改后传出

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

--  ------------- inout --------------
-- 可以对传递进来的参数进行修改,然后输出
-- 参数的传出是自动的,可以直接调用
-- 案例:传入一个数字,传出这个数字的10倍值
delimiter $$
CREATE PROCEDURE proc10(inout num int)
BEGIN
 set num = num *10;
END $$
delimiter ;
set @inoutnum = 2; -- 随便定义一个用户变量存储输出
call proc10(@inoutnum);
select @inoutnum;
--  案例:传入员工名,拼接部门号,传入薪资,求出年薪
delimiter $$
create procedure proc11(inout inout_ename varchar(50),inout inout_sal int)
-- 定义两个inout变量,分别为员工名、薪资
begin
  select  concat(deptno,"_",inout_ename)  into inout_ename 
    from emp where ename = inout_ename;
    -- select...into... 给inout_ename 赋值
    -- concat用来拼接成“部门名_员工名”格式
  set inout_sal = inout_sal * 12;   -- 年薪 = 薪资*12
end $$
delimiter ;
set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc10(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;

数据附录

-- 准备数据
create database if not exists mydb6_view;
use mydb6_view;
create table dept(
    deptno int primary key,
  dname varchar(20),
    loc varchar(20)
);
insert into dept values(10, '教研部','北京'),
(20, '学工部','上海'),
(30, '销售部','广州'),
(40, '财务部','武汉');

create table emp(
    empno int primary key,
    ename varchar(20),
    job varchar(20),
    mgr int,
    hiredate date,
    sal numeric(8,2),
    comm numeric(8, 2),
    deptno int,
--  FOREIGN KEY (mgr) REFERENCES emp(empno),
    FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE
);
insert into emp values
(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, null, 20),
(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),
(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),
(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, null, 20),
(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),
(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, null, 30),
(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, null, 10),
(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, null, 20),
(1009, '曾阿牛', '董事长', null, '2001-11-17', 50000.00, null, 10),
(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),
(1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, null, 20),
(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, null, 30),
(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, null, 20),
(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, null, 10);

create table salgrade(
    grade int primary key,
    losal int,
    hisal int
);
insert into salgrade values
(1, 7000, 12000),
(2, 12010, 14000),
(3, 14010, 20000),
(4, 20010, 30000),
(5, 30010, 99990);

声明:内容来源于B站视频《2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程》,博客内容仅作学习参考使用。



博主个人公众号
版权声明 ▶ 本网站名称:陶小桃Blog
▶ 本文链接:https://www.52txr.cn/2022/mysql20.html
▶ 本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长进行核实删除。
▶ 转载本站文章需要遵守:商业转载请联系站长,非商业转载请注明出处!!

最后修改:2022 年 06 月 05 日
如果觉得我的文章对你有用,请随意赞赏