Mysql 变量 存储过程 自定义函数的使用

一. 变量

系统变量:

• 全局变量
• 会话变量

自定义变量:

• 用户变量
• 局部变量

1.会话变量与全局变量

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

使用语法:
# 1. 查看所有的系统变量
show [global] | [session] variables;  (不加参数默认查看session)

# 2. 查看满足条件的部分系统变量
show [global] | [session] variables like '%条件%';

# 3. 查看指定的某个系统变量的值
select @@[global] | [session].系统变量名;

# 4. 为某个系统变量赋值
方式一:
set [global] | [session] 系统变量名 = 值;

方式二:
set @@[global] | [session] 系统变量名 = 值;

## 注意 ##
#全局变量的作用域: 服务器每次启动会为所有的全局变量赋初始值,针对所有的会话连接有效,但是不能跨重启(跨重启就需要修改配置文件的默认值.)

会话变量的作用域: 仅针对当前的会话连接有效.

2.用户变量(临时变量)

# 说明: 变量是用户自定义的.不是系统的

# 一、定义语法
=====================
'方式1'：使用set,定义形式
set @用户变量名=值;
set @用户变量名:=值;

实例如下:
set  @temp=10;
SELECT @temp;

'方式2'：使用select,定义形式
select @用户变量名:=值;

实例如下:
SELECT  @temp1:=10;
SELECT  @temp1;

# 赋值(更新用户变量的值)
set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;
select 字段 into 变量名 from 表;

例子:
select count(*) into @count from 表;

# 使用(查看,比较,运算等)
select @变量名

# 作用域: 仅针对当前的会话连接有效.

3.局部变量

## 作用域: 仅针在定义它的begin end中有效.

使用步骤: 
# 应用在begin end 中的第一句话!

# 声明的方式:
declare 局部变量名 类型;
declare 局部变量名 类型 default 值;

# 赋值(更新用户变量的值)
set 局部变量名=值;     #注意这里不用@
set 变量名:=值;        #注意这里不用@
select @用户变量名:=值;    #注意这里使用@
select 字段 into 变量名 from 表;

# 使用(查看,比较,运算等)
select 变量名      #注意这里不用@

注意: 局部变量需放在begin end中才可以生效,直接使用会报错.

二. 存储过程和函数

存储过程的定义:
一组预先编译好的sql语句的集合,相当于批处理语句.

好处:
1. 提高代码重用性
2. 简化操作
3. 会减少编译次数,减少了和服务器的连接次数,增加效率

1.存储过程的创建

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

# 注意:
1. 参数列表包含三部分:
参数模式 参数名 参数类型

2. 如果存储过程只有一句话,begin end就可以省略.

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

4. 存储过程的结尾可以使用 delimiter 重新设置定义.定义之后的语句都用此标识结尾.
语法: delimiter 结束标记   (这里不用分号)

# 参数模式:
in   该参数可以作为输入(需要调用方传入值)
out  该参数可以作为输出(该参数可以返回值)
inout 该参数可以作为输入也可以输出

参数举例
... in 表名 varchar(20)

2.多参数模式的创建和调用

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

# 1.空参列表 (调用的时候不提供参数)
案例: 插入四条记录到表名1中
=====================================
select * from 表名1

delimiter create procedure myp1()
begin    insert into 表名1(username,password)
    values('Tom','000'),('Bom','000'),('Com','000'),('Dom','000');
end

call myp1()select * from 表名1
=====================================


# 2.创建带in模式参数的存储过程 (in在这里可以省略不写,默认就是in参数)
例: 创建存储过程实现根据女神名,查询对应男神信息.
create procedure myp2(in 女神名变量1 varchat(20))
begin 
    select 男表.*
    from 男表 bo
    right join 女表 n on bo.id=n.男友id;
    where n.name=女神名变量1
end call myp2('条件')
=====================================


# 3.创建带多个in模式参数的存储过程
例: 创建存储过程实现用户是否登录成功.
create procedure myp3(in username变量 varchar(20),in password变量 varchar(20))
begin
    declare res int default 0;  #声明res变量并初始化
    select count(*) into res    # 给res变量赋值
    from 用户表 y
    where y.username = username变量
    and y.password = password变量;
    select if(res>0,'成功','失败');     #使用变量判断
end call myp3('条件用户名','条件密码');      #调用存储过程
=====================================


# 4.创建带out模式的存储过程
例: 根据女神名,返回对应的男神名.
create procedure myp5(in 女神名变量 varchar(20),out 男神名变量 varchar(20))
begin
    select bo.男神名 into 男神名变量
    from 男神表 bo    inner join 女神表 n on bo.id=n.男友id
    where n.name=女神名变量;
end
set @bname # 用于接受out中男神名变量的返回值(可以省略不定义)
call myp5('条件女神名',@bname)
select @bname =====================================


# 5.创建带多个out模式的存储过程
例: 根据女神名,返回对应的男神名和男神魅力值
create procedure myp6(in 女神名变量 varchar(20),out 男神名变量 varchar(20),out 男神魅力值变量 int)
begin
    select bo.男神名,bo.魅力值 into 男神名变量,男神魅力值变量
    from 男神表 bo    inner join 女神表 n on bo.id=n.男友id
    where n.name=女神名变量;
end
call myp5('条件女神名',@bname,@b魅力值) select @bname,@b魅力值
=====================================


# 6.创建带inout模式的存储过程
例: 传入a和b,最终a和b翻倍返回
create procedure myp8(inout a int,inout b int)
begin
    set a=a*2;
    set b=b*2
end set @m=10
set @n=20 call myp8(@m,@n)
select @m,@n $

3.经典案例:

4.存储过程的删除

# 语法
drop procedure 存储过程名;

#一次只能删除一个.

5.存储过程的查看

# 查看库中的存储过程和函数
show procedure status; //存储过程
show function status;     //函数

# 语法
show create procedure 存储过程名;

# 注意!! 存储过程存储在系统的mysql表下的proc表中.

6.存储过程的修改

一般不能修改.推荐删除重建.

案例 :

案例:

三. 自定义函数

1.创建

create function 函数名(参数列表 参数类型) returns 返回类型
begin
    函数体...;
    return 值;
end $

# 注意:
函数体: 一定会有return语句,否则会报错.

2.调用

select 函数名(参数列表);
执行函数中的语句显示返回值

# 1.无参有返回值
案例 : 返回公司的员工个数
delimiter create function myf() returns int
begin
    declare c int default 0;  #定义变量
    select count(*) into c    #赋值给变量
    from 员工表;
    return c;
end
select myf() # 2.有参有返回
案例 : 根据员工名,返回他的工资.
create function myf(员工名变量 varchar(20)) returns double #double是工资对应的数据类型
begin
    set @sal=0;   #定义用户变量
    select 工资 into @sal   #赋值
    from 工资表
    where 姓名 = 员工名变量;
    return @sal;
end
select myf('员工名') $

3.查看函数

show create function 函数名;

# 注意!! 存储过程存储在系统的mysql表下的proc表中.

4.删除函数

drop function 函数名;

5.修改函数

与存储过程相同,不建议修改,一般删除重建.