mapengpeng1999@163.com 变量存储过程函数控制流程

变量（存储过程和函数控制流程都要用到变量）

系统变量：是数据库服务器层面的，不是用户定义的。
全局变量：在数据库系统，针对所有的数据库的用户、会话【一次连接】都有效
会话变量：在一次连接中有效，新的连接，这个变量就无效了
用户自定义变量
用户变量：在一次连接中有效，新的连接，这个变量就无效了
局部变量：定义在程序块中，其他的程序或者程序块使用不了这个变量

一、系统变量

​ 说明：属于系统定义的变量，不是用户定义，所以属于数据库服务器层面的
​ 在开发中，系统变量中分为全局变量和会话变量的操作基本是一样的，
​ 仅仅只有一个单词的区别
​ 全局：GLOBAL
​ 会话：SESSION

使用步骤：

1.查看所有的系统变量,使用GLOBAL查看全局的，SESSION查看会话的，默认则是会话变量
SHOW [GLOBAL|SESSION] VARIABLES;
SHOW GLOBAL VARIABLES;
2.查看满足条件的系统变量
SHOW [GLOBAL|SESSION] VARIABLES LIKE ‘%关键字%’;
SHOW GLOBAL VARIABLES LIKE ‘%auto%’;
3.查看指定系统变量的值
SELECT @@系统变量名称
SELECT @@autocommit;

4.设置系统变量的值
方式一：SET [GLOBAL|SESSION] 系统变量名称=值;
方式二：SET [@@global|SESSION] 系统变量名=值;
SET SESSION autocommit = 0;
SET GLOBAL autocommit = 0;

二、用户自定义变量

使用步骤：

声明

赋值

使用

1 用户变量：值针对当前的连接[会话]有效。

① 声明用户变量：声明和赋值可以是一起操作，（mysql赋值符号有=或:=）
SET @变量名 = 值;
SET @变量名 := 值; //实际上在Oracle里面也可以 使用 DEFAULT
SELECT @变量名 := 值;

SET @shcool = ‘南航科院’;
SET @shcool1 := ‘江西理工’;
SELECT @shcool2 := ‘万邦易嵌’;

②给变量赋值
方式一：
SET @变量名 = 值;
SET @变量名 := 值; //用SET或SELECT给变量赋值
SELECT @变量名 := 值;
方式二：将查询的结果赋值给变量
SELECT 数据列 INTO @变量名 FROM 表 ; //要求查询的结果集是单行的，数据列数要和变量列表一致
SELECT stu_id,stu_name INTO @stuId,@stuName FROM student_info WHERE stu_id = 1;

SELECT ‘wanbangee’ INTO @shcool;

③ 使用变量
SELECT @变量名;
SELECT @shcool;

2.局部变量

作用域：仅仅只能在定义这个变量的程序块[BEGIN END]中使用
而且变量必须声明在BEGIN END开头

BEGIN 
	#声明变量
	程序;
	程序;
	#声明变量，这是错误的
END;

① 声明局部变量
declare 变量名 类型;
declare 变量名 类型 default 值; //oracle中 declare 变量名 类型 := 值

② 给局部变量赋值
方式一：
set 变量名 = 值;
set 变量名 := 值;
select 变量名 := 值;
方式二：
select 数据列 into 变量名 from 表 ; //要求查询的结果集是单行的，数据列数要和变量列表一致

③ 查看局部变量
select 局部变量名称;

	作用域	定义的位置	语法
用户变量	在当前会话中有效	哪里都可以	加 @符号 不指定类型
局部变量	在当前的begin end 中有效	在begin end第一行位置	不用@,但是有类型

存储过程

存储过程和函数，在传统J2EE企业级开发用的多，互联网开发用的少。
存储过程和函数：类似于Java中的方法，在数据库、前端JS中叫函数。
好处：
1、提高代码的重用性
2、简化代码编写
存储过程的含义：一组预先编译好的SQL语句的集合，理解成批处理。
1、提高代码的重用性
2、简化代码编写
3、减少了编译次数和数据库服务器的连接次数，提升了效率。
① 声明局部变量
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
② 给局部变量赋值
方式一：
SET 变量名 = 值;
SET 变量名 := 值;
SELECT 变量名 := 值;
方式二：
SELECT 数据列 INTO 变量名 FROM 表 ;
③ 查看局部变量
SELECT 局部变量名称;

	作用域	定义的位置	语法
用户变量	在当前会话中有效	哪里都可以	加 @符号 不指定类型
局部变量	在当前的begin end 中有效	在begin end第一行位置	不用@,但是有类型

一、 创建存储过程procedure

create PROCEDURE 过程名称(参数列表)
begin
存储过程体(一组SQL语句)
end

注意点：
1.参数列表包含三个部分
参数模式 参数名 参数类型
举例:
create procedure myp1(in stu_name varchar(20))
begin

end
参数模式总共有三种：
in:表示这个参数是入参，在调用这个过程的时候必须传入实际参数
out：表示这个参数是输出参数，实际上这个参数就是过程的返回值
inout：即是传入参数，又是输出参数
2.存储过程编写规则

如果存储过程体只有一句sql，那么begin end 可以省略

存储过程体中的每一条sql语句必须结尾，必须使用;结束

必须标记其他符号作为结束符号，使用delimiter 来标记

DELIMITER $;
SELECT * FROM employees $

二、 调用存储过程

call 存储过程名称(参数列表)

案例1.没有参数的存储过程

1.创建存储过程，往admin表中插入5笔数据。

DELIMITER $       #设置$为程序结束符
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,password) VALUES 
	('jhon','123'),
	('lucy','123'),
	('lili','123'),
	('tom','123'),
	('jreey','123'),
	('rose','123');    #用;结束sql语句
END $    #用$来结束整个程序

调用：
CALL myp1()

案例2.带in模式参数的存储过程

2 传入女神名，查询对应的男神
DELIMITER $
CREATE PROCEDURE myp2(IN girlName VARCHAR(20))
BEGIN 
	SELECT b.* FROM beauty a ,boys b
	WHERE a.`boyfriend_id` = b.id 
	AND a.`name` = girlName;
END $

调用：
CALL myp2('Angelababy')

案例3.带in模式 两个参数的存储过程

3 传入用户名和密码，显示用户是否登录成功

CREATE PROCEDURE myp3(IN user_name VARCHAR(20),IN pass_word VARCHAR(20))
BEGIN
	DECLARE result  INT;
	SELECT COUNT(*) INTO result FROM admin WHERE username= user_name 
	AND password = pass_word;
	SELECT IF(result>0,'success','fail');
END $

调用：
CALL myp3('jjm','123') 

案例4.带out模式参数 的存储过程

4 传入用户名和密码，返回用户登录成功或失败的消息
CREATE PROCEDURE myp4(IN user_name VARCHAR(20),IN pass_word VARCHAR(20),OUT res VARCHAR(10))
BEGIN
	DECLARE result  INT;
	SELECT COUNT(*) INTO result FROM admin WHERE username= user_name 
	AND password = pass_word;
	SELECT IF(result>0,'success','fail') INTO res;
END $

调用：
CALL myp4('lucy','123',@result) $
SELECT  @result $

三 、删除过程

DROP PROCEDURE myp3

四 、查看存储过程

show create PROCEDURE 存储过程名

SHOW CREATE PROCEDURE myp3;

函数

函数类似存储过程，存储过程和函数：类似于Java中的方法

好处：
1、提高代码的重用性
2、简化代码编写

函数的含义：一组预先编译好的sql语句的集合，理解成批处理
1、提高代码的重用性
2、简化代码编写
3、减少了编译次数和数据库服务器的连接次数，提升了效率

函数与存储过程的区别：

函数：有且仅有一个返回值，适合进行数据处理之后返回一个结果

过程：可以有0个返回值，可以定义多个返回值，适合做批量的插入、修改、删除操作

一 、函数的创建

create function 函数名(参数列表) returns 返回类型
begin
函数体
end
注意点：
1.参数列表 包含两个部分
参数名称 参数类型
2.函数体必须存在return语句，没有则会报错 ，return放在方法体的最后，
3.如果函数体中仅仅只有一条sql，可以省略begin end
4.使用DELIMITER语句定义结束标识符

二、 调用函数

select 函数名(参数列表)

三、 删除函数

drop function 函数名1,函数名2…

案例1.没有参数的函数

案例：返回员工个数

CREATE FUNCTION myf1() RETURNS INT
BEGIN	
	DECLARE emp_count INT;
	SELECT COUNT(*) INTO emp_count FROM employees;
	RETURN emp_count;
END $
调用函数：
SELECT myf1();

案例2.有参数有返回的函数

案例：根据出入的员工的last_name，返回工资

CREATE FUNCTION get_salary_by_last_name(last_name VARCHAR(20)) RETURNS DOUBLE
BEGIN 
	DECLARE emp_salary DOUBLE;
	SELECT salary INTO emp_salary FROM employees WHERE employees.last_name = last_name;
	RETURN emp_salary;
END $
调用函数：
SELECT get_salary_by_last_name('De Haan');

流程控制语句

程序执行流程有：顺序，分支，循环

一、 分支结构

1.if函数
语法：if(条件，值1，值2) //三目运算符
可以使用在select字句中，也可以使用begin end 中

2.case结构
语法：类似于java的switch case
语法：
case 变量或表达式
when 值1/表达式 then 语句1；
when 值2/表达式 then 语句2；
…
else 语句n;
end case; //结束case分支

可以用在select字句中，也可以用在begin end 中
3.if结构
语法：
if 条件1 then 语句1；
elseif 条件2 then 语句2；
…
else 语句n;
end if; //结束if分支
只能用在begin end 中

案例1：创建函数，传入Java的成绩，如果成绩>90 返回A，如果大于80 返回B…小于60 返回 E

使用if结构：

DELIMITER $
CREATE FUNCTION get_score_level(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE score_level CHAR ;
	IF score >= 90 THEN SET score_level='A';
	ELSEIF score >= 80 THEN SET score_level='B';
	ELSEIF score >= 70 THEN SET score_level='C';
	ELSEIF score >= 60 THEN SET score_level='D';
	ELSE SET score_level='E';
	END IF;
	RETURN score_level;
END $

SELECT get_score_level(59);

使用case结构：

DELIMITER $
CREATE FUNCTION get_score_level2(score FLOAT) RETURNS CHAR
BEGIN 
	DECLARE score_level CHAR ;
	CASE 
	WHEN score >= 90 THEN SET score_level='A';
	WHEN score >= 80 THEN SET score_level='B';
	WHEN score >= 70 THEN SET score_level='C';
	WHEN score >= 60 THEN SET score_level='D';
	ELSE SET score_level='E';
	END CASE;
	RETURN score_level;
END $

SELECT get_score_level2(80);

二 、循环结构

while loop repeat 三种循环语法
循环控制：

iterate 类似于java中continue，表示结束本次循环，继续执行洗一次循环

leave 类似于java中break,退出循环，终止循环

1.while 循环

语法：while 循环条件 do
循环体;
end while;

2.loop 循环

语法：loop

循环体;
end loop;

如果不在循环体中编写退出的话，则直接就是死循环

3.repeat 循环(用的少)

语法：
repeat
循环体;
until 结束循环的条件;
end repeat;

案例: 批量数据插入，根据传入的次数插入数据到admin表中

DELIMITER $
CREATE PROCEDURE pro_insert_admin(IN insertc_count INT)
BEGIN 
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertc_count DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('rose',i),'666');
		SET i = i+1;
	END WHILE;
END $

CALL pro_insert_admin(20);

java中：
int i = 1;
	while(i<=insertc_count){
		//执行相应的操作
		i++;
	}

给循环一个标签（用的少）

java中给循环加个标签，用的少	
a:while(....){
	while(....){
		break a;//结束哪个循环
	}
}

DELIMITER $
CREATE PROCEDURE pro_insert_admin1(IN insert_count INT)
BEGIN 
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insert_count DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('lucy',i),'666');
		SET i = i+1;
	END WHILE a;

END $

CALL pro_insert_admin1(10);

案例: 添加 leave 终止循环 ,增加到第 5 笔 就不增加了

DELIMITER $
CREATE PROCEDURE pro_insert_admin2(IN insert_count INT)
BEGIN 
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insert_count DO
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('lucy',i),'666');
		IF i >= 5 THEN LEAVE a; -- 表示完成5行插入则退出程序
		END IF;
		SET i = i+1;
	END WHILE a;

END $

CALL pro_insert_admin2(20);

添加 iterate 终止本次循环 ,奇数添加数据，偶数不新增数据

DELIMITER $
CREATE PROCEDURE pro_insert_admin3(IN insert_count INT)
BEGIN 
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insert_count DO
		SET i = i+1;
		IF MOD(i,2) = 0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('lucy',i),'666');
	END WHILE a;

END $

CALL pro_insert_admin3(20);

使用loop循环

DELIMITER $
CREATE PROCEDURE pro_insert_admin4(IN insert_count INT)
BEGIN 
	DECLARE i INT DEFAULT 0;
	a:LOOP
		IF i > insert_count THEN LEAVE a;
		END IF;
		SET i = i+1;
		IF MOD(i,2) = 0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username,PASSWORD) VALUES(CONCAT('lucy',i),'666');
	END LOOP a;

END $
CALL pro_insert_admin4(20);