这两天写数据库升级脚本,发现MSSQL和Oracle之间的转化还是比较轻易的。
以下面两个过程为例。两者的功能相似。
1.MSSQL脚本
1
/**//** 更改表名 **/2
Begin3
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_Cursor16
17
--判定是否由符合游标条件的行,假如没有则关闭和释放游标,异常返回18
IF(@@CURSOR_ROWS = 0 )19
BEGIN20
CLOSE allValues_Cursor21
DEALLOCATE allValues_Cursor22
set @errorInfo = '没有指定表名或存储过程名!'23
PRint @errorInfo24
return25
END26
27
print '开始更改原有表名……'28
FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName,@tempPoSpName29
--根据给定的表名、存储过程名 创建相应的数据存储存储过程30
WHILE (@@FETCH_STATUS <> -1)31
BEGIN32
print @tempPoTableName33
34
IF (EXISTS (SELECT name from sysobjects WHERE name=@tempPoTableName))35
BEGIN36
set @tempSQL = 'ALTER TABLE '+ @tempPoTableName+' DROP constraint PK_'+@tempPoTableName37
EXEC (@tempSQL)38
set @tempSQL = @tempPoTableName+'_TMP'39
EXEC Sp_rename @tempPoTableName,@tempSQL40
END41
ELSE42
BEGIN43
print '没有找到表'+@tempPoTableName;44
END45
46
IF (EXISTS (SELECT name from sysobjects WHERE name=@tempPoSpName))47
BEGIN48
set @tempSQL = 'DROP PROCEDURE '+@tempPoSpName;49
EXEC (@tempSQL)50
END51
ELSE52
BEGIN53
print '没有找到过程'+@tempPoSpName;54
END55
56
FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName,@tempPoSpName57
END58
CLOSE allValues_Cursor59
DEALLOCATE allValues_Cursor60
print '结束更改原有表名……'61
print '------------------------'62
END63
GO
2.ORACLE脚本
1
BEGIN2
DECLARE3
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 is12
select UPPER(TRIM(POTABLENAME)),UPPER(TRIM(POSPNAME)) from PM_NEPODEF_TABLE WHERE POID>110499 and POID<110580;13
14
BEGIN15
OPEN allValues_Cursor;16
17
--判定是否由符合游标条件的行,假如没有则关闭和释放游标,异常返回18
19
DBMS_OUTPUT.PUT_LINE('开始更改原有表名……');20
FETCH allValues_Cursor INTO tempPoTableName,tempPoSpName;21
--根据给定的表名、存储过程名 创建相应的数据存储存储过程22
WHILE allValues_Cursor%found LOOP23
24
cnt1:=0;25
cnt2:=0;26
BEGIN27
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
exception30
WHEN no_data_found THEN31
null;32
END;33
34
IF cnt1 = 1 THEN35
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
ELSE41
DBMS_OUTPUT.PUT_LINE('没有找到表'tempPoTableName);42
END IF;43
44
IF cnt2 = 1 THEN45
tempSQL := 'DROP PROCEDURE 'tempPoSpName;46
EXECUTE IMMEDIATE tempSQL;47
ELSE48
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
Begin2
declare --变量3
--过程4
END5
GOOralce的总体结构如下,需要两个begin和end,一个是整个过程,一个是除去申明之外的过程。
1
BEGIN2
DECLARE3
--变量4
BEGIN5
--过程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) BEGINFETCH NEXT FROM allValues_Cursor INTO @tempPoTableName,@tempPoSpName
--3进行数据处理 END CLOSE allValues_Cursor --4.关闭游标 DEALLOCATE allValues_Cursor --5.注销游标
Oracle的游标是在变量中声明定义的,然后在过程中使用。其结构如下:
1
--声明中2
Cursor allValues_Cursor is3
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 LOOP10
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。