MySQL全总结四-存储过程, 函数,和流程控制结构 1.5

六, MySQL存储过程,函数,流程控制结构===4.18

6.1 变量

MySQL的变量包括系统变量和自定义变量,

其中系统变量中有全局变量和会话变量,

自定义变量中有用户变量和局部变量.

6.1.1 系统变量

系统变量由系统提供而非用户自定义,属于服务器层面.
系统变量中包含全局变量和自定义变量.

1、全局变量
服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效

2、会话变量
服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）

全局变量的使用语法：

# 1.查看系统变量
show [global|session] variables like '';  #如果没有显式声明global还是session，则默认是session

# 2.查看指定的系统变量的值
select @@[global|session].变量名; #如果没有显式声明global还是session，则默认是session

# 3.为系统变量赋值
##方式一：
set [global|session] 变量名=值; 如果没有显式声明global还是session，则默认是session
##方式二：
set @@global.变量名=值;
set @@变量名=值

6.1.2 自定义变量

自定义变量包括用户变量和局部变量.

6.1.2.1 用户变量

作用域：针对于当前连接（会话）生效
位置：begin end里面，也可以放在外面
使用：

①声明并赋值:

	set @变量名=值;
	#或
	set @变量名:=值;
	-- #或
	-- select @变量名:=值;

②更新值

# 方式一：
	set @变量名=值;
	-- #或
	-- set @变量名:=值;
	-- #或
	-- select @变量名:=值;
# 方式二：
	select xx into @变量名 from 表;

③使用

	select @变量名;

6.1.2.2 自定义变量

作用域：仅仅在定义它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句
使用：

①声明

	declare 变量名 类型 【default 值】;

②赋值或更新

#方式一：
	set 变量名=值;
	#或
	set 变量名:=值;
	#或
	select @变量名:=值;
#方式二：
	select xx into 变量名 from 表;

③使用

	select 变量名;

6.2 存储过程

一组预先编译好的SQL语句的集合,类似于批处理语句.
好处:

提高了代码的重用性.
简化操作.
减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率.

6.2.1 存储过程的语法

6.2.1.1 创建语法

create procedure 存储过程名(参数列表)
begin
	存储过程体(一组合法有效的SQL语句)
end

注意:

存储过程的参数列表包括三部分: 参数模式参数名参数类型

举个栗子:
IN stuname varchar(20)

参数模式的类型:
- IN------该参数可以作为输入,也就是说该参数需要调用方传入值.
- OUT-----该参数可以作为输出,也就是该参数可以作为返回值
- INOUT—该参数既可以作为输入又可以作为输出,也就是该参数既可以传入值,又可以返回值.

如果存储过程体仅仅只有一句话, BEGIN END 可以省略.
存储过程体中的每条SQL语句的结尾要求必须加分号.
存储过程的结尾标志可以使用 DELIMITER 重新设置.
语法: DELIMITER 自定义结束标记
案例: DELIMITER $

6.2.1.2 调用语法

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

6.2.2 空参的存储过程

举个栗子:

DELIMITER *
CREATE PROCEDURE see_av_star()
BEGIN 
	SELECT NAME,sex,borndate,phone
	FROM beauty
	WHERE NAME LIKE '%苍%';
END *

CALL see_av_star;

6.2.3 IN 模式的存储过程

举个栗子:

## 1.查找girls表中的av_star
DELIMITER ^
CREATE PROCEDURE search(IN girlName VARCHAR(20))
BEGIN 
	SELECT boyName
	FROM beauty be 
	RIGHT OUTER JOIN boys bo
	ON be.boyfriend_id = bo.id
	WHERE be.name=girlName;
END ^

CALL search('苍老师');



## 2.判断用户是否登录成功
DELIMITER &
CREATE PROCEDURE loginJudge(IN username VARCHAR(20), IN PASSWORD INT)
BEGIN 
	SELECT a.username,
	
		CASE
		WHEN (a.username=username AND a.password=PASSWORD) THEN '登录成功'
		ELSE '登录失败'
		END AS 登录状态
	FROM admin a;
END &

CALL loginJudge('john',8888);

6.2.4 OUT 模式的存储过程(▷)

举个栗子:

####1. 根据女神名,返回对应的男生名
DELIMITER &
CREATE PROCEDURE girlsMan(IN girlName VARCHAR(20), OUT boyName VARCHAR(10))
BEGIN 
	SELECT bo.boyName INTO boyName
	FROM beauty be
	INNER JOIN boys bo
	ON be.boyfriend_id = bo.id
	WHERE be.name=girlName;
END&

CALL girlsman('苍老师', @boyname)
SELECT @boyname
#===================================================
###2. 根据女神名,返回对应的男生名和魅力值
DROP PROCEDURE searchman;

DELIMITER &
CREATE PROCEDURE searchman(IN girlName VARCHAR(20), OUT  boyName VARCHAR(20), OUT boyattrack VARCHAR(20))
 BEGIN
	SELECT boys.boyName, boys.userCp  INTO boyName,boyattrack	
	FROM beauty be
	INNER JOIN boys
	ON be.boyfriend_id = boys.id
	WHERE be.name=girlName;
END&  
 
CALL searchman('苍老师',@bname,@bcp);
 
SELECT @bname, @bcp;

6.2.5 INOUT 模式的存储过程

#### INOUT模式, 可以输入也可以返回

DELIMITER %
CREATE PROCEDURE test(INOUT a INT, INOUT b INT)
BEGIN

	SET a=a*3;
	SET b=b*4;
END %
 
SET @p=10;
SET @q=20;

CALL test(@p,@q);

SELECT @p,@q;

6.2.6 删除存储过程

注意存储过程的删除不支持多个同时删除.
	drop procedure [if exists] xx;

6.2.7 查看存储过程

	show create procedure xx;

6.2.8 存储过程案例

	
###1. 创建存储过程或函数,实现传入女孩名称,返回 女神 and 男神格式字符串
-- 注意体会此处内连接和外连接的差异所在!
DROP PROCEDURE MandWM;

DELIMITER $
CREATE PROCEDURE MandWM(IN girlName VARCHAR(20), OUT str VARCHAR(50))
BEGIN
	SELECT CONCAT(be.name,' and ', IFNULL(bo.boyName,'NULL')) INTO str
	FROM beauty be
	LEFT OUTER JOIN boys bo
	ON be.boyfriend_id = bo.id
	WHERE be.name=girlName;
END$

CALL MandWM('柳岩', @sb);

SELECT @sb;


# 2. 创建存储过程或函数,根据传入的条目数和起始索引,
##查询 beauty表的记录

DROP PROCEDURE IF EXISTS checktable;
DELIMITER $
CREATE PROCEDURE checktable(IN outindex INT, IN itemNum INT)
BEGIN 
	SELECT *
	FROM beauty
	LIMIT outindex,itemNum;

END$

6.3 函数

函数与存储过程特点基本一致;

函数与过程的最大区别:

函数: 有且仅有一个返回,适合做处理数据后返回一个结果;
过程: 可以0个返回,也可以有多个返回,适合做批量插入,批量更新;

6.3.1 函数的创建语法

需要注意的几点

参数列表包含两部分, 参数名和参数类型;
函数体中肯定会有return语句,没有则报错,最好把’return 值’语句放在函数体的最后.
函数体只有一行时,begin end同样可以省略
仍需要自定义结束符’ delimiter 结尾符号’

	create function 函数名(参数列表) returns 返回类型
	begin	
			函数体
	end

6.3.2 函数的调用方法

函数调用: 执行函数体并返回 return值;

	select 函数名(参数列表)

6.3.3 函数的栗子

#1. 无参有返回
###1.返回employees 表中的员工个数
USE myemployees;

DELIMITER $
CREATE FUNCTION countNum() RETURNS INT
BEGIN 
	DECLARE s INT DEFAULT 0;
	SELECT COUNT(*) INTO s
	FROM employees;
	RETURN s;
END$

SELECT countNum();


##有参有返回
#1. 根据员工名返回他的工资
### 注意: mysql声明变量也是一定需要初始化的!

DELIMITER $
CREATE FUNCTION salary(empName VARCHAR(30)) RETURNS INT
BEGIN 
	DECLARE salary INT DEFAULT 0;
	SELECT emp.salary INTO salary
	FROM employees emp
	WHERE emp.lastname = empName;
	
	RETURN salary;
END$

SELECT salary('Austin');

### 2. 根据部门名返回该部门的平均工资

##主要函数体
#### 内连接也可以做,搞什么子查询
SELECT department_name,AVG(salary)
FROM employees emp
INNER JOIN departments de
ON emp.department_id = de.department_id
WHERE de.department_name = 'IT';
		
DROP FUNCTION IF EXISTS avg_de_salary;

DELIMITER $
CREATE FUNCTION avg_de_salary(de_name VARCHAR(20)) RETURNS DOUBLE
BEGIN 
	
	DECLARE sall INT DEFAULT 0;
	SELECT de_id_salary.salary INTO sall
	FROM departments d,(

		SELECT department_id id,AVG(salary) salary
		FROM employees
		GROUP BY department_id
	) AS de_id_salary
	WHERE d.department_id = de_id_salary.id AND department_name=de_name;
	
	RETURN sall;
END$

SELECT avg_de_salary('IT');


##3. 创建函数,传入两个float值,返回两者之和

DELIMITER $
CREATE FUNCTION adds(a FLOAT, b FLOAT) RETURNS FLOAT
BEGIN 
	DECLARE re FLOAT DEFAULT 0;
	SET re = a+b;
	RETURN re;
END$

6.3.4 函数的查看和删除

##跟过程的写法基本一致

#1.查看
	show create function avg_sal();
#2.删除
	drop function if exists avg_sal();

6.4 流程控制结构

种类:

结构	说明
顺序结构	程序从上往下依次执行
分支结构	程序按条件进行选择执行,从两条或多条路径中选择一条来执行
循环结构	程序满足一定条件下,重复执行一组语句

6.4.1 分支结构

6.4.1.1 IF函数

功能: 实现简单的双分支
语法: IF(表达式1, 表达式2, 表达式3)
执行顺序:
- 如果表达式1为真, if函数返回表达式2的值;
- 如果表达式1为假, if函数返回表达式3的值;
1. IF函数–等同于Java中的 if else 语句.

/*
	基本格式:  IF(条件判断, 条件为真的表达式1, 条件为假的表达式2)
*/
#栗子1:
	SELECT IF(10>1, '大于', '小于');
#栗子2:
	 SELECT last_name, commission_pct,IF(commission_pct is NULL,'我没有,好烦','哈哈.我有') AS '奖金' FROM employees;

6.4.1.2 CASE结构

1.CASE函数1(类似于Java的switch…case语句),一般用于实现等值判断

#when中的常量都是case中字段可能的值
#用在SELECT中, case是表达式或字段,而then后是值
#用在函数或存储过程中, case是语句, then后是语句



	case 要判断的变量,字段或表达式
	when 常量1 then 要显示的值1或语句1;
	when 常量2 then 要显示的值2或语句2;
	...
	else 要显示的值n或语句n;
	end

举个栗子:

/* 案例: 查询员工的工资,要求:
部门号=30, 显示的工资为1.1倍;
部门号=40, 显示的工资为1.2倍;
部门号=50, 显示的工资为1.3倍;
其他部门,显示的工资为原工资;
*/

SELECT salary 原始工资, department_id,
CASE department_id
WHEN 30 THEN salary*1.1;
WHEN 40 THEN salary*1.2;
WHEN 50 THEN salary*1.3;
ELSE salary
END AS 新工资
FROM employees;

2.CASE函数2(类似于Java的多重if语句),一般用于实现区间判断

CASE
WHEN 条件1 THEN 要显示的值1或语句1
WHEN 条件2 THEN 要显示的值2或语句2
...
ELSE 要显示的值n或语句n
END

举个栗子:

/* 案例: 查询员工的工资情况,要求:
如果工资>20000, 显示A级别,
如果工资>15000, 显示B级别,
如果工资>10000, 显示C级别,
否则,显示D级别
*/

SELECT salary,
CASE
WHEN salary>2000 THEN 'A'	
WHEN salary>1500 THEN 'B'
WHEN salary>1000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

当case结构用在过程或函数中:

DELIMITER $
CREATE PROCEDURE gradesLevel(IN grade INT)
BEGIN 
	CASE
	WHEN grade<=100 AND grade>=90 THEN SELECT 'A';
	WHEN grade>=80 THEN SELECT 'B';
	WHEN grade>=60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;
END$


CALL gradesLevel(88);

6.4.1.3 IF结构

功能: 实现多重分支;

只能用在begin end中.

语法:

	if 条件1 then 语句1;
	else if 条件2 then 语句2;
	...
	[else 语句n;]
	end if;

举个栗子:

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

##函数select, 过程 call


SELECT gradesLevel(88);

6.4.2 循环结构

分类: while, loop, repeat;
循环控制:
- iterate, 类似于continue; 结束本次循环,继续下一次;
- leave, 类似于break; 跳出循环,结束当前所在的循环.

6.4.2.1 while

语法:

	[自定义名称]: while 循环条件 do
									循环体
	end while[自定义名称];

6.4.2.2 loop

语法:

[自定义名称]: loop
					循环体;
end loop [自定义名称];

6.4.2.3 repeat

语法:

[自定义名称]: repeat
					循环体;
util 结束循环的条件
end repeat [自定义名称];

6.4.2.4 循环结构的栗子

###循环语句
##1. 没有添加循环控制语句
### 批量插入,根据次数插入到admin表中多条记录

DELIMITER $
CREATE PROCEDURE pro_insert(IN num INT)
BEGIN 
	DECLARE i INT DEFAULT 1;
	WHILE i<=num DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('jack',i),'1234');
		SET i=i+1;
	END WHILE;
END $
	
CALL pro_insert(10);


###2. 添加循环控制语句以及使用leave跳出循环
####重复上一个案例

DROP PROCEDURE pro_insert_2;
DELIMITER $
CREATE PROCEDURE pro_insert_2 (IN insert_Count INT)
BEGIN 
	DECLARE i INT DEFAULT 1;
	w: WHILE i<=insert_Count  DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('xiaogou',i),'7777');
		##添加if结构,作为循环跳出的判断
		IF i>=20 THEN LEAVE w;
		END IF;
		SET i=i+1;
	END WHILE w;
END$

CALL pro_insert_2(23);

posted @ 2022-05-26 20:31 青松城阅读(38) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

sha-Pao-Zi

MySQL全总结四-存储过程, 函数,和流程控制结构 1.5

六, MySQL存储过程,函数,流程控制结构===4.18

6.1 变量

6.1.1 系统变量

6.1.2 自定义变量

6.1.2.1 用户变量

6.1.2.2 自定义变量

6.2 存储过程

6.2.1 存储过程的语法

6.2.1.1 创建语法

6.2.1.2 调用语法

6.2.2 空参的存储过程

6.2.3 IN 模式的存储过程

6.2.4 OUT 模式的存储过程(▷)

6.2.5 INOUT 模式的存储过程

6.2.6 删除存储过程

6.2.7 查看存储过程

6.2.8 存储过程案例

6.3 函数

6.3.1 函数的创建语法

6.3.2 函数的调用方法

6.3.3 函数的栗子

6.3.4 函数的查看和删除

6.4 流程控制结构

6.4.1 分支结构

6.4.1.1 IF函数

6.4.1.2 CASE结构

6.4.1.3 IF结构

6.4.2 循环结构

6.4.2.1 while

6.4.2.2 loop

6.4.2.3 repeat

6.4.2.4 循环结构的栗子

公告