Cursors

• A cursor is a way to step through a table in a stored procedure.
• It is much like the way we stepped through a table in the C api.
• A reference
• Three types
  • Asensitive: there may or may not be a copy of the table made
  • Read Only:
  • Nonscrollabel: Can be traversed only in one direction and cannot skip rows
• Cursors are declared before handlers.
• And after variable declarations.
• Declare
  • Declare Reference
  • DECLARE name CURSOR FOR select_stmt
  • Later this will be used with a fetch statement
  • The select statement must not have an into
  • Cursors must have unique names.
• OPEN
  • OPEN cursor_name
  • Does the query.
  • Before any FETCH
• FETCH
  • reference
  • Retrieve the next row.
  • variables must match the variables in the declare statement in number and type.
  • If no data is available, then SQLSTATE 02000 is set.
• CLOSE
  • CLOSE cursor_name
• Note how the handler is used in the procedure
• Look at cursor.sql