oracle中的cursor属性有哪些,自己整理的使用oracle Cursor的例子

使用Cursor:

--测试一下，今天才申请使用itpub.net 的blog

declare

RoomIDRoom.RoomID%Type;

RoomNameRoom.RoomName%Type;

cursor crRoomis

select RoomID,RoomName

from Room;

begin

open crRoom;loop;

fetch crRoom into RoomID,RoomName;

exit when crRoom%notFound;

end loop;

close crRoom;

end;

3.1在游标使用入口参数

在SQL语句的Where 子句中恰当使用 相关语句简化逻辑，本来需要使用两个游标，把相关入口参数放入到SQL语句的Where 子句中，一个就搞定了：

cursorcrRoomis

select

distinct楼层,房屋用途

fromTT_没有处理的房屋t

where数据级别>=0and房屋处理类别=3and产权编号=p_产权编号

and拆迁房屋类别=p_拆迁房屋类别

and面积>0and(not p_房屋用途 is null

and 房屋用途=p_房屋用途

or p_房屋用途 is null);

另外一个例子：

CREATE OR REPLACE PROCEDURE PrintStudents(

p_Major IN students.major%TYPE) AS

CURSOR c_Students IS

SELECT first_name, last_name

FROM students

WHERE major = p_Major;

BEGIN

FOR v_StudentRec IN c_Students LOOP

DBMS_OUTPUT.PUT_LINE(v_StudentRec.first_name || ' ' ||

v_StudentRec.last_name);

END LOOP;

END;

Oracle带的例子examp6.sql

DECLARECURSOR bin_cur(part_number NUMBER) IS SELECT amt_in_bin

FROM bins

WHERE part_num = part_number AND

amt_in_bin >0ORDER BY bin_num

FOR UPDATE OF amt_in_bin;

bin_amtbins.amt_in_bin%TYPE;

total_so_farNUMBER(5) :=0;

amount_neededCONSTANT NUMBER(5) :=1000;

bins_looked_atNUMBER(3) :=0;

BEGIN

OPEN bin_cur(5469);

WHILE total_so_far < amount_needed LOOP

FETCH bin_cur INTO bin_amt;

EXIT WHEN bin_cur%NOTFOUND;

/* If we exit, there's not enough to *

* satisfy the order.*/bins_looked_at := bins_looked_at +1;

IF total_so_far + bin_amt < amount_needed THEN

UPDATE bins SET amt_in_bin =0WHERE CURRENT OF bin_cur;

-- take everything in the bintotal_so_far := total_so_far + bin_amt;

ELSE-- we finally have enoughUPDATE bins SET amt_in_bin = amt_in_bin

- (amount_needed - total_so_far)

WHERE CURRENT OF bin_cur;

total_so_far := amount_needed;

END IF;

END LOOP;

CLOSE bin_cur;

INSERT INTO temp VALUES (NULL, bins_looked_at,'

COMMIT;

END;

-- Created on 2004-8-9 by ADMINISTRATOR

declare

--带有变量的Cursor

cursor crBooks(c_bookTitle varchar2) is

select *

from booksa

where a.title likec_bookTitle||'%';

begin

for v_Books in crBooks('Oracle8') loop

dbms_output.put_line(v_Books.author1);

end loop;

end;

3.2.使用对象的属性

-- Author: XJG

-- Created : 2002.03.01 20:52:22

-- Purpose :产生拆迁数据的报表数据

procedure GenConCQReport (

p_UserIDinCommon.SEQType, ---0 =使用权,1 =高级使用权,2 =机团

p_UserTypeinCommon.THundred) is

typeTTitleArrisvarray (5) of varchar2 (20);

v_TitleTTitleArr

:= TTitleArr ('可以签订的财产',

'已经签订的财产',

'可能可以签定的财产',

'不能签订的财产',

'已签定的合同'

);

v_DateLevelTTitleArr := TTitleArr ('1', '2', '3', '4', '5');

begin

forall i in v_Title.first .. v_Title.last

update TT_拆迁数据分类T

set T.数据级别= to_number (v_DateLevel (i))

where (T.财产编号is not null)

and (T.财产编号, T.财产类别) in (

selectTT.财产编号, TT.财产类别

from TT_拆迁数据分类TT

startwithTT.标题= v_Title (i)

connectby prior TT.拆迁数据分类编号=

TT.上级编号);

end;

3.3匿名游标

匿名游标,是我自己给的一个称呼。在游标FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过游标查询来定义。

DECALRE

v_tot_salary EMP.SALARY%TYPE;

BEGIN

FORr_dept IN(SELECT deptno,dname FROM dept ORDER BY deptno) LOOP

DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);

v_tot_salary:=0;

DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);

END

LOOP

;

END;