这两天写数据库升级脚本
发现MSSQL和Oracle之间的转化还是比较容易的
以下面两个过程为例两者的功能相似
MSSQL脚本
/**//**更改表名**/
Begin
declare@tempPoTableNamevarchar()性能对象表名
declare@tempPoSpNamevarchar()性能过程名
declare@errorInfovarchar()错误信息
declare@cntint计数器
declare@tempSQLvarchar()
定义表名同步表名和存储过程游标
set@tempSQL=declareallValues_Cursorcursorfor+CHAR()+CHAR()
set@tempSQL=@tempSQL+selectPOTABLENAMEPOSPNAMEfromPM_NEPODEF_TABLEWHEREPOID>andPOID<
EXEC(@tempSQL)
OPENallValues_Cursor
判断是否由符合游标条件的行如果没有则关闭和释放游标异常返回
IF(@@CURSOR_ROWS=)
BEGIN
CLOSEallValues_Cursor
DEALLOCATEallValues_Cursor
set@errorInfo=没有指定表名或存储过程名！
print@errorInfo
return
END
print开始更改原有表名……
FETCHNEXTFROMallValues_CursorINTO@tempPoTableName@tempPoSpName
根据给定的表名存储过程名创建相应的数据存储存储过程
WHILE(@@FETCH_STATUS<>)
BEGIN
print@tempPoTableName
IF(EXISTS(SELECTnamefromsysobjectsWHEREname=@tempPoTableName))
BEGIN
set@tempSQL=ALTERTABLE+@tempPoTableName+DROPconstraintPK_+@tempPoTableName
EXEC(@tempSQL)
set@tempSQL=@tempPoTableName+_TMP
EXECSp_rename@tempPoTableName@tempSQL
END
ELSE
BEGIN
print没有找到表+@tempPoTableName;
END
IF(EXISTS(SELECTnamefromsysobjectsWHEREname=@tempPoSpName))
BEGIN
set@tempSQL=DROPPROCEDURE+@tempPoSpName;
EXEC(@tempSQL)
END
ELSE
BEGIN
print没有找到过程+@tempPoSpName;
END
FETCHNEXTFROMallValues_CursorINTO@tempPoTableName@tempPoSpName
END
CLOSEallValues_Cursor
DEALLOCATEallValues_Cursor
print结束更改原有表名……
print
END
GO
ORACLE脚本
BEGIN
DECLARE
tempPoTableNamevarchar();性能对象表名
tempPoSpNamevarchar();性能过程名
errorInfovarchar();错误信息
tempSQLvarchar();
cntnumber();
cntnumber();
定义表名同步表名和存储过程游标
CursorallValues_Cursoris
selectUPPER(TRIM(POTABLENAME))UPPER(TRIM(POSPNAME))fromPM_NEPODEF_TABLEWHEREPOID>andPOID<;
BEGIN
OPENallValues_Cursor;
判断是否由符合游标条件的行如果没有则关闭和释放游标异常返回
DBMS_OUTPUTPUT_LINE(开始更改原有表名……);
FETCHallValues_CursorINTOtempPoTableNametempPoSpName;
根据给定的表名存储过程名创建相应的数据存储存储过程
WHILEallValues_Cursor%foundLOOP
cnt:=;
cnt:=;
BEGIN
SELECTINTOcntFROMdualWHEREexists(SELECTtable_nameFROMuser_tablesWHEREtable_name=tempPoTableName);
SELECTINTOcntFROMdualWHEREexists(SELECTOBJECT_NAMEFROMuser_proceduresWHEREOBJECT_NAME=tempPoSpName);
exception
WHENno_data_foundTHEN
null;
END;
IFcnt=THEN
DBMS_OUTPUTPUT_LINE(tempPoTableName);
tempSQL:=ALTERTABLE||tempPoTableName||DROPconstraintPK_||tempPoTableName;
EXECUTEIMMEDIATEtempSQL;
tempSQL:=ALTERTABLE||tempPoTableName||RENAMETO||tempPoTableName||_TMP;
EXECUTEIMMEDIATEtempSQL;
ELSE
DBMS_OUTPUTPUT_LINE(没有找到表||tempPoTableName);
ENDIF;
IFcnt=THEN
tempSQL:=DROPPROCEDURE||tempPoSpName;
EXECUTEIMMEDIATEtempSQL;
ELSE
DBMS_OUTPUTPUT_LINE(没有找到过程||tempPoSpName);
ENDIF;
FETCHallValues_CursorINTOtempPoTableNametempPoSpName;
ENDLOOP;
CLOSEallValues_Cursor;
DBMS_OUTPUTPUT_LINE(结束更改原有表名……);
DBMS_OUTPUTPUT_LINE();
END;
END;
/
上面两个是无名存储过程不需要考虑是否已经存在该过程对于有名的过程需要考虑对象是否已经存在
我是从MSSQL向Oracle转化的
第一步修改整体结构
MSSQL的总体结构如下只需要一个begin和end中间加入变量声明
Begin
declare 变量
过程
END
GO
Oralce的总体结构如下需要两个begin和end一个是整个过程一个是除去申明之外的过程
BEGIN
DECLARE
变量
BEGIN
过程
END;
END;
/
第二步修改声明变量
MSSQL需要在每个变量前面加declare标示Oracle只需要一个declare标示此外注意修改各自的数据类型
第三步修改游标复杂的过程中离不开游标因此更改游标结构经常用到
MSSQL的游标是全局的需要建立之后再清空而Oracle的游标类似于局部变量使用完之后自动清除
MSSQL游标结构如下
set@tempSQL=declareallValues_Cursorcursorfor+CHAR()+CHAR()
set@tempSQL=@tempSQL+selectPOTABLENAMEPOSPNAMEfromPM_NEPODEF_TABLEWHEREPOID>andPOID<
游标语句
EXEC(@tempSQL)
创建游标
OPENallValues_Cursor
打开游标
判断是否由符合游标条件的行如果没有则关闭和释放游标异常返回
IF(@@CURSOR_ROWS=)
BEGIN
CLOSEallValues_Cursor
DEALLOCATEallValues_Cursor
set@errorInfo=没有指定表名或存储过程名！
print@errorInfo
return
END
WHILE(@@FETCH_STATUS<>)
BEGIN
FETCHNEXTFROMallValues_CursorINTO@tempPoTableName@tempPoSpName
进行数据处理
END
CLOSEallValues_Cursor
关闭游标
DEALLOCATEallValues_Cursor
注销游标
Oracle的游标是在变量中声明定义的然后在过程中使用其结构如下
声明中
CursorallValues_Cursoris
selectUPPER(TRIM(POTABLENAME))UPPER(TRIM(POSPNAME))fromPM_NEPODEF_TABLEWHEREPOID>andPOID<;
声明游标
过程中
OPENallValues_Cursor;
打开游标
WHILEallValues_Cursor%foundLOOP
FETCHallValues_CursorINTOtempPoTableNametempPoSpName;
处理数据
ENDLOOP;
CLOSEallValues_Cursor;
关闭游标
第四步修改赋值语句和比较语句MSSQL中使用Set语句来赋值Oracle中使用:=来赋值此外MSSQL中的变量习惯前面增加一个@字符在Oracle中可以删除
第五步修改逻辑结构MSSQL中使用IF()ELSE
结构体之间都要用BEGIN和END框起来而Oracle则使用IFTHENELSEEND IF结构中间不必使用BEGIN和END此外While结构差别也类似
第六步修改各自的调用方法和函数常见的是MSSQL的EXEC (@tempSQL)对应Oracle的EXECUTE IMMEDIATE tempSQLMSSQL的print函数对应Oracle的DBMS_OUTPUTPUT_LINE()函数此外还有各自使用的数据表有所不同例如MSSQL中所有的对象都在sysobjects表中而Oracle中的表在user_tables中过程在user_procedures中等这些需要积累一些经验
最后不要忘了检查Oracle的所有句子必须要有分号表示结束而MSSQL中不需要即使加了也不错几步下来MSSQL过程就转化成Oracle
