金蝶云oracle数据库运维必须了解的SQL语法要点

金蝶云支持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 基本上是一样的。