本文参考"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' );
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