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

前言

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) - 30 THEN
1
ELSE
0
END c4,
CASE
WHEN a.ser_f_xxx_time