一、测试环境
数据库版本：KingbaseES V009R002C013（Oracle 兼容模式）
客户端工具：Navicat/CRT
操作系统：Linux x86_64
测试账号：test
字符集：UTF8
二、测试目标
验证金仓数据库对 Oracle 常用特性的兼容性，包括：
1. Oracle 专有对象：dual、rownum、sequence、rowid
2. Oracle 常用函数：decode、nvl、instr、substr
3. 典型语法结构：connect by（层级查询）、merge（合并语句）
4. PL/SQL 兼容性：存储过程、异常处理、触发器
5. 日期函数、空值处理、事务控制和复杂查询
三、测试内容与结果
1. SQL 语法兼容性
1.1 INTERVAL 表达式
测试目标：验证日期加间隔功能
SELECT CURRENT_DATE + INTERVAL '7' DAY AS next_week FROM DUAL;
测试数据:
| oracle | Kingbase |
|---|---|
| 2025-09-30 | 2025-09-30 |
预期结果:符合预期，KingbaseES 与 Oracle 均支持
1.2 WITH 子句（CTE）
测试目标：验证公共表表达式功能
WITH dept_count AS (
SELECT deptno, COUNT(*) AS cnt
FROM emp
GROUP BY deptno
)
SELECT deptno, cnt
FROM dept_count
WHERE cnt > 2;
测试数据:
oracle：
DEPTNO CNT
---------- ----------
30 5
20 3
50 3
Kingbase：
deptno | cnt
--------+-----
30 | 5
20 | 3
50 | 3
预期结果:符合预期，KingbaseES 与 Oracle 均支持
1.3 MERGE INTO
测试目标：验证合并语法
MERGE INTO dept d
USING (SELECT 60 AS deptno, 'HR' AS dname FROM dual) s
ON (d.deptno = s.deptno)
WHEN MATCHED THEN
UPDATE SET d.dname = s.dname
WHEN NOT MATCHED THEN
INSERT (deptno, dname, loc)
VALUES (s.deptno, s.dname, 'BEIJING');
测试数据:
| oracle | Kingbase |
|---|---|
| MERGE 1 | MERGE 1 |
预期结果:符合预期，KingbaseES 与 Oracle 均支持
1.4 LATERAL 子查询
测试目标：验证横向连接查询
SELECT d.deptno, e.ename
FROM dept d
LEFT JOIN (
SELECT ename, deptno
FROM (
SELECT ename, deptno,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno) AS rn
FROM emp
) t
WHERE rn = 1
) e ON d.deptno = e.deptno;
测试数据:
oracle：
DEPTNO ENAME
---------- ----------
10 CLARK
20 SMITH
30 ALLEN
50 DAVID
40
60
Kingbase：
deptno | ename
--------+-------
10 | CLARK
20 | SMITH
30 | ALLEN
40 |
50 | DAVID
60 |
预期结果:符合预期
2. PL/SQL 兼容性
2.1 存储过程 + IF 判断
测试目标：验证存储过程和条件操作
-- Oracle 版本
CREATE OR REPLACE PROCEDURE raise_salary(p_empno INT, p_pct NUMBER) AS
BEGIN
UPDATE emp
SET sal = sal * (1 + p_pct/100)
WHERE empno = p_empno;
END;
/
-- 调用
BEGIN
raise_salary(7369, 10);
END;
/
-- Kingbase / PostgreSQL 版本
CREATE OR REPLACE PROCEDURE raise_salary(p_empno INT, p_pct NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE emp
SET sal = sal * (1 + p_pct/100)
WHERE empno = p_empno;
END;
$$;
-- 调用
CALL raise_salary(7369, 10);
测试数据:
oracle：
SQL> select empno,ename,sal from emp where empno=7369;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 880
Kingbase：
test=# select empno,ename,sal from emp where empno=7369;
empno | ename | sal
-------+-------+--------
7369 | SMITH | 880.00
(1 row)
预期结果:基本符合
2.2 触发器
测试目标：验证行级触发器
CREATE OR REPLACE TRIGGER trg_orders_before
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
IF :NEW.amount > 10000 THEN
:NEW.status := 'REVIEW';
END IF;
END;
/
INSERT INTO orders(order_id, empno, amount) VALUES (10001, 7369, 20000);
测试数据:
oracle：
ORDER_ID EMPNO ORDER_DATE AMOUNT STATUS
---------- ---------- --------------- ---------- ----------
1 7369 01-SEP-25 2000 NEW
2 7499 02-SEP-25 5000 NEW
3 7521 03-SEP-25 3000 NEW
4 8001 04-SEP-25 7000 NEW
5 8002 05-SEP-25 6000 NEW
6 8003 06-SEP-25 8000 NEW
10001 7369 24-SEP-25 20000 REVIEW
Kingbase：
CREATE OR REPLACE TRIGGER trg_orders_before
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
IF :NEW.amount > 10000 THEN
:NEW.status := 'REVIEW'
> ERROR: 语法错误 在 "BEGIN" 或附近的
LINE 4: BEGIN
^
> 查询时间: 0.025s
预期结果:与预期不符，需要改写
3. 系统函数兼容性
3.1 NVL & DECODE
测试目标：验证空值处理和条件判断函数
SELECT ename,
NVL(comm, 0) AS comm_value,
DECODE(deptno, 10, 'ACCOUNTING', 20, 'RESEARCH', 'OTHER') AS dept_name
FROM emp;
测试数据:
oracle：
ENAME COMM_VALUE DEPT_NAME
---------- ---------- ------------------------------
SMITH 0 RESEARCH
ALLEN 300 OTHER
WARD 500 OTHER
JONES 0 RESEARCH
BLAKE 0 OTHER
CLARK 0 ACCOUNTING
JAMES 0 OTHER
TURNER 0 OTHER
FORD 0 RESEARCH
MILLER 0 ACCOUNTING
DAVID 0 OTHER
LINDA 0 OTHER
RAY 0 OTHER
13 rows selected.
Kingbase：
ename | comm_value | dept_name
--------+------------+------------
ALLEN | 300.00 | OTHER
WARD | 500.00 | OTHER
JONES | 0 | RESEARCH
BLAKE | 0 | OTHER
CLARK | 0 | ACCOUNTING
JAMES | 0 | OTHER
TURNER | 0.00 | OTHER
FORD | 0 | RESEARCH
MILLER | 0 | ACCOUNTING
DAVID | 0 | OTHER
LINDA | 0 | OTHER
RAY | 0 | OTHER
SMITH | 0 | RESEARCH
(13 rows)
预期结果:符合预期
3.2 TO_CHAR 日期格式化
测试目标：验证日期格式化
SELECT ename,
TO_CHAR(hiredate,'YYYY-MM-DD') AS hire_dt
FROM emp
WHERE deptno = 20;
测试数据:
oracle：
ENAME HIRE_DT
---------- ------------------------------
SMITH 1980-12-17
JONES 1981-04-02
FORD 1981-12-03
Kingbase：
ename | hire_dt
-------+------------
JONES | 1981-04-02
FORD | 1981-12-03
SMITH | 1980-12-17
(3 rows)
预期结果:符合预期
4. 事务与控制语句
4.1 SAVEPOINT & ROLLBACK TO
测试目标：验证事务回滚
BEGIN;
UPDATE emp SET sal = sal + 100 WHERE empno = 7369;
SAVEPOINT sp1;
UPDATE emp SET sal = sal + 200 WHERE empno = 7499;
ROLLBACK TO sp1;
COMMIT;
测试数据：
oracle：
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- -------------------- ---------- --------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 968 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7900 JAMES CLERK 7698 21-JUN-81 950 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 21-JAN-82 1300 10
8001 DAVID DEVELOPER 7698 15-MAR-82 4200 50
8002 LINDA DEVELOPER 7698 15-MAR-82 4300 50
8003 RAY DEVELOPER 7698 15-MAR-82 4100 50
13 rows selected.
SQL> UPDATE emp SET sal = sal + 100 WHERE empno = 7369;
1 row updated.
SQL> SAVEPOINT sp1;
Savepoint created.
SQL> UPDATE emp SET sal = sal + 200 WHERE empno = 7499;
1 row updated.
SQL> ROLLBACK TO sp1;
Rollback complete.
SQL> COMMIT;
Commit complete.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- -------------------- ---------- --------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1068 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7900 JAMES CLERK 7698 21-JUN-81 950 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 21-JAN-82 1300 10
8001 DAVID DEVELOPER 7698 15-MAR-82 4200 50
8002 LINDA DEVELOPER 7698 15-MAR-82 4300 50
8003 RAY DEVELOPER 7698 15-MAR-82 4100 50
13 rows selected.
Kingbase：
test=# select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+---------+--------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | | 20
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10
7900 | JAMES | CLERK | 7698 | 1981-06-21 00:00:00 | 950.00 | | 30
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-21 00:00:00 | 1300.00 | | 10
8001 | DAVID | DEVELOPER | 7698 | 1982-03-15 00:00:00 | 4200.00 | | 50
8002 | LINDA | DEVELOPER | 7698 | 1982-03-15 00:00:00 | 4300.00 | | 50
8003 | RAY | DEVELOPER | 7698 | 1982-03-15 00:00:00 | 4100.00 | | 50
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 980.00 | | 20
(13 rows)
test=# BEGIN;
BEGIN
test=# UPDATE emp SET sal = sal + 100 WHERE empno = 7369;
UPDATE 1
test=# SAVEPOINT sp1;
SAVEPOINT
test=# UPDATE emp SET sal = sal + 200 WHERE empno = 7499;
UPDATE 1
test=# ROLLBACK TO sp1;
ROLLBACK
test=# COMMIT;
COMMIT
test=# select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+---------+--------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | | 20
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10
7900 | JAMES | CLERK | 7698 | 1981-06-21 00:00:00 | 950.00 | | 30
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-21 00:00:00 | 1300.00 | | 10
8001 | DAVID | DEVELOPER | 7698 | 1982-03-15 00:00:00 | 4200.00 | | 50
8002 | LINDA | DEVELOPER | 7698 | 1982-03-15 00:00:00 | 4300.00 | | 50
8003 | RAY | DEVELOPER | 7698 | 1982-03-15 00:00:00 | 4100.00 | | 50
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 1080.00 | | 20
(13 rows)
测试数据：emp 表
预期结果：7369 薪资 +100，7499 薪资不变
实际结果：符合预期
4.2 FLASHBACK 模拟
测试目标：验证历史数据查询
SELECT * FROM emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE empno = 7369;
测试数据:
oracle：
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- -------------------- ---------- --------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1068 20
7369 SMITH CLERK 7902 17-DEC-80 968 20
Kingbase：
test=# SELECT * FROM emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
test-# WHERE empno = 7369;
ERROR: 无法获得提交时间戳
HINT: 确保在服务器上设置了配置参数 "track_commit_timestamp"。
test=# SHOW track_commit_timestamp;
track_commit_timestamp
------------------------
off
(1 row)
---将track_commit_timestamp设置为on
test-# WHERE empno = 7369;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-------+------+---------------------+---------+------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 1080.00 | | 20
(1 row)
测试数据：emp 表
预期结果：返回员工 7369 历史版本
实际结果：符合预期
5. 复杂查询与扩展
5.1 窗口函数
测试目标：验证窗口函数排序与排名
SELECT deptno, ename, sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rank_in_dept
FROM emp;
测试数据:
oracle：
DEPTNO ENAME SAL RANK_IN_DEPT
---------- ---------- ---------- ------------
10 CLARK 2450 1
10 MILLER 1300 2
20 FORD 3000 1
20 JONES 2975 2
20 SMITH 1068 3
30 BLAKE 2850 1
30 ALLEN 1600 2
30 TURNER 1500 3
30 WARD 1250 4
30 JAMES 950 5
50 LINDA 4300 1
50 DAVID 4200 2
50 RAY 4100 3
13 rows selected.
Kingbase：
deptno | ename | sal | rank_in_dept
--------+--------+---------+--------------
10 | CLARK | 2450.00 | 1
10 | MILLER | 1300.00 | 2
20 | FORD | 3000.00 | 1
20 | JONES | 2975.00 | 2
20 | SMITH | 1080.00 | 3
30 | BLAKE | 2850.00 | 1
30 | ALLEN | 1600.00 | 2
30 | TURNER | 1500.00 | 3
30 | WARD | 1250.00 | 4
30 | JAMES | 950.00 | 5
50 | LINDA | 4300.00 | 1
50 | DAVID | 4200.00 | 2
50 | RAY | 4100.00 | 3
(13 rows)
测试数据：emp 表
预期结果：每部门员工按工资排名
实际结果：符合预期
5.2 多表关联查询
测试目标：验证多表联合查询
SQL：
SELECT e.ename, d.dname, p.pname, o.amount
FROM emp e
JOIN dept d ON e.deptno = d.deptno
LEFT JOIN projects p ON d.deptno = p.deptno
LEFT JOIN orders o ON e.empno = o.empno
ORDER BY e.ename;
测试数据:
oracle：
ENAME DNAME PNAME AMOUNT
ALLEN SALES Project C 5000
BLAKE SALES Project C
CLARK ACCOUNTING Project A
DAVID IT Project D 7000
FORD RESEARCH Project B
JAMES SALES Project C
JONES RESEARCH Project B
LINDA IT Project D 6000
MILLER ACCOUNTING Project A
RAY IT Project D 8000
SMITH RESEARCH Project B 2000
SMITH RESEARCH Project B 20000
TURNER SALES Project C
WARD SALES Project C 3000
14 rows selected.
Kingbase：
```language
ename | dname | pname | amount
--------+------------+-----------+---------
ALLEN | SALES | Project C | 5000.00
BLAKE | SALES | Project C |
CLARK | ACCOUNTING | Project A |
DAVID | IT | Project D | 7000.00
FORD | RESEARCH | Project B |
JAMES | SALES | Project C |
JONES | RESEARCH | Project B |
LINDA | IT | Project D | 6000.00
MILLER | ACCOUNTING | Project A |
RAY | IT | Project D | 8000.00
SMITH | RESEARCH | Project B | 2000.00
TURNER | SALES | Project C |
WARD | SALES | Project C | 3000.00
(13 rows)
测试数据：emp、dept、projects、orders
预期结果：返回员工-部门-项目-订单信息
实际结果：符合预期
四、测试总结
本次测试在 KingbaseES V009R002C013（Oracle 兼容模式） 上，对 Oracle 常用特性进行了系统性验证，主要结论如下：
1. SQL 语法层面
• INTERVAL、WITH（CTE）、MERGE INTO、窗口函数、多表关联等语法均能正确执行，结果与 Oracle 基本一致。
• 分析型查询（如 LATERAL、窗口函数）在输出顺序上略有差异，但不影响结果正确性。
2. PL/SQL 兼容性
• 存储过程的创建与调用兼容性较好，仅需注意参数类型与调用方式上的语法差异（CALL vs BEGIN … END;）。
• 行级触发器 语法不完全兼容，Oracle 语法的 BEGIN … END; 块在 Kingbase 中报错，需要改写为符合 PostgreSQL/Kingbase 的 PL/pgSQL 格式。
3. 系统函数
• NVL、DECODE、TO_CHAR 等常用函数均能正确使用，兼容性良好。
4. 事务控制
• SAVEPOINT、ROLLBACK TO、COMMIT 测试结果符合预期，与 Oracle 行为一致。
5. 闪回查询
• 通过 VERSIONS BETWEEN 能部分模拟 Oracle Flashback 功能，但需启用 track_commit_timestamp 参数才能正常使用。相比 Oracle，功能有限，仅能追溯到最近版本记录。
6. 综合表现
• 兼容性较好：常见查询语法、函数、事务控制均与 Oracle 保持一致。
• 存在差异：PL/SQL 触发器语法、闪回功能的完整性存在差异，需要根据 Kingbase 特性进行适配与改写。
• 整体结论：KingbaseES 在 Oracle 兼容模式下，能够较好支撑常见 Oracle 应用的迁移，但对于依赖复杂 PL/SQL 触发器、Flashback 等特性的系统，仍需额外改造。
作者：Digital Observer（施嘉伟）
Oracle ACE Pro
PostgreSQL ACE Partner
Oracle OCM、KCM、PGCM、DB2 、MySQL OCP、PCTP、PCSD、OCI、PolarDB技术专家、达梦师资认证，从业11年+
ITPUB认证专家、崖山YVP、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师
公众号/墨天轮/金仓社区/IF Club：Digital Observer；CSDN/PGfans：施嘉伟；ITPUB：sjw1933
