PL/SQL - BULK COLLECT INTO and CURSOR

BULK COLLECT INTO:

       Bulk collect can be used in the 'SELECT' statement to populate the records in bulk or in fetching the cursor in bulk. BULK COLLECT fetches the record in BULK, the INTO clause should always contain a collection type variable.

• The FORALL statement
• The BULK COLLECT clause

The FORALL statement:

• The FORALL statement sends INSERT, UPDATE, or DELETE statements in batches from PL/SQL.

       
           FOR i IN 1..10 LOOP
             INSERT INTO table_name
             VALUES (...);
           END LOOP;

• The bulk collect clause can appear in

              select into statement
              fetch into the statement
             -> returning into
                   delete statement
                   insert
                   update
                   execute immediate.

CURSOR:

• A cursor is a temporary work area created in the system memory when a SQL statement is executed.
• A cursor contains information on a select statement and the rows of data accessed by it.
• This temporary work area is used to store the data retrieved from the database and manipulate this data.
• A cursor is a SELECT statement that is defined within the declaration section of your PLSQL code.

       Three different syntaxes to declare a cursor.

            * Cursor without parameters.
            * Cursor with parameters.
            * Cursor with a return clause.

Cursor without parameters:

     Syntax:
 
            CURSOR cursor_name
            IS
            SELECT statement;

Cursor with parameters:

    Syntax:
         
            CURSOR(parameter_list)
            IS
            SELECT statement;

Cursor with return clause:

    Syntax:
     
           CURSOR cursor_name
           RETURN field%ROWTYPE
           IS
           SELECT statement;



Retrieve data from the cursor

   Syntax:

          FETCH cursor_name INTO PL/SQL variable;
          or
          FETCH cursor_name INTO PL/SQL record;



Difference between cursor and ref cursor:

• A ref cursor is a pointer to a result set.
• This is normally used to open a query on the database server, then leave it up to the client to fetch the result it needs.
• A ref cursor is also a cursor, though normally the term cursor is used when discussing static SQL.

CURSOR ATTRIBUTES:

         Name             Description
       %FOUND            Returns TRUE if the record was fetched successfully, FALSE otherwise.
       %NOTFOUND    Returns TRUE if the record was not fetched successfully, FALSE otherwise.
       %ROWCOUNT    Returns the number of records fetched from the cursor at that point in time.
       %IS OPEN            Returns TRUE if the cursor is open, FALSE otherwise.

REF CURSOR:

• REF CURSOR is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.
• A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database.
• Cursor variables give you easy access to centralized data retrieval.
• REF CURSOR is a PL/SQL data type. create and return a REF CURSOR inside a PL/SQL code block.
• REF CURSOR is also referred to as Cursor Variables.
• Ref Cursor itself is a data type and easy to declare.
• Cursor variables are bind variables.
• PL/SQL cursor is static and the ref cursor is dynamic.

Dis advantage:

• Ref Cursors are not efficient as Static cursors.
• Need additional code to print Ref Cursor values.

syntax:
       type<type_name>is ref cursor
       [return return_type];

strong and weak cursor:

• cursor variable has a return type then it is called a strong cursor.
• cursor variable has no return type then it is called a weak cursor.

Example:
         1) TYPE ref_item is ref cursor return item_master%rowtype.
         2) TYPE client is ref cursor;