变量、存储过程与函数

变量

分类

1. 系统变量
   1. 全局变量
   2. 会话变量
2. 自定义变量
   1. 用户变量
   2. 局部变量

系统变量

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

1. 查看所有的系统变量

SHOW GLOBAL | [SESSION] VARIABLES;

SHOW SESSION VARIABLES;

2. 查看满足部分条件的系统变量

SHOW GLOBAL | [SESSION] VARIABLES LIKE "%char%";

SHOW SESSION VARIABLES LIKE "%commit%";

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

SELECT @@global | [SESSION] .系统变量名;

SELECT @@global.autocommit;

4. 为某个系统变量赋值

SET GLOBAL | [SESSION] 系统变量名 = 值;
SET @@global | [SESSION] .系统变量名 = 值;

5. 查看会话变量

SELECT @@tx_isolation;
SELECT @@session.tx_isolation;

用户变量

简介

变量是由用户定义的，不是系统的

声明并初始化

SET @hello = "world";
SET @hello := 100;
SELECT @hello := 200;

查询

SELECT COUNT(*) INTO @count
FROM employees;

存储过程

存储过程类似于函数

语法

创建

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体,也就是一组合法的sql语句
END

注意
参数列表包含三部分

1. 参数模式
2. 参数名
3. 参数类型

IN stuName varchar(20)

参数模式

1. IN:该参数可以作为输入，也就是该参数需要调用方法传入值
2. OUT:改参数可以作为输出，也就是该参数可以作为返回值
3. INOUT:该参数既可以作为输入也可以作为输出

其他

• 如果存储过程只有一句话，BEGIN END可以省略
• 存储过程中，每天一的SQL都必须加分号，但是数据库的一条语句会以分号进行结尾，也就是说当执行到第一个分号的时候sql语句就会结束，想要实现多个语句一起执行，那么就需要结束标志符，这就用到了DELIMITER关键字
• DELIMITER用于重新设置结束符,使用完之后，我们需要再次指定结束语句为分号

DELIMITER $

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体,也就是一组合法的sql语句
END $

调用

CALL 存储过程名(实参列表);

删除

DROP PROCEDURE 存储过程名;

查看

例子

空参

DELIMITER $

CREATE PROCEDURE mypro()
BEGIN
	INSERT INTO admin(username,PASSWORD)
	VALUES('join1',1111),('join2',2222);
END $

CALL mupro()$

IN模式

DELIMITER $

CREATE PROCEDURE mypro2(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 mupro("小花")$

OUT模式

DELIMITER $

CREATE PROCEDURE mypro2(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
	
END $

CALL mupro("小花")$

INOUT模式

DELIMITER $

CREATE PROCEDURE mypro3(INOUT a INT,INOUT b INT)
BEGIN
	SET a = a*2
	SET b = b*2
END $

SET @m = 10$
SET @n = 20$

CALL mypro3(@m,@n)$

函数

创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN 
	函数体
END

1. 参数列表包括两部分
   1. 参数名
   2. 参数类型
2. 函数体，一定要有return语句，如果没有return语句就会报错
3. return 值：如果函数体中有且仅有一句话，则可以省略begin end
4. 可以使用DELIMITER来重新定义结束标记

调用语法

SELECT 函数名(参数列表);

与存储过程的区别

存储过程

1. 存储过程可以有0个返回，也可以多个返回，是个批量插入，批量更新

函数

• 有且仅有一个返回，适合处理数据后返回一个结果

例子

无参数有返回

CREATE FUNCTION myfun() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0 ; # 定义变量并赋值
	SELECT  COUNT(*) INTO c
	FROM studentinfo
	RETURN c
END $

有参数有返回

CREATE FUNCTION myfun(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN 
	SET @sal=0; # 定义用户变量
	SELECT salary INTO @sal
	FROM employee
	WHERE last_name = empName;
	
	RETURN @sal;
END $