【mysql】扩展-变量_存储过程_函数

一、变量

类似Java中的变量，MySQL也存在变量。变量分类：

• 系统变量
  • 全局变量
  • 会话变量
• 自定义变量
  • 用户变量
  • 局部变量

1.1、系统变量

说明：变量由系统定义，不是用户定义，属于服务器层面。 注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别。

常见操作如下：

# 查看所有系统变量
show global|session variables;
# 查看满足条件的部分系统变量
show global|session variables like 'xxx';
# 查看指定的系统变量的值
select @@global|session.系统变量名;
# 为某个系统变量赋值
# 方式一：
set global|session 系统变量名=值;
# 方式二：
set @@global|session.系统变量名=值;

# 设置隔离级别
set autocommit=0; #默认设置，设置会话的自动提交
set @@global.autocommit=0;

# 查看隔离级别
select @@autocommit; #默认查询，查询会话的自动提交
select @@global.autocommit; #查询全局的自动提交

1.2、自定义变量

说明：变量由用户自定义，而不是系统提供的。 使用步骤：

• 声明
• 赋值
• 使用（查看、比较、运算等）

1.2.1、用户变量

作用域：针对于当前会话（连接）有效，作用域同会话变量。

赋值操作符：=或:=

# 声明并初始化
set @变量名=值;
set @变量名:=值;
select @变量名:=值;

# 赋值，更新变量的值
# 方式1
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
# 方式2，查询出的数据一定是单个值
select 字段 into @变量名 from 表;

# 使用，查看变量的值
select ＠变量名；

set @name='zs';
set @ct:=100;
select @ct:=0;

select count(*) into @ct from emp;
select @ct;

1.2.2、局部变量

作用域：仅仅在定义它的begin、end块中有效。 应用在begin、end中的第一句话。

# 声明
declare 变量名 类型;
declare 变量名 类型 [default 值];

# 赋值
# 方式1
set 局部变量名=值;
set 局部变量名:=值;
select 局部变量名:=值;
# 方式2
select 字段 into 变量名 from 表;

# 使用，查看变量的值
select 局部变量名;

1.2.3、用户变量和局部变量对比

	作用域	定义和使用的位置	语法
用户变量	当前会话	会话中的任何地方	必须加@符号，不用限定类型
局部变量	begin、end块中	只能在begin、end块中，且为第一句话	不用加@符号，需要限定类型

二、存储过程

含义：一组预先编译好的SQL语句的集合，理解成批处理语句。

优势：

• 提高代码的重用性；
• 简化操作；
• 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

2.1、创建语法

create procedure 存储过程名(参数列表)
begin
    存储过程体(一组SQL语句)
end

说明：

1. 如果存储过程体仅仅只有一句话，begin、end可以省略；

2. 存储过程体中的每条sql语句的结尾要求必须加分号；

3. 由于存储过程体使用分号表示结束，那么存储过程必须使用其他字符表示结尾。存储过程使用delimiter重新设置结尾；

   # 语法
   delimiter 结束标记
   # 案例
   delimiter $
   

4. 参数列表包含三部分：

   • 参数模式
     • in：该参数可以作为输入，也就是该参数需要调用方传入值
     • out：该参数可以作为输出，也就是该参数可以作为返回值
     • inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
   • 参数名
   • 参数类型

2.2、调用语法

call 存储过程名(实参列表);

2.3、删除存储过程

drop procedure 存储过程名;

2.4、查看存储过程的信息

show create proceduce 存储过程名;

2.5、举例

# 创建存储过程,在部门表中添加两条数据
delimiter $
create procedure pro1()
begin
    insert into dept(dname, loc) values('aaa', 'bj'),('bbb', 'tj');
end $
# 调用存储过程
call pro1()$


# 创建存储过程，根据部门名称查询部门名
delimiter $
create procedure pro2(in deptloc varchar(20))
begin
    select dname from dept where loc=deptloc;
end $
# 调用存储过程
call pro2('北京')$


# 创建存储过程，查询在特定部门从事特定工种的员工的信息和部门信息
delimiter $
create procedure pro3(in job varchar(20), in deptname varchar(20))
begin
    select
        e.*, d.*
    from
        emp e, dept d
    where
        e.deptno=d.deptno and e.job=job and d.dname=deptname;
end $
# 调用存储过程
call pro3('文员','市场部')$


# 创建存储过程，查询特定部门和特定工种的员工个数
delimiter $
create procedure pro4(in job varchar(20), in deptname varchar(20))
begin
    declare result int default 0;
    select
        count(*) into result
    from
        emp e, dept d
    where
        e.deptno=d.deptno and e.job=job and d.dname=deptname;
        
    select result as '个数';
end $
# 调用存储过程
call pro4('文员','市场部')$


# 创建存储过程，查询特定部门和特定工种的员工个数
delimiter $
create procedure pro5(in job varchar(20), in deptname varchar(20), out result int)
begin
    select
        count(*) into result
    from
        emp e, dept d
    where
        e.deptno=d.deptno and e.job=job and d.dname=deptname;
end $
# 调用
call pro5('文员', '研发部', @r);
select @r;


# 创建存储过程，传入a和b两个值，最终a和b都翻倍并返回
delimiter $
create procedure pro6(inout num1 int, inout num2 int)
begin
    set num1=num1*2;
    set num2=num2*2;
end $
set @a=2$
set @b=3$
# 调用存储过程
call pro6(@a, @b)$
select @a$
select @b$

三、函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句。

优势：

• 提高代码的重用性；
• 简化操作；
• 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

与存储过程区别：

• 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新；
• 函数：有且仅有1 个返回，适合做处理数据后返回一个结果。

3.1、创建语法

create function 函数名(参数列表) returns 返回类型
begin
    函数体
end

说明：

1. 参数列表包含两部分：参数名 参数类型；
2. 函数体肯定会有return语句，如果没有会报错；
3. 函数体中仅有一句话，则可以省略begin、end；
4. 使用delimiter语句设置结束标记。

3.2、调用语法

select 函数名(参数列表)

3.3、查看函数

show create function 函数名;

3.4、删除函数

drop function 函数名;

3.5、案例

# 定义函数，查询员工数量
delimiter $
create function myfun1() returns int
begin
    declare c int default 0;
    select count(*) into c from emp;
    return c;
end $
# 调用函数
select myfun1()$


# 定义函数，根据工种查询员工数量
delimiter $
create function myfun2(job varchar(20)) returns int
begin
    declare c int default 0;
    select count(*) into c from emp where emp.job=job;
    return c;
end $
# 调用函数
select myfun2('研发部')$


# 定义函数，根据部门名称，查询平均工资
delimiter $
create function myfun3(dname varchar(20)) returns double
begin
    declare avgsal double default 0;
    select
        a.a into avgsal
    from
        dept d, (select deptno, avg(sal) a from emp group by deptno) a
    where
        d.deptno=a.deptno and d.dname=dname;
    return avgsal;
end $
# 调用函数
select myfun3('研发部')$

# 定义函数，求两数的和
delimiter $
create function myfun4(num1 int, num2 int) returns int
begin
    declare sum int default 0;
    set sum:=num1+num2;
    return sum;
end $
# 调用函数
select myfun4(11，21)$