MySQL：存储过程和函数
变量
系统变量
变量由系统提供，不是用户自定义的，属于服务器层面
-
- 全局变量
- 会话变量
# 如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，则默认是会话 # 查看全局变量 SHOW GLOBAL VARIABLES; SHOW GLOBAL VARIABLES LIKE "%char%"; # 查看会话变量 SHOW SESSION VARIABLES; # 默认是会话变量 SHOW SESSION VARIABLES LIKE "%char%"; # 查看指定的某个系统变量的值 SELECT @@系统变量名； # 默认查看的是会话变量 SELECT @@GLOBAL.系统变量名； # 为系统变量赋值 SET global|[session] 系统变量名=值; # 默认是会话变量 SET @@global|[session].系统变量名=值; # 默认是会话变量
自定义变量
用户自定义的变量
用户变量：针对于当前会话连接有效，同于会话变量的作用域
# 申明并初始化 SET @用户变量名=值； SET @用户变量名：=值； SELECT @用户变量名：=值； # 赋值或更新用户变量的值 SET @用户变量名=值； SET @用户变量名：=值； SELECT @用户变量名：=值； SELECT 字段 INTO 变量名 FROM 表 SELECT COUNT(*) INTO @count1 FROM student; # 查看用户变量名 SELECT @用户变量名；
局部变量：仅仅在begin end中有效，应用在begin end的第一句话
# 声明 DECLARE 变量名 类型； DECLARE 变量名 类型 DEFAULT 值； # 赋值 SET 局部变量名=值 SET 局部变量名：=值 SELECT @局部变量名：=值 SELECT 字段 INTO @局部变量名 FROM 表； #查看 SELECT 局部变量名
局部变量和用户变量
-
- 用户变量： 当前会话 会话的任何地方定义和使用 必须加@符号，不用限定类型
- 局部变量： BEGIN END中 只能在BEGIN END中，且为第一句话 一般不加@符号，需要限定类型
存储过程
介绍
一组预先编译好的SQL语句的集合，理解成批处理语句
- 提高代码的重用性
- 简化操作
- 减少了编译次数，并且减少了和数据库服务器的连接次数，提高了效率
创建
CREATE PROCEDURE 存储过程名（参数列表） BEGIN 存储过程体（一组合法的SQL语句） END
注意：
- 参数列表包含三个部分 参数模式 参数名 参数类型
- 如果存储过程体仅仅只有一条语句，BEGIN END可以省略
- 存储过程体中的每条SQL语句的结尾必须要加分号
- 存储过程的结尾可以使用DELIMITER重新设置
- 参数模式：
- IN： 该参数可以作为输入，也就是该参数需要调用方法传入值
- OUT： 该参数可以作为输出，也就是该参数可以作为返回值
- INOUT： 既可以作为输入也可以作为输出，既要传入值，也可以返回值
调用
CALL 存储过程名（实参列表）；
案例
/* 1. 空参列表 案例1： 插入到admin表中五条记录 DELIMITER $ CREATE PROCEDURE myp1() BEGIN INSERT INTO admin(name, `password`) VALUES('jhon1', "000"),('jhon2',"001"); END $ CALL myp1()$ SELECT * FROM admin$ 2. IN模式参数, 默认是IN 案例： 创建存储过程实现 根据girl name 查询对应的boy信息 CREATE PROCEDURE myp2(IN g_name VARCHAR(20) ) BEGIN SELECT b.* FROM boys b RIGHT JOIN girls g ON b.id=g.boy_id WHERE g.name=g_name; END $ CALL myp2('Mary')$ 案例： 创建存储过程实现，用户是否登入成功 CREATE PROCEDURE myp3(IN name VARCHAR(20), in PASSWORD VARCHAR(20)) BEGIN DECLARE res INT DEFAULT ''; select count(*) into res from admin where admin.name=name and admin.password=PASSWORD; SELECT IF(res>0,'successfule','failed'); END $ CALL myp3('root','123')$ 3. OUT模式的存储过程 案例： 根据girl name ，返回boy name CREATE PROCEDURE myp5(IN girl_nam VARCHAR(20), OUT boy_name VARCHAR(20), OUT age INT) BEGIN SELECT b.name,b.age INTO boy_name,age FROM boys b INNER JOIN girls g ON b.id=g.boy_id WHERE g.name=girl_name; END $ CALL myp5('Mary', @bname, @age)$ SELECT @banme,@age$ 4. 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 @a=20$ SET @b=20$ CALL myp6(@a,@b)$ SELECT @a,@b$ *
删除存储过程
DROP PROCEDURE 存储过程名
查看存储过程
SHOW CREATE PROCEDURE 存储过程名
函数
介绍
一组预先编译好的SQL语句的集合，理解成批处理语句
- 提高代码的重用性
- 简化操作
- 减少了编译次数，并且减少了和数据库服务器的连接次数，提高了效率
对比存储
- 存储过程： 可以有0或多个返回， 适合做批量插入、批量更新
- 函数 ： 有且仅有一个返回，适合做处理数据后返回一个结果
创建
CREATE FUNCTION函数名（参数列表） RETURNS 返回类型 BEGIN 函数体 END
注意：
- 参数列表包含两部分： 参数名 参数类型
- 函数体必须有return语句： return 值
- 函数体仅有一句话时，可以省略begin end
- 使用delimiter语句设置结束标记
调用
SELECT 函数名（参数列表）
案例
/* 1. 无参数返回 案例： 返回员工个数 DELIMITER $ CREATE FUNCTION myf1 RETURNS INT BEGIN DECLARE c INT DEFAULT 0; SELECT COUNT(*) INTO c FROM employees; RETURN c; END $ SELECT myf1()$ 2. 有参数返回 案例： 根据员工名，返回他的工资 CREATE FUNCTION(name VARCHAR(20)) RETURNS DOUBLE BEGIN SET @sal=0; SELECT salary INTO @sal FROM employees e WHERE e.name =name; RETURN @sal; END SELECT myf2('jhon')$ */
查看函数
SHOW CREATE FUNCTION 函数名
删除函数
DROP FUNCTION 函数名；
