Procedures

CST2010: PRACTICAL 5

IMPORTANT: Whenever you login, execute the following command first everytime before you start work:

SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000

Section A

Which of the following do you use to check the number of rows affected by an UPDATE statement

SQL%FOUND

SQL%NOTFOUND

SQL%COUNT

SQL%ROWCOUNT

The FOR UPDATE clause in a cursor declaration instructs the system to do which of the following?

execute the cursor.

issue an SQL UPDATE statement

keep track of the physical database row related to each cursor row

allow transaction control statements

Which of the following should be used to manage a query that returns multiple rows?

implicit cursor

explicit cursor

record variable

table variable

Which of the following is used to hold each row processed from a cursor?

scalar variable

record variable

table variable

none of the above

What cursor action retrieves the next row from a cursor?

FETCH

GET

OPEN

READ

In a CURSOR FOR loop, which command is used to open the cursor?

FETCH

OPEN

FOR loop

The cursor is opened automatically by a CURSOR FOR loop

Which of the following is not true for CURSOR FOR loop?

A record variable must be declared to hold a cursor row

Fetching rows is handled automatically by the loop

Opening the cursor is handled automatically by the loop

No exit condition is needed to end the looping action

Section B

TASK 1: EXPLICIT CURSOR WITH A BASIC LOOP

EXAMPLE OF AN EXPLICIT CURSOR

DECLARE

CURSOR PER_CURSOR IS

SELECT *

FROM PERSONNEL;

This explicit cursor is formally declared in the DECLARE section.

Write an anonymous block that creates an explicit cursor which will retrieve every row in the DEPT1 table. Test the cursor with the following code in the executable section:

BEGIN

NULL;

END;

Declare

cursor dept_cur is

select *

from dept1;

BEGIN

NULL;

END;

Now, change the select statement in your cursor select statement so that only the deptno is selected.

Declare

cursor dept_cur is

select deptno

from dept1;

BEGIN

NULL;...