Oracle 和 MIcrosoft SQL 的不同

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

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.

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 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/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

Row Identifier

implicit ROWID column

(use an IDENTITY column)

Secure OS Label

MLSLABEL, RAW MLSLABEL

128-bit Unique Number

(UUID, GUID)

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:

Description

Oracle

MS SQL Server

Columns per table

1000

250 (6.5)

1024 (7.0)

Row size

unlimited

1962 bytes (6.5)

8060 bytes (7.0)

- includes pointers, but not data, for TEXT and IMAGE columns

LONG and LONG RAW columns per row

1 (must be last column)

unlimited (16-byte pointer per)

LOB, TEXT, and IMAGE columns per row

unlimited (16-byte pointer per)

Clustered indexes per table

Non-clustered indexes per table

unlimited

249

Columns per index

Index row size

2K bytes

900 bytes

Identifier Length

30 chars

30 chars (6.5)

128 chars (7.0)

Tables per SELECT

unlimited

16 (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:

Description

Oracle

MS SQL Server

String concatenation

string1 || string2

string1 + 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"

Description

Oracle

MS SQL Server

Smallest integer >= n

CEIL

CEILING

Modulus

MOD

Truncate number

TRUNC

<none>

Max or min number or string in list

GREATEST,

LEAST

<none>

Translate NULL to n

NVL

ISNULL

Return NULL if two values are equal

DECODE

NULLIF

String concatenation

CONCAT(str1,str2)

str1 + str2

Convert ASCII to char

CHR

CHAR

Capitalize first letters of words

INITCAP

<none>

Find string in string

INSTR

CHARINDEX

Find pattern in string

INSTR

PATINDEX

String length

LENGTH

DATALENGTH

Pad string with blanks

LPAD,

RPAD

<none>

Trim leading or trailing chars other than blanks

LTRIM(str,chars),

RTRIM(str,chars)

<none>

Replace chars in string

REPLACE

STUFF

Convert number to string

TO_CHAR

STR, CAST

Convert string to number

TO_NUMBER

CAST

Get substring from string

SUBSTR

SUBSTRING

Char for char translation in string

TRANSLATE

<none>

Date addition

ADD_MONTH or +

DATEADD

Date subtraction

MONTHS_BETWEEN or -

DATEDIFF

Last day of month

LAST_DAY

<none>

Time zone conversion

NEW_TIME

<none>

Next specified weekday after date

NEXT_DAY

<none>

Convert date to string

TO_CHAR

DATENAME, CONVERT

Convert string to date

TO_DATE

CAST

Convert date to number

TO_NUMBER(TO_CHAR(d))

DATEPART

Date round

ROUND

CONVERT

Date truncate

TRUNC

CONVERT

Current date

SYSDATE

GETDATE

Convert hex to binary

HEXTORAW

CAST

Convert binary to hex

RAWTOHEX

CONVERT

If statement in an expression

DECODE

CASE ... WHEN

or COALESCE

User's login id number or name

UID, USER

SUSER_ID, SUSER_NAME

User's database id number or name

UID, USER

USER_ID, USER_NAME

Current user

USER

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

Description

Oracle

MS SQL Server

Left Outer Join

WHERE 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 Join

WHERE column1(+) = column2

FROM 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 FROM

SELECT 'hello world' FROM DUAL

SELECT 'hello world'

SELECT data into a table

CREATE TABLE AS SELECT ...

SELECT ... INTO

Intersection of 2 SELECTS

SELECT ... INTERSECT SELECT ...

SELECT ... WHERE EXISTS (SELECT ...)

Subtraction of 2 SELECTS

SELECT ... MINUS SELECT ...

SELECT ... WHERE NOT EXISTS (SELECT ...)

INSERT into a JOIN

INSERT INTO SELECT ...

Create a VIEW and INSERT INTO it.

UPDATE data in a JOIN

UPDATE SELECT...

Create a VIEW and INSERT INTO it.

UPDATE one table based on criteria in another table

UPDATE table FROM ...

DELETE rows from one table based on criteria in another table

DELETE FROM table FROM ...

DROP a column from a table

ALTER TABLE table_name DROP COLUMN column_name

Readonly VIEW

CREATE VIEW ... WITH READONLY

GRANT SELECT ...

Save point

SAVEPOINT

SAVE TRANSACTION

Table lock

LOCK TABLE...IN SHARE MODE

SELECT...table_name (TABLOCK)

Exclusive table lock

LOCK TABLE...IN EXCLUSIVE MODE

SELECT...table_name (TABLOCKX)

Reserving index space

PCTFREE=0

FILLFACTOR=100

Declaring a local variable

DECLARE varname type;

DECLARE @varname type

Initializing a local variable

DECLARE varname type := value;

Declaring a constant

DECLARE varname CONSTANT type := value;

Assigning to a 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

Loop exit

EXIT, EXIT WHEN

BREAK, CONTINUE

Print output

DBMS_OUTPUT.PUT_LINE

Raise error

RAISE_APPLICATION_ERROR

RAISERROR

Statement terminator

Semi-colon (;)

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 Server

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:

Description

Oracle

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:

Description

Oracle

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:

Description

Oracle

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

Description

Oracle

MS SQL Server

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:

Description

Oracle

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:

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.

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