MySQL进阶（一）：变量、存储过程(procedure)、函数(function)

目录

一、变量

1.1 系统变量

1.2 自定义变量

1.2.1 用户变量

1.2.2 局部变量

二、存储过程、函数

2.1 存储过程

2.1.1 创建存储过程、调用存储过程

存储过程批量造数据：

2.1.2 空参存储过程

2.1.3 IN模式的存储过程

2.1.4 out模式的存储过程

2.1.5 inout模式的存储过程

2.2 函数

mysql创建函数报错：

---

---

一、变量

1.1 系统变量

1.1全局变量:针对所有，关键字global
1.2会话变量:只针对当前会话,关键字session

1.查看所有的系统变量
show GLOBAL|session VARIABLES;#全局|会话
2.查看满足条件的部分系统变量
show GLOBAL|session VARIABLES like '%ch%';#全局|会话
3.查看指定的某个系统变量的值
select @@session.系统变量名;
select @@global.系统变量名;
4.为某个系统变量赋值
set global|session.系统变量名=value;
注意：如果是全局系统变量，需要加上global,如果是会话级别，则需要加上session,如果不加，默认会话级别的系统变量
show global VARIABLES;
show global VARIABLES LIKE "%cha%"

示例：

1.2 自定义变量

分类：
用户变量：针对当前会话有效，
局部变量：在begin end中有效；局部变量的声明需要指定类型，使用局部变
量必须在begin end关键字范围内的第一句话，局部变量不需要使用@符号(仅赋值使用select要@符号)；

1.2.1 用户变量

使用步骤：
1.声明并初始化
方式一：set @用户变量名=值;
方式二：set @用户变量名:=值；
方式三：select @用户变量名:=值；
2.赋值（更新用户变量的值）
方式一：通过set或select,同声明一样
			set @用户变量名=值;
			或set @用户变量名:=值；
			或select @用户变量名:=值；
方式二：
			select 字段 into 变量名 from 表；
3.使用
select @用户变量名;

1.2.2 局部变量

特别注意：1.局部变量定义需要使用declare关键字 而且要指定变量类型；2.局部变量在赋值的时候，与用户变量不同，不需要@符号，仅仅在使用select 需要使用@；

使用步骤：
1.声明并初始化
declare 变量名 类型;
declare 变量名 类型 default 值;

2.赋值（更新用户变量的值）
方式一：通过set或select,同声明一样
			set 用户变量名=值;
			或set 用户变量名:=值；
			或select @用户变量名:=值；
方式二：
			select 字段 into 变量名 from 表；
3.使用
SELECT 局部变量名;

二、存储过程、函数

#存储过程和函数
/*
存储过程和函数：类似于java中的方法
存储过程：简单理解：一组预先编译好的sql语句的集合，理解成批处理语句
存储过程的好处：
1.提高代码的复用性
2.简化操作
3.减少了编译次数，并且减少了和数据库服务器的连接次数，提高了效率
*/
存储过程：可以有0个返回、也可以有多个返回，适合做批量插入、批量更新；
函数：有且仅有1个返回，适合处理数据后返回一个结果

2.1 存储过程

存储过程的创建及使用都需要在命令提示符里面，在图形界面软件（如：navicat）是无法使用的；

2.1.1 创建存储过程、调用存储过程

#创建存储过程
create procedure 存储过程名(参数列表)
begin
   存储过程体
end 存储过程结束符、

………………………………………………………………………………………………注解……………………………………………………………………………………………………………………
1.存储过程体：就是一组合法有效的sql语句
2.参数列表：包含3部分，分别是：参数模式 参数名 参数类型;
3.参数模式：
    1.in :该模式修饰的参数可以作为输入，即：该参数需要调用时传入值
    2.out ：该模式修饰的参数可以作为输出，即：函数的返回值
    3.inout：该模式修饰的参数既可以作为输入又可以作为输出；
4.如果存储过程体仅仅只有一句话，begin end可以省略
5.存储过程体中的每条sql语句的结尾要求必须加分号“;”。
    存储过程的结尾可以使用 delimiter 进行设置；语法如下：
    delimiter 结束标记；
    举例：
    delimiter $ 设置之后,在存储过程体最后就需要使用$作为结束符。

示例：

步骤1：在Navicat中编辑好：设置存储过程结束符、【存储过程】语句

步骤2：在命令提示符中,登录musql
mysql -u root -p
步骤3：全选、复制上面编写的【设置结束符、创建存储过程】语句，粘贴到命令提示符，运行

截图：

此时在navicat中对应库中的【函数】项，就会有该存储过程：px

步骤4：调用存储过程,注意也需要在命令提示符中支持，而navicat中不支持
call 存储过程名(实参列表) 结束符;

存储过程批量造数据：

create PROCEDURE insert_user_50()
BEGIN
set @j=0;
start transaction;
while @j<50 do
 --your sql

set @j=@j+1;

END WHILE;

commit;
END 

示例二：

 

2.1.2 空参存储过程

#空参：顾名思义，就是没有参数的存储过程
案例：插入到admin表中五条记录【实际使用可能是一下插入上万条记录】
delimiter $;
create PROCEDURE my_inpu_girl()
BEGIN
	INSERT into admin (username,`password`) 
	VALUES('john1','0000'),('john2','0000'),('john3','0000'),('john4','0000'),('john5','0000');
END $

#调用的该存储过程也不要参数
CALL my_inpu_girl() $;

2.1.3 IN模式的存储过程

#创建带in模式参数的存储过程
#案例1：创建存储过程
#多个参数以逗号隔开
create procedure myp2(in username VARCHAR(20),in password varchar(20))
begin
   declare res varchar(20) default '';#声明并初始化变量
	SELECT count(*) into res  #赋值变量
	FROM admin
	WHERE admin.username = username
	and admin.`password`=password;#存储过程体中使用参数名直接引用
    select res;#使用变量
END $

2.1.4 out模式的存储过程

#案例：根据女神名，返回对应男神名
delimiter $
create procedure myp5(in beautyname varchar(20),out boyname varchar(20))
begin

end $

2.1.5 inout模式的存储过程

2.2 函数

函数的关键字： function

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

创建语法：

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

------------------------------注释---------------------------------
参数列表：包含两个部分：参数名 参数类型
函数体：肯定会有return语句；如果没有会报错；

函数调用：

select 函数名(参数列表)

mysql创建函数报错：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

访问：ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declar_冷凝娇的博客-CSDN博客_error 1418 (hy000): this function has none of dete