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     
 8      declare   @tempSQL      varchar ( 1000 )
 9     
10      -- 定义表名、同步表名和存储过程游标
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
15      OPEN  allValues_Cursor
16
17      -- 判断是否由符合游标条件的行，如果没有则关闭和释放游标，异常返回
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     
27      print   ' 开始更改原有表名…… '
28      FETCH    NEXT   FROM  allValues_Cursor  INTO   @tempPoTableName , @tempPoSpName
29      -- 根据给定的表名、存储过程名 创建相应的数据存储存储过程
30      WHILE   ( @@FETCH_STATUS   <>   - 1 )
31      BEGIN
32          print   @tempPoTableName
33         
34          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
46          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
56      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     
10      -- 定义表名、同步表名和存储过程游标
11      Cursor  allValues_Cursor  is
12           select   UPPER (TRIM(POTABLENAME)), UPPER (TRIM(POSPNAME))  from  PM_NEPODEF_TABLE  WHERE  POID > 110499   and  POID < 110580 ;
13              
14 BEGIN
15      OPEN  allValues_Cursor;
16
17      -- 判断是否由符合游标条件的行，如果没有则关闭和释放游标，异常返回
18     
19     DBMS_OUTPUT.PUT_LINE( ' 开始更改原有表名…… ' );
20      FETCH   allValues_Cursor  INTO  tempPoTableName,tempPoSpName;
21      -- 根据给定的表名、存储过程名 创建相应的数据存储存储过程
22      WHILE  allValues_Cursor % found LOOP
23     
24     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     
34      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     
44      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     
51          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
 9      WHILE  allValues_Cursor % found LOOP
10      FETCH  allValues_Cursor  INTO  tempPoTableName,tempPoSpName;
11          -- 3.处理数据
12
13      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。

          

转载于:https://www.cnblogs.com/xiaoyz/archive/2006/07/28/462344.html