oracle 自定义聚合函数

1. 创建对象类型

CREATE OR REPLACE TYPE STRING_AGGREGATOR AS OBJECT
(
    G_STRING VARCHAR2(32767), -- 用于存储累积的结果
    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT STRING_AGGREGATOR) RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STRING_AGGREGATOR, VALUE IN VARCHAR2) RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STRING_AGGREGATOR, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STRING_AGGREGATOR, CTX2 IN STRING_AGGREGATOR) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY STRING_AGGREGATOR IS
    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT STRING_AGGREGATOR) RETURN NUMBER IS
    BEGIN
        SCTX := STRING_AGGREGATOR(NULL); -- 初始化为空字符串
        RETURN ODCICONST.SUCCESS;
    END;

    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STRING_AGGREGATOR, VALUE IN VARCHAR2) RETURN NUMBER IS
    BEGIN
        IF SELF.G_STRING IS NULL THEN
            SELF.G_STRING := VALUE;
        ELSE
            SELF.G_STRING := SELF.G_STRING || ',' || VALUE; -- 使用逗号分隔符连接
        END IF;
        RETURN ODCICONST.SUCCESS;
    END;

    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STRING_AGGREGATOR, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
    BEGIN
        RETURNVALUE := SELF.G_STRING;
        RETURN ODCICONST.SUCCESS;
    END;

    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STRING_AGGREGATOR, CTX2 IN STRING_AGGREGATOR) RETURN NUMBER IS
    BEGIN
        IF CTX2.G_STRING IS NOT NULL THEN
            IF SELF.G_STRING IS NULL THEN
                SELF.G_STRING := CTX2.G_STRING;
            ELSE
                SELF.G_STRING := SELF.G_STRING || ',' || CTX2.G_STRING;
            END IF;
        END IF;
        RETURN ODCICONST.SUCCESS;
    END;
END;
/

2. 创建聚合函数

CREATE OR REPLACE FUNCTION WM_CONCAT(P_INPUT VARCHAR2)
    RETURN VARCHAR2
    PARALLEL_ENABLE AGGREGATE USING STRING_AGGREGATOR;
/

3. 使用自定义函数

SELECT WM_CONCAT(oi.owner_name), COUNT(oi.id)
FROM owner_info oi
GROUP BY oi.house_id;

4. 上述操作使用指定的用户创建，其他用户没有权限无法访问到此函数。如果想让所有用户使用自定义函数，需要使用dba权限用户（比如SYS用户以数据库管理员DBA身份连接数据库）完成上述1、2步骤创建函数,然后完成以下授权和同义词创建。
   • 授予权限给所有用户

   GRANT EXECUTE ON WM_CONCAT TO PUBLIC;
   

   • DBA用户创建同义词

   CREATE OR REPLACE PUBLIC SYNONYM WM_CONCAT FOR WM_CONCAT;
   

   • 使用维修资金用户（HFMP以普通用户身份）登陆数据库，使用步骤4的sql，检验是否成功。
5. 同义词有关操作

-- 作为DBA用户或具有足够权限的用户
CREATE OR REPLACE PUBLIC SYNONYM MY_CONCAT FOR MY_CONCAT;

-- 查询所有用户的私有同义词
SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME = 'MY_CONCAT';
-- 删除同义词
DROP PUBLIC SYNONYM MY_CONCAT;