SQL Server --> Oracle

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

网管下载dl.bitscn.com

　　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中發現了很多用到臨時表的地方;…… 网管网www.bitscn.com
　　你也一定發現了在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進行碎片整理以確保性能.

中国网管联盟bitsCN.com

　　但是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最讓人不滿意的是它必須優化!”過了一個季度甚至於一個月,我們的用戶就會向我們抱怨我們的系統跑得越來越慢了.又提到了我前面說過的話:我們的絕大多數程式沒有受到真正的考驗,如果不考慮清楚的話,我們的系統將有嚴重的問題.

网管联盟bitsCN_com

　　
　　對於原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存儲過程.

网管论坛bbs_bitsCN_com

　　在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;'; 网管bitscn_com
　　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的原表結構:

网管u家u.bitscn@com

　　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 p