Oracle的greatest和least函数
Oracle的greatest和least函数
碰巧看到一篇文章提到了Oracle的greatest函数，相对应的有个least，以为这俩是个新的函数，但是看了下官方文档，其实在11g的《SQL Language Reference》，就提到了他们，是我孤陋寡闻了。
从函数的名称上，能猜到这两个函数分别求的是最大值和最小值。
greatest函数介绍，
GREATEST returns the greatest of a list of one or more expressions. Oracle Database uses the first expr to determine the return type. If the first expr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type. If the first expr is not numeric, then each expr after the first is implicitly converted to the data type of the first expr before the comparison. Oracle Database compares each expr using nonpadded comparison semantics. The comparison is binary by default and is linguistic if the NLS_COMP parameter is set to LINGUISTIC and the NLS_SORT parameter has a setting other than BINARY. Character comparison is based on the numerical codes of the characters in the database character set and is performed on whole strings treated as one sequence of bytes, rather than character by character. If the value returned by this function is character data, then its data type is VARCHAR2 if the first expr is a character data type and NVARCHAR2 if the first expr is a national character data type.
least函数介绍，
LEAST returns the least of a list of one or more expressions. Oracle Database uses the first expr to determine the return type. If the first expr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type. If the first expr is not numeric, then each expr after the first is implicitly converted to the data type of the first expr before the comparison. Oracle Database compares each expr using nonpadded comparison semantics. The comparison is binary by default and is linguistic if the NLS_COMP parameter is set to LINGUISTIC and the NLS_SORT parameter has a setting other than BINARY. Character comparison is based on the numerical codes of the characters in the database character set and is performed on whole strings treated as one sequence of bytes, rather than character by character. If the value returned by this function is character data, then its data type is VARCHAR2 if the first expr is a character data type and NVARCHAR2 if the first expr is a national character data type.
概括来讲，
1. greatest/least可以接收一个或多个字面值/字段列，返回其中的最大值/最小值。
2. greatest/least返回的数据类型，参照第一个参数的数据类型。
greatest作为例子，如果是字面值，直接返回最大值，
SQL> select greatest(1, 2, 3) from dual;
GREATEST(1,2,3)
---------------
3创建测试表，具备2个number类型，2个varchar2类型，
SQL> create table t_compare (id1 number, id2 number, name1 varchar2(1), name2 varchar2(1));
Table created.
SQL> insert into t_compare values(1, 2, 'a', 'c');
1 row created.
SQL> insert into t_compare values(2, 3, 'A', 'D');
1 row created.
SQL> commit;
Commit complete.当参数是两个number类型的，返回的是每行中数值最大的值，
SQL> select greatest(id1, id2) from t_compare;
GREATEST(ID1,ID2)
-----------------
2
3当参数是两个varchar2类型的，返回的是每行中字符最大的值，
SQL> select greatest(name1, name2) from t_compare;
G
-
c
Dnumber和varchar2类型混合比较时，如果number在前，返回的是number类型，此时name1和name2是字母，不能转换成数字，因此报错，
SQL> select least(id1, id2, name1, name2) from t_compare;
select least(id1, id2, name1, name2) from t_compare
*
ERROR at line 1:
ORA-01722: invalid number如果name1和name2是能转换成数字的值，就可以执行，
SQL> insert into t_compare values(1, 2, '3', '2', sysdate, sysdate);
1 row created.
SQL> select greatest(id1, id2, name1, name2) from t_compare where name1='3';
GREATEST(ID1,ID2,NAME1,NAME2)
-----------------------------
3如果varchar2在前，返回的是varchar2类型，此时id1和id2可以转换成varchar2，因此不会报错，
SQL> select greatest(name1, name2, id1, id2) from t_compare;
GREATEST(NAME1,NAME2,ID1,ID2)
----------------------------------------
c
D除了数值、字符串类型，日期类型也能进行比较，
SQL> alter table t_compare add (t1 date, t2 date);
Table altered.
SQL> select * from t_compare;
ID1 ID2 N N T1 T2
---------- ---------- - - ------------------ ------------------
1 2 a c 22-FEB-21 21-FEB-21
2 3 A D 22-FEB-21 21-FEB-21
SQL> select greatest(t1, t2) from t_compare;
GREATEST(T1,T2)
------------------
22-FEB-21
22-FEB-21如果date和number混合比较，提示两者不能互相转换，
SQL> select greatest(id1, id2, t1, t2) from t_compare;
select greatest(id1, id2, t1, t2) from t_compare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
SQL> select greatest(t1, t2, id1, id2) from t_compare;
select greatest(t1, t2, id1, id2) from t_compare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER可以想象，Oracle可能存在其他和greatest/least相近的函数，看着很小，但在某些场景下，还是能起到一定的作用。个人理解，未必都得记住，当需要的时候，我们能进行有效的搜索，找到他们，知道怎么用，就可以了。