MySQL 中的过程和函数

存储过程和函数

存储过程和函数：类似java中的方法

存储过程：自足预先编译好的SQL语句的集合，理解成批处理语句

好处：
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据可服务器的连接次数，提高了效率

一、创建函数

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

注意
1、begin - end 语句的作用类似于java中的{}
2、参数泪飙包含三部分
	参数模式	参数名		参数类型
ex.  in name varchar(20)
3、参数模式
	in:该参数可以作为输入值，也就是该参数需要调用方法传入值
	out:该参数可以作为输出值，也就是该参数可以作为返回值
	inout : 该参数既可以作为输入有可以作为输出，也就该参数既需要传入值，有需要返回值
4、如果存储过程体只有一句话：begin - end 可以省略
5、存储过程体中的每条SQL语句结尾必须加分号
6、存储过程体的结尾可以用 delimiter 关键字重新设置
	语法：
		delimiter 结束标记;
	如果读到结束标记,函数结束

二、调用语法

语法
call 存储过程名(实参列表) 结束标记;

SELECT * FROM ADMIN;

案例：插入到admin表中5条记录

DELIMITER $;
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO `admin`(username,`password`) 
	VALUES
	('john1','0001'),
	('john2','0002'),
	('john3','0003'),
	('john4','0004'),
	('john5','0005');
END $

调用

CALL myp1()$;

2、创建一个 in 参数模式的存储过程

根据女神名，查询对应的男生信息

CREATE PROCEDURE myp2(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 myp2('刘德华')$

案例2：创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN 
	DECLARE result INT DEFAULT 0;# 声明并初始化
	
	SELECT COUNT(*) INTO result #赋值：把结果传给result
	FROM `admin` 
	WHERE admin.username = username
	AND padmin.assword = PASSWORD;
	
	SELECT IF (result>0,'成功','失败');#使用
END$;

调用

CALL myp3(‘张飞’,‘8888’)$;

3、创建带out模式的存储过程

案例1： 根据女神名，返回对应的男神名

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

调用

SET @ bName$;
CALL myp5('小昭',@bName)$;
SELECT @bName;

案例2 根据女神名，返回对应的男神名和男神魅力值

CREATE PROCEDURE mpy6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN 
	SELECT b.boyName,b.userCP INTO boyName,userCP
	FROM beauty g
	INNER JOIN boys b
	ON b.id = g.boyfriend_id
	WHERE g.name = beautyName;

END $;

调用

SET @userCP;
CALL myp6('小昭',@bName,@userCP);

4、创建带inout 模式参数的存储过程

案例1：传入a和b两个值，最终a和b都翻倍并返回

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

调用

SET @a = 10;
SET @b = 20;
CALL myp8(@a,@b)$;
SELECT @a,@b;

创建存储过程或函数实现比较两个女神的生日，返回大小

CREATE PROCEDURE compara(IN beautyName1,IN beautyName2 OUT result VARCHAR(20))
BEGIN 
	DECLARE b1 DATE
	DECLARE b2 DATE
	SELECT g1.borndate,g2.borndate INTO b1,b2
	FROM beauty g1,beauty g2
	WHERE g1.name = beautyName1
	AND g2.name = beautyName;
	result = IF(b1>b2,1号女神大，2号女神大);
END $;

二、存储过程的删除

语法：
drop procedure 存储过程名

查看存储过程的信息

SHOW CREATE PROCEDURE mpy2;

练习1：创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日，并返回

CREATE PROCEDURE test_pro1(IN birth DATETIME,OUT strDAte VARCHAR(20))
BEGIN 
	SELECT DATE_FORMAT(mydate,'%y年%m月%日d') INTO strDate;
END $;

练习2：创建存储过程或函数实现传入一个女神名，返回：女神 and 男神 格式的字符串

如 传入：小昭

返回 小昭 and 张无忌

CREATE PROCEDURE test_pro2(IN beautyName VARCHAR(20),OUT str VARCHAR(50)) 
BEGIN 
	SELECT CONCAT(beautyName,'and',IFNULL(boyName,'没有男朋友')) INTO str 
	FROM beautys g
	LEFT JOIN boys b
	ON b.id = g.boyfriend_id     
	WHERE g.name = beautyName;
END $;

CALL test_pro2('柳岩',@str)$;
SELECT @str $;

创建存储过程或函数，根据传入的条目和其实索引，查询beauty表的记录

CREATE PROCEDURE test_pro3(IN startIndex INT,IN size INT)
BEGIN 
	SELECT * FROM beau9ty LIMIT startIndex,size
END $;
CALL test_pro3(3,5);

函数

存储过程和函数：类似java中的方法

函数：自足预先编译好的SQL语句的集合，理解成批处理语句

好处：
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据可服务器的连接次数，提高了效率


函数和存储过程的区别
存储过程：可以有0个返回值，也可以有多个返回值，
适合左批量的插入，批量的更新

函数:有且仅有1个返回值
适合处理一个数据后返回一个结果

一、函数的创建语法


create function 函数名（参数列表）return 返回类型
begin
	函数体
end $;


注意：
1、参数列表包含两部分：参数名  参数类型

2、函数体：肯定会有return语句，如果没有会报错
   如果return语句没有放在函数体的最后也不会报错，但不建议

3、函数体中仅有一句话，则可以省略begin end

4、使用 delimiter 语句设置结束标记

二、调用语法
select 函数名（参数列表）

三、查看函数
show create function 函数名
   
四、删除函数
drop function 函数名；   

案例【无参数有返回】返回公司公司员工的个数

CREATE FUNCTION myf1() RETURNS INT 
BEGIN 
	DECLARE c INT DEFAULT 0; #定义局部变量
	SELECT COUNT(*) INTO c #赋值
	FROM employees;
	RETURN c;
END $;

SELECT myf1()$;

【有参有返回】根据员工名，返回他的工资

CREATE FUNCTION myf2(employeeName VARCHAR(20)) RETURN DOUBLE
BEGIN 
	SET @salary = 0; # 定义用户变量
	SELECT e.salary INTO salary # 赋值
	FROM employees e
	WHERE last_name = employeeName;
	RETURN @salary;

END $;