--创建定义数值数组类型 
create or replace type t_number_array is varray(100) of number;

--定义分析函数类型
--包头
create or replace type t_med
as object
        (
                numbers t_number_array,
                --初始化
                
        static function odciaggregateinitialize
                (
                        this in out t_med)
                return number,
                --遍历 member
        function odciaggregateiterate
                (
                        self in out t_med,
                        value number)
                return number,
                --结束 member
        function odciaggregateterminate
                (
                        self in t_med,
                        returnvalue out number,
                        flags in        number)
                return number,
                --合并 member
        function odciaggregatemerge
                (
                        self in out t_med,
                        ctx2 in t_med)
         return number 
 );

--包体
CREATE OR REPLACE TYPE BODY t_med
IS
        --初始化
    STATIC FUNCTION odciaggregateinitialize
        (
          this IN OUT t_med)
        RETURN NUMBER
IS
BEGIN
        this := t_med(t_number_array());
        RETURN odciconst.success;
END;
--遍历 MEMBER
FUNCTION odciaggregateiterate
        (
                SELF IN OUT t_med,
                VALUE NUMBER)
        RETURN NUMBER
IS
        --将元素按倒序,插入到数组中 --新元素将要插入的位置
        i_loc1 INTEGER;
        --移位时的数组指针
        i_loc2 INTEGER;
BEGIN
        --空值不处理
        IF VALUE IS NULL THEN
                RETURN odciconst.success;
        END IF;
        --假定初始的位置是最后
        i_loc1 := self.numbers.count + 1;
        FOR i                       IN 1 .. self.numbers.count
        LOOP
                IF VALUE        > self.numbers(i) THEN
                        i_loc1 := i;
                        GOTO outer1;
                END IF;
        END LOOP;
        <<outer1>>
        --数组扩充一个元素
        self.numbers.extend;
        i_loc2 := self.numbers.count;
        --插入位置的元素后移
        WHILE i_loc2 > i_loc1
        LOOP
                self.numbers(i_loc2) := self.numbers(i_loc2 - 1);
                i_loc2               := i_loc2              - 1;
        END LOOP;
        --新元素填入
        self.numbers(i_loc1) := VALUE;
        RETURN odciconst.success;
END;
--结束MEMBER
FUNCTION odciaggregateterminate
        (
                SELF IN t_med,
                returnvalue OUT NUMBER,
                flags IN        NUMBER)
        RETURN NUMBER
IS
BEGIN
        IF self.numbers.count = 0 THEN
                returnvalue  := NULL;
        ELSE
                IF self.numbers.count MOD 2 = 0 THEN
                        --元素数量是偶数,返回中间两个元素的平均值
                        returnvalue := (self.numbers(self.numbers.count / 2 + 1) + self.numbers(self.numbers.count / 2)) / 2;
                ELSE
                        --元素数量是奇数,返回中间元素
                        returnvalue := self.numbers((self.numbers.count - 1) / 2 + 1);
                END IF;
        END IF;
        RETURN odciconst.success;
END;
--合并 MEMBER
FUNCTION odciaggregatemerge
        (
                SELF IN OUT t_med,
                ctx2 IN t_med)
        RETURN NUMBER
IS
BEGIN
        NULL;
        RETURN odciconst.success;
END;
END;

--定义分析函数
create or replace function f_med(p_value number) return number aggregate using t_med;

--创建测试表

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

COMMIT;

--测试一下
SELECT JOB,SAL,f_med(SAL) over(PARTITION BY JOB) FROM EMP;

参考博文：https://blog.csdn.net/iteye_11541/article/details/81973096