SQL Server开发者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' );

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