這兩天寫數據庫升級腳本
發現MSSQL和Oracle之間的轉化還是比較容易的
以下面兩個過程為例
兩者的功能相似
MSSQL腳本
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083242.gif)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083240.gif)
/**//** 更改表名 **/
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
Begin
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
declare @tempPoTableName varchar(
)
性能對象表名
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
declare @tempPoSpName varchar(
)
性能過程名
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
declare @errorInfo varchar(
)
錯誤信息
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
declare @cnt int
計數器
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
declare @tempSQL varchar(
)
定義表名
同步表名和存儲過程游標
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
set @tempSQL =
declare allValues_Cursor cursor for
+CHAR(
) + CHAR(
)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
set @tempSQL = @tempSQL +
select POTABLENAME
POSPNAME from PM_NEPODEF_TABLE WHERE POID>
and POID<
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
EXEC (@tempSQL)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
OPEN allValues_Cursor
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
判斷是否由符合游標條件的行
如果沒有則關閉和釋放游標
異常返回
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
IF(@@CURSOR_ROWS =
)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
CLOSE allValues_Cursor
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DEALLOCATE allValues_Cursor
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
set @errorInfo =
沒有指定表名或存儲過程名!
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
print @errorInfo
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
return
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
print
開始更改原有表名……
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName
@tempPoSpName
根據給定的表名
存儲過程名 創建相應的數據存儲存儲過程
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
WHILE (@@FETCH_STATUS <>
)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
print @tempPoTableName
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
IF (EXISTS (SELECT name from sysobjects WHERE name=@tempPoTableName))
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
set @tempSQL =
ALTER TABLE
+ @tempPoTableName+
DROP constraint PK_
+@tempPoTableName
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
EXEC (@tempSQL)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
set @tempSQL = @tempPoTableName+
_TMP
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
EXEC Sp_rename @tempPoTableName
@tempSQL
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
ELSE
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
print
沒有找到表
+@tempPoTableName;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
IF (EXISTS (SELECT name from sysobjects WHERE name=@tempPoSpName))
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
set @tempSQL =
DROP PROCEDURE
+@tempPoSpName;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
EXEC (@tempSQL)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
ELSE
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
print
沒有找到過程
+@tempPoSpName;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName
@tempPoSpName
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
CLOSE allValues_Cursor
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DEALLOCATE allValues_Cursor
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
print
結束更改原有表名……
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
print
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
GO
ORACLE腳本
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DECLARE
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
tempPoTableName varchar
(
);
性能對象表名
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
tempPoSpName varchar
(
);
性能過程名
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
errorInfo varchar
(
);
錯誤信息
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
tempSQL varchar
(
);
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
cnt
number(
);
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
cnt
number(
);
定義表名
同步表名和存儲過程游標
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
Cursor allValues_Cursor is
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
select UPPER(TRIM(POTABLENAME))
UPPER(TRIM(POSPNAME)) from PM_NEPODEF_TABLE WHERE POID>
and POID<
;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
OPEN allValues_Cursor;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
判斷是否由符合游標條件的行
如果沒有則關閉和釋放游標
異常返回
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DBMS_OUTPUT
PUT_LINE(
開始更改原有表名……
);
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
FETCH allValues_Cursor INTO tempPoTableName
tempPoSpName;
根據給定的表名
存儲過程名 創建相應的數據存儲存儲過程
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
WHILE allValues_Cursor%found LOOP
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
cnt
:=
;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
cnt
:=
;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
SELECT
INTO cnt
FROM dual WHERE exists(SELECT table_name FROM user_tables WHERE table_name = tempPoTableName);
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
SELECT
INTO cnt
FROM dual WHERE exists(SELECT OBJECT_NAME FROM user_procedures WHERE OBJECT_NAME = tempPoSpName);
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
exception
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
WHEN no_data_found THEN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
null;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
IF cnt
=
THEN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DBMS_OUTPUT
PUT_LINE(tempPoTableName);
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
tempSQL :=
ALTER TABLE
||tempPoTableName||
DROP constraint PK_
||tempPoTableName;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
EXECUTE IMMEDIATE tempSQL;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
tempSQL :=
ALTER TABLE
||tempPoTableName||
RENAME TO
||tempPoTableName||
_TMP
;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
EXECUTE IMMEDIATE tempSQL;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
ELSE
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DBMS_OUTPUT
PUT_LINE(
沒有找到表
||tempPoTableName);
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END IF;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
IF cnt
=
THEN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
tempSQL :=
DROP PROCEDURE
||tempPoSpName;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
EXECUTE IMMEDIATE tempSQL;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
ELSE
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DBMS_OUTPUT
PUT_LINE(
沒有找到過程
||tempPoSpName);
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END IF;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
FETCH allValues_Cursor INTO tempPoTableName
tempPoSpName;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END LOOP;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
CLOSE allValues_Cursor;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DBMS_OUTPUT
PUT_LINE(
結束更改原有表名……
);
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DBMS_OUTPUT
PUT_LINE(
);
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
/
上面兩個是無名存儲過程不需要考慮是否已經存在該過程對於有名的過程需要考慮對象是否已經存在
我是從MSSQL向Oracle轉化的
第一步修改整體結構
MSSQL的總體結構如下只需要一個begin和end中間加入變量聲明
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
Begin
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
declare
變量
過程
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
GO
Oralce的總體結構如下需要兩個begin和end一個是整個過程一個是除去申明之外的過程
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DECLARE
變量
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
過程
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
/
第二步修改聲明變量
MSSQL需要在每個變量前面加 declare標示Oracle只需要一個declare標示此外注意修改各自的數據類型
第三步修改游標復雜的過程中離不開游標因此更改游標結構經常用到
MSSQL的游標是全局的需要建立之後再清空而Oracle的游標類似於局部變量使用完之後自動清除
MSSQL游標結構如下
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
set @tempSQL =
declare allValues_Cursor cursor for
+CHAR(
) + CHAR(
)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
set @tempSQL = @tempSQL +
select POTABLENAME
POSPNAME from PM_NEPODEF_TABLE WHERE POID>
and POID<
游標語句
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
EXEC (@tempSQL)
創建游標
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
OPEN allValues_Cursor
打開游標
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
判斷是否由符合游標條件的行
如果沒有則關閉和釋放游標
異常返回
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
IF(@@CURSOR_ROWS =
)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
CLOSE allValues_Cursor
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DEALLOCATE allValues_Cursor
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
set @errorInfo =
沒有指定表名或存儲過程名!
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
print @errorInfo
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
return
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
WHILE (@@FETCH_STATUS <>
)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
BEGIN
FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName
@tempPoSpName
進行數據處理
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
CLOSE allValues_Cursor
關閉游標
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
DEALLOCATE allValues_Cursor
注銷游標
Oracle的游標是在變量中聲明定義的然後在過程中使用其結構如下
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
聲明中
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
Cursor allValues_Cursor is
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
select UPPER(TRIM(POTABLENAME))
UPPER(TRIM(POSPNAME)) from PM_NEPODEF_TABLE WHERE POID>
and POID<
;
聲明游標
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
過程中
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
OPEN allValues_Cursor;
打開游標
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
WHILE allValues_Cursor%found LOOP
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
FETCH allValues_Cursor INTO tempPoTableName
tempPoSpName;
處理數據
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
END LOOP;
![](http://tw.wingwit.com/Article/UploadFiles/201311/2013111316083218.gif)
CLOSE allValues_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
From:http://tw.wingwit.com/Article/program/Oracle/201311/17883.html