variable
varname := value SELECT value INTO varname
SET @varname = value SELECT @varname = value
Assigning to a variable from a cursor
FETCH cursorname INTO varname
FETCH NEXT FROM cursorname INTO varname
Declaring a cursor
CURSOR curname(params) IS SELECT ...;
DECLARE curname CURSOR FOR SELECT ...
If statement
IF ... THEN ELSIF ... THEN ELSE ENDIF
IF ... BEGIN ... END ELSE BEGIN ... END
While loop
WHILE ... LOOP END LOOP
WHILE ... BEGIN ... END
Other loops
FOR ... END LOOP LOOP ... END LOOP
<not supported>
Loop exit
EXIT, EXIT WHEN
BREAK, CONTINUE
Print output
DBMS_OUTPUT.PUT_LINE
PRINT
Raise error
RAISE_APPLICATION_ERROR
RAISERROR
Statement terminator
Semi-colon (;)
<none required>
Thanks to Tom Johnston for catching a mistake in this tip. I had the FROM DUAL in the wrong column.
--Fred
Differences in SQL Semantics
Last Updated: 6/6/1999 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following table shows some semantic differences between Oracle and MS SQL Server:
Description
Oracle
MS SQL Server
Commit
Explicit COMMIT statement required
Automatic commit unless SET IMPLICIT_TRANSACTIONS ON
Reading uncommitted data
Database does temporary internal rollback to reconstruct most recently committed data for reader.
Depending on options, reader as allowed to read uncommitted data, or is forced to wait for writer to commit or rollback.
Releasing cursor data
CLOSE CURSOR releases all data. You can''''t re-open.
CLOSE CURSOR does not release data. You must explicitly call DEALLOCATE CURSOR. Until then, you can re-open the cursor.
Implicit data conversion in a statement like the following where vc is a column of type VARCHAR2:
SELECT * FROM person WHERE vc =123
As each row is fetched from the table, an attempt is made to convert it to a number for the comparison with 123. If any row contains a value that cannot be converted to a number, a runtime error occurs.
The number 123 is converted to the string ''''123'''' once, and then the data is fetched from the table. If any row contains a value that cannot be converted to a number, it simply doesn''''t match ''''123'''' and is skipped without any error.
Conversion to NULL
Setting a VARCHAR2 column to '''''''' (the empty string) makes it NULL.
Setting a VARCHAR column to '''''''' makes it the empty string (not NULL).
--Fred
Differences in Managing Databases
Last Updated: 6/6/1999 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following table shows some differences in how databases are managed in Oracle and MS SQL Server:
Description
Oracle
MS SQL Server
Model database
No model database
Newly created databases inherit characteristics (users, etc.) from the special database named "model".
--Fred
Differences in Managing Database Objects
Last Updated: 6/6/1999 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following table shows some differences in how database objects (tables, views, stored procedures, etc.) are managed in Oracle and MS SQL Server:
Description
Oracle
MS SQL Server
Fully qualified name
[schema.]table [schema.]view
[[[server.][database].][owner].]table [[[server.][database].][owner].]view
Temp tables
Pre 8i: Temporary tables must be deleted explicitly
8i+: CREATE GLOBAL TEMPORARY TABLE
#table -- Any table named starting with a pound sign (#) is automatically deleted when the user logs off or the procedure ends. ##table -- Same as above, except that the table is accessible to other users.
Re-creating an object
CREATE OR REPLACE ...
DROP ... CREATE ...
Create view before dependent tables
CREATE FORCE VIEW
Not supported. Tables used by view must exist before view can be created.
--Fred
Differences in Managing Users
Last Updated: 6/6/1999 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following table shows some differences in how users are managed in Oracle and MS SQL Server:
Description
Oracle
MS SQL Server
Membership in groups
Each user can be a member of any number of groups.
Each user can be a member of only one group other than "public".
--Fred
Differences in Integration with MS ADO, RDO, etc.
Last Updated: 6/6/1999 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following table shows the different techniques used in Oracle and MS SQL Server to interact with MS ADO, RDO, etc.:
Description
Oracle
MS SQL Server
Return a recordset to the caller
Return a handle to a cursor. For more info: See MS KB article Q174679.
SELECT with no INTO clause; Multiple such SELECTs return multiple recordsets
--Fred
Miscellaneous Differences
Last Updated: 6/6/1999 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following table shows miscellaneous differences between Oracle and MS SQL Server:
Description
Oracle
MS SQL Server
Generate unique numbers
CREATE SEQUENCE
IDENTITY column of a table
Cascaded DELETE
DELETE CASCADE ...
(use triggers)
Call a user-defined function from a SQL statement (as column of SELECT or expression in WHERE clause)
supported
not supported
--Fred
See Also
Last Updated: 3/3/2001 Applies to: Oracle 7.3+, MS SQL Server 6.5+
The following are good sources of info about differences between Oracle and MS SQL Server:
Bowman, Judith S., Sandra L. Emerson, and Marcy Darnovsky. The Practical SQL Handbook. Addison-Wesley Publishing Company, 1993. ISBN 0-201-62623-3. This book gives a good introduction to SQL, with a slight emphasis on Sybase, but with a useful summary in the back of the syntax for each of the SQL statements (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REVOKE, etc.) for each of the major databases (Oracle, Sybase, DB2, Informix, Ingres, etc.) The book pre-dates MS SQL Server, but the Sybase info is a good approximation since MS SQL Server is a derivative of Sybase.
"Migrating Oracle Applications to SQL Server" on MSDN CD, and at MS TechNet Web site: http://www.microsoft.com/TechNet/sql/Tools/Sqldevkt/ORCL2SQL.asp Microsoft clearly intended this to be used in one direction only, but I''''ve used it quite successfully to translate my SQL Server knowledge to Oracle as well.
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
