Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 39
Nov 152011
 

Asi před rokem jsem se zájmem zjistil, že je možné naprogramovat si vlastní agragační funkci pro SQL, tenkrát mi zaujalo, že je to možné a odnesl jsem si i něco málo o tom, jak by bylo možné to implimentovat. K myšlence vlastní agragační funkce jsem se dostal znovu až tento týden – často grupuji objednávky ze Sieblu dle procesu a mimo jejich počtu mi v daném procesu zajímá jedna, dvě (či více), které jsou pro danou grupu typické (a ideálně ve formátu, kde to mohu čapnout do clipboardu). Takže můj cíl je následující :

CREATE TABLE T_TEST_AGR AS SELECT ROWNUM PK,MOD(ROWNUM,3) po3,MOD(ROWNUM,5) po5 FROM DUAL CONNECT BY LEVEL<101;

Moje představa je zavolání následujícího selectu:
SELECT po3,GET_REP(po5) FROM T_TEST_AGR GROUP BY po3;

po3 get_rep(po5)
0 0,1,2,3,4
1 0,1,2,3,4
2 0,1,2,3,4

 

K vytvoření vlastní agragační funkce jsou potřeba následující kroky:
1) Vytvoření typu
2) Implementace funkcí typu
3) Vytvoření wrap funkce
4) Testování..

Takže hurá na to:

1) Nejrpve je třeba vytvořit objekt, který obsahuje funkce (viz dokumentace Oracle® Database Data Cartridge Developers Guide), které je nutné implemtnovat.

CREATE TYPE GetRepresentants AS OBJECT(
-- proměnná pro počet reprezentantů
s_reprezentants VARCHAR2(4000),
-- počet reprezentantů
n_num_of_rep NUMBER,
-- aktuální počet reprezentantů
n_actual_length NUMBER,
-- funkce, kterou se agregační funkce inicializuje
STATIC FUNCTION ODCIAggregateInitialize(actx IN OUT GetRepresentants) RETURN NUMBER,
-- interační funkce
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT GetRepresentants, val IN VARCHAR2) RETURN NUMBER,
-- funkce pro spojení dvou podvýsledků
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT GetRepresentants,ctx2 IN GetRepresentants) RETURN NUMBER,
-- ukončení funkce
MEMBER FUNCTION ODCIAggregateTerminate(self IN GetRepresentants, returnValue OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER
);

2) Po té je třeba dané funkce implementovat

CREATE OR REPLACE
TYPE BODY GetRepresentants IS
STATIC FUNCTION ODCIAggregateInitialize(actx IN OUT GetRepresentants ) RETURN NUMBER
IS BEGIN
actx:=GetRepresentants(null,0,0);
-- incializační hodnota
actx.s_reprezentants :='(';
actx.n_num_of_rep:=5;
actx.n_actual_length:=0;
-- povrzení, že funkce pracovala jak měla
return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT GetRepresentants, val IN VARCHAR2) RETURN NUMBER IS
BEGIN
IF self.n_actual_length --
self.s_reprezentants:=s_reprezentants
|| CASE WHEN self.n_actual_length=0 THEN NULL ELSE ','END
||val;
self.n_actual_length:=self.n_actual_length+1;
ELSE
-- mám již dostateční počet reprezentantů
-- zde by chtělo implementovat jejich náhodné prohození
NULL;
END IF;
-- povrzení, že funkce pracovala jak měla
return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge(self IN OUT GetRepresentants,ctx2 IN GetRepresentants) RETURN NUMBER IS
BEGIN
-- pokud je délka obou reprezentantů menší než požadovaná - mohu je zmergovat
IF ctx2.n_actual_length+self.n_actual_lengthself.n_actual_length THEN
self.s_reprezentants:=ctx2.s_reprezentants;
self.n_actual_length:=ctx2.n_actual_length;
END IF;

-- zde by chtělo doimplementovat jejich merge.
NULL;
END IF;
return ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate(self IN GetRepresentants, returnValue out VARCHAR2, FLAGS IN number) RETURN NUMBER
IS
BEGIN
-- návrat hodnoty při ukončení -> na zkoušku ukončuji endem
returnValue := self.s_reprezentants||')';
return ODCIConst.Success;
END;
END;

3) Po té je třeba založit funkci, která obalí všechny volání

CREATE FUNCTION get_rep (input VARCHAR2) RETURN VARCHAR2 AGGREGATE USING GetRepresentants;

4) A nyní sladká odměna v podobě vlastní (uf, fungující) agregační funkce:

SELECT po3,GET_REP(po5,3) FROM T_TEST_AGR GROUP BY po3;

po3 get_rep(po5)
0 (0,1,2,3,4)
1 (0,1,2,3,4)
2 (0,1,2,3,4)

 

Výsledek, dle očekávání trhu ;) Bohužel tím implementace dané funkce nekončí, ještě je na ní třeba dodělat správně merge funkci, prověřit, zda je možné jí spouštět paralelně, přidat parametr počtu reprezentantů – a především jí přetížit a třeba i přidat možnost, aby se pro varchar2 hodnoty vracela množina v apostrofech – aby bylo možné opravdu daný výsledek čapnout do clipboardu.

Mimochodem mluvil jsem pouze o agergační funkci – ale nad takto implementovanou agregační funkcí lze použít i analytickou klauzuli ;)

 Posted by at 22:41
Feb 262011
 

Pokud potřebujete zjistit aktuální schéma svoje či uživatele, který pouští váš PL/SQL kód,  je možné použít

select sys_context( 'userenv', 'current_schema' ) from dual;

Zjištění aktuálního schématu se velice hodí pokud použiváte  ve svém programu authid user v některé z funkcí.

 Posted by at 20:52