Skip to content

Latest commit

 

History

History
343 lines (267 loc) · 7.66 KB

06.变量、存储过程和函数.md

File metadata and controls

343 lines (267 loc) · 7.66 KB
Raw

10. 变量

分类：

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

10.1 系统变量

变量由系统提供，不是用户定义，属于服务器层面

如果是全局级别，需要加global，如果是会话级别，则需要加session，如果不写，则默认session

1. 全局变量

作用域：服务器每次启动将为所有的全局变量赋初始值，必须拥有super权限才能为系统变量赋值，针对所有的会话(连接)有效，但不能跨重启。

  • 查看系统变量
#全局变量
show global variables;
#会话变量
show session variables;
  • 查看满足条件的部分变量
show global variables like '%char%';
  • 查看指定的某个系统变量的值
select @@global.autocommit;
select @@transaction_isolation;
  • 为某个具体的变量赋值
set @@global.autocommit=0;

2. 会话变量

作用域：仅针对于当前会话(连接)有效

  • 查看所有会话变量
#会话变量
show session variables;
show variables;
  • 查看满足条件的部分变量
show session variables like '%char%';
show variables like '%char%';
  • 查看指定的某个系统变量的值
select @@session.transaction_isolation;
select @@transaction_isolation;
  • 为某个具体的变量赋值
set @@session.autocommit=0;
set session autocommit=0;

10.2 自定义变量

用户自定义的，不是由系统提供的

使用步骤：

  • 声明
  • 赋值
  • 使用(查看、比较、运算等)

1. 用户变量

作用域：针对于当前会话(连接)有效，同于会话变量的作用域

应用在任何地方, begin end 里外都可以

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

#2. 更新用户变量的值
#方式一
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
#方式二
select 字段 into @变量名 from 表;

#3. 查看用户变量的值
select @用户变量名

set @count=1;
select count(*) into @count from employees;
select @count;

2. 局部变量

作用域：仅仅在定义它的begin end中有效

应用在 begin end 中的第一句话

#1. 声明
declare 变量名 类型;
declare 变量名 类型 default 值;
#2. 赋值
#方式一
set 局部变量名=值;
set 局部变量名:=值;
select @局部变量名:=值;
#方式二
select 字段 into 局部变量名 from 表;
#3. 使用
select 局部变量名;

3. 用户变量与局部变量对比

用户变量

  • 作用域：当前会话
  • 定义和使用的位置：会话中的任何地方
  • 语法：必须加@符号，不用限定类型

局部变量

  • 作用域：begin end中
  • 定义和使用的位置：只能在begin end 中，且为第一句话
  • 语法：一般不用加@符号，需要限定类型
#声明两个变量并赋初始值，求和，并打印
#1. 用户变量
set @m=1;
set @n=2;
set @sum=@m+@n;
select @sum;

#2. 局部变量
declare m int default 1;
declare n int default 2;
declare sum int;
set sum=m+n;
select sum;

11. 存储过程和函数

类似于java中的方法

好处：

  1. 提高代码的重用性
  2. 简化操作

11.1 存储过程

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

好处：

  1. 提高代码的重用性
  2. 简化操作
  3. 减少了编译次数，并且减少了和数据服务器的连接次数，提高效率

1. 创建语法

语法：create procedure 存储过程名(参数列表) begin 存储过程体(一组合法的sql语句) end

注意：

  1. 参数列表包含三部分：参数模式 参数名 参数类型
    • in stuname varchar(20)
    • 参数模式
      • in: 该参数可以作为输入，该参数需要调用方传入值
      • out: 该参数作为输出，该参数可以作为返回值
      • inout: 该参数既可以作为输入，又可以作为输出
  2. 如果存储过程体仅仅只有一句话，begin end 可以省略
  3. 存储过程体中的每条sql语句的结尾必须加分号，存储过程的结尾可以使用 delimiter 重新设置
    • 语法：delimiter 结束标记

2. 调用语法

语法：call 存储过程名(实参列表)

  • 空参列表
#插入到admin表中五条记录
delimiter $

create procedure myp1()
begin
	insert into admin(username,password) values('john1', '0000'), ('lily', '0000'), ('rose', '0000'),('jack', '0000'),('tom', '0000');
end $

call myp1()$
  • 创建带in模式参数的存储过程
#根据女神名，查询对应的男神信息
create procedure myp2(in beautyName varchar(20))
begin
    select bo.* from boys bo right join beauty b on bo.id = b.boyfriend_id where b.name = beautyName;
end $

call myp2('liuyan')$

#用户是否登录成功
create procedure myp3(in username varchar(20), in password varchar(20))
begin
    declare result int default '';
    select count(*) into result from admin where admin.username=username and admin.password=password;
    select if(result>0,'success','unsuccess');
end $

call myp3('zhangfei', '8888')$
  • 创建带out模式的存储过程
#根据女神名，返回对应的男神名
create procedure myp4(in beautyName varchar(20), out boyName varchar(20))
begin
    select bo.boyName into boyName from boys bo inner join beauty b on bo.id = b.boyfriend_id where b.name=beautyName;
end $

set @bName$
call myp4('xiaozhao', @bName)$
select @bName$

#根据女神名，返回对应的男神名和男神魅力值
create procedure myp5(in beautyName varchar(20), out boyName varchar(20), out userCP int)
begin
    select bo.boyName,bo.userCP into boyName,userCP from boys bo inner join beauty b on bo.id = b.boyfriend_id where b.name=beautyName;
end $

call myp5('xiaozhao', @bName, @usercp)$
select @bName, @usercp$
  • 创建带inout模式的存储过程
#传入a和b两个值，最终a和b都翻倍并返回
create procedure myp6(inout a int, inout b int)
begin
    set a=a*2;
    set b=b*2;
end $

set @m=10$
set @n=20$
call myp6(@m, @n)$
select @m, @n$

3. 删除存储过程

语法：drop procedure 存储过程名;

drop procedure mp1;

4. 查看存储过程的信息

show create procedure myp2;

11.2 函数

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

1. 创建语法

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

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

2. 调用语法

语法：select 函数名(参数列表)

  • 无参有返回
#返回公司的员工个数
create funciton myf1() returns int
begin 
	declare c int default 0;
	SELECT 
    COUNT(*)
INTO c FROM
    employees;
    return c;
end $
select myf1() $
  • 有参有返回
#根据员工名，返回他的工资
create function myf2(empName varchar(20)) returns double
begin
    set @sal=0;#定义用户变量
    select salary into @sal from employees where last_name=empName;
    return @sal;
end $
select myf2('kochhar') $

#根据部门名，返回该部门的平均工资
create function myf3(depName varchar(20)) returns double
begin
    declare sal double;
    select avg(salary) into sal from employees e join departments d on e.department_id = d.department_id where d.department_name=deptName;
    return sal;
end $
select myf3('IT')$

3. 查看函数

show create function myf3;

4. 删除函数

drop function myf3;