TranslatingProceduralStatements_Oracle_SQLServer.pdf

(331 KB) Pobierz
Translating Procedural
Statements Between
Oracle and SQL Server
White Paper
®
written by
Kevin Kline, Director of Technology
SQL Server Solutions
Applications and Database Management
Quest Software, Inc.
© Copyright Quest® Software, Inc. 2005. All rights reserved.
The information in this publication is furnished for information use only, does not constitute
a commitment from Quest Software Inc. of any features or functions discussed and is
subject to change without notice. Quest Software, Inc. assumes no responsibility or
liability for any errors or inaccuracies that may appear in this publication.
Last revised: June 2005
T
ABLE OF
C
ONTENTS
OVERVIEW ................................................................................................................................... 4
HISTORY ...................................................................................................................................... 5
THE RULES .................................................................................................................................. 6
I
DENTIFIERS
................................................................................................................................ 6
D
ATATYPE
D
IFFERENCES
............................................................................................................ 7
V
ARIABLES AND
P
ARAMETERS
..................................................................................................... 8
BEGIN…END D
IFFERENCES
...................................................................................................... 9
C
ONTROL
S
TRUCTURES
.............................................................................................................. 9
LOOP S
TRUCTURES
................................................................................................................. 10
C
URSORS
................................................................................................................................. 11
E
XCEPTION
H
ANDLING AND
E
RROR
C
ONTROL
............................................................................ 13
PROCEDURES, FUNCTIONS,
AND
TRIGGERS..................................................................... 14
PACKAGES............................................................................................................................. 15
B
UILT
-I
N
’
S
................................................................................................................................. 16
S
OME
T
RANSLATIONS OF
C
OMMONLY
U
SED
C
OMMANDS
............................................................ 17
SUMMARY.................................................................................................................................. 18
ABOUT QUEST SOFTWARE, INC. ........................................................................................... 19
C
ONTACTING
Q
UEST
S
OFTWARE
............................................................................................... 19
T
RADEMARKS
............................................................................................................................ 19
Translating Procedural Statements Between Oracle and SQL Server - Kevin Kline, Director of Technology,
SQL Server Solutions, Quest Software, Inc.
3
O
VERVIEW
This white paper is intended to give you a
QUICK
but thorough overview of the issues you
may encounter when translating procedural code, such as stored procedures or user-
defined functions, between Oracle’s PL/SQL and Microsoft SQL Server’s Transact-SQL
procedural extensions to the ANSI SQL standard. The document should be equally useful
for SQL Server DBAs and developers who need to become familiar with PL/SQL, as for
Oracle DBAs and developers who want to get up to speed quickly on Transact-SQL
Translating Procedural Statements Between Oracle and SQL Server - Kevin Kline, Director of Technology,
SQL Server Solutions, Quest Software, Inc.
4
H
ISTORY
Microsoft does not issue separate releases Transact-SQL from the database. When
thinking about the version number of a given set of Transact-SQL code, you must really
think about the version number of the database (and service pack) that the code was
written for.
Oracle, on the other hand, issues releases of PL/SQL separately from the database.
Why? Oracle has long supported PL/SQL not only with the relational database, but also
in other development products like Oracle Forms and Oracle Reports. Consequently,
certain releases of the procedural language were necessary to provide features for the
separate client and server applications. Oracle is now in its 8.x release of PL/SQL, but
other prominent releases include 1.x and 2.x.
Another interesting aspect of PL/SQL is that it has shared roots with the ADA language,
most prevalent in the Department of Defense of the U.S. federal government. It shares
many of ADA’s capabilities including extensive exception handling features and
distinctive block structure.
Oracle also supports Java as a procedural language within the relational database.
Although there doesn’t seem to be a high rate of adoption in the user community, it is
possible to write all of your stored procedures and user-defined functions on an Oracle
database using Java. Microsoft SQL Server will offer similar capabilities, such as writing
stored procedures in C# or VB.Net, in the next release of SQL Server.
Translating Procedural Statements Between Oracle and SQL Server - Kevin Kline, Director of Technology,
SQL Server Solutions, Quest Software, Inc.
5

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin