使用Cursor:
--測試一下，今天才申請使用itpub.net 的blog
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在游標使用入口參數(shù)
在SQL語句的Where 子句中恰當使用 相關語句簡化邏輯，本來需要使用兩個游標，把相關入口參數(shù)放入到SQL語句的Where 子句中，一個就搞定了：
cursor crRoom is
select
distinct 樓層,房屋用途
from TT_沒有處理的房屋 t
where 數(shù)據(jù)級別>= 0 and 房屋處理類別= 3 and 產(chǎn)權編號=p_產(chǎn)權編號
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;
3.2.使用對象的屬性
-- Author : XJG
-- Created : 2002.03.01 20:52:22
-- Purpose : 產(chǎn)生拆遷數(shù)據(jù)的報表數(shù)據(jù)
procedure GenConCQReport (
p_UserID in Common.SEQType, ---0 =使用權,1 = 高級使用權,2 = 機團
p_UserType in Common.THundred) is
type TTitleArr is varray (5) of varchar2 (20);
v_Title TTitleArr
:= TTitleArr ('可以簽訂的財產(chǎn)',
'已經(jīng)簽訂的財產(chǎn)',
'可能可以簽定的財產(chǎn)',
'不能簽訂的財產(chǎn)',
'已簽定的合同'
);
v_DateLevel TTitleArr := TTitleArr ('1', '2', '3', '4', '5');
begin
forall i in v_Title.first .. v_Title.last
update TT_拆遷數(shù)據(jù)分類 T
set T.數(shù)據(jù)級別 = to_number (v_DateLevel (i))
where (T.財產(chǎn)編號 is not null)
and (T.財產(chǎn)編號, T.財產(chǎn)類別) in (
select TT.財產(chǎn)編號, TT.財產(chǎn)類別
from TT_拆遷數(shù)據(jù)分類 TT
start with TT.標題 = v_Title (i)
connect by prior TT.拆遷數(shù)據(jù)分類編號 =
TT.上級編號);
end;
3.3匿名游標
匿名游標,是我自己給的一個稱呼。在游標FOR循環(huán)中可以定義查詢，由于沒有顯式聲明所以游標沒有名字，記錄名通過游標查詢來定義。
DECALRE
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_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
END;
