-- 這條建表語(yǔ)句，Oracle和KingbaseES跑出來(lái)一模一樣
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)
);

-- 建表、插入、查詢，寫(xiě)法跟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展開(kāi)成表來(lái)查，做報(bào)表很好用
SELECT jt.name, jt.price
FROM product_catalog,
     JSON_TABLE(attributes, '$'
         COLUMNS (name VARCHAR2(100) PATH '$.name',
                  price NUMBER PATH '$.price')
     ) jt;

-- 壞設(shè)計(jì)：日期用字符串、金額也用字符串、狀態(tài)用數(shù)字
CREATE TABLE bad_order (
    order_date  VARCHAR2(20),   -- 該用DATE
    amount      VARCHAR2(20),   -- 該用NUMBER
    status      NUMBER          -- 只有幾個(gè)值，用CHAR(1)更清楚
);
-- 好設(shè)計(jì)
CREATE TABLE good_order (
    order_date  DATE,           -- 日期就用日期類型
    amount      NUMBER(12,2),   -- 金額就用數(shù)值類型
    status      CHAR(1)         -- 'Y'/'N' 比 1/0 直觀
);

-- 數(shù)字函數(shù)：26個(gè)全部兼容，行為一模一樣
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;
-- 字符函數(shù)：19個(gè)全部兼容
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;
-- 日期函數(shù)：22個(gè)全部兼容
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;
-- 聚集函數(shù)：30個(gè)全部兼容，包括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;
-- 分析函數(shù)：31個(gè)全部兼容，窗口查詢直接搬
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寫(xiě)法：不報(bào)錯(cuò)，返回包含空字符的字符串
SELECT 'A' || CHR(0) || 'B' FROM dual;
-- 遷移后報(bào)錯(cuò)！改寫(xiě)方案：用Tab（CHR(9)）或換行（CHR(10))替代
SELECT 'A' || CHR(9) || 'B' FROM dual;
-- 如果是做字符串分隔，也可以用不可見(jiàn)的Unit Separator
SELECT 'A' || CHR(31) || 'B' FROM dual;

-- Oracle寫(xiě)法：CONVERT(字符串, 目標(biāo)字符集, 源字符集)
SELECT CONVERT('測(cè)試文字', 'ZHS16GBK', 'AL32UTF8') FROM dual;
-- KingbaseES寫(xiě)法：第二三個(gè)參數(shù)順序要反過(guò)來(lái)！
SELECT CONVERT('測(cè)試文字', 'AL32UTF8', 'ZHS16GBK') FROM dual;

-- 這類SQL一定要用真實(shí)數(shù)據(jù)跑一遍，對(duì)比兩邊結(jié)果
SELECT REGEXP_COUNT('Hello World', 'hello', 1, 'i') FROM dual;
-- 涉及time類型的正則場(chǎng)景，差異更容易暴露
SELECT REGEXP_INSTR(time_col::text, '\d{2}:\d{2}') FROM schedule_table;

-- 不指定精度的話，兩邊返回的小數(shù)位數(shù)可能不一樣
SELECT CURRENT_TIMESTAMP FROM dual;
-- 保險(xiǎn)做法：顯式指定精度
SELECT CURRENT_TIMESTAMP(6) FROM dual;

-- 這個(gè)Oracle存儲(chǔ)過(guò)程，搬到KingbaseES上一行不改就能編譯運(yùn)行
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;
/

-- 查看哪些對(duì)象編譯失敗了
SELECT object_name, object_type, status
FROM user_objects
WHERE status = 'INVALID'
ORDER BY object_type;

-- 包規(guī)范：定義對(duì)外接口
CREATE OR REPLACE PACKAGE pkg_order AS
    MAX_ITEMS CONSTANT NUMBER := 100;
    -- 兩個(gè)同名函數(shù)，參數(shù)不同（重載）
    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;
/
-- 包體：實(shí)現(xiàn)具體邏輯
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
    -- 包初始化塊：首次被調(diào)用時(shí)自動(dòng)執(zhí)行
    SELECT COUNT(*) INTO g_cancel_count
    FROM orders WHERE status = 'CANCELLED';
END pkg_order;
/

-- 行級(jí)BEFORE觸發(fā)器：審計(jì)薪資變更
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觸發(fā)器：讓多表聯(lián)查的視圖也能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;
/
-- 啟用/禁用觸發(fā)器
ALTER TRIGGER trg_salary_audit DISABLE;
ALTER TRIGGER trg_salary_audit ENABLE;

-- 寫(xiě)法一：EXECUTE IMMEDIATE——大多數(shù)場(chǎng)景夠用
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;
/
-- 寫(xiě)法二：DBMS_SQL——列的類型和數(shù)量在編譯時(shí)不確定時(shí)用
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：批量刪除，一條語(yǔ)句干掉整個(gè)集合
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：批量查詢到集合里，省去來(lái)回切換上下文
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;
/

-- 寫(xiě)法一：傳統(tǒng)ROWNUM分頁(yè)（Oracle 8i就有了，老項(xiàng)目里最多）
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT emp_name, salary FROM employees ORDER BY salary DESC
    ) a WHERE ROWNUM <= 20
) WHERE rn > 10;
-- 寫(xiě)法二：12c+的OFFSET...FETCH（新項(xiàng)目常用）
SELECT emp_name, salary FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- MERGE：數(shù)據(jù)同步神器——有就更新，沒(méi)有就插入
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;

-- 畫(huà)組織架構(gòu)樹(shù)，完全兼容Oracle寫(xiě)法
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 *（字段多了浪費(fèi)帶寬，還會(huì)阻止覆蓋索引生效）
SELECT emp_id, emp_name, salary FROM employees WHERE dept_id = 10;
-- IN 子查詢改成 EXISTS（數(shù)據(jù)量大的時(shí)候差距很明顯）
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;
-- 索引列上別套函數(shù)（套了就用不上索引了）
-- 錯(cuò)誤：WHERE TRUNC(create_time) = DATE '2025-01-01'
-- 正確：
WHERE create_time >= DATE '2025-01-01'
  AND create_time <  DATE '2025-01-02';

-- 查出所有沒(méi)有索引的外鍵字段
SELECT
    c.table_name     AS "表名",
    c.constraint_name AS "外鍵名",
    cc.column_name    AS "外鍵列",
    '缺索引！建議補(bǔ)上' 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分區(qū)（最常見(jiàn)的方案）
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");
// 關(guān)鍵參數(shù)
config.setMaximumPoolSize(36);     // 每個(gè)CPU核心×10，別超過(guò)
config.setMinimumIdle(10);         // 最小空閑連接
config.setIdleTimeout(300000);     // 空閑超時(shí)5分鐘
config.setConnectionTimeout(5000); // 獲取連接超時(shí)5秒
config.setMaxLifetime(1800000);    // 連接最大存活30分鐘

# Oracle端導(dǎo)出
exp userid=hr/password@orcl OWNER=hr FILE=hr_backup.dmp LOG=hr_export.log
# KingbaseES端導(dǎo)入
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"

-- 驗(yàn)證一：表數(shù)量對(duì)不對(duì)
SELECT COUNT(*) AS table_count FROM user_tables;
-- 驗(yàn)證二：有沒(méi)有編譯失敗的對(duì)象（重點(diǎn)?。?
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;
-- 驗(yàn)證三：索引狀態(tài)
SELECT index_name, table_name, status
FROM user_indexes
WHERE status != 'VALID'
ORDER BY table_name;
-- 驗(yàn)證四：約束狀態(tài)
SELECT constraint_name, table_name, constraint_type, status
FROM user_constraints
WHERE status != 'ENABLED'
ORDER BY table_name;
-- 驗(yàn)證五：每張表的數(shù)據(jù)量（抽樣跟Oracle比一下）
SELECT table_name,
       (SELECT COUNT(*) FROM employees) AS spot_check  -- 換成你的表名
FROM user_tables
WHERE table_name = 'EMPLOYEES';

-- 重編譯單個(gè)對(duì)象
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;
-- 或者批量重編譯（生成重編譯語(yǔ)句）
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;