1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Cursors in Oracle PL/SQL

Discussion in 'SQL PL/SQL' started by tyro, Oct 11, 2008.

  1. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    The Beauty of Cursors in Oracle PL/SQL

    Oracle cursors allow the developer to decide how and when data items are retrieved. Cursors can also be provided to other developers in the form of what is known as “reference cursors”, which basically provide a gateway to a set of data which has been previously filtered and extracted into memory.

    The type and use of cursor depends on the data and the business logic. They are a powerful method of manipulating data items. Cursors can be declared, opened, fetched and closed. Basically this means declare a cursor name to use as a handle representing a query for obtaining items. Open the cursor so that it can be used. Fetch the data required. Finally, when done close the cursor.

    Cursors also have attributes indicating when items are present or not. These are indicated via the attributes %FOUND and %NOTFOUND.

    Cursors can be used for simple data extraction, or for updating specific records based upon set criteria. Another useful feature of cursors is that they can have cursors which also contain cursors within the overall logic. This technique is frequently used when information from a detail table relies on related information from the master, or driving table.

    Variables can be used with cursors to allow more flexibility with regards to filtering data as it is extracted.
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Some examples of cursor operation and how they are called are shown below:

    Example 1:

    Code (Text):
    CREATE OR REPLACE Function FindAccountId
       ( class_group IN varchar2 )
       RETURN number
    IS
        account_identifier number;

        CURSOR c1
        IS
           SELECT account_id
            from customer_accounts
            where account_group = class_group;

    BEGIN

    open c1;
    fetch c1 into account_identifier;

    if c1%notfound then
         account_identifier := 9999;
    end if;

    close c1;

    RETURN account_identifier;

    END;
    The above example is a function designed to return an account identifier from the customer accounts table, based upon a class of accounts, specified in the ‘class group’. The cursors used the passed in parameter ‘class_group’ to find an id associated with this group. If it is not found the identifier is set to 9999 and returned to the calling function.

    Example 2

    Cursors can also operate with parameters. The following example shows how this is achieved:

    Code (Text):
    CURSOR c2 (payroll_id IN varchar2)
    IS
        SELECT employee_name1, employee_name2, staff_id
          from payroll
          where pay_id = payroll_id;
    This cursor declaration provides the employee name and staff id where the payroll id is presented. This example demonstrates how cursors can have parameters passed to them in much the same way as functions and procedures.

    Example 3

    Cursors can also have return types associated with them. The following cursor shows how this is stated.

    Code (Text):
    CURSOR c3
    RETURN accounts_tbl%ROWTYPE
    IS
        SELECT *
          from accounts_tbl
          where account_type = ‘Transactions';
    The result set of this cursor is all columns from the accounts_tbl where the account_type = ‘Transactions’.