使用 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_needed CONSTANT 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 ;