本文参考"Beginning SQL: Differences Between SQL Server and Oracle-A Quick Intro for SQL Server Users",作者Les Kopari
本文比较适合刚接触Oracle的SQL Server开发人员.
I. 简单概念的介绍
1. 连接数据库
S: use mydatabase
O: connect username/password@DBAlias
conn username/password@DBAlias
2. 在Oracle中使用Dual, Dual是Oracle一个特有的虚拟表, Oracle中很多系统的变量和函数都可以通过Dual中获得
S: select getdate();
O: select sysdate from dual;
3. Select Into和Insert 语句的使用, 在SQL Server中的Select Into语句在Oracle中一般是Insert into…select…, 另外2个数据库都支持标准的SQL, 写法上略有区别
S: select getdate() mycolumn into mytable;
Insert mytable values(‘more text’);
O: insert into mytable select getdate() mycolumn from dual
insert into mytable (mycolumn) values(sysdate);
4. Update语句
S: update mytable set mycolumn=myothertable.mycolumn
from mytable,myothertable
where mytable.mycolumn like 'MY%' and myothertable.myothercolumn='some text';
O: update mytable set mycolumn=
(select a.mycolumn from myothertable a
where myothertable.myothercolumn='some text')
where mytable.mycolumn like 'MY%';
5. Delete语句
S: delete mytable where mycolumn like 'some%';
O: delete from mytable where mycolumn like 'some%';
6. 使用开发管理的软件
S: isql
osql: for queries developed in SQL Analyzer
SQL Server Management Studio Express 图形化管理工具
O: sqlplus
PL/SQL Developer 图形化开发管理工具
TOAD 图形化开发管理工具
注: 个人建议基本的简单的Select, Update, Delete使用标准的SQL语句,如SQL92或SQL99的定义
II. 一些细节问题: Joins, Subqueries, Deletes
1. Outer Join 外连接
S: select d.deptname, e.ename from dept d, emp e where d.empno *= e.enum;
O: select d.deptname,e.ename from dept d, emp e where d.empno = e.enum (+);
2. SubQueries in Place of Columns
S: select distinct year,
q1 = (select Amount amt FROM sales where Quarter=1 AND year = s.year),
q2 = (SELECT Amount amt FROM sales where Quarter=2 AND year = s.year),
q3 = (SELECT Amount amt FROM sales where Quarter=3 AND year = s.year),
q4 = (SELECT Amount amt FROM sales where Quarter=4 AND year = s.year)
from sales s;
O: SELECT year,
DECODE( quarter, 1, amount, 0 ) q1,
DECODE( quarter, 2, amount, 0 ) q2,
DECODE( quarter, 3, amount, 0 ) q3,
DECODE( quarter, 4, amount, 0 ) q4
FROM sales s;
3. Delete with Second From Clause
S: delete from products, product_deletes
where products.a = product_deletes.a
and products.b = product_deletes.b
and product_deletes.c = 'd';
O: delete from products
where (a, b ) in
(select a, b from product_deletes where c = 'd' );
#日志日期：2007-9-30 星期日(Sunday) 晴
评论人：漂流的河 评论日期：2007-9-30 10:41
III. 某些概念上的区别
1. The Connect Concept
S: Multiple databases
O: Single Database, Multiple tablespaces, schemas, users
2. Other Conceptual Differences
SQL Server Oracle
Database owner, DBO Schema
Group/Role Role
Non-unique index Index
T-SQL stored procedure{ PL/SQL procedure; PL/SQL function
Trigger BEFORE trigger After trigger
Column identity property Sequence
Oracle中独有的概念, SQL Server2005中也开始支持了:
Clusters; Packages; Triggers for each row; Synonyms; Snapshots
3. Data Type Differences
SQL Server Oracle
INTEGER NUMBER(10)
SMALLINT NUMBER(6)
TINYINT NUMBER(3)
REAL FLOAT
FLOAT FLOAT
BIT NUMBER(1)
VARCHAR(n) VARCHAR2(n)
TEXT CLOB
IMAGE BLOB
BINARY(n) RAW(n) or BLOB
VARBINARY RAW(n) or BLOB
DATETIME DATE
SMALL-DATETIME DATE
MONEY NUMBER(19,4)
NCHAR(n) CHAR(n*2)
NVARCHAR(n) VARCHAR(n*2)
SMALLMONEY NUMBER(10,4)
TIMESTAMP NUMBER
SYSNAME VARCHAR2(30), VARCHAR2(128)
时间上:
S: Datetime: 1/300th second
O: Date: 1 second
Timestamp: 1/100 millionth second
4. 列别名
S: select a=deptid, b=deptname,c=empno from dept;
O: select deptid a, deptname b, empno c from dept;
5. 子查询
S: SELECT ename, deptname
FROM emp, dept
WHERE emp.enum = 10 AND
(SELECT security_code FROM employee_security
WHERE empno = emp.enum) =
(SELECT security_code FROM security_master
WHERE sec_level = dept.sec_level);
O: SELECT empname, deptname
FROM emp, dept
WHERE emp.empno = 10
AND EXISTS
(SELECT security_code FROM employee_security es
WHERE es.empno = emp.empno AND es.security_code =
(SELECT security_code FROM security_master
WHERE sec_level = dept.sec_level));
IV. 强大的新特性
正则表达式的支持, SQLServer2005查找和替换可以用正则表达式
Regular Expressions: Operators & Functions
Operator: REGEXP_LIKE
Functions: REGEXP_INSTR; REGEXP_SUBSTR; REGEXP_REPLACE
Select zip from zipcode where regexp_like (zip, ‘[^[:digit:]]’);
SELECT REGEXP_INSTR(
'Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',
' [[:digit:]]{5}(-[[:digit:]]{4})?$')
AS starts_at
FROM dual
V. 总结和更深入的探讨
以上属于比较基本的探讨,更深入的内容可以参考下面的内容
1. Oracle Migration Workbench Reference Guide for SQL Server and Sybase Adaptive Server Migrations, Release 9.2.0 for Microsoft Windows 98/2000/NT and Microsoft Windows XP, Part Number B10254-01
2. Oracle Technology Network, OTN:
http://otn.oracle.com/software/index.html 3. Writing Better SQL Using Regular Expressions, By Alice Rischert
http://otn.oracle.com/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html直接百度来的答案，要我自己还真说不出来这么多。