Pl Sql Tutorial

Pl Sql Explicit Cursors

- It uses to process individually each line (recording) returned by a SELECT statement that returns more records.
- The crowd of records returned by a SELECT statement is called lot result.
- Cursor keeps a pointer to the current line within a result crowds.
- Check the status of a cursor explicitly is achieved through the following attributes:
  - cursor_name%ISOPEN - evaluated to TRUE if the cursor is opened;
  - cursor_name%NOTFOUND - evaluated to TRUE if the most recent FETCH didn't return any line;
  - cursor_name%FOUND - complement of% NOTFOUND;
  - cursor_name%ROWCOUNT - has the value of the number of lines returned by the time stream.

Syntax:

DECLARE cursor_name IS SELECT;
OPEN cursor_name;
FETCH cursor_name INTO var1, var2;
CLOSE cursor_name;

To process the lines a pointer explicitly defines a loop to execute a FETCH in each iteration. In the end, all the lines in the active set is processed and a FETCH executed unsuccessfully competing attribute% NOTFOUND on TRUE. Before the first FETCH,% NOTFOUND evaluates to NULL, as in the case that FETCH does not run never successfully.

For greater flexibility, you can use a variable of type record to load the values in the cursor. This variable type record may have the same attributes as the cursor by specifying the% ROWTYPE property. In this case the loading of cursor will be made directly through var_cursor into var_record statement fech.