How cursor will work.

How cursor will work:

• The life cycle of the explicit cursor goes through four stages.

1. DECALRE
2. OPEN
3. FETCH
4. CLOSE

DECLARE:

• The CURSOR is declared in the declarative block and is provided with a name and a SELECT statement.

Syntax:-

• CURSOR <cursor name> is select _statement.
• Note: In <select statement> include most of the usual clauses, except INTO clause.(if use It there is no use).
• The cursor name can be any valid identifier.
• Any SELECT statements are legal, including joins and statements with the SET OPERATORS.
• Illustration

SQL>DECLARE
    vsal number:=2000;
    cursor c1 is
    select sal into vsal
    from emp
    where empno=7902;
    begin
    display(‘The vsal val is’||vsal);
    end;

OPEN:
Opening  a Cursor
Syntax:
OPEN cursor_name;

• The opening of cursor executes the query and retrieves the information from the database and dumps it into the context area to from active set.
• Cursor can be opend only in the EXECUTION OR EXCEPTION section of the PL/SQL block.
• The active set pointer is set to the first row.
• Once a cursor has been opened, it cannot be reopened unless it is first closed.
• Fetching from Cursor:
• Fetch the record from CONTEXT AREA into cursor variable.
• This fetches 1 row at  time into the cursor variable from the active set.
• The INTO clause for the query is part of the FETCH statement.

FETCH:
The FETCH statement has two forms:
Syntax:
1)FETCH cursor_name INTO list_of_variables.
2)FETCH cursor_name INTO PL/SQL_record;

• The list_of_variable is a comma-separated list of previously declared PL/SQL variable, and PL/SQL_record is a previously declared PL/SQL record.

Illustration:
FETCH Empcursor INTO v_emprecord;

CLOSE:
Closing a Cursor

• This tells PL/SQL engine that the program is finished with the cursor, and the resources associated with it can be freed.
• These resource include the storage used to hold the active set, as well as any temporary space used for determining the active set.
• The active set can be re-established several times.

Syntax:
    CLOSE <cursor name>;