Stworzyłem to rozwiązanie, zanim zetknąłem się z ListAgg, ale wciąż zdarzają się takie sytuacje, jak na przykład problem zduplikowanej wartości, wtedy to narzędzie jest przydatne. Poniższa wersja zawiera 4 argumenty, które dają ci kontrolę nad wynikami.
Objaśnienie CLOBlist przyjmuje jako parametr parametr CLOBlistParam contructor. CLOBlistParam ma 4 argumenty
string VARCHAR2(4000) - The variable to be aggregated
delimiter VARCHAR2(100) - The delimiting string
initiator VARCHAR2(100) - An initial string added before the first value only.
no_dup VARCHAR2(1) - A flag. Duplicates are suppressed if this is Y
Przykładowe użycie
--vertical list of comma separated values, no duplicates.
SELECT CLOBlist(CLOBlistParam(column_name,chr(10)||',','','Y')) FROM user_tab_columns
--simple csv
SELECT CLOBlist(CLOBlistParam(table_name,',','','N')) FROM user_tables
Link do Gist znajduje się poniżej.
https://gist.github.com/peter-genesys/d203bfb3d88d5a5664a86ea6ee34eeca] 1
-- Program : CLOBlist
-- Name : CLOB list
-- Author : Peter Burgess
-- Purpose : CLOB list aggregation function for SQL
-- RETURNS CLOB - to allow for more than 4000 chars to be returned by SQL
-- NEW type CLOBlistParam - allows for definition of the delimiter, and initiator of sequence
------------------------------------------------------------------
--This is an aggregating function for use in SQL.
--It takes the argument and creates a comma delimited list of each instance.
WHENEVER SQLERROR CONTINUE
DROP TYPE CLOBlistImpl;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
create or replace type CLOBlistParam as object(
string VARCHAR2(4000)
,delimiter VARCHAR2(100)
,initiator VARCHAR2(100)
,no_dup VARCHAR2(1) )
/
show error
--Creating CLOBlist()
--Implement the type CLOBlistImpl to contain the ODCIAggregate routines.
create or replace type CLOBlistImpl as object
(
g_list CLOB, -- progressive concatenation
static function ODCIAggregateInitialize(sctx IN OUT CLOBlistImpl)
return number,
member function ODCIAggregateIterate(self IN OUT CLOBlistImpl
, value IN CLOBlistParam) return number,
member function ODCIAggregateTerminate(self IN CLOBlistImpl
, returnValue OUT CLOB
, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT CLOBlistImpl
, ctx2 IN CLOBlistImpl) return number
)
/
show error
--Implement the type body for CLOBlistImpl.
create or replace type body CLOBlistImpl is
static function ODCIAggregateInitialize(sctx IN OUT CLOBlistImpl)
return number is
begin
sctx := CLOBlistImpl(TO_CHAR(NULL));
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT CLOBlistImpl
, value IN CLOBlistParam) return number is
begin
IF self.g_list IS NULL THEN
self.g_list := value.initiator||value.string;
ELSIF value.no_dup = 'Y' AND
value.delimiter||self.g_list||value.delimiter LIKE '%'||value.delimiter||value.string||value.delimiter||'%'
THEN
--Do not include duplicate value
NULL;
ELSE
self.g_list := self.g_list||value.delimiter||value.string;
END IF;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN CLOBlistImpl
, returnValue OUT CLOB
, flags IN number) return number is
begin
returnValue := self.g_list;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT CLOBlistImpl
, ctx2 IN CLOBlistImpl) return number is
begin
self.g_list := LTRIM( self.g_list||','||ctx2.g_list,',');
return ODCIConst.Success;
end;
end;
/
show error
--Using CLOBlist() to create a vertical list of comma separated values
-- SELECT CLOBlist(CLOBlistParam(product_code,chr(10)||',','','Y'))
-- FROM account
--DROP FUNCTION CLOBlist
--/
PROMPT Create the user-defined aggregate.
CREATE OR REPLACE FUNCTION CLOBlist (input CLOBlistParam) RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING CLOBlistImpl;
/
show error
order by nullbyć,order by Num2czy się mylę?