·Oracle初学者必知的100个问题·Oracle PL/SQL语言基础·简单的Oracle存储过程的创建方法（菜鸟·Oracle架构的基础知识(入门级)·ORACLE中的各种数据类型详细的介绍·Oracle触发器详细介绍·简单的Oracle存储过程的创建方法·oracle的nvl函数的用法·Oracle的初学者入门心得·转一个很好的Oracle贴子
本文参考"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' );
·Oracle初学者必知的100个问题·Oracle PL/SQL语言基础·简单的Oracle存储过程的创建方法（菜鸟·Oracle架构的基础知识(入门级)·ORACLE中的各种数据类型详细的介绍·Oracle触发器详细介绍·简单的Oracle存储过程的创建方法·oracle的nvl函数的用法·Oracle的初学者入门心得·转一个很好的Oracle贴子
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