MySQL基础(七)——存储过程与函数

MySQL基础(七)——存储过程与函数

视频学习来源:MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷;

作者:木子六日;

sql版本:5.7.33;

变量

# 系统变量
# 可分为全局变量和会话变量
# 查看所有全局变量
SHOW GLOBAL VARIABLES;

# 查看所有会话变量
SHOW SESSION VARIABLES;

# 或者(一般来说不写global或者session都是默认session，需要查看全局变量都要加上global)
SHOW VARIABLES;

# 也可以使用模糊查询查出部分变量
SHOW VARIABLES LIKE '%char%';

# 查看指定的某个变量使用select
SELECT
	@@character_set_client;

SELECT
	@@GLOBAL .character_set_client;

# 为系统变量赋值
SET autocommit = 0;

# 或者
SET @@SESSION .autocommit = 0;

# 自定义变量
# 可分为用户变量和局部变量
# 用户变量的作用域是一次会话
# 用户变量的声明(三种方式)
SET @ljj = 'ljj1';


SET @ljj := 'ljj2';

SELECT
	@ljj := 'ljj3';

# 用户变量的赋值(可以使用声明的三种方式进行赋值，亦可以计算得到一个值放入变量中)
SELECT
	COUNT(*) INTO @ljj
FROM
	employees;

SELECT
	@ljj;

# 局部变量的有效范围仅在begin和end之间
# 局部变量的声明
DECLARE
	muziliuri VARCHAR (20);

DECLARE
	muziliuri VARCHAR (20) DEFAULT 'muziliuri';

# 局部变量的赋值
SET muziliuri = '123';


SET muziliuri := '123';

SELECT
	muziliuri := '123';

SELECT
	COUNT(*) INTO muziliuri
FROM
	employees;

# 查看局部变量
SELECT
	muziliuri;

存储过程

# 存储过程
# 创建语法
/*
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
			存储过程体(一组合法的sql语句)
END

参数列表包含三部分:
参数模式、参数名、参数类型
例如:
IN stuName VARCHAR(20)

参数模式：
IN			该参数作为输入
OUT			该参数作为输出
INOUT		该参数既能作为输入又能作为输出

如果begin end中只有一句话，那么begin end可以省略

由于begin end中的语句需要加分号，所以分号不能作为存储过程的结束符，需要自定义结束符

delimiter只能在命令行下使用
*/
# 调用语法
/*
CALL 存储过程名(实参列表);
*/
# 案例一(需要放到命令行中执行)
USE students;
DELIMITER $;


CREATE PROCEDURE myp1 ()
BEGIN
	INSERT INTO stuInfo (stuName, age)
VALUES
	('ljj1', 18),
	('ljj2', 18),
	('ljj3', 18),
	('ljj4', 18),
	('ljj5', 18) ;
END $ # 案例二(查询所有学生信息)
CREATE PROCEDURE myp2 ()
BEGIN
	SELECT
		*
	FROM
		stuInfo ;
	END $ CALL myp2 () $ # 案例三(登录)
	USE myemployees ; CREATE PROCEDURE login (
		IN username VARCHAR (20),
		IN `password` VARCHAR (20),
		OUT loginStatus INT
	)
	BEGIN
		SELECT
			count(*) INTO loginStatus
		FROM
			t_user
		WHERE
			t_user.username = username
		AND t_user.`password` = `password` ;
		END $ 
CALL login ('123', '123' ,@loginStatus) $ 
SELECT
			@loginStatus$ # 删除存储过程
			DROP PROCEDURE 存储过程名 ; USE students ; # 查看存储过程信息
			SHOW CREATE PROCEDURE myp1 ;

函数

# 函数
/*
	函数和存储过程是类似的，
	区别在于函数必须有一个返回值，
	而存储过程可以没有返回值或者多个返回值。
*/

# 创建语法
/*
	CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
	BEGIN
		函数体
	END 结束符
*/
# 案例：返回员工个数
delimiter $
CREATE FUNCTION myf1 () RETURNS INT
BEGIN
	DECLARE
		c INT DEFAULT 0 ; SELECT
			COUNT(*) INTO c
		FROM
			employees ; RETURN c ;
		END$

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

流程控制

# 流程控制

# 分支
CREATE FUNCTION myf2(score INT) RETURNS CHAR(1)
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 $

# 循环(leave类似于break，iterate类似于continue)
CREATE PROCEDURE my_while(IN insertCount INT)
BEGIN
			DECLARE i INT DEFAULT 1;
			a:WHILE i <= insertCount DO
					INSERT INTO book(bName,price) VALUES ('aa',12.23);
					SET i = i + 1;
					IF i > 20 THEN LEAVE a;
					END IF;
			END WHILE a;
END $