Oracle兼容扩展（orafce）

Oracle兼容（orafce）¶

orafce 扩展主要是为了在 SeaboxSQL 中兼容 Oracle 的部分语法、数据类型、函数、字典表等，有了 orafce 可以对使用 Oracle 数据的应用程序更加方便地移植到 SeaboxSQL 数据库上，尽可能地减少应用程序的代码改动量，从而简化了许多迁移工作量。

函数¶

介绍 orafce 的主要函数,包括以下类型的具体函数。

函数类型	函数名称
数学函数	BITAND
数学函数	SINH
数学函数	COSH
数学函数	TANH
字符串函数	BTRIM
字符串函数	INSTR
字符串函数	LENGTH
字符串函数	LENGTHB
字符串函数	LPAD
字符串函数	RPAD
字符串函数	LTRIM
字符串函数	RTRIM
字符串函数	NLSSORT
字符串函数	SUBSTR
字符串函数	SUBSTRB
Date/time 函数	ADD_MONTHS
Date/time 函数	DBTIMEZONE
Date/time 函数	SESSIONTIMEZONE
Date/time 函数	LAST_DAY
Date/time 函数	MONTHS_BETWEEN
Date/time 函数	NEXT_DAY
Date/time 函数	ROUND and TRUNC
数据类型格式化函数	TO_CHAR
数据类型格式化函数	TO_DATE
数据类型格式化函数	TO_NUMBER
数据类型格式化函数	TO_TIMESTAMP
数据类型格式化函数	TO_TIMESTAMP_TZ
数据类型格式化函数	TO_MULTI_BYTE
数据类型格式化函数	TO_SINGLE_BYTE
条件表达式函数	DECODE
条件表达式函数	LNNVL
条件表达式函数	NANVL
聚合函数	LISTAGG
聚合函数	MEDIAN
返回内部信息的函数	DUMP
返回内部信息的函数	SQL 运算符

BITAND¶

BITAND，返回两个数值型数值在按位进行 AND 运算后的结果。orafce 对这个函数做了改进。

-- 未安装 orafce
seaboxsql=# \df BITAND
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+------
 pg_catalog | bitand | bit              | bit, bit            | func
(1 row)

-- 安装 orafce
seaboxsql=# \df BITAND
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+------
 pg_catalog | bitand | bit              | bit, bit            | func
 public     | bitand | bigint           | bigint, bigint      | func
(2 rows)

seaboxsql=# SELECT BITAND(5,3) FROM DUAL;
 bitand
--------
      1
(1 row)

双曲函数¶

支持以下三个双曲函数(Hyperbolic Functions)，双曲正弦(SINH)，双曲余弦(COSH)，双曲正切(TANH)。

seaboxsql=# SELECT oracle.SINH(1.414) FROM DUAL;
        sinh
--------------------
 1.9346016882495571
(1 row)

seaboxsql=# SELECT oracle.COSH(2.236) FROM DUAL;
       cosh
-------------------
 4.731359100024696
(1 row)

seaboxsql=# SELECT oracle.TANH(3) FROM DUAL;
        tanh
--------------------
 0.9950547536867306
(1 row)

BTRIM¶

BTRIM，从字符串的开头和结尾删除指定的字符。Oracle 数据库不存在 BTRIM。

orafce 对这个函数做了改进，orafce 对 BTRIM 函数的更改对比：

-- 未安装 orafce
seaboxsql=# \df BTRIM
                         List of functions
   Schema   | Name  | Result data type | Argument data types | Type
------------+-------+------------------+---------------------+------
 pg_catalog | btrim | bytea            | bytea, bytea        | func
 pg_catalog | btrim | text             | text                | func
 pg_catalog | btrim | text             | text, text          | func
(3 rows)

-- 安装 orafce
seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df BTRIM
                          List of functions
   Schema   | Name  | Result data type | Argument data types  | Type 
------------+-------+------------------+----------------------+------
 oracle     | btrim | text             | character            | func
 oracle     | btrim | text             | character, character | func
 oracle     | btrim | text             | character, nvarchar2 | func
 oracle     | btrim | text             | character, text      | func
 oracle     | btrim | text             | character, varchar2  | func
 oracle     | btrim | text             | nvarchar2            | func
 oracle     | btrim | text             | nvarchar2, character | func
 oracle     | btrim | text             | nvarchar2, nvarchar2 | func
 oracle     | btrim | text             | nvarchar2, text      | func
 oracle     | btrim | text             | nvarchar2, varchar2  | func
 oracle     | btrim | text             | text                 | func
 oracle     | btrim | text             | text, character      | func
 oracle     | btrim | text             | text, nvarchar2      | func
 oracle     | btrim | text             | text, text           | func
 oracle     | btrim | text             | text, varchar2       | func
 oracle     | btrim | text             | varchar2             | func
 oracle     | btrim | text             | varchar2, character  | func
 oracle     | btrim | text             | varchar2, nvarchar2  | func
 oracle     | btrim | text             | varchar2, text       | func
 oracle     | btrim | text             | varchar2, varchar2   | func
 pg_catalog | btrim | bytea            | bytea, bytea         | func
(21 rows)

seaboxsql=# SET search_path TO default;
SET

使用 SeaboxSQL 自带的 BTRIM 函数处理的字符串如果是 CHAR 类型，则会删除行尾空格，然后删除修剪字符。

在以下示例中，将返回从 "aabcaba" 两端删除 "a" 的字符串。

-- 使用 SeaboxSQL 自带的 BTRIM 函数，先删除行尾空格，然后删除修剪字符
seaboxsql=# create table tt (id int,name char(10));
seaboxsql=# insert into tt values (3,'aabcaba');
INSERT 0 1
seaboxsql=# SELECT name, BTRIM(name,'a') from tt where id=3;
    name    | btrim
------------+-------
 aabcaba    | bcab
(1 row)

-- 使用 orafce 的 LENGTH 函数，不会删除行尾空格
seaboxsql=# SELECT name, oracle.BTRIM(name,'a') from tt where id=3;
    name    |  btrim
------------+----------
 aabcaba    | bcaba
(1 row)

INSTR¶

INSTR，返回字符串中子字符串的位置。

seaboxsql=# \df INSTR
                                       List of functions
   Schema   | Name  | Result data type |               Argument data types               | Type
------------+-------+------------------+-------------------------------------------------+------
 pg_catalog | instr | integer          | str text, patt text                             | func
 pg_catalog | instr | integer          | str text, patt text, start integer              | func
 pg_catalog | instr | integer          | str text, patt text, start integer, nth integer | func
(3 rows)

在以下示例中，在字符串 “ABCACBCAAC” 中找到字符 “BC” ，并返回这些字符的位置。

seaboxsql=# SELECT INSTR('ABCACBCAAC','BC') FROM DUAL;
 instr
-------
     2
(1 row)

seaboxsql=# SELECT INSTR('ABCACBCAAC','BC',-1,2) FROM DUAL;
 instr
-------
     2
(1 row)

LENGTH¶

LENGTH，以字符个数返回字符串的长度。

但是使用 SeaboxSQL 自带的 LENGTH 函数处理的字符串是 CHAR 类型，则长度中不包含行尾空格。

-- 未安装 orafce
seaboxsql=# \df LENGTH
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+------
 pg_catalog | length | integer          | bit                 | func
 pg_catalog | length | integer          | bytea               | func
 pg_catalog | length | integer          | bytea, name         | func
 pg_catalog | length | integer          | character           | func
 pg_catalog | length | double precision | lseg                | func
 pg_catalog | length | double precision | path                | func
 pg_catalog | length | integer          | text                | func
 pg_catalog | length | integer          | tsvector            | func
(8 rows)

-- 安装 orafce
seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df LENGTH
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+------
 oracle     | length | integer          | character           | func
 pg_catalog | length | integer          | bit                 | func
 pg_catalog | length | integer          | bytea               | func
 pg_catalog | length | integer          | bytea, name         | func
 pg_catalog | length | double precision | lseg                | func
 pg_catalog | length | double precision | path                | func
 pg_catalog | length | integer          | text                | func
 pg_catalog | length | integer          | tsvector            | func
(8 rows)

seaboxsql=# SET search_path TO default;
SET

在以下示例中，将返回表 tt 中 name 列（使用 CHAR(10) 定义）中的字符数。

-- 使用 SeaboxSQL 自带的 LENGTH 函数，char(10) 返回 4 ，不包含行尾空格
seaboxsql=# create table tt (id int,name char(10));
CREATE TABLE
seaboxsql=# insert into tt values (1,'AAAA');
INSERT 0 1
seaboxsql=# select name, LENGTH(name) from tt where id=1;
    name    | length
------------+--------
 AAAA       |      4
(1 row)

-- 在 Oracle 中的 LENGTH 函数，char(10) 返回 10 ，包含行尾空格
SQL> create table tt (id int,name char(10));
SQL> insert into tt values (1,'AAAA');
SQL> select name, LENGTH(name) from tt where id=1;

NAME       LENGTH(NAME)
---------- ------------
AAAA                 10

-- 使用 orafce 的 LENGTH 函数，char(10) 返回 10 ，包含行尾空格，与 Oracle 相符合
seaboxsql=# create table tt (id int,name char(10));
CREATE TABLE
seaboxsql=# insert into tt values (1,'AAAA');
INSERT 0 1
seaboxsql=# select name, oracle.LENGTH(name) from tt where id=1;
    name    | length
------------+--------
 AAAA       |     10
(1 row)

LENGTHB¶

LENGTHB，以字节数返回字符串的长度。

LENGTHB 函数处理的字符串是 CHAR 类型，则长度中会包含行尾空格。

seaboxsql=# \df LENGTHB
                          List of functions
   Schema   |  Name   | Result data type | Argument data types | Type
------------+---------+------------------+---------------------+------
 pg_catalog | lengthb | integer          | varchar2            | func
(1 row)

在以下示例中，将返回表 tt 中列 name（使用 CHAR(10) 定义）中的字节数。注意，在第二个 SELECT 语句中，每个汉字的长度为 3 个字节，两个汉字总共 6 个字节，其中 8 个行尾空格增加了 8 个字节，这给出了 14 个字节的结果。

drop table tt;
create table tt (id int,name char(10));
insert into tt values (1,'AAAAA');
insert into tt values (3,'中国');

seaboxsql=# SELECT name, LENGTHB(name) FROM tt WHERE id = 1;
    name    | lengthb
------------+---------
 AAAAA      |      10
(1 row)

seaboxsql=# SELECT name, LENGTHB(name) FROM tt WHERE id = 3;
     name     | lengthb
--------------+---------
 中国         |      14
(1 row)

--但是 Oracle 统计中文还是 10 个字节
SQL> SELECT name, LENGTHB(name) FROM tt WHERE id = 3;

NAME       LENGTHB(NAME)
---------- -------------
中国                  10

SQL> SELECT LENGTHB('中国') from dual;

LENGTHB('??????')
-----------------
                6
对于中文的字节数统计有点迷糊，如果应用程序代码中存在 LENGTHB ，需要额外关注一下。

LPAD¶

LPAD，在字符串的左边填充指定长度的字符串

但是使用 SeaboxSQL 自带的 LPAD 函数处理的字符串如果是 CHAR 类型，则删除行尾空格，然后将填充字符添加到字符串中。

-- 未安装 orafce
seaboxsql=# \df LPAD
                         List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
 pg_catalog | lpad | text             | text, integer       | func
 pg_catalog | lpad | text             | text, integer, text | func
(2 rows)

-- 安装 orafce
seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df LPAD
                            List of functions
 Schema | Name | Result data type |      Argument data types      | Type
--------+------+------------------+-------------------------------+------
 oracle | lpad | text             | bigint, integer, integer      | func
 oracle | lpad | text             | character, integer            | func
 oracle | lpad | text             | character, integer, character | func
 oracle | lpad | text             | character, integer, nvarchar2 | func
 oracle | lpad | text             | character, integer, text      | func
 oracle | lpad | text             | character, integer, varchar2  | func
 oracle | lpad | text             | integer, integer, integer     | func
 oracle | lpad | text             | numeric, integer, integer     | func
 oracle | lpad | text             | nvarchar2, integer            | func
 oracle | lpad | text             | nvarchar2, integer, character | func
 oracle | lpad | text             | nvarchar2, integer, nvarchar2 | func
 oracle | lpad | text             | nvarchar2, integer, text      | func
 oracle | lpad | text             | nvarchar2, integer, varchar2  | func
 oracle | lpad | text             | smallint, integer, integer    | func
 oracle | lpad | text             | text, integer                 | func
 oracle | lpad | text             | text, integer, character      | func
 oracle | lpad | text             | text, integer, nvarchar2      | func
 oracle | lpad | text             | text, integer, text           | func
 oracle | lpad | text             | text, integer, varchar2       | func
 oracle | lpad | text             | varchar2, integer             | func
 oracle | lpad | text             | varchar2, integer, character  | func
 oracle | lpad | text             | varchar2, integer, nvarchar2  | func
 oracle | lpad | text             | varchar2, integer, text       | func
 oracle | lpad | text             | varchar2, integer, varchar2   | func
(24 rows)

seaboxsql=# SET search_path TO default;
SET

在下面的示例中，返回一个 20 个字符的字符串，该字符串是通过在 ‘abc’ 的左边填充 ‘a’ 而形成的。

drop table tt;
create table tt (id int,name char(10));
insert into tt values (1,'abc');

-- 使用 SeaboxSQL 自带的 LPAD 函数，会先删除行尾空格，再填充字符
seaboxsql=# SELECT name, LPAD(name,20,'a') FROM tt;
    name    |         lpad
------------+----------------------
 abc        | aaaaaaaaaaaaaaaaaabc
(1 row)

-- 在 Oracle 中的 LPAD 函数，不会删除行尾空格
SQL> SELECT name, LPAD(name,20,'a') FROM tt;

NAME       LPAD(NAME,20,'A')
---------- ----------------------------------------
abc        aaaaaaaaaaabc

-- 使用 orafce 的 LPAD 函数，也不会删除行尾空格，与 Oracle 相符合
seaboxsql=# SELECT name, oracle.LPAD(name,20,'a') FROM tt;
    name    |         lpad
------------+----------------------
 abc        | aaaaaaaaaaabc
(1 row)

RPAD¶

RPAD，在字符串的右边填充指定长度的字符串，与 LPAD 类似

LTRIM¶

LTRIM，从字符串的开头删除指定的字符。

但是使用 SeaboxSQL 自带的 LTRIM 函数处理的字符串如果是 CHAR 类型，则先删除行尾空格，然后删除修剪字符。

-- 未安装 orafce
seaboxsql=# \df LTRIM
                         List of functions
   Schema   | Name  | Result data type | Argument data types | Type
------------+-------+------------------+---------------------+------
 pg_catalog | ltrim | text             | text                | func
 pg_catalog | ltrim | text             | text, text          | func
(2 rows)

-- 安装 orafce
seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df LTRIM
                        List of functions
 Schema | Name  | Result data type | Argument data types  | Type
--------+-------+------------------+----------------------+------
 oracle | ltrim | text             | character            | func
 oracle | ltrim | text             | character, character | func
 oracle | ltrim | text             | character, nvarchar2 | func
 oracle | ltrim | text             | character, text      | func
 oracle | ltrim | text             | character, varchar2  | func
 oracle | ltrim | text             | nvarchar2            | func
 oracle | ltrim | text             | nvarchar2, character | func
 oracle | ltrim | text             | nvarchar2, nvarchar2 | func
 oracle | ltrim | text             | nvarchar2, text      | func
 oracle | ltrim | text             | nvarchar2, varchar2  | func
 oracle | ltrim | text             | text                 | func
 oracle | ltrim | text             | text, character      | func
 oracle | ltrim | text             | text, nvarchar2      | func
 oracle | ltrim | text             | text, text           | func
 oracle | ltrim | text             | text, varchar2       | func
 oracle | ltrim | text             | varchar2             | func
 oracle | ltrim | text             | varchar2, character  | func
 oracle | ltrim | text             | varchar2, nvarchar2  | func
 oracle | ltrim | text             | varchar2, text       | func
 oracle | ltrim | text             | varchar2, varchar2   | func
(20 rows)

seaboxsql=# SET search_path TO default;
SET

在下面的示例中，将返回从 “aabcab” 开头删除 “ab” 的字符串。

drop table tt;
create table tt (id int,name char(10));
insert into tt values (1,'aabcab');

-- 使用 SeaboxSQL 自带的 LTRIM 函数，会先删除行尾空格，再删除修剪字符
seaboxsql=# SELECT name, LTRIM(name,'ab'), LENGTH(LTRIM(name,'ab')) FROM tt;
    name    | ltrim | length
------------+-------+--------
 aabcab     | cab   |      3
(1 row)

-- 在 Oracle 中的 LTRIM 函数，不会删除行尾空格
SQL> SELECT name, LTRIM(name,'ab'), LENGTH(LTRIM(name,'ab')) FROM tt;

NAME       LTRIM(NAME LENGTH(LTRIM(NAME,'AB'))
---------- ---------- ------------------------
aabcab     cab                               7

-- 使用 orafce 的 LTRIM 函数，也不会删除行尾空格，与 Oracle 相符合
seaboxsql=# SELECT name, oracle.LTRIM(name,'ab'), LENGTH(oracle.LTRIM(name,'ab')) FROM tt;
    name    |  ltrim  | length
------------+---------+--------
 aabcab     | cab     |      7
(1 row)

RTRIM¶

RTRIM，从字符串的末尾删除指定的字符，与 LTRIM 类似

NLSSORT¶

NLSSORT，用于在与默认语言环境不同的语言环境 (COLLATE) 的整理顺序中进行比较和排序。

seaboxsql=# \df NLSSORT
                          List of functions
   Schema   |  Name   | Result data type | Argument data types | Type
------------+---------+------------------+---------------------+------
 pg_catalog | nlssort | bytea            | text                | func
 pg_catalog | nlssort | bytea            | text, text          | func
(2 rows)

示例：

drop table tt;
create table tt (id int,name varchar2(10));
insert into tt values (1001,'aabcabbc'),(2001,'abcdef'),(3001,'aacbaab');

seaboxsql=# SELECT id, name FROM tt ORDER BY NLSSORT(name,'da_DK.UTF8');
  id  |   name
------+----------
 2001 | abcdef
 1001 | aabcabbc
 3001 | aacbaab
(3 rows)

seaboxsql=# SELECT id, name FROM tt ORDER BY NLSSORT(name,'en_US.UTF8');
  id  |   name
------+----------
 1001 | aabcabbc
 3001 | aacbaab
 2001 | abcdef
(3 rows)

-- 可以使用 SELECT 语句设置 set_nls_sort 语言环境
seaboxsql=# SELECT set_nls_sort('da_DK.UTF8');
seaboxsql=# SELECT id, name FROM tt ORDER BY NLSSORT(name);
  id  |   name
------+----------
 2001 | abcdef
 1001 | aabcabbc
 3001 | aacbaab
(3 rows)

seaboxsql=# SELECT set_nls_sort('en_US.UTF8');
seaboxsql=# SELECT id, name FROM tt ORDER BY NLSSORT(name);
  id  |   name
------+----------
 1001 | aabcabbc
 3001 | aacbaab
 2001 | abcdef
(3 rows)

SUBSTR¶

SUBSTR，提取字符串指定位置和长度的一部分字符。

orafce 对这个函数做了改进。

-- 未安装 orafce
seaboxsql=# \df SUBSTR
                            List of functions
   Schema   |  Name  | Result data type |   Argument data types   | Type
------------+--------+------------------+-------------------------+------
 pg_catalog | substr | bytea            | bytea, integer          | func
 pg_catalog | substr | bytea            | bytea, integer, integer | func
 pg_catalog | substr | text             | text, integer           | func
 pg_catalog | substr | text             | text, integer, integer  | func
(4 rows)

-- 安装 orafce
seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df SUBSTR
                                  List of functions
   Schema   |  Name  | Result data type |         Argument data types          | Type
------------+--------+------------------+--------------------------------------+------
 oracle     | substr | text             | character varying, numeric           | func
 oracle     | substr | text             | character varying, numeric, numeric  | func
 oracle     | substr | text             | numeric, numeric                     | func
 oracle     | substr | text             | numeric, numeric, numeric            | func
 oracle     | substr | text             | str text, start integer              | func
 oracle     | substr | text             | str text, start integer, len integer | func
 pg_catalog | substr | bytea            | bytea, integer                       | func
 pg_catalog | substr | bytea            | bytea, integer, integer              | func
(8 rows)

seaboxsql=# SET search_path TO default;
SET

示例：

seaboxsql=# SELECT oracle.SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;
 Substring
-----------
 CDEF
(1 row)

seaboxsql=# SELECT oracle.SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL;
 Substring
-----------
 CDEF
(1 row)

SUBSTRB¶

SUBSTRB，在字符串的提取指定位置和长度的一部分字符。

seaboxsql=# \df SUBSTRB
                              List of functions
   Schema   |  Name   | Result data type |    Argument data types     | Type
------------+---------+------------------+----------------------------+------
 pg_catalog | substrb | varchar2         | varchar2, integer          | func
 pg_catalog | substrb | varchar2         | varchar2, integer, integer | func
(2 rows)

示例：

seaboxsql=# SELECT SUBSTRB('aaabbbccc',4,3) FROM DUAL;
 substrb
---------
 bbb
(1 row)

-- 以下这个返回的结果应该是错误的
seaboxsql=# SELECT SUBSTRB('aaabbbccc',-2,6) FROM DUAL;
 substrb
---------
 aaa
(1 row)

SQL> SELECT SUBSTRB('aaabbbccc',-2,6) FROM DUAL;

SU
--
cc

ADD_MONTHS¶

ADD_MONTHS，返回加月份的日期

seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df ADD_MONTHS
                                         List of functions
   Schema   |    Name    |      Result data type       |        Argument data types         | Type
------------+------------+-----------------------------+------------------------------------+------
 oracle     | add_months | timestamp without time zone | timestamp with time zone, integer  | func
 pg_catalog | add_months | pg_catalog.date             | day pg_catalog.date, value integer | func
(2 rows)

seaboxsql=# SET search_path TO default;
SET

下面的示例显示了在 2016 年 5 月 1 日上加 3 个月的结果。

seaboxsql=# SELECT oracle.ADD_MONTHS(to_date('2016/05/01','YYYY/MM/DD'),3) from dual;
     add_months
---------------------
 2016-08-01 00:00:00
(1 row)

DBTIMEZONE¶

DBTIMEZONE，返回数据库时区

seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df DBTIMEZONE
                          List of functions
 Schema |    Name    | Result data type | Argument data types | Type
--------+------------+------------------+---------------------+------
 oracle | dbtimezone | text             |                     | func
(1 row)

seaboxsql=# SET search_path TO default;
SET

示例：

seaboxsql=# SELECT oracle.DBTIMEZONE() FROM DUAL;
 dbtimezone
------------
 GMT
(1 row)

-- oracle，加括号会报错误，但这个函数在程序上一般用不上
SQL> SELECT DBTIMEZONE() FROM DUAL;
SELECT DBTIMEZONE() FROM DUAL
                 *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+00:00

SESSIONTIMEZONE¶

SESSIONTIMEZONE，返回会话的时区。

seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df SESSIONTIMEZONE
                            List of functions
 Schema |      Name       | Result data type | Argument data types | Type
--------+-----------------+------------------+---------------------+------
 oracle | sessiontimezone | text             |                     | func
(1 row)

seaboxsql=# SET search_path TO default;
SET

在以下示例中，将返回会话的时区。

seaboxsql=# SELECT SESSIONTIMEZONE() FROM DUAL;
 sessiontimezone 
-----------------
 Asia/Shanghai
(1 row)

-- oracle，加括号会报错误，但这个函数在程序上一般用不上
SQL> SELECT SESSIONTIMEZONE() FROM DUAL;
SELECT SESSIONTIMEZONE() FROM DUAL
                      *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> SELECT SESSIONTIMEZONE FROM DUAL;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

LAST_DAY¶

LAST_DAY，返回指定日期所在月份的最后一天

seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df LAST_DAY
                                   List of functions
   Schema   |   Name   |      Result data type       |   Argument data types    | Type
------------+----------+-----------------------------+--------------------------+------
 oracle     | last_day | timestamp without time zone | timestamp with time zone | func
 pg_catalog | last_day | pg_catalog.date             | value pg_catalog.date    | func
(2 rows)

seaboxsql=# SET search_path TO default;
SET

在下面的示例中，返回 “2016 年 2 月 1 日” 的最后日期：

seaboxsql=# SELECT oracle.LAST_DAY(to_date('2016/02/01','YYYY/MM/DD')) from dual;
      last_day
---------------------
 2016-02-29 00:00:00
(1 row)

MONTHS_BETWEEN¶

MONTHS_BETWEEN，返回两个日期之间的月数

seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df MONTHS_BETWEEN
                                             List of functions
   Schema   |      Name      | Result data type |                Argument data types                 | Type
------------+----------------+------------------+----------------------------------------------------+------
 oracle     | months_between | numeric          | timestamp with time zone, timestamp with time zone | func
 pg_catalog | months_between | numeric          | date1 pg_catalog.date, date2 pg_catalog.date       | func
(2 rows)

seaboxsql=# SET search_path TO default;
SET

在以下示例中，将返回 “2016 年 3 月 15 日” 和 “2015 年 11 月 15 日” 之间的月份差。

seaboxsql=# SELECT oracle.MONTHS_BETWEEN(to_date('2016/03/15','YYYY/MM/DD'), to_date('2015/11/15','YYYY/MM/DD')) FROM DUAL;
 months_between
----------------
              4
(1 row)

NEXT_DAY¶

NEXT_DAY，返回指定日期之后一周内特定日期的日期。

seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df NEXT_DAY
                                          List of functions
   Schema   |   Name   |      Result data type       |          Argument data types           | Type
------------+----------+-----------------------------+----------------------------------------+------
 oracle     | next_day | timestamp without time zone | timestamp with time zone, integer      | func
 oracle     | next_day | timestamp without time zone | timestamp with time zone, text         | func
 pg_catalog | next_day | pg_catalog.date             | value pg_catalog.date, weekday integer | func
 pg_catalog | next_day | pg_catalog.date             | value pg_catalog.date, weekday text    | func
(4 rows)

seaboxsql=# SET search_path TO default;
SET

在下面的示例中，返回 “2016 年 5 月 1 日” 之后的第一个星期五的日期。

seaboxsql=# SELECT oracle.NEXT_DAY(to_date('2016/05/01','YYYY/MM/DD'), 'Friday') FROM DUAL;
      next_day
---------------------
 2016-05-06 00:00:00
(1 row)

seaboxsql=# SELECT oracle.NEXT_DAY(to_date('2016/05/01','YYYY/MM/DD'), 6) FROM DUAL;
      next_day
---------------------
 2016-05-06 00:00:00
(1 row)

ROUND and TRUNC¶

ROUND，对日期进行四舍五入。TRUNC，截断日期，截取时不进行四舍五入。

SeaboxSQL 也自带了这两个函数，但是不能操作日期类型，orafce 对这两个函数做了改进。

-- 未安装 orafce
seaboxsql=# \df ROUND
                         List of functions
   Schema   | Name  | Result data type | Argument data types | Type
------------+-------+------------------+---------------------+------
 pg_catalog | round | double precision | double precision    | func
 pg_catalog | round | numeric          | numeric             | func
 pg_catalog | round | numeric          | numeric, integer    | func
(3 rows)

seaboxsql=# \df TRUNC
                         List of functions
   Schema   | Name  | Result data type | Argument data types | Type
------------+-------+------------------+---------------------+------
 pg_catalog | trunc | double precision | double precision    | func
 pg_catalog | trunc | macaddr          | macaddr             | func
 pg_catalog | trunc | macaddr8         | macaddr8            | func
 pg_catalog | trunc | numeric          | numeric             | func
 pg_catalog | trunc | numeric          | numeric, integer    | func
(5 rows)

-- 安装 orafce
seaboxsql=# SET search_path TO oracle,"$user", public, pg_catalog;
SET
seaboxsql=# \df ROUND
                                           List of functions
   Schema   | Name  |      Result data type       |             Argument data types             | Type
------------+-------+-----------------------------+---------------------------------------------+------
 oracle     | round | numeric                     | double precision, integer                   | func
 oracle     | round | numeric                     | real, integer                               | func
 pg_catalog | round | double precision            | double precision                            | func
 pg_catalog | round | numeric                     | numeric                                     | func
 pg_catalog | round | numeric                     | numeric, integer                            | func
 pg_catalog | round | pg_catalog.date             | value pg_catalog.date                       | func
 pg_catalog | round | pg_catalog.date             | value pg_catalog.date, fmt text             | func
 pg_catalog | round | timestamp without time zone | value timestamp without time zone           | func
 pg_catalog | round | timestamp without time zone | value timestamp without time zone, fmt text | func
 pg_catalog | round | timestamp with time zone    | value timestamp with time zone              | func
 pg_catalog | round | timestamp with time zone    | value timestamp with time zone, fmt text    | func
(11 rows)

seaboxsql=# \df TRUNC
                                           List of functions
   Schema   | Name  |      Result data type       |             Argument data types             | Type
------------+-------+-----------------------------+---------------------------------------------+------
 oracle     | trunc | numeric                     | double precision, integer                   | func
 oracle     | trunc | numeric                     | real, integer                               | func
 pg_catalog | trunc | double precision            | double precision                            | func
 pg_catalog | trunc | macaddr                     | macaddr                                     | func
 pg_catalog | trunc | macaddr8                    | macaddr8                                    | func
 pg_catalog | trunc | numeric                     | numeric                                     | func
 pg_catalog | trunc | numeric                     | numeric, integer                            | func
 pg_catalog | trunc | pg_catalog.date             | value pg_catalog.date                       | func
 pg_catalog | trunc | pg_catalog.date             | value pg_catalog.date, fmt text             | func
 pg_catalog | trunc | timestamp without time zone | value timestamp without time zone           | func
 pg_catalog | trunc | timestamp without time zone | value timestamp without time zone, fmt text | func
 pg_catalog | trunc | timestamp with time zone    | value timestamp with time zone              | func
 pg_catalog | trunc | timestamp with time zone    | value timestamp with time zone, fmt text    | func
(13 rows)

seaboxsql=# SET search_path TO default;
SET

Values that can be specified for the format model

Format model	Rounding unit
Y,YY,YYY,YYYY, Year SYYYY,YEAR,SYEAR	Year
I,IY,IYY,IYYY	Year(values including calendar weeks,in compliance with the ISO standard)
Q	Quarter
WW	Week(first day of the year)
IW	Week(Monday of that week)
W	Week(first weekday on which the first day of the month falls)
DAY,DY,D	Week(Sunday of that week)
MONTH,MON,MM,RM	Month
CC,SCC	Century
DDD,DD,J	Day
HH,HH12,HH24	Hour
MI	Minute

在下面的示例中，返回 “2016 年 6 月 20 日 18:00:00” 按星期几进行四舍五入的结果。

seaboxsql=# SELECT ROUND(to_date('2016/06/20 18:00:00','YYYY/MM/DD HH24:MI:SS'),'DAY') FROM DUAL;
   round    
------------
 2016-06-19
(1 row)

在下面的示例中，返回按天截断的 “2016 年 8 月 10 日 15:30:00” 的结果。

seaboxsql=# SELECT TRUNC(to_date('2016/08/10 15:30:00','YYYY/MM/DD HH24:MI:SS'),'DAY') FROM DUAL;
   trunc    
------------
 2016-08-07
(1 row)

TO_CHAR¶

TO_CHAR，将值转换为字符串。

SeaboxSQL 在 orafce 中对这个函数做了改进。

-- 未安装 orafce
seaboxsql=# \df TO_CHAR
                                 List of functions
   Schema   |  Name   | Result data type |        Argument data types        | Type
------------+---------+------------------+-----------------------------------+------
 pg_catalog | to_char | text             | bigint, text                      | func
 pg_catalog | to_char | text             | double precision, text            | func
 pg_catalog | to_char | text             | integer, text                     | func
 pg_catalog | to_char | text             | interval, text                    | func
 pg_catalog | to_char | text             | numeric, text                     | func
 pg_catalog | to_char | text             | real, text                        | func
 pg_catalog | to_char | text             | timestamp without time zone, text | func
 pg_catalog | to_char | text             | timestamp with time zone, text    | func
(8 rows)

-- 安装 orafce
seaboxsql=# select pronamespace as Schema, prosrc as Name , prorettype as Result_data_type, proargtypes as Argument_data_types from pg_proc where proname = 'to_char';
 schema |            name            | result_data_type | argument_data_types 
--------+----------------------------+------------------+---------------------
     11 | timestamptz_to_char        |               25 | 1184 25
     11 | numeric_to_char            |               25 | 1700 25
     11 | int4_to_char               |               25 | 23 25
     11 | int8_to_char               |               25 | 20 25
     11 | float4_to_char             |               25 | 700 25
     11 | float8_to_char             |               25 | 701 25
     11 | interval_to_char           |               25 | 1186 25
     11 | timestamp_to_char          |               25 | 1114 25
     11 | orafce_to_char_int4        |               25 | 21
     11 | orafce_to_char_int4        |               25 | 23
     11 | orafce_to_char_int8        |               25 | 20
     11 | orafce_to_char_float4      |               25 | 700
     11 | orafce_to_char_float8      |               25 | 701
     11 | orafce_to_char_numeric     |               25 | 1700
     11 |  SELECT $1                 |               25 | 25
     11 | orafce_to_char_date        |               25 | 1082
     11 | orafce_to_char_timestamp   |               25 | 1114
     11 | orafce_to_char_timestamptz |               25 | 1184
     11 | orafce_to_char_interval    |               25 | 1186
(19 rows)

示例：

seaboxsql=# SELECT oracle.TO_CHAR(123.45) FROM DUAL;
 to_char
---------
 123.45
(1 row)

-- 转换日期格式
seaboxsql=# select * from test_range;
 id |     create_time
----+---------------------
  1 | 2022-04-01 10:08:18
(1 row)

seaboxsql=# select TO_CHAR(create_time,'YYYY/MM/DD HH24:MI:SS') from test_range;
       to_char
---------------------
 2022/04/01 10:08:18
(1 row)

-- 可以使用 orafce.nls_date_format 变量设置日期/时间格式
seaboxsql=# SET orafce.nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
SET
seaboxsql=# select TO_CHAR(create_time) from test_range;
       to_char
---------------------
 2022/04/01 10:08:18
(1 row)

seaboxsql=# SET orafce.nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
SET
seaboxsql=# select TO_CHAR(create_time) from test_range;
       to_char
---------------------
 2022-04-01 10:08:18
(1 row)

TO_DATE¶

TO_DATE，根据指定格式将字符串转换为日期。

orafce 对这个函数做了改进。

-- 未安装 orafce
seaboxsql=# \df TO_DATE
                          List of functions
   Schema   |  Name   | Result data type | Argument data types | Type
------------+---------+------------------+---------------------+------
 pg_catalog | to_date | date             | text, text          | func
(1 row)

-- 安装 orafce
seaboxsql=# \df TO_DATE
                          List of functions
   Schema   |  Name   | Result data type | Argument data types | Type 
------------+---------+------------------+---------------------+------
 pg_catalog | to_date | date             | text                | func
 pg_catalog | to_date | pg_catalog.date  | text, text          | func
(2 rows)

在以下示例中，字符串 “2016/12/31” 被转换为日期并返回

seaboxsql=# SELECT TO_DATE('2016/12/31','YYYY/MM/DD') FROM DUAL;
  to_date   
------------
 2016-12-31
(1 row)

-- 问题：orafce.nls_date_format 似乎对 TO_DATE 不起作用
seaboxsql=# SET orafce.nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
SET
seaboxsql=# SELECT TO_DATE('2016/12/31','YYYY/MM/DD') FROM DUAL;
  to_date   
------------
 2016-12-31
(1 row)

TO_NUMBER¶

TO_NUMBER，根据指定格式将值转换为数字。

SeaboxSQL 在 orafce 中对这个函数做了改进。

-- 未安装 orafce
seaboxsql=# \df TO_NUMBER
                           List of functions
   Schema   |   Name    | Result data type | Argument data types | Type
------------+-----------+------------------+---------------------+------
 pg_catalog | to_number | numeric          | text, text          | func
(1 row)

-- 安装 orafce
seaboxsql=# select pronamespace as Schema, prosrc as Name , prorettype as Result_data_type, proargtypes as Argument_data_types from pg_proc where proname = 'to_number';
 schema |                      name                       | result_data_type | argument_data_types 
--------+-------------------------------------------------+------------------+---------------------
     11 | numeric_to_number                               |             1700 | 25 25
     11 | orafce_to_number                                |             1700 | 25
     11 |                                                +|             1700 | 1700
        | SELECT pg_catalog.to_number($1::text);         +|                  | 
        |                                                 |                  | 
     11 |                                                +|             1700 | 700
        | SELECT pg_catalog.to_number($1::text);         +|                  | 
        |                                                 |                  | 
     11 |                                                +|             1700 | 701
        | SELECT pg_catalog.to_number($1::text);         +|                  | 
        |                                                 |                  | 
     11 |                                                +|             1700 | 1700 1700
        | SELECT pg_catalog.to_number($1::text,$2::text);+|                  | 
        |                                                 |                  | 
(6 rows)

示例:

-- 数字文字 "-130.5" 被转换为数值并返回。
seaboxsql=# SELECT TO_NUMBER(-130.5) FROM DUAL;
 to_number
-----------
    -130.5
(1 row)

seaboxsql=# SELECT TO_NUMBER('-130.5') FROM DUAL;
 to_number
-----------
    -130.5
(1 row)

-- 转换 varchar2
seaboxsql=# \d tt2
                    Table "public.tt2"
 Column |      Type      | Collation | Nullable | Default
--------+----------------+-----------+----------+---------
 id     | varchar2(100)  |           |          |
 name   | nvarchar2(100) |           |          |
 ctime  | date           |           |          |

seaboxsql=# select id, TO_NUMBER(id) from tt2 where id = '3003963447';
     id     | to_number
------------+------------
 3003963447 | 3003963447

TO_TIMESTAMP¶

TO_TIMESTAMP，根据指定格式将字符串转换为 TIMESTAMP。

SeaboxSQL 在 orafce 中对这个函数做了改进。

-- 未安装 orafce
seaboxsql=# \df TO_TIMESTAMP
                                 List of functions
   Schema   |     Name     |     Result data type     | Argument data types | Type 
------------+--------------+--------------------------+---------------------+------
 pg_catalog | to_timestamp | timestamp with time zone | double precision    | func
 pg_catalog | to_timestamp | timestamp with time zone | text, text          | func
(2 rows)

-- 安装 orafce
seaboxsql=# select pronamespace as Schema, prosrc as Name , prorettype as Result_data_type, proargtypes as Argument_data_types from pg_proc where proname = 'to_timestamp';
 schema |                                   name                                   | result_data_type | argument_data_types 
--------+--------------------------------------------------------------------------+------------------+---------------------
   6109 | ts_pg_unix_microseconds_to_timestamp                                     |             1184 | 20
     11 |  SELECT pg_catalog.TO_TIMESTAMP($1,'YYYY-MM-DD HH24:MI:SS')::timestamp;  |             1114 | 25
     11 | float8_timestamptz                                                       |             1184 | 701
     11 | to_timestamp                                                             |             1184 | 25 25
(4 rows)

示例:

seaboxsql=# select to_timestamp('2015-01-01 23:23:23');
    to_timestamp     
---------------------
 2015-01-01 23:23:23
(1 row)

TO_TIMESTAMP_TZ¶

TO_TIMESTAMP_TZ，将字符串转换为 TIMESTAMPTZ。

SeaboxSQL 在 orafce 中添加了这个函数。

seaboxsql=# select pronamespace as Schema, prosrc as Name , prorettype as Result_data_type, proargtypes as Argument_data_types from pg_proc where proname = 'to_timestamp_tz';
 schema |      name       | result_data_type | argument_data_types 
--------+-----------------+------------------+---------------------
     11 | to_timestamp_tz |             1184 | 25
(1 row)

示例:

seaboxsql=# select to_timestamp_tz('1997-02-10 17:32:01 PST');
    to_timestamp_tz     
------------------------
 1997-02-11 09:32:01+08
(1 row)

TO_MULTI_BYTE¶

TO_MULTI_BYTE，将单字节字符串转换为多字节字符串，也是将半角字符转换为全角字符。

seaboxsql=# \df TO_MULTI_BYTE
                           List of functions
 Schema |     Name      | Result data type | Argument data types | Type
--------+---------------+------------------+---------------------+------
 public | to_multi_byte | text             | str text            | func
(1 row)

在以下示例中，“abc123” 被转换为全角字符并返回。

seaboxsql=# SELECT TO_MULTI_BYTE('abc123') FROM DUAL;
 to_multi_byte
---------------
 ａｂｃ１２３
(1 row)

TO_SINGLE_BYTE¶

TO_SINGLE_BYTE，将多字节字符串转换为单字节字符串，也是将全角字符转换为半角字符。

seaboxsql=# \df TO_SINGLE_BYTE
                            List of functions
 Schema |      Name      | Result data type | Argument data types | Type
--------+----------------+------------------+---------------------+------
 public | to_single_byte | text             | str text            | func
(1 row)

在以下示例中，“ａｂｃ１２３” 被转换为半角字符并返回。

seaboxsql=# SELECT TO_SINGLE_BYTE('ａｂｃ１２３') FROM DUAL;
 to_single_byte
----------------
 abc123
(1 row)

DECODE¶

DECODE，比较值，如果它们匹配，则返回相应的值。

orafce 对这个函数做了改进。

-- 未安装 orafce
seaboxsql=# \df DECODE
                          List of functions
   Schema   |  Name  | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+------
 pg_catalog | decode | bytea            | text, text          | func
(1 row)

-- 安装 orafce
seaboxsql=# \df DECODE
                                                                                                       List of functions
   Schema   |  Name  |      Result data type       |                                                                        Argument data types                                                                         | Type
------------+--------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+------
 pg_catalog | decode | bytea                       | text, text                                                                                                                                                         | func
 public     | decode | bigint                      | anyelement, anyelement, bigint                                                                                                                                     | func
 public     | decode | bigint                      | anyelement, anyelement, bigint, anyelement, bigint                                                                                                                 | func
 public     | decode | bigint                      | anyelement, anyelement, bigint, anyelement, bigint, anyelement, bigint                                                                                             | func
 public     | decode | bigint                      | anyelement, anyelement, bigint, anyelement, bigint, anyelement, bigint, bigint                                                                                     | func
 public     | decode | bigint                      | anyelement, anyelement, bigint, anyelement, bigint, bigint                                                                                                         | func
 public     | decode | bigint                      | anyelement, anyelement, bigint, bigint                                                                                                                             | func
 public     | decode | character                   | anyelement, anyelement, character                                                                                                                                  | func
 public     | decode | character                   | anyelement, anyelement, character, anyelement, character                                                                                                           | func
 public     | decode | character                   | anyelement, anyelement, character, anyelement, character, anyelement, character                                                                                    | func
 public     | decode | character                   | anyelement, anyelement, character, anyelement, character, anyelement, character, character                                                                         | func
 public     | decode | character                   | anyelement, anyelement, character, anyelement, character, character                                                                                                | func
 public     | decode | character                   | anyelement, anyelement, character, character                                                                                                                       | func
 public     | decode | integer                     | anyelement, anyelement, integer                                                                                                                                    | func
 public     | decode | integer                     | anyelement, anyelement, integer, anyelement, integer                                                                                                               | func
 public     | decode | integer                     | anyelement, anyelement, integer, anyelement, integer, anyelement, integer                                                                                          | func
 public     | decode | integer                     | anyelement, anyelement, integer, anyelement, integer, anyelement, integer, integer                                                                                 | func
 public     | decode | integer                     | anyelement, anyelement, integer, anyelement, integer, integer                                                                                                      | func
 public     | decode | integer                     | anyelement, anyelement, integer, integer                                                                                                                           | func
 public     | decode | numeric                     | anyelement, anyelement, numeric                                                                                                                                    | func
 public     | decode | numeric                     | anyelement, anyelement, numeric, anyelement, numeric                                                                                                               | func
 public     | decode | numeric                     | anyelement, anyelement, numeric, anyelement, numeric, anyelement, numeric                                                                                          | func
 public     | decode | numeric                     | anyelement, anyelement, numeric, anyelement, numeric, anyelement, numeric, numeric                                                                                 | func
 public     | decode | numeric                     | anyelement, anyelement, numeric, anyelement, numeric, numeric                                                                                                      | func
 public     | decode | numeric                     | anyelement, anyelement, numeric, numeric                                                                                                                           | func
 public     | decode | pg_catalog.date             | anyelement, anyelement, pg_catalog.date                                                                                                                            | func
 public     | decode | pg_catalog.date             | anyelement, anyelement, pg_catalog.date, anyelement, pg_catalog.date                                                                                               | func
 public     | decode | pg_catalog.date             | anyelement, anyelement, pg_catalog.date, anyelement, pg_catalog.date, anyelement, pg_catalog.date                                                                  | func
 public     | decode | pg_catalog.date             | anyelement, anyelement, pg_catalog.date, anyelement, pg_catalog.date, anyelement, pg_catalog.date, pg_catalog.date                                                 | func
 public     | decode | pg_catalog.date             | anyelement, anyelement, pg_catalog.date, anyelement, pg_catalog.date, pg_catalog.date                                                                              | func
 public     | decode | pg_catalog.date             | anyelement, anyelement, pg_catalog.date, pg_catalog.date                                                                                                           | func
 public     | decode | text                        | anyelement, anyelement, text                                                                                                                                       | func
 public     | decode | text                        | anyelement, anyelement, text, anyelement, text                                                                                                                     | func
 public     | decode | text                        | anyelement, anyelement, text, anyelement, text, anyelement, text                                                                                                   | func
 public     | decode | text                        | anyelement, anyelement, text, anyelement, text, anyelement, text, text                                                                                             | func
 public     | decode | text                        | anyelement, anyelement, text, anyelement, text, text                                                                                                               | func
 public     | decode | text                        | anyelement, anyelement, text, text                                                                                                                                 | func
 public     | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone                                                                                                                | func
 public     | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone, anyelement, timestamp without time zone                                                                       | func
 public     | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone, anyelement, timestamp without time zone, anyelement, timestamp without time zone                              | func
 public     | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone, anyelement, timestamp without time zone, anyelement, timestamp without time zone, timestamp without time zone | func
 public     | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone, anyelement, timestamp without time zone, timestamp without time zone                                          | func
 public     | decode | timestamp without time zone | anyelement, anyelement, timestamp without time zone, timestamp without time zone                                                                                   | func
 public     | decode | timestamp with time zone    | anyelement, anyelement, timestamp with time zone                                                                                                                   | func
 public     | decode | timestamp with time zone    | anyelement, anyelement, timestamp with time zone, anyelement, timestamp with time zone                                                                             | func
 public     | decode | timestamp with time zone    | anyelement, anyelement, timestamp with time zone, anyelement, timestamp with time zone, anyelement, timestamp with time zone                                       | func
 public     | decode | timestamp with time zone    | anyelement, anyelement, timestamp with time zone, anyelement, timestamp with time zone, anyelement, timestamp with time zone, timestamp with time zone             | func
 public     | decode | timestamp with time zone    | anyelement, anyelement, timestamp with time zone, anyelement, timestamp with time zone, timestamp with time zone                                                   | func
 public     | decode | timestamp with time zone    | anyelement, anyelement, timestamp with time zone, timestamp with time zone                                                                                         | func
 public     | decode | time without time zone      | anyelement, anyelement, time without time zone                                                                                                                     | func
 public     | decode | time without time zone      | anyelement, anyelement, time without time zone, anyelement, time without time zone                                                                                 | func
 public     | decode | time without time zone      | anyelement, anyelement, time without time zone, anyelement, time without time zone, anyelement, time without time zone                                             | func
 public     | decode | time without time zone      | anyelement, anyelement, time without time zone, anyelement, time without time zone, anyelement, time without time zone, time without time zone                     | func
 public     | decode | time without time zone      | anyelement, anyelement, time without time zone, anyelement, time without time zone, time without time zone                                                         | func
 public     | decode | time without time zone      | anyelement, anyelement, time without time zone, time without time zone                                                                                             | func
(55 rows)

DECODE 将要转换的值与搜索值一一进行比较。如果值匹配，则返回相应的结果值。如果没有匹配的值，则返回已指定的默认值。如果未指定默认值，则返回 NULL 值。
如果多次指定相同的搜索值，则返回的结果值是为第一次出现的搜索值列出的值。
以下数据类型可用于结果值和默认值：

CHAR
VARCHAR
VARCHAR2
NCHAR
NCHAR VARYING
NVARCHAR2
TEXT
INTEGER
BIGINT
NUMERIC
DATE
TIME WITHOUT TIME ZONE
TIMESTAMP WITHOUT TIME ZONE
TIMESTAMP WITH TIME ZONE

DECODE 可以转换的数据类型组合见下图：（总结）

Decode1

可通过 DECODE 转换的结果值和默认值日期/时间数据类型见下图：

Decode2

在以下示例中，比较表 t1 中 col3 的值并将其转换为不同的值。如果 col3 值与搜索值 1 匹配，则返回的结果值为 “one”。如果 col3 值不匹配任何搜索值 1、2 或 3，则返回默认值 “other number”。

drop table tt;
create table tt (col1 int,col3 int);
insert into tt values (1001,1),(2001,2),(3001,3),(4001,4);

SELECT col1,
       DECODE(col3, 1, 'one',
                    2, 'two',
                    3, 'three',
                    'other number') "num-word"
       FROM tt;

 col1 |   num-word
------+--------------
 1001 | one
 2001 | two
 3001 | three
 4001 | other number
(4 rows)

LNNVL¶

LNNVL 确定指定条件的值是 TRUE 还是 FALSE。

如果条件的结果为 FALSE 或 NULL，则返回 TRUE。如果条件的结果为 TRUE，则返回 FALSE。

seaboxsql=# \df LNNVL
                         List of functions
   Schema   | Name  | Result data type | Argument data types | Type
------------+-------+------------------+---------------------+------
 pg_catalog | lnnvl | boolean          | boolean             | func
(1 row)

在以下示例中，当 col3 的值小于等于 2000 或为空值时，将返回表 tt 的 col1 和 col3。

drop table tt;
create table tt (col1 int,col3 int);
insert into tt values (1001,1000),(1002,2000),(2002,null),(3001,3000);

seaboxsql=# select * from tt;
 col1 | col3
------+------
 1001 | 1000
 1002 | 2000
 2002 |
 3001 | 3000
(4 rows)

seaboxsql=# SELECT col1,col3 FROM tt WHERE LNNVL( col3 > 2000 );
 col1 | col3
------+------
 1001 | 1000
 1002 | 2000
 2002 |
(3 rows)

NANVL¶

NANVL，当值不是数字 (NaN) 时返回替代值。

替代值可以是数字或可以转换为数字的字符串。

seaboxsql=# \df NANVL
                               List of functions
 Schema | Name  | Result data type |         Argument data types         | Type
--------+-------+------------------+-------------------------------------+------
 public | nanvl | double precision | double precision, character varying | func
 public | nanvl | double precision | double precision, double precision  | func
 public | nanvl | numeric          | numeric, character varying          | func
 public | nanvl | numeric          | numeric, numeric                    | func
 public | nanvl | real             | real, character varying             | func
 public | nanvl | real             | real, real                          | func
(6 rows)

在以下示例中，如果表 tt 中 col3 的值为 NaN 值，则返回 “0”。

seaboxsql=# SELECT NANVL('123',0);
 nanvl
-------
   123
(1 row)

seaboxsql=# SELECT NANVL('NaN',0);
 nanvl
-------
     0
(1 row)

LISTAGG¶

LISTAGG，连接并分隔一组字符串值并返回结果。

seaboxsql=# \df LISTAGG
                          List of functions
   Schema   |  Name   | Result data type | Argument data types | Type
------------+---------+------------------+---------------------+------
 pg_catalog | listagg | text             | text                | agg
 pg_catalog | listagg | text             | text, text          | agg
(2 rows)

在以下示例中，将返回表 tt 中列 col1 的值由 ‘:’ 分隔的结果。

drop table tt;
create table tt (col1 int,col3 int);
insert into tt values (1001,1000),(1002,2000),(2002,null),(3001,3000);

-- oracle
SQL> SELECT LISTAGG(col1,':') WITHIN GROUP (ORDER BY col1) from tt;

LISTAGG(COL1,':')WITHINGROUP(ORDERBYCOL1)
--------------------------------------------------------------------------------
1001:1002:2002:3001

-- orafce，问题：不支持隐式转换
seaboxsql=# SELECT LISTAGG(col1,':') FROM tt;
ERROR:  function listagg(integer, unknown) does not exist
LINE 1: SELECT LISTAGG(col1,':') FROM tt;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

seaboxsql=# SELECT LISTAGG(col1::text,':') FROM tt;
       listagg
---------------------
 1001:1002:2002:3001
(1 row)

seaboxsql=# SELECT LISTAGG(col3::text,':') FROM tt;
    listagg
----------------
 1000:2000:3000
(1 row)

-- orafce，问题：不能排序，不支持 oracle 的 WITHIN
drop table tt;
create table tt (col1 text,col3 int);
insert into tt values ('2001',1000),('1002',2000),('1002',null),('3001',3000);

seaboxsql=# SELECT LISTAGG(col1,':') FROM tt;
       listagg
---------------------
 2001:1002:1002:3001
(1 row)

seaboxsql=# SELECT LISTAGG(col1,':') WITHIN GROUP (ORDER BY col1) from tt;
ERROR:  function listagg(text, unknown, text) does not exist
LINE 1: SELECT LISTAGG(col1,':') WITHIN GROUP (ORDER BY col1) from t...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

MEDIAN¶

MEDIAN，计算一组数字的中位数。

seaboxsql=# \df MEDIAN
                                                List of functions
   Schema   |  Name  |      Result data type       |                  Argument data types                  | Type 
------------+--------+-----------------------------+-------------------------------------------------------+------
 pg_catalog | median | double precision            | double precision                                      | agg
 pg_catalog | median | double precision            | double precision ORDER BY double precision            | agg
 pg_catalog | median | interval                    | double precision ORDER BY interval                    | agg
 pg_catalog | median | timestamp with time zone    | double precision ORDER BY timestamp with time zone    | agg
 pg_catalog | median | timestamp without time zone | double precision ORDER BY timestamp without time zone | agg
 pg_catalog | median | real                        | real                                                  | agg
(6 rows)

在以下示例中，返回表 tt 中列 col1 的中位数。

drop table tt;
create table tt (col1 text,col3 int);
insert into tt values ('2001',1000),('1002',2000),('1002',null),('3001',3000);

-- 一样不支持隐式转换
seaboxsql=# SELECT MEDIAN(col1) FROM tt;
ERROR:  function median(text) does not exist
LINE 1: SELECT MEDIAN(col1) FROM tt;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

seaboxsql=# SELECT MEDIAN(col1::double precision) FROM tt;
 median
--------
 1501.5
(1 row)

DUMP¶

DUMP，返回值的内部信息。

seaboxsql=# \df DUMP
                       List of functions
 Schema | Name | Result data type  | Argument data types | Type
--------+------+-------------------+---------------------+------
 public | dump | character varying | "any"               | func
 public | dump | character varying | "any", integer      | func
 public | dump | character varying | text                | func
 public | dump | character varying | text, integer       | func
(4 rows)

在下面的示例中，返回表 tt 中列 col1 的内部信息。

drop table tt;
create table tt (col1 text,col3 int);
insert into tt values ('2001',1000),('1002',2000),('1002',null),('3001',3000);

seaboxsql=# SELECT col1, DUMP(col1) FROM tt;
 col1 |             dump
------+------------------------------
 2001 | Typ=25 Len=5: 11,50,48,48,49
 1002 | Typ=25 Len=5: 11,49,48,48,50
 1002 | Typ=25 Len=5: 11,49,48,48,50
 3001 | Typ=25 Len=5: 11,51,48,48,49
(4 rows)

SQL 运算符¶

DATE 类型的 orafce 支持以下日期时间运算符。

Operation	Example	Result
+	DATE'2016/01/01'+10	2016-01-11 00:00:00
-	DATE'2016/03/20'-35	2016-02-14 00:00:00
-	DATE'2016/09/01'- DATE'2015/12/31'	245

DBMS_ROWID¶

解析表中伪列rowid的含义，其中参数部分的rid为18位Base64编码，boolean一直指示是否为列存表

seaboxsql=# \df
                                            List of functions
   Schema   |        Name        | Result data type |             Argument data types             | Type 
------------+--------------------+------------------+---------------------------------------------+------
 dbms_rowid | rowid_block_number | bigint           | rid, boolean                                | func
 dbms_rowid | rowid_create       | rid              | integer, integer, integer, integer, boolean | func
 dbms_rowid | rowid_filenode     | oid              | rid                                         | func
 dbms_rowid | rowid_row_number   | integer          | rid, boolean                                | func
 dbms_rowid | rowid_segmentid    | smallint         | rid                                         | func
 dbms_rowid | rowid_segno        | bigint           | rid, boolean                                | func
(6 rows)

通过dbms_rowid.rowid_create 拼接成一个rid

select dbms_rowid.rowid_create(0, 16384, 0, 1, false) as rowid;
       rowid        
--------------------
 AAAAAAEAAAAAAAAAAB
(1 row)

如下示例中，通过以上函数将表t1的rowid解析成可读内容

seaboxsql=# create table t1(a int);
CREATE TABLE
Time: 16.168 ms
seaboxsql=# insert into t1 values(10);
INSERT 0 1
Time: 0.602 ms
seaboxsql=# select rowid from t1;
       rowid        
--------------------
 AAAAAAFGRAAAAAAAAB
(1 row)

seaboxsql=# select *, rowid,
seaboxsql-# dbms_rowid.rowid_segmentid(t1.rowid) as sc_dbindex_id,
seaboxsql-# dbms_rowid.rowid_filenode(rowid) as fileno,
seaboxsql-# dbms_rowid.rowid_segno(t1.rowid, false) as segno, 
seaboxsql-# dbms_rowid.rowid_block_number(t1.rowid, false) as blockno, 
seaboxsql-# dbms_rowid.rowid_row_number(t1.rowid, false) as rowno 
seaboxsql-# from t1;
 a  |       rowid        | sc_dbindex_id | fileno | segno | blockno | rowno 
----+--------------------+---------------+--------+-------+---------+-------
 10 | AAAAAAFGRAAAAAAAAB |             0 |  20881 |     0 |       0 |     1
(1 row)