金蝶云支持oracle和sql server数据库,由于oracle数据库客户使用得比较少,不同同事在实际维护的过程中感到非常费力。下面总结金蝶云实际维护过程中经常用到的SQL语法对比,供大家作为参考。
一、查询相关的语法
1、最简单的查询:sql server是select,而oracle则为select from。 例如:
--sql server
select 'hello'
--oracle
select 'hello' from dual;
2、字符串的连接:sql server可以使用“+”号,而oracle则使用“||”。不过,concat函数在两个数据库上可以使用。例如:
--sql server
select 'good '+'weather',concat('good ','weather')
--oracle
select 'good '||'weather',concat('good ','weather') from dual;
3、字符串的匹配,要注意oracle是区分大小写的.我的习惯是转换为大写之后进行比较。例如:
--sql server
select * from T_AR_RECMACTHLOGENTRY where fsourcefromid='AR_receivable'
--oracle
select * from T_AR_RECMACTHLOGENTRY where upper(fsourcefromid)=upper('AR_receivable');
4、日期转换:sql server 支持日期字符串到日期的隐式转换,而oracle要使用to_date函数显式转换。例如:
--sql server
select GETDATE() where GETDATE()>'2019-01-01'
--oracle
select 1 from dual where sysdate>to_date('2019-01-01','yyyy-mm-dd');
5、查询所有列:sql server支持(*)与单独列名作为结果返回,oracle 不支持。例如:
--sql server正常
select fnumber, * from t_bd_account
--oracle下面语句报错
select fnumber, * from t_bd_account
二、备份表:oracle不支持select into语法
--sql server
select * into t_bd_account20190126bak from t_bd_account;
--oracle
create table t_bd_account20190126bak as select * from t_bd_account;
三、插入数据
同sql server 支持insert into 表名(列名) values()
同sql server 支持insert into 表名(列名) select-sql
四、更新字段,这种情况最为常见,也容易让人迷惑。
sql server支持关联表直接更新(也支持merge into 但是除非进行not matched insert,否则没必要用),而oracle需使用merge into语法或者update exists语法。例如:
--sql server
update a set a.fdc=b.fdc,a.FISCASH=b.FISCASH from t_bd_account a join t_bd_accountbak b on a.FACCTID=b.FACCTID
join t_bd_accounttable c on a.FACCTTBLID=c.FACCTTABLEID and c.fnumber='PRE01'
where a.fnumber='1001.01'
--oracle下,介绍最便于理解的方式,使用merge into语法
merge into t_bd_account t1 using (select a.facctid, b.fdc,b.FISCASH from t_bd_account a join t_bd_accountbak b on a.FACCTID=b.FACCTID
join t_bd_accounttable c on a.FACCTTBLID=c.FACCTTABLEID and upper(c.fnumber)=upper('PRE01')
where a.fnumber='1001.01' ) t2 on (t1.facctid=t2.facctid)
when matched then update
set t1.fdc=t2.fdc,t1.FISCASH=t2.FISCASH
--oracle下 update exists语法,要在set和where进行两次匹配,显得不简洁
update t_bd_account t1
set (fdc,FISCASH)=(select b.fdc,b.FISCASH from t_bd_account a join t_bd_accountbak b on a.FACCTID=b.FACCTID
join t_bd_accounttable c on a.FACCTTBLID=c.FACCTTABLEID and upper(c.fnumber)=upper('PRE01')
where a.fnumber='1001' and a.facctid=t1.facctid )
where exists
(select 1 from t_bd_account a join t_bd_accountbak b on a.FACCTID=b.FACCTID
join t_bd_accounttable c on a.FACCTTBLID=c.FACCTTABLEID and upper(c.fnumber)=upper('PRE01')
where a.fnumber='1001' and a.facctid=t1.facctid )
五,删除:sql server支持关联表后直接进行删除,而oracle 不支持,需要修改为简单的delete from 格式。例如:
--sql server
delete from a from t_bd_account a join t_bd_accountbak b on a.FACCTID=b.FACCTID
join t_bd_accounttable c on a.FACCTTBLID=c.FACCTTABLEID and c.fnumber='PRE01'
where a.fdc<>b.fdc
--oracle
delete from t_bd_account where facctid in
(select a.facctid from t_bd_account a join t_bd_accountbak b on a.FACCTID=b.FACCTID
join t_bd_accounttable c on a.FACCTTBLID=c.FACCTTABLEID and c.fnumber='PRE01'
where a.fdc<>b.fdc);
六,if exists用法
Oracle的if语法不能直接使用if exists,只能在if后面直接添加条件
DECLARE var001 number; BEGIN SELECT count(1) INTO var001 FROM user_tables WHERE table_name=upper('expandxml'); IF var001=0 then EXECUTE immediate 'create table expandxml(ftype int, fid varchar2(100), flevel int, fxml xmltype)'; END IF; end;
七,循环
--while 循环 CREATE TABLE chl_result(var002 number); DECLARE var001 number; BEGIN SELECT 1 INTO var001 FROM dual; WHILE var001<=100 loop INSERT INTO chl_result(var002) values(var001); SELECT var001+1 INTO var001 FROM dual; END LOOP ; END; SELECT * FROM chl_result;
八、空字符串比较和null值比较,特别慎用'',跟sql server完全不同
CREATE TABLE chltest002(fname varchar2(100)); INSERT INTO chltest002(fname) values('abc'); --有输出 SELECT * FROM chltest002 WHERE fname<>' '; --以下无输出 SELECT * FROM chltest002 WHERE fname<>NULL; SELECT * FROM chltest002 WHERE fname<>''; SELECT * FROM chltest002 WHERE fname=NULL; SELECT * FROM chltest002 WHERE fname='';
其他
其他如create table,drop table,truncate tabale 基本上是一样的。
推荐阅读