Using Oralce Cursor

Use 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 Using entry parameters in cursors
The proper use of related statements in the WHERE clause of the SQL statement simplifies logic by using two cursors, putting the relevant entry parameters into the WHERE clause of the SQL statement, and one is done:

Cursor Crroom is
Select
Distinct floors, housing uses
From Tt_ did not deal with the house T
Where data level >= 0
and housing Treatment category = 3
and Title number =p_ property number
And demolition of the housing category =p_ Demolition Housing category
and area >0
and (not P_ house use is null
and housing use =P_ housing use
or p_ house use is null);

Another example:

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;



Examples of Oracle bands 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 with variables
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