ORACLE 與SQL SERVER SQL語言比較

資料類型比較

 

類型名稱	Oracle	SQLServer	比較
字元資料類型	CHAR	CHAR	都是固定長度字元資料但oracle裡面最大度為2kb，SQLServer裡面最大長度為8kb
變長字元資料類型	VARCHAR2	VARCHAR	Oracle裡面最大長度為4kb，SQLServer裡面最大長度為8kb
根據字元集而定的固定長度字串	NCHAR	NCHAR	前者最大長度2kb後者最大長度4kb
根據字元集而定的可變長度字串	NVARCHAR2	NVARCHAR	二者最大長度都為4kb
日期和時間資料類型	DATE	有Datetime和Smalldatetime兩種	在oracle裡面格式為DMY在SQLSerser裡面可以調節，預設的為MDY
數字類型	NUMBER(P,S)	NUMERIC[P(,S)]	Oracle裡面p代表小數點左面的位元，s代表小數點右面的位元。而SQLServer裡面p代表小數點左右兩面的位元之和，s代表小數點右面的位元。
數字類型	DECIMAL(P,S)	DECIMAL[P(,S)]	Oracle裡面p代表小數點左面的位元，s代表小數點右面的位元。而SQLServer裡面p代表小數點左右兩面的位元之和，s代表小數點右面的位元。
整數類型	INTEGER	INT	同為整數類型，儲存大小都為4個位元組
浮點數類型	FLOAT	FLOAT
實數類型	REAL	REAL

SQL語句比較

Oracle	SQL Server
SELECT語句基本是一致的 　　但是有如下不同： 　　SQL Server 不支援Oracle START WITH…CONNECT BY 語句. 你可以替換為SQLServer的一個stored procedure來做同樣的工作。 　　Oracle 的INTERSECT and MINUS 在SQL SERVER中是不被支援的，不過可以用SQLServer的 EXISTS and NOT EXISTS 語句來完成相同的工作。 　　Oracle特殊的用語效能最佳化的cost-based optimizer hints 是不被SQL SERVER支援的，建議刪除。在SQL SERVER中請用SQL SERVER的cost-based optimization。 　　SELECT 文法如下：
Subquery [ for_update_clause] ; subquery::= SELECT [ hint ] [ ALL| DISTINCT| UNIQUE ] { * | { {expr [ [ AS ] c_alias ] | schema.{ table | view | snapshot }.*} [ ,…n ] }* FROM { < query_table_expression_clause > [ ,…n ] } [ where_clause ] [ [group_by_clause | hierarchical_query] [,…n] ] [ where_clause ] [ [group_by_clause | hierarchical_query ] […n] ] { UNION [ ALL ] | INTERSECT | MINUS } ( subquery ) ] [ order_by_clause ] query_table_expression_clause::= { { [ schema. ] { { table { { [ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ] [ sample_clause ] } | [ @dblink ] } } | { view | snapshot } [ @dblink ] } } | ( subquery [ with_clause ] ) | table_collection_expression } [ t_alias ] sample_clause::= SAMPLE [ BLOCK ] { ( sample_percent ) } with_clause::= WITH { READ ONLY | CHECK OPTION [CONSTRAINT constraint ] } table_collection_expression::= TABLE { ( collection_expression ) [ ( * ) ] } where_clause::= WHERE { condition | outer_join } outer_join::= { table1. column { =table2. column ( + ) | ( + )=table2. column } } hierarchical_query_clause::= [ START WITH condition ] { CONNECT BY condition } group_by_clause::= GROUP BY { { expr [,…n] } | [expr] [,…n] { CUBE | ROLLUP} ( expr [,…n] ) } [ HAVING condition ] order_by_clause::= ORDER BY { { expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] } [,…n] for_update_clause::= FOR UPDATE [ OF { [ schema. ] { table | view } . column} [,…n ] ] [ NOWAIT ]	SELECT select_list[ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] 文法 SELECT statement ::=< query_expression >[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }[ ,...n ]] [ COMPUTE{ { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ][ BY expression [ ,...n ] ]] [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }[ , XMLDATA ][ , ELEMENTS ][ , BINARY base64 ]} ] [ OPTION ( < query_hint > [ ,...n ]) ] < query expression > ::={ < query specification > | ( < query expression > ) }[ UNION [ ALL ] < query specification> | ( < query expression > ) [...n ] ] < query specification > ::=SELECT [ ALL | DISTINCT ][ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ]< select_list >[ INTO new_table ][ FROM { < table_source > } [ ,...n ] ][ WHERE < search_condition > ][ GROUP BY [ ALL ] group_by_expression [ ,...n ][ WITH { CUBE | ROLLUP } ]][HAVING ]
Insert在 ORACLE與SQL SERVER中基本是一致的，有如下的不同： 　　SQL SERVER的 TransactSQL language 支援 inserts into tables and views,但是不支援INSERT operations into SELECT statements，如果ORACLE中包含inserts into SELECT statements則需要改變。 　　SQL SERVER的TransactSQL values_list parameter 提供的 SQL-92 standard keyword DEFAULT在ORACLE中是不被支援的。 　　SQL SERVER中一個非常有用的TransactSQL option (EXECute procedure_name) 是用來執行一個 procedure 並將輸出結果匯入一個目標表或視圖，但在Oracle 中是不被支援的。 　　INSERT 文法如下：
INSERT [ hint ] INTO table_expression_clause [ ( [,…n] ) ] { values_clause | subquery } [,…n] ; DML_table_expression_clause::= { { [ schema. ] { table{ [ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ] | @ dblink } } | { view | snapshot } [ @dblink ] } | ( subquery [ with_clause ] ) | table_collection_expression } [ t_alias ] subquery:見SELECT文法重的subquery. with_clause::= WITH { READ ONLY | CHECK OPTION [ CONSTRAINT constraint ] } table_collection_expression::= TABLE ( collection_expression ) [ (+) ] values_clause::= VALUES ( { expr | subquery } ) [ returning_clause ] returning_clause::= RETURNING { expr } [ ,…n ] INTO { data_item } [ ,…n ]	INSERT [ INTO]{ table_name WITH ( < table_hint_limited > [ ...n ] )| view_name| rowset_function_limited} {[ ( column_list ) ]{ VALUES( { DEFAULT | NULL | expression } [ ,...n] )| derived_table| execute_statement} } | DEFAULT VALUES < table_hint_limited > ::={ FASTFIRSTROW| HOLDLOCK| PAGLOCK| READCOMMITTED| REPEATABLEREAD| ROWLOCK| SERIALIZABLE| TABLOCK| TABLOCKX| UPDLOCK }
DELETE和UPDATE在 ORACLE與SQL SERVER中基本是一致的
DELETE 文法： DELETE [ hint ] [ FROM ] table_expression_clause [ where_clause ] [ returning_clause ] ; DML_table_expression_clause::= { { [ schema. ] { table{ [ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ] | @ dblink } } | { view | snapshot } [ @dblink ] } | ( subquery [ with_clause ] ) | table_collection_expression } [ t_alias ] subquery:見SELECT文法重的subquery. with_clause::= WITH { READ ONLY | CHECK OPTION [ CONSTRAINT constraint ] } table_collection_expression::=TABLE ( collection_expression ) [ ( * ) ] where_clause::= WHERE condition returning_clause::= RETURNING { expr } [,…n] INTO { data_item } [ ,…n ]	DELETE[ FROM ]{ table_name WITH ( < table_hint_limited > [ ...n ] ) | view_na| rowset_function_limited} [ FROM { < table_source > } [ ,...n ] ] [ WHERE{ < search_condition >| { [ CURRENT OF{ { [ GLOBAL ] cursor_name }| cursor_variable_name} ] }} ] [ OPTION ( < query_hint > [ ,...n ] ) ] < table_source > ::=table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]| view_name [ [ AS ] table_alias ]| rowset_function [ [ AS ] table_alias ]| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]| < joined_table > < joined_table > ::=< table_source > < join_type > < table_source > ON < search_condition >| < table_source > CROSS JOIN < table_source >| < joined_table > < join_type > ::=[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ][ < join_hint > ] JOIN < table_hint_limited > ::={ FASTFIRSTROW | HOLDLOCK | PAGLOCK | READCOMMITTED | REPEATABLEREAD| ROWLOCK| SERIALIZABLE| TABLOCK| TABLOCKX| UPDLOCK } < table_hint > ::={ INDEX ( index_val [ ,...n ] )| FASTFIRSTROW| HOLDLOCK| NOLOCK| PAGLOCK| READCOMMITTED| READPAST| READUNCOMMITTED| REPEATABLEREAD| ROWLOCK| SERIALIZABLE| TABLOCK| TABLOCKX| UPDLOCK} < query_hint > ::={ { HASH | ORDER } GROUP| { CONCAT | HASH | MERGE } UNION| FAST number_row| FORCE ORDER| MAXDOP| ROBUST PLAN| KEEP PLAN}
UPDATE 文法： UPDATE [ hint ] table_expression_clause set_clause [ where_clause ] [ returning_clause ] ;	UPDATE{ table_name WITH ( < table_hint_limited > [ ...n ] )| view_name| rowset_function_limited} SET{ column_name = { expression | DEFAULT | NULL }| @variable = expression| @variable = column = expression } [ ,...n ]
DML_table_expression_clause::= { { [ schema. ] { table{ [ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ] | @ dblink } } | { view | snapshot } [ @dblink ] } | ( subquery [ with_clause ] ) | table_collection_expression } [ t_alias ] subquery:見SELECT文法重的subquery. with_clause::= WITH { READ ONLY | CHECK OPTION [ CONSTRAINT constraint ] } table_collection_expression::= TABLE ( collection_expression ) [ (+) ] set_clause::= SET { { { ( { column } [ ,…n ] ) = ( subquery ) } | column = { expr | ( subquery ) } } [ ,…n ] } | VALUE ( t_alias ) = { expr | ( subquery ) } where_clause::= WHERE condition returning_clause::= RETURNING { expr } [ ,…n ] INTO { data_item } [ ,…n ]	{ { [ FROM { < table_source > } [ ,...n ] ][ WHERE < search_condition > ] } [ WHERE CURRENT OF{ { [ GLOBAL ]cursor_name } | cursor_variable_name }] } [ OPTION ( < query_hint > [ ,...n ] ) ] < table_source > ::=table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ]| view_name [ [ AS ] table_alias ]| rowset_function [ [ AS ] table_alias ]| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]| < joined_table > < joined_table > ::=< table_source > < join_type > < table_source > ON < search_condition >| < table_source > CROSS JOIN < table_source >| < joined_table > < join_type > ::=[ INNER | { { LEFT | RIGHT | FULL } [OUTER] } ][ < join_hint > ]JOIN < table_hint_limited > ::={FASTFIRSTROW| HOLDLOCK| PAGLOCK| READCOMMITTED| REPEATABLEREAD| ROWLOCK| SERIALIZABLE| TABLOCK| TABLOCKX| UPDLOCK} < table_hint > ::={INDEX ( index_val [ ,...n ] | FASTFIRSTROW| HOLDLOCK| NOLOCK| PAGLOCK| READCOMMITTED| READPAST| READUNCOMMITTED| REPEATABLEREAD| ROWLOCK| SERIALIZABLE| TABLOCK| TABLOCKX| UPDLOCK } < query_hint > ::={{ HASH | ORDER } GROUP| { CONCAT | HASH | MERGE } UNION| {LOOP | MERGE | HASH } JOIN| FAST number_rows| FORCE ORDER| MAXDOP| ROBUST PLAN| KEEP PLAN}