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 infoomaniD, roomname;
Exit when crroom% notfound;
End loop;
Close Crroom;
END;
3.1 Inlet parameters in the cursor
In the case of the WHERE clause of the SQL statement, simplify the logic. It is necessary to use two cursors, put the relevant entry parameters into the WHERE clause of the SQL statement, one will be done:
Cursor Crroom Is Select Distinct Floor, Housing Use from TT_ Nothing House T Where Data Level> = 0 And House Processing Category = 3 And Property Number = P_ Property Number AND Demolition Housing Category = P_ Demolition Housing Category And Area> 0 and (not p_ housing use is Null and housing use = p_ housing use or P_ housing 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;
Example examp6.sql with oracle
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 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;
