1.部分SQL语句差异
(1)SQL:select top 10 * from table
ORA: select * from table where rownum<11
(2)SQL:Select * from t1 join t2 on t1.c1=t2.c1
ORA: select * from t1,t2 where t1.c1=t2.c1
(3)select * from t1 left join t2 on t1.c1=t2.c1
ORA: select * from t1,t2 where t1.c1=t2.c1(+)
(4)SQL:select * from t1 right join t2 on t1.c1=t2.c1
ORA: select * from t1,t2 where t1.c1(+)=t2.c1
ORA: select * from table where rownum<11
(2)SQL:Select * from t1 join t2 on t1.c1=t2.c1
ORA: select * from t1,t2 where t1.c1=t2.c1
(3)select * from t1 left join t2 on t1.c1=t2.c1
ORA: select * from t1,t2 where t1.c1=t2.c1(+)
(4)SQL:select * from t1 right join t2 on t1.c1=t2.c1
ORA: select * from t1,t2 where t1.c1(+)=t2.c1
2. 其他技术差异
GPRS技术可以应用的领域有:
◆在oracle中,delete语句中的from从句中不能涉及到多个表,要基于多个表进行删除记录只有使用子查询。;
◆在oracle中,update语句不能基于多个表进行修改数据,要基于多个表进行修改数据只有使用子查询;
◆在oracle中,存储过程不能返回记录集,如果要存储过程返回记录集,可以创建视图,可以单独执行select语句 (注意:并不是说在存储过程中不能使用select语句,但select只能用于以下情况:insert into table select * from t1 /select col1 into a from table where ……(a为一变量));
◆在oracle中insert 的语法是:insert into table values() 或insert into table select * from t1;;
◆Oracle中的日期型常量必须用to_date()函数得到。;
◆Dual表是一个虚拟表,专门用于测试的目的用,注意dual表的用法:
在sql server中,如果我们项测试某个函数,例如:select convert(int,’13’) ,在 oracle 中这种表示方法是错误的,应表示为:select to_number(‘13’) from dual;;
◆得到相差的天数:两个日期相减,再用ceil或floor函数取整得到相差的月数:months_between()
◆数据类型的转换;
◆ORACLE中对象名不能超过30个字符。ORACLE中界限符是“”(对应SQLSERVER中的[ ])(我的建议是:最好不要是用界限符,能避免实用系统的保留字就尽量避免);
◆ORACLE中空字符串被看成null;
◆ORACLE中字符的比较是区分大小写的;
◆视图中如果用了order by ,并且order by 从句中的字段名都是别名,那么,在显示视图时必须显示出order by 子句中的所有字段,例如,有下面这样一个视图:
create view v
as
select name as c1,address from t1 order by c1
那么在select 该视图时
select c1,address from v 是正确的, select address from v是错误的;
◆关于临时表的问题
在sqlserver中 create table #table1 (c int);
在oracle 中解决办法是:
create global temporary table table1(c int) on commit preserve rows
这个table1在数据库中是永久存在的,所以不要是用drop命令,在你的程序中在对这个表进行任何操作之前,先执行truncate table table1,切忌不要用drop命令删除此表。;
◆ http://technet.oracle.com/doc/server815.htm这个网站有许多在线教程,但一定要先注册;
3. 数据类型转换
SQL SERVER ORACLE
bigint NUMBER (19 ,0)
binary (50) RAW (50)
bit NUMBER (1, 0)
char (10) CHAR (10)
datetime DATE
decimal (18 , 0) NUMBER (18, 0)
float FLOAT
image BLOB
int NUMBER (10, 0)
money NUMBER (19 ,4)
nchar (10) CHAR (20)
ntext CLOB
numeric (18 , 0) NUMBER (18 ,0)
nvarchar (50) VARCHAR2 (100)
real FLOAT
smalldatetime DATE
smallint NUMBER (5, 0)
smallmoney NUMBER (10, 4)
sql_variant LONG RAW
text CLOB
timestamp RAW (8) NOT
tinyint NUMBER (3 ,0)
uniqueidentifier LONG RAW
varbinary (50) RAW (50)
varchar (50) VARCHAR2 (50)
bigint NUMBER (19 ,0)
binary (50) RAW (50)
bit NUMBER (1, 0)
char (10) CHAR (10)
datetime DATE
decimal (18 , 0) NUMBER (18, 0)
float FLOAT
image BLOB
int NUMBER (10, 0)
money NUMBER (19 ,4)
nchar (10) CHAR (20)
ntext CLOB
numeric (18 , 0) NUMBER (18 ,0)
nvarchar (50) VARCHAR2 (100)
real FLOAT
smalldatetime DATE
smallint NUMBER (5, 0)
smallmoney NUMBER (10, 4)
sql_variant LONG RAW
text CLOB
timestamp RAW (8) NOT
tinyint NUMBER (3 ,0)
uniqueidentifier LONG RAW
varbinary (50) RAW (50)
varchar (50) VARCHAR2 (50)