We continue with the cursors, this time we are going to see attributes with explicit cursors and use of cursors to update rows.
Attributes with Implicit Cursors
The attributes of the implicit cursors that are created are the following:
- SQL%NOTFOUND: tells us if the last insert, update, delete or select into have not affected any row.
- SQL%FOUND: tells us if the last insert, update, delete or select into has affected one or more rows
- SQL%ROWCOUNT – Returns the number of rows affected by the last insert, update, delete, or select into
- SQL%ISOPEN: It tells us if the cursor is closed, so in theory it will always give us false since Oracle automatically closes the cursor after each SQL command.
It is important to keep a number of things in mind:
If it is a select into we have to take into account that it can only return a single row otherwise it will automatically raise one of these two exceptions:
When a select into references a group function, the NO_DATA_FOUND exception will never be raised and SQL%FOUND will always be true. This is because group functions always return some value (NULL is considered a value).
Using cursors to update rows
To perform an update with a cursor we have to add the following FOR UPDATE at the end of the cursor declaration:
CURSOR cursor_name
This indicates that the rows selected by the cursor are to be updated or deleted. Once a FOR UPDATE cursor is declared, the CURRENT OF cursor_name specifier is included in the WHERE clause to update or delete the last row retrieved by the FETCH command.
{UPDATE|DELETE}… WHERE CURRENT OF cursor_name.
I give you an example to make it clear:
Raise the salary to all employees of the department indicated in the call. The percentage will also be indicated in the call.
CREATE OR REPLACE PROCEDURE raise_salary (num_dept NUMBER, incre NUMBER)
ES
CURSOR c IS SELECT job, salary FROM employees WHERE cod_dept=num_dept
FOR UPDATE;
reg c%ROWTYPE;
inc NUMBER(8);
BEGIN
OPENc;
FETCH c INTO reg;
WHILE c%FOUND LOOP
inc :=(reg.salary/100 )* inc;
UPDATE employees SET salary=salary+inc WHERE CURRENT OF c
FETCH c INTO reg;
END LOOP;
END;
We can also use ROWID instead of FOR UPDATE. ROWID will indicate the row that is going to be updated. To do this, when declaring the cursor in the SELECT clause, we will indicate that it also selects the row identifier:
CURSOR cursor_name IS SELECT column1,column2,…ROWID FROM table;
Running the FETCH will save the row number to a variable and then that number can be used in the WHERE clause of the update:
{UPDATE |DELETE } … WHERE ROWID = variable_rowid
The previous example using ROWID would be as follows:
CREATE OR REPLACE PROCEDURE raise_salary (num_dept NUMBER, incre NUMBER)
ES
CURSOR c IS SELECT job, salary,ROWID FROM employees WHERE cod_dept=num_dept
FOR UPDATE;
reg c%ROWTYPE;
inc NUMBER(8);
BEGIN
OPENc;
FETCH c INTO reg;
WHILE c%FOUND LOOP
inc :=(reg.salary/100 )* inc;
UPDATE employees SET salary=salary+inc WHERE ROWID = reg.ROWID;
FETCH c INTO reg;
END LOOP;
END;
With this article we end everything related to cursors and we begin to deal with exceptions in the next article.
Facebook Comments
Disqus