本文详细比较了Oracle和SQLServer在字段类型、脏读处理、表定义、存储过程/函数、自增字段等方面的语法差异，对于数据库开发者具有较高的参考价值。
a) oracle各类型字段描述
数据类型 | 取值范围 | 描述 | 备注 |
Char(n) | n=1 to 2000字节 | 存储固定长度的字符串。默认长度为1。 |
|
Varchar2(n) | n=1 to 4000字节 | 可变长的字符串，具体定义时指明最大长度n， | 节省表空间：一个空的varchar2(2000)字段和一个空的varchar2(2)字段所占用的空间是一样的。 |
Nchar(n) | n=1 to 2000字节 | 在创建数据库时，需要指 定所使用的字符集，以便对数据库中数据进行编码。还可以指定一个辅助的字符集［即本地语言集（National Language Set，简称NLS）］。存储固定长度的字符串。 | 与char的差别是，Nchar是基于NLS的国家字符集，实际开发很少用。 |
Nvarchar2(n) | n=1 to 4000字节 | 在创建数据库时，需要指定所使用的字符集，以便对数据库中数据进行编码。还可以指定一个辅助的字符集［即本地语言集（National Language Set，简称NLS）］。存储变长字符串。 | 与varchar2的差别是，Nvarchar2是基于NLS的国家字符集，实际开发很少用。 |
Long | 最大2G | 可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。 | 在不同系统间移动需要进行转换。 |
Clob | 最大4G | 存储单字节字符型数据。适用于存储超长文本。 |
|
Nclob | 最大4G | 存储多字节国家字符型数据。适用于存储超长文本。 |
|
Blob | 最大4G | 存储二进制数据。适用于存储图像、视频、音频等。 |
|
Bfile | 最大长度是4GB | 在数据库外部保存的大型二进制对象文件，最大长度是4GB。这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。 Oracle可以读取、查询BFILE，但是不能写入，不参与事务。 |
|
Number(m,n) | m=1 to 38 | 存储整数或浮点数。可变长的数值列，允许0、正值及负值，m是所有有效数字的位数，n是小数点以后的位数。 |
|
Date |
| 数据类型用来存储日期和时间格式的数据。默认格式：DD-MON-YYYY。从公元前4712年1月1日到公元4712年12月31日的所有合法日期 |
|
Row(n) | n=1 to 2000 | 可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle用这种格式来保存较小的图形文件或带格式的文本文件，如Miceosoft Word文档。 |
|
LongRow | 最大长度是2GB。 | 可变长二进制数据，最大长度是2GB。Oracle用这种格式来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件。 |
|
Rowid |
| 数据类型是ORACLE数据表中的一个伪列，它是数据表中每行数据内在的唯一的标识。 |
|
b) 字段类型比较
| SQL SERVER | ORACLE |
数字类型 | DECIMAL[(P[, S])] | NUMBER[(P[, S])] |
NUMERIC[(P[, S])] | NUMBER[(P[, S])] | |
FLOAT[(N)] | NUMBER[(N)] | |
INT | NUMBER | |
SMALLINT | NUMBER | |
TINYINT | NUMBER | |
MONEY | NUMBER[19,4] | |
SMALLMONEY | NUMBER[19,4] | |
字符类型 | CHAR[(N)] | CHAR[(N)] |
VARCHAR[(N)] | VARCHAR2[(N)] | |
日期时间类型 | DATETIME | DATE |
SMALLDATETIME | DATE | |
其它 | TEXT | CLOB |
IMAGE | BLOB | |
BIT | NUMBER（1） |
c) 常用函数比较
注：此处仅记录语法有区别的地方，更多丰富的语法请查看附件中的手册。
以下的exp为expression的缩写
Sql server | orcale | |
字符类函数 | ||
Ascii(char_exp) | Ascii(str_exp) | |
Char(int_exp) | Chr(int_exp) | |
Datalength(char_exp) | Length(str_exp) | |
Substring(exp, start, length) | Substr(exp, start, length) | |
Upper(char_exp) | Upper(str_exp) | |
Lower(char_exp) | Lower(str_exp) | |
Stuff(char_exp1,start,length, Char_exp2) | Translate(str_exp,from_str,to_str) | |
Ltrim(char_exp) | Ltrim(str_exp1 [,str_exp2]) | |
Rtrim(char_exp) | Rtrim(str_exp1 [,str_exp2]) | |
日期类函数 | ||
Getdate() | Sysdate | |
数学类函数 | ||
Abs(numeric_exp) | Abs(number_exp) | |
Ceiling(numeric_exp) | Ceil(number_exp) | |
Exp(float_exp) | Exp(number_exp) | |
Floor(numeric_exp) | Floor(number_exp) | |
Power(numeric_exp,int_exp) | Power(number_exp1,number_exp2) | |
Round(numeric_exp,int_exp) | Round(number_exp1 [,number_exp2]) | |
Sign(int_exp) | Sign(number_exp) | |
Sqrt(float_exp) | Sqrt(number_exp) | |
转换函数 | ||
Convert(datatype[(length)],exp,format) | To_char(datatype,str_format) | |
Convert(datatype[(length)],exp,format)s | To_date(str_exp,date_format) | |
Convert(datatype[(length)],exp,format) | To_number(str_exp,num_format) | |
其它函数 | ||
AVG([ALL | DISTINCT] col) | AVG([ALL | DISTINCT] col) | |
COUNT({[ALL | DISTINCT] col] | *}) | COUNT({[ALL | DISTINCT] col} | *)) | |
MAX([ALL | DISTINCT] col) | MAX([ALL | DISTINCT] col) | |
MIN([ALL | DISTINCT] col) | MIN([ALL | DISTINCT] col) | |
SUM([ALL | DISTINCT] col) | SUM([ALL | DISTINCT] col) | |
STDEV(col) | STDDEV(col) | |
VAR(col) | VARIANCE(col) | |
ISNULL(check_exp, replace_value) | NVL(check_exp, replace_value) | |
CASE | DECCODE | |
|
|
|
d) 关于脏读
目前我们使用的sqlserver 数据库，在查询数据时，都要求在select语句中后面跟一个（nolock）或（with nolock）来保证读取大表时不影响其它程序进程的数据操作。
在oracle中，目前还不允许脏读的方式，在每次select 后，读到的数据都是已经commit的数据，所以为了避免读取数据的不准确，程序中如果有比较耗时的sql操作，尤其是在insert 和 update后，尽量减小事务，而且要在事务结束后及时commit。
e) 表（主键、外键、CHECK、UNIQUE、DEFAULT、INDEX）
在创建表及其主键、外键、CHECK、UNIQUE、DEFAULT、INDEX时，SQL SERVER 与ORACLE的语法大致相同。主要区别如下：
Oracle定义表字段的default属性紧跟字段类型之后，如下：
Create table MZ_Ghxx
( ghlxh number primay key ,
rq date default sysdate not null,
….
)
而不能写成
Create table MZ_Ghxx
( ghlxh number primay key ,
rq date not null default sysdate,
….
)
f) 存储过程/函数 结构的不同
SQLSERVER中存储过程的结构大致如下
CREATE PROCEDURE procedure_name
/*输入、输出参数的声明部分*/
AS
DECLARE
/*局部变量的声明部分*/
BEGIN
/*主体SQL语句部分*/
/*游标声明、使用语句在此部分*/
END
ORACLE中存储过程的结构大致如下
CREATE OR REPLACE PROCEDURE procedure_name
（ /*输入、输出参数的声明部分*/ )
AS
/*局部变量、游标等的声明部分*/
BEGIN
/*主体SQL语句部分*/
/*游标使用语句在此部分*/
EXCEPTION
/*异常处理部分*/
END ；
ORACLE端FUNCTION语法说明
CREATE [OR REPLACE] FUNCTION function_name
[(argument [{IN | OUT | IN OUT }] ) type,
…
[(argument [{IN | OUT | IN OUT }] ) type
RETURN return_type {IS | AS}
BEGIN
…
END;
变量赋值
在SQL SERVER语句中用如下语句对局部变量赋值（初始值或数据库表的字段值或表达式）：
“SELECT 局部变量名 = 所赋值（初始值或数据库表的字段值或表达式）”；
而在ORACLE中，将初始值赋给局部变量时，用如下语句：
“局部变量名 : = 所赋值（初始值或表达式）;” ，
将检索出的字段值赋给局部变量时，用如下语句：
“SELECT 数据库表的字段值 INTO 局部变量名 …” 。
g) 自增字段的实现不同
以下比较以iBatis的sqlMap配置文件说明为例
Sqlserver
在建表时，自增字段需要设置为identity类型
ibatis配置如下
<insert id="insertDept" parameterClass="com.domain.DEPT"> <![CDATA[insert into dept(dname,loc) values(#dname#,#loc#); ]]> <selectKey keyProperty="deptno" resultClass="int"> select @@identity as value </selectKey> </insert> |
oracle
首先需要在建库时建好相应的序列。如下：
1、在scott用户下建立表dept(其实oracle缺省安装就已经生成了) create table DEPT ( DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14), LOC VARCHAR2(13) )
2、建立一个sequence create sequence seq_dept minvalue 1 start with 1 increment by 1 nocache;
3、取下一个序列 SELECT seq_dept.nextval AS deptno FROM dual |
ibatis配置如下
<insert id="insertDept" parameterClass="com.domain.DEPT"> <selectKey resultClass="int" keyProperty="DEPTNO" type="pre"> <![CDATA[SELECT seq_dept.nextval AS deptno FROM dual]]> </selectKey> <![CDATA[insert into dept(dname,loc) values(#dname#,#loc#) ]]> </insert> |
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
