How to Use a Cursor for REC in a Loop in Oracle

Save

Oracle explicit cursors -- those you must physically declare -- let you remove the “single row” limitation of common SELECT statements. Instead of copying and storing column values in SELECT statement variables one row at a time, explicit cursors allow you to run a query, store a collection of values in memory and go back and process each record individually. This not only reduces processing time, but also reduces the amount of code you need to write. Automate the process and further reduce coding requirements by using a CURSOR FOR rec loop to loop through and retrieve multiple database records.

  • Create or declare the cursor as normal by giving it a unique name, binding it to a common SELECT statement and then append it to the declaration section of your function or process. The SELECT statement can be simple or can be more complex, such as a SELECT/WHERE/GROUPBY statement. If, for example, you want to use a CURSOR FOR rec loop to fetch the current monthly salary for each of your employees and then total these amounts, the declaration statement will display as:

    CREATE OR REPLACE Function SalaryExpense

    ( name_in IN varchar2 )
    RETURN varchar2

    IS

    total_val number(6);

    DECLARE cursor salary_cursor is

      select monthly_salary
    from employees
    where name = name_in;

    BEGIN

    (Reference 1-page 6, 7 and Reference 2)

  • Write a looping statement that automatically opens the cursor, accesses each employee record, extracts the monthly salary information you need to calculate your monthly salary expense and adds each value to a running total. When the loop reaches the last record, it automatically closes the cursor:

    total_monthy_salary := 0;

    FOR employee_rec in salary_cursor
    LOOP
    total_monthy_salary:= total_monthy_salary + employee_rec.monthly_salary;
    END LOOP;

  • Display the results and end the function or process:

    RETURN total_monthy_salary;
    END;

References

  • Photo Credit Comstock/Comstock/Getty Images
Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!