【干货攻略】达梦数据库自定义分析函数

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 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;