Oracle迁移到KingbaseES实战指南：语法差异、函数映射与避坑指南

-- 这条建表语句，oracle和kingbasees跑出来一模一样
create table orders (
    order_id      number(12) primary key,
    order_no      varchar2(50) not null,
    status        char(1),
    amount        number(12,2),
    discount      float,
    order_date    date,
    created_at    timestamp,
    delivery_ts   timestamp with time zone,
    local_ts      timestamp with local time zone,
    warranty      interval year to month,
    delivery_span interval day to second,
    description   clob,
    attachment    blob,
    raw_data      raw(500),
    ext_file      bfile,
    row_loc       rowid,
    extra_info    nvarchar2(500),
    tags          nchar(100)
);

-- 建表、插入、查询，写法跟oracle一致
create table product_catalog (
    id          number primary key,
    attributes  json,
    metadata    jsonb
);
insert into product_catalog values (
    1,
    '{"name": "笔记本电脑", "price": 5999, "tags": ["电子", "办公"]}',
    '{"source": "官方"}'
);
-- 提取json字段
select json_value(attributes, '$.name') as product_name from product_catalog;
-- json_table：把json展开成表来查，做报表很好用
select jt.name, jt.price
from product_catalog,
     json_table(attributes, '$'
         columns (name varchar2(100) path '$.name',
                  price number path '$.price')
     ) jt;

-- 坏设计：日期用字符串、金额也用字符串、状态用数字
create table bad_order (
    order_date  varchar2(20),   -- 该用date
    amount      varchar2(20),   -- 该用number
    status      number          -- 只有几个值，用char(1)更清楚
);
-- 好设计
create table good_order (
    order_date  date,           -- 日期就用日期类型
    amount      number(12,2),   -- 金额就用数值类型
    status      char(1)         -- 'y'/'n' 比 1/0 直观
);

-- 数字函数：26个全部兼容，行为一模一样
select
    abs(-15)            as abs_val,      -- 15
    ceil(4.3)           as ceil_val,     -- 5
    floor(4.7)          as floor_val,    -- 4
    round(3.1415, 2)    as round_val,    -- 3.14
    trunc(3.1415, 2)    as trunc_val,    -- 3.14
    mod(10, 3)          as mod_val,      -- 1
    power(2, 10)        as power_val,    -- 1024
    sqrt(144)           as sqrt_val,     -- 12
    sign(-5)            as sign_val      -- -1
from dual;
-- 字符函数：19个全部兼容
select
    upper('hello')        as upper_str,    -- hello
    lower('world')        as lower_str,    -- world
    initcap('hello you')  as initcap_str,  -- hello you
    substr('abcdef',2,3)  as sub_str,      -- bcd
    instr('abcdef','cd')  as pos,          -- 3
    lpad('123', 8, '0')   as padded,       -- 00000123
    trim('  hi  ')        as trimmed,      -- hi
    replace('hello','l','l') as replaced   -- hello
from dual;
-- 日期函数：22个全部兼容
select
    sysdate                          as now,
    add_months(date '2025-01-15', 3) as after_3m,
    last_day(date '2025-02-10')      as feb_last,
    next_day(date '2025-03-15', 'monday') as next_mon,
    trunc(sysdate, 'month')          as month_start,
    to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') as formatted
from dual;
-- 聚集函数：30个全部兼容，包括listagg
select
    department_id,
    count(*)   as emp_count,
    avg(salary) as avg_sal,
    sum(salary) as total_sal,
    listagg(emp_name, ',') within group (order by emp_id) as name_list
from employees
group by department_id;
-- 分析函数：31个全部兼容，窗口查询直接搬
select
    emp_name,
    salary,
    row_number() over (order by salary desc) as rn,
    rank()       over (order by salary desc) as rnk,
    dense_rank() over (order by salary desc) as d_rnk,
    lag(salary, 1) over (order by salary)   as prev_sal,
    lead(salary, 1) over (order by salary)  as next_sal
from employees;

-- oracle写法：不报错，返回包含空字符的字符串
select 'a' || chr(0) || 'b' from dual;
-- 迁移后报错！改写方案：用tab（chr(9)）或换行（chr(10))替代
select 'a' || chr(9) || 'b' from dual;
-- 如果是做字符串分隔，也可以用不可见的unit separator
select 'a' || chr(31) || 'b' from dual;

-- oracle写法：convert(字符串, 目标字符集, 源字符集)
select convert('测试文字', 'zhs16gbk', 'al32utf8') from dual;
-- kingbasees写法：第二三个参数顺序要反过来！
select convert('测试文字', 'al32utf8', 'zhs16gbk') from dual;

-- 这类sql一定要用真实数据跑一遍，对比两边结果
select regexp_count('hello world', 'hello', 1, 'i') from dual;
-- 涉及time类型的正则场景，差异更容易暴露
select regexp_instr(time_col::text, '\d{2}:\d{2}') from schedule_table;

-- 不指定精度的话，两边返回的小数位数可能不一样
select current_timestamp from dual;
-- 保险做法：显式指定精度
select current_timestamp(6) from dual;

-- 这个oracle存储过程，搬到kingbasees上一行不改就能编译运行
create or replace procedure calc_monthly_report(
    p_year    in  number,
    p_month   in  number,
    p_status  out varchar2
) as
    v_count number;
begin
    execute immediate 'truncate table tmp_report';
    insert into tmp_report
    select department_id, sum(amount), count(*)
    from transactions
    where extract(year from trans_date) = p_year
      and extract(month from trans_date) = p_month
    group by department_id;
    select count(*) into v_count from tmp_report;
    if v_count = 0 then
        p_status := 'no_data';
    else
        p_status := 'success';
    end if;
exception
    when others then
        p_status := 'error: ' || sqlerrm;
        rollback;
end calc_monthly_report;
/

-- 查看哪些对象编译失败了
select object_name, object_type, status
from user_objects
where status = 'invalid'
order by object_type;

-- 包规范：定义对外接口
create or replace package pkg_order as
    max_items constant number := 100;
    -- 两个同名函数，参数不同（重载）
    function get_total(p_order_id number) return number;
    function get_total(p_order_id number, p_include_tax number) return number;
    procedure cancel_order(p_order_id number);
end pkg_order;
/
-- 包体：实现具体逻辑
create or replace package body pkg_order as
    g_cancel_count number := 0;
    -- 不含税版本
    function get_total(p_order_id number) return number as
        v_total number;
    begin
        select sum(quantity * unit_price) into v_total
        from order_items where order_id = p_order_id;
        return nvl(v_total, 0);
    end get_total;
    -- 含税版本（重载）
    function get_total(p_order_id number, p_include_tax number)
        return number as
    begin
        if p_include_tax = 1 then
            return get_total(p_order_id) * 1.13;
        else
            return get_total(p_order_id);
        end if;
    end get_total;
    procedure cancel_order(p_order_id number) as
    begin
        update orders set status = 'cancelled'
        where order_id = p_order_id;
        g_cancel_count := g_cancel_count + 1;
        commit;
    end cancel_order;
begin
    -- 包初始化块：首次被调用时自动执行
    select count(*) into g_cancel_count
    from orders where status = 'cancelled';
end pkg_order;
/

-- 行级before触发器：审计薪资变更
create or replace trigger trg_salary_audit
before update of salary on employees
for each row
begin
    if :new.salary != :old.salary then
        insert into salary_audit_log(
            emp_id, old_salary, new_salary, changed_by, changed_at
        ) values(
            :new.emp_id, :old.salary, :new.salary, user, sysdate
        );
    end if;
end;
/
-- instead of触发器：让多表联查的视图也能update
create or replace view emp_detail as
select e.emp_id, e.emp_name, e.salary, d.dept_name
from employees e, departments d
where e.dept_id = d.dept_id;
create or replace trigger trg_emp_detail_update
instead of update on emp_detail
for each row
begin
    update employees set
        emp_name = :new.emp_name,
        salary   = :new.salary
    where emp_id = :new.emp_id;
end;
/
-- 启用/禁用触发器
alter trigger trg_salary_audit disable;
alter trigger trg_salary_audit enable;

-- 写法一：execute immediate——大多数场景够用
create or replace procedure dynamic_count(
    p_table_name varchar2
) as
    v_count number;
begin
    execute immediate 'select count(*) from ' || p_table_name
    into v_count;
    dbms_output.put_line(p_table_name || ' 共 ' || v_count || ' 行');
end;
/
-- 写法二：dbms_sql——列的类型和数量在编译时不确定时用
declare
    v_cursor integer;
    v_name   varchar2(100);
    v_sal    number;
begin
    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor,
        'select emp_name, salary from employees where salary > :1',
        dbms_sql.native);
    dbms_sql.bind_variable(v_cursor, ':1', 10000);
    dbms_sql.define_column(v_cursor, 1, v_name, 100);
    dbms_sql.define_column(v_cursor, 2, v_sal);
    dbms_sql.execute(v_cursor);
    loop
        exit when dbms_sql.fetch_rows(v_cursor) = 0;
        dbms_sql.column_value(v_cursor, 1, v_name);
        dbms_sql.column_value(v_cursor, 2, v_sal);
        dbms_output.put_line(v_name || ': ' || v_sal);
    end loop;
    dbms_sql.close_cursor(v_cursor);
end;
/

-- forall：批量删除，一条语句干掉整个集合
declare
    type id_list is table of number;
    v_ids id_list := id_list(101, 102, 103, 104, 105);
begin
    forall i in v_ids.first .. v_ids.last
        delete from temp_data where id = v_ids(i);
    dbms_output.put_line('删除了 ' || sql%rowcount || ' 行');
end;
/
-- bulk collect：批量查询到集合里，省去来回切换上下文
declare
    type emp_array is table of employees%rowtype;
    v_emps emp_array;
begin
    select * bulk collect into v_emps
    from employees where department_id = 10;
    for i in v_emps.first .. v_emps.last loop
        dbms_output.put_line(v_emps(i).emp_name || ': ' || v_emps(i).salary);
    end loop;
end;
/

-- 写法一：传统rownum分页（oracle 8i就有了，老项目里最多）
select * from (
    select a.*, rownum rn from (
        select emp_name, salary from employees order by salary desc
    ) a where rownum <= 20
) where rn > 10;
-- 写法二：12c+的offset...fetch（新项目常用）
select emp_name, salary from employees
order by salary desc
offset 10 rows fetch next 10 rows only;

-- merge：数据同步神器——有就更新，没有就插入
merge into target t
using source s
on (t.id = s.id)
when matched then
    update set t.name = s.name, t.val = s.val
when not matched then
    insert (id, name, val) values (s.id, s.name, s.val);
-- insert all：一条select插入多张表
insert all
    into emp_active (emp_id, name, salary) values (emp_id, name, salary)
    into emp_audit  (emp_id, action, time)  values (emp_id, 'migrate', sysdate)
select emp_id, name, salary from employees where status = 'active';
-- insert returning：插入后返回自增id
insert into employees(emp_name, salary)
values ('新员工', 10000)
returning emp_id into :new_id;

-- 画组织架构树，完全兼容oracle写法
select
    level,
    lpad(' ', 2 * (level - 1)) || emp_name as org_chart,
    connect_by_isleaf as is_leaf,
    sys_connect_by_path(emp_name, ' / ') as full_path
from employees
start with manager_id is null
connect by prior emp_id = manager_id
order siblings by emp_name;

-- 不要 select *（字段多了浪费带宽，还会阻止覆盖索引生效）
select emp_id, emp_name, salary from employees where dept_id = 10;
-- in 子查询改成 exists（数据量大的时候差距很明显）
select * from orders o where exists (
    select 1 from vip_customers v where v.customer_id = o.customer_id
);
-- union 改 union all（不需要去重的话，能省掉排序这一步）
select name from table_a
union all
select name from table_b;
-- 索引列上别套函数（套了就用不上索引了）
-- 错误：where trunc(create_time) = date '2025-01-01'
-- 正确：
where create_time >= date '2025-01-01'
  and create_time <  date '2025-01-02';

-- 查出所有没有索引的外键字段
select
    c.table_name     as "表名",
    c.constraint_name as "外键名",
    cc.column_name    as "外键列",
    '缺索引！建议补上' as "建议"
from user_constraints c
join user_cons_columns cc
    on c.constraint_name = cc.constraint_name
left join user_ind_columns ic
    on cc.table_name = ic.table_name
    and cc.column_name = ic.column_name
where c.constraint_type = 'r'
    and ic.index_name is null
order by c.table_name;

create index idx_order_customer on orders(customer_id);

-- 按季度做range分区（最常见的方案）
create table order_logs (
    id          number,
    order_no    varchar2(50),
    amount      number(12,2),
    created_at  date
) partition by range (created_at) (
    partition p_2025_q1 values less than (date '2025-04-01'),
    partition p_2025_q2 values less than (date '2025-07-01'),
    partition p_2025_q3 values less than (date '2025-10-01'),
    partition p_2025_q4 values less than (date '2026-01-01'),
    partition p_max     values less than (maxvalue)  -- 兜底，必须有
);

// java连接池配置示例（hikaricp）
hikariconfig config = new hikariconfig();
config.setjdbcurl("jdbc:kingbase8://10.0.0.1:54321/mydb");
config.setdriverclassname("com.kingbase8.driver");
config.setusername("hr");
config.setpassword("password");
// 关键参数
config.setmaximumpoolsize(36);     // 每个cpu核心×10，别超过
config.setminimumidle(10);         // 最小空闲连接
config.setidletimeout(300000);     // 空闲超时5分钟
config.setconnectiontimeout(5000); // 获取连接超时5秒
config.setmaxlifetime(1800000);    // 连接最大存活30分钟

# oracle端导出
exp userid=hr/password@orcl owner=hr file=hr_backup.dmp log=hr_export.log
# kingbasees端导入
imp userid=hr/password@kingbase file=hr_backup.dmp log=hr_import.log fromuser=hr touser=hr

sys_bulkload -i /data/import/employees.csv \
    -t employees \
    -d kingbase \
    -u hr \
    -o "type=csv" \
    -o "delimiter=," \
    -o "header=y"

-- 验证一：表数量对不对
select count(*) as table_count from user_tables;
-- 验证二：有没有编译失败的对象（重点！）
select object_name, object_type, status
from user_objects
where status = 'invalid'
  and object_type in ('procedure', 'function', 'package', 'package body', 'trigger', 'view')
order by object_type, object_name;
-- 验证三：索引状态
select index_name, table_name, status
from user_indexes
where status != 'valid'
order by table_name;
-- 验证四：约束状态
select constraint_name, table_name, constraint_type, status
from user_constraints
where status != 'enabled'
order by table_name;
-- 验证五：每张表的数据量（抽样跟oracle比一下）
select table_name,
       (select count(*) from employees) as spot_check  -- 换成你的表名
from user_tables
where table_name = 'employees';

-- 重编译单个对象
alter package pkg_order compile;
alter package pkg_order compile body;
alter procedure calc_monthly_report compile;
alter trigger trg_salary_audit compile;
alter view emp_detail compile;
-- 或者批量重编译（生成重编译语句）
select 'alter ' ||
       case object_type
           when 'package body' then 'package'
           when 'type body' then 'type'
           else object_type
       end || ' ' || object_name || ' compile' ||
       case object_type
           when 'package body' then ' body'
           when 'type body' then ' body'
           else ''
       end || ';'
from user_objects
where status = 'invalid'
  and object_type in ('procedure', 'function', 'package', 'package body', 'trigger', 'view')
order by object_type;

// 改之前（oracle）
string url = "jdbc:oracle:thin:@10.0.0.1:1521:orcl";
string driver = "oracle.jdbc.oracledriver";
// 改之后（kingbasees）
string url = "jdbc:kingbase8://10.0.0.1:54321/mydb";
string driver = "com.kingbase8.driver";
// 连接池完整示例（spring boot配置）
// application.yml
spring:
  datasource:
    url: jdbc:kingbase8://10.0.0.1:54321/mydb
    username: hr
    password: password
    driver-class-name: com.kingbase8.driver
    hikari:
      maximum-pool-size: 36
      minimum-idle: 10

# oracle
driver={oracle in oradb11g_home1};server=10.0.0.1;port=1521;dbq=orcl;
# kingbasees
driver={kingbasees 8 odbc driver};server=10.0.0.1;port=54321;database=mydb;