1.選用何種游標(biāo)?

顯示游標(biāo)分為：普通游標(biāo)，參數(shù)化游標(biāo)和游標(biāo)變量三種。

create or replace procedure proc(p varchar2)

as

v_rownum number(10) := 1;

cursor c1 is select ename from emp where rownum = 1;

cursor c2 is select ename from emp where rownum = v_rownum;

cursor c3(p_rownum number) is select ename from emp where rownum = p_rownum;

type c_c is ref cursor;

c4 c_c;

v1 varchar2(20);

begin

?open c1;

?fetch c1 into v1;

?dbms_output.put_line('1.' || v1);

?close c1;

?open c2;

?fetch c2 into v1;

?dbms_output.put_line('2.' || v1);

?close c2;

?open c3(1);

?fetch c3 into v1;

?dbms_output.put_line('3.' || v1);

?close c3;

?open c4?for?select ename from emp where rownum = 1;

?fetch c4 into v1;

?dbms_output.put_line('4.' || v1);

?close c4;

end; ?

-- 調(diào)用

call ? proc(1);

-- 說(shuō)明

cursor c1 is select ename from emp where rownum = 1;

這一句是定義了一個(gè)最普通的游標(biāo)，把整個(gè)查詢已經(jīng)寫(xiě)死,調(diào)用時(shí)不可以作任何改變。

cursor c2 is select ename from emp where rownum = v_rownum;

這一句并沒(méi)有寫(xiě)死，查詢參數(shù)由變量v_rownum來(lái)決定。需要注意的是v_rownum必須在這個(gè)游標(biāo)定義之前聲明。

cursor c3(p_rownum number) is select ename from emp where rownum = p_rownum;

這一條語(yǔ)句與第二條作用相似，都是可以為游標(biāo)實(shí)現(xiàn)動(dòng)態(tài)的查詢。但是它進(jìn)一步的縮小了參數(shù)的作用域范圍。但是可讀性降低了不少。

type c_c is ref cursor;

c4 c_c;

先定義了一個(gè)引用游標(biāo)類型，然后再聲明了一個(gè)游標(biāo)變量。

open c4 for select ename from emp where rownum = 1;

然后再用open for 來(lái)打開(kāi)一個(gè)查詢。需要注意的是它可以多次使用,用來(lái)打開(kāi)不同的查詢。

從動(dòng)態(tài)性來(lái)說(shuō)，游標(biāo)變量是最好用的，但是閱讀性也是最差的。

注意，游標(biāo)的定義只能用使關(guān)鍵字IS，它與AS不通用。

2.游標(biāo)的循環(huán)策略

create or replace procedure proc1

as

cursor c1 is select ename,sal from emp ;

v1 varchar2(20);

v2 number(4);

begin

?open c1;

?if?c1%found?= true then

? ?dbms_output.put_line('found true ...');

?elsif?c1%found?= false then

? ?dbms_output.put_line('found false ...');

?else

? ?dbms_output.put_line('found null ...');

?end if;

?--1.loop循環(huán)

?loop

? ?fetch c1 into v1,v2;

? ?exit when c1%notfound;

? ?dbms_output.put_line('ename: ' || v1 || ',val:' || v2);

?end loop;

?dbms_output.put_line('--- loop end ...');

?close c1;

/*exit when語(yǔ)句一定要緊跟在fetch之后。必避免多余的數(shù)據(jù)處理。

處理邏輯需要跟在exit when之后。這一點(diǎn)需要多加小心。

循環(huán)結(jié)束后要記得關(guān)閉游標(biāo)*/

?--2.while循環(huán)

?open c1;

fetch c1 into v1,v2;

?while c1%found loop

? ? ?dbms_output.put_line('ename: ' || v1 || ',val:' || v2);

? ? ?fetch c1 into v1,v2;

?end loop;

?close c1; ?

?dbms_output.put_line('---while end---'); ?

?/*

我們知道了一個(gè)游標(biāo)打開(kāi)后，必須執(zhí)行一次fetch語(yǔ)句，游標(biāo)的屬性才會(huì)起作用。

所以使用while 循環(huán)時(shí)，就需要在循環(huán)之前進(jìn)行一次fetch動(dòng)作。

而且數(shù)據(jù)處理動(dòng)作必須放在循環(huán)體內(nèi)的fetch方法之前。

循環(huán)體內(nèi)的fetch方法要放在最后。否則就會(huì)多處理一次。這一點(diǎn)也要非常的小心。

總之，使用while來(lái)循環(huán)處理游標(biāo)是最復(fù)雜的方法。

?*/

? --3.for循環(huán)

? for v in c1 loop

? ? v1 := v.ename;

? ? v2 := v.sal;

? ? dbms_output.put_line('ename: ' || v1 || ',val:' || v2);

? end loop;

? dbms_output.put_line('---for end---'); ?

/*

可見(jiàn)for循環(huán)是比較簡(jiǎn)單實(shí)用的方法。

首先，它會(huì)自動(dòng)open和close游標(biāo)。解決了你忘記打開(kāi)或關(guān)閉游標(biāo)的煩惱。

其它,自動(dòng)定義了一個(gè)記錄類型及聲明該類型的變量，并自動(dòng)fetch數(shù)據(jù)到這個(gè)變量中。

我們需要注意v 這個(gè)變量無(wú)需要在循環(huán)外進(jìn)行聲明，無(wú)需要為其指定數(shù)據(jù)類型。

它應(yīng)該是一個(gè)記錄類型，具體的結(jié)構(gòu)是由游標(biāo)決定的。

這個(gè)變量的作用域僅僅是在循環(huán)體內(nèi)。

把v看作一個(gè)記錄變量就可以了，如果要獲得某一個(gè)值就像調(diào)用記錄一樣就可以了。

如v.ename

由此可見(jiàn)，for循環(huán)是用來(lái)循環(huán)游標(biāo)的最好方法。高效，簡(jiǎn)潔，安全。

但遺憾的是，常常見(jiàn)到的卻是第一種方法。所以從今之后得改變這個(gè)習(xí)慣了

*/

end;

--調(diào)用

call proc1();

說(shuō)明：

在打開(kāi)一個(gè)游標(biāo)之后，馬上檢查它的%found或%notfound屬性，

它得到的結(jié)果即不是true也不是false.而是null.

必須執(zhí)行一條fetch語(yǔ)句后，這些屬性才有值。

3.select into不可忽視的問(wèn)題

<1.>

我們知道在pl/sql中要想從數(shù)據(jù)表中向變量賦值，需要使用select into 子句。

但是它會(huì)帶動(dòng)來(lái)一些問(wèn)題，如果查詢沒(méi)有記錄時(shí)，會(huì)拋出no_data_found異常。

如果有多條記錄時(shí)，會(huì)拋出too_many_rows異常。

這個(gè)是比較糟糕的。一旦拋出了異常，就會(huì)讓過(guò)程中斷。

特別是no_data_found這種異常，沒(méi)有嚴(yán)重到要讓程序中斷的地步，可以完全交給由程序進(jìn)行處理。 ?

eg1:

create or replace procedure proc2

AS

? ? ?v varchar2(20);

begin

?dbms_output.put_line('---開(kāi)始：================');

select ename into v from emp where 1 = 0;

?dbms_output.put_line('---' || v);

exception

?when no_data_found then

? ? ?dbms_output.put_line('no data found...。。。');

end;

--調(diào)用

call proc2();

<2.>

說(shuō)明：加exception → 這樣做換湯不換藥，程序仍然被中斷。

? ? ?可能這樣不是我們所想要的。

select into做為一個(gè)獨(dú)立的塊，在這個(gè)塊中進(jìn)行異常處理 。

? 這是一種比較好的處理方式了。不會(huì)因?yàn)檫@個(gè)異常而引起程序中斷。

? 如下面的例子：

eg2:

create or replace procedure proc3

as

? ? ?v varchar2(20);

begin

begin

? ?dbms_output.put_line('---begin...========');

select ename into v from emp where 1 = 0;

? ?dbms_output.put_line('---' || v);

exception

? ?when no_data_found then

? ? ?dbms_output.put_line('no data found...give new value...');

? ? ?v := '';

end; ? ?

? dbms_output.put_line('v :' || v);

end;

-- 調(diào)用：

call proc3();

<3.>使用游標(biāo)：這樣就完全的避免了no_data_found異常。完全交由程序員來(lái)進(jìn)行控制了。

eg3:

create or replace procedure proc4

as

? ? ? v varchar2(20);

cursor c is select ename from emp where 1=0;

begin

?open c;

? ?dbms_output.put_line('---begin...========');

fetch c into v;

? ?dbms_output.put_line('v :' || v);

?close c;

? ?dbms_output.put_line('end...v :' || v);

end;

--

call proc4(); ? ?

4. ?too_many_rows 異常的問(wèn)題。 ?

Too_many_rows 這個(gè)問(wèn)題比起no_data_found要復(fù)雜一些。

給一個(gè)變量賦值時(shí)，但是查詢結(jié)果有多個(gè)記錄。

處理這種問(wèn)題也有兩種情況：

<1>． 多條數(shù)據(jù)是可以接受的，也就是說(shuō)從結(jié)果集中隨便取一個(gè)值就行。

這種情況應(yīng)該很極端了吧，如果出現(xiàn)這種情況，也說(shuō)明了程序的嚴(yán)謹(jǐn)性存在問(wèn)題。

<2>． 多條數(shù)據(jù)是不可以被接受的，在這種情況肯定是程序的邏輯出了問(wèn)題，也說(shuō)是說(shuō)原來(lái)根本就不會(huì)想到它會(huì)產(chǎn)生多條記錄。

對(duì)于第一種情況，就必須采用游標(biāo)來(lái)處理，而對(duì)于第二種情況就必須使用內(nèi)部塊來(lái)處理，重新拋出異常。

多條數(shù)據(jù)可以接受,隨便取一條，這個(gè)跟no_data_found的處理方式一樣，使用游標(biāo)。

我這里僅說(shuō)第二種情況，不可接受多條數(shù)據(jù)，但是不要忘了處理no_data_found哦。

這就不能使用游標(biāo)了，必須使用內(nèi)部塊。

需要注意的是一定要加上對(duì)no_data_found的處理，對(duì)出現(xiàn)多條記錄的情況則繼續(xù)拋出異常，讓上一層來(lái)處理。

總之對(duì)于select into的語(yǔ)句需要注意這兩種情況了。需要妥當(dāng)處理啊。

eg4:

create or replace procedure proc5

as

? ? ? v varchar2(20);

begin

begin

select ename into v from emp where rownum < 5;

exception

? ? ?when no_data_found then

? ? ? ?v := null;

when too_many_rows then ?

? ? ? raise_application_error('-20000','對(duì)v賦值時(shí)，找到多行記錄！');

? ?end;

? ?dbms_output.put_line(v);

end; ?

--

call proc5(); ? ?

5.在存儲(chǔ)過(guò)程中返回結(jié)果集

見(jiàn)參考文章吧：→ 【http://www.cnblogs.com/chinafine/archive/2010/07/12/1776102.html】

------------------demo---------------------

create or replace procedure proc6(v1 varchar2,v2 number)

as

? ? ? total number(4) := 0;

? ? ? cursor c is select * from empa ;-- where 1=0;

begin

?if v1is not null??and v2?!= 0?then

? ?dbms_output.put_line('ok' || v1 || v2); ? ?

?elsif v1?is null?then

? ? ?dbms_output.put_line('v1 is null'); ?

-- elsif v1 = '' then

?-- ? ?dbms_output.put_line('v1 is kong...'); ?

?elsif v2 = 0 then

? ? ?dbms_output.put_line('v2 is 0'); ? ? ?

?end if;

for varObj in c loop

total := c%Rowcount ;

? ? dbms_output.put_line(c%Rowcount || 'empno :' || varObj.empno || 'ename: ' || varObj.ename || 'sal:' || varObj.sal);

? end loop;

? dbms_output.put_line('total:' || total);

? if?total <= 0?then

? ? ?dbms_output.put_line('total:' || total);

?raise_application_error('ORA-00973','rownumber is zero!');

? end if;

end;

--

call proc6(null,1);

call proc6('',1);-- ''會(huì)被認(rèn)為是null

call proc6('tom',0);

==========================2013-12-17-說(shuō)明===================

1.texit用來(lái)跳出循環(huán) ?2.return跳出存儲(chǔ)過(guò)程.參考地址：http://sunrise-king.iteye.com/blog/391374

http://www.jb51.net/article/34230.htm

? ? ?本文轉(zhuǎn)自韓立偉 51CTO博客，原文鏈接：http://blog.51cto.com/hanchaohan/1340791，如需轉(zhuǎn)載請(qǐng)自行聯(lián)系原作者

總結(jié)

以上是生活随笔為你收集整理的oracle存储过程的简单学习2的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。