Oracle特色SQL语法

本节介绍 Halo 在 Oracle 兼容模式下支持的 Oracle 特色 SQL 语法，包括查询、DML、序列、DUAL 伪表等。示例均在 Oracle 兼容模式下执行，提示符为 oracle=#。

说明：本章示例依赖的示例表（如 ta、t_a、td 等）均为演示用临时表，可在执行前自行创建，示例跑通后可删除。

外连接符（+）

Oracle 使用 (+) 表示外连接：(+) 所在的一侧为补充表（匹配表），另一侧为基表全部显示。(+) 写在右表即左连接，写在左表即右连接。

先准备两张示例表：

oracle=# CREATE TABLE t_a (id INT, name VARCHAR2(10));
CREATE TABLE
oracle=# INSERT INTO t_a VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');
INSERT 0 5
oracle=# CREATE TABLE t_b (id INT, name VARCHAR2(10));
CREATE TABLE
oracle=# INSERT INTO t_b VALUES (1,'AA'),(1,'BB'),(2,'CC'),(1,'DD');
INSERT 0 4

左连接（t_a 全部显示，t_b 中无匹配的行对应列为 NULL）：

oracle=# SELECT * FROM t_a a, t_b b WHERE a.id = b.id(+);
 ID | NAME | ID | NAME
----+------+----+------
  1 | A    |  1 | AA
  1 | A    |  1 | BB
  1 | A    |  1 | DD
  2 | B    |  2 | CC
  3 | C    |    |
  4 | D    |    |
  5 | E    |    |
(7 行记录)

右连接（t_b 中无匹配的 t_a 行不显示）：

oracle=# SELECT * FROM t_a a, t_b b WHERE a.id(+) = b.id;
 ID | NAME | ID | NAME
----+------+----+------
  1 | A    |  1 | AA
  1 | A    |  1 | BB
  1 | A    |  1 | DD
  2 | B    |  2 | CC
(4 行记录)

MINUS

MINUS 对结果集做差集运算：返回在第一个结果集中存在、但在第二个结果集中不存在的记录，并对结果去重。两个结果集的列数和数据类型须一致。

oracle=# CREATE TABLE td (id INT);
CREATE TABLE
oracle=# INSERT INTO td VALUES (1),(2),(3);
INSERT 0 3
oracle=# CREATE TABLE te (id INT);
CREATE TABLE
oracle=# INSERT INTO te VALUES (1),(2),(0);
INSERT 0 3
oracle=# SELECT * FROM td MINUS SELECT * FROM te;
 ID
----
  3
(1 行记录)

DECODE

DECODE 是 Oracle 的条件判断函数，语法为 DECODE(expr, search1, result1 [, search2, result2...] [, default])：依次比较 expr 与各 search，匹配则返回对应 result，全部不匹配则返回 default（未指定返回 NULL）。

oracle=# CREATE TABLE ta (id INT, name VARCHAR2(10), location VARCHAR2(10));
CREATE TABLE
oracle=# INSERT INTO ta VALUES (1,'A','CN'),(2,'B','CN'),(3,'C','US'),(4,'D','JP');
INSERT 0 4
oracle=# SELECT location, DECODE(location, 'CN', 'Y', 'N') FROM ta;
 LOCATION | CASE
----------+------
 CN       | Y
 CN       | Y
 US       | N
 JP       | N
(4 行记录)

ROWNUM

ROWNUM 是一个伪列，按行的检索顺序为每一行分配递增的唯一整数（第一行为 1）。常用于分页查询。

oracle=# SELECT * FROM (SELECT rownum rn, id, name, location FROM ta) WHERE rn <= 3 AND rn >= 1;
 RN | ID | NAME | LOCATION
----+----+------+----------
  1 |  1 | A    | CN
  2 |  2 | B    | CN
  3 |  3 | C    | US
(3 行记录)

NULL 与空串等价

Oracle 中空串 '' 与 NULL 等价。Halo 的 Oracle 兼容模式同样支持：

oracle=# SELECT 'Y' FROM dual WHERE '' IS NULL;
 ?COLUMN?
----------
 Y
(1 行记录)

IN 特别语法

Oracle 允许 IN 直接跟单个值（不加括号），Halo 同样支持：

oracle=# SELECT 'Y' FROM dual WHERE 1 IN 1;
 ?COLUMN?
----------
 Y
(1 行记录)

序列

序列（SEQUENCE）是序列号生成器，可为表的主键等自动生成等间隔数值。通过 nextval 取下一个值，currval 取当前会话的当前值。

oracle=# CREATE SEQUENCE a_seq;
CREATE SEQUENCE
oracle=# SELECT a_seq.nextval FROM dual;
 ORA_NEXTVAL
-------------
           1
(1 行记录)
oracle=# SELECT a_seq.currval FROM dual;
 ORA_CURRVAL
-------------
           1
(1 行记录)

SYSDATE

SYSDATE 返回数据库服务器当前的日期时间：

oracle=# SELECT sysdate FROM dual;
       SYSDATE
---------------------
 2026-07-30 14:38:55
(1 行记录)

DUAL 伪表

DUAL 是 Oracle 的伪表，单行单列，用于在不涉及具体表时选择系统变量或计算表达式的值（如调用函数、查看序列）：

oracle=# SELECT 'ww' FROM dual;
 ?COLUMN?
----------
 ww
(1 行记录)

别名 UPDATE

UPDATE 可通过表别名简化代码，也是同表多次引用时的常用手段：

oracle=# SELECT * FROM ta ORDER BY id;
 ID | NAME | LOCATION
----+------+----------
  1 | A    | CN
  2 | B    | CN
  3 | C    | US
  4 | D    | JP
(4 行记录)
oracle=# UPDATE ta a SET a.location = 'CN' WHERE id = 4;
UPDATE 1
oracle=# SELECT * FROM ta ORDER BY id;
 ID | NAME | LOCATION
----+------+----------
  1 | A    | CN
  2 | B    | CN
  3 | C    | US
  4 | D    | CN
(4 行记录)

无别名子查询

支持无别名的子查询（FROM 子查询不强制要求别名）：

oracle=# SELECT * FROM (SELECT * FROM ta WHERE id IN (1, 2)) WHERE location = 'CN';
 ID | NAME | LOCATION
----+------+----------
  1 | A    | CN
  2 | B    | CN
(2 行记录)

MERGE

MERGE INTO 根据指定条件对表进行"存在则更新、不存在则插入"（upsert）操作：

oracle=# CREATE TABLE merge_test (id NUMBER, name VARCHAR2(50), age NUMBER);
CREATE TABLE
oracle=# INSERT INTO merge_test VALUES (1,'John',25),(2,'Jane',30),(3,'Tom',35);
INSERT 0 3
oracle=# SELECT * FROM merge_test ORDER BY id;
 ID | NAME | AGE
----+------+-----
  1 | John |  25
  2 | Jane |  30
  3 | Tom  |  35
(3 行记录)
oracle=# MERGE INTO merge_test t
oracle-# USING (
oracle(#   SELECT 1 AS id, 'John' AS name, 26 AS age FROM dual
oracle(#   UNION ALL
oracle(#   SELECT 4 AS id, 'Sarah' AS name, 28 AS age FROM dual
oracle(# ) s
oracle-# ON (t.id = s.id)
oracle-# WHEN MATCHED THEN UPDATE SET t.name = s.name, t.age = s.age
oracle-# WHEN NOT MATCHED THEN INSERT (id, name, age) VALUES (s.id, s.name, s.age);
MERGE 2
oracle=# SELECT * FROM merge_test ORDER BY id;
 ID | NAME  | AGE
----+-------+-----
  1 | John  |  26
  2 | Jane  |  30
  3 | Tom   |  35
  4 | Sarah |  28
(4 行记录)

上例中 id=1 已存在故更新（age 25→26），id=4 不存在故插入，共影响 2 行（MERGE 2）。

CTE（WITH 子句）

CTE（公共表表达式）通过 WITH 定义临时结果集，在主查询中引用，可使复杂查询更清晰：

oracle=# WITH cte_src AS (
oracle(#   SELECT 1 AS id, 'Alice' AS name FROM dual
oracle(#   UNION ALL SELECT 2, 'Bob' FROM dual
oracle(#   UNION ALL SELECT 3, 'Carol' FROM dual
oracle(# )
oracle-# SELECT * FROM cte_src WHERE id >= 2 ORDER BY id;
 ID | NAME
----+-------
  2 | Bob
  3 | Carol
(2 行记录)

START WITH / CONNECT BY（层级查询）

START WITH ... CONNECT BY 用于查询树形层级数据，START WITH 指定根节点，CONNECT BY PRIOR 定义父子关系：

oracle=# CREATE TABLE org_tree (id NUMBER, name VARCHAR2(10), manager_id NUMBER);
CREATE TABLE
oracle=# INSERT INTO org_tree VALUES
    (1,'CEO',NULL),(2,'VP1',1),(3,'VP2',1),
    (4,'MgrA',2),(5,'MgrB',2),(6,'Emp1',4);
INSERT 0 6
oracle=# SELECT id, name, manager_id FROM org_tree
oracle-# START WITH id = 1 CONNECT BY PRIOR id = manager_id ORDER BY id;
 ID | NAME | MANAGER_ID
----+------+------------
  1 | CEO  |
  2 | VP1  |          1
  3 | VP2  |          1
  4 | MgrA |          2
  5 | MgrB |          2
  6 | Emp1 |          4
(6 行记录)

COUNT(DISTINCT ...) OVER(PARTITION BY ...)

聚合函数配合 OVER(PARTITION BY ...) 可在分组内计算，DISTINCT 支持分组去重统计。支持 COUNT、SUM、LISTAGG 等聚合函数：

oracle=# CREATE TABLE cvt (id INT, name VARCHAR2(10));
CREATE TABLE
oracle=# INSERT INTO cvt VALUES (1,'a'),(2,'a'),(3,'a'),(4,'b'),(5,'c'),(6,'d');
INSERT 0 6
oracle=# SELECT name, COUNT(DISTINCT id) OVER(PARTITION BY name) FROM cvt ORDER BY id;
 NAME | COUNT
------+-------
 a    |     3
 a    |     3
 a    |     3
 b    |     1
 c    |     1
 d    |     1
(6 行记录)

数值格式化

支持 Oracle 方式的数值输出：

oracle=# SELECT cast(100.00100 AS VARCHAR2(30)) FROM dual;
 VARCHAR
---------
 100.001
(1 行记录)

输出字段名大写

通过 enable_col_output_upcase 参数可控制查询结果字段名输出为大小写。设为 TRUE 后，字段名按 Oracle 方式输出大写：

oracle=# SET enable_col_output_upcase = TRUE;
SET

日期类型

支持 Oracle 的 DATE 类型（在 Halo 内部存储为 datetime）：

oracle=# CREATE TABLE halo_test1(
oracle(#   a DATE
oracle(# );
CREATE TABLE
oracle=# \dS+ halo_test1
                         数据表 "public.halo_test1"
 栏位 |   类型   | 校对规则 | 可空的 | 预设 | 存储  | 压缩 | 统计目标 | 描述
------+----------+----------+--------+------+-------+------+----------+------
 a    | datetime |          |        |      | plain |      |          |
访问方法 heap

DBLINK

DBLink（数据库链接）用于从本库访问远程数据库。Halo 支持通过 CREATE DATABASE LINK 访问远程 Oracle 或 PostgreSQL 数据库，实现跨库查询、插入、更新、删除。

前提条件：

已安装 aux_oracle 与 oracle_fdw 扩展；
当前数据库中存在与连接用户同名的 schema（建链依赖此 schema，否则报错）；
远程数据库已建好示例表与可登录用户。

以下示例以远程 Oracle 数据库（192.168.10.155:1522/XEPDB1，用户 halolink）中已存在的 test_table 表为目标：

-- 远程 Oracle 源库中已建表：
-- CREATE TABLE test_table (
--   id NUMBER(10) PRIMARY KEY,
--   name VARCHAR2(50) NOT NULL,
--   age NUMBER(3),
--   created_at DATE DEFAULT SYSDATE
-- );

-- 1) 在 Halo 本地安装 oracle_fdw 扩展
oracle=# CREATE EXTENSION IF NOT EXISTS oracle_fdw;
CREATE EXTENSION

-- 2) 创建与当前用户同名的 schema（建链依赖）
oracle=# CREATE SCHEMA IF NOT EXISTS halo;
CREATE SCHEMA

-- 3) 创建 DATABASE LINK（USING 为 Oracle EZCONNECT 串：//host:port/service）
oracle=# CREATE DATABASE LINK link_ora_xe
oracle-#   CONNECT TO halolink IDENTIFIED BY "oracle"
oracle-#   USING '//192.168.10.155:1522/XEPDB1';
CREATE DATABASE LINK

-- 查看已创建的 DBLINK
oracle=# \ddbl+
                    List of databse link
    名称     | Namespace Name |               Spec
-------------+----------------+-------------------------------------
 link_ora_xe | halo           | CREATE DATABASE LINK link_ora_xe ...

跨库查询（通过 表名@链接名 访问远程表）：

oracle=# SELECT * FROM test_table@link_ora_xe WHERE age > 25 ORDER BY id;
 ID |  NAME   | AGE |     CREATED_AT
----+---------+-----+---------------------
  1 | Alice   |  30 | 2026-07-30 06:56:06
  3 | Charlie |  35 | 2026-07-30 06:56:06
  4 | David   |  28 | 2026-07-30 06:56:06
(3 行记录)

跨库插入：

oracle=# INSERT INTO test_table@link_ora_xe (id, name, age) VALUES (5, 'Jone', 18);
INSERT 0 1

跨库更新：

oracle=# UPDATE test_table@link_ora_xe SET age = 27 WHERE id = 3;
UPDATE 1

跨库删除：

oracle=# DELETE FROM test_table@link_ora_xe WHERE id = 3;
DELETE 1

PUBLIC DATABASE LINK：公共数据库链接可被其他用户（经授权）共享使用。创建语法与普通链接类似，加 PUBLIC 关键字；授权通过 GRANT USAGE ON FOREIGN SERVER 完成：

-- 创建公共链接
oracle=# CREATE PUBLIC DATABASE LINK publink_ora_xe
oracle-#   CONNECT TO halolink IDENTIFIED BY "oracle"
oracle-#   USING '//192.168.10.155:1522/XEPDB1';
CREATE DATABASE LINK

-- 授权给其他用户使用
oracle=# GRANT USAGE ON FOREIGN SERVER publink_ora_xe_public TO test;
GRANT

说明：跨库更新与删除操作依赖远程表的主键；远程表须定义主键，否则更新/删除会受限。删除链接使用 DROP DATABASE LINK 链接名 / DROP PUBLIC DATABASE LINK 链接名。

外连接符（+）​

MINUS​

DECODE​

ROWNUM​

NULL 与空串等价​

IN 特别语法​

序列​

SYSDATE​

DUAL 伪表​

别名 UPDATE​

无别名子查询​

MERGE​

CTE（WITH 子句）​

START WITH / CONNECT BY（层级查询）​

COUNT(DISTINCT ...) OVER(PARTITION BY ...)​

数值格式化​

输出字段名大写​

日期类型​

DBLINK​