SQL编程---存储过程和存储函数

1.基本概念

• 存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合。

2.存储过程和函数的好处

1. 提高代码的复用性。
2. 减少数据在数据库和应用服务器之间的传输，提高效率。
3. 减少代码层面的业务处理。

3.创建和调用存储过程

<1>创建存储过程

创建存储过程

	-- 修改分隔符为$[不然默认到;结束，后面的执行不到]
	DELIMITER $

	-- 标准语法
	CREATE PROCEDURE 存储过程名称(参数列表)
	BEGIN
		SQL 语句列表;
	END$

	-- 修改分隔符为分号[;前面有空格]
	DELIMITER ;

<2>调用存储过程

	调用存储过程
	CALL 存储过程名称(实际参数);

举例学生表使用存储过程查看信息

<1>创建stu_group()存储过程，封装 分组查询总成绩，并按照总成绩升序排序的功能

DELIMITER $$ -- 修改sql的结束符

CREATE PROCEDURE stu_group();
BEGIN
    SELECT gender ,SUM(score) getsum FROM student GROUP BY gender ORDER BY getsum ASC;
END $$

DELIMITER ; -- 将SQL的结束符改回;

<2>调用stu_group()存储过程

CALL stu_group();

结果：

<3>查看/删除存储过程

-- 查看存储过程（语法）
SELECT * FROM mysql.proc WHERE db = '数据库名称';
-- 删除存储过程（语法）
DROP PROCEDURE [IF EXISTS] 存储过程名称;

4.存储函数

<1>概念
存储函数和存储过程理解上是一样的，只是存储函数有返回值，必须写return语句。
<2>很多存储函数mysql底层帮我们实现了
如:

-- 获取系统当前时间
 SELECT NOW();
 
 SELECT YEAR ('2021-8-4'); -- 年
 
 SELECT MONTH ('2021-8-4');-- 月
 
 SELECT DAY ('2021-8-4');-- 日
 
 SELECT CONCAT ('a','b','c') AS str ; -- 拼接字符串
 
 SELECT CHAR_LENGTH ('hello'); -- 字符串长度
 
 SELECT LENGTH (''hello) -- 占用字节大小
 
 -- 格式化日期
 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS birthday -- 格式化日期

格式化日期结果:

<3>自己创建存储函数

创建存储函数
	CREATE FUNCTION 函数名称([参数 数据类型])
	RETURNS 返回值类型
	BEGIN
		执行的sql语句;
		RETURN 结果;
	END$

需求–定义存储函数，获取学生表中成绩大于95分的学生数量

DELIMITER $

CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
	-- 定义变量
	DECLARE s_count INT;
	-- 查询成绩大于95分的数量，为s_count赋值
	SELECT COUNT(*) INTO s_count FROM student WHERE score > 95;
	-- 返回统计结果
	RETURN s_count;
END$

DELIMITER ;

调用删除函数:

--调用函数
SELECT fun_test1();
-- 删除函数
DROP FUNCTION fun_test1;

5.存储过程和函数的区别

1. 返回值不同

   存储函数必须有一个且必须只有一个返回值，并且还要指定返回值的数值类型。存储过程可以有返回值，也可以没有返回值，甚至可以有多个返回值。

2. 两者赋值的方式不同：

   存储函数可以采用select …into …方式和set值得方式进行赋值，只能用return返回结果集。存储过程可以使用select的方式进行返回结果集。

3. 使用方法不同：

   函数可以直接用在sql语句当中，可以用来拓展标准的sql语句。存储过程，需要使用call进行单独调用，不可以嵌入sql语句当中。

4. 函数中函数体的限制较多：

   不能使用显式或隐式方式打开transaction、commit、rollback、set autocommit=0等。但是存储过程可以使用几乎所有的sql语句。