Oracle 和 MIcrosoft SQL 的不同[6]|中文方案文档站(提供各类方案下载,WORD文档下载,范文,案例等)

thanks to tom johnston for catching a mistake in this tip. i had the from dual in the wrong column.

--fred

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

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

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.

Oracle 和 MIcrosoft SQL 的不同[6]