-- Oracle 原代码SELECT TRUNC(SYSDATE, 'YYYY') AS year_start,
TRUNC(SYSDATE, 'HH24') AS hour_start,
MONTHS_BETWEEN(SYSDATE, TO_DATE('2025-01-01','YYYY-MM-DD')) AS month_diff
FROM DUAL;
-- KingbaseES 适配后代码SELECT date_trunc('year', CURRENT_DATE) AS year_start,
date_trunc('hour', CURRENT_DATE) AS hour_start,
MONTHS_BETWEEN(CURRENT_DATE, TO_DATE('2025-01-01','YYYY-MM-DD')) AS month_diff
FROM DUAL;
2. 字符与正则函数
功能描述
Oracle 语法
KingbaseES 适配语法
差异说明
字符串拼接
CONCAT('a', 'b', 'c')
concat('a', 'b', 'c')
KingbaseES 支持多参数,双参数用法兼容
字符转换(小写)
NLS_LOWER(...)
nls_lower(...)
支持指定 collation,参数格式微调
正则替换
REGEXP_REPLACE(...)
REGEXP_REPLACE(...)
核心语法兼容,match_param 参考 POSIX 标准
字符串位置查找
INSTR(...)
strpos(...) 或 position(...)
函数名不同,功能一致
3. 数值与聚合函数
功能描述
Oracle 语法
KingbaseES 适配语法
差异说明
立方根计算
无内置函数
cbrt(27)
KingbaseES 有专用函数
整数商计算
无内置函数
div(10, 3)
返回 3,效果同 TRUNC(10/3)
中位数计算
MEDIAN(col1)
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col1)
用百分位函数替代
集合聚合
COLLECT(col1)
array_agg(col1)
Oracle 返回嵌套表,KingbaseES 返回数组
代码示例:聚合函数适配
-- Oracle 原代码:计算中位数SELECT MEDIAN(salary) AS median_sal FROM employees;
-- KingbaseES 适配后代码SELECTPERCENTILE_CONT(0.5) WITHINGROUP (ORDERBY salary) AS median_sal FROM employees;
4. JSON 函数
KingbaseES 提供了更丰富的 JSON 处理函数,部分 Oracle 操作可直接适配:
-- Oracle 原代码SELECTJSON_OBJECT('id'VALUE1, 'name'VALUE'test') AS json_obj FROM DUAL;
-- KingbaseES 适配后代码SELECT json_build_object('id', 1, 'name', 'test') AS json_obj FROM DUAL;
-- KingbaseES 扩展用法:解析 JSON 数组SELECT json_array_elements('[{"id":1},{"id":2}]') AS json_elem FROM DUAL;
-- Oracle 原代码SELECT dept_id, dept_name, LEVEL, CONNECT_BY_ISLEAF AS is_leaf
FROM departments
STARTWITH parent_dept_id ISNULLCONNECTBY PRIOR dept_id = parent_dept_id;
-- KingbaseES 适配后代码SELECT dept_id, dept_name, LEVEL, CONNECT_BY_ISLEAF AS is_leaf
FROM departments
STARTWITH parent_dept_id ISNULLCONNECTBY PRIOR dept_id = parent_dept_id;
3. 不支持语法的替代方案
KingbaseES 不支持 Oracle 的 DELETE hint 语法。若需实现类似性能优化,可通过禁用索引或调整查询逻辑:
-- Oracle 原代码DELETE/*+ FULL(employees) */FROM employees WHERE hire_date <'2020-01-01';
-- KingbaseES 适配后代码ALTER INDEX idx_emp_hire_date DISABLE;
DELETEFROM employees WHERE hire_date <'2020-01-01';
ALTER INDEX idx_emp_hire_date ENABLE;
-- KingbaseES 适配后代码SELECT table_name, tablespace_name, num_rows FROM all_tables WHERE owner ='SCOTT';
SELECT sid, username, logon_time FROM v$session WHERE status ='ACTIVE';