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

    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.

    •  

    上一页  [1] [2] [3] 

    打印本文 打印本文 关闭窗口 关闭窗口
    Oracle 和 MIcrosoft SQL 的不同
    作者：武汉SEO闵涛  文章来源：敏韬网  点击数2825  更新时间：2009/4/22 22:05:26  文章录入：mintao  责任编辑：mintao
    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 ServerDescription Oracle MS SQL ServerDescription Oracle MS SQL Server Description Oracle MS SQL ServerDescription Oracle MS SQL ServerDescription Oracle MS SQL Server
    打印本文 打印本文 关闭窗口 关闭窗口