oracle语法差异

虚拟表

虚拟表

Oracle获取一个常量需要通过dual，unvdb则不需要

oracle	unvdb
select 2 from dual;	select 3

虚拟列

虚拟列rownum

对于查询返回的每行数据，rownum都会返回对应列的数值，第一行为1第二行为2以此类推。

rownum在select列表中时重写为row_number() over ()
rownum在where子句中时重写为limit… offset…

oracle	unvdb
select rownum from test;	select row_numder() over() as rownum from test;
select tableoid from test where rownum < 10 and rownum >=2;	select tableoid from test limit 9 OFFSET 2 ;
select name from test where rownum < 10 and age = '111';	select test.name from test where test.age = '111' limit9;
select * from test wherer rownum between 1 and 10;	select tableoid from test limt 10 offset 0;

虚拟列rowid

oracle 中rowid虚拟列会返回特定行的具体地址，在unvdb中为 tableoid || ‘#’ || ctid

oralce	unvdb
select rowid, t.* from test t;	select tableoid \|\| '#' \|\| ctid, t.* from test as t;

字符串函数

nvl(col,value)

在oracle中nvl（col,value）用来设置默认值，col为空就设置为value。在unvdb中重写为coalesce

oracle	unvdb
select nvl(t_phone,1) from test;	select coalesce(customer.t_phone,'1') from test;

nvl2(col,v1,v2)

nvl2对col的null值进行处理，如果col为null，则返回v1，否则返回v2。unvdb中没有此类函数，可以重写为case when。

oracle	unvdb
select nvl2(t_phone,1,2) from test;	select case when t_phone is null then 1else 2 end from test;

decode(arg1,arg2,arg3,arg4)

oracle中decode(arg1,arg2,arg3,arg4)函数，表示arg1等于arg2时取arg3，否则取arg4。 unvdb中没有此类函数，可以重写为 case when

oracle	unvdb
select decode(t_phone,'110',1,2) from test;	select case when t_phone = '110' then 1else 2 end from test;
select decode(t_phone,null,1,2) from test;	select case when t_phone is null then 1 else 2 end from test;

substr(str,int,int)

在oracle中substr是用来取第一个字符串的子串，unvdb中有同名的函数实现类似的功能。其中不同的是oracle中，第二，三个参数是可以为负数的，代表从后面进行计数。unvdb不允许其成为负数，需要对其进行转换。oracle中以0开始计数，unvdb中以1开始计数。

oracle	unvdb
select substr(t_phone,1,-2) from test;	select substr(t_phone1,length(t_phone) -2) from test;
select substr(t_phone,-3,1) from test;	select substr(t_phone,length(t_phone) -3,1) from test;

instr(str1,str2)

oracle中instr用来取一个字符穿的子串位置，当其只有两个参数时，表示子串第一次出现的位置，和unvdb中对应的函数为strpos。当有多个参数时没有对应函数。

oracle	unvdb
select inste('123','23')	select strpos('123','23')

replace(srcstr,oldsub[,newsub])

在oracle中，replace()函数用于替换字符串，replace(srcstr,oldsub[,newsub]), 和unvdb中的replace函数用法基本一直。需要注意在oracle中没有第三个参数时代表删除此字符，在unvdb中第三个参数可以设置为’’。

oracle	unvdb
select replace('123','1');	select replace('123','1','');

listagg(str,[str])

oracle里的listagg函数实现对列值的拼接，它可以在分组内以指定顺序对非分组列进行拼接。在unvdb中可以使用string_agg函数来实现，需要注意语法方面也有区别。另外，其第二个参数可选，默认值’’，在unvdb中需要补充第二个参数。

当没有group by子句时，可以使用over（partiton by… order by…）进行替换
当指定group by子句时，它的重写算法比较复杂
如果需要保持拼接的顺序，需要通过子查询来实现（见第二行）
如果不需要保持拼接顺序，可以把它转化为简单的聚集函数（见第三行）

oracle	unvdb
select listagg(t_name,',') within group(order by t_name) over (partiton by t_name) as name from test;	select string_agg(test.t_name,',') over (patition by test.t_phone order by t_custkey) as name from test;
select listagg(t_name,',') within group(order by t_name) as name from test group by t_phone;	select max(paw_dt.name) as name from (select string_agg (test.t_name,',') over (partition by test.t_phone order by t_name) as name, t_phone from test) as paw_dt group by t_phone ;
select listagg(t_name,',') within group(order by t_name) as name from test group by t_phone;	select string_agg(t_name,',') as name from test group by t_phone;

日期函数

sysdate和systimestamp

oracle中sysdate()和sysdate返回的是当前系统的日期 ,时间 (日期，时分秒)，在unvdb中对应now()或current_timestamp (日期-时分秒-毫秒)。

oracle中systimestamp返回系统当前时间戳（日期-时分秒-毫秒），在unvdb中对应now()或current_timestamp

oracle	unvdb
select sysdate	select current_to,estamp
select sysdate()	select now()
select systimestamp	select current_timestamp

to_date(str,fmt)

oracle中to_date返回的是时间类型，在unvdb中to_date是日期类型，在unvdb中对应的是to_timestamp

oracle	unvdb
select to_date(endTime ,'yyyy-mm-ddhh24:mi:ss') from test;	select to_timestamp(endTime,'yyyy-mm-ddhh24:mi:ss') from test;

trunc(arg1,[arg2])

在oracle中trunc函数有两种使用方法

第一种是对数字进行截取, trunc(num,[int]); 是去掉数字num小数位以后的部分，并且不进行四舍五入。这种用法和在unvdb的trunc用法一致，不需要转换。
第二种用法是对日期进行提取，trunc(date,[fmt])。这种用法在unvdb对应的函数是date_trunc(fmt, date)，需注意在unvdb中fmt是第一个参数，且不可省略。

oracle	unvdb
select trunc(111.23,2)	select trunc(111.23,2)
select trunc(sysdate,'year')	select date_trunc('year',current_timestamp)
select trunc(sysdate)	select date_trunc('dd',current_timestamp)

add_months(date,int)

oracle中的add_months函数主要用于对日期函数进行按月增加的操作。在unvdb中没有对应的函数，需要转为基于日期和interval的运算。

oracle	unvdb
select add_months(sysdate,2)	select current_timestamp + 2 * interval '1 month'

last_ady(date)

oracle中last_day返回指定日期所在月份的最后一天，在unvdb中没有对应函数，需要转为基于日期和interval的运算。

oracle	unvdb
select add_months(sysdate,2)	select cast(date_trunc('MONTH',current_timestamp) + interval '1 MONTH - 1 DAY' as date)

sql语句

HAVING子句顺序

oracle中允许HACVING在group by 之前或之后。在unvdb中，HAVING子句必须在 group by之后。

oracle	unvdb
select t_name from test having count(*) > 2 group by t_name;	select t_name from test group by t_name having count(*) > 2;

括号中的表名

oracle中允许单表在括号中使用表名，而unvdb中不允许。

oracle	unvdb
select * from (test);	select * from test;

UNIQUE关键字

oracle中允许使用UNIQUE进行去重，在unvdb中是DISTINCT关键字实现。

oracle	unvdb
select unique t_phone from test;	select distinct test.t_phone from test;

MINUS关键字

oracle中可以使用minus关键字来取两个结果的集的差，在unvdb中迁移为except。

oracle	unvdb
select t_custkey from test minus select o_custkey from orders;	select t_custkey from test except select o_custkey from orders ;

FROM关键字

oracle的delete语句的FROM关键字可以省略，迁移至unvdb需补充上。

oracle	unvdb
delete test where 1=0;	delete from test where 1 = 0 ;

NOLOGGING关键字

oracle在执行iinsert语句时，可以通过指定NOLOGGING关键字来减少日志记录，提升操作性能。unvdb不支持此关键字。

oracle	unvdb
insert into test nologging select * from test_bk;	insert into test select * from test_bk;

AS关键字

unvdb 中 insert into 后面不需要添加as关键字。

oracle	unvdb
insert into t as select c1 from t1;	insert into t select c1 from t1;

FROM子查询别名

oracle中不引起歧义的情况下可以不需要别名，在unvdb中，所有from 子查询都需要带有别名

oracle	unvdb
select * from (select * from test);	select * from (select * from test) as foo ;

UPDATE语句里的字段名

在unvdb中，update的时候，更新列不允许添加表名前缀。

oracle	unvdb
update test t set t.t_name = ‘xxx’ where t_custkey = 1;	update test set t_name = ‘xxx’ where t_custkey = 1 ;

左（右）外连接

在oracle中，外连接可以通过在条件上添加(+)来定义，连接符（+）跟在哪个条件后面就是哪张表被左连。在unvdb中，需将其重写为标准的外连接语法。

oracle	unvdb
select * from test,orders where t_custkey = o_custkey(+);	select * from test left outer join orders on t_custkey = o_custkey;
select * from test,orders where t_custkey(+) = o_custkey and t_name(+) = o_clerk and o_custkey>100;	select * from test right outer join orders on (t_custkey = o_custkey and t_name = o_clerk ) where o_custkey > 100 ;

CONNECT BY子句

oracle中，CONNECT BY 用于存在上下级等层级关系的数据表进行递归查询。语法格式:START WITH condition1 CONNECT BY [ NOCYCLE ] condition2 在unvdb中通过Recursive Common Table Expression来实现此功能，主要是把START WITH… CONNECT BY Prior拆成两个部分，查询表一致，但条件不一致，用UNION ALL合并.

oracle

unvdb

select id from city_branch start with id=1 connect by prior id=parent_id;

with RECURSIVE MIG_CTE as (
select id, 1 as level from city_branch where id = 1
union all
select id, level + 1 from city_branch, MIG_CTE where MIG_CTE.id = parent_id)
select * from MIG_CTE ;

select t.branch_level, t.id from city_branch c where (c.branch_level = ‘1’ or t.branch_level = ‘2’)
and (t.sign = ‘1’ or t.sign = ‘4’ or t.sign = ‘8’) and t.status = ‘1’ start with c.id = 1
connect by c.id = prior c.parent_id
order by c.branch_level desc;

with RECURSIVE MIG_CTE as
(select t.branch_level, t.id, 1 as level from city_branch as cwhere ((((branch_level = ‘1’ or t.branch_level = ‘2’)and ((t.sign = ‘1’ or t.sign = ‘4’) or t.sign = ‘8’)) and t.status = ‘1’) and c.id = 1)
union all
select t.branch_level, t.id, level + 1 from city_branch as c, MIG_CTE where ((((branch_level = ‘1’ or t.branch_level = ‘2’) and ((t.sign = ‘1’ or t.sign = ‘4’)
or t.sign = ‘8’)) and t.status = ‘1’) and c.id = MIG_CTE.parent_id))
select * from MIG_CTE order by MIG_CTE.branch_level desc;

操作符的强类型限制

oracle中不同类型进行基于操作符的运算，会自动转化类型，譬如select 1 + ‘1’ from dual。

unvdb是强类型，不同类型的运算会提示类型不匹配，执行select 1 + ‘1’会报错，需要进行显式的类型转换。

涉及的操作符类型包括：

操作符	操作符名称
+	加法
-	减法
/	除法
%	取余
*	乘法
\|\|	字符串拼接

数值运算（+,-,*,/,%）

oracle	unvdb
select 1+'1'	select 1+1
select 1 + charCol from tbl ;	select 1+ cast(charCol as unmeric) from tbl ;
select '1' - 1	select 1-1
select 1* charCol from tbl ;	select 1 * cast(charCol as numeric) from tbl ;
select 1 / charCol from tbl ;	select 1 / cast(charCol as numeric) from tbl;
select charCol % 2 from tbl;	select cast(charCol as numeric) % 2 from tbl;

日期计算(+,-)

oracle	unvdb
select sysdate - 1	select current_timestamp - interval ‘1’ DAY
select 1 + sysdate()	select interval ‘1’ DAY + now()
select systimestamp +1	select current_timestamp + interval ‘1’ DAY
select systimestamp - 1	select current_timestamp - interval ‘1’ DAY

字符串拼接(||)

oracle	unvdb
select 1 \|\| 1	select '1' \|\| '1'
select 1 \|\| c_custkey	select 1 \|\| cast(c_custkey as text)

函数参数的强类型限制

oracle中在函数调用时，参数类型进行会自动转化类型，譬如 select substr(123.12,0,2)是合法的，且返回123。unvdb是强类型，执行select substr(123.12,0,2)会报错，需要进行显式的类型转换。

substr（arg1, arg2, arg3）

oracle	unvdb
select substr(1234.1, 0, 4)	select substr(‘1234.1’, 1, 4+1)
select substr(‘1234.1’, 0, ‘2’)	select substr(‘1234.1’, 0, 2)

sum(arg)

oracle	unvdb
select sum('2')	select sum(2)

avg(arg)

oracle	unvdb
select round(‘2’)	select round(2)

条件判断中的强类型限制

oracle中在进行条件判断时，左右表达式的类型进行会自动转化，譬如 where c_phone = 110是合法的。unvdb是强类型，执行where c_phone = 110会报错，需要进行显式的类型转换。

比较运算(=、>、<、>=、<=、<>)

转换原则，优先转换常量类型；当两个都为数据列时，优先转换左边的。

oracle	unvdb
select * from test where t_phone = 110	select * from test where t_phone = ‘110’
select * from test where ‘1’ = t_custkey	select * from test where 1 = t_custkey
select * from test where t_phone = t_custkey	select * from test where cast(t_phone as int) = t_custkey

BETWEEN

转换原则，转换 var0 between var1 and var2 中的 var1, var2。

oracle	unvdb
select * from test where t_custkey between ‘100’ and ‘200’;	select * from test where t_custkey between 100 and 200;

IN LIST

转换原则，转换List中的变量。

oracle	unvdb
select * from test where t_phone in (110,120);	select * from test where t_phone in (‘110’, ‘120’);

默认参数

oracle中有部分函数存在默认参数,而在unvdb其参数是必填项。

to_char(unknown)

oracle	unvdb
select to_char(t_custkey) from test;	select cast(t_custkey as text) from test;

to_number(str)

oracle	unvdb
select to_number(‘100’)	select 100
select to_number(t_phone) from test;	select cast(t_phone as numeric) from test;

oracle	unvdb
select 1 \|\| 1	select '1' \|\| '1'
select 1 \|\| c_custkey	select 1 \|\| cast(c_custkey as text)