Cursor

Cursor is a work area where a SQL statement is executed

There are implicit and explicit cursors.

IMPLICIT CURSORS

They are used for queries that return up to one row. They are automatically connected to DML statements (UPDATE, DELETE, INSERT, SELECT INTO). If SELECT returns more than one row, error will be issued.

We can use the following attributes to evaluate what has happened when the implicit cursor was last used.
%ROWCOUNT – Number of rows processed
%FOUND – TRUE if any row was processed
%NOTFOUND – TRUE if no rows are processed

Example of using attribute

DECLARE
rows_deleted NUMBER;
BEGIN
DELETE * FROM tabela;
rows_deleted := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(‘Number of rows deleted: ‘ || rows_deleted);
END;

EXPLICIT CURSORS
They are used when we want to work on a set of rows one by one. In other words we use these explicit cursors when we are sure that the SQL statement will return more than one row.
Explicit cursor goes through following steps:
Declare: we need to declare explicit cursor
Open: we also need to open previously declared cursor
Fetch: only when the cursor is declared and opened, we can access the data
Close: we need to close the cursor to release the memory allocated when it was opened

With explicit cursors we have 3 attributes like with implicit cursors, plus one additional
%ROWCOUNT – Current row to be processed
%FOUND – TRUE if any row was processed
%NOTFOUND – TRUE if no rows are processed
%ISOPEN – TRUE if cursor is opened and not closed

There are also 2 important attributes to mention:
%TYPE – is used in place of a datatype during variable declaration. It is good to use it in case we change the type of the column of table, so that PL/SQL code continues working.
%ROWTYPE – is also used in declaration part. It consist of all the columns of a table. It is used when we are returning all columns with

DECLARE
variable table_name%rowtype
BEGIN
SELECT * INTO variable
FROM table_name;
DBMS_OUTPUT.PUT_LINE(variable.first_column);
DBMS_OUTPUT.PUT_LINE(variable.second_column);
DBMS_OUTPUT.PUT_LINE(variable.last_column);
END;

/

DECLARE
CURSOR cursor_name IS
SELECT column1, column2 FROM table_name;
value1 table_name.column1%TYPE;
value2 table_name.column2%TYPE;
BEGIN
IF NOT cursor_name%ISOPEN THEN OPEN cursor_name; END IF;
LOOP
FETCH cursor_name INTO value1, value2;
EXIT WHEN cursor_name%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Row number ‘ || cursor_name%ROWCOUNT || ‘: ‘ || value1 || ‘ ‘ || value2);
END LOOP;
DBMS_OUTPUT.PUT_LINE(cursor_name%ISOPEN); /* will return TRUE */
IF cursor_name%ISOPEN THEN CLOSE cursor_name; END IF;
DBMS_OUTPUT.PUT_LINE(cursor_name%ISOPEN); /* will return FALSE */
END;
/

DECLARE
CURSOR cursor_name IS
SELECT column1, column2 FROM table_name;
BEGIN
OPEN cursor_name;
FOR row IN cursor_name LOOP
DBMS_OUTPUT.PUT_LINE(row.column1 || ‘ ‘ || row.column2);
END LOOP;
CLOSE cursor_name;
END;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: