פונקציית משתמש סיכומית
לפעמים עולה הצורך לכתוב פונקצייה סיכומית (כלומר שעובדת עם 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