金蝶云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;BEGINSELECT count(1) INTO var001  FROM user_tables WHERE  table_name=upper('expandxml');IF var001=0 thenEXECUTE 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 loopINSERT 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 基本上是一样的。