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结构差别也类似。

第六步修改各自的调用方法和函数。常见的是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。

HDMI 19 pin Male to HDMI 19 pin Male Black 1.5M Data CableUSB A sinking SMT&DIP 180° RoHS compliant connector OEM adapter connectorDVI24+5 Male 180°heightening type plugboard connector pin OEM adapter connectorMicro 5P AB/F SMT connector cellphone connector LG mobile phone connectorMini 4P B/M USB connector OEM usb plug socket adapterPCI 1394 card 3+1 ports up to 400Mbps data transfer rate support plug and play PCI cardStylish 4Gb USB 2.0 Flash Storage DriveUSB B/M short body wire solder (L=20.7mm)connector plug OEM adapter connectorUSB 2.0 Data Charger Adapter Cable for Samsung Phone8DBi Radar direction antenna wireless lan card detachable antenna DSSS spread spectrumSilver Foldable 4 Ports USB 2.0 Hub for PC LaptopHDMI-02B.HDMI 19P/F with PCB full coverage HDMI connectorDVI 24+5 Female to HDMI Male adapter connector with long life time OEMIPOD 30P welding on board(L=23.0mm) connector cellphone connector usb adapter mobile phone connectorCool Ratatable 512Mb USB 2.0 Flash Storage DriveHDMI-08E.HDMI 19P/M wire solder HDMI connector HDMI high definition data plugLG 12P straight head connector cellphone connector LG mobile phone connectorSwitching power supply adapter card Bi-directional high speed transmission cardBattery Shaped 64Mb USB 2.0 Flash MemoryPremium HDMI Male to Male Cable 1.3 Gold For PS3 1080p HDTV NewNew 90W chager adapter with 8 renewable DC plugs IBM series 124*56*39 mmIPOD 30P welding on board(L=29.6mm) connector cellphone connector usb adapter mobile phone connectorUSB 2.0 Data Charger Adapter Retractable Cable for Samsung PhoneBlue 8Gb USB 2.0 Flash MemoryUSB B/F short body(L=16.1mm)connector plug OEM adapter connectorHDMI 19P/F SMT Dual ear HDMI high definition data plug HDMI connector3.5" IDE Hard Drive Enclosure Case USB + Firewire 1394SUPER VGA Male to Female MONITOR/LCD/PROJECTOR CABLE WhiteIPod audio transitions cable & USB cableFLP Mini 10P Female sinking USB connector cellphone connector mobile connectorPCI -Express to 2 serial port expansion card support plug and play MosChip / NetMOS chip PCI cardNew 90W charger with 8 renewable DC plugs ZM-60 series 124*56*41 mmUSB DATA CHARGER SYNC CABLE FOR iPhone iPod Nano Touch3.5 IDE to 2.5 IDE adapter fashion design suitable for laptop transmission cardSamsung 12P Female SMT USB connector cellphone connector mobile connectorDVI 18+1 Pin Male to DVI 18+1 Pin Male CableBar Shaped 4 Ports USB HubMini 54m WLAN wireless lan card external WPS button widely coverage area8*BNC Female to 25 pin Male HD VGA Video Audio Monitor CableHDMI 19P/F SMT with screw hole H5.08mm HDMI high definition data plug HDMI connectorDVI24+1 Male solder 180° plugboard plug connector pin OEM adapter connectorUltra Mini white USB AC power adapter for ipod iPhone 2G 3G chargerSamsung 14P Male wire solder USB connector cellphone connector mobile connectorStylish Wooden 8Gb USB 2.0 Flash Memory Drive512Mb USB 2.0 Flash Memory DriveYellow Gold 8Gb USB 2.0 Flash Storage DriveStylish Foldable 2Gb USB 2.0 Flash Storage DriveWhite and Steel 10 Shaped 4 Ports USB Hub for PC LaptopNew 90W charger with 8 renewable DC plugs ZM series 5V 2.5A 12V 2A 124*56*42 mm9 pin M to F HD VGA Monitor Cable Replica lv Hangbags Replica Hangbags Hangbags replica handbags replica handbags replica handbags replica handbags usb cable usb cable usb cable usb cable usb flash drives