Oracle与MSSQL过程之间的转化

      这两天写数据库升级脚本，发现MSSQL和Oracle之间的转化还是比较轻易的。

       以下面两个过程为例。两者的功能相似。

       1.MSSQL脚本 1

/**//** 更改表名 **/
2

Begin
3

declare @tempPoTableName varchar(50) --性能对象表名
4

declare @tempPoSpName varchar(50) --性能过程名
5

declare @errorInfo varchar(200) --错误信息
6

declare @cnt int --计数器
7

declare @tempSQL varchar(1000)
9

--定义表名、同步表名和存储过程游标
11

set @tempSQL = ' declare allValues_Cursor cursor for '+CHAR(13) + CHAR(10)
12

set @tempSQL = @tempSQL + ' select POTABLENAME,POSPNAME from PM_NEPODEF_TABLE WHERE POID>110499 and POID<110580'
13

EXEC (@tempSQL)
14

OPEN allValues_Cursor
16

--判定是否由符合游标条件的行，假如没有则关闭和释放游标，异常返回
18

IF(@@CURSOR_ROWS = 0 )
19

BEGIN
20

CLOSE allValues_Cursor
21

DEALLOCATE allValues_Cursor
22

set @errorInfo = '没有指定表名或存储过程名！'
23

print @errorInfo
24

return
25

END
26

print '开始更改原有表名……'
28

FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName,@tempPoSpName
29

--根据给定的表名、存储过程名创建相应的数据存储存储过程
30

WHILE (@@FETCH_STATUS <> -1)
31

BEGIN
32

print @tempPoTableName
33

IF (EXISTS (SELECT name from sysobjects WHERE name=@tempPoTableName))
35

BEGIN
36

set @tempSQL = 'ALTER TABLE '+ @tempPoTableName+' DROP constraint PK_'+@tempPoTableName
37

EXEC (@tempSQL)
38

set @tempSQL = @tempPoTableName+'_TMP'
39

EXEC Sp_rename @tempPoTableName,@tempSQL
40

END
41

ELSE
42

BEGIN
43

print '没有找到表'+@tempPoTableName;
44

END
45

IF (EXISTS (SELECT name from sysobjects WHERE name=@tempPoSpName))
47

BEGIN
48

set @tempSQL = 'DROP PROCEDURE '+@tempPoSpName;
49

EXEC (@tempSQL)
50

END
51

ELSE
52

BEGIN
53

print '没有找到过程'+@tempPoSpName;
54

END
55

FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName,@tempPoSpName
57

END
58

CLOSE allValues_Cursor
59

DEALLOCATE allValues_Cursor
60

print '结束更改原有表名……'
61

print '------------------------'
62

END
63

GO

2.ORACLE脚本 1

BEGIN
2

DECLARE
3

tempPoTableName varchar2(50); --性能对象表名
4

tempPoSpName varchar2(50); --性能过程名
5

errorInfo varchar2(200); --错误信息
6

tempSQL varchar2(1000);
7

cnt1 number(1);
8

cnt2 number(2);
9

--定义表名、同步表名和存储过程游标
11

Cursor allValues_Cursor is
12

select UPPER(TRIM(POTABLENAME)),UPPER(TRIM(POSPNAME)) from PM_NEPODEF_TABLE WHERE POID>110499 and POID<110580;
13

BEGIN
15

OPEN allValues_Cursor;
16

--判定是否由符合游标条件的行，假如没有则关闭和释放游标，异常返回
18

DBMS_OUTPUT.PUT_LINE('开始更改原有表名……');
20

FETCH allValues_Cursor INTO tempPoTableName,tempPoSpName;
21

--根据给定的表名、存储过程名创建相应的数据存储存储过程
22

WHILE allValues_Cursor%found LOOP
23

cnt1:=0;
25

cnt2:=0;
26

BEGIN
27

SELECT 1 INTO cnt1 FROM dual WHERE exists(SELECT table_name FROM user_tables WHERE table_name = tempPoTableName);
28

SELECT 1 INTO cnt2 FROM dual WHERE exists(SELECT OBJECT_NAME FROM user_procedures WHERE OBJECT_NAME = tempPoSpName);
29

exception
30

WHEN no_data_found THEN
31

null;
32

END;
33

IF cnt1 = 1 THEN
35

DBMS_OUTPUT.PUT_LINE(tempPoTableName);
36

tempSQL := 'ALTER TABLE 'tempPoTableName' DROP constraint PK_'tempPoTableName;
37

EXECUTE IMMEDIATE tempSQL;
38

tempSQL := 'ALTER TABLE 'tempPoTableName' RENAME TO 'tempPoTableName'_TMP';
39

EXECUTE IMMEDIATE tempSQL;
40

ELSE
41

DBMS_OUTPUT.PUT_LINE('没有找到表'tempPoTableName);
42

END IF;
43

IF cnt2 = 1 THEN
45

tempSQL := 'DROP PROCEDURE 'tempPoSpName;
46

EXECUTE IMMEDIATE tempSQL;
47

ELSE
48

DBMS_OUTPUT.PUT_LINE('没有找到过程'tempPoSpName);
49

END IF;
50

FETCH allValues_Cursor INTO tempPoTableName,tempPoSpName;
52

END LOOP;
53

CLOSE allValues_Cursor;
54

DBMS_OUTPUT.PUT_LINE('结束更改原有表名……');
55

DBMS_OUTPUT.PUT_LINE('------------------------');
56

END;
57

END;
58

/
       上面两个是无名存储过程，不需要考虑是否已经存在该过程。对于有名的过程需要考虑对象是否已经存在。
        我是从MSSQL向Oracle转化的。
      第一步，修改整体结构。
      MSSQL的总体结构如下，只需要一个begin和end，中间加入变量声明。
1

Begin
2

declare --变量
3

--过程
4

END
5

GO Oralce的总体结构如下，需要两个begin和end，一个是整个过程，一个是除去申明之外的过程。 1

BEGIN
2

DECLARE
3

--变量
4

BEGIN
5

--过程
6

END;
7

END;
8

/
        第二步，修改声明变量。
        MSSQL需要在每个变量前面加 declare标示，Oracle只需要一个declare标示。此外注重修改各自的数据类型。

        第三步，修改游标。复杂的过程中离不开游标。因此更改游标结构经常用到。
        MSSQL的游标是全局的，需要建立之后再清空。而Oracle的游标类似于局部变量，使用完之后，自动清除。
        MSSQL游标结构如下：

set @tempSQL = ' declare allValues_Cursor cursor for '+CHAR(13) + CHAR(10)

set @tempSQL = @tempSQL + ' select POTABLENAME,POSPNAME from PM_NEPODEF_TABLE WHERE POID>110499 and POID<110580'

--游标语句

EXEC (@tempSQL)

--1.创建游标

OPEN allValues_Cursor

--2.打开游标

--判定是否由符合游标条件的行，假如没有则关闭和释放游标，异常返回

IF(@@CURSOR_ROWS = 0 )

BEGIN

CLOSE allValues_Cursor

DEALLOCATE allValues_Cursor

set @errorInfo = '没有指定表名或存储过程名！'

print @errorInfo

return

END

WHILE (@@FETCH_STATUS <> -1)

BEGIN
FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName,@tempPoSpName

--3进行数据处理

END

CLOSE allValues_Cursor

--4.关闭游标

DEALLOCATE allValues_Cursor

--5.注销游标
Oracle的游标是在变量中声明定义的，然后在过程中使用。其结构如下： 1

--声明中
2

Cursor allValues_Cursor is
3

select UPPER(TRIM(POTABLENAME)),UPPER(TRIM(POSPNAME)) from PM_NEPODEF_TABLE WHERE POID>110499 and POID<110580;
4

--1.声明游标
5

--过程中
6

OPEN allValues_Cursor;
7

--2.打开游标
8

WHILE allValues_Cursor%found LOOP
10

FETCH allValues_Cursor INTO tempPoTableName,tempPoSpName;
11

--3.处理数据
12

END LOOP;
14

CLOSE allValues_Cursor;
15

        --4.关闭游标
        第四步修改赋值语句和比较语句。MSSQL中使用Set语句来赋值，Oracle中使用:=来赋值。此外MSSQL中的变量习惯前面增加一个@字符，在Oracle中可以删除。
        第五步修改逻辑结构。MSSQL中使用IF()....ELSE....
，结构体之间都要用BEGIN和END框起来。而Oracle则使用IF...THEN...ELSE..END IF结构，中间不必使用BEGIN和END。此外While结构差别也类似。
        第六步修改各自的调用方法和函数。常见的是MSSQL的EXEC (@tempSQL)，对应Oracle的EXECUTE IMMEDIATE tempSQL。MSSQL的print函数，对应Oracle的DBMS_OUTPUT.PUT_LINE('')函数。此外还有各自使用的数据表，有所不同。例如MSSQL中所有的对象都在sysobjects表中，而Oracle中的表在user_tables中，过程在user_procedures中等。这些需要积累一些经验。

         最后不要忘了检查，Oracle的所有句子，必须要有分号表示结束。而MSSQL中不需要，即使加了也不错。几步下来，MSSQL过程就转化成Oracle。