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