变量 - 存储过程 - 函数
时间：2020-07-17 18:19:50
收藏：0
阅读：46
变量 - 存储过程 - 函数
变量
分为：
/*
系统变量：
全局变量 global
会话变量 session
自定义变量：
用户变量
局部变量
*/
系统变量
分为全局变量（global）和会话变量（session）
查看、赋值、使用
# 系统变量
/*
说明：变量由系统提供，不是用户定义，属于服务器曾便
使用的语法：
*/
1. 查看所有系统变量
show global|【session】 variables;
2. 查看满足条件的部分系统变量
show global|【session】 variables like ‘%char%‘;
3. 查看指定的某个系统变量的值
select @@global\【session】; 系统变量名；
4. 为某个系统变量赋值
方式1：
set global|【session】.系统变量名 = 值;
方式2：
set @@global|【session】.系统变量名 = 值;
-- 注意
如果是全局级别，则需要加global，如果是会话级别，则需要加session。如过不写，默认session。
实例：
-- 1.全局变量
/*
作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）都有效，但不能跨重启
*/
# 1. 查看所有的全局变量
SHOW GLOBAL VARIABLES;
# 2. 查看部分的全局变量
SHOW GLOBAL VARIABLES LIKE ‘%char%‘;
# 3. 查看指定的全局变量的值
SELECT @@global.autocommit;
SELECT @@tx_isolation;
# 4. 为某个指定的全局变量赋值（事务不自动开启）
SET @@global.autocommit=0
-- 2，会话变量
/*
作用域：针对于当前会话（连接）有效
*/
# 1. 查看所有的会话变量
SHOW VARIABLES
SHOW SESSION VARIABLES;
# 2. 查看部分的会话变量
SHOW VARIABLES LIKE ‘%char%‘
SHOW SESSION VARIABLES LIKE ‘%char%‘
# 3. 查看指定的某个会话变量
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;
# 4. 为某个会话变量赋值(更改隔离级别)
方式一：
SET @@session.tx_isolation=‘read-uncommitted‘;
方式二：
SET SESSION tx_isolation=‘read-committed‘;
自定义变量
分为用户变量和局部变量
自定义变量
/*
说明：变量是由用户自己定义的，不是由系统的
使用步骤：
声明
赋值
使用（查看，比较，运算等）
*/
用户变量
# 1. 用户变量
/*
作用域：针对于当前会话（连接）有效，同系统变量中会话变量的作用域
应用在任何地方，也就是begin end
*/
# 1. 声明并初始化
SET @用户变量名=值
SET @用户变量名:=值
SELECT @用户变量名:=值
# 2. 赋值（更新用户变量的值）
# 方式一：
# 通过set 或 select ，跟声明初始化相同（三种方式）
SET @用户变量名=值
SET @用户变量名:=值
SELECT @用户变量名:=值
#方式二：
# 通过select into
SELECT 字段 INTO 变量名
FROM 表
# 3. 使用（查看用户变量的值）
SELECT @用户变量名
-- 实例：
SET @name=‘haha‘; # 声明并初始化
SET @name=100; # 修改（赋值）
SELECT COUNT(*) INTO @count # 赋值
FROM employees;
SELECT @count; # 查看
局部变量
# 2. 局部变量
/*
作用域：仅仅在定义它的begin end中有效
应用在 begin end 中的第一句话
*/
# 1. 声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
# 2. 赋值（更新用户变量的值）
# 方式一：
# 通过set 或 select ，跟声明初始化相同（三种方式）
SET 局部变量名=值
SET 局部变量名:=值
SELECT @局部变量名:=值
#方式二：
# 通过select into
SELECT 字段 INTO 变量名
FROM 表
# 3，使用
SELECT 局部变量名
对比与实例
-- 对比
作用域 第一和使用的位置 语法
用户变量 当前会话 会话中的任何地方 必须加@符号。不限定类型
局部变量 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;
存储过程（procedure）
核心代码（创建+调用）：
# 创建
create procedure 存储过程名(参数列表)
begin
存储过程体（一组合法的SQL语句）
end $
# 调用
call 存储过程名（实参列表）
注：$为结束标记，可以由delimiter设定
解释：
存储过程和函数
/*
存储过程和函数：类似于jave中的方法，golang中的函数
好处：
1. 提高代码的重用性
2. 简化操作
3. 减少了编译次数，减少了和数据服务的连接次数，提高了效率
*/
/*
存储过程
含义：一组预先编译好的sql语句的集合，理解成批处理语句
语法： 创建 + 使用
注意：须在在命令行中调用，SQLyog不支持
*/
创建
/*
create procedure 存储过程名(参数列表)
begin
存储过程体（一组合法的SQL语句）
end $
如下所示：
*/
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END $
/*
注：
1. 参数列表包含三部分：
参数模式 参数名 参数类型
参数模式包括：
in ：该参数可以作为输入，即传入参数
out ： 该参数可以作为输出，即返回值
inout ：既可以作为输入又可以作为输出
2. 如果存储过程体仅有一句话，则 begin end 可以省略
3. 存储过程体中的每条 sql语句 结尾必须加 分号（;）
所以，存储过程的结尾可以使用 delimiter 重新设置结束标记(一般开始存储过程前先使用该语句设定结束标记)
语法（此处设定为$）：
delimiter $
*/
调用
-- 调用语法
CALL 存储过程名（实参列表）;
-- 空参列表：
# 案例一：插入admin表中五条记录
# 1. 查看一下原来的
SELECT * FROM admin;
# 2. 操作
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username, `password`)
VALUES(‘join1‘, ‘0000‘),(‘join2‘, ‘0000‘),(‘join3‘, ‘0000‘),(‘join4‘, ‘0000‘),(‘join5‘, ‘0000‘);
END $
# 3. 调用
CALL myp1() $
# 4. 查看
SELECT * FROM admin$
-- 创建带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(‘柳岩‘) $
CALL myp2(‘王语嫣‘) $
# 出现errotr：不正确字符串，执行下面语句
SET NAMES gbk;
# 案例二： 创建存储过程实现，用户是否登陆成功
DELIMITER $ # 先结束一下
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT ‘‘; # 声明并初始化 自定义变量 中的 局部变量 result
SELECT COUNT(*) INTO result # 赋值
FROM admin
WHERE admin.username = username
AND admin.`password` = `password`;
SELECT IF(result>0,‘成功‘,‘失败‘); # 使用
END $
# 调用
CALL myp3(‘侠奢‘,‘66666‘) $ # 返回失败
-- 3. 创建带out模式的存储过程
# 案例1：根据女生名字，返回对应的男生名
CREATE PROCEDURE myp5(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 myp5(‘小昭‘, @bName) $
SELECT @bName $
# 案例2 ：根据女生名，返回对应的男生名和男生魅力值
CREATE PROCEDURE myp6(IN beautName VARCHAR(20), OUT boyName VARCHAR(20), OUT boy, 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 myp6(‘小昭‘, @bName, @uCp) $
SELECT @bname, @uCP $
# 创建带inout的存储过程
# 案例1：传入a和b两个值，最终a和b翻倍返回
DELIMITER $
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 $
删除与查看
-- 删除存储过程
# 语法：drop procedure 存储过程名
DROP PROCEDURE myp3;
-- 查看存储过程的信息
DESC myp2; -- 错误方法
SHOW CREATE PROCEDURE myp2; -- 正确方法
综合案例
-- 一、创建存储过程实现传入用户名和密码，插入到admin表中
CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username,PASSWORD)
VALUES(username,loginpwd);
END $
-- 二、创建存储过程实现传入女神编号，返回女神名称和女神电话
CREATE PROCEDURE test_pro2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))
BEGIN
SELECT b.name ,b.phone INTO NAME,phone
FROM beauty b
WHERE b.id = id;
END $
-- 三、创建存储存储过程或函数实现传入两个女神生日，返回大小
CREATE PROCEDURE test_pro3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)
BEGIN
SELECT DATEDIFF(birth1,birth2) INTO result;
END $
-- 四、创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回
CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,‘%y年%m月%d日‘) INTO strDate;
END $
CALL test_pro4(NOW(),@str)$
SELECT @str $
-- 五、创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串
如 传入 ：小昭
返回： 小昭 AND 张无忌
DROP PROCEDURE test_pro5 $
CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))
BEGIN
SELECT CONCAT(beautyName,‘ and ‘,IFNULL(boyName,‘null‘)) INTO str
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name=beautyName;
SET str=
END $
CALL test_pro5(‘柳岩‘,@str)$
SELECT @str $
-- 六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录
DROP PROCEDURE test_pro6$
CREATE PROCEDURE test_pro6(IN startIndex INT,IN size INT)
BEGIN
SELECT * FROM beauty LIMIT startIndex,size;
END $
CALL test_pro6(3,5)$
函数（function）
# 函数
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
作用：
1. 提高代码重用性
2. 简化操作
3. 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
与 存储过程 的区别：
存储过程：可以有0个返回，也可以有多个返回
适合做 批量插入，批量更新等
函数：只能有1个返回
适合处理数据后返回一个结果（一般增删改操作）
*/
创建
# 创建
/*
create function 函数名(参数列表) returns 返回类型
begin
end $
注：
1. 参数列表 包含两部分 ： 参数名 参数类型
2. 函数体：要存在return语句 --- return 返回值;
3. 当函数题只有一句话，begin end 可以省略
4. 用delimiter 设置结束标记 一般用$
*/
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
END $
调用
# 调用
SELECT 函数名(参数列表)
# 无参有返回
-- 案例：返回公司的员工个数
DELIMITER $ # 设置结束标记
CREATE FUNCTION myf1() RETURN INT
BEGIN
DECLARE c INT DEFAULT 0; # 定义变量
SELECT COUNT(*) INTO c # 赋值
FROM employees;
RETURN c;
END $
SELECT myf1() $
# 有参有返回
-- 案例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(‘kochhar‘) $
-- 案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName 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
# 创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM = num1 + num2;
RETURN SUM;
END $
# 调用
SELECT test_fun1(1,2) $
查看与删除
# 查看函数
SHOW CREATE FUNCTION myf3;
# 删除函数
DROP FUNCTION myf3;
原文：https://www.cnblogs.com/newbase/p/13331375.html
评论（0）