1.

What are Large Object (LOB) data types in PL/SQL?

Answer»

The %ROWCOUNT cursor attribute returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

To use any of the above attribute, use the following syntax:

sql%attribute_name

Above, set any attribute name under “attribute_name”.

LET us consider the following table for our example:

<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME  | DEPTLOC | BUDGET     | +----------+------------------+---------------+------------------+ |  1 | Finance          | NORTH | 56678      | |  2 | MARKETING     | EAST |      87687 | |  3 | Operations   | WEST |      95979 | |  4 | Technical      | SOUTH |    76878 | |  5 | Accounting   | NORTHWEST| 86767      | +----------+------------------+------------------+--------------+

Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:

DECLARE     rows_count number(2); BEGIN   UPDATE deptbudget   SET budget = budget + 3000;   IF sql%notfound THEN      dbms_output.put_line('Nothing selected!');   ELSIF sql%found THEN      rows_count := sql%rowcount;      dbms_output.put_line( rows_count || ' rows affected! ');   END IF;   END; /  

The output:

5 rows affected!

Since we updated the budget for all the departments above, therefore our table rows would look like this:

<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME  | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ |  1 | Finance          | NORTH | 59678      | |  2 | Marketing     | EAST |      90687 | |  3 | Operations   | WEST |      98979 | |  4 | Technical      | SOUTH |    79878 | |  5 | Accounting   | NORTHWEST| 89767      | +----------+------------------+------------------+--------------+


Discussion

No Comment Found

Related InterviewSolutions