Listagg Distinct for Oracle database < 19c

همانطور که دوستان میدانند تا قبل از نسخه اوراکل دیتابیس ۱۹ امکان استفاده از Distinct  در Listagg وجود ندارد. سال گذشته نیاز پیدا کردم که در هنگام استفاده از Listagg دیتای من بصورت یکتا باشد، به خاطر نوع Query که داشتم نمی توانستم دیتا را با استفاده از Distinct بصورت یکتا ایجا کرده و سپس با استفاده از Listagg رشته مورد نظرم را ایجاد کنم. سپس Googling انجام دادم و بعد از جستجوی فراوان یک پست را پیدا کردم که بسیار جالب بود و از طریق ایجاد Type و یک فانکشن آنرا پیاده سازی کرده بود. متاسفانه آدرس پست را فراموش کردم و نمی توانم به آن اشاره کنم.

حال می خواهم که این روش را با دوستان به اشتراک بگذارم.

در ابتدا باید از طریق اسکریپت زیر یک TYPE و OBJECT را در اوراکل ایجاد کنید.

DROP TYPE LISTAGG_DISTINCT_PARAMS;
/
CREATE OR REPLACE TYPE LISTAGG_DISTINCT_PARAMS AS OBJECT (ELEMENTO VARCHAR2(2000), SEPARATORE VARCHAR2(10));
/

DROP TYPE T_LISTAGG_DISTINCT;
/
CREATE OR REPLACE TYPE T_LISTAGG_DISTINCT AS OBJECT (

LISTA_ELEMENTI T_LISTA_ELEMENTI,
SEPARATORE VARCHAR2(10),

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LISTAGG_DISTINCT)
RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT T_LISTAGG_DISTINCT,
VALUE IN LISTAGG_DISTINCT_PARAMS )
RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN T_LISTAGG_DISTINCT,
RETURN_VALUE OUT VARCHAR2,
FLAGS IN NUMBER )
RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT T_LISTAGG_DISTINCT,
CTX2 IN T_LISTAGG_DISTINCT )
RETURN NUMBER
);
/

DROP TYPE BODY T_LISTAGG_DISTINCT;
/
CREATE OR REPLACE TYPE BODY T_LISTAGG_DISTINCT IS

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LISTAGG_DISTINCT) RETURN NUMBER IS
BEGIN
SCTX := T_LISTAGG_DISTINCT(T_LISTA_ELEMENTI() , ',');
RETURN ODCICONST.SUCCESS;
END;

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LISTAGG_DISTINCT, VALUE IN LISTAGG_DISTINCT_PARAMS) RETURN NUMBER IS
BEGIN

IF VALUE.ELEMENTO IS NOT NULL THEN
SELF.LISTA_ELEMENTI.EXTEND;
SELF.LISTA_ELEMENTI(SELF.LISTA_ELEMENTI.LAST) := TO_CHAR(VALUE.ELEMENTO);
SELF.LISTA_ELEMENTI:= SELF.LISTA_ELEMENTI MULTISET UNION DISTINCT SELF.LISTA_ELEMENTI;
SELF.SEPARATORE := VALUE.SEPARATORE;
END IF;
RETURN ODCICONST.SUCCESS;
END;

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LISTAGG_DISTINCT, RETURN_VALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
STRINGA_OUTPUT CLOB:='';
LISTA_OUTPUT T_LISTA_ELEMENTI;
TERMINATORE VARCHAR2(3):='...';
LUNGHEZZA_MAX NUMBER:=4000;
BEGIN

IF SELF.LISTA_ELEMENTI.EXISTS(1) THEN -- se esiste almeno un elemento nella lista

-- initializes a new support list
LISTA_OUTPUT := T_LISTA_ELEMENTI();

-- transfer of only the elements in DISTINCT
LISTA_OUTPUT := SELF.LISTA_ELEMENTI MULTISET UNION DISTINCT SELF.LISTA_ELEMENTI;

-- ordering of the elements
SELECT CAST(MULTISET(SELECT * FROM TABLE(LISTA_OUTPUT) ORDER BY 1 ) AS T_LISTA_ELEMENTI ) INTO LISTA_OUTPUT FROM DUAL;

-- concatenation in a string
FOR I IN LISTA_OUTPUT.FIRST .. LISTA_OUTPUT.LAST - 1
LOOP
STRINGA_OUTPUT := STRINGA_OUTPUT || LISTA_OUTPUT(I) || SELF.SEPARATORE;
END LOOP;
STRINGA_OUTPUT := STRINGA_OUTPUT || LISTA_OUTPUT(LISTA_OUTPUT.LAST);

-- if the string exceeds the set maximum size, it truncates and ends with a terminator
IF LENGTH(STRINGA_OUTPUT) > LUNGHEZZA_MAX THEN
RETURN_VALUE := SUBSTR(STRINGA_OUTPUT, 0, LUNGHEZZA_MAX - LENGTH(TERMINATORE)) || TERMINATORE;
ELSE
RETURN_VALUE:=STRINGA_OUTPUT;
END IF;

ELSE --if no element exists, return NULL

RETURN_VALUE := NULL;

END IF;

RETURN ODCICONST.SUCCESS;
END;

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LISTAGG_DISTINCT, CTX2 IN T_LISTAGG_DISTINCT) RETURN NUMBER IS
BEGIN
RETURN ODCICONST.SUCCESS;
END;

END;
/

حال باید یک فانکشن به نام LISTAGG_DISTINCT در اوراکل همانند اسکریپت زیر ایجاد کنید.

DROP FUNCTION LISTAGG_DISTINCT;
/
CREATE OR REPLACE FUNCTION LISTAGG_DISTINCT (INPUT LISTAGG_DISTINCT_PARAMS) RETURN VARCHAR2
  PARALLEL_ENABLE AGGREGATE USING T_LISTAGG_DISTINCT;
/

در این مرحله نحوه استفاده از آن ذکر شده است.

select LISTAGG_DISTINCT (LISTAGG_DISTINCT_PARAMS (MY_Field,','))
from MY_TABLE

امیدوار هستم مورد استفاده دوستان قرار گیرد.