兼容的Oracle函数

ADD_MONTHS

ADD_MONTHS函数在给定日期的基础上增加(如果第二个参数为负数则减少)指定的月数。在执行计算之前，月数参数的任何小数部分都会被截断。如果给定的日期包含时间部分，则将其结转到结果中。

下面是ADD_MONTHS函数的示例。

halo0root=# SELECT ADD_MONTHS('13-JUN-07',4) FROM DUAL;
  add_months
------------------------
 2013-10-07 00:00:00+08
(1 行记录)

BITAND

BITAND函数执行按位和操作，并根据输入参数的数据类型返回一个值。

语法：BITAND(<expr1>, <expr2>)

expr1和expr2的输入数据类型是一个NUMBER，将在其上执行按位和操作。BITAND使用逐位AND操作将expr1值的每一位与exp2的对应位进行比较，并根据输入参数的数据类型返回值。如果BITAND的任意一个参数为NULL，则结果为NULL值。

返回类型：

BITAND函数返回与输入参数的数据类型相同的值。

例如：

halo0root=# SELECT BITAND(10,11) FROM DUAL;
 bitand
--------
   10
(1 行记录)

BTRIM

BTRIM 函数通过删除前导空格和尾随空格或删除与可选的指定字符串匹配的字符来剪裁字符串。

语法：BTRIM(string [, matching_string ] )

返回类型：VARCHAR 字符串。

例如：

halo0root=# select 'xyzaxyzbxyzcxyz' as untrim,btrim('xyzaxyzbxyzcxyz', 'xyz') as trim;
   untrim   |  trim
-----------------+-----------
 xyzaxyzbxyzcxyz | axyzbxyzc
(1 行记录)

CONCAT

CONCAT函数将多个RAW值连接成单个RAW值。

这个函数返回一个RAW值。

例如：

halo0root=# select concat('aba','df') from dual;
 concat
--------
 abadf
(1 行记录)

CONCAT_WS

concat_ws()函数, 表示有分隔符的字符串连接。

例如：

halo0root=# select concat_ws(',','11','22','33') from dual;
 concat_ws
-----------
 11,22,33
(1 行记录)

COSH

Cosh函数返回输入值的反余弦值。

例如：

halo0root=# SELECT COSH(0) from dual;
 cosh
------
  1
(1 行记录)

DUMP

DUMP返回一个包含数据类型代码、字节长度xpr的内部表示的VARCHAR2值返回的结果总是在数据库字符集中。

语法：DUMP(expr,[return_fmt],[start_position],[length]])

参数return_fmt指定返回值的格式，可以有以下任何值：

8：以8进制返回结果的值

10：以10进制返回结果的值（默认）

16：以16进制返回结果的值

17：以单字符的形式返回结果的值

1000：以上4种加上1000，表示在返回值中加上当前字符集

start_position：开始进行返回的字符位置

length：需要返回的字符长度

例如：

halo0root=# SELECT dump('ddddd',10) from dual;
                 dump
--------------------------------------
 Typ=705 Len=6: 100,100,100,100,100,0
(1 行记录)

GET_FULL_VERSION_NUM

函数返回完整版本

例如：

halo0root=# select GET_FULL_VERSION_NUM();
 get_full_version_num
----------------------
 13.6
(1 行记录)

GET_MAJOR_VERSION

函数返回主版本号

例如：

halo0root=# select get_major_version();
 get_major_version
-------------------
 Halo 13.6
(1 行记录)

GET_MAJOR_VERSION_NUM

函数返回版本号

例如：

halo0root=# select GET_MAJOR_VERSION_NUM();
 get_major_version_num
-----------------------
 13
(1 行记录)

GET_PLATFORM

返回驱动信息

例如：

halo0root=# select GET_PLATFORM();
 get_platform
--------------
 64bit
(1 行记录)

GET_STATUS

返回状态

例如：

halo0root=# select GET_STATUS();
 get_status
------------
 Production
 (1 行记录)

INSTR

INSTR函数返回在一个大对象中出现的第n个给定模式的位置。

语法：instr( string1, string2 [, start_position [, nth_appearance ] ] )

string2 的值要在string1中查找，是从start_position给出的数值（即位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

例如：

在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置

halo0root=# select instr('helloworld','l',2,2) from dual;

 instr
-------
   4
(1 行记录)

LAST_DAY

函数返回给定日期所代表的月份的最后一天。

例如：

halo0root=# SELECT last_day(SYSDATE) FROM dual;
   last_day
---------------------
 2022-03-31 16:54:32
   (1 行记录)

LENGTH

LENGTH函数返回RAW值的长度

例如：

halo0root=# select length('hhhhh') from dual;
 length
--------
  5
(1 行记录)

LISTAGG

LISTAGG是一个聚合函数，它以有序的方式将多行数据连接到单行中。可以选择为数据包含自定义分隔符。

LISTAGG函数要求使用WITHIN GROUP子句下的ORDER BY子句来连接度量列的值，然后生成排序的聚合数据。Halo数据库暂不支持WITHIN GROUP 子句。

LNNVL

函数lnnvl用于某个语句的where子句中的条件，如果条件为true就返回false；如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND，OR，或者BETWEEN中

例如：

halo0root=# select * from emp;
 empno| ename |  job     | mgr  |   hiredate          | sal | comm  | deptno
-------+--------+----------+------+---------------------+------+------+--------
 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300  |   30
 7521 | WARD  | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500  |   30
 7566 | JONES | MANAGER  | 7839 | 1981-04-02 00:00:00 | 2975 |      |   20
 7654 | MARTIN| SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |   30
 7698 | BLAKE | MANAGER  | 7839 | 1981-05-01 00:00:00 | 2850 |      |   30
(5 行记录)

halo0root=# select * from emp where lnnvl(deptno>21);
 empno | ename |  job    | mgr  |   hiredate          | sal  | comm | deptno
-------+-------+---------+------+---------------------+------+------+--------
 7566  | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 |      |   20
(1 行记录)

LPAD

lpad函数从左边对字符串使用指定的字符进行填充。

语法：

lpad( string, padded_length, [ pad_string ] )

string：准备被填充的字符串；

padded_length：填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;

pad_string：填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。

例如：

halo0root=# select Lpad('tech', 2) from dual;
 lpad
------
 te
(1 行记录)

LTRIM

Ltrim函数移除字符串两侧的空白字符或其他预定义字符。

例如：

halo0root=# select Trim ('   string ') from dual;                         
btrim
--------
string
(1 行记录)

MEDIAN

MEDIAN函数从给定的值范围计算表达式的中间值；NULL值将被忽略。如果查询没有引用用户定义的表，MEDIAN函数将返回一个错误。

例如：

halo0root=# select * from emp;
 empno| ename |  job     | mgr  |   hiredate          | sal  | comm | deptno
-------+--------+----------+------+---------------------+------+------+--------
 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300  |   30
 7521 | WARD  | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500  |   30
 7566 | JONES | MANAGER  | 7839 | 1981-04-02 00:00:00 | 2975 |      |   20
 7654 | MARTIN| SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |   30
 7698 | BLAKE | MANAGER  | 7839 | 1981-05-01 00:00:00 | 2850 |      |   30
     (5 行记录)

halo0root=# SELECT MEDIAN (SAL) FROM emp;
 median
--------
  1600
(1 行记录)

MOD

Mod是一个数学函数，它返回除法的余数或模数。

halo0root=# select mod(10,3) from dual;
 mod
-----
  1
(1 行记录)

MONTHS_BETWEEN

MONTHS_BETWEEN函数返回两个日期之间的月数。结果是一个数值，如果第一个日期大于第二个日期则为正，如果第一个日期小于第二个日期则为负。如果两个日期参数的月份的日期相同，或者两个日期参数都落在各自月份的最后一天，则结果始终是整数个月。

例如：

halo0root=# SELECT MONTHS_BETWEEN('15-DEC-06','15-OCT-06') FROM DUAL;
 months_between
----------------
      2
(1 行记录)

NEXT_DAY

NEXT_DAY函数返回第一个严格大于给定日期的给定工作日。

例如：

halo0root=# select next_day(sysdate,'tuesday') from dual;
   next_day
--------------------
 2022-03-08 18:59:59
(1 行记录)

NVL

NVL函数返回第一个不为空的参数。NVL计算第一个表达式；如果该表达式的计算结果为NULL，则NVL返回第二个表达式。

例如：

halo0root=# select * from emp;

 empno | ename |  job  | mgr |   hiredate    | sal | comm | deptno
-------+--------+----------+------+---------------------+------+------+--------
 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 |   30
 7521 | WARD  | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 |   30
 7566 | JONES | MANAGER  | 7839 | 1981-04-02 00:00:00 | 2975 |     |   20
 7654 | MARTIN| SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400|   30
 7698 | BLAKE | MANAGER  | 7839 | 1981-05-01 00:00:00 | 2850 |     |   30
(5 行记录)

halo0root=# select nvl(comm,999),empno from emp;
  nvl   | empno
------------+-------
 1908633333 | 7499
 1908633253 | 7521
        999 | 7566
 1908633109 | 7654
        999 | 7698
      (5 行记录)

NVL2

NVL2是函数NVL的拓展。NVL2计算一个表达式，并根据第一个表达式的值返回第二个或第三个表达式。如果第一个表达式不是NULL, NVL2返回expr2中的值;如果第一个表达式是NULL, NVL2返回expr3中的值。

NVL2 (expr1，expr2，expr3)

同样基于上述emp表举例：

halo0root=# select nvl2(comm,'999','000'),empno from emp;
 nvl2 | empno
------+-------
 999 | 7499
 999 | 7521
   0 | 7566
 999 | 7654
   0 | 7698
(5 行记录)

REGEXP_COUNT

REGEXP_COUNT在字符串中搜索正则表达式，并返回正则表达式出现的次数。语法：

REGEXP_COUNT (source_char, pattern [, position [, match_param]])

Position：开始位置，默认为1。

match_param：参数c表示大小写敏感；i表示不敏感。

例如 从第一个字符位置开始，A出现的次数，不区分大小写：

halo0root=# select regexp_count ('c means Case insensitive matching','A','1','i') "count(A)" from dual;
 count(A)
----------
   3
(1 行记录)

REGEXP_INSTR

REGEXP_INSTR在字符串中搜索一个正则表达式模式字符串。函数返回匹配项在字符串中的位置。它返回一个整数，指示开始或结束匹配的子位置，这取决于return_option参数的值。如果没有找到匹配，则函数返回0。

例如：

halo0root=# SELECT REGEXP_INSTR ('hello itmyhome', 'e') from dual;
regexp_instr
--------------
     2
(1 行记录)

REGEXP_LIKE

函数返回查找指定字符串。

例如：

halo0root=# select * from fzq where regexp_like(value,'1....60');
 id | value
----+----------
 1 | 1234560
 2 | 1234560
 3 | 1b3b560
 11| 11114560
 11| 11124560 
 (5 行记录)

REGEXP_REPLACE

该函数用一个指定的 replace_string 来替换匹配的模式，从而允许复杂的"搜索并替换"操作。

例如：

halo0root=# SELECT REGEXP_REPLACE('Joe Smith','( ){2,}', ' ') from dual;
 regexp_replace
----------------
 Joe Smith
 (1 行记录)

REGEXP_SUBSTR

返回与函数调用中指定的模式匹配的字符串。

例如：

halo0root=# SELECT regexp_substr('the1111333444fox', '[1-9]f', 1, 1, 'c');
 regexp_substr
---------------
    4f
(1 行记录)

REPLACE

REPLACE 函数是用另外一个值来替代串中的某个值。 语法：REPLACE(srcstr, oldsub[, newsub ] )

例如：

halo0root=# select replace('123456789','4','88888888' ) from dual;
   replace
------------------
 1238888888856789
   (1 行记录)

ROUND

ROUND函数根据指定的模板模式返回一个四舍五入的日期。如果模板模式被省略，日期将四舍五入到最近的一天。

格式：ROUND（number[,decimals]）

其中：number 待做截取处理的数值，decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分，并四舍五入。如果为负数则表示从小数点开始左边的位数，相应整数数字用0填充，小数被去掉。需要注意的是，和trunc函数不同，对截取的数字要四舍五入。

例如：

halo0root=# select round(1234.5678,3) from dual;
 round
----------
 1234.568
 (1 行记录)

RPAD

函数通过附加字符填充字符串的大小，长度，填充(默认为空格)。如果字符串已经超过长度，那么它将被截断。

例如：

halo0root=# select rpad('tech on the netttt', 20, 'z') from dual;
  rpad
---------------------
 tech on the nettttzz
   (1 行记录)

RTRIM

函数从给定字符串的右侧移除集合中指定的所有字符。如果未指定，则默认使用空格。

例如：

halo0root=# select RTRIM('abcdefghi','ghi') from dual;
 rtrim
--------
 abcdef
(1 行记录)

SINH

函数返回

例如：

halo0root=# select SINH(0) from dual;
 sinh
------
  0
 (1 行记录)

SUBSTR

SUBSTR函数为字符串截取函数。

例如 job字段中从第一个字符开始截取，长度为3：

halo0root=# select job,substr(job,1,3) from emp;
  job  | substr
----------+--------
 SALESMAN | SAL
 SALESMAN | SAL
  MANAGER | MAN
 SALESMAN | SAL
  MANAGER | MAN
   (5 行记录)

SUBTRACT

函数减去时间改变原始的时刻，返回日期。

例如：

halo0root=# select SUBTRACT('2021-03-11',5) from dual;
   subtract
---------------------
 2021-03-06 00:00:00
   (1 行记录)

TANH

函数返回双曲正切值。

例如：

halo0root=# select tanh(20),tan(20) from dual;
 tanh |   tan
------+-------------------
  1 | 2.237160944224742
    (1 行记录)

TO_CHAR

函数的功能是将数值型或者日期型转化为字符型。

例如：

halo0root=# select hiredate,to_char(hiredate,'yyyymmdd') from emp;
         hiredate    | to_char
---------------------+----------
 1981-02-20 00:00:00 | 19810220
 1981-02-22 00:00:00 | 19810222
 1981-04-02 00:00:00 | 19810402
 1981-09-28 00:00:00 | 19810928
 1981-05-01 00:00:00 | 19810501
            (5 行记录)

TO_DATE

同样是转换函数。

例如：

halo0root=# select hiredate,to_date(to_char(hiredate,'yyyymmdd'),'yyyymmdd') from emp;
         hiredate    |    to_date
---------------------+---------------------
 1981-02-20 00:00:00 | 1981-02-20 00:00:00
 1981-02-22 00:00:00 | 1981-02-22 00:00:00
 1981-04-02 00:00:00 | 1981-04-02 00:00:00
 1981-09-28 00:00:00 | 1981-09-28 00:00:00
 1981-05-01 00:00:00 | 1981-05-01 00:00:00
               (5 行记录)

TO_HEX

函数返回十六进制。

例如：

halo0root=# select to_hex(10) from dual;
 to_hex
--------
   a
(1 行记录)

TO_MULTI_BYTE

将字符串中的半角转化为全角。

例如：

halo0root=# select to_multi_byte('1234') from dual;
 to_multi_byte
---------------
 １２３４
(1 行记录) 

TO_NUMBER

函数返回数值型。

例如：

halo0root=# select hiredate,to_number(to_char(hiredate,'yyyymmdd')) from emp;
         hiredate    | to_number
---------------------+-----------
 1981-02-20 00:00:00 | 19810220
 1981-02-22 00:00:00 | 19810222
 1981-04-02 00:00:00 | 19810402
 1981-09-28 00:00:00 | 19810928
 1981-05-01 00:00:00 | 19810501
             (5 行记录)

TO_SINGLE_BYTE

将字符串中的全角转化为半角。

例如：

halo0root=# select to_single_byte('aaaaa') from dual;
 to_single_byte
----------------
     aaaaa
   (1 行记录)

TRUNC

TRUNC函数返回一个根据指定模板模式截断的日期。如果模板模式被省略，日期将被截短到最近的一天。

例如：

halo0root=# SELECT TO_CHAR(TRUNC(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;
  Century
-------------
 01-JAN-1901
  (1 行记录)

WM_CONCAT

函数把聚合数据拼接成一个字符串，一般搭配group by 使用。

例如：

halo0root=# select * from test;
empno | ename | job
-------+-------+--------
      |       |
   1  | wy    | shishi
   1  | zlq   | p
   2  | ww    | c
   (4 行记录)

halo0root=# select empno,to_char(wm_concat(ename)) as ename,to_char(wm_concat(job)) as job from test group by empno;
 empno | ename |  job
-------+--------+----------
       |       |
   1   | wy,zlq| shishi,p
   2   | ww    | c
   (3 行记录)