BEGINNING SQL: DIFFERENCES BETWEEN SQL SERVER AND ORACLE
A Quick Intro for SQL Server Users
Introduction
If you're new to SQL or just new to Oracle SQL, perhaps coming from a Microsoft SQL Server environment, it may seem like the two versions should be very similar, and they are, to a certain degree, but they are also very different in some important and basic ways.
Agenda
I. Quick Intro for SQL Server Users II. Some Detail: Joins, Subqueries, Deletes III. Certain Conceptual Differences IV. Powerful New Features V. Summary & References
Dont Use Databases
SQL Server
use mydatabase
Oracle
connect mydatabase/mypassword
Use Dual
SQL Server
select getdate();
Oracle
select sysdate from dual;
Select Into
SQL Server
select getdate() mycolumn into mytable;
Oracle
insert into mytable (mycolumn) values(sysdate);
Inserts
SQL Server
Insert mytable values(more text);
Oracle
Insert into mytable values(more text);
Updates
SQL Server
update mytable set mycolumn=myothertable.mycolumn from mytable,myothertable where mytable.mycolumn like 'MY%' and myothertable.myothercolumn='some text';
Updates
Oracle
update mytable set mycolumn= (select a.mycolumn from myothertable a where myothertable.myothercolumn='some text ) where mytable.mycolumn like 'MY%';
Deletes
SQL Server
delete mytable where mycolumn like 'some%';
Oracle
delete from mytable where mycolumn like 'some%';
Software
SQL Server isql osql: for queries developed in SQL Analyzer
Oracle
sqlplus
II. A Little More Detail
Outer Join
Sub-Queries in Place of Columns Deletes With a Second From Clause
Outer Join
SQL Server select d.deptname, e.ename from dept d, emp e where d.empno *= e.enum; Oracle select d.deptname,e.ename from dept d, emp e where d.empno = e.enum (+);
SubQueries in Place of Columns SQL Server
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;
SubQueries in Place of Columns
Oracle
SELECT year, DECODE( quarter, DECODE( quarter, DECODE( quarter, DECODE( quarter, FROM sales s; 1, 2, 3, 4, amount, amount, amount, amount, 0 0 0 0 ) ) ) ) q1, q2, q3, q4
Delete with Second From Clause
SQL Server
delete from products from products, product_deletes where products.a = product_deletes.a and products.b = product_deletes.b and product_deletes.c = 'd';
Delete with Second From Clause
Oracle
delete from products where ( a, b ) in ( select a, b from product_deletes where c = 'd' );
III. More Depth
The Connect Concept
Other Conceptual Differences Data Type Differences Column Aliases Sub-Queries
The Connect Concept
SQL Server Multiple databases Oracle Single Database Multiple tablespaces, schemas, users
Other Conceptual Differences
SQL Server
Database owner, DBO Group/Role Non-unique index T-SQL stored procedure { Trigger Compex rule Column identity property
Oracle
Schema Role Index PL/SQL procedure PL/SQL function BEFORE trigger After trigger Sequence
Only in Oracle
Clusters
Packages Triggers for each row Synonyms Snapshots
Data Type Differences
SQL Server
INTEGER
Oracle
NUMBER(10)
SMALLINT
TINYINT REAL FLOAT BIT VARCHAR(n) TEXT IMAGE BINARY(n)
NUMBER(6)
NUMBER(3) FLOAT FLOAT NUMBER(1) VARCHAR2(n) CLOB BLOB RAW(n) or BLOB
Data Type Differences
SQL Server
VARBINARY
Oracle
RAW(n) or BLOB
DATETIME
SMALL-DATETIME MONEY NCHAR(n) NVARCHAR(n) SMALLMONEY TIMESTAMP SYSNAME
DATE
DATE NUMBER(19,4) CHAR(n*2) VARCHAR(n*2) NUMBER(10,4) NUMBER VARCHAR2(30), VARCHAR2(128)
Time
SQL Server Datetime: 1/300th second Oracle Date: 1 second Timestamp: 1/100 millionth second
Column Aliases
SQL Server select a=deptid, b=deptname,c=empno from dept; Oracle select deptid a, deptname b, empno c from dept;
Sub-queries, again
SQL Server
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);
Sub-queries, again
Oracle
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));
Powerful New Features
Regular Expressions: Operators & Functions
Operator: REGEXP_LIKE Functions: REGEXP_INSTR
REGEXP_SUBSTR REGEXP_REPLACE
Regular Expressions
Select zip from zipcode where regexp_like (zip, [^[:digit:]]);
Regular Expressions
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 912341234', ' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at FROM dual
Summary
This discussion has been an attempt at a light and lively introduction to the Oracle database world for those familiar with the Microsoft SQL Server database products. Much more in-depth examples are available in the references shown that follow, from which many of the examples were drawn and for which we can thank the authors involved. Welcome Aboard!
References
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 Oracle Technology Network, OTN:
http://otn.oracle.com/software/index.html
Writing Better SQL Using Regular Expressions, By
Alice Rischert
http://otn.oracle.com/oramag/webcolumns/2003/te charticles/rischert_regexp_pt1.html
