Oracle和SQL平台差异

编程规范（同时居于Oracle和SQL平台）：

 

（1）      Database 部分: 可以分成Sql Server 和 Oracle 两个版本。

1.     Stored Procedure: 注意语法问题，在SQL SERVER中用的是 T-SQL,而在Oracle中用的是PL/SQL，区别地方需要手动进行修改。

2.     SQL Mail & SQL JOB:可以通过Enterprise Console进行设置。

3.     User Defined Type（ Cancel ）

 

4.     某些表中有多个Text字段的问题

   在SQL Server数据库中，我们常常会在一个表中设置多个Text类型的字段，这在SQL Server中是允许的。在Oracle中与Text类型相对应的是Long类型，但Oracle规定在一个表中只能有一个Long类型的字段，所以就会产生这个问题了。为了解决这个问题，我们想了一个可以替代的方法，即用Varchar2(4000)来代替Long类型对应Text字段。在实际的转化和后面的简单测试中均证明，我们这种替代的方法是可行的。

 

5．表中有Identify字段的问题：

       实际上，这应该是SQL Server特殊的类型之一，在Oracle中不存在这样的字段类型。不过，在Oracle中存在一个叫做序列的类型，它可以用来替代Identify字段。你可以认为，序列是Identify类型的扩大类型。具体的做法如下：

首先，我们要建一个递增的序列，如下：

    Create SEQUENCE temp_seq

            START WITH 1

            INCREMENT BY 1;

    然后，我们要对需要用自动递增字段的表做一个Trigger，如下：

    Create Trigger temp_trigger

        BEGIN

SELECT temp_seq.nextval INTO: new.identity FROM dual;

END;

    其中：temp_seq为刚才建的序列的名称；

          identity为需要自动递增的字段名称；

    这样，这个自动递增字段就完成了。在这里要说明的一点是，当我们在用MW导表时，Oracle已经自动地帮我们实现了上面的操作。因此，我们无需再用手工操作了。

 

6.     关于数据库中的Char和NChar类型字段问题；我们建议，在导到Oracle时，将其转换成VarChar2类型。

  7. Store Procedure & Function:

 

（2）  应用程序：同时居于SQL SERVER和ORACLE问题上。

       原则：尽量采用标准SQL语句的写法，考虑速度和性能的基础上，使SQL语句在两种平台上都可以运行。

1.     常见的查询Select语句中，尽量写清字段的名字，少使用*的方法，如果名字比较容易混淆，可以使用别名进行标示。

如 Select Hbdcnm as Name, Hbdlas … From Hrshhbd where …

2.     Insert Into TempTmp(field_list) values(… ) 注意不要缺省掉Field_list和 Into .  [在SQL SERVER中可以省掉Into，不过在ORACLE中不可以，所以Into不可省掉！]

 

3.     按照真实的表、字段等对象名称，区分大小写。

4.     SQL语句，适当分布多行进行书写，提高可读性。

 

如果不能做到统一，我们将采用如下方法修改：

在HRSPPRVB中增加一个方法常量，如

Public Function DataBase() as string

DataBase=“MSSQL”

‘DataBase=“ORACLE”

        End Function

 然后可以通过DataBase此标志位进行判断数据库平台，再做修改。

       注意以下几点:

1．  VB连接（ADO连接）的问题

    具体的实现如下：

    A.在SQL SERVER 中：

     ConnString="Provider=MSDataShape;Data Provider=SQLOLEDB;uid=hruser;

pwd=h&abcd;server=" & Server & ";database=hrleolo"

      B.在Oracle中：

         ConnString="Provider=MSDataShape;Data Provider=OraOLEDB.Oracle.1;

Persist Security Info=False;

User ID=siemenshr;password=siemens;Data Source=hrms"

       

2.     SQL Server的特殊语法规则问题

在SQL SERVER中，可以在Select语句中使用Top

--select top 1 wtbtwno,wtbtban,wtbtdte from hrswtbt  where

wtbtwno='0001001' order by wtbtdte

在Oracle中，可以使用RowNum进行替代。

  --select wtbtwno,wtbtban,wtbtdte from hrswtbt where wtbtwno='0001001' and rownum<2 order by wtbtdte

 

3.     注意Update语句的用法:

在SQL SERVER中:

   Update HrmsTmp Set Wsr=Sprprn from Hrshspr where Sprpnc= Wsr and Sprpst=2

在Oracle中：

   Update HrmsTmp Set Wsr=(select Sprprn from Hrshspr where Sprpnc= Wsr and Sprpst=2 )

所以采用Oracle中Update的方法。

 

4.     临时表的问题

      临时表是SQL Server中一个经常使用的对象，不过，它却成了我们在转Oracle的过程中遇到的最大的问题之一。其实，Oracle也有临时表的，但它的用法却和SQL Server的临时表有很大的区别。

    下面是我们总结的SQL Server临时表和Oracle临时表的区别：

SQL Server	Oracle
1、存在局部临时表，它的生命周期为它的创建者的Session的生命周期而且各Session之间可以创建同名的临时表	1、没有local temporary table的说法
2、存在全局临时表，它的生命周期也是和它的创建者的Session相同，也被手工Drop掉	2、也有global temporary，它的生命周期从创建开始，到手工Drop table结束
3、它在生命周期内，其它用户访问它得表结构	3、它在生命周期内,其它用户也可看到它的表结构
4、它的数据可以被其它用户查询、修改、删除	4、用户只可以操作自己的数据,不能访问和修改其它用户的数据，它的数据与Session_ID绑定
5、临时表的任何使用者都可以手工Drop，如果没有手工drop的话，它将在创建者的Session结束时自动删除；	5、当表中数据为空时，任何用户可以drop table，但当有数据时，不可以drop table，即使表中只有当前用户的数据； 在创建临时表时可以设定当提交(commit)时所要执行的动作可以将数据删除,也可以将数据保存

通过上面的比较，我们可以发现，两者对于临时表有很大的不同，最后采用的是修改程序中临时表的方法，即改为一般实际的表。

表名需动态的获得。我们采用的是下面这个公式：

表名 = Left(“TMP_”+CSTR(format(now,”yymmdd_hhmmss”) + format(Rnd(Timer()*1000000,”000000”) & “_” & format(Userid,”000”)

    （注意：在服务器我们会建一个Procedure，用于Drop掉中途出错的Temp table,所以一定要以TMP开头，当然手动建立的Temp Table 用完都要手动Drop掉！）

 

     在SQL SERVER中创建临时表：

       Select Field_List Into TMP1 from tableName where…(通过查询直接创建到临时表中)

     在ORACLE：

       CREATE TABLE　TMP1  AS Select Field_List from tableName where….   

 

　　 所以可以先采用Create Table..然后再Insert数据进来。以求统一。

    

     注意：如果在同一个Procedure中产生几个临时表，可能会产生Timer相同的情况，所以最好在设个标注位，比如命名临时表为TMP01_...  TMP02_...    

 

5. 关于空值：

   在SQL SERVER中，Int类型的栏位默认为0，Char类型默认为 '' ,在Oracle中，Int类型默认为0，而Char类型默认为一个空格 ' ', 在Oracle中，空字符串 '' 会被认为为Null,所以在做处理时应特别注意，移植为 ''à ' ', 如果往Oracle中允许为Null的栏位中插入 '' ,实际上是Null,所以

  用下列的语句将找不到 Select … from … where ..= ''

  应该用：Select … from … where … is Null

 

 

 

 

图解

SQL  数据库

存

取

Oracle 数据库

存

取

‘’

‘’

‘’

‘ ‘

‘ ’

null

 

 

 

 

 

 

 

 

 

 

 

 

 

  在SQL SERVER中：

     Case When … Then … Else … End   与  IsNull

  在Oracle中对应为：  

     Case When … Then … Else … End   与  Nvl( ) 与 Decode

  NVL(<字段名>,<置换值>)

 

6.关于关联：

  表的简单连接为表的交叉部分，如果希望在做表连接时能够列出某张表的全部数据，而不管它是否能与另一张表匹配，这时我们要用到特殊的连接方式：外连接。

两张表在做外连接时，所处的地位不是平等的，一张表处于支配地位，称为支配表，另一表处于被支配地位，称为被支配表。外连接的结果由两部分组成，一部分由来自两张表的满足条件的行匹配组成，另一部分由来自支配表中无法满足条件的行组成，结果中的第二部分数据在被支配表上的分量为null。

 

在SQL SERVER中可以使用Join,*=，=*等。Outer Join,Inner

在ORACLE中使用（+），如

select hbdwno ,hetdfr from hrshhbd,hrshhet where hbdwno=hetwno(+);

  

7. 数据类型：  

    在SQL SERVER:   Int    , Text            ,Image

在对应的ORACLE: Number , Varchar2(4000)  ,BLOB 

 

8.     Oracle 的函数表

1.     字符串运算符：

 

SQL SERVER:用‘+‘表示连接字符串连接

ORACLE:用“||”表示字符串连接,或者CONCAT

 

2．其他的Oracle函数：

函数名称	使用语法	用途
SYSDATE()	Sysdate()	取日期
LENGTH()/LENGTHB()	Length(string); LengthB(string)	取字符串的长度
ADD_MONTHS	ADD_MONTHS(DATE,MONTHS)	在一个日期上加上一个指定的月分数
CONVERT	CONVERT(SRING,DEST_CHAR_ SET,(SOURCE_CHAR_SET) STRING  :待转换的字符串 DEST_CAHR_SET: 目标字符集 SOURCE_CHAR_SET:源字符集	把一个字符串从一个字符集转换到另一个字符集
CAST（{Expresstion|subquery|Multiset } as type_name	转化函数
RTRIM	RITRIM(STRING,TRIMCHAR) STRING :任意VARCHAR2 或 CHAR 型数据 TRIMCHARS: 准备要压缩的字符串	压缩掉串右面得尾随字符串
TO_CHAR(FOR  DATE)	TO_CHAR(DATE,FMT,LANGUAGE) DATE  :任意一DATE 数据 FRM :日期格式 LANGUAGE: 使用语言	把日期型数据转换成字符型数据
TO_DATE	To_date(string,fmt,language) String:待转换的字符串 Fmt:日期格式 Language:使用语言	把字符型数据转换成日期格式
TO_CHAR(for Numbers)	To_char(number,fmt,nlsparams) Number:数据型数据 Fmt :数据格式 Nlsparams:数据语言	把数值型数据转换成字符型
ROUND(n,[m])	四舍五入和截断处理
TRUNC(n,[m])	只做截断，不做四舍五入
SubStr	select substr('adfdf',2,4) from dual	取子字符串

 

 SQL SERVER函数表：

函数名称	使用语法	用途
getdate()	Select getdate()	取服务器当前的日期
DataLength()	Select datalength(‘abc’)	得到字符串的长度
DATEADD ( datepart , number, date )	select DATEADD ( Month , 1, getdate() )
DATEDIFF ( datepart , startdate , enddate )	SELECT DATEADD(day, 21, pubdate) AS timeframe FROM titles
DATEPART ( datepart , date )	SELECT DATEPART(month, GETDATE())
ROUND(n,[m])	四舍五入和截断处理
CAST(expression AS data_type )	转化函数
CONVERT(data_type [ ( length ) ] , expression [ , style ] )	转化函数
Substring	select substring('aaasb',3,2)	取子字符串

 

有关Oracle的Function部分，我们将采用如下的方法： 把Oracle中没有而SQL SERVR中有的部分用自定义函数的方法生成，这样，对于函数的部分将统一，不用作修改。

9．在Oracle中，直接调用用户定义函数，但在SQL中，必须加上[DBO]., 如

   Select [DBO].Rcmd(usrpwd) from Hrspusr…

10．为了统一，在Oracle中增加一些在SQL SERVER中使用的函数，如：

[SQL SERVER]： Len ，Substring ，