MSSQL和Oracle之間的轉化

這兩天寫數據庫升級腳本，發現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中使用IF()....ELSE....，結構體之間都要用BEGIN和END框起來。而Oracle則使用IF...THEN...ELSE..END IF結構，中間不必使用BEGIN和END。此外While結構差別也類似。

第六步修改各自的調用方法和函數。