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})?