MySQL 存储过程和函数

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

好处：
1、提高代码的复用性
2、简化操作

一、 存储过程

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

（1）创建语句

 CREATE PROCEDURE 存储过程的名字(参数列表)
 BEGIN 
	存储过程体(一组合法的 SQL 语句 )

 END;
 

注意：
1、参数列表包含三部分
参数模式 参数名 参数类型
举例

 IN stuname VARCHAR(20)

参数模式：
IN :该参数可作为输入，也就是该参数需要调用方传入值
OUT ：该参数可以作为输出，也就是该参数可以作为返回值
INOUT :该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话， BEGIN END 可以省略
存储过程体中的每条 SQL 语句的结尾要求必须加上分号
存储过程的结尾可以使用 DELIMITER 重新设置

 --语法：
 DELIMITER  结束标记
 --案例：
 DELIMITER $	

（2）调用语法

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

1、空参列表

插入

DELIMITER $       

CREATE PROCEDURE myadmin()

BEGIN  
	INSERT INTO admin(username,`password`) 	VALUES('张世杰','ming456'),('唐莲','admin'),('雷鸣','ray520'),('李如意','ling526'),('Ray','fang203');
END $

--调用
 CALL myadmim

2.创建带in模式参数的存储过程

案例一
创建存储过程实现，根据女神名，查询对应的男神名

USE girls;
 
DELIMITER $
CREATE PROCEDURE myboys(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.`boyName`
	FROM boys bo
	RIGHT JOIN beauty m 
	ON bo.`id`=m.`boyfriend_id`
	WHERE m.name=beautyName;
END $
--调用
CALL myboys('周芷若')$

3、创建带out模式的存储过程
案例1：根据女神名,返回对应的男神名

DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo 
	LEFT JOIN beauty m 
	ON bo.id=m.boyfriend_id
	WHERE m.name=beautyName;
END $
--调用
CALL myp5('小昭',@bname)$
--查询
SELECT @bname$

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

DELIMITER $
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
	SELECT bo.boyName,bo.userCP INTO boyName,userCP
	FROM boys bo
	INNER JOIN beauty m
	ON bo.id=m.boyfriend_id
	WHERE m.name = beautyName;
END $
--调用
CALL myp6('周芷若',@bname,@userCp)$
--查询
SELECT @bname,@userCp$

4、创建带inout模式参数的存储过程
案例1：传入a和b的值，最终a和b都翻倍

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

--分别为@x,@y设置值
SET @x = 10;
SET @y = 20;
--调用
CALL myp7(@x,@y)$

-- 查询
SELECT @x,@y$

（3）删除存储过程

drop procedure 存储过程名称;

（4）查看存储过程的信息

SHOW CREATE PROCEDURE 存储过程名称;

二、函数

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

区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果

（1）创建语法

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

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

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

return 值;

3.函数体中仅只有一句话，则可以省略begin end
4.使用delimiter语句 设置结束标记

如：
delimiter $

（2）调用语法

SELECT 函数名(参数列表)

1.无参有返回
案例：返回公司的员工个数

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

-- 调用
SELECT myf1()$

2.有参有返回

案例：根据员工名，返回它的工资

CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE 
BEGIN
	SET @sal = 0;#定义用户变量  
	SELECT salary INTO @sal #赋值
	FROM employees 
	WHERE last_name=empName;
	RETURN @sal;
END $
--调用
SELECT myf2('Kochhar') $

案例①：根据部门名，返回该部门的平均工资

CREATE FUNCTION myf3(depName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @avgSalary= 0;#定义用户变量  
	SELECT AVG(salary)  INTO @avgSalary #赋值
	FROM employees e 
	INNER JOIN departments d
	ON e.department_id=d.department_id
	WHERE d.department_name=depName;
	RETURN @avgSalary;

END $

-- 调用
SELECT myf3('Mar')

案例②：创建函数，实现传入两个float，返回二者之和

CREATE FUNCTION Demo(num1 FLOAT,num2 FLOAT)RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM =num1+num2;
	RETURN SUM; 
END $
--调用
SELECT Demo(5,7)$

三、查看函数

SHOW CREATE FUNCTION 函数名;

四、删除函数

DROP FUNCTION 函数名;