Re: PL/SQL cursor question

From: Walter T Rejuney <BlueSax_at_Unforgetable.com>
Date: Tue, 09 Jan 2001 09:08:24 -0500
Message-ID: <3A5B1B58.857596B6@Unforgetable.com>

Klaas Talsma wrote:
>
> I need to achieve the following
>
> using a cursor i need to update a table wich stores employee's and
> their salary, now i need to know how,
>
> the salary's up to 2000 need to by updated by 8%
> ,, .. from 2000 to 2500 need to be updated by 6%
> ,, ,, ,, 2500 to 3500 ,, ,, ,, updated by 4%
> and from 3500 and above updated by 2%
>
> i have no clue what so ever to do this with a cursor
>
> i'm able to do this with normal SQL but know i would like to know how
> to achieve this with a cursor
>
> can someone help me?
>
> grtnx,
>
> Klaas Talsma

How about this:

rem - updatesalary.sql
set feedback off;
set pages 0;
set serveroutput on;
whenever sqlerror exit failure;
Declare

    Rate1 Number(3,2) := 1.08;
    Rate2 Number(3,2) := 1.06;
    Rate3 Number(3,2) := 1.04;
    Rate4 Number(3,2) := 1.02;

    Cursor C_Emp is
    SELECT INITCAP(ENAME) EmployeeName,

           SAL Salary,
           DECODE(SIGN(SAL-2000),
                  1,
                  DECODE(SIGN(SAL-2500),
                         1,
                         DECODE(SIGN(SAL-3500),
                                1,
                                SAL*Rate4,
                                SAL*Rate3),
                         SAL*Rate2),
                  SAL*Rate1) NewSalary

    FROM EMP
    FOR UPDATE OF SAL;
Begin

    dbms_output.enable(1000000);
    For I in C_Emp Loop

        dbms_output.put_line('Employee: '||I.EmployeeName||' '||
                             'Old Salary: '||To_Char(I.Salary)||' '||
                             'New Salary: '||To_Char(I.NewSalary));
        UPDATE EMP
        SET SAL = I.NewSalary
        WHERE CURRENT OF C_Emp;

    End Loop;
    Commit;
End;
.
/
EXIT SUCCESS; Received on Tue Jan 09 2001 - 08:08:24 CST