Oracle 和 MIcrosoft SQL 的不同

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

Table of Contents: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 Limits Operators Built-In Functions Differences in SQL Syntax Differences in SQL Semantics Differences in Managing Databases Differences in Managing Database Objects Differences in Managing Users Differences in Integration with MS ADO, RDO, etc. Miscellaneous Differences See Also Details of Tips:Oracle TipsSQL 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: MS SQL Server 6.5+

The following are good sources of info about MS SQL Server:

MS SQL Server books on the MSDN Library CD.

--Fred

Differences Between Oracle and MS SQL ServerConcepts 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/TermOracleMS SQL ServerDatabase enginedatabasedatabase serverDatabase (collection of tables)schemadatabaseRoles/GroupsrolesgroupsDatabase adminstrator account, database ownerdbasa, dboData about the databaseData Dictionary

- one per serverDatabase Catalog

- one per database

"master" database

- one per serverBlocks and extentsblocks and extentspages and extentsNetwork softwareSQL*NetNet-libraryData stream protocolTransparent Network Substrate (TNS)Tabular Data Stream (TDS) Case sensitivity of names of tables, columns, etc.case-insensitivedepends on character sort order, default is case-insensitiveSynonymssupportednot supportedReadonly transactionsupportednot 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 TypeOracleMS SQL ServerFixed Length StringCHAR(n)

- limit 2KBCHAR(n), CHARACTER(n)

- limit 255 (6.5)

- limit 8KB (7.0)Variable Length StringVARCHAR2(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)IntegerINTEGER, INTEGER(n), SMALLINTINTEGER (4 bytes),

INT (4 bytes),

SMALLINT (2 bytes),

TINYINT (1 byte),

BIT (1 bit)Fixed PointNUMBER, 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, SMALLMONEYFloating PointDECIMALFLOAT, FLOAT(n), DOUBLE PRECISION,

REAL, DateDATEDATETIME, SMALLDATETIME, TIMESTAMP

- TIMESTAMP auto-updatedBinaryRAW(n)

- limit 255 bytesBINARY(n), VARBINARY(n), BINARY VARYING(n)

- limit 255 (6.5)

- limit 8KB (7.0)Large StringLONG, LONG VARCHAR

- limit 2GB

- limit one per table row

CLOB

- limit 4GBTEXT

- limit 2GBLarge BinaryLONG RAW

- limit 2GB

- limit one per table row

BLOB

- limit 4GBIMAGE

- limit 2GBMulti-byte charsNCHAR(n)

NVARCHAR(n)

NCLOB

- same limits as CHAR, VARCHAR, CLOBNCHAR(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, TEXTOS FileBFILE<

not supported>Row Identifierimplicit ROWID column(use an IDENTITY column)Secure OS LabelMLSLABEL, RAW MLSLABEL<not supported>128-bit Unique Number

(UUID, GUID) <not supported>UNIQUEIDENTIFIER (version 7.0 only)

--Fred

Limits

Last Updated: 6/14/2000

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows differences in limits of Oracle and MS SQL Server:

DescriptionOracleMS SQL ServerColumns per table1000250 (6.5)

1024 (7.0)Row size unlimited1962 bytes (6.5)

8060 bytes (7.0)

- includes pointers, but not data, for TEXT and IMAGE columnsLONG and LONG RAW columns per row1 (must be last column)unlimited (16-byte pointer per)LOB, TEXT, and IMAGE columns per rowunlimited (16-byte pointer per)unlimited (16-byte pointer per)Clustered indexes per table11Non-clustered indexes per tableunlimited249Columns per index1616Index row size2K bytes900 bytesIdentifier Length30 chars30 chars (6.5)

128 chars (7.0)Tables per SELECTunlimited16 (6.5)

256 (7.0)Source code per stored procedure 64KB (6.5)

250MB (7.0)Data type limits(see Data Types)

--Fred

Operators

Last Updated: 6/7/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

Most operators are the same in Oracle and MS SQL Server. Here are some that differ:

DescriptionOracleMS SQL ServerString concatenationstring1 string2string1 + string2

--Fred

Built-In Functions

Last Updated: 6/7/1999

Applies to: Oracle 7.3+, MS SQL Server 6.5+

Oracle 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"

DescriptionOracleMS SQL ServerSmallest integer >= nCEILCEILINGModulusMOD%Truncate numberTRUNC<none>Max or min number or string in listGREATEST,

LEAST<none>Translate NULL to nNVLISNULLReturn NULL if two values are equalDECODENULLIFString concatenationCONCAT(str1,str2)str1 + str2Convert ASCII to charCHRCHARCapitalize first letters of wordsINITCAP<none>Find string in stringINSTRCHARINDEXFind pattern in stringINSTRPATINDEXString lengthLENGTHDATALENGTHPad string with blanksLPAD,

RPAD<none>Trim leading or trailing chars other than blanksLTRIM(str,chars),

RTRIM(str,chars)<none>Replace chars in stringREPLACESTUFFConvert number to stringTO_CHARSTR, CASTConvert string to numberTO_NUMBERCASTGet substring from stringSUBSTRSUBSTRINGChar for char translation in stringTRANSLATE<none>Date additionADD_MONTH or +DATEADDDate subtractionMONTHS_BETWEEN or -DATEDIFFLast day of monthLAST_DAY<none>Time zone conversionNEW_TIME<none>

Next specified weekday after dateNEXT_DAY<none>Convert date to stringTO_CHARDATENAME, CONVERTConvert string to dateTO_DATECASTConvert date to numberTO_NUMBER(TO_CHAR(d))DATEPARTDate roundROUNDCONVERTDate truncateTRUNCCONVERTCurrent dateSYSDATEGETDATEConvert hex to binaryHEXTORAWCASTConvert binary to hexRAWTOHEXCONVERTIf statement in an expressionDECODECASE ... WHEN

or COALESCEUser's login id number or nameUID, USERSUSER_ID, SUSER_NAMEUser's database id number or nameUID, USERUSER_ID, USER_NAMECurrent userUSERUSER

--Fred

Differences in SQL Syntax

Last Updated: 3/21/2001

Applies to: Oracle 7.3+, MS SQL Server 6.5+

The following table shows the different syntax used in Oracle and MS SQL Server for the same SQL operations:

DescriptionOracleMS SQL ServerLeft Outer JoinWHERE column1 = column2(+)FROM table1 LEFT OUTER JOIN table2 ON table1.column1 = table2.column2

Note: The following syntax is also supported, but is no longer recommended:

WHERE column1 *= column2Right Outer JoinWHERE column1(+) = column2FROM table1 RIGHT OUTER JOIN table2 ON table1.column1 = table2.column2

Note: The following syntax is also supported, but is no longer recommended:

WHERE column1 =* column2Full Outer Join FROM table1 FULL OUTER JOIN table2 ON table1.column1 = table2.column2SELECT without FROMSELECT 'hello world' FROM DUALSELECT 'hello world'SELECT data into a tableCREATE TABLE AS SELECT ...SELECT ... INTOIntersection of 2 SELECTSSELECT ... INTERSECT SELECT ...SELECT ... WHERE EXISTS (SELECT ...)Subtraction of 2 SELECTSSELECT ... MINUS SELECT ...SELECT ... WHERE NOT EXISTS (SELECT ...)INSERT into a JOININSERT INTO SELECT ...Create a VIEW and INSERT INTO it.UPDATE data in a JOINUPDATE SELECT...Create a VIEW and INSERT INTO it.UPDATE one table based on criteria in another table<not supported>UPDATE table FROM ...DELETE rows from one table based on criteria in another table<not supported>DELETE FROM table FROM ...DROP a column from a table<not supported until Oracle 8i>ALTER TABLE table_name DROP COLUMN column_nameReadonly VIEWCREATE VIEW ... WITH READONLYGRANT SELECT ...Save pointSAVEPOINTSAVE TRANSACTIONTable lockLOCK TABLE...IN SHARE MODE SELECT...table_name (TABLOCK)Exclusive table lockLOCK TABLE...IN EXCLUSIVE MODE SELECT...table_name (TABLOCKX)Reserving index space PCTFREE=0FILLFACTOR=100Declaring a local variableDECLARE varname type;DECLARE @varname typeInitializing a local variableDECLARE varname type := value;<not supported>Declaring a constantDECLARE varname CONSTANT type := value;<not supported>Assigning to a variablevarname := value

SELECT value INTO varnameSET @varname = value

SELECT @varname = valueAssigning to a variable from a cursorFETCH cursorname INTO varnameFETCH NEXT FROM cursorname INTO varnameDeclaring a cursorCURSOR curname (params)

IS SELECT ...;DECLARE curname CURSOR FOR SELECT ...If statementIF ... THEN

ELSIF ... THEN

ELSE

ENDIFIF ...

BEGIN ... END

ELSE BEGIN ... ENDWhile loopWHILE ... LOOP

END LOOPWHILE ...

BEGIN ... ENDOther loopsFOR ... END LOOP

LOOP ... END LOOP<

not supported>Loop exitEXIT, EXIT WHENBREAK, CONTINUEPrint outputDBMS_OUTPUT.PUT_LINEPRINTRaise errorRAISE_APPLICATION_ERRORRAISERRORStatement terminatorSemi-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:

DescriptionOracleMS SQL ServerCommitExplicit COMMIT statement requiredAutomatic commit unless SET IMPLICIT_TRANSACTIONS ONReading uncommitted dataDatabase 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 dataCLOSE 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 NULLSetting 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:

DescriptionOracleMS SQL ServerModel databaseNo model databaseNewly 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:

DescriptionOracleMS SQL ServerFully qualified name[schema.]table

[schema.]view[[[server.][database].][owner].]table

[[[server.][database].][owner].]viewTemp tablesPre 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 objectCREATE OR REPLACE ...DROP ...

CREATE ...Create view before dependent tables CREATE FORCE VIEWNot 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:

DescriptionOracleMS SQL ServerMembership in groupsEach 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.:

DescriptionOracleMS SQL ServerReturn a recordset to the callerReturn 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:

DescriptionOracleMS SQL ServerGenerate unique numbersCREATE SEQUENCEIDENTITY column of a tableCascaded DELETEDELETE CASCADE ...(use triggers)Call a user-defined function from a SQL statement (as column of SELECT or expression in WHERE clause)supportednot supported

--Fred