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 ) |
|
|
DATEPART ( datepart , date ) |
|
|
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 ,
posted on 2005-03-01 10:46 Day Day Up 阅读(1367) 评论(0) 编辑 收藏 举报