使用oralce Cursor

使用Cursor: declareRoomID;;;;Room.RoomID%Type;RoomName;;Room.RoomName%Type;cursor crRoom; is select RoomID,RoomName ;;from Room; beginopen 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.sqlDECLARE 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 --带有变量的Cursorcursor 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;