InterviewSolution
Saved Bookmarks
InterviewSolution
| 1. |
What are the Schema Objects created with PL/SQL? |
Answer»
The %FOUND cursor attribute returns TRUE if record was fetched successfully, FALSE otherwise. Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
The %NOTFOUND cursor attribute returns TRUE if record was not fetched successfully, FALSE otherwise. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE. Cursor attributes are used like this in PL/SQL: sql%attribute_nameAbove, set any attribute name under “attribute_name”. Here we are LEARNING about %FOUND and %NOTFOUND attribute names. 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 | +----------+------------------+------------------+--------------+ |
|