MSSQL和Oracle之间的转化
时间：2010-09-17 来源：缘分星空
这两天写数据库升级脚本，发现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。
相关阅读 更多 +
