1 2 Previous Next 16 Replies Latest reply on Mar 8, 2013 4:48 AM by BS2012 Go to original post
      • 15. Re: What kind of input parameter is used in SUM function?
        BluShadow
        BS2012 wrote:
        Hi Blu,
        Thanks a lot for sharing all this. Don't get confused. My question was if I need to build a same functionality in PLSQL as SUM...how can I do that?
        I'm not confused. I'm perfectly clear in my understanding of the functionality of SUM and how it's implemented, or how you implement aggregate functions using user defined functions in PL/SQL (as already demonstrated).

        The confusion seems to be in the description of the requirements. If the previous answers do not answer the question then the question (requirement) is not being asked correctly.

        What do you actually mean by "build the same functionality in PL/SQL"?

        User defined aggregate functions are building the same functionality in PL/SQL.
        Or do you want to have some more convoluted plain PL/SQL to poorly simulate something similar, such as...
        SQL> select sum(sal) as sal_sum from emp;
        
           SAL_SUM
        ----------
             29025
        
        SQL> select deptno, sum(sal) as sal_sum from emp group by deptno order by 1;
        
            DEPTNO    SAL_SUM
        ---------- ----------
                10       8750
                20      10875
                30       9400
        
        SQL>
        SQL> create or replace package pkg_sum as
          2    function initialize(val number) return number;
          3    function iterate(val number) return number;
          4    function terminate(val number) return number;
          5  end;
          6  /
        
        Package created.
        
        SQL>
        SQL> create or replace package body pkg_sum as
          2    val number;
          3    function initialize(val number) return number is
          4    begin
          5      pkg_sum.val := initialize.val;
          6      return pkg_sum.val;
          7    end;
          8    function iterate(val number) return number is
          9    begin
         10      pkg_sum.val := nvl(pkg_sum.val,0)+iterate.val;
         11      return pkg_sum.val;
         12    end;
         13    function terminate(val number) return number is
         14    begin
         15      return iterate(val);
         16    end;
         17  end;
         18  /
        
        Package body created.
        
        SQL>
        SQL> select sal_sum
          2  from (
          3        select cast(decode(rownum,1,decode(pkg_sum.initialize(sal),null,null),count(*) over (),pkg_sum.terminate(sal),decode(pkg_sum.iterate(sal),null,null)) as number) as sal_sum
          4        from emp
          5       )
          6  where sal_sum is not null
          7  /
        
           SAL_SUM
        ----------
             29025
        
        SQL> ed
        Wrote file afiedt.buf
        
          1  select deptno, sal_sum
          2  from (
          3        select deptno, cast(decode(row_number() over (partition by deptno order by deptno),1,decode(pkg_sum.initialize(sal),null,null),count(*) over (partition by deptno),pkg_sum.terminate(sal),decode(pkg_sum.iterate(sal),null,null)) as number) as sal_sum
          4        from emp
          5       )
          6* where sal_sum is not null
        SQL> /
        
            DEPTNO    SAL_SUM
        ---------- ----------
                10       8750
                20      10875
                30       9400
        ... which is something I wouldn't recommend doing, as it's completely pointless and doesn't allow for easy usage, as well as the group sums probably only working by luck in that example due to the analytical partitioning. (And let's face it if you really wanted some PL/SQL code to take a series of numbers and add them up, I'm sure you wouldn't have had to ask how to do that).
        1 person found this helpful
        • 16. Re: What kind of input parameter is used in SUM function?
          BS2012
          Hi Purvesh,
          I've understood and implementing all those. I just wrote "This was my question, so don't be confused." :)

          Regards,
          BS2012
          1 2 Previous Next