MySQL之存储过程和函数

MySQL版本：5.5.62-LOG

1.什么是存储过程和函数？

相同点:

存储过程和函数是事先经过编译并存储到数据库中的一段SQL语句的集合，简化应用开发人员的工作，减少数据在数据库和应用服务器之间的传输，提高数据处理的效率。

不同点:

存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL，那么就可能因此需要将函数改造成存储过程。

2.存储过程和函数需要的权限

对于我们创建存储过程和函数，MySQL首先是需要确认当前操作的用户是否具有相应的权限的

• 创建：需要CREATE ROUTINE权限
• 修改或者删除：需要ALTER ROUTINE权限
• 执行：需要EXECUTE权限

3.创建存储过程和函数

语法：

创建存储过程:sayHi

DELIMITER // -- 用于定义当前的存储过程的结束符，可以避免与;冲突
CREATE PROCEDURE sayHi()
BEGIN
   SELECT 'hello world!'; -- 创建存储过程的SQL主体
END //	-- 表示当前的存储过程结束
DELIMITER ; --还原mysql中的操作结束符

创建函数:say

DELIMITER //
CREATE FUNCTION say(context VARCHAR(20)) --这里添加参数的时候需要按照参数名称 参数类型
RETURNS VARCHAR(20)		--这里需要返回值还有返回值类型
BEGIN
    RETURN context;
END //
DELIMITER ;

4.使用存储过程和函数

使用存储过程语法：call 存储过程名

-- 调用上面的存储过程sayHi
CALL sayHi();

使用函数：select 函数名

-- 调用上面创建的函数say
SELECT say('hello world!');

5.使用存储过程或者函数需要注意的地方

1. 在存储过程中可以执行commit和rollback,也可以执行其他的存储过程和函数
2. 不能在存储过程或者函数中执行load data infile语句

6.修改存储过程或函数

发现当前的存储过程或函数的修改只能修改权限或者验证之类的但是不能修改当前执行的SQL语句！

7.查看存储过程

1. 查看存储过程或函数的状态
   show [procedure|function] status like '存储过程名或者函数名'
2. 查看存储过程或者函数的定义
   show create [procedure|function] 存储过程名或函数名;
3. 通过查看information_schema.Rountines了解存储过程和函数信息
   SELECT * FROM information_schema.ROUTINES WHERE routine_name='存储过程名或函数名'

8.删除存储过程或函数

语法：drop [procedure|function] [if exists] 存储过程名称

-- 删除存储过程
DROP PROCEDURE IF EXISTS sayHi ;

-- 删除函数
DROP FUNCTION IF EXISTS say

9.总结

1.存储过程和函数是一种存在服务器端的预编译的SQL语句，可以大大的提高开发者的能力

2.执行存储过程或函数需要execute权限，删除或者修改需要alter ROUTINE权限，创建需要create ROUTINE权限

3.存储过程与函数的区别在于，函数需要指定returns 参数类型，其中需要return 返回值(与定义的returns中的类型一致)

4.可以通过information_schema中的ROUTINES表查看所有存储过程和函数,其类别有ROUTINE_TYEP决定

以上纯属个人见解，如有问题请联本人！