达梦数据库自定义分析函数

1.1、创建定义数值数组类型：

create or replace type t_number_array is varray(100) of number;

1.2、定义分析函数类型

--包头create or replace type t_medas 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_medIS        --初始化    STATIC FUNCTION odciaggregateinitialize        (          this IN OUT t_med)        RETURN NUMBERISBEGIN        this := t_med(t_number_array());        RETURN odciconst.success;END;​​​​​​​

--遍历 MEMBERFUNCTION odciaggregateiterate        (                SELF IN OUT t_med,                VALUE NUMBER)        RETURN NUMBERIS        --将元素按倒序,插入到数组中 --新元素将要插入的位置        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;​​​​​​​

--结束MEMBERFUNCTION odciaggregateterminate        (                SELF IN t_med,                returnvalue OUT NUMBER,                flags IN        NUMBER)        RETURN NUMBERISBEGIN        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;​​​​​​​

--合并 MEMBERFUNCTION odciaggregatemerge        (                SELF IN OUT t_med,                ctx2 IN t_med)        RETURN NUMBERISBEGIN        NULL;        RETURN odciconst.success;END;END;

1.3、定义分析函数

create or replace function f_med(p_value number) return number aggregate using t_med;

创建测试表：​​​​​​​

CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,                  ENAME VARCHAR(10),                  JOB VARCHAR(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;