SQL Server --> Oracle (一)

SQL Server --> Oracle (一)

Support Oracle
zhuzhichao 2002.4

讓基於Oracle開發的程式Support SQL Server或許是輕鬆而又愉快的;但是讓基於SQL Server開發的Support Oracle可不是那麼簡單,你可能面對大量接踵而來的問題並要為此做相當多的工作.
Oracle以其良好的穩定性和安全性受到了眾多DBA們的推崇和好評,但其糟糕的可操作性和易用性也讓絕大多數的Programmer感到深惡痛絕.
SQL Server的T-SQL是那麼的自由靈活,Oracle的PL/SQL又是那麼的嚴謹刻板,SQL Server --> Oracle會讓你感到那麼的不習慣.
你是否花了兩整天未曾調通一個300行的sp?……
你是否為了一個疑難問題耽誤了好幾天工期並為此輾轉難眠?……

如果是,請繼續看下面的內容.

一. basic
二. 如何返回結果集?
三. 關於臨時表
四. 執行動態SQL
五. 自增的identity如何移植?
六. 關於觸發器
七. 內聯結,外聯結,全聯結
八. 想要F2正常開窗還需做哪些工作?
九. Top n與rownum
十. 性能優化
十一. 其他

......

二. 如何返回結果集?
“天啦.Oracle的sp不能返回結果集!!!”……
在SQL ServeràOracle的工作中你是否會發出這樣的驚呼?……
在Oracle的任何PL/SQL語句塊中所有的select語句必須要有into子句!
這就是Oracle的sp不能返回結果集的原因!
任何的事情都有其解決的辦法,你千萬不要為此而氣餒.下面的是解決的例程:
create or replace package pkg_test
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); --
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm );
end p_test;
/
Java程式:
……
CallableStatement call = conn.prepareCall("{ call p_test(?) }");
call.registerOutParameter(1, OracleTypes.CURSOR);// 註冊out參數的SQL數據類型
call.execute();
ResultSet rs=(ResultSet)call.getObject(1);// 取得得數据結果集合
while(rs.next())
……


......
三. 關於臨時表
你一定在程式中和SQL Server的sp中發現了很多用到臨時表的地方;……
你也一定發現了在Oracle的sp中不能create table;……
你還一定發現了原來Oracle中的臨時表和SQL Server中的臨時表根本不是
一個概念,它無法達到SQL Server中臨時表的作用!……
這一切都是為什麼???又將如何解決?

抱著這麼多的疑問,我們先來回顧一下SQL Server的臨時表.
SQL Server的臨時表有兩種:局部臨時表(#temp)和全局臨時表(##temp)
局部臨時表是獨立於每一個session的,對於每一個session來說,就算都create一個同名的#temp但是每個session都獨立操作自己的#temp,互相不干擾.session退出後自動釋放自己的#temp.
全局臨時表一旦創建,所有的session都可以使用,只不過只有當創建全局臨時表的session退出後,全局臨時表才能自動釋放.
值得一提的是,SQL Server的臨時表全部都存儲在tempdb數據庫中,SQL SERVER6.5有tempdb in RAM的選項,自7.0之後的版本雖然取消了該屬性.但只要你的tempdb分配了足夠大的硬盤空間,在多用戶併發操作時臨時表的性能就不會降低.並且每隔一段週期SQL SERVER就自動會對tempdb進行碎片整理以確保性能.
但是Oracle中的臨時表和SQL Server中的臨時表的處理機制全然不同.Oracle的臨時表頂多只能相當於SQL Server中的全局臨時表,而且它會永久存在,如果你自己不去drop它的話,Oracle是不會自動將其釋放的.而且SQL Server中的局部臨時表在Oracle中無法替代,因此我在Oracle中放棄了對臨時表的使用,所有的臨時表全部改為永久表.
在Oracle的sp中不能出現DDL語句.因為所有的DDL語句(create,drop,alter,truncate等)都是顯式帶有commit命令,Oracle的sp中不能有顯式commit的存在.如果你非要在sp中建表或者刪除表的話,你可以用動態SQL來完成隱式commit.
例如:
execute immediate “create table ……”;
關於動態SQL,後面將有一節將做出詳細的介紹.

在這裡將有可能有兩個誤區,處理不當將影響性能或者導致程式出錯:
1. 在sp一開始直接用動態SQL建永久表,然後對表進行操作,sp退出之前再用動態SQL刪除永久表.
請注意了,在多用戶併發操作的時候.A session調用了該sp並成功創建了表, B session也調用該sp試圖去創建這張表的時候.Oracle會很野蠻的將sp中斷,然後我們的客戶就會看到很不友好的出錯框.
2. 為了讓多用戶使用互不干擾,由程式生成sessionid傳入sp或者利用Oracle的函數userenv(‘sessionid’)生成sessionid.然後在sp中用動態SQL生成表名+sessionid的表,對該表進行操作,sp退出時將其刪除.
但這樣會有一個問題:由於sp被經常調用導致不斷的建表刪表.而Oracle的表都存放在表空間上.這樣大量的DDL語句會讓表空間內的碎片不斷的增多而表空間將不斷增大,要知道Oracle的碎片整理必須要手動進行,它不會像SQL SERVER那樣自動整理.”Oracle最讓人滿意的是它可以優化,Oracle最讓人不滿意的是它必須優化!”過了一個季度甚至於一個月,我們的用戶就會向我們抱怨我們的系統跑得越來越慢了.又提到了我前面說過的話:我們的絕大多數程式沒有受到真正的考驗,如果不考慮清楚的話,我們的系統將有嚴重的問題.

對於原SQL SERVER中的臨時表移植Oracle中我的處理方法如下:
1. 創建1-Module-CreateTempTable.sql文件:
所有原SQL SERVER中用到的臨時表都在此建立,只是每張表多一個字段sessionid int,如果表有主鍵,那麼將sessionid加入主鍵.
2. 創建0-Module-DropTempTable.sql文件,裡面的內容是:
begin
for r in (select 'drop table '||object_name as sqls from user_objects where object_type = ' TABLE' and object_name in (‘temp1’,’temp2’,……)) loop -- 所有的臨時表都寫在in中
execute immediate (r.sqls);
end loop;
end;
這兩個SQL文件讓我們的程式自動運行.
3. 由程式生成sessionid或者通過userenv(‘sessionid’) 生成sessionid寫入表的sessionid字段.每個session只處理本session的數據.

注:在Oracle中用如下方式建立的臨時表同SQL Server的全局臨時表效果相當:
CREATE GLOBAL TEMPORARY TABLE temp1
(……
……
sessionid int)
on commit delete rows；

四. 執行動態SQL
一個動態SQL語句是在運行時而不是在編譯時建立和執行的,在編譯時,用戶如果不了解查詢的結構或者想要查詢的對象,就可以使用動態SQL.但是一般而言,運行動態SQL的系統資源花費是運行同樣的靜態SQL的兩到三倍.因為每次執行動態SQL的時候都必須重新對它進行語法分析.由於此點因素,希望大家盡可能的少使用動態SQL.
SQL Server用exec(字符串)可以執行動態SQL,如果需要從執行的動態SQL中得到結果值的話可以用sp_executesql存儲過程.
在Oracle中,可以使用DBMS_SQL包和execute immediate ‘……’來執行動態SQL,不過要注意的是execute immediate是Oracle8i才推出的新特性,在Oracle8及以前的版本中是不能用這種方式的.
例程如下:
SQL SERVER:
declare @count int
declare @SQL nvarchar(200)
set @SQL = N'select count(*) from sysobjects'
exec sp_executesql @SQL,N'@i int output',@count output
print @count
Oracle:
set serveroutput on
/
declare
i_count int ;
v_SQL varchar2(200) := 'begin select count(*) into :1 from user_objects; end;';
begin
execute immediate v_SQL using out i_count;
dbms_output.put_line(i_count);
end;
/

DBMS_SQL包使用起來較為複雜繁瑣而且顯式使用游標,不提倡使用,故此處不做說明.
備註:有一點需要注意了, execute immediate所能執行的字符串的長度是有限制的,如果超過了這個限制,那麼只能用DBMS_SQL包了.



五. 自增的identity如何移植?
SQL Server中的identity特性給我們的工作帶來了很大的方便,我們可以利用它方便的進行排序.但是在Oracle中卻沒有這樣的特性.Oracle只有sequence的概念.sequence是事務無關性的,sequence並不是依附在表的上面,它是獨立存在的,和事務是無關的.但是我們可以利用sequence來達到SQL SERVER中identity的效果.例程如下:
SQL SERVER的原表結構:
create table ftm07 -- 應收票據異動
(
ftg00c TYPE_v_CMPID not null, -- 公司別
ftg01c varchar(20) not null, -- 票據號碼
ftg02f int identity not null, -- 流水號
ftg03d TYPE_v_DATE null , -- 狀況處理日
ftg04c TYPE_v_ENUM null , -- 狀況
ftg06c varchar(20) null , -- 傳票管理編號
ftg07c varchar(2) null , -- 票據異動別
ftg08c varchar(20) null , -- 票據異動單號
constraint PK_FTM07 primary key (ftg00c, ftg01c, ftg02f)
)
移植至Oracle中的做法:
create table ftm07 -- 應收票據異動
(
ftg00c varchar2(3) not null, -- 公司別
ftg01c varchar2(20) not null, -- 票據號碼
ftg02f int not null, -- 流水號 /* SEQUENCE */
ftg03d varchar2(8) null , -- 狀況處理日
ftg04c varchar2(1) null , -- 狀況
ftg06c varchar2(20) null , -- 傳票管理編號
ftg07c varchar2(2) null , -- 票據異動別
ftg08c varchar2(20) null , -- 票據異動單號
constraint PK_FTM07 primary key (ftg00c, ftg01c, ftg02f)
);
CREATE SEQUENCE ftm07_seq INCREMENT BY 1;

create or replace TRIGGER Cash_ftm07_insert_before -- 增加一個insert之前的觸發器
before insert on ftm07
for each row
declare
i_id integer;
begin
select ftm07_seq.nextval into i_id from dual;
:NEW.ftg02f := i_id;
end;
可以從上面看到,如同SQL SERVER一樣,你並不需要對ftg02f字段進行操作,觸發器會幫你搞定一切.但是有一個不同需要注意了:sequence的值不能被手工重置
在SQL SERVER中可以通過dbcc checkident(表名,reseed,0)或者truncate table tableName(如果數據也不需要的話)將表中的identity欄位重置為1,而Oracle的sequence做不到這點,sequence只有達到最大值後,系統才會自動將其重置為預定的最小值.

__________________
zhuzhichao@itpub.net

.
SQL Server --> Oracle (二)

Support Oracle
zhuzhichao 2002.4

六. 關於觸發器
SQL Server的觸發器都是在觸發語句之後執行,而且是語句級的.也就是說一條語句只能導致一次觸發,而不管它有多少行紀錄被更改,由於虛擬表inserted和deleted的存在,確保了每行紀錄都能被引用.而Oracle的觸發器分為觸發語句之前執行或者觸發語句之後執行,語句級或者行級組合起來的4種方式.
將SQL Server的觸發器移植至Oracle上,你可以創建一個觸發語句之後的行級觸發器,切記,一定要用行級觸發器而不能用語句級觸發器.因為Oracle的觸發器模式中沒有像SQL Server那樣的inserted和deleted虛表的存在,而只有:new和ld.:new和ld不能算成是二維的表,它只是紀錄字段更新之前的值和更新之後的值.
在Oracle中,游標的概念貫穿整個PL/SQL,游標在Oracle中被廣泛應用.就連每一個update語句和delete語句都有一個內置的隱式的游標!因此,我們可以使用行級觸發器加ld及:new來達到SQL Server中的語句級觸發器加inserted及deleted的效果.
例程如下:
SQL SERVER:
create trigger Cash_frm02_delete
on dbo.frm02
for delete
as
if @@rowcount=0
return
delete frm05
from frm05 f,deleted d
where f.fre00c = d.frb00c and f.fre01c = d.frb01c and f.fre02c = d.frb02c
if @@error <> 0
goto error_handler
return
error_handler:
begin
rollback transaction
return
end
Oracle:
create or replace trigger Cash_frm02_delete
after delete
on frm02
for each row
begin
delete from frm05
where fre00c = :OLD.frb00c and fre01c = :OLD.frb01c and fre02c = :OLD.frb02c;
exception
when others then
rollback;
end Cash_frm02_delete;

七. 內聯結,外聯結,全聯結
內聯結(inner join)也就是我們平常的等聯結.在SQL-92標準之中,內部聯結可指定於FROM或WHERE子句中.這是 SQL-92在WHERE子句中唯一支援的聯結類型.
SQL-92標準中的外聯結分為left outer join和right outer join.傳統的語法是*=和=*。SQL Server 同時支援 SQL-92 外部聯結語法與傳統語法，以便以使用 *= 與 =* 運算子於 WHERE 子句中的方式來指定外部聯結。建議您使用 SQL-92 語法，因為它並不會產生傳統 Transact-SQL 外部聯結有時所產生的模擬兩可現象，並且MS還聲稱在以後的版本中將有可能不再支持*=和=*,這點請大家注意了.
全聯結:SQL-92 FULL OUTER JOIN 運算子指出兩個資料表中的所有資料列都必須包含於結果之中，而不管資料表中是否有符合的資料。SQL Server支持全聯結,但不幸的是:Oracle並不支持全聯結,如果你想達到全聯結的效果,那麼只有使用union了.
Oracle的PL/SQL並不是非常的支持SQL-92標準,它有許多地方的做法都與標準不同,它的外聯結的與眾不同.
Oracle的外聯結符號是(+),並且在整個select語句中,只能有一張表做為主表和其他的表進行外聯.如果你的SQL Server中用到兩張或者兩張以上的表作為主表的話,那麼只有完全改寫.並且在SQL Server的外聯語句中如果用到了in或者or的話,那轉成Oracle將頗費周折.
例程如下:
SQL Server:
select a.COMP_ID,a.TRANS_NO,b.PART_NO from TransMaster a
left outer join TransDetail b on
(a.COMP_ID = b.COMP_ID and a.TRANS_NO = b.TRANS_NO)
Oracle:
select a.COMP_ID,a.TRANS_NO,b.PART_NO from TransMaster a,TransDetail b
where a.COMP_ID = b.COMP_ID(+) and a.TRANS_NO = b.TRANS_NO(+);
//請注意(+)這個符號應該緊挨著從表的字段出現


......

九. Top n與rownum
在SQL Server中可以用top n來返回指定數量的紀錄,Oracle中與之對應的是rownum,只是不同的是:SQL Server的top n是邏輯順序返回的紀錄,而Oracle的rownum指定的是物理順序的序號,其物理的序號是在order by之前就已經決定好了的.因此下面的語句在Oracle中都是錯誤的:
錯誤語句1:
select rownum,fom01.* from fom01 where rownum < 11 order by foa02d;
//這條語句永遠也得不到正確的,你想要的結果.你可以運行一下看看結果.
//因為rownum是物理順序的序號,是在排序之前就已經決定好了的.
糾正後的語句:
select rownum,ss.* from
(select * from fom01 order by foa02d) ss where rownum < 11;
但是要注意這樣的語句在Oracle8i之後才能正常運行,因為Oracle8i才能在子查詢中使用order by.
錯誤語句2:
select rownum,fom01.* from fom01 where rownum = 2;
//這條語句將返回不了任何的值,但是將2改成1就可以返回一條紀錄。因為Oracle的rownum只有在生成了1後才能有2,同樣的有了2才能有3,以此類推.其實這個道理也很簡單,你有沒有看過一棟房子沒有蓋好1樓就蓋好了2樓懸在半空? ^_^
糾正後的語句:
select * from (select rownum r,fom01.* from fom01 where rownum <= 2)
where r > 1;

......

十一. 其他
還有一些SQL Server與Oracle的不同需要注意。
SQL Server Oracle
T-SQL無須;來表示結束每個語句 PL/SQL使用分號;結束每個語句
T-SQL用go來表示批次執行 T-SQL用/來表示批次執行
@@rowcount sql%rowcount
@@spid Userenv(‘sessionid’)
@@FETCH_STATUS 游標名%found
object_id(‘object_name’) select object_id from user_objects
nvarchar,varchar字段之間互相轉換用convert(varchar(n),字段名)和convert(nvarchar(n),字段名) nvarchar,varchar字段之間互相轉換用translate(字段名 using char_cs)和translate(字段名 using nchar_cs)
有if exists(結果集)的寫法 沒有if exists(結果集)的寫法
字符串用+連接 字符串用||連接
賦值語句用set或者select 賦值語句用pascal的賦值方法:=
sp的形參需要指定長度 sp的形參不需要指定長度
case … when … then … else … end decode()
update語句可以from結果集 update語句不能from結果集
insert語句可以只寫insert insert語句必須寫insert into
查看表結構用sp_help tableName 查看表結構用desc tableName
結果集需要指定別名 結果集不需要指定別名
給表或結果集取別名可以用as 給表或結果集取別名不能用as
日期加減用dateadd和datediff函數 日期加減直接用+號-號
當前日期和時間用getdate() 當前日期和時間用sysdate
suser_id(),user_id() uid
user_name(),suser_name() user
like 支持[]通配符 like 不支持[]通配符
打印輸出用print 打印輸出用dbms_output.put_line()
http://www.oradb.net/tran/08.htm
http://www.oradb.net/tran/09.htm
http://www.oradb.net/tran/ora2sql.htm

__________________
zhuzhichao@itpub.net