Cursors

Question 1

What is a cursor

Answer 1

A temporary work area created in the system memory when a SQL statement is executed. Contains information on a select statement and the rows of data accessed by it

Holds more than one row but only processes one row at a time

Question 2

Active Set

Answer 2

The set of rows the cursor holds

Question 3

What are the two types of cursors

Answer 3

Implicit Cursor

Explicit Cursor

Question 4

Implicit Cursor

Answer 4

Created by default when DML statements like INSERT, UPDATE, DELETE statements are executed. Also when a select statement only returns one row is executed

Question 5

Implicit cursor attributes

Answer 5

%FOUND
%NOTFOUND
%ROWCOUNT
%ISOPEN

Question 6

EXPLICIT Cursor what is it

Answer 6

Must be created when executing a select statement that return more than one row.

Multiple rows retrieved only one process at a time is the current row.

Question 7

FETCH A ROW

Answer 7

Retrieves the first row or the current row position moves to the next row. Explicit Cursor attribute

Question 8

What are the 4 steps in using an Explicit Cursor

Answer 8

DECLARE - Cursor in declaration section
OPEN - Cursor in the execution section
FETCH - data from the cursor variables or records is in execution sec
CLOSED - in the execution section before you end then PL/SQL block

Question 9

What is a parameterized cursor

Answer 9

Passes parameters into a cursor and uses them in a query
Defines only data types and not type length
Default value assigned and are local

Question 10

Cursor with a FOR Loop

Answer 10

Not required to declare a record or variable to store the cursor values, need not open, fetch and close the cursor.

Declare
Cursor cur_emp is
Select * From emp where sal > 1000;

Begin
 For cv_emp in cur_emp
 Loop
   Statement;
 End loop;
End;

Question 11

What is a cursor variable

Answer 11

A pointier that distinguishes the current row in a resultset from a multiple row query

Several advantages over an explicit cursor
Point to a variety of queries suitable return type explicit tied to individual query
Opened independently of being processed
Passed as parameters between application layers and server side
Reduce client-server network traffic allows serval open in a round trip

Question 12

How do you define a Cursor Variable

Answer 12

REF CURSOR TYPE

Define in one or two ways
STRONGLY TYPED - restricted to an individual return type using return clause. Reduces chance if run time errors. Col mismatch at compile
and overflow flexibility

WEAKLY TYPE - Return clause is omitted allowing type to reference any return type. Greater flexibility increase likely runtime errors not picked up at compile