程序能同时使用在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 ] |
|
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_hint_limited > ::= |
DELETE和UPDATE在 ORACLE与SQL SERVER中基本是一致的 | |
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 [ WHERE{ < search_condition >| { [ CURRENT OF{ { [ GLOBAL ] cursor_name } |
with_clause::= WITH { READ ONLY | CHECK OPTION [ CONSTRAINT constraint ] }
table_collection_expression::=
where_clause::= WHERE condition
returning_clause::= RETURNING { expr } [,…n] INTO { data_item } [ ,…n ] | < table_source > ::= < joined_table > ::= < join_type > ::= < table_hint_limited > ::= < table_hint > ::= < query_hint > ::= |
UPDATE 语法:
UPDATE [ hint ] table_expression_clause set_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 ) [ (+) ]
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 ] | {
|
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的转换程序,针对不同的后台数据库来生成不同的应用程序,这样做的好处是在程序执行的时候不需要通过转换,提高代码的执行效率。 |