

Jim King is the visionary expert in the field of BI, database, Java development and programming. He has more than 10 years of experience in BI and development of Java applications, like esProc and esCalc. He is passionate about applying simpler methods to solve the complex problems of users. He is also an active blogger and has released a serial of original ideas on BI and Java development, such as BI, database application development, report development with help of Java, SQL, etc. Jim has posted 14 posts at DZone. You can read more from them at their website. View Full User Profile

Alternative to Difficult Stored Procedures in Big Data Computation

  • submit to reddit


In the past, the data structure and business logics were so simple that one SQL statement was enough to achieve the user's computational goal. With the rapid growing of the information industry, users frequently find that they need to achieve increasingly complex computational goals to out-perform their competitors. To address such computations, SQL alone is far from enough. Database programmers have the additional demands regarding the judge and loop statements, branches at multiple levels, or more accurate data transverse operations, as well as decomposing an obscure goal into several clear and actionable steps correlated with complex logics. It was for all these demands that the stored procedure was introduced.

The stored procedure has so far become the preferred tools for complex data computations, playing an important role. However, stored procedures still cause various inconveniences. For example, lots of their functions are inconvenient - hard to debug or migrate; and there are some databases with rather weak stored procedures. These problems sometimes affect the efficiency of database developers seriously.


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 
11      if tb_count=0 then
                     stateTmp NUMBER not null,
                     productTmp varchar2(10)  not null,
                     amountTmp NUMBER not null
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 (
         and rowid not in (
     order by state,product;
17      OPEN io_cursor for
18      SELECT productTmp  FROM (
    SELECT stateTmp,productTmp,amountTmp,rankorder
    WHERE rankorder<=10 order by stateTmp
  GROUP BY productTmp 

In which, the code at line 16 is to filter the duplicates, and write the filtered data to the ”temporary table." Since it is difficult to retrieve the distinct data directly, try this tip: find the duplicate data, then use "not" to reverse the condition, and the remaining is the distinct data. This function can be implemented by embedding two sub-queries.



Although there are Oracle, DB2, and other databases offering debug functions for their respective stored procedures in the market, their debug functions are incomplete. In running the stored procedure, no matter if a SQL statement is long or short, no matter how many nested loops or computational steps it contains, programmers can only view the result of one statement, and the intermediate procedure is completely transparent to them. It rather defeats the purpose of step-by-step debug, and compromising the benefits of running to cursor or the next step. The programmers are actually only allowed to view the cursor and the simple variables. Such variables are useful, but by no means of the same importance as the intermediate procedure of SQL. Another trouble is that lots of setting and preparation workloads are required to launch the debugging tools. The beginners can hardly handle it without guides.



Obviously, the inconveniences of stored procedures have compromised the computational performance of databases, and given much trouble to programmers – considerable development difficulty, inefficient development, and awkward maintenances. In addition, these inconveniences also affect the result of implementing business logics, achieving complex computational goals, and making smart business decisions. Then how do you empower the stored procedure?




esProc can empower the stored procedures, and ultimately boost the computational capability of a database, reduce the development difficulty for programmers, improve the development efficiency, and facilitate the code maintenance and migration. esProc can implement the complex data algorithms and business logics easily.


Published at DZone with permission of its author, Jim King.
