大数据计算中复杂存储过程的替代方案

我们知道，存储过程的设计是为了处理具有复杂业务逻辑的计算。

以往的数据结构和业务逻辑比较简单，所以单条SQL语句就可以实现业务的需要。但随着技术的进步，用户计算的复杂程度也越来越高。要实现复杂的计算，单条SQL语句就显得不是很够了。将一个复杂目标分解为几个有逻辑、清晰、可执行的步骤，数据库开发人员对循环和判断语句、多层分支以及更精确的数据横向操作有了额外的需要。基于这些需要，我们引入了存储过程。

存储过程是目前复杂数据计算的首选工具，在数据计算领域起着很大的作用。然而，存储过程也会造成各种不便。例如，许多函数难以调试或迁移，某些数据库对存储过程的支持也不是很好。这些问题影响了数据库开发人员的效率。

存储过程的不方便之处体现在逐步计算的不完善，对集合化数据计算的支持较差，不能为数据集编号，也没有对象引用机制。举个简单的例子，如果要在区域销售报表中找出“在任何州都最畅销的N个产品”，编写存储过程就显得有些复杂了。

01    create or replace package salesPkg
02    as
03             type salesCur is ref cursor;
04    end;
05    CREATE OR REPLACE PROCEDURE topPro(io_cursor OUT salesPkg.salesCur) 
06    is
07       varSql varchar2(2000);
08       tb_count integer;
09    BEGIN 
10      select count(*) into tb_count from dba_tables where table_name='TOPPROTMP';
11      if tb_count=0 then
12      strCreate:='CREATE GLOBAL TEMPORARY TABLE TOPPROTMP (
                     stateTmp NUMBER not null,
                     productTmp varchar2(10)  not null,
                     amountTmp NUMBER not null
              )
              ON COMMIT PRESERVE ROWS';
13      execute immediate strCreate;
14      end if;
15      execute immediate 'truncate table TOPPROTMP';
16      insert into TOPPROTMP(stateTmp,productTmp,amountTmp)
     select state,product,amount from stateSales a
       where not(
         (a.state,a.product) in (
           select state,product from stateSales group by state,product having count(*) > 1
         )
         and rowid not in (
           select min(rowid) from stateSales group by state,product having count(*)>1
         )
       )
     order by state,product;
17      OPEN io_cursor for
18      SELECT productTmp  FROM (
    SELECT stateTmp,productTmp,amountTmp,rankorder
    FROM (SELECT stateTmp,productTmp,amountTmp,RANK() OVER(PARTITION BY stateTmp ORDER BY amountTmp DESC) rankorder 
          FROM TOPPROTMP
         ) 
    WHERE rankorder<=10 order by stateTmp
    )
  GROUP BY productTmp 
  HAVING COUNT(*)=(SELECT COUNT(DISTINCT stateTmp ) FROM TOPPROTMP);
END;

在示例代码中，第16行是过滤重复项，并将过滤的数据写入“临时表”。因为不方便直接检索不同的数据，所以我们要先找出重复的数据，再用“not”来反转条件，这样剩下的便是不同的数据。我们用了两个子查询来实现这个函数。

第18行是要找出“在任何洲都排名前十的产品”。因为SQL没有提供交集函数，所以我们需要先用统计分析函数获取每个洲的产品排名，然后筛选出每个州前十的产品。最后，如果某个产品在每个洲都进入了前十，我们就把它存储起来。另一个方法是按产品分组，然后确认某一产品的数量是否等于州的数量；如果相等，则说明这个产品在每个州均排名前十。

除了函数不方便以外，存储过程对调试的支持相当也相当薄弱。

虽然Oracle，DB2和其他数据库为其各自的存储过程提供了调试功能，但是这些功能并不完整。在执行存储过程时，无论SQL语句长短，无论包含多少层嵌套循环或计算步骤，开发者都只能查看这一整条语句的执行结果，而中间过程哪一步出错则是不可见的。这就失去了逐步调试的目的。开发者实际上只能看到光标和简单变量。虽然这些变量有用，但没有SQL的中间过程那么重要。并且启动调试工具需要大量的设置和准备工作。

第三个不便之处是难以迁移。一般来说，对SQL语句进行一些简单的修改，就可以迁移了。尽管语法细节略有不同，但各厂商的SQL语句都基于ANSI标准。但是，存储过程就不一样了。因为各个厂商的标准不尽相同，差异还比较大，存储过程的迁移比重写还要复杂。在这种情况下，出于开发成本考虑，用户只好选定一个固定的数据库供应商，并且长期用下去。这样，如果数据库供应商在用户需要升级服务器、存储和授权时收费过高，那么用户也没有很大的议价空间。

任何数据库都有基本的SQL功能，但存储过程就不一定了。有的数据库提供的存储过程功能较弱，有的干脆就不提供。就拿MySQL来说，MySQL的存储过程在功能和性能上比起MS SQL、Oracle和其他大型商用数据库就显得比较弱，并且MySQL在密集并发时还可能抛出许多异常。MSSQL Compact、SQLITE、Hive、Access和还有其他一些数据库则没有提供存储过程的功能。

显然，存储过程的不便性降低了数据库的计算性能，增加了开发和维护的难度，降低了开发的效率，这些缺点还会影响到业务逻辑、计算目标以及业务决策的实现。那么，我们有没有什么办法改进这个功能呢？

esProc是专为解决复杂计算目标而设计的数据库计算脚本，提供了直观的网格界面、分步计算、专业的调试功能、灵活的语法、完整的计算系统以及对不同数据库之间交互式计算的无缝支持，计算性能与存储过程相当甚至更优。

esProc是一个网格界面的脚本工具。通过esProc，计算逻辑可以很方便地显示在屏幕上，业务算法也可以更容易地被解释为编程语言。esProc支持逐步计算，用户能够将复杂的目标分解为网格中的几个小步骤，然后通过这些小步骤来实现复杂的目标。根据“分步计算”的思想，esProc引入了真正实用的调试功能，包括断点、分步执行、执行至光标、开始和结束等。与SQL或存储过程(SP)的伪调试脚本不同，esProc可以直接调试基本步骤，不需要构建特定的中间表。断点可以设置在任何一处，并不需要改动代码。

esProc支持集合的真实数据类型。集合中的成员可以是任何简单数据类型的数据、记录或其他集合。esProc支持有序集合，用户可以访问集合成员并执行与数据编号相关的计算，例如排名、排序、同比和环比。集合的集合可以用于表示等分组、对齐分组和枚举分组。此外，用户可以像操作对象一样操作单个记录。esProc中灵活的语法可以更容易地表示复杂的计算，例如计算多级分组中的相对位置，并通过指定的集合进行分组汇总。

esProc可以改进存储过程，从根本上提升数据库的计算能力，减少程序员的开发难度，提高开发效率，便于代码的维护和迁移，轻松实现复杂的数据算法和业务逻辑。

综上，我们讨论了存储过程的不便之处，下面是esProc的解决方案。

原文：Alternative to Difficult Stored Procedures in Big Data Computation

作者：Jim King

译者：Kevin Chen

原文链接：https://dzone.com/articles/alternative-difficult-stored