Mysql之存储过程与函数

存储过程概述

官网解释是：存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过 预先编译 的 SQL 语句的封装。
执行的过程：
存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用
存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行

好处：
1.简化操作，提高了sql语句的重用性，减少了开发程序员的压力
2.减少操作过程中的失误，提高效率
3.减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器） 4、减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

注意：存储过程一旦被创建出来，我们直接调用存储过程名就行 (类似函数的调用)

不过需要注意的是：存储过程是没有返回值的

分类

存储过程根据有无参数和有无返回值进行分类
1.没有参数（无参数无返回）
2.仅仅带 IN 类型（有参数无返回）
3.仅仅带 OUT 类型（无参数有返回）
4.既带 IN 又带 OUT（有参数有返回）
5.带 INOUT（有参数有返回）

关键字为IN, OUT,INOUT, 可以多个存在，多个存在用 | 连接

IN: 输入参数，代表着入参
OUT：输出参数，代表着出参
INOUT:既是输入参数，又是输出参数

创建存储过程

一般的语法格式

语法格式：

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
BEGIN 
	#存储的Mysql语句
END

语法格式类似于C语言中的函数
如果只有一条Mysql语句，可以省略BEGIN 和END，如果有多条Mysql语句，那么就不能省略

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符

一般使用 $或者 // 来作为语句结束的符号

完整的语法格式

DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
sql语句1;
sql语句2;
END $
DELIMITER;  #注意：这个步骤不要忘记

案例一

创建存储过程select2_all_data()，查看 jobs 表的所有数据

DELIMITER $
CREATE PROCEDURE select2_all_data()
BEGIN
SELECT * FROM jobs;
END $
DELIMITER;

#CALL 用CALL调用
CALL select2_all_data();

案例二

这里注意：使用OUT输出结果的时候，在Mysql语句中药结合INTO 一起使用作为， INTO后面为变量名，存储结果

DELIMITER $ 
CREATE PROCEDURE show_data(IN empname varchar(20), OUT empsalary varchar(20))
BEGIN
	SELECT salary INTO empsalary FROM emp WHEREE last_name = empname
END $ 
DELIMITER;

#这里注意：使用set设置，  @ + 变量名
set @empname = 'ABEl';

#调用
show_data(@empname, @empsalary);

#选择
SELECT @emsalary;

调用存储过程

存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname

调用语法格式

IN参数模式

#IN参数 调用
CALL 存储过程名('值')

OUT参数模式

#OUT参数  调用
set @变量名
CALL 存储过程名(@变量量)
SELECT @变量名

INOUT参数模式

set @变量名  = '值'
CALL 存储过程名(@变量名)
SELECT @变量名

创建存储函数

语法格式

DELIMITER //
CREATE FUNCTION 变量名(参数)
[character 约束条件]
RETURN 返回值类型

BEGIN 
	函数体
END //
DELIMITER;

特别注意：

案例：创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;


#调用
SELECT email_by_name();

调用，这里使用SELECT 关键字来调用存储函数

存储过程与函数的查看，修改和删除

查看信息
语法格式

#查看创建信息
SHOW CREATE FUNCTION 名字;
SHOW CREATE PROCEDURE 名字;

#查看状态信息  （可用LIKE 取别名）
SHOW FUNCTION STATUS 名字;
SHOW PROCEDURE STATUS 名字;

修改信息
使用关键字ALTER

#修改定义
ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER ;

删除信息
使用关键字DROP

DROP FUNCTION IF  EXISTS 名字;
DROP PROCEDURE IF EXISTS 名字;