PLSQL - 利用Object Type实现Greatest/Least忽略空值计算

之前发布过利用自定义函数实现greatest和least忽略空值计算的文章，最近又想到可以用对象类型来实现，不算成熟，抛砖引玉。

本例以最多容纳五个数字入参为例：

CREATE OR REPLACE TYPE bhsc_numeric_battle AS OBJECT
(
  -- 输入属性
  num0 NUMBER,
  num1 NUMBER,
  num2 NUMBER,
  num3 NUMBER,
  num4 NUMBER,
  -- 比较结果，作为属性只是为了方便应用，并不允许输入
  greatest NUMBER,
  least    NUMBER,
  -- 用于比较入参的过程，不需要调用
  MEMBER PROCEDURE battle,
  -- 自定义构造函数，用于支持不定量入参
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0 NUMBER)
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0 NUMBER,
                                           num1 NUMBER)
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0 NUMBER,
                                           num1 NUMBER,
                                           num2 NUMBER)
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0 NUMBER,
                                           num1 NUMBER,
                                           num2 NUMBER,
                                           num3 NUMBER)
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0 NUMBER,
                                           num1 NUMBER,
                                           num2 NUMBER,
                                           num3 NUMBER,
                                           num4 NUMBER)
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0     NUMBER,
                                           num1     NUMBER,
                                           num2     NUMBER,
                                           num3     NUMBER,
                                           num4     NUMBER,
                                           greatest NUMBER)
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0     NUMBER,
                                           num1     NUMBER,
                                           num2     NUMBER,
                                           num3     NUMBER,
                                           num4     NUMBER,
                                           greatest NUMBER,
                                           least    NUMBER)
    RETURN SELF AS RESULT
)
/
CREATE OR REPLACE TYPE BODY bhsc_numeric_battle IS

  -- 该过程用于比较计算出两个最值，在实例化时内部调用，无需用户调用
  MEMBER PROCEDURE battle IS
  BEGIN
    greatest := coalesce(num0, num1, num2, num3, num4);
    least    := coalesce(num4, num3, num2, num1, num0);
  
    IF greatest IS NULL THEN
      RETURN;
    END IF;
  
    IF least > greatest THEN
      SELECT least, greatest INTO greatest, least FROM dual;
    END IF;
  
    IF num0 > greatest THEN
      greatest := num0;
    ELSIF num0 < least THEN
      least := num0;
    END IF;
    IF num1 > greatest THEN
      greatest := num1;
    ELSIF num1 < least THEN
      least := num1;
    END IF;
    IF num2 > greatest THEN
      greatest := num2;
    ELSIF num2 < least THEN
      least := num2;
    END IF;
    IF num3 > greatest THEN
      greatest := num3;
    ELSIF num3 < least THEN
      least := num3;
    END IF;
    IF num4 > greatest THEN
      greatest := num4;
    ELSIF num4 < least THEN
      least := num4;
    END IF;
  END;

  -- 这两个构造函数专门用于禁止用户实例化greatest和least
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0     NUMBER,
                                           num1     NUMBER,
                                           num2     NUMBER,
                                           num3     NUMBER,
                                           num4     NUMBER,
                                           greatest NUMBER)
    RETURN SELF AS RESULT IS
    too_many_arguments EXCEPTION;
    PRAGMA EXCEPTION_INIT(too_many_arguments, -909);
  BEGIN
    RAISE too_many_arguments;
  END bhsc_numeric_battle;
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0     NUMBER,
                                           num1     NUMBER,
                                           num2     NUMBER,
                                           num3     NUMBER,
                                           num4     NUMBER,
                                           greatest NUMBER,
                                           least    NUMBER)
    RETURN SELF AS RESULT IS
    too_many_arguments EXCEPTION;
    PRAGMA EXCEPTION_INIT(too_many_arguments, -909);
  BEGIN
    RAISE too_many_arguments;
  END bhsc_numeric_battle;
  -- 这些构造函数用于支持不定量入参，给未输入的属性赋予空值，并调用battle计算最值
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0 NUMBER) RETURN SELF AS RESULT IS
  BEGIN
    self.num0 := num0;
    self.num1 := NULL;
    self.num2 := NULL;
    self.num3 := NULL;
    self.num4 := NULL;
    battle;
    RETURN;
  END bhsc_numeric_battle;
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0 NUMBER,
                                           num1 NUMBER) RETURN SELF AS RESULT IS
  BEGIN
    self.num0 := num0;
    self.num1 := num1;
    self.num2 := NULL;
    self.num3 := NULL;
    self.num4 := NULL;
    battle;
    RETURN;
  END bhsc_numeric_battle;
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0 NUMBER,
                                           num1 NUMBER,
                                           num2 NUMBER) RETURN SELF AS RESULT IS
  BEGIN
    self.num0 := num0;
    self.num1 := num1;
    self.num2 := num2;
    self.num3 := NULL;
    self.num4 := NULL;
    battle;
    RETURN;
  END bhsc_numeric_battle;
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0 NUMBER,
                                           num1 NUMBER,
                                           num2 NUMBER,
                                           num3 NUMBER) RETURN SELF AS RESULT IS
  BEGIN
    self.num0 := num0;
    self.num1 := num1;
    self.num2 := num2;
    self.num3 := num3;
    self.num4 := NULL;
    battle;
    RETURN;
  END bhsc_numeric_battle;
  CONSTRUCTOR FUNCTION bhsc_numeric_battle(num0 NUMBER,
                                           num1 NUMBER,
                                           num2 NUMBER,
                                           num3 NUMBER,
                                           num4 NUMBER) RETURN SELF AS RESULT IS
  BEGIN
    self.num0 := num0;
    self.num1 := num1;
    self.num2 := num2;
    self.num3 := num3;
    self.num4 := num4;
    battle;
    RETURN;
  END bhsc_numeric_battle;

END;
/

在使用的时候，对象被实例化出来的同时，其greatest和least属性便已计算好，并可在游标中直接引用了。

例如有这样一张表：

SQL> SELECT * FROM bhsc_numeric_battle_demo;

    ROWSEQ       NUM0       NUM1       NUM2       NUM3       NUM4
---------- ---------- ---------- ---------- ---------- ----------
         1                -23.49                -26.91 
         2     -40.78     -33.19      63.89     -52.48      18.52
         3                 99.12                       
         4                                             
         5      56.85      71.81      41.41                 31.09

可在SQL中直接得到它五列中的最值：

SELECT rowseq, bhsc_numeric_battle(num0, num1, num2, num3, num4) x
  FROM bhsc_numeric_battle_demo;

或者也可以只比较后三列：

SELECT rowseq, bhsc_numeric_battle(num2, num3, num4) x
  FROM bhsc_numeric_battle_demo;

 但是不要输入超过五个值：

SQL> SELECT bhsc_numeric_battle(1, 2, 3, 4, 5, 6) x FROM dual;
SELECT bhsc_numeric_battle(1, 2, 3, 4, 5, 6) x FROM dual

ORA-00909: 参数个数无效
ORA-06512: 在 "APPS.BHSC_NUMERIC_BATTLE", line 55