Oracle 和 MIcrosoft SQL 的不同

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

 

Table of Contents:

  1. Oracle Tips
    1. SQL Tips
      1. SELECT * and more
      2. Materialized View
    2. PL/SQL Tips
    3. SQL Navigator Tips
    4. See Also
  2. MS SQL Server Tips
    1. SQL Tips
      1. Dynamic SQL in a Stored Procedure
    2. SQL Enterprise Manager Tips
      1. Keyboard Shortcuts
      2. SQL Generating SQL
    3. See Also
  3. 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:

          1. 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.
          2. 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.

              KeyFunction
              F1Help on SQL Enterprise Manager
              Shift-F1Help on syntax of current SQL statement
              Ctrl-EExecute selected text in Query Analyzer
              Ctrl-RHide/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.

              See also: Windows Shortcut Keys

              --Fred

            • SQL Generating SQL

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

              To automate tedious database maintenance chores, you can use SQL statements to generate SQL statements that do your maintenance for you.  For example, to change the permissions on all stored procedures in a database, you can use a SELECT statement like:

              	SELECT 'GRANT EXECUTE ON ' + name + ' TO PUBLIC
                      	GO'
                      	FROM sysobjects
                      	WHERE type = 'P'

              The output of this SELECT statement is a series of alternating GRANT and GO statements, one pair per stored procedures, for all stored procedures in the database.  Then you copy that output as your next set of commands and execute it. 

              Note:  Be sure to leave the line break before the word GO. It is required to start on a new line, after the GRANT statement.

              Thanks to Steve Rhoads for this tip.

              --Fred

        • 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:

          1. MS SQL Server books on the MSDN Library CD.

          --Fred

    • Differences Between Oracle and MS SQL Server

        • 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/TermOracleMS SQL Server
          Database enginedatabasedatabase server
          Database (collection of tables)schemadatabase
          Roles/Groupsrolesgroups
          Database adminstrator account, database ownerdbasa, dbo
          Data about the databaseData Dictionary
          - one per server
          Database Catalog
          - one per database
          "master" database
          - one per server
          Blocks and extentsblocks and extentspages and extents
          Network softwareSQL*NetNet-library
          Data 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-insensitive
          Synonymssupportednot supported
          Readonly 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 Server
          Fixed Length StringCHAR(n)
          - limit 2KB
          CHAR(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, SMALLMONEY
          Floating PointDECIMALFLOAT, FLOAT(n), DOUBLE PRECISION,
          REAL,
          DateDATEDATETIME, SMALLDATETIME, TIMESTAMP
          - TIMESTAMP auto-updated
          BinaryRAW(n)
          - limit 255 bytes
          BINARY(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 4GB
          TEXT
          - limit 2GB
          Large BinaryLONG RAW
          - limit 2GB
          - limit one per table row
          BLOB
          - limit 4GB
          IMAGE
          - limit 2GB
          Multi-byte charsNCHAR(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 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 Server
          Columns 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 columns
          LONG 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 table11
          Non-clustered indexes per tableunlimited249
          Columns per index1616
          Index row size2K bytes900 bytes
          Identifier 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 Server
          String 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 Server
          Smallest integer >= nCEILCEILING
          ModulusMOD%
          Truncate numberTRUNC<none>
          Max or min number or string in listGREATEST,
          LEAST
          <none>
          Translate NULL to nNVLISNULL
          Return NULL if two values are equalDECODENULLIF
          String concatenationCONCAT(str1,str2)str1 + str2
          Convert ASCII to charCHRCHAR
          Capitalize first letters of wordsINITCAP<none>
          Find string in stringINSTRCHARINDEX
          Find pattern in stringINSTRPATINDEX
          String lengthLENGTHDATALENGTH
          Pad string with blanksLPAD,
          RPAD
          <none>
          Trim leading or trailing chars other than blanksLTRIM(str,chars),
          RTRIM(str,chars)
          <none>
          Replace chars in stringREPLACESTUFF
          Convert number to stringTO_CHARSTR, CAST
          Convert string to numberTO_NUMBERCAST
          Get substring from stringSUBSTRSUBSTRING
          Char for char translation in stringTRANSLATE<none>
          Date additionADD_MONTH or +DATEADD
          Date subtractionMONTHS_BETWEEN or -DATEDIFF
          Last day of monthLAST_DAY<none>
          Time zone conversionNEW_TIME<none>
          Next specified weekday after dateNEXT_DAY<none>
          Convert date to stringTO_CHARDATENAME, CONVERT
          Convert string to dateTO_DATECAST
          Convert date to numberTO_NUMBER(TO_CHAR(d))DATEPART
          Date roundROUNDCONVERT
          Date truncateTRUNCCONVERT
          Current dateSYSDATEGETDATE
          Convert hex to binaryHEXTORAWCAST
          Convert binary to hexRAWTOHEXCONVERT
          If statement in an expressionDECODECASE ... WHEN
          or COALESCE
          User's login id number or nameUID, USERSUSER_ID, SUSER_NAME
          User's database id number or nameUID, USERUSER_ID, USER_NAME
          Current 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 Server
          Left 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 *= column2
          Right 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 =* column2
          Full Outer Join FROM table1 FULL OUTER JOIN table2 ON table1.column1 = table2.column2
          SELECT without FROMSELECT 'hello world' FROM DUALSELECT 'hello world'
          SELECT data into a tableCREATE TABLE AS SELECT ...SELECT ... INTO
          Intersection 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_name
          Readonly VIEWCREATE VIEW ... WITH READONLYGRANT SELECT ...
          Save pointSAVEPOINTSAVE TRANSACTION
          Table 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=100
          Declaring a local variableDECLARE varname type;DECLARE @varname type
          Initializing 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 varname
          SET @varname = value
          SELECT @varname = value
          Assigning to a variable from a cursorFETCH cursorname INTO varnameFETCH NEXT FROM cursorname INTO varname
          Declaring a cursorCURSOR curname (params)
          IS SELECT ...;
          DECLARE curname CURSOR FOR SELECT ...
          If statementIF ... THEN
          ELSIF ... THEN
          ELSE
          ENDIF
          IF ...
          BEGIN ... END
          ELSE BEGIN ... END
          While loopWHILE ... LOOP
          END LOOP
          WHILE ...
          BEGIN ... END
          Other loopsFOR ... END LOOP
          LOOP ... END LOOP
          <not supported>
          Loop exitEXIT, EXIT WHENBREAK, CONTINUE
          Print outputDBMS_OUTPUT.PUT_LINEPRINT
          Raise errorRAISE_APPLICATION_ERRORRAISERROR
          Statement 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 Server
          CommitExplicit COMMIT statement requiredAutomatic commit unless SET IMPLICIT_TRANSACTIONS ON
          Reading 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 Server
          Model 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 Server
          Fully qualified name[schema.]table
          [schema.]view
          [[[server.][database].][owner].]table
          [[[server.][database].][owner].]view
          Temp 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 Server
          Membership 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 Server
          Return 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 Server
          Generate unique numbersCREATE SEQUENCEIDENTITY column of a table
          Cascaded 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

        • 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:

          1. 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.
          2. "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.

 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值