用户体验 | Oracle与崖山数据库多种类型兼容性对比验证测试

YashanDB 2024-01-26

804

前言

YashanDB个人版开放后，陆续获得一大波「产品体验官」的关注和体验反馈，小崖在此把优秀的用户投稿文章分享给大家~今天分享的用户文章是《Oracle与崖山数据库多种类型兼容性对比验证测试》（作者：尚雷），欢迎大家一起交流。

以下为评测全文：

最近有款国产数据库真的很火，火到什么程度呢，很多数据库行业的大佬都在多种场合讨论它、墨天轮社区里的很多小伙伴也在群里分享使用的感受、很多微信公众号也都是它的新闻。

是哪款数据库能这么火呢，当然是崖山数据库了，对，就是这款名叫YashanDB的数据库。

我之前曾有幸参加了崖山数据库的YCA认证培训，并通过了YCA的认证。

自从那以后，也一直非常关心崖山数据库的发展。

做为一名DBA，在进行去O国产数据库选型时，最关注什么，是关注这款数据库如何安装部署吗，还是关注它的界面多么酷炫，肯定最为关注的就是和Oracle数据库的兼容性，能否更适配，业务迁移是否代码改动量最小，迁移的难度更低。

上次参加YCA培训，算是初探崖山数据库容貌，正好公司最近在进行去O国产化选型，让我考察一些国产数据库。趁着最近崖山数据库举办活动，我也更希望能更深入的去了解崖山数据库，作为国产去O的一个数据库选型参考。

今天我花了半天的时间，从公司一套核心Oracle数据库选择了不同的数据类型在崖山数据库上进行对比兼容性测试。

本次测试主要从建表、存储过程、函数、触发器、视图、序列、Package等多个维度考察两者的兼容性。

本次测试其实相对复杂，完成这些测试，涉及的表有几十张，也涉及多个函数。接下来，我将一一列举。

兼容测试

由于所选取的都是生产数据库代码，为安全，对相关表名和字段进行了脱敏和部分省略，所选取的对象也都是精心挑选的。

2.1 建表测试

-- 建表语句
SQL> CREATE TABLE yashan.COR_xxx_xxx_MAINT_xxx
   2 (
   3   COM_ID                 NUMBER(10)             NOT NULL,
   4   OFFER_ID               NUMBER(10)             NOT NULL,
   5   xxxx_CODE              NUMBER(10)             NOT NULL,
   6   xxxx_LEVEL             NUMBER(1)              NOT NULL,
   7   OTHER_xxxx_CODE        VARCHAR2(255 CHAR)         NULL,
   8   INFO_FULL_FLAG         VARCHAR2(1 CHAR)       NOT NULL,
   9   OFFER_STATUS           VARCHAR2(1 CHAR)       NOT NULL,
  10   xxxx                   NUMBER(4)              DEFAULT 0                     NOT NULL,
  11   xxxx                   VARCHAR2(255 CHAR)     NOT NULL,
  12   OFFER_TYPE             VARCHAR2(1 CHAR)       NOT NULL,
  13   HS_CODE                VARCHAR2(50 CHAR)          NULL,
  14   xxxxx_PRODS            VARCHAR2(255 CHAR)         NULL,
  15   xxxxx_DESCRIPT         VARCHAR2(4000 CHAR)    NOT NULL,
  16   OFFER_KEYWORD          VARCHAR2(1000 CHAR)    NOT NULL,
  17   xxxxx_xxxx_FLAG       VARCHAR2(1 CHAR)       DEFAULT '0'                       NULL,
  18   PIC_WIDTH              NUMBER(4)              DEFAULT 0                     NOT NULL,
  19   PIC_HEIGHT             NUMBER(4)              DEFAULT 0                     NOT NULL,
  20   OFFER_OPEN             VARCHAR2(1 CHAR)       DEFAULT '1'                   NOT NULL,
  21   MAX_REPLY              NUMBER(4)                  NULL,
  22   xxxx_xxxx_TIME        DATE                   DEFAULT TO_DATE('1900-01-01','YYYY-MM-DD HH24:MI:SS') NOT NULL,
  23   RETURN_ADVISE          VARCHAR2(4000 CHAR)        NULL,
  24   ADD_TIME               DATE                   DEFAULT TO_DATE('1900-01-01','YYYY-MM-DD HH24:MI:SS') NOT NULL,
  25   ADDER_NO               VARCHAR2(50 CHAR)      DEFAULT '0'                   NOT NULL,
  26   ADDER_NAME             VARCHAR2(50 CHAR)      DEFAULT ' '                   NOT NULL,
  27   LAST_xxxx_TIME        DATE                   DEFAULT TO_DATE('1900-01-01','YYYY-MM-DD HH24:MI:SS')     NULL,
  28   CHECKER_NO             VARCHAR2(50 CHAR)          NULL,
  29   CHECKER_NAME           VARCHAR2(50 CHAR)          NULL,
  30   xxxx_TIME               TIMESTAMP(6) WITH TIME ZONE DEFAULT TO_DATE('1900-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') NOT NULL,
  31   SYNC_FLAG              VARCHAR2(1 CHAR)       DEFAULT 'F'                   NOT NULL,
  32   ADDER_ROLE             VARCHAR2(1 CHAR)           NULL,
  33   RETURN_ADVISE_CN       VARCHAR2(4000 CHAR)        NULL,
  34   TABLE_xxxx_FLAG       VARCHAR2(1 CHAR)           NULL,
  35   INFO_xxxxx_TYPE       VARCHAR2(1 CHAR)           NULL,
  36   CONSTRAINT PK_COxxxxxxxxCHK
  37   PRIMARY KEY
  38   (OFFER_ID)
  39   ENABLE VALIDATE
  40 )
  41 NOPARALLEL;


[30:39]YAS-04209 unexpected word WITH

--- 建表语句
SQL> CREATE TABLE yashan.Fxxxxx_TABLE
   2 (
   3   COM_ID       NUMBER(10)                       DEFAULT 0                     NOT NULL,
   4   OFFER_ID     NUMBER(10)                       DEFAULT 0                     NOT NULL,
   5   xxxxx_TABLE  CLOB                                 NULL,
   6   xxx_TIME     DATE      DEFAULT TO_DATE ('1900-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') NOT NULL,
   7   CONSTRAINT PK_xxxxxxxTABLE
   8   PRIMARY KEY
   9   (OFFER_ID)
  10   ENABLE VALIDATE
  11 )
  12 LOB (OFFER_TABLE) STORE AS (
  13   TABLESPACE  users
  14   ENABLE      STORAGE IN ROW
  15   CHUNK       32768
  16   PCTVERSION  10)
  17 NOPARALLEL;


[15:3]YAS-04209 unexpected word CHUNK

这套Oracle生产库有很多时间列字段，有不少字段采用TIMESTAMP(xxx) WITH TIME ZONE DEFAULT TO_DATE这种时间类型，另外也有不少LOB字段，使用32k大小。通过在崖山数据库上的建表情况，并查询了崖山数据库的官方文档，发现其不支持TIMESTAMP WITH TIME ZONE 这种类型，也不支持使用chunk，将Oracle建表语句进行改写，将TIMESTAMP(6) WITH TIME ZONE DEFAULT TO_DATE改写为 DATA DEFAULT TO_DATE，去掉chunk相关内容，在崖山数据库上创建成功。

2.2 序列测试

--- 代码如下
SQL> CREATE SEQUENCE yashan.S_xxxxxOG
   2   START WITH 2038290304
   3   INCREMENT BY 30
   4   MAXVALUE 999999999999999999999999999
   5   MINVALUE 2007051994
   6   NOCYCLE
   7   CACHE 20
   8   ORDER
   9   NOKEEP
  10   GLOBAL;


[9:3]YAS-04225 invalid word NOKEEP


SQL> CREATE SEQUENCE yashan.S_xxxxxOG
   2   START WITH 2038290304
   3   INCREMENT BY 30
   4   MAXVALUE 999999999999999999999999999
   5   MINVALUE 2007051994
   6   NOCYCLE
   7   CACHE 20
   8   ORDER
   9   GLOBAL;


[9:3]YAS-04225 invalid word GLOBAL


SQL> CREATE SEQUENCE yashan.S_xxxxxOG
   2   START WITH 2038290304
   3   INCREMENT BY 30
   4   MAXVALUE 999999999999999999999999999
   5   MINVALUE 2007051994
   6   NOCYCLE
   7   CACHE 20
   8   ORDER;


Succeed.

通过对比，崖山数据库在创建序列时不支持使用 NOKEEP 和 GLOBAL。

2.3 存储过程测试

--- 代码如下
SQL> CREATE OR REPLACE PROCEDURE yashan."xxxxx_LOG" (
   2    t_starttime    IN   xxxx_xxxx_log.startime%TYPE,
   3    t_endtime      IN   xxxx_xxxx_log.endtime%TYPE,
   4    t_proc_name    IN   xxxx_xxxx_log.proc_name%TYPE,
   5    t_record_num   IN   xxxx_xxxx_log.record_num%TYPE,
   6    t_relate_id    IN   xxxx_xxxx_log.relate_id%TYPE,
   7    t_msg          IN   xxxx_xxxx_log.msg%TYPE
   8 )
   9 IS
  10    tmpvar   NUMBER;
  11 BEGIN
  12    tmpvar := 0;
  13    
  14    SELECT s_xxxxxclog.NEXTVAL
  15      INTO tmpvar
  16      FROM DUAL;
  17 
  18    INSERT INTO yashan.xxxx_xxxx_log
  19                (ID, startime, endtime, proc_name, record_num,
  20                 relate_id, msg
  21                )
  22         VALUES (tmpvar, t_starttime, t_endtime, t_proc_name, t_record_num,
  23                 t_relate_id, t_msg
  24                );
  25 
  26    COMMIT;
  27 EXCEPTION
  28    WHEN OTHERS
  29    THEN
  30       -- Consider logging the error and then re-raise
  31       RAISE;
  32 END xxxxx_log;
  33 /


Succeed.

通过多次测试，崖山数据库支持不用改写可直接创建Oracle的存储过程。

2.4 触发器测试

--- 代码如下
SQL> CREATE OR REPLACE TRIGGER yashan.T_xxxx_xxxx_xxxx_EN
   2    BEFORE INSERT
   3    ON yashan.xxxx_xxxx_xxxx_EN
   4    FOR EACH ROW
   5 DECLARE
   6    NEW_ID   INTEGER;
   7 BEGIN
   8    IF :NEW.PACKAGE_ID IS NULL
   9    THEN
  10       SELECT S_xxxx_xxxx_xxxx_EN.NEXTVAL INTO NEW_ID FROM DUAL;
  11 
  12       :NEW.PACKAGE_ID := NEW_ID;
  13    END IF;
  14 END;
  15 


Succeed.




SQL> CREATE OR REPLACE trigger yashan.xxxx_xxxx_xxxx_LOG
   2 before
   3 insert
   4 or
   5 update on yashan.TM_VISITOR_EMAIL_LOG
   6 for each row when (user != 'XXXXXX') begin if :OLD.xxx_TIME is null
   7 or :OLD.xxx_TIME < SYSTIMESTAMP then :NEW.xxx_TIME := SYSTIMESTAMP; else :NEW.xxx_TIME := :OLD.xxx_TIME + 1  86400; end if; end;
   8 


Succeed.

经过多次反复测试，崖山数据库支持无需改写Oracle创建触发器代码可以直接创建。

2.5 函数测试

---代码如下
SQL> CREATE OR REPLACE FUNCTION yashan.f_get_exch_xxxx_re (
   2    v_price   NUMBER,
   3    v_unit    VARCHAR2
   4 )
   5    RETURN NUMBER
   6 IS
   7    v_usdrate   NUMBER;
   8    v_hkdrare   NUMBER;
   9    v_result    NUMBER;
  10 BEGIN
  11    SELECT   exch_xxxx_re
  12        INTO v_usdrate
  13        FROM yashan.cre_ba_exch_xxxx_re_log
  14       WHERE currency = 0
  15     and ADDER_TIME = (select max(ADDER_TIME) from yashan.cre_ba_exch_xxxx_re_log where CURRENCY = 0);
  16 
  17    SELECT   exch_xxxx_re
  18        INTO v_hkdrare
  19        FROM yashan.cre_ba_exch_xxxx_re_log
  20       WHERE currency = 1
  21       and ADDER_TIME = (select max(ADDER_TIME) from yashan.cre_ba_exch_xxxx_re_log where CURRENCY = 1);
  22 
  23    IF v_unit = '1'
  24    THEN
  25       v_result := ROUND (v_price  (v_usdrate  100), 4);
  26    ELSIF v_unit = '4'
  27    THEN
  28       v_result := ROUND (v_price * (v_hkdrare  v_usdrate), 4);
  29    ELSE
  30       v_result := v_price;
  31    END IF;
  32 
  33    RETURN v_result;
  34 END;
  35 /


Succeed.

通过多次测试，崖山数据库兼容Oracle创建函数语法，无需改写可以直接创建。

2.6 视图测试

-- 代码如下
CREATE OR REPLACE VIEW VIEW_COR_XXX_XXXX_REPORT (
  "REC_ID",
  "typ_id",
  "SGS_REPORT_ID",
    -- 此处省略
  "LEAD_TIME",
  "xxxxx_QUOTATION_TERMS",
  "xxxxx_PAYMENT_TERMS",
  "MANAGEMENT_CERTIFICATION",
  "xxxxx_CERTIFICATION",
  "RAW_MATERIALS_REPORT",
  "YEAR_OF_ESTABLISHED",
  "DISPLAY_STATUS",
  "SGS_REPORT_INDUSTRY",
  "xxx_TIME1",
  "xxx_TIME2",
  "OVERSEAS_AGENT") AS 
SELECT d.rec_id,
          d.typ_id,
          d.report_id AS sgs_report_id,
          d.sgs_report_end_date,
          d.sgs_report_begin_date,
          yashan.f_get_report_rang (d.cat_range, e.com_prod_cat_range)
             AS report_range,
          d.audit_times,
          d.report_usable,
          d.add_time,
          d.report_sections,
          d.com_employee,
          yashan.f_get_exchange_rate (d.annual_revenue,
                                       d.annual_revenue_unit)
             AS annual_revenue,
          yashan.f_get_exchange_rate (d.export_revenue,
                                       d.export_revenue_unit)
             AS export_revenue,
          d.export_market,
        --- 此处省略
          d.display_status,
          yashan.f_get_sgs_industry (d.indent_id) sgs_report_industry,
          d.xxx_time1,
          d.xxx_time2,
          d.overseas_agent
     FROM (SELECT *
             FROM (SELECT a.rec_id,
                          a.typ_id,
                          a.report_id,
                          b.sgs_report_end_date,
                          b.sgs_report_begin_date,
                          a.cat_range,
                          --- 此处省略
                          b.com_type,
                          b.audit_type,
                          a.xxx_time AS xxx_time1,
                          b.xxx_time AS xxx_time2,
                          b.overseas_agent,
                          ROW_NUMBER ()
                          OVER (
                             PARTITION BY a.typ_id
                             ORDER BY
                                DECODE (a.report_usable,  1, 0,  2, 1,  0, 2) ASC,
                                a.report_end_date DESC,
                                a.add_time DESC)
                             rn
                     FROM    core_sgs_report_info a
                          LEFT JOIN
                             xxx_xxx_xxx_xxxx b
                          ON a.report_id = b.sgs_report_id
                    WHERE (a.symbiosis_flag IS NULL OR a.symbiosis_flag = '0')) c
            WHERE c.rn = 1) d,
          xxx_xxx_xxx_xxxx_sea e,
          mic_swr_info f
    WHERE     d.typ_id = e.typ_id
          AND d.typ_id = f.typ_id
          AND f.AS_SHOW_FLAG = '1'
 ;

通过多次测试，崖山数据库可以很好的兼容Oracle的创建视图语句，无需改写即可创建。

2.7 同义词测试

-- 创建代码
SQL> CREATE SEQUENCE yashan.S_xxxxxxxxLIST_RECID
   2   START WITH 1766855434
   3   INCREMENT BY 30
   4   MAXVALUE 999999999999999999999999999
   5   MINVALUE 1766854234
   6   NOCYCLE
   7   CACHE 20
   8   ORDER;


Succeed.


SQL> CREATE SYNONYM yashan.S_xxxxxx_xxx_RECID FOR yashan.S_xxxxxxxxx_RECID;


Succeed.


SQL> CREATE SYNONYM yashan.S_xxxxx_xxx_RECID FOR yashan.S_xxxxxxLIST_RECID;


Succeed.

经过测试，崖山数据库可以很好的兼容Oracle的创建同义词语句。

2.8 自定义测试

--- 创建代码
SQL> CREATE OR REPLACE TYPE "YASHAN"."TYPE_CONTRACT_IDS"  AS TABLE OF NUMBER;
   2 


Succeed.

通过反复测试，从Oracle上直接获取到的type类型创建语句不含结束符号，直接在崖山数据库执行是需要添加结束符号。

2.9 包及包体测试

2.9.1 包测试

--- 代码如下


SQL> CREATE OR REPLACE PACKAGE pkg_cst_cat_statistics IS
   1   PROCEDURE load_cst_cat_statistics;
   2 
   3 END pkg_cst_cat_statistics;
   4 /


Succeed.

2.9.2 包体测试

-- 代码如下
CREATE OR REPLACE PACKAGE BODY yashan.pkg_xxxx_xxxx_xxxxstics IS
    PROCEDURE load_c_cat_sta AS
        sum_1  NUMBER(10);
        sum_2  NUMBER(10);
        sum_3  NUMBER(10);
        sum_4  NUMBER(10);
        sum_5  NUMBER(10);
        sum_6  NUMBER(10);
        sum_7  NUMBER(10);
        sum_8  NUMBER(10);
        sum_9  NUMBER(10);
        sum_10 NUMBER(10);
        sum_11 NUMBER(10);
        sum_12 NUMBER(10);
        sum_13 NUMBER(10);
        sum_14 NUMBER(10);
        sum_15 NUMBER(10);
        sum_16 NUMBER(10);
        sum_17 NUMBER(10);
        sum_18 NUMBER(10);
        sum_19 NUMBER(10);
        sum_20 NUMBER(10);
        sum_21 NUMBER(10);
        sum_22 NUMBER(10);
        sum_23 NUMBER(10);
    BEGIN
        DELETE FROM c_cat_sta;


        for x in (select aa.user_id
                  FROM ocl_xxxx_xxxx aa, ocl_xxxx_xxxx_role bb
                  WHERE aa.user_id = bb.user_id
                    AND bb.role_id in (20006,110569)
                    AND aa.department_id IN ('9101', '9100','6424')
                    AND aa.active = '1') loop
                select sum(c1),
                       sum(c2),
                       sum(c3),
                       sum(c4),
                       sum(c5),
                       sum(c6),
                       sum(c7),
                       sum(c10),
                       sum(c13),
                       sum(c14),
                       sum(c15),
                       sum(c21),
                       sum(c11),
                       sum(c20),
                       sum(c23)
                into sum_1,
                    sum_2,
                    sum_3,
                    sum_4,
                    sum_5,
                    sum_6,
                    sum_7,
                    sum_10,
                    sum_13,
                    sum_14,
                    sum_15,
                    sum_21,
                    sum_11,
                    sum_20,
                    sum_23
                from (SELECT 1 c1,
                             CASE
                                 WHEN a.ser_beg_time > TRUNC(SYSDATE) THEN
                                     1
                                 ELSE
                                     0
                                 END c2,
                             CASE
                                 WHEN EXISTS (SELECT 1
                                              FROM C_FOL_U_TAG
                                              WHERE FOL_UP_T_ID = 341
                                                AND COM_ID = A.COM_ID) THEN
                                     1
                                 ELSE
                                     0
                                 END c3,
                             CASE
                                 WHEN a.ser_beg_time <= TRUNC(SYSDATE) - 7 and
                                      a.ser_beg_time > TRUNC(SYSDATE) - 30 THEN
                                     1
                                 ELSE
                                     0
                                 END c4,
                             CASE
                                 WHEN a.ser_f_xxx_time <=
                                      ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 3) AND
                                      TRUNC(SYSDATE, 'Q') <= a.ser_f_xxx_time THEN
                                     1
                                 ELSE
                                     0
                                 END c5,
                             CASE
                                 WHEN a.ser_f_xxx_time <=
                                      ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 6) AND
                                      ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 3) <=
                                      a.ser_f_xxx_time THEN
                                     1
                                 ELSE
                                     0
                                 END c6,
                             CASE
                                 WHEN a.ser_beg_time <=
                                      ADD_MONTHS(TRUNC(SYSDATE), -6) AND NOT EXISTS
                                     (SELECT 1
                                      FROM c_wo_rec wr
                                      WHERE wr.re_relat_id = a.com_id
                                        AND wr.rec_rel_web = a.lan_code
                                        AND wr.rec_bus_ty = '32'
                                        AND (wr.par6 = '3204' OR
                                             wr.record_type = '320101')
                                        AND wr.created_time >=
                                            ADD_MONTHS(TRUNC(SYSDATE), -6)) THEN
                                     1
                                 ELSE
                                     0
                                 END c7,
                             CASE
                                 WHEN f_get_perfdegree_score(a.com_id) < 80 THEN
                                     1
                                 ELSE
                                     0
                                 END c10,
                             CASE
                                 WHEN EXISTS
                                     (SELECT 1
                                      FROM yashan.c_a_adv_infomation t,
                                           yashan.c_ad_con_infomation   cc
                                      WHERE t.type_id = '1'
                                        AND t.lan_code != '1'
                                        AND t.rec_id = cc.ads_id
                                        AND t.bus_status = 1
                                        AND TRUNC(t.expire_time) >= TRUNC(SYSDATE)
                                        AND t.par14 IN ('0', '1')
                                        AND ((DBMS_LOB.getlength(cc.content_1) = '0' OR
                                              DBMS_LOB.getlength(cc.content_1) IS NULL) OR
                                             (t.par9 = '1' AND
                                              (t.par12 IS NULL OR t.par13 IS NULL)))
                                        AND cc.content_3 IS NULL
                                        AND t.com_id = a.com_id) THEN
                                     1
                                 ELSE
                                     0
                                 END c13,
                             CASE
                                 WHEN EXISTS
                                     (SELECT 1
                                      FROM yashan.c_a_adv_infomation t,
                                           yashan.c_ad_con_infomation   cc
                                      WHERE t.type_id = '1'
                                        AND t.lan_code = '0'
                                        AND t.rec_id = cc.ads_id
                                        AND t.bus_status = 1
                                        AND TRUNC(t.expire_time) >= TRUNC(SYSDATE)
                                        AND t.par14 IN ('0', '1')
                                        AND cc.content_3 IS NULL
                                        AND t.com_id = a.com_id) THEN
                                     1
                                 ELSE
                                     0
                                 END c14,
                             CASE
                                 WHEN EXISTS (SELECT 1
                                              FROM yashan.c_a_adv_infomation aa
                                              WHERE type_id = 'st'
                                                AND aa.com_id = a.com_id
                                                AND aa.lan_code = '0'
                                                AND aa.par15 IS NULL
                                                AND aa.bus_status = '1') THEN
                                     1
                                 ELSE
                                     0
                                 END c15,
                             CASE
                                 WHEN (f_get_last_logon_time(a.com_id, a.lan_code) IS NULL OR
                                       ADD_MONTHS(f_get_last_logon_time(a.com_id,
                                                                        a.lan_code),
                                                  3) < SYSDATE) AND
                                      a.ser_beg_time < ADD_MONTHS(SYSDATE, -3) THEN
                                     1
                                 ELSE
                                     0
                                 END c21,
                             CASE
                                 WHEN EXISTS (SELECT 1
                                              FROM C_FOL_U_TAG
                                              WHERE FOL_UP_T_ID = 342
                                                AND COM_ID = A.COM_ID) THEN
                                     1
                                 ELSE
                                     0
                                 END c11,
                             CASE
                                 WHEN EXISTS (SELECT 1
                                              FROM C_FOL_U_TAG
                                              WHERE FOL_UP_T_ID = 343
                                                AND COM_ID = A.COM_ID) THEN
                                     1
                                 ELSE
                                     0
                                 END c20,
                             CASE
                                 WHEN NOT EXISTS
                                     (SELECT 1
                                      FROM C_FOL_U_TAG
                                      WHERE FOL_UP_T_ID IN (341, 342, 343)
                                        AND COM_ID = A.COM_ID) THEN
                                     1
                                 ELSE
                                     0
                                 END c23


                      FROM cst_cu a
                      WHERE a.service_status = '1' 
                        AND a.lan_code = '0'
                        AND (a.service_type LIKE '1,%' OR a.service_type LIKE '0%' OR
                             a.service_type = '1')
                        AND a.cat_cst_id = x.user_id);


                select sum(c8), sum(c9), sum(c12), sum(c22)
                into sum_8, sum_9, sum_12, sum_22
                from (SELECT case
                                 when b.avg_month_msg <= 5 then
                                     1
                                 else
                                     0
                                 end c8,
                             case
                                 when b.total_prod_cnt <= 20 then
                                     1
                                 else
                                     0
                                 end c9,
                             case
                                 when b.main_prod_flag = '0' then
                                     1
                                 else
                                     0
                                 end c12,
                             CASE
                                 WHEN f_get_ord_main_prod_num(A.COM_ID, 1) <
                                      B.MAIN_PROD THEN
                                     1
                                 ELSE
                                     0
                                 END c22
                      FROM cst_cu a, cst_cu_bi_stat b
                 ---- 此处省略代码
                  AND wr.creator_id = x.user_id;
                select sum(c17), sum(c18), sum(c19)
                into sum_17, sum_18, sum_19
                from (SELECT case
                                 when wr.par6 = '3201' then
                                     1
                                 else
                                     0
                                 end c17,
                             case
                                 when wr.record_type = '320101' then
                                     1
                                 else
                                     0
                                 end c18,
                             case
                                 when wr.record_type = '320103' then
                                     1
                                 else
                                     0
                                 end c19
                      FROM cst_cu a, c_wo_rec wr
                      WHERE a.lan_code = '0'
                    ---- 此处省略代码
                INSERT INTO c_cat_sta
                (user_id,
                 user_name,
                 sta_time,
                 sta_num_val_1,
                 sta_num_val_2,
                 sta_num_val_3,
                 sta_num_val_4,
                 sta_num_val_5,
                 sta_num_val_6,
                 sta_num_val_7,
                 sta_num_val_8,
                 sta_num_val_9,
                 sta_num_val_10,
                 sta_num_val_11,
                 sta_num_val_12,
                 sta_num_val_13,
                 sta_num_val_14,
                 sta_num_val_15,
                 sta_num_val_16,
                 sta_num_val_17,
                 sta_num_val_18,
                 sta_num_val_19,
                 sta_num_val_20,
                 sta_num_val_21,
                 sta_num_val_22,
                 sta_num_val_23,
                 creator_id,
                 created_time,
                 modifier_id,
                 modified_time)
                SELECT aa.user_id,
                       aa.fullname,
                       SYSDATE,
                       nvl(sum_1, 0),
                       nvl(sum_2, 0),
                       nvl(sum_3, 0),
                       nvl(sum_4, 0),
                       nvl(sum_5, 0),
                       nvl(sum_6, 0),
                       nvl(sum_7, 0),
                       nvl(sum_8, 0),
                       nvl(sum_9, 0),
                       nvl(sum_10, 0),
                       nvl(sum_11, 0),
                       nvl(sum_12, 0),
                       nvl(sum_13, 0),
                       nvl(sum_14, 0),
                       nvl(sum_15, 0),
                       nvl(sum_16, 0),
                       nvl(sum_17, 0),
                       nvl(sum_18, 0),
                       nvl(sum_19, 0),
                       nvl(sum_20, 0),
                       nvl(sum_21, 0),
                       nvl(sum_22, 0),
                       nvl(sum_23, 0),
                       '99999999',
                       SYSDATE,
                       '9999999',
                       SYSDATE
                FROM ocl_xxxx_xxxx aa
                where aa.user_id = x.user_id;
            end loop;


        INSERT INTO c_cat_sta
        (user_id,
         user_name,
         sta_time,
         sta_num_val_1,
         sta_num_val_2,
         sta_num_val_3,
         sta_num_val_4,
         sta_num_val_5,
         sta_num_val_6,
         sta_num_val_7,
         sta_num_val_8,
         sta_num_val_9,
         sta_num_val_10,
         sta_num_val_11,
         sta_num_val_12,
         sta_num_val_13,
         sta_num_val_14,
         sta_num_val_15,
         sta_num_val_16,
         sta_num_val_17,
         sta_num_val_18,
         sta_num_val_19,
         sta_num_val_20,
         sta_num_val_21,
         sta_num_val_22,
         sta_num_val_23,
         creator_id,
         created_time,
         modifier_id,
         modified_time)
        SELECT b.department_id,
               c.department_name,
               SYSDATE,
               SUM(sta_num_val_1),
               SUM(sta_num_val_2),
               SUM(sta_num_val_3),
               SUM(sta_num_val_4),
               SUM(sta_num_val_5),
               SUM(sta_num_val_6),
               SUM(sta_num_val_7),
               SUM(sta_num_val_8),
               SUM(sta_num_val_9),
               SUM(sta_num_val_10),
               SUM(sta_num_val_11),
               SUM(sta_num_val_12),
               SUM(sta_num_val_13),
               SUM(sta_num_val_14),
               SUM(sta_num_val_15),
               SUM(sta_num_val_16),
               SUM(sta_num_val_17),
               SUM(sta_num_val_18),
               SUM(sta_num_val_19),
               SUM(sta_num_val_20),
               SUM(sta_num_val_21),
               SUM(sta_num_val_22),
               SUM(sta_num_val_23),
               '99999999',
               SYSDATE,
               '99999999',
               SYSDATE
        FROM c_cat_sta a, ocl_xxxx_xxxx b, xxxx_am_department c
        WHERE a.user_id = b.user_id
          AND b.department_id = c.department_id
        GROUP BY b.department_id, c.department_name;


        INSERT INTO c_cat_sta
        (user_id,
         user_name,
         sta_time,
         sta_num_val_1,
         sta_num_val_2,
         sta_num_val_3,
         sta_num_val_4,
         sta_num_val_5,
         sta_num_val_6,
         sta_num_val_7,
         sta_num_val_8,
         sta_num_val_9,
         sta_num_val_10,
         sta_num_val_11,
         sta_num_val_12,
         sta_num_val_13,
         sta_num_val_14,
         sta_num_val_15,
         sta_num_val_16,
         sta_num_val_17,
         sta_num_val_18,
         sta_num_val_19,
         sta_num_val_20,
         sta_num_val_21,
         sta_num_val_22,
         sta_num_val_23,
         creator_id,
         created_time,
         modifier_id,
         modified_time)
        SELECT '1200',
                       ---- 此处省略代码
               SUM(sta_num_val_8),
               SUM(sta_num_val_9),
               SUM(sta_num_val_10),
               SUM(sta_num_val_11),
               SUM(sta_num_val_12),
               SUM(sta_num_val_13),
               SUM(sta_num_val_14),
               SUM(sta_num_val_15),
               SUM(sta_num_val_16),
               SUM(sta_num_val_17),
               SUM(sta_num_val_18),
               SUM(sta_num_val_19),
               SUM(sta_num_val_20),
               SUM(sta_num_val_21),
               SUM(sta_num_val_22),
               SUM(sta_num_val_23),
               '99999999',
               SYSDATE,
               '99999999',
               SYSDATE
        FROM c_cat_sta a
        WHERE user_id IN (9101, 9100, 6424);


        COMMIT;
    END load_c_cat_sta;


END pkg_xxxx_xxxx_xxxxstics;
/

2.10 dblink测试

测试崖山数据库通过dblink连接Oracle

[yashan@opensource-db ~]$ yasql sys/yashan_1234@192.168.73.21:1688
YashanDB SQL Personal Edition Release 23.1.1.100 x86_64


Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux


SQL> conn yashan/yashan123


Connected to:
YashanDB Server Personal Edition Release 23.1.1.100 x86_64 - X86 64bit Linux


SQL> CREATE DATABASE LINK ya2Odblink CONNECT TO "test" IDENTIFIED BY "test" USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.xxx.xxx)(PORT = 1521)))(CONNECT_DATA =(SID = xxxx1)))';


Succeed.


SQL> select * from dual@ya2Odblink;


YAS-07318 failed to call external module
YAS-00404 address (DESCRIPTION=(ADDRESS_LIST=(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168. is an invalid IPV4 URL/Ip address/hostname


SQL> select * from v$session@ya2Odblink;


YAS-07318 failed to call external module
YAS-00404 address (DESCRIPTION=(ADDRESS_LIST=(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168. is an invalid IPV4 URL/Ip address/hostname

Oracle创建dblink的语句可以在崖山数据库执行，但无法正常连接到远程Oracle数据库。

根据崖山数据库官方文档，在Oracle数据库上可通过透明网关方式通过dblink远程连接到崖山数据库，官网有介绍，此处就不在过多介绍，详细内容可参见崖山官网文档。

总结

通过选取Oracle生产环境比较复杂的一些数据类型进行测试，崖山数据库可以很大程度无需改写SQL兼容Oracle数据库语法，部分SQL还是需要手工介入调整。本次测试更多围绕Oracle和崖山数据库语法结构的兼容性测试，其它更复杂场景有待评测。还未进行数据的迁移测试，后面会抽时间搭建测试环境，从Oracle迁移数据到崖山数据库。

国产去O道路漫长而深远，我们要对国产数据库抱有兼容开放的心态。

只要国人不断努力，国产数据库一定会发展更好。最后祝愿崖山数据库更加蒸蒸日上。

相关阅读

文章转载自YashanDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。