Oracle 和 MIcrosoft SQL 的不同

还是有很多的不同,转贴如下:http://www.bristle.com/Tips/SQL.htm#Oracle%20Tips

Oracle Tips 1. SQL Tips
1. SELECT * and more
2. Materialized View 2. PL/SQL Tips 3. SQL Navigator Tips 4. See Also
MS SQL Server Tips 1. SQL Tips
1. Dynamic SQL in a Stored Procedure 2. SQL Enterprise Manager Tips
2. Keyboard Shortcuts
3. SQL Generating SQL 3. See Also
Differences Between Oracle and MS SQL Server 1. Concepts and Terminology 2. Data Types 3. Limits 4. Operators 5. Built-In Functions 6. Differences in SQL Syntax 7. Differences in SQL Semantics 8. Differences in Managing Databases 9. Differences in Managing Database Objects 10. Differences in Managing Users 11. Differences in Integration with MS ADO, RDO, etc. 12. Miscellaneous Differences 13. See Also

Details of Tips:

Oracle Tips

SQL Tips

This section contains tips on standard SQL (Structured Query Language) statements in Oracle.

SELECT * and more

Last Updated: 6/6/1999
Applies to: Oracle 7.3, 8 (and probably earlier versions)

To select all columns of a table:

	select * from table

However, to select all real columns, plus a pseudo-column like "user":

	select **table.** *, user from table

The following does not work:

	select *, user from table

--Fred

Materialized View

Last Updated: 1/7/2002
Applies to: Oracle 8+

Oracle 8i introduced a new feature called a "materialized view". You define it just like any other view, except that you add the keyword MATERIALIZED :

	CREATE MATERIALIZED VIEW view_name

A materialized view is like a combination of a table and a view. Like a view, it is defined as a logical view into the data of one or more tables. When you update the tables, subsequent queries of the view see the updated data. However, like a table, its data is stored in the database. Also, like a table, it is faster if you define indexes for it.

A regular view is stored as a mapping of data from tables. When you modify the data in the tables, the view is completely ignored. When you access the view, it joins the data currently in the tables, and returns the data you requested. A materialized view is stored as such a mapping along with a copy of the actual data from the tables. When you modify the data in the tables, the view's copy of the data is also updated. When you access the view, the data is drawn directly from the copy.

Thus a materialized view makes table updates a little slower, but makes view queries much faster. It also consumes additional space in the database.

You could accomplish the same effect by defining an additional table instead of the view, and using triggers on the component tables to update it each time they are changed. However, using a materialized view is more convenient, more efficient, and clearer to the next person who has to maintain your database.

Thanks to Andy Glick for sending me a sample of a materialized view from his application!

--Fred

PL/SQL Tips

This section contains tips on PL/SQL statements -- the Oracle "procedural language" superset of SQL that you use to write stored procedures.

SQL Navigator Tips

This section contains tips on the SQL Navigator tool by Quest Systems. It is a graphical front end to the Oracle database, allowing you to create, delete, view, and modify all Oracle objects: tables, views, stored procedures, etc.

See Also

Last Updated: 6/6/1999
Applies to: Oracle 7.3+

The following are good sources of info about Oracle:

Koch, George, and Kevin Loney. Oracle 8, The Complete Reference . Berkeley CA: For Oracle Press by Osborne McGraw-Hill, 1997. ISBN 0-07-882396-X.
This book includes introductory database concepts as well as a complete reference to Oracle SQL and PL/SQL statements. The companion CD contains a complete copy of the book, so you can read it on-line, search it, etc.
Any of the O'Reilly books. I've been very impressed by all of the O'Reilly books since my early Unix and X-Windows days in the 80's, and they have a complete series on Oracle, covering PL/SQL, the standard packages, etc.

--Fred

MS SQL Server Tips

SQL Tips

This section contains tips on SQL (Structured Query Language) statements in MS SQL Server.

Dynamic SQL in a Stored Procedure

Last Updated: 2/7/1999
Applies to: MS SQL Server 6.5+

A typical tradeoff for a database application is dynamic SQL (SQL commands embedded in the application -- for flexibility) vs. stored procedures (pre-compiled SQL procedures stored in the database and invoked by name from the application -- for speed and control over what SQL statements get executed). However, you can have the best of both worlds by using dynamic SQL inside your stored procedures. In a stored procedure, you can use the EXEC statement to execute a string of SQL statements that you built dynamically in the stored procedure or read from the database or any other data source.

Thanks to Steve Rhoads for this tip.

--Fred

SQL Enterprise Manager Tips

This section contains tips on the SQL Enterprise Manager tool. It is a graphical front end to the database, allowing you to create, delete, view, and modify all MS SQL Server objects: tables, views, stored procedures, etc.

Keyboard Shortcuts

Last Updated: 6/20/1999
Applies to: MS SQL Server 7.0

Here is a list of some of the more useful shortcut keys in SQL Enterprise Manager.

Key	Function
F1	Help on SQL Enterprise Manager
Shift-F1	Help on syntax of current SQL statement
Ctrl-E	Execute selected text in Query Analyzer
Ctrl-R	Hide/show results pane in Query Analyzer

Obviously, this list is far from complete. Please feel free to mail me your favorite shortcuts. I'll add to this list as time permits.

Concepts and Terminology

Last Updated: 4/24/2001
Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows some differences in concepts and terminology between Oracle and MS SQL Server:

Concept/Term	Oracle	MS SQL Server
Database engine	database	database server
Database (collection of tables)	schema	database
Roles/Groups	roles	groups
Database adminstrator account, database owner	dba	sa, dbo
Data about the database	Data Dictionary
- one per server	Database Catalog
- one per database
"master" database
- one per server
Blocks and extents	blocks and extents	pages and extents
Network software	SQL*Net	Net-library
Data stream protocol	Transparent Network Substrate (TNS)	Tabular Data Stream (TDS)
Case sensitivity of names of tables, columns, etc.	case-insensitive	depends on character sort order, default is case-insensitive
Synonyms	supported	not supported
Readonly transaction	supported	not supported

--Fred

Data Types

Last Updated: 6/6/1999
Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows the corresponding data types in Oracle and MS SQL Server:

Data Type	Oracle	MS SQL Server
Fixed Length String	CHAR(n)
- limit 2KB	CHAR(n), CHARACTER(n)
- limit 255 (6.5)
- limit 8KB (7.0)
Variable Length String	VARCHAR2(n), VARCHAR(n)
- limit 4KB in a column
- limit 32KB in a variable
- VARCHAR is obsolete
VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)
- limit 255 (6.5)
- limit 8KB (7.0)
Integer	INTEGER, INTEGER(n), SMALLINT	INTEGER (4 bytes),
INT (4 bytes),
SMALLINT (2 bytes),
TINYINT (1 byte),
BIT (1 bit)
Fixed Point	NUMBER, NUMBER(n), NUMBER(n,d),
FLOAT, FLOAT(n), FLOAT(n,d)	NUMERIC, NUMERIC(n), NUMERIC(n,d),
DECIMAL, DECIMAL(n), DECIMAL(n,d),
DEC, DEC(n), DEC(n,d),
MONEY, SMALLMONEY
Floating Point	DECIMAL	FLOAT, FLOAT(n), DOUBLE PRECISION,
REAL,
Date	DATE	DATETIME, SMALLDATETIME, TIMESTAMP
- TIMESTAMP auto-updated
Binary	RAW(n)
- limit 255 bytes	BINARY(n), VARBINARY(n), BINARY VARYING(n)
- limit 255 (6.5)
- limit 8KB (7.0)
Large String	LONG, LONG VARCHAR
- limit 2GB
- limit one per table row
CLOB
- limit 4GB	TEXT
- limit 2GB
Large Binary	LONG RAW
- limit 2GB
- limit one per table row
BLOB
- limit 4GB	IMAGE
- limit 2GB
Multi-byte chars	NCHAR(n)
NVARCHAR(n)
NCLOB
- same limits as CHAR, VARCHAR, CLOB	NCHAR(n), NATIONAL CHAR(n), NATIONAL CHARACTER(n)
NVARCHAR(n), NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING(n)
NTEXT, NATIONAL TEXT
- same limits as CHAR, VARCHAR, TEXT
OS File	BFILE

  1<not supported="">  
  2Row Identifier  |  implicit ROWID column  |  (use an IDENTITY column)   
  3Secure OS Label  |  MLSLABEL, RAW MLSLABEL  |  <not supported="">  
  4128-bit Unique Number   
  5(UUID, GUID)  |  <not supported=""> |  UNIQUEIDENTIFIER (version 7.0 only)   
  6  
  7\--Fred 
  8
  9* ###  Limits 
 10
 11Last Updated: 6/14/2000   
 12Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
 13
 14The following table shows differences in limits of Oracle and MS SQL Server: 
 15
 16Description  |  Oracle  |  MS SQL Server   
 17---|---|---  
 18Columns per table  |  1000  |  250 (6.5)   
 191024 (7.0)   
 20Row size  |  unlimited  |  1962 bytes (6.5)   
 218060 bytes (7.0)   
 22\- includes pointers, but not data, for TEXT and IMAGE columns   
 23LONG and LONG RAW columns per row  |  1 (must be last column)  |  unlimited (16-byte pointer per)   
 24LOB, TEXT, and IMAGE columns per row  |  unlimited (16-byte pointer per)  |  unlimited (16-byte pointer per)   
 25Clustered indexes per table  |  1  |  1   
 26Non-clustered indexes per table  |  unlimited  |  249   
 27Columns per index  |  16  |  16   
 28Index row size  |  2K bytes  |  900 bytes   
 29Identifier Length  |  30 chars  |  30 chars (6.5)   
 30128 chars (7.0)   
 31Tables per SELECT  |  unlimited  |  16 (6.5)   
 32256 (7.0)   
 33Source code per stored procedure  |  |  64KB (6.5)   
 34250MB (7.0)   
 35Data type limits  |  (see Data Types )   
 36  
 37\--Fred 
 38
 39* ###  Operators 
 40
 41Last Updated: 6/7/1999   
 42Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
 43
 44Most operators are the same in Oracle and MS SQL Server. Here are some that differ: 
 45
 46Description  |  Oracle  |  MS SQL Server   
 47---|---|---  
 48String concatenation  |  string1 || string2  |  string1 + string2   
 49  
 50\--Fred 
 51
 52* ###  Built-In Functions 
 53
 54Last Updated: 6/7/1999   
 55Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
 56
 57Oracle and MS SQL Server offer many of the same built-in functions. For example, they both offer ABS, EXP, ROUND, UPPER, LOWER, AVG, COUNT, SUM, ASCII, etc. The following table shows some of the corresponding functions that don't have the same name. For a more complete list, see "Migrating Oracle Applications to SQL Server" 
 58
 59Description  |  Oracle  |  MS SQL Server   
 60---|---|---  
 61Smallest integer &gt;= n  |  CEIL  |  CEILING   
 62Modulus  |  MOD  |  %   
 63Truncate number  |  TRUNC  |  <none>  
 64Max or min number or string in list  |  GREATEST,   
 65LEAST  |  <none>  
 66Translate NULL to n  |  NVL  |  ISNULL   
 67Return NULL if two values are equal  |  DECODE  |  NULLIF   
 68String concatenation  |  CONCAT(str1,str2)  |  str1 + str2   
 69Convert ASCII to char  |  CHR  |  CHAR   
 70Capitalize first letters of words  |  INITCAP  |  <none>  
 71Find string in string  |  INSTR  |  CHARINDEX   
 72Find pattern in string  |  INSTR  |  PATINDEX   
 73String length  |  LENGTH  |  DATALENGTH   
 74Pad string with blanks  |  LPAD,   
 75RPAD  |  <none>  
 76Trim leading or trailing chars other than blanks  |  LTRIM(str,chars),   
 77RTRIM(str,chars)  |  <none>  
 78Replace chars in string  |  REPLACE  |  STUFF   
 79Convert number to string  |  TO_CHAR  |  STR, CAST   
 80Convert string to number  |  TO_NUMBER  |  CAST   
 81Get substring from string  |  SUBSTR  |  SUBSTRING   
 82Char for char translation in string  |  TRANSLATE  |  <none>  
 83Date addition  |  ADD_MONTH or +  |  DATEADD   
 84Date subtraction  |  MONTHS_BETWEEN or -  |  DATEDIFF   
 85Last day of month  |  LAST_DAY  |  <none>  
 86Time zone conversion  |  NEW_TIME  |  <none>  
 87Next specified weekday after date  |  NEXT_DAY  |  <none>  
 88Convert date to string  |  TO_CHAR  |  DATENAME, CONVERT   
 89Convert string to date  |  TO_DATE  |  CAST   
 90Convert date to number  |  TO_NUMBER(TO_CHAR(d))  |  DATEPART   
 91Date round  |  ROUND  |  CONVERT   
 92Date truncate  |  TRUNC  |  CONVERT   
 93Current date  |  SYSDATE  |  GETDATE   
 94Convert hex to binary  |  HEXTORAW  |  CAST   
 95Convert binary to hex  |  RAWTOHEX  |  CONVERT   
 96If statement in an expression  |  DECODE  |  CASE ... WHEN   
 97or COALESCE   
 98User's login id number or name  |  UID, USER  |  SUSER_ID, SUSER_NAME   
 99User's database id number or name  |  UID, USER  |  USER_ID, USER_NAME   
100Current user  |  USER  |  USER   
101  
102\--Fred 
103
104* ###  Differences in SQL Syntax 
105
106Last Updated: 3/21/2001   
107Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
108
109The following table shows the different syntax used in Oracle and MS SQL Server for the same SQL operations: 
110
111Description  |  Oracle  |  MS SQL Server   
112---|---|---  
113Left Outer Join  |  WHERE column1 = column2 **(+)** |  FROM table1 **LEFT OUTER JOIN** table2 ON table1.column1 = table2.column2   
114  
115Note: The following syntax is also supported, but is no longer recommended:   
116WHERE column1 *= column2   
117Right Outer Join  |  WHERE column1 **(+)** = column2  |  FROM table1 **RIGHT OUTER JOIN** table2 ON table1.column1 = table2.column2   
118  
119Note: The following syntax is also supported, but is no longer recommended:   
120WHERE column1 =* column2   
121Full Outer Join  |  |  FROM table1 **FULL OUTER JOIN** table2 ON table1.column1 = table2.column2   
122SELECT without FROM  |  SELECT 'hello world' **FROM DUAL** |  SELECT 'hello world'   
123SELECT data into a table  |  CREATE TABLE **AS SELECT** ...  |  SELECT ... **INTO**  
124Intersection of 2 SELECTS  |  SELECT ... **INTERSECT** SELECT ...  |  SELECT ... WHERE **EXISTS** (SELECT ...)   
125Subtraction of 2 SELECTS  |  SELECT ... **MINUS** SELECT ...  |  SELECT ... WHERE **NOT EXISTS** (SELECT ...)   
126INSERT into a JOIN  |  INSERT INTO **SELECT** ...  |  Create a VIEW and INSERT INTO it.   
127UPDATE data in a JOIN  |  UPDATE **SELECT** ...  |  Create a VIEW and INSERT INTO it.   
128UPDATE one table based on criteria in another table  |  <not supported=""> |  UPDATE table **FROM** ...   
129DELETE rows from one table based on criteria in another table  |  <not supported=""> |  DELETE FROM table **FROM** ...   
130DROP a column from a table  |  <not 8i="" oracle="" supported="" until=""> |  ALTER TABLE table_name DROP COLUMN column_name   
131Readonly VIEW  |  CREATE VIEW ... **WITH READONLY** |  GRANT SELECT ...   
132Save point  |  SAVEPOINT  |  SAVE TRANSACTION   
133Table lock  |  LOCK TABLE...IN SHARE MODE  |  SELECT...table_name ( **TABLOCK** )   
134Exclusive table lock  |  LOCK TABLE...IN EXCLUSIVE MODE  |  SELECT...table_name ( **TABLOCKX** )   
135Reserving index space  |  PCTFREE=0  |  FILLFACTOR=100   
136Declaring a local variable  |  DECLARE varname type;  |  DECLARE **@** varname type   
137Initializing a local variable  |  DECLARE varname type := value;  |  <not supported="">  
138Declaring a constant  |  DECLARE varname CONSTANT type := value;  |  <not supported="">  
139Assigning to a variable  |  varname := value   
140SELECT value INTO varname  |  SET @varname = value   
141SELECT @varname = value   
142Assigning to a variable from a cursor  |  FETCH cursorname INTO varname  |  FETCH NEXT FROM cursorname INTO varname   
143Declaring a cursor  |  CURSOR _curname_ _(param_ s)   
144IS _SELECT ..._ ;  |  DECLARE curname CURSOR FOR SELECT ...   
145If statement  |  IF ... THEN   
146ELSIF ... THEN   
147ELSE   
148ENDIF  |  IF ...   
149BEGIN ... END   
150ELSE BEGIN ... END   
151While loop  |  WHILE ... LOOP   
152END LOOP  |  WHILE ...   
153BEGIN ... END   
154Other loops  |  FOR ... END LOOP   
155LOOP ... END LOOP  |  <not supported="">  
156Loop exit  |  EXIT, EXIT WHEN  |  BREAK, CONTINUE   
157Print output  |  DBMS_OUTPUT.PUT_LINE  |  PRINT   
158Raise error  |  RAISE_APPLICATION_ERROR  |  RAISERROR   
159Statement terminator  |  Semi-colon (;)  |  <none required="">  
160  
161Thanks to Tom Johnston for catching a mistake in this tip. I had the FROM DUAL in the wrong column. 
162
163\--Fred 
164
165* ###  Differences in SQL Semantics 
166
167Last Updated: 6/6/1999   
168Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
169
170The following table shows some semantic differences between Oracle and MS SQL Server: 
171
172Description  |  Oracle  |  MS SQL Server   
173---|---|---  
174Commit  |  Explicit COMMIT statement required  |  Automatic commit unless SET IMPLICIT_TRANSACTIONS ON   
175Reading 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.   
176Releasing 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.   
177Implicit data conversion in a statement like the following where vc is a column of type VARCHAR2: 
178
179SELECT * FROM person   
180WHERE vc =123   
181
182
183|  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.   
184Conversion 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).   
185  
186\--Fred 
187
188* ###  Differences in Managing Databases 
189
190Last Updated: 6/6/1999   
191Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
192
193The following table shows some differences in how databases are managed in Oracle and MS SQL Server: 
194
195Description  |  Oracle  |  MS SQL Server   
196---|---|---  
197Model database  |  No model database  |  Newly created databases inherit characteristics (users, etc.) from the special database named "model".   
198  
199\--Fred 
200
201* ###  Differences in Managing Database Objects 
202
203Last Updated: 6/6/1999   
204Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
205
206The following table shows some differences in how database objects (tables, views, stored procedures, etc.) are managed in Oracle and MS SQL Server: 
207
208Description  |  Oracle  |  MS SQL Server   
209---|---|---  
210Fully qualified name  |  [schema.]table   
211[schema.]view  |  [[[server.][database].][owner].]table   
212[[[server.][database].][owner].]view   
213Temp tables  |  Pre 8i: Temporary tables must be deleted explicitly 
214
2158i+: CREATE **GLOBAL TEMPORARY** TABLE 
216
217|  **#table** \-- Any table named starting with a pound sign (#) is automatically deleted when the user logs off or the procedure ends.   
218**##table** \-- Same as above, except that the table is accessible to other users.   
219Re-creating an object  |  CREATE **OR REPLACE** ...  |  DROP ...   
220CREATE ...   
221Create view before dependent tables  |  CREATE **FORCE** VIEW  |  Not supported. Tables used by view must exist before view can be created.   
222  
223\--Fred 
224
225* ###  Differences in Managing Users 
226
227Last Updated: 6/6/1999   
228Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
229
230The following table shows some differences in how users are managed in Oracle and MS SQL Server: 
231
232Description  |  Oracle  |  MS SQL Server   
233---|---|---  
234Membership 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".   
235  
236\--Fred 
237
238* ###  Differences in Integration with MS ADO, RDO, etc. 
239
240Last Updated: 6/6/1999   
241Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
242
243The following table shows the different techniques used in Oracle and MS SQL Server to interact with MS ADO, RDO, etc.: 
244
245Description  |  Oracle  |  MS SQL Server   
246---|---|---  
247Return a recordset to the caller  |  Return a handle to a cursor.   
248For more info: See MS KB article Q174679.  |  SELECT with no INTO clause;   
249Multiple such SELECTs return multiple recordsets   
250  
251\--Fred 
252
253* ###  Miscellaneous Differences 
254
255Last Updated: 6/6/1999   
256Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
257
258The following table shows miscellaneous differences between Oracle and MS SQL Server: 
259
260Description  |  Oracle  |  MS SQL Server   
261---|---|---  
262Generate unique numbers  |  CREATE SEQUENCE  |  IDENTITY column of a table   
263Cascaded DELETE  |  DELETE CASCADE ...  |  (use triggers)   
264Call a user-defined function from a SQL statement (as column of SELECT or expression in WHERE clause)  |  supported  |  not supported   
265  
266\--Fred 
267
268* ###  See Also 
269
270Last Updated: 3/3/2001   
271Applies to: Oracle 7.3+, MS SQL Server 6.5+ 
272
273The following are good sources of info about differences between Oracle and MS SQL Server: 
274
275  1. Bowman, Judith S., Sandra L. Emerson, and Marcy Darnovsky. _The Practical SQL Handbook_ . Addison-Wesley Publishing Company, 1993. ISBN 0-201-62623-3.   
276This 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.   
277
278  2. "Migrating Oracle Applications to SQL Server" on MSDN CD, and at MS TechNet Web site:   
279http://www.microsoft.com/TechNet/sql/Tools/Sqldevkt/ORCL2SQL.asp   
280Microsoft 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.</none></not></not></not></not></not></not></none></none></none></none></none></none></none></none></none></not></not></not>

Oracle 和 MIcrosoft SQL 的不同

Table of Contents:

Details of Tips:

Oracle Tips

SQL Tips

SELECT * and more

Materialized View

PL/SQL Tips

SQL Navigator Tips

See Also

MS SQL Server Tips

SQL Tips

Dynamic SQL in a Stored Procedure

SQL Enterprise Manager Tips

Keyboard Shortcuts

SQL Generating SQL

See Also

Differences Between Oracle and MS SQL Server

Concepts and Terminology

Data Types