数据库-浅谈储存过程

缘起

数据库，已经是这个时代大部分软件系统架构里的标配。一个可靠的数据管理系统的 ACID特性也保证了我们使用到的数据的准确性。
作为软件架构中的基础部分，数据库的操作不应该暴露给用户应用层。所以相关数据的操作应该封装在服务层或者说是业务规则层中。
虽然常规认知中，数据库本身的主要职责是保证数据的存储与查询，但它的功能其实还有很多。在基础数据存储的本职工作外，还提供一些运行定义，定时运行，甚至发送邮件等扩展服务。基于最近的项目经验，想浅谈一下这扩展功能中的储存过程（Stored Procedure）。

储存过程

先看一下定义——“存储过程是在数据库中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（可选）来执行。”
简单来讲就是把数据处理逻辑封装为了一个公用函数。这个逻辑目前是定义在相关的数据库里的。在SQL Server，可以在数据库的Programmability 的Stored Procedures里找到目前定义的储存过程的列表，并进行相关修改查看等操作。
这个功能的本质还是使用SQL语句进行数据操作，所以这个功能的使用并不是一个必选项。只是当进行一个多成员协作的软件开发的时候，尤其是团队中有专业的DBA的时候，暴露合适的参数给其他人引用的话不仅可以提升交付效率也简化软件开发逻辑；而且由于数据库会自带一些加密和权限控制，所以其实可以有更高的数据安全性。

• 存储过程的优点：
  – 预编译SQL，提升执行效率；
  – 可以隐藏执行逻辑，只暴露名称和参数相较于程序来说，修改起来更加便捷存；
  – 数据安全性更高；
• 储过程的缺点：
  – 随着SQL行数的增加以及定义的过程的增加，维护复杂度呈线性提升；
  – 调试不方便，迭代过程中风险较高；
  – 破坏了MVC 三次结构设计，把逻辑功能定义在数据层；

示例

比如我要在SQL Server做一个查询特定年月对应数据的汇总，大概的实现思路是需要有年月对应的参数：

USE [DB2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTERPROCEDURE[dbo].[GetStats] @Year INT,@Month INT AS
BEGIN
	SELECT *
	INTO #SelectedMonthTransactions
	FROM [dbo].[Stats]
	WHERE YEAR(StartTime)=@Year AND MONTH(StartTime)=@Month AND Name !='';
	SELECT
	Name,
	SUM(CASE WHEN Level = 1 THEN 1 ELSE 0 END) AS Level1Count,
	SUM(CASE WHEN Level = 1 THEN DATEDIFF(second, StartTime, CloseTime)ELSE 0 END) / 60.0 AS Level1Sum,
	SUM(CASE WHEN Level = 2 THEN 1 ELSE 0 END) AS Level2count,
	SUM(CASE WHEN Level = 2 THEN DATEDIFF(second, StartTime, CloseTime)ELSE 0 END) / 60.0 AS Level2Sum,
	COUNT(DISTINCT CASE WHEN Level = 2 THEN Context END) AS Level2DCount
	FROM #SelectedMonthTransactions
	GROUP BY Name;
END;

小结

本文更多是自我解决工作难题的同时做的一个小记录。SQL本身作为一种早年的企业级商用低代码设计语言，虽然语法上与自然语言很类似，但是与其他编程语言的结构是格格不入的。虽然这样对使用者没有很高的门槛，但其实码农用起来还是觉得别扭（所以很多人选择用linq）。储存过程的存在感觉是一种数据层封包的早年尝试，把单元功能定义的足够完善的话，也许可以使用起来更简洁轻松。