使用Cursor:
declare

RoomID;;;;Room.RoomID%Type;

RoomName;;Room.RoomName%Type;

cursor crRoom; is

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 子句中，一个就搞定了：

cursor;crRoom;is
select;
distinct; 楼层,房屋用途
;;;from;;;TT_没有处理的房屋; t
where;;数据级别>= 0
and房屋处理类别= 3
and产权编号=p_产权编号
and拆迁房屋类别=p_拆迁房屋类别
and面积>0
and(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

DECLARE
CURSOR bin_cur(part_number NUMBER) IS SELECT amt_in_bin
FROM bins
WHERE part_num = part_number AND

;;;;amt_in_bin > 0
ORDER BY bin_num
FOR UPDATE OF amt_in_bin;
bin_amt bins.amt_in_bin%TYPE;
total_so_far;NUMBER(5) := 0;
amount_neededCONSTANT NUMBER(5) := 1000;
bins_looked_at; NUMBER(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 = 0
WHERE CURRENT OF bin_cur
-- take everything in the bin
total_so_far := total_so_far + bin_amt;
ELSE;;;;;-- we finally have enough
UPDATE 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, '<- bins looked at');
COMMIT;
END;





-- Created on 2004-8-9 by ADMINISTRATOR
declare
--带有变量的Cursor
cursor crBooks(c_bookTitle varchar2) is
select *
from books; a

where a.title like; c_bookTitle'%';
begin
for v_Books in crBooks('Oracle8') loop
dbms_output.put_line(v_Books.author1);
end loop;
end;