Cursors

1. Cursors How to step through the returned set of rows

2. Selected set • When a Select statement is issued, the returned set is a set of rows. • To step through, or process the rows in the set, it is necessary to be able to look at each one in turn. • A CURSOR allows the user to iterate through the returned set.

3. Managing explicit cursors • The cursor can be declared in any part of a PL/SQL block, subprogram or package. • Within the program body, the cursor can be OPENed, FETCHed and CLOSEd. • A cursor cannot be assigned a value or set of values – it must get its contents from a select statement.

4. Declaring a cursor CURSOR cursor_name [(parameter[, parameter]…)] [RETURN return_type] IS select_statement; • Where • return_type is a record or row in a database table • Parameter is defined as: cursor_parameter)name [IN] datatype [{:= | DEFAULT} expression]

5. Example cursor declaration DECLARE CURSOR lateOrd IS SELECT supplierorderno, (delivereddate – supplierOrderDate) from sorder where (delivereddate – supplierOrderDate) > 5 or delivereddate is null; • This cursor will contain a set of rows of supplier orders that either took more than 5 days to deliver, or have not been delivered. • The scope of the cursor is the scope of the block in which it is declared.

6. Declaring the cursor with parameters DECLARE nodays integer; CURSOR lateOrd IS SELECT supplierorderno, (delivereddate – supplierOrderDate) from sorder where (delivereddate – supplierOrderDate) > nodays or delivereddate is null;

7. Opening and closing the cursor DECLARE CURSOR lateOrd IS SELECT … … BEGIN OPEN lateOrd; … CLOSE lateOrd; END; This opens the cursor, but doesn’t fill it. If it is a parameterised cursor, it could be: OPEN lateOrd(3);

8. Fetching the cursor DECLARE CURSOR lateOrd IS SELECT supplierorderno, (delivereddate - supplierOrderDate) from sorder where (delivereddate - supplierOrderDate) > 5 or delivereddate is null; LocOrdNo sOrder.SupplierORDERNO%Type; DaysLate Integer; BEGIN OPEN lateOrd; LOOP FETCH lateOrd into LocOrdNo, DaysLate; EXIT WHEN lateOrd%NOTFOUND; dbms_output.put_line ('Order number '||LocOrdNo||' was delivered '||DaysLate||' days late.'); End Loop; Close LateOrd; END;

9. Fetching • Fetch retrieves the rows one at a time from the result set. • The named exceptions relating to cursors are: • %found, %notfound, %rowcount and %isopen. • These do not automatically raise an exception, so you must test for them.

10. Cursor loop Open LateOrd; LOOP FETCH lateOrd INTO LocOrdNo, DaysLate; dbms_output.put_line (lateOrd%rowcount||’. Order number ’||LocOrdNo||’ was ‘||DaysLate||’ days late.’); END LOOP; Dbms_output.put_line (‘Altogether, there were || LateOrd%rowcount||’ late orders’); Close LateOrd; • This numbers the late orders.

11. Other Cursor qualities • The implicit For loop can open the cursor, fetch each item individually and then close it (see next slide). • Instead of having to itemise the columns in the table, we can declare a cursor row type: • Declare • cursor s1 is … s1_rec s1%rowtype;

12. Cursors for update • I want to up the cost price of all stock items that are supplied by supplier number 101 by 10%. Declare cursor s1 is select * from stock where supplier_Id = 501 for update; s1_rec s1%rowtype; Begin for s1_rec in s1 loop update stock set unitcostprice = unitcostprice * 1.1 where current of s1; end loop; commit; End;