变量&存储过程&函数
- 一、变量
- 1.1 系统变量
- 1.2 自定义变量
- 1.2.1 用户变量
- 1.2.2 局部变量
- 1.2.3 对比用户变量和局部变量
- 案例:声明两个用户变量并赋初始值,求和并打印
- 二、存储过程
- 2.1 创建语法
- 2.2 调用语法
- 2.2.1 空参列表
- 案例:插入到admin表中1条记录
- 2.2.2 创建带in模式参数的存储过程
- 案例1:创建存储过程,实现 根据女神名查询对应的男神信息
- 案例2:创建存储过程实现,用户是否登录成功
- 2.2.3 创建带out模式的存储过程
- 案例1:根据女神名返回对应的男神名
- 案例2:根据女神名,返回对应的男神名和男神魅力值
- 2.2.4 创建带inout模式参数的存储过程
- 案例1:传入a和b两个值,最终a和b都翻倍并返回
- 2.2.5 删除存储过程
- 2.2.6 查看存储过程的信息
- 三、函数
- 3.1 创建语法
- 3.2 调用语法
- 3.2.1 无参有返回
- 案例:返回员工的个数
- 3.2.2 有参有返回
- 案例1:根据员工名返回他的工资
- 案例2:根据部门名,返回该部门的平均工资
- 3.2.3 查看函数
- 3.2.4 删除函数
一、变量
1.1 系统变量
说明: 变量是由系统提供,不是用户定义,属于服务器层面
- 全局变量 GLOBAL
- 作用域:服务器每次启动将为所有的全局变量赋初始值,针对所有会话(连接)有效,但不能跨重启
- 会话变量 SESSION(默认)
- 作用域:仅针对于当前会话(连接)有效
语法:
1.查看所有的系统变量
SHOW GLOBAL |SESSION VARIABLES;
2.查看满足条件的部分系统变量
SHOW GLOBAL |SESSION VARIABLES LIKE '%char%';
3.查看指定的某个系统变量的值
SELECT @@global|session.系统变量名;
4.为某个系统变量赋值
方式一:
SET GLOBAL|SESSION 系统变量名=值;
方式二:
SET @@global|session.系统变量名=值;
1.2 自定义变量
说明: 变量是用户自定义的不是由系统的
使用步骤:
声明、赋值、使用(查看、比较、运算等)
1.2.1 用户变量
作用域:针对于当前会话(连接)有效,可以应用在任何地方
①声明并初始化
set @用户变量名=值;或
set @用户变量名:=值;或
select 用户变量名:=值;
②赋值
方式一:通过set或select
set @用户变量名=值;或
set @用户变量名:=值;或
select 用户变量名:=值;
方式二:通过select into
select 字段 into @变量名
from 表;
③使用
select @用户变量名;
1.2.2 局部变量
作用域:仅仅在定义它的begin end中的第一句话有效
①声明
declare 变量名 类型;
declare 变量名 类型 default 值;
②赋值
方式一:通过set或select
set 局部变量名=值;或
set 局部变量名:=值;或
select @局部变量名:=值;
方式二:通过select into
select 字段 into 局部变量名
from 表;
③使用
select 局部变量名;
1.2.3 对比用户变量和局部变量
作用域 | 定义和使用的位置 | 语法 | |
用户变量 | 当前会话 | 会话中的任何地方 | 必须加@,不用限定类型 |
局部变量 | begin end中 | 只能在begin end中,且为第一句话 | 一般不加@,需要限定类型 |
案例:声明两个用户变量并赋初始值,求和并打印
SET @m=1;
SET @n=2;
SET @sum=@m+@n;
SELECT @sum;
二、存储过程
含义: 一组预先编译好的SQL语句的集合,理解成批处理语句
2.1 创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
注意:
1.参数列表包含三部分
参数模式 | 参数名 | 参数类型 | |
举例: | IN | stuname | VARCHAR(20) |
参数模式:
- IN 该参数可以作为输入,需要调用方传入值
- OUT 该参数可以作为输出,可以作为返回值
- INOUT 既可以作为输入又可以作为输出
2.如果存储过程体仅仅只有一句话: BEGIN END 可省略
3.存储过程体中的每条SQL语句的结尾必须加分号;
存储过程的结尾使用 DELIMITER 重新设置结束标记
DELIMITER 结束标记
如: DELIMITER $
2.2 调用语法
CALL 存储过程名(实参列表);
2.2.1 空参列表
案例:插入到admin表中1条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username, `password`)
VALUES('lily','0000');
END $
CALL myp1()$ #调用存储过程
2.2.2 创建带in模式参数的存储过程
案例1:创建存储过程,实现 根据女神名查询对应的男神信息
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('柳岩') $
案例2:创建存储过程实现,用户是否登录成功
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= usename
AND admin.`password`=`password`;
SELECT IF(result>0,'成功','失败') ; #使用
END $
CALL myp3('张飞','8888')$
2.2.3 创建带out模式的存储过程
案例1:根据女神名返回对应的男神名
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('柳岩',@bname) $
SELECT @bname$
案例2:根据女神名,返回对应的男神名和男神魅力值
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 myp4('柳岩',@bname,@usercp) $
SELECT @bname, @usercp $
2.2.4 创建带inout模式参数的存储过程
案例1:传入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=10$
SET @b=20$
CALL myp6(@a,@b)$
SELECT @a,@b$
2.2.5 删除存储过程
DROP PROCEDURE 存储过程名; #一次只能删一个
2.2.6 查看存储过程的信息
SHOW CREATE PROCEDURE 存储过程名;
三、函数
含义: 一组预先编译好的SQL语句的集合,理解成批处理语句
区别:
- 存储过程:可以有0个返回,也可以有多个返回;适合做批量插入、批量更新
- 函数:只能有1个返回,适合做处理数据后返回一个结果
3.1 创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意:
1.参数列表包含 参数名 参数类型 两部分;
2.函数体肯定会有return语句,如果没有会报错
3.函数体中只有一句话时, BEGIN END 可省
4.使用 DELIMITER 重新设置结束标记
3.2 调用语法
SELECT 函数名(参数列表)
3.2.1 无参有返回
案例:返回员工的个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义变量
SELECT COUNT(*) INTO c #赋值
FROM employees;
RETURN c;
END $
SELECT myf1() $
3.2.2 有参有返回
案例1:根据员工名返回他的工资
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('k_ing')$
案例2:根据部门名,返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE a DOUBLE;
SELECT AVG(salary)
FROM employees e INNER JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_name=deptName;
RETURN a;
END $
SELECT myf3('IT')$
3.2.3 查看函数
SHOW CREATE FUNCTION 函数名;
3.2.4 删除函数
DROP FUNCTION 函数名;