(Continued from previous part...)

How do you pass cursor variables in PL/SQL?

Pass a cursor variable as an argument to a procedure or function. You can, in essence, share the results of a cursor by passing the reference to that result set.

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

Using OPEN cursor_name and CLOSE cursor_name commands. The cursor must be opened before using it in order to fetch the result set of the query it is associated with. The cursor needs to be closed so as to release resources earlier than end of transaction, or to free up the cursor variable to be opened again.

What should be the return type for a cursor variable. Can we use a scalar data type as return type?

The return type of a cursor variable can be %ROWTYPE or record_name%TYPE or a record type or a ref cursor type. A scalar data type like number or varchar can’t be used but a record type may evaluate to a scalar value.

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

Cursor variable is used to mark a work area where Oracle stores a multi-row query output for processing. It is like a pointer in C or Pascal. Because it is a TYPE, it is defined as TYPE REF CURSOR RETURN ;

What WHERE CURRENT OF clause does in a cursor?

The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.

Difference between NO DATA FOUND and %NOTFOUND

NO DATA FOUND is an exception which is raised when either an implicit query returns no data, or you attempt to reference a row in the PL/SQL table which is not yet defined. SQL%NOTFOUND, is a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row.

What is a cursor for loop?

A cursor FOR loop is a loop that is associated with (actually defined by) an explicit cursor or a SELECT statement incorporated directly within the loop boundary. Use the cursor FOR loop whenever (and only if) you need to fetch and process each and every record from a cursor, which is a high percentage of the time with cursors.

(Continued on next part...)