Sql Question and answer 6

31. What a SELECT FOR UPDATE cursor represent.

SELECT......FROM......FOR......UPDATE[OF column-reference][NOWAIT] The
processing done in a fetch loop modifies the rows that have been retrieved by the
cursor.

A convenient way of modifying the rows is done by a method with two parts: the FOR
UPDATE clause in the cursor declaration, WHERE CURRENT OF CLAUSE in an
UPDATE or declaration statement.

32. What 'WHERE CURRENT OF ' clause does in a cursor.

LOOP
SELECT num_credits INTO v_numcredits FROM classes
WHERE dept=123 and course=101;
UPDATE students
SET current_credits=current_credits+v_numcredits
WHERE CURRENT OF X;
END LOOP
COMMIT;
END;

33. What is use of a cursor variable? How it is defined.

A cursor variable is associated with different statements at run time, which can hold
different values at run time. Static cursors can only be associated with one run time
query. A cursor variable is reference type(like a pointer in C).

Declaring a cursor variable:
TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of
the reference type,return_type is a record type indicating the types of the select list
that will eventually be returned by the cursor variable.

34. What should be the return type for a cursor variable.Can we use a scalar data type as return type.
The return type for a cursor must be a record type.It can be declared explicitly as a
user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF
CURSOR RETURN students%ROWTYPE

35. How you open and close a cursor variable.Why it is required.

OPEN cursor variable FOR SELECT...Statement
CLOSE cursor variable In order to associate a cursor variable with a particular
SELECT statement OPEN syntax is used.In order to free the resources used
for the query CLOSE
statement is used.