Oracle與MSSQL過(guò)程之間的轉(zhuǎn)化

2024-08-29 13:34:15

字體：大中小

供稿：網(wǎng)友

      這兩天寫(xiě)數(shù)據(jù)庫(kù)升級(jí)腳本，發(fā)現(xiàn)MSSQL和Oracle之間的轉(zhuǎn)化還是比較輕易的。

       以下面兩個(gè)過(guò)程為例。兩者的功能相似。

       1.MSSQL腳本 1

/**//** 更改表名 **/
2

Begin
3

declare @tempPoTableName varchar(50) --性能對(duì)象表名
4

declare @tempPoSpName varchar(50) --性能過(guò)程名
5

declare @errorInfo varchar(200) --錯(cuò)誤信息
6

declare @cnt int --計(jì)數(shù)器
7

declare @tempSQL varchar(1000)
9

--定義表名、同步表名和存儲(chǔ)過(guò)程游標(biāo)
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

OPEN allValues_Cursor
16

--判定是否由符合游標(biāo)條件的行，假如沒(méi)有則關(guān)閉和釋放游標(biāo)，異常返回
18

IF(@@CURSOR_ROWS = 0 )
19

BEGIN
20

CLOSE allValues_Cursor
21

DEALLOCATE allValues_Cursor
22

set @errorInfo = '沒(méi)有指定表名或存儲(chǔ)過(guò)程名！'
23

PRint @errorInfo
24

return
25

END
26

print '開(kāi)始更改原有表名……'
28

FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName,@tempPoSpName
29

--根據(jù)給定的表名、存儲(chǔ)過(guò)程名創(chuàng)建相應(yīng)的數(shù)據(jù)存儲(chǔ)存儲(chǔ)過(guò)程
30

WHILE (@@FETCH_STATUS <> -1)
31

BEGIN
32

print @tempPoTableName
33

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 '沒(méi)有找到表'+@tempPoTableName;
44

END
45

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 '沒(méi)有找到過(guò)程'+@tempPoSpName;
54

END
55

FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName,@tempPoSpName
57

END
58

CLOSE allValues_Cursor
59

DEALLOCATE allValues_Cursor
60

print '結(jié)束更改原有表名……'
61

print '------------------------'
62

END
63

GO

2.ORACLE腳本 1

BEGIN
2

DECLARE
3

tempPoTableName varchar2(50); --性能對(duì)象表名
4

tempPoSpName varchar2(50); --性能過(guò)程名
5

errorInfo varchar2(200); --錯(cuò)誤信息
6

tempSQL varchar2(1000);
7

cnt1 number(1);
8

cnt2 number(2);
9

--定義表名、同步表名和存儲(chǔ)過(guò)程游標(biāo)
11

Cursor allValues_Cursor is
12

select UPPER(TRIM(POTABLENAME)),UPPER(TRIM(POSPNAME)) from PM_NEPODEF_TABLE WHERE POID>110499 and POID<110580;
13

BEGIN
15

OPEN allValues_Cursor;
16

--判定是否由符合游標(biāo)條件的行，假如沒(méi)有則關(guān)閉和釋放游標(biāo)，異常返回
18

DBMS_OUTPUT.PUT_LINE('開(kāi)始更改原有表名……');
20

FETCH allValues_Cursor INTO tempPoTableName,tempPoSpName;
21

--根據(jù)給定的表名、存儲(chǔ)過(guò)程名創(chuàng)建相應(yīng)的數(shù)據(jù)存儲(chǔ)存儲(chǔ)過(guò)程
22

WHILE allValues_Cursor%found LOOP
23

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

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('沒(méi)有找到表'tempPoTableName);
42

END IF;
43

IF cnt2 = 1 THEN
45

tempSQL := 'DROP PROCEDURE 'tempPoSpName;
46

EXECUTE IMMEDIATE tempSQL;
47

ELSE
48

DBMS_OUTPUT.PUT_LINE('沒(méi)有找到過(guò)程'tempPoSpName);
49

END IF;
50

FETCH allValues_Cursor INTO tempPoTableName,tempPoSpName;
52

END LOOP;
53

CLOSE allValues_Cursor;
54

DBMS_OUTPUT.PUT_LINE('結(jié)束更改原有表名……');
55

DBMS_OUTPUT.PUT_LINE('------------------------');
56

END;
57

END;
58

/
       上面兩個(gè)是無(wú)名存儲(chǔ)過(guò)程，不需要考慮是否已經(jīng)存在該過(guò)程。對(duì)于有名的過(guò)程需要考慮對(duì)象是否已經(jīng)存在。
        我是從MSSQL向Oracle轉(zhuǎn)化的。
      第一步，修改整體結(jié)構(gòu)。
      MSSQL的總體結(jié)構(gòu)如下，只需要一個(gè)begin和end，中間加入變量聲明。
1

Begin
2

declare --變量
3

--過(guò)程
4

END
5

GO Oralce的總體結(jié)構(gòu)如下，需要兩個(gè)begin和end，一個(gè)是整個(gè)過(guò)程，一個(gè)是除去申明之外的過(guò)程。1

BEGIN
2

DECLARE
3

--變量
4

BEGIN
5

--過(guò)程
6

END;
7

END;
8

/
        第二步，修改聲明變量。
        MSSQL需要在每個(gè)變量前面加 declare標(biāo)示，Oracle只需要一個(gè)declare標(biāo)示。此外注重修改各自的數(shù)據(jù)類型。

        第三步，修改游標(biāo)。復(fù)雜的過(guò)程中離不開(kāi)游標(biāo)。因此更改游標(biāo)結(jié)構(gòu)經(jīng)常用到。
        MSSQL的游標(biāo)是全局的，需要建立之后再清空。而Oracle的游標(biāo)類似于局部變量，使用完之后，自動(dòng)清除。
        MSSQL游標(biāo)結(jié)構(gòu)如下：

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'

--游標(biāo)語(yǔ)句

EXEC (@tempSQL)

--1.創(chuàng)建游標(biāo)

OPEN allValues_Cursor

--2.打開(kāi)游標(biāo)

--判定是否由符合游標(biāo)條件的行，假如沒(méi)有則關(guān)閉和釋放游標(biāo)，異常返回

IF(@@CURSOR_ROWS = 0 )

BEGIN

CLOSE allValues_Cursor

DEALLOCATE allValues_Cursor

set @errorInfo = '沒(méi)有指定表名或存儲(chǔ)過(guò)程名！'

print @errorInfo

return

END

WHILE (@@FETCH_STATUS <> -1)

BEGIN
FETCH NEXT FROM allValues_Cursor INTO @tempPoTableName,@tempPoSpName

--3進(jìn)行數(shù)據(jù)處理

END

CLOSE allValues_Cursor

--4.關(guān)閉游標(biāo)

DEALLOCATE allValues_Cursor

--5.注銷(xiāo)游標(biāo)
Oracle的游標(biāo)是在變量中聲明定義的，然后在過(guò)程中使用。其結(jié)構(gòu)如下： 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.聲明游標(biāo)
5

--過(guò)程中
6

OPEN allValues_Cursor;
7

--2.打開(kāi)游標(biāo)
8

WHILE allValues_Cursor%found LOOP
10

FETCH allValues_Cursor INTO tempPoTableName,tempPoSpName;
11

--3.處理數(shù)據(jù)
12

END LOOP;
14

CLOSE allValues_Cursor;
15

        --4.關(guān)閉游標(biāo)
        第四步修改賦值語(yǔ)句和比較語(yǔ)句。MSSQL中使用Set語(yǔ)句來(lái)賦值，Oracle中使用:=來(lái)賦值。此外MSSQL中的變量習(xí)慣前面增加一個(gè)@字符，在Oracle中可以刪除。
        第五步修改邏輯結(jié)構(gòu)。MSSQL中使用IF()....ELSE....
，結(jié)構(gòu)體之間都要用BEGIN和END框起來(lái)。而Oracle則使用IF...THEN...ELSE..END IF結(jié)構(gòu)，中間不必使用BEGIN和END。此外While結(jié)構(gòu)差別也類似。
        第六步修改各自的調(diào)用方法和函數(shù)。常見(jiàn)的是MSSQL的EXEC (@tempSQL)，對(duì)應(yīng)Oracle的EXECUTE IMMEDIATE tempSQL。MSSQL的print函數(shù)，對(duì)應(yīng)Oracle的DBMS_OUTPUT.PUT_LINE('')函數(shù)。此外還有各自使用的數(shù)據(jù)表，有所不同。例如MSSQL中所有的對(duì)象都在sysobjects表中，而Oracle中的表在user_tables中，過(guò)程在user_procedures中等。這些需要積累一些經(jīng)驗(yàn)。

         最后不要忘了檢查，Oracle的所有句子，必須要有分號(hào)表示結(jié)束。而MSSQL中不需要，即使加了也不錯(cuò)。幾步下來(lái)，MSSQL過(guò)程就轉(zhuǎn)化成Oracle。

上一篇：Oracle9i中MERGE的用法

下一篇：oracle 9206升級(jí)到10.2.0.2簡(jiǎn)要步驟