mysql 存储过程与存储函数

1  存储过程

存储过程就是一条或者多条SQL语句的集合，可以视为批文件。它可以定义批量插入的语句，也可以定义一个接收不同条件的SQL。

1.1 存储过程语法

1.1.1  创建

CREATE  PROCEDURE `NewProc`(IN `contid` int,OUT `contNo` varchar(200))
BEGIN
SELECT CONT_NO into contNo from bs_cont where id= `contid` ; --分号要加
END

语法： CREATE PROCEDURE sp_name(定义输入输出参数) [ 存储特性 ]  BEGIN SQL语句；  END

IN 表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出的参数。sp_name为存储过程的名字。

如果此存储过程没有任何输入输出，其实就没什么意义了，但是sp_name()的括号不能省略。

1.1.2 注意：

　　1. 先定义名称，在定义类型  （ IN `id` int）。

　　2. 给出参变量赋值要用 into

1.1.3 查看刚才创建的存储过程。

　　 SHOW PROCEDURE STATUS LIKE 'g%';

1.1.4 下面是调用存储过程。对于存储过程提供的临时变量而言，MySQL规定要加上@开头。

1. #study 是当前数据库名称
2. CALL study.findCont(1,@contNo);

SELECT @contNo;

 

1.1.5  相对于直接使用SQL 语句，在应用程序中直接调用存储过程(存储函数)有以下好处：

 

　　(1)减少网络通信量。
存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。
　　(2)执行速度更快。
有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化（编译）。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

　　(3)可维护性：的存储过程有些时候比程序更容易维护，这是因为可以实时更新DB端的存储过程。  有些bug，直接改存储过程里的业务逻辑，就搞定了

　　(4) 分布式工作。
应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

　　(5) 增强安全性：提高代码安全，防止 SQL注入。这一点sql语句也可以做到。

1.1.6 缺点：
　　1.SQL本身是一种结构化查询语言，我们不应该用存储过程处理复杂的业务逻辑——让SQL回归它“结构化查询语言”的功用。复杂的业务逻辑，还是交给代码去处理吧。 

　　2.  开发调试复杂，存储过程的开发调试不能像一般的程序代码那样debug调试排除错。 
　　3. 不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

2  存储函数

存储函数与存储过程本质上是一样的，都是封装一系列SQL语句，简化调用。我们自己编写的存储函数可以像MySQL函数那样自由的被调用。

2.1 存储函数语法

2.1.1  创建 （类型 1 和类型2）

CREATE FUNCTION getStuNameById(stuId INT)  -- 默认是IN，但是不能写上去。stuId视为输入的临时变量
RETURNS VARCHAR(255)   -- 指明返回值类型
RETURN  (SELECT name FROM t_student WHERE id = stuId); //  -- 指明SQL语句,并使用结束标记。注意分号位置

CREATE DEFINER=`vx`@`%` FUNCTION `calInvoiceAmountForRe`(sourceType VARCHAR(20),sourceId BIGINT) RETURNS decimal(12,2)
begin
-- 定义两个变量
declare invocieAmountRe decimal(13,2);  
declare invocieAmountAr decimal(13,2); 
-- 来自于实收的时候：检查sourece_id对应的实收已经开票的金额合计
select  sum(a.amount_invoice) from fin_invoice_detail a inner join fin_invoice b
on a.INVOICE_NO = b.INVOICE_NO
where b.STATUS <> '5' and b.is_del ='0' and a.type is null 
and a.SOURCE_ID = sourceId and a.SOURCE_TYPE = sourceType
group by a.SOURCE_TYPE,a.SOURCE_ID into invocieAmountRe;


--来自于实收的时候：检查sourece_id对应的实收已经开票的金额
select  sum(a.amount_invoice) from fin_invoice_detail a inner join fin_invoice b
on a.INVOICE_NO = b.INVOICE_NO
where b.STATUS <> '5' and b.is_del ='0' and a.type is null 
and a.SOURCE_ID in (select charge_detail_id from fin_received_charge_relate where id = sourceId )  and a.SOURCE_TYPE = 'AR'
group by a.SOURCE_TYPE,a.SOURCE_ID into invocieAmountAr;
   
return IFNULL(invocieAmountRe,0) +IFNULL(invocieAmountAr,0);
end

2.2.2 使用存储函数。

　　SELECT getStuNameById(1);

2.2.3 注意：

　　1. 在RETURN 语句后面，有趣的是，分号在SQL语句的外面。如果不加分号，查询结果居然查询出两条记录，很奇怪。

　　2. 给出参变量赋值要用 into。

3  相对于直接使用SQL 语句，在应用程序中直接调用存储过程(存储函数)有以下好处：

　　(1)减少网络通信量。
存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。
　　(2)执行速度更快。
有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化（编译）。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

　　(3)可维护性：的存储过程有些时候比程序更容易维护，这是因为可以实时更新DB端的存储过程。  有些bug，直接改存储过程里的业务逻辑，就搞定了

　　(4) 分布式工作。
应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

　　(5) 增强安全性：提高代码安全，防止 SQL注入。这一点sql语句也可以做到。

3.1 缺点：
　　1.SQL本身是一种结构化查询语言，我们不应该用存储过程处理复杂的业务逻辑——让SQL回归它“结构化查询语言”的功用。复杂的业务逻辑，还是交给代码去处理吧。 

　　2.  开发调试复杂，存储过程的开发调试不能像一般的程序代码那样debug调试排除错。 
　　3. 不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。

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

　　（1）存储函数的限制比较多,例如不能用临时表,只能用表变量,而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。

　　（2）返回值不同。存储函数必须有返回值（必须包含一条return语句  ）,且仅返回一个结果值（一行一列）；存储过程可以没有返回值（不允许包含return语句）,但是能返回结果集(out,inout)（一行多列）。

　　（3）调用时的不同。存储函数嵌入在SQL中使用,可以在select 存储函数名(变量值)；存储过程通过call语句调用 call 存储过程名。

　　（4）参数的不同。存储函数的参数类型类似于IN参数，没有类似于OUT和INOUT的参数（没有出参）。存储过程的参数类型有三种，IN、out和INOUT：

　　　　a. in：数据只是从外部传入内部使用(值传递),可以是数值也可以是变量

　　　　b. out：只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量

　　　　c. inout：外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量。

https://blog.csdn.net/yanluandai1985/article/details/83656374   

存储函数和存储过程

https://blog.csdn.net/yanluandai1985/article/details/83689524   存储过程 语法