一、前言
最近有款国产数据库真的很火，火到什么程度呢，很多数据库行业的大佬都在多种场合讨论它、墨天轮社区里的很多小伙伴也在群里分享使用的感受、很多微信公众号也都是它的新闻。
是哪款数据库能这么火呢，当然是崖山数据库了，对，就是这款名叫yashandb的数据库。
我之前曾有幸参加了崖山数据库的YCA认证培训，并通过了YCA的认证。
自从那以后，也一直非常关心崖山数据库的发展。
当前收到中美贸易摩擦和世界环境的多变，很多公司都在进行去O国产数据库迁移。
做为一名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道路漫长而深远，我们要对国产数据库抱有兼容开放的心态。
只要国人不断努力，国产数据库一定会发展更好。
最后祝愿崖山数据库更加蒸蒸日上。
最后修改时间：2023-11-30 13:39:06
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
