分享
 
 
 

Oracle

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

讓基於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 p

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有