Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Red face Unanswered: PL/SQl -To find the total increase in annual salary

    there's a table employee with emp no and salary. I need to find salary raise , new salary and total increase in salary.

    DECLARE

    CURSOR cur_emp IS
    SELECT empno, sal
    FROM employee
    WHERE job <> 'PRESIDENT'//
    FOR UPDATE NOWAIT;
    lv_cursal_num NUMBER(6);
    lv_raise_num NUMBER(6);
    lv_newsal_num NUMBER(6);
    lv_salinc_num NUMBER(6);

    BEGIN

    FOR rec_emp IN cur_emp LOOP
    lv_cursal_num := rec_emp.sal * 12;
    lv_raise_num := rec_emp.sal * 12 * .06;
    lv_newsal_num := lv_cursal_num + lv_raise_num;
    IF lv_raise_num > 2000 THEN
    lv_raise_num := 2000;
    lv_newsal_num := lv_cursal_num + lv_raise_num;

    END IF;
    --UPDATE employee SET sal = lv_newsal_num/12 WHERE CURRENT OF cur_emp;
    Dbms_Output.put_line('Emp no: ' || rec_emp.empno ||' Current Annual Salary: ' || lv_cursal_num ||
    ' Salary Raise: ' ||lv_raise_num||' Proposed New Annual Salary: '||lv_newsal_num);
    END LOOP;

    END;

    Now I need total amount of annual salary increase

    I tried to use sum(lv_newsal_num - lv_cursal_num)

    please help
    Last edited by vj27; 10-01-10 at 23:31.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    tot_raise := tot_raise + lv_raise_num; -- place inside LOOP
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    Dear anacedent,

    Thank you! It was really straightforward.

    VJ

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Thank you! It was really straightforward.
    Why make the solution more complex or difficult than it needs to be?
    Not every problem needs to be solved using SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •