oracle -- sqlserver

 

 

程序能同时使用在ORACLE数据库和SQL SERVER 数据库的调查报告（1）

1           编写目的

由于客户所使用的数据库有时候为ORALCE，有时候为SQL SERVER，为了使程序能支持多个数据库，所以做此调查。

2           方案构想

 

标准SQL语句

语句转换技术

适用于ORACLE的语句

适用于SQL SERVER的语句

 

 

3           ORACLE数据库与SQL SERVER数据库的比较

3.1        数据类型比较

 

类型名称	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

 

 

3.2        SQL 语句比较.

Oracle	SQL Server
SELECT语句基本是一致的 但是有如下不同： 1、  SQL Server 不支持Oracle START WITH…CONNECT BY 语句. 你可以替换为SQL Server的一个stored procedure来做同样的工作. 2、  Oracle 的INTERSECT and MINUS 在SQL SERVER中是不被支持的，不过可以用SQL Server的 EXISTS and NOT EXISTS 语句来完成相同的工作。 3、  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 <search_condition>]
Insert在 ORACLE与SQL SERVER中基本是一致的，有如下的不同： 1、  SQL SERVER的 Transact‑SQL language 支持 inserts into tables and views,但是不支持INSERT operations into SELECT statements，如果ORACLE中包含inserts into SELECT statements则需要改变. 2、  SQL SERVER的Transact‑SQL values_list parameter 提供的 SQL-92 standard keyword DEFAULT在ORACLE中是不被支持的. 3、  SQL SERVER中一个非常有用的Transact‑SQL option (EXECute procedure_name) 是用来执行一个 procedure 并将输出结果导入一个目标表或视图，但在Oracle 中是不被支持的. 4、  对于Oracle的Insert语句来说，into关键字不可以少的。 5、  两者的表间copy支持。 INSERT 语法如下：
INSERT [ hint ] INTO table_expression_clause [ (<column> [,…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.	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 ] ) ]
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 ]	< 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 }

 

 

3.3        普通函数比较

数据库类型 函数	ORACLE	SQLServer	比较
数学函数	ABS	ABS	两者都是取绝对值函数格式都为abs（numeric_expression）
	ASIN，ACOS，ATAN	ASIN，ACOS，ATIN	用于计算反正弦，反余弦，反正切的值。参数为float类型的表达式，取值-1~1.
	SIN，COS，TAN	SIN，COS，TAN	用于计算正弦，余弦，正切的值。参数为float类型的表达式，.
	CEIL	CELING	用于计算大于或等于指定值的最小整数，格式：~（numeric_expression）
		DEGREES	用于弧度对角度的转变，而RADIANS用于角度到弧度的转变，这两个函数都是SQLServer特有的。
	EXP	EXP	给定资料的指数值
	FLOOR	FLOOR	用于计算小于或等于、给定值的最大整数。
	LOG	LOG	用语求指定函数的自然对数，在SQLServer里面还有LOG10这个函数，用于求以10为底的对数值
	POWER	POWER	用于求指定表达式的给定次方，POWER（M，N）表示M的N次方。
	ROUND	ROUND	用于把表达式圆整到指定长度，在oracle里面还分数据型和日期型。两种
	SIGN	SIGN
	SQRT	SQRT	用于求给定式子的平方根
字符串函数	||	+	用于把两个或多个字符数据或列连接起来。Oracle里用||，SQLServer里用+。
	ASCII	ASCII	用于求最左端字符的ASCII值。
	TO_CHAR	CHAR	都是数据类型转换标识，前者主要是将日期、时间或数转换为文本。后者主要是将整形的ASCII数，转换成相对应的字符
		CHARINDEX	返回指定样式的起始位置
		DIFFERENCE
	LOWER	LOWER	转换成小写字母。
	LTRIM	LTRIM	删除资料前面的空格，也就是左删除，还有与之对应的RTRIM，为右删除
	SOUNDEX	SOUNDEX	返回由4个字符组成的代码，以平估两个字符的相似性。
	SUBSTR/SUBSTRB	SUBSTRING	格式为~（string，a，b），返回以a位置开始的有b个字符长的string的一部分，oracle里面还有substrb函数，功能相同但是他里面的a，b是以字节为单位而不是以字符为单位。
	UPPER	UPPER	以大写形式返回string。
日期时间函数	SYSDATE	GETDATE	返回当前的时间日期。
	NEXT_DAY	DATEADD	前者格式为NEXT_DAY（d，stirng）表示返回在日期d之后满足string给出条件的第一天。DATEADD格式为DATEADD（d，num，date）表示返回在日期d天之后加上num天后的日期，date表示返回的类型。
系统函数		COL_NAME	返回列名
		COL_LENGTH	返回列的长度
	VSIZE	DATELENGTH	返回任意数据类型表达式的实际长度,二者功能相近。

 

3.4        特殊规则比较

ORACLE	SQL SERVER
CURRVAL, NEXTVAL	Use the identity column type, and @@identity global variable, IDENT_SEED() and IDENT_INCR() functions.
SYSDATE	GETDATE()
ROWID	Use the identity column type
USER	USER
LIKE模糊查询%，_ , ^	LIKE模糊查询%，_ , ^

 

3.5        操作符比较

Operator	Oracle	Microsoft SQL Server
Equal to	(=)	Same
Greater than	(>)	Same
Less than	(<)	Same
Greater than or equal to	(>=)	Same
Less than or equal to	(<=)	Same
Not equal to	(!=, <>,^=)	Same
Not greater than, not less than	N/A	!> , !<
In any member in set	IN	Same
Not in any member in set	NOT IN	Same
Any value in set	ANY, SOME	Same
Referring to all values in set.	!= ALL, <> ALL, < ALL, > ALL, <= ALL, >= ALL	Same
Like pattern	LIKE	Same
Not like pattern	NOT LIKE	Same
Value between x and y	BETWEEN x AND y	Same
Value not between	NOT BETWEEN	Same
Value exists	EXISTS	Same
Value does not exist	NOT EXISTS	Same
Value {is | is not} NULL	IS NULL, IS NOT NULL	Same. Also supports = NULL, != NULL for backward compatibility (not recommended).

 

4           在ORCLE DML语句和PL/SQL程序与SQL SERVER之间进行转换所需做的工作。

1.4.1  保证所有的 SELECT, INSERT, UPDATE, and DELETE 语句的语法是正确的，如果有什么不同需要做一定的更改.

2.4.2  改变所有ORACLE中的 outer joins 为SQL SERVER支持的 SQL-92 standard outer join 标准.

3.4.3  将Oracle functions 与SQL Server functions 进行转换.

4.4.4        检查所有的操作符.

5.4.5  将ORACLE的“||” 字串连接操作符转换为SQL SERVER的 “+”字串连接操作符.

6.4.6  将ORACLE的 PL/SQL 程序转换为SQL SERVER的Transact‑SQL 程序。

7.4.7  修改所有的ORACLE的 PL/SQL 游标为没有游标的SELECT 语句或者是SQL SERVER的 Transact‑SQL 游标.

8.4.8  将ORACLE的PL/SQL procedures, functions, and packages 与SQL SERVER的Transact‑SQL procedures进行转换.

9.4.9  将ORACLE的 PL/SQL triggers与SQL SERVER的Transact‑SQL triggers进行转换.

10.4.10   用 SET SHOWPLAN 语句来调整你的查询的性能.

5           实现自动转换的方案设想

方案	具体描述
1	对于已经写好的程序，由于SQL 语句使用的不是很规范，所以进行转换可能需要在程序中直接处理。
2	制定SQL语句的规范，编程的时候参照规范来写，同时需要编写一个SQL SERVER与ORACLE的转换类，在程序的编写过程中，需要对每个SQL 语句进行转换处理。
3	制定SQL语句的规范，编程的时候参照规范来写。同时写一个SQL SERVER与ORACLE的转换程序，针对不同的后台数据库来生成不同的应用程序，这样做的好处是在程序执行的时候不需要通过转换，提高代码的执行效率。