您现在的位置: 破洛洛 >> 建站学院 >> 网站开发 >> Oracle教程 >> 正文

细化解析:Oracle与SQL过程之间的转化

作者:佚名 来源:CNZZ 更新时间:2010-4-16
分享到

这两天写数据库升级脚本,发现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

转载请注明:破洛洛(谢谢合作)
网友评论: