聚合函数listagg（超出长度限制时xmlagg）
表&数据
CREATE TABLE peoplebuy (people Varchar2(10),buy Varchar2(10),price NUMBER);
INSERT INTO peoplebuy VALUES ('我','⼩猫',10);
INSERT INTO peoplebuy VALUES ('我','⼩狗',200);
INSERT INTO peoplebuy VALUES ('你','树',20);
tabletotal函数原来的结果
SELECT*FROM PEOPLEBUY ORDER BY PEOPLE;
想要的结果
SELECT PEOPLE,
LISTAGG(BUY, ',') WITHIN GROUP(ORDER BY BUY DESC) AS BUY,
SUM(PRICE) TOTAL
FROM PEOPLEBUY
GROUP BY PEOPLE
ORDER BY PEOPLE
LISTAGG(measure_expr [, 'delimiter'])
WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
当连接的字符串过长时会出现以下错误：
解决⽅案：
RTRIM(XMLAGG(XMLELEMENT(e, t.signature_id, ',').extract('//text()')).getClobVal(),',') very_long_text
聚合元素，提取⽂本，获取clob，去空格
Function Description
Constructor that constructs an instance of the XMLType datatype. The constructor can take in the XML as a CLOB, VARCHAR2 or take in a object type.
Static function for creating and returning an XMLType instance.
Takes a XMLType instance and a XPath and returns 1 or 0 indicating if applying the XPath returns a non-empty set of nodes.
Takes a XMLType instance and an XPath, applies the XPath expression and returns the results as an XMLType.
Checks if the input XMLType instance is a fragment or not. A fragment is a XML instance, which has more than one root
element.
Returns the value of the XMLtype instance as a CLOB
Returns the value of the XMLtype instance as a NUMBER. This is only valid if the input XMLtpye instance contains a simple text node and is convertible to a number.
Returns the value of the XMLType instance as a string.
Takes an XMLtype instance and an associated stylesheet (which is also an XMLtype instance) , applies the stylesheet and
returns the result as XML.
Converts the XMLType instance to an object type.
Returns 1 or 0 indicating if the input XMLType instance is a schema based one or not.
Returns the XML schema URL if the input is a XMLSchema based.
Returns the root element of the input instance. Returns NULL if the instance is a fragment
Creates a schema based XMLtype instance from the non-schema based instance using the input schema URL.
Creates a non schema based XML from the input schema based instance.
Returns the namespace for the top level element in a schema based document.
Validates the input instance according to the XMLSchema. Raises error if the input instance is non-schema based.
Checks if the instance has been validated against the schema.
Checks if the instance has been validated against the schema.
Function Description
Sets the schema valid flag to avoid costly schema validation.
Checks if the input instance is schema valid according to the given schema URL.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。