Cursors

1
Cursors

• For viewing and updating

2
Cursors

• How to step through the returned set of rows

3
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.

4
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.

5
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

6
Example cursor declaration

• DECLARE
• CURSOR lateOrd IS
• SELECT supplierorderno, (delivereddate
  supplierOrderDate) from sorder where
  (delivereddate supplierOrderDate) gt 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.

7
Declaring the cursor with parameters

• DECLARE
• nodays integer
• CURSOR lateOrd IS
• SELECT supplierorderno, (delivereddate
  supplierOrderDate) from sorder where
  (delivereddate supplierOrderDate) gt nodays or
  delivereddate is null

8
Opening and closing the cursor

• DECLARE
• CURSOR lateOrd IS SELECT
• BEGIN
• OPEN lateOrd
• CLOSE lateOrd
• END
• This opens the cursor, but doesnt fill it.
• If it is a parameterised cursor, it could be
• OPEN lateOrd(3)

9
Fetching the cursor

• DECLARE
• CURSOR lateOrd IS
• SELECT
• supplierorderno, (delivereddate -
  supplierOrderDate)
• from sorder
• where (delivereddate - supplierOrderDate) gt 5
• or delivereddate is null
• LocOrdNo sOrder.SupplierORDERNOType
• DaysLate Integer
• BEGIN
• OPEN lateOrd
• LOOP
• FETCH lateOrd into LocOrdNo, DaysLate
• EXIT WHEN lateOrdNOTFOUND
• dbms_output.put_line
• ('Order number 'LocOrdNo' was delivered
  'DaysLate' days late.')
• End Loop
• Close LateOrd
• END

10
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.

11
Cursor loop

• Open LateOrd
• LOOP
• FETCH lateOrd INTO LocOrdNo,
• DaysLate
• dbms_output.put_line (lateOrdrowcount.
  Order number LocOrdNo was DaysLate
  days late.)
• END LOOP
• Dbms_output.put_line (Altogether, there were
  LateOrdrowcount late orders)
• Close LateOrd
• This numbers the late orders.

12
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 s1rowtype

13
Updating in a shared database

• Selecting places no lock on a row.
• To lock a row, select it with a row share lock,
  and then update it.
• A row share lock allows others to
• Read the locked row, but only return the
  pre-locked state
• Read any other row
• Lock any other row.

14
Row-share lock

• A row-share lock allows for
• Read consistency.
• The session placing the lock may update the
  locked row.
• N.B. The session placing the lock MUST RELEASE
  the lock.
• This is done by COMMIT or ROLLBACK.

15
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 s1rowtype
• Begin
• for s1_rec in s1 loop
• update stock
• set unitcostprice unitcostprice 1.1
• where current of s1
• end loop
• commit
• End