Oracle 和 MIcrosoft SQL 的不同

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

Table of Contents:

Details of Tips:

1. Oracle Tips

   1. SQL Tips

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

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

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

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

   3. 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.

   4. 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

2. MS SQL Server Tips

   1. SQL Tips

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

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

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

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

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18