ארכיון עבור קטגוריה: PL/SQL

לפעמים עולה הצורך לכתוב פונקצייה סיכומית (כלומר שעובדת עם Group by). אורקל מאפשרים את האופציה על ידי שימוש ב-API מיוחד שהם מספקים.

להלן דוגמה לקטע קוד שיוצר פונקציה סיכומית כזו שיודעת להכפיל במקום לסכום ערכים בטבלה.
למה כתבתי כזו פונקציה, למה זה היה טוב? לדוגמה אם שמים בטבלה הסתברות שיקרה מאורע ורוצים לדעת מה ההסתברות שיקרו כל המאורעות, אז ההסתברות היא מכפלה של כל ההסתברויות ובמקרה שלנו עושים את זה בשליפה אחת (אבל בפועל עשיתי את זה בתור דוגמה למפתח שהתעניין).

-- Create type object for the aggrigation
create or replace type MultSum as object
(
-- Author  : ZELKAYAM
-- Created : 09/02/2009 18:45:45
-- Purpose :

    v_sum NUMBER, -- second highest value seen so far
    v_is_first number,

    static function ODCIAggregateInitialize(sctx IN OUT MultSum) return number,

    member function ODCIAggregateIterate(self  IN OUT MultSum,
                                         value IN number) return number,

    member function ODCIAggregateTerminate(self        IN MultSum,
                                           returnValue OUT number,
                                           flags       IN number) return number,

    member function ODCIAggregateMerge(self IN OUT MultSum,
                                       ctx2 IN MultSum) return number
)
/

-- Create type body for the aggrigation
create or replace type body zelkayam2.MultSum is

    static function ODCIAggregateInitialize(sctx IN OUT MultSum) return number is
    begin
        sctx := MultSum(null, 1);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(self  IN OUT MultSum,
                                         value IN number) return number is
    begin
        if self.v_is_first = 1
        then
            self.v_sum := 1;
        end if;
        self.v_sum := self.v_sum * value;

        self.v_is_first := 0;

        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self        IN MultSum,
                                           returnValue OUT number,
                                           flags       IN number) return number is
    begin
        returnValue := self.v_sum;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self IN OUT MultSum,
                                       ctx2 IN MultSum) return number is
    begin
        self.v_sum := self.v_sum * ctx2.v_sum;
        return ODCIConst.Success;
    end;
end;
/

-- Create actual function whith aggrigation property
CREATE or replace FUNCTION MultSum1 (input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING MultSum;

-- Create test table
create table chk_for_shim
(a number, b number);

-- Create test data
insert into chk_for_shim values (1, 0);
insert into chk_for_shim values (2, 0);
insert into chk_for_shim values (3, 0);
insert into chk_for_shim values (4, 1);
insert into chk_for_shim values (5, 1);
insert into chk_for_shim values (6, 1);

-- use the function
SQL> select b, multsum1(a) from chk_for_shim group by b;

         B MULTSUM1(A)
---------- -----------
         0           6
         1         120