MSSQL和Oracle之间的转化
这两天写数据库升级脚本，发现MSSQL和Oracle之间的转化还是比较容易的。
以下面两个过程为例。两者的功能相似。
1.MSSQL脚本
1/**更改表名**/
2Begin
3declare@tempPoTableNamevarchar(50)--性能对象表名
4declare@tempPoSpNamevarchar(50)--性能过程名
5declare@errorInfovarchar(200)--错误信息
6declare@cntint--计数器
7
8declare@tempSQLvarchar(1000)
9
10--定义表名、同步表名和存储过程游标
11set@tempSQL='declareallValues_Cursorcursorfor'+CHAR(13)+CHAR(10)
12set@tempSQL=@tempSQL+'selectPOTABLENAME,POSPNAMEfromPM_NEPODEF_TABLEWHEREPOID>110499andPOID<110580'
13EXEC(@tempSQL)
14
15OPENallValues_Cursor
16
17--判断是否由符合游标条件的行，如果没有则关闭和释放游标，异常返回
18IF(@@CURSOR_ROWS=0)
19BEGIN
20CLOSEallValues_Cursor
21DEALLOCATEallValues_Cursor
22set@errorInfo='没有指定表名或存储过程名！'
23print@errorInfo
24return
25END
26
27print'开始更改原有表名……'
28FETCHNEXTFROMallValues_CursorINTO@tempPoTableName,@tempPoSpName
29--根据给定的表名、存储过程名创建相应的数据存储存储过程
30WHILE(@@FETCH_STATUS<>-1)
31BEGIN
32print@tempPoTableName
33
34IF(EXISTS(SELECTnamefromsysobjectsWHEREname=@tempPoTableName))
35BEGIN
36set@tempSQL='ALTERTABLE'+@tempPoTableName+'DROPconstraintPK_'+@tempPoTableName
37EXEC(@tempSQL)
38set@tempSQL=@tempPoTableName+'_TMP'
39EXECSp_rename@tempPoTableName,@tempSQL
40END
41ELSE
42BEGIN
43print'没有找到表'+@tempPoTableName;
44END
45
46IF(EXISTS(SELECTnamefromsysobjectsWHEREname=@tempPoSpName))
47BEGIN
48set@tempSQL='DROPPROCEDURE'+@tempPoSpName;
49EXEC(@tempSQL)
50END
51ELSE
52BEGIN
53print'没有找到过程'+@tempPoSpName;
54END
55
56FETCHNEXTFROMallValues_CursorINTO@tempPoTableName,@tempPoSpName
57END
58CLOSEallValues_Cursor
59DEALLOCATEallValues_Cursor
60print'结束更改原有表名……'
61print'------------------------'
62END
63GO
2.ORACLE脚本
1BEGIN
2DECLARE
3tempPoTableNamevarchar2(50);--性能对象表名
4tempPoSpNamevarchar2(50);--性能过程名
5errorInfovarchar2(200);--错误信息
6tempSQLvarchar2(1000);
7cnt1number(1);
8cnt2number(2);
9
10--定义表名、同步表名和存储过程游标
11CursorallValues_Cursoris
12selectUPPER(TRIM(POTABLENAME)),UPPER(TRIM(POSPNAME))fromPM_NEPODEF_TABLEWHEREPOID>110499andPOID<110580;
13
14BEGIN
15OPENallValues_Cursor;
16
17--判断是否由符合游标条件的行，如果没有则关闭和释放游标，异常返回
18
19DBMS_OUTPUT.PUT_LINE('开始更改原有表名……');
20FETCHallValues_CursorINTOtempPoTableName,tempPoSpName;
21--根据给定的表名、存储过程名创建相应的数据存储存储过程
22WHILEallValues_Cursor%foundLOOP
23
24cnt1:=0;
25cnt2:=0;
26BEGIN
27SELECT1INTOcnt1FROMdualWHEREexists(SELECTtable_nameFROMuser_tablesWHEREtable_name=tempPoTableName);
28SELECT1INTOcnt2FROMdualWHEREexists(SELECTOBJECT_NAMEFROMuser_proceduresWHEREOBJECT_NAME=tempPoSpName);
29exception
30WHENno_data_foundTHEN
31null;
32END;
33
34IFcnt1=1THEN
35DBMS_OUTPUT.PUT_LINE(tempPoTableName);
36tempSQL:='ALTERTABLE'||tempPoTableName||'DROPconstraintPK_'||tempPoTableName;
37EXECUTEIMMEDIATEtempSQL;
38tempSQL:='ALTERTABLE'||tempPoTableName||'RENAMETO'||tempPoTableName||'_TMP';
39EXECUTEIMMEDIATEtempSQL;
40ELSE
41DBMS_OUTPUT.PUT_LINE('没有找到表'||tempPoTableName);
42ENDIF;
43
44IFcnt2=1THEN
45tempSQL:='DROPPROCEDURE'||tempPoSpName;
46EXECUTEIMMEDIATEtempSQL;
47ELSE
48DBMS_OUTPUT.PUT_LINE('没有找到过程'||tempPoSpName);
49ENDIF;
50
51FETCHallValues_CursorINTOtempPoTableName,tempPoSpName;
52ENDLOOP;
53CLOSEallValues_Cursor;
54DBMS_OUTPUT.PUT_LINE('结束更改原有表名……');
55DBMS_OUTPUT.PUT_LINE('------------------------');
56END;
57END;
58/
上面两个是无名存储过程，不需要考虑是否已经存在该过程。对于有名的过程需要考虑对象是否已经存在。
我是从MSSQL向Oracle转化的。
第一步，修改整体结构。
MSSQL的总体结构如下，只需要一个begin和end，中间加入变量声明。
1Begin
2declare--变量
3--过程
4END
5GO
Oralce的总体结构如下，需要两个begin和end，一个是整个过程，一个是除去申明之外的过程。
1BEGIN
2DECLARE
3--变量
4BEGIN
5--过程
6END;
7END;
8/
第二步，修改声明变量。
MSSQL需要在每个变量前面加declare标示，Oracle只需要一个declare标示。此外注意修改各自的数据类型。
第三步，修改游标。复杂的过程中离不开游标。因此更改游标结构经常用到。
MSSQL的游标是全局的，需要建立之后再清空。而Oracle的游标类似于局部变量，使用完之后，自动清除。
MSSQL游标结构如下：
set@tempSQL='declareallValues_Cursorcursorfor'+CHAR(13)+CHAR(10)
set@tempSQL=@tempSQL+'selectPOTABLENAME,POSPNAMEfromPM_NEPODEF_TABLEWHEREPOID>110499andPOID<110580'
--游标语句
EXEC(@tempSQL)
--1.创建游标
OPENallValues_Cursor
--2.打开游标
--判断是否由符合游标条件的行，如果没有则关闭和释放游标，异常返回
IF(@@CURSOR_ROWS=0)
BEGIN
CLOSEallValues_Cursor
DEALLOCATEallValues_Cursor
set@errorInfo='没有指定表名或存储过程名！'
print@errorInfo
return
END
WHILE(@@FETCH_STATUS<>-1)
BEGIN
FETCHNEXTFROMallValues_CursorINTO@tempPoTableName,@tempPoSpName
--3进行数据处理
END
CLOSEallValues_Cursor
--4.关闭游标
DEALLOCATEallValues_Cursor
--5.注销游标
Oracle的游标是在变量中声明定义的，然后在过程中使用。其结构如下：
1--声明中
2CursorallValues_Cursoris
3selectUPPER(TRIM(POTABLENAME)),UPPER(TRIM(POSPNAME))fromPM_NEPODEF_TABLEWHEREPOID>110499andPOID<110580;
4--1.声明游标
5--过程中
6OPENallValues_Cursor;
7--2.打开游标
8
9WHILEallValues_Cursor%foundLOOP
10FETCHallValues_CursorINTOtempPoTableName,tempPoSpName;
11--3.处理数据
12
13ENDLOOP;
14CLOSEallValues_Cursor;
15--4.关闭游标
第四步修改赋值语句和比较语句。MSSQL中使用Set语句来赋值，Oracle中使用:=来赋值。此外MSSQL中的变量习惯前面增加一个@字符，在Oracle中可以删除。
第五步修改逻辑结构。MSSQL中使用IF()....ELSE....
，结构体之间都要用BEGIN和END框起来。而Oracle则使用IF...THEN...ELSE..ENDIF结构，中间不必使用BEGIN和END。此外While结构差别也类似。
第五步修改逻辑结构。MSSQL中使用IF()....ELSE....，结构体之间都要用BEGIN和END框起来。而Oracle则使用IF...THEN...ELSE..ENDIF结构，中间不必使用BEGIN和END。此外While结构差别也类似。
第六步修改各自的调用方法和函数。常见的是MSSQL的EXEC(@tempSQL)，对应Oracle的EXECUTEIMMEDIATEtempSQL。MSSQL的print函数，对应Oracle的DBMS_OUTPUT.PUT_LINE('')函数。此外还有各自使用的数据表，有所不同。例如MSSQL中所有的对象都在sysobjects表中，而Oracle中的表在user_tables中，过程在user_procedures中等。这些需要积累一些经验。
最后不要忘了检查，Oracle的所有句子，必须要有分号表示结束。而MSSQL中不需要，即使加了也不错。几步下来，MSSQL过程就转化成Oracle。