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}