Mysql-存储过程-函数-流程控制

最新推荐文章于 2023-05-21 16:12:07 发布

ChengLang_111

最新推荐文章于 2023-05-21 16:12:07 发布

阅读量166

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_47299953/article/details/115674694

版权

变量

系统变量
全局变量服务器每次启动将为所有的全局变量赋予初始值，针对所有连接（会话）有效，但不能跨重启
会话变量只在当前会员中有效

# 查看所有系统变量
SHOW [session] VARIABLES; # 会话
SHOW GLOBAL VARIABLES;  # 全局


# 按条件查询
SHOW SESSION VARIABLES LIKE '%char%'

# 查看指定的某个系统变量的值
SELECT @@tx_isolation;
SELECT @@autocommit

# 为某个系统变量赋值
set autocommit = 0;

自定义变量针对于当前会话有效

# 声明并初始化
#方式一：
SET @变量名 = 值;
#方式二：
SET @变量名:= 值;
#方式三：
SELECT @用户变量名:=值

#案例：
SET @name = 'lang';
SET @name = 100; #重新赋值
SET @count = 1;

#把查询结果赋值到变量中，要求查询结果为单个值
SELECT 字段 INTO 变量名
FROM 表;

SELECT COUNT(*) INTO @count
FROM employees;

自定义局部变量作用域：
仅仅在定义它的begin end中有效
应用在begin end中第一句话

# 声明并初始化
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

变量的使用：
在这里插入图片描述

储存过程和函数（方法）

存储过程含义：一组预先编译好的sql语句的集合。理解成批处理语句

一创建语法：

CREATE PROCEDURE 储存过程名(参数列表)
BEGIN
存储过程体
END

参数模式：
INT : 该参数可以作为输入
OUT : 该参数可以作为输出，也就是该参数可以作为返回值
INOUT : 该参数即可作为输入又可以作为输出

如果储存过程体仅仅只有一句话，begin　end可以省略，
储存过程体中的每天sql语句的结尾要求必须加分号，
储存过程的结尾可以使用 DELIMITER 重新设置

语法： DELIMITER 结束标记

案例： DELIMITER $

二调用语法：

CALL 储存过程名（实参列表）;

案例：（需在命令行中执行）

参数列表为空


# 插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(`username`,`password`,`blance`) VALUES('john1','aaa',300),
	('john1','aaa',300),('john1','aaa',300),('john1','aaa',300),('john1','aaa',300);
END $


#调用
CALL myp1()$

SELECT * FROM admin $

in模式参数

#根据传入的username 查询对应的余额
CREATE PROCEDURE myp2(IN username VARCHAR(10))
BEGIN 
	SELECT a.blance FROM admin a
	WHERE a.username = username;
END $

CALL myp2('lang')$

# 创建储存过程，实现用户是否登录成功
CREATE PROCEDURE myp4(IN username VARCHAR(10) ,IN pwd VARCHAR(20))
BEGIN 
	DECLARE result INT DEFAULT 0 ; # 定义变量，存放结果
	
	SELECT COUNT(*) INTO result #为变量赋值
	FROM admin
	WHERE admin.`username` = username AND admin.`password` = pwd;
	
	SELECT IF(result > 0,'登录成功','登录失败') AS 登录提示; #使用变量
END $

在这里插入图片描述

out参数

# out参数 根据传入的username 查询对应的余额
CREATE PROCEDURE myp6 (
  IN username VARCHAR (10),
  OUT blance VARCHAR (10)
) 
BEGIN
  SELECT 
    a.blance into blance 
  FROM
    admin a 
  WHERE a.username = username ;
  
  
END $ 

# 调用
CALL myp6('lang',@userBlance) $ #使用变量接受返回值

多个out参数

CREATE PROCEDURE myp8 (
  IN username VARCHAR (10),
  OUT pwd VARCHAR(20),
  OUT blance INT
) 
BEGIN
  SELECT 
    a.password,a.blance INTO pwd,blance
  FROM
    admin a 
  WHERE a.username = username ;
  
  
END $ 

# 调用
CALL myp8('lang',@userPwd,@userBlance) $ #使用变量接受返回值
SELECT @userPwd$
SELECT @userBlance$

inout 参数

# 案例1：传入a和b两个值，最终a和b都翻倍返回
CREATE PROCEDURE myp9( INOUT a INT ,INOUT b INT )
BEGIN	
	SET a = a * 2;
	SET b = b *2;
END $

#调用 需要传入两个有值的变量
SET @m = 10$
SET @n = 20$
CALL myp9(@m,@n)$

SELECT @m,@n$

二：删除存储过程
语法：

DROP PROCEDURE 名字

三：查看存储过程
语法：

SHOW CREATE PROCEDURE myp2

自定义函数

与储存过程差不多，区别在于返回值
储存过程：可以有0个返回，也可以有多个返回，适合做批量插入，批量更新
函数：有且仅有一个返回，适合做处理数据后返回一个结果

创建函数


#创建语法
CREATE FUNCTION 函数名（参数列表） RETURNS 返回类型
BEGIN
	函数体
END

# 调用语法
SELECT 函数名(参数列表)

# 无参
#案例：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN 
	DECLARE my_count INT DEFAULT 0; #定义局部变量
	SELECT COUNT(*) INTO my_count #给变量赋值
	FROM employees;
	
	RETURN my_count; #返回
END$

SELECT myf1();

# 有参数
#案例：根据员工名返回其工资
CREATE FUNCTION myf4(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET  @sal = 0; #定义用户变量
	SELECT salary INTO @sal #为变量赋值
	FROM `employees` e 
	WHERE e.`last_name` = empName;
	RETURN @sal;
END$

SELECT myf4('Baida');

查看函数

SHOW CREATE FUNCTION myf4;

删除函数

DROP FUNCTION myf4;

流程控制

if函数可以应用于任何地方

IF(表达式1,表达式2,表达式三);

等同于Java的三元运算符

case 结构

# 方式一 类似于Java 中的switch 语句 一般用于实现等值判断
CASE 变量
WHEN 要判断的值 
	THEN  表达式
WHEN 要判断的值
	THEN 表达式
...
ELSE 表达式 
END

# 方式二 类似于Java 中的多重if语句，一般用于实现区间判断

CASE
WHEN 条件一 
	THEN 表达式
WHEN 条件二 
	THEN 表达式
ELSE 表达式
END

如果在嵌套在 begin end 中使用
在这里插入图片描述

案例：


# 创建储存过程，根据传入的成绩来显示等级
# 比如传入的成绩： 90-100，显示A
		  #80-90， 显示B
		  #60-80， 显示C
		  #否则，  显示D
CREATE PROCEDURE test_case(IN score INT)
BEGIN
	CASE
	WHEN score >= 90 AND score <= 100 THEN SELECT 'A';
	WHEN score >= 80 THEN SELECT 'B';
	WHEN score >= 60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;
END$

CALL test_case(83);

if 结构

只能应用在begin end 中

IF 条件
    THEN 表达式;
ELSEIF 条件2 
    THEN 表达式; 
...
ELSE 表达式;

END IF;

案例：

CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
	IF score >= 90 AND score <= 100
		THEN RETURN 'A';
	ELSEIF score >= 80 
		THEN RETURN 'B'; 
	ELSEIF score >= 60 
		THEN RETURN 'c'; 
	ELSE RETURN 'D';
	END IF;
END$

SELECT test_if(80)

循环结构
分类： while loop repeat
循环控制：
iterate ：类似于continue ，继续，结束本次循环，继续下一次
leave ：类似于 break 跳出，结束当前所在循环

#while 语法 ：
WHILE 循环条件 DO
	循环体；
END WHILE

#如果需要进行循环控制，需要给循环起一个标签

标签:WHILE 循环条件 DO
	循环体;
END WHILE 标签

# loop 语法：
【标签:】LOOP
	循环体;
END LOOP【标签】

#repeat 上来先执行一遍 语法：
【标签:】REPEAT
	循环体;
UNTIL 结束循环的条件

END REPEAT【标签】


# 案例： 没有添加循环控制
# 批量插入，根据传入的次数插入到admin表中多条记录

CREATE PROCEDURE pro_while(IN inserCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	
	WHILE i <= inserCount DO
		INSERT INTO admin(username,PASSWORD,blance) VALUES('test_while','000','111');
		SET i = i + 1;
	END WHILE;
	
END $

CALL pro_while(10)$

# 案例：loop+添加循环控制
# 批量插入，根据传入的次数插入到admin表中多条记录 一次最多插入20条
 CREATE PROCEDURE test_loop()
 BEGIN
	DECLARE i INT DEFAULT 1;
	test:LOOP
	
		IF i > 20
			THEN LEAVE test; # 如果大于20次就结束循环
		END IF;
		
		INSERT INTO admin(username,PASSWORD,blance) VALUES('test_while','000','111');
		SET i = i + 1;
		
	END LOOP test;
	
 END $
 
 CALL test_loop()$

# 案例：while + 添加循环控制
# 返回 输入insercount 打印出insercount以内的偶数
CREATE PROCEDURE pro_while2(IN insercount INT)
BEGIN 
	DECLARE i INT DEFAULT -1;
	test:WHILE i<= insercount DO
		SET i = i + 1;
		
		IF i % 2 != 0 THEN ITERATE test; # 如果不是偶数 则跳出本次
		END IF;
		
		SELECT i; # 打印
		
	 END WHILE test;
END $

CALL pro_while2(10)$