Mysql之变量、存储过程、函数

最新推荐文章于 2022-06-29 10:49:45 发布

兰交余文乐

最新推荐文章于 2022-06-29 10:49:45 发布

阅读量997

点赞数

分类专栏： mysql 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_56674682/article/details/121424988

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

Mysql之变量、过程、函数

变量分为系统变量和自定义变量

系统变量：

说明：系统变量是由系统提供的，不是用户自定义的，属于服务器层面

1.全局变量 2.会话变量

使用语法：

1.查看所有的全局/[局部变量] 默认是局部变量

SHOW global|[session] VARIABLES;

2.查看某些满足条件的系统变量

SHOW  GLOBAL|[session] VARIABLES LIKE '%char%';

3.查看指定的某个系统变量的值

SELECT  @@global|[session].transaction_isolation;

4. 为系统变量赋值

方式一：

set global|[session] 系统变量名 = 值;

SET GLOBAL autocommit=0;
SHOW GLOBAL VARIABLES LIKE 'autocommit';

方式二：

set @@global|[session].变量名 = 值;

SET @@global.autocommit = 1;

global和session作用域的讲解：

global的作用域：服务器每次重启都会对全局变量进行一个赋值，所以全局变量的修改只对所有会话(连接)有效，不能跨重启

session的作用域：仅对当前的会话有效

自定义变量：

说明：由用户自己定义，不是由系统

使用：①声明②赋值③使用

1.用户变量

声明：

set @变量名=值;

set @变量名:=值;

select @变量名:=值;

赋值：

方式一：

set @变量名=值;

set @变量名:=值;

select @变量名:=值;

方式二:

select 字段 into @变量名 from table where 条件;

使用：

select @变量名;

用户变量的作用域：当前会话有效

2.局部变量

声明：

declare 变量名数据类型 [default 默认值];

赋值：

set 变量名 = 值;

set 变量名 := 值;

select @变量名 :=值;

select 字段 into 变量名 from table;

使用：

select 变量名;

局部变量的作用域：只能在begin-end中有效

用户变量与局部变量的对比：

作用域定义与使用语法

用户变量当前会话任何地方必须加上@，不限定数据类型

局部变量 BEGIN END BEGIN-END且为第一句一般不用加@，限定数据类型

案例：声明两个变量并赋初始值，求和，并打印

#使用用户变量实现
SET @num1=10;
SET @num2=5;
SET @sum=@num1+@num2;
SELECT @sum;

#使用局部变量实现,由于局部变量的作用域仅在begin-end中所以这里只是个局部代码
DECLARE num1 INT DEFAULT 0;
DECLARE num2 INT DEFAULT 0;
SET num1=10;
SET num2=5;
SELECT num1+num2;

存储过程：procedure

说明：一组预先编号的sql语句，可以看成是批处理

好处:

1.提高代码的重用性

2.简化操作

3.减少编译次数和与服务器连接的次数，提高效率

语法：

create procedure 过程名(参数列表)

begin

过程体;

end

细节：

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

参数模式 :in\out\inout

in:该参数可以作为输入，需要调用方传入参数

out:该参数可以作为输出，也就是该参数可以作为返回值

inout：该参数即可输入也可输出

2.如果过程体中仅有一句语句，begin和end可以省略

存储体的每条语句结构需要加上' ; '

存储过程的结尾可以使用delimiter重新设置

语法：

delimiter 结束标记

delimiter $

调用语法：call 存储过程名(实参列表);

说明：存储过程、函数需要在命令行窗口执行，数据库管理软件并不支持

案例1：向admin表中插入五条记录(无参列表)

DELIMITER $
CREATE PROCEDURE insert_admin_pro()
BEGIN
	INSERT 	INTO admin VALUES(NULL,'Kobe','0000'),(NULL,'Oneal','0000'),(NULL,'Lebron','0000'),(NULL,'Wade','0000'),(NULL,'Paul','0000');
END $
CALL insert_admin_pro()$

#案例：创建存储过程，实现根据女神名，查询对应的男生信息(带in模式参数的存储过程)

CREATE PROCEDURE select_boysinfo_pro(IN NAME VARCHAR(20))
BEGIN
	SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id=b.boyfriend_id WHERE b.name=NAME;
END $
CALL select_boysinfo_pro('热巴')$

案例：创建存储过程，实现用户是否登录成功

CREATE PROCEDURE login_pro(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;
	SELECT COUNT(*) INTO result  FROM admin  WHERE admin.`username`=username AND admin.`password`=PASSWORD;
	SELECT IF(result>0,'登录成功','登录失败');
END $
CALL login_pro('Kobe','0000')$

案例：根据女神名，返回对应的男生名(创建带out模式的存储过程)

CREATE PROCEDURE byGirlName_getBoysName_pro(IN beautyName VARCHAR(20),OUT  boysName VARCHAR(20))
BEGIN
	SELECT boys.boyName INTO boysName FROM boys RIGHT JOIN beauty ON boys.id=beauty.boyfriend_id WHERE beauty.`name`=beautyName;
END $
CALL byGirlName_getBoysName_pro('小昭',@boyName)$
SELECT @boysName$

案例：传入a和b两个值，最终a和b都翻倍并返回(带inout参数模式的存储过程)

CREATE PROCEDURE double_pro(INOUT a INT,INOUT b INT)
BEGIN
	SELECT a*2,b*2 INTO a,b;
END $
SET @a=10$
SET @b=5$
CALL double_pro(@a,@b)$
SELECT @a,@b$

1.创建存储过程或函数实现传入用户名和密码,插入到admin表中

CREATE PROCEDURE my_pro1(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	INSERT INTO admin VALUES(NULL,username,PASSWORD);
END $
CALL my_pro1('阿粉','1111')$

2.创建存储过程或函数实现传入女神编号，返回女神名称和女神电话

CREATE PROCEDURE my_pro2(IN id INT,OUT beautyName VARCHAR(20),OUT phone VARCHAR(11))
BEGIN
	SELECT beauty.name,beauty.phone INTO beautyName,phone FROM beauty LEFT JOIN boys ON beauty.boyfriend_id=boys.id WHERE beauty.id=id;
END $
CALL my_pro2(4,@beautyName,@phone)$
SELECT @beautyName,@phone$

3.创建存储过程或函数实现传入女神编号返回两个女神生日的大小

CREATE PROCEDURE my_pro3(IN id1 INT,IN id2 INT,OUT result CHAR(2))
BEGIN
	DECLARE r INT;
	DECLARE b1 DATETIME;
	DECLARE b2 DATETIME;
	SELECT borndate INTO b1 FROM beauty WHERE id=id1 ;
	SELECT borndate INTO b2 FROM beauty WHERE id=id2;
	SELECT DATEDIFF(b1,b2) INTO result;
END $
CALL my_pro3(1,2,@result)$
SELECT @result$

4.创建存储过程实现传入一个日期，格式化成xx年xx月xx日并返回

CREATE PROCEDURE my_pro4(IN d DATETIME,OUT result VARCHAR(20))
BEGIN
	SELECT DATE_FORMAT(d,'%y年%m月%d日') INTO result;
END $
CALL my_pro4('2021-11-19',@result)$
SELECT @result$

5.创建一个存储过程实现传入女神的名称，返回：女神 AND 男神格式的字符串

传入：小昭

返回：小昭 AND 张无忌

CREATE PROCEDURE my_pro5(IN NAME VARCHAR(20))
BEGIN
	SELECT CONCAT(NAME,'  AND ',boyName) FROM beauty LEFT JOIN boys ON beauty.boyfriend_id=boys.id WHERE beauty.name = NAME;
END $
CALL my_pro5('小昭')$

6.创建存储过程，根据传入的条目数和起始索引，查询beauty表的记录

CREATE PROCEDURE my_pro6(IN s INT,IN size INT)
BEGIN
	SELECT * FROM beauty LIMIT s,size;
END $
CALL my_pro6(0,5)$

存储过程的删除：

drop procedure 存储过程名;

存储过程的查看：

show create procedure 存储过程名;

函数：

说明：一组预先编译好的sql语句，可以看成批处理

优点：

1.提高代码的复用性

2.简化操作

3.减少编译次数和数据库服务器的连接，提高效率

语法：

create function 函数名 returns 返回值类型

begin

函数体;

end

细节：

1.参数列表

参数名返回值类型

2.必须要有返回值，没有返回值会报错

return语句如果不放在函数体的最后面不会报错但是不推荐

3.如果函数体只有一句话则可以省略begin和end

4.使用delimiter设置结束标记

存储过程和函数的区别：

1.存储过程可以没有返回值，也可以有多个返回值，适合做批插入，批更新

2.函数必须有一个返回值，适合做对数据的处理后返回一个结果

函数的调用：

select 函数名(参数列表);

案例：返回公司的员工个数

CREATE FUNCTION f1() RETURNS INT
DETERMINISTIC
BEGIN
	DECLARE c INT DEFAULT 0;
	SELECT COUNT(*) INTO c FROM employees;
	RETURN c;
END $
SELECT f1()$

案例：根据员工名，返回它的工资

CREATE FUNCTION f2(NAME VARCHAR(20)) RETURNS DOUBLE
DETERMINISTIC
BEGIN
	DECLARE s DOUBLE;
	SELECT salary INTO s FROM employees WHERE last_name=NAME;
	RETURN s;
END $
SELECT f2('Kochhar')$

案例：根据部门名，返回该部门的平均工资

CREATE FUNCTION f3(dName VARCHAR(20)) RETURNS  DOUBLE
DETERMINISTIC
BEGIN
	DECLARE result DOUBLE;
	SELECT AVG(salary) INTO result FROM employees JOIN departments ON employees.department_id=departments.department_id WHERE department_name=dName GROUP BY departments.department_id ;
	RETURN result;
END $
SELECT f3('Adm')$

查看函数：

show create function 函数名;

删除函数:

drop function 函数名;

流程控制：

1.顺序结构：按照代码顺序从上到下执行

2.选择结构

单分支：IF -> 类似于java中的三元运算符

语法:if(exp1,exp2,exp3) ->若果exp1为真则执行exp2否则执行exp3

多分支：case

①类似于java中switch-case，一般用于值判断

语法：

case 表达式\字段\值

when 值1 then 值1\语句1;

when 值2 then 值2\语句2;

....

else 语句n;

end case;

②类似于java中的if-else if，一般用于范围判断

case

when exp1 then 值1\语句1;

when exp2 then 值2\语句2;

....

else 语句n;

end case;

案例:创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，显示A，80-90，#显示B，60-80，显示C，否则，显示D

CREATE PROCEDURE my_pro1(IN score DOUBLE)
BEGIN
	CASE
	WHEN score<=100 AND score>=90 THEN SELECT 'A';
	WHEN score>=80 THEN SELECT 'B';
	WHEN score>=60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;
END $
CALL my_pro1(85)$

if-elseif-else:

语法：

if exp1 then 语句1;

elseif exp2 then 语句2;

elseif exp3 then 语句3;

....

else 语句n;

end if;

案例：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100，返回A，80-90，返回B，60-80，返回C，否则，返回D

CREATE PROCEDURE my_pro2(IN score INT)
BEGIN
	IF score<=100 AND score>=90 THEN SELECT 'A';
	ELSEIF score>=80 THEN SELECT 'B';
	ELSEIF score>=60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END IF;
END $
CALL my_pro2(75)$

3.循环结构

1.while 2.loop 死循环 3.repeat 类比java中的do-while

leave: 类比java中的break;

iterate:类比java中的continue;

语法：

①[标志:]while 循环条件 do

循环体;

end while [标志];

②[标志:]loop

循环体;

end loop [标志:];

③[标志:]repeat

循环体;

util 结束循环的条件;

end repeat [标志];

案例：批量插入，根据次数插入到admin表中多条记录

CREATE PROCEDURE my_p1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	WHILE i<insertCount DO
	INSERT INTO admin VALUES(NULL,CONCAT('Kobe',i),CONCAT(i,i,i,i));
	SET i=i+1;
	END WHILE;
END $
CALL my_p1(5)$
SELECT * FROM admin$

案例:批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止

CREATE PROCEDURE my_p2(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<insertCount DO
	SET i=i+1;
	INSERT INTO admin VALUES(NULL,CONCAT('Maryna',i),CONCAT(i,i,i,i));
	IF i>=20 THEN LEAVE a;
	END IF;
	END WHILE a;
END $
CALL my_p2(30)$
SELECT * FROM admin$

案例：批量插入，根据次数插入到admin表中多条记录，值插入偶数次

CREATE PROCEDURE my_p3(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<insertCount DO
	SET i=i+1;
	IF i%2!=0 THEN ITERATE a;
	END IF;
	INSERT INTO admin VALUES(NULL,CONCAT('Maryna',i),CONCAT(i,i,i,i));
	END WHILE a;
END $
CALL my_p3(10)$
SELECT * FROM admin$

DROP TABLE IF EXISTS stringcontent;
CREATE TABLE stringcontent(
	id INT PRIMARY KEY AUTO_INCREMENT,
	content VARCHAR(20)
);

CREATE PROCEDURE my_p4(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrxtuvwxyz';
	DECLARE sta INT DEFAULT 0;
	DECLARE len INT DEFAULT 0; 
	WHILE i<insertCount DO
	SET sta=FLOOR(RAND()*26+1);
	SET len=20-FLOOR(RAND()*20+1)+1;
	INSERT INTO stringcontent VALUES(NULL,SUBSTR(str,sta,len));
	SET i=i+1;
	END WHILE;
END $
CALL my_p4(5);
SELECT * FROM stringcontent$

兰交余文乐

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql之变量、存储过程、函数

Mysql之变量、过程、函数变量分为系统变量和自定义变量系统变量：说明：系统变量是由系统提供的，不是用户自定义的，属于服务器层面 1.全局变量2.会话变量使用语法：1.查看所有的全局/[局部变量] 默认是局部变量SHOW global|[session] VARIABLES;2.查看某些满足条件的系统变量SHOW GLOBAL|[session] VARIABLES LIKE '%char%';3.查看指定的某个系统变量的值...
复制链接

扫一扫

专栏目录