还是有很多的不同,转贴如下:http://www.bristle.com/Tips/SQL.htm#Oracle%20Tips
This section contains tips on standard SQL (Structured Query Language) statements in Oracle.
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
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 aclearcase/" target="_blank" >ccess 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
This section contains tips on PL/SQL statements -- the Oracle "procedural language" superset of SQL that you use to write stored procedures.
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.
Last Updated: 6/6/1999
Applies to: Oracle 7.3+
The following are good sources of info about Oracle:
--Fred
This section contains tips on SQL (Structured Query Language) statements in MS SQL Server.
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
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.
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 |
---|---|
F1 | 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: Windows Shortcut Keys
--Fred
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
Last Updated: 6/6/1999
Applies to: MS SQL Server 6.5+
The following are good sources of info about MS SQL Server:
--Fred
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
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 | <not supported> |
Row Identifier | implicit ROWID column | (use an IDENTITY column) |
Secure OS Label | MLSLABEL, RAW MLSLABEL | <not supported> |
128-bit Unique Number (UUID, GUID) |
<not supported> | UNIQUEIDENTIFIER (version 7.0 only) |
--Fred
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) | unlimited (16-byte pointer per) |
Clustered indexes per table | 1 | 1 |
Non-clustered indexes per table | unlimited | 249 |
Columns per index | 16 | 16 |
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
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
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 | USER |
--Fred
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 | <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 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; | <not supported> |
Declaring a constant | DECLARE varname CONSTANT type := value; | <not supported> |
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 |
<not supported> |
Loop exit | EXIT, EXIT WHEN | BREAK, CONTINUE |
Print output | DBMS_OUTPUT.PUT_LINE | |
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
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 |
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
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
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
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
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
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
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: