在去'O'大背景下，为了减轻用户去'O'成本以及加快去'O'速度，Halo作为一款成熟、稳定的国产数据库，自然也是兼容Oracle和一些主流的数据库（如Mysql SQL Serve DB2等），可以做到平滑的替换这些数据库，本文主要针对Halo对Oracle的语法兼容。
字符串截取
select substr('abcdef',1,3) from table_1;
字符串连接
select 'HELLO'||'hello world' from table_1;
去掉字符串中的空格
select ltrim(' abc') s1,rtrim('chen ') s2,trim(' chen') s3 from table_1;
返回字符串首字母的Ascii值
select ascii('a') from table_1;
返回ascii值对应的字母
select chr(97) from table_1;
计算字符串长度
select length('abcdefghijk') from table_1;
initcap（首字母变大写） ,lower(变小写),upper(变大写)
select lower('ABC') s1, upper('def') s2, initcap('efg') s3from table_1;
Replace
select replace('abc','b','xy') from table_1;
Translate
select translate('abc','b','xx') from table_1;
lpad [左添充] rpad [右填充](用于控制输出格式)
select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from table_1;
支持varchar2类型
drop table table_2;
create table table_2(
id int not null primary key,
id1 int,
id2 int,
char1 char(10),
char2 char(10),
varchar1 varchar(80),
varchar02 varchar2(80),
datetime1 timestamp
) distribute by hash(id);
start transaction;
insert into table_2(id,id1,id2,char1,char2,varchar1,varchar02) values
(1,1018246,1,'true','13333','2020-3-15 14:25:36','2020-3-15 '),
(2,20061488,2,null,'flase','2020-4-15 19:25:36','2020-4-15 '),
(3,10145646,3,null,null,'2019-3-15 18:25:00','2019-3-15 '),
(4,90062008,4,'正确',null,'2010-06-15 00:00:36','2020-6-15 '),
(5,5658221,5,null,'错误','2020-3-25','2020-3-25 ');
commit;
兼容ORACLE from子查询不带表别名
select * from (select id,id1,id2 from test where char1 is not null);
MERGE INTO操作
创建目标表products和源表newproducts，并插入数据
CREATE TABLE products
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);
INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs');
INSERT INTO products VALUES (1502, 'olympus is50', 'electrncs');
INSERT INTO products VALUES (1600, 'play gym', 'toys');
INSERT INTO products VALUES (1601, 'lamaze', 'toys');
INSERT INTO products VALUES (1666, 'harry potter', 'dvd');
CREATE TABLE newproducts
(
product_id INTEGER,
product_name VARCHAR2(60),
category VARCHAR2(60)
);
INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs');
INSERT INTO newproducts VALUES (1601, 'lamaze', 'toys');
INSERT INTO newproducts VALUES (1666, 'harry potter', 'toys');
INSERT INTO newproducts VALUES (1700, 'wait interface', 'books');
进行MERGE INTO操作
MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'
WHEN NOT MATCHED THEN
INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';
查询更新后的结果
SELECT * FROM products ORDER BY product_id;
greatest():查找里面最大的一个，least():查找里面最小的一个
select greatest(1,2,3,4,5) from table_1;
select least(1,2,3,4,5) from table_1;
删除序列
DROP SEQUENCE IF EXISTS test1;
序列存在：
序列不存在：
总结：
这些语法的兼容只是适配Oracle的冰山一角，Halo对Oracle在兼容性方面上有着绝对的优势。
