Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: Help with pl/sql code-UPDATE, EXCEPTION

    This is what I am trying to do:

    Prompt user for an employee number. Give 15% raise to an employee who has a manager and work in New York(this is done by joing two tables). The employee cannot get a raise if he/she is a clerk. If the employee does not have a manager, then he/she gets a 50% raise. If employee number from the user is invalid, stop the processing and give the user an error message.

    Here is my code so far. It is not updating the salaries and the exception is not handled.

    SET SERVEROUTPUT ON

    DECLARE

    v_empno emp1.empno%type := &p_empno;
    v_sal emp1.sal%type;
    v_new_sal_mgr emp1.sal%type := v_sal*1.15;
    v_new_sal_no_mgr emp1.sal%type := v_sal*1.5;
    v_same_sal emp1.sal%type := v_sal*1;
    v_loc dept1.loc%type;
    v_mgr emp1.mgr%type;
    v_job emp1.job%type;


    BEGIN

    IF v_mgr is not null and v_loc = 'NEW YORK' THEN
    UPDATE emp1 e
    SET sal = v_new_sal_mgr
    WHERE v_loc =
    (select d.loc from dept1 d where
    e.deptno = d.deptno);

    IF v_mgr is null THEN
    UPDATE emp1
    SET sal = v_new_sal_no_mgr;


    IF v_job = 'CLERK' THEN
    UPDATE emp1
    SET sal = v_same_sal;

    END IF;
    END IF;
    END IF;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('That is not a valid employee number.');
    WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);


    end;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Your code never executes as variables don't have a value. Declaring them using, for example, "v_mgr emp1.mgr%type;" says only that "v_mgr" is the same type as column "mgr" in "emp1" table - it does NOT implicitly fill its value into the "v_mgr" variable.

    Therefore, you'll need to do it yourself at the beginning:
    Code:
     select e.mgr into v_mgr
    from emp1 e
    where e.empno = v_empno;
    Select ALL the values you need in IF clauses. Your UPDATE statements will make a mess in "emp1" table as they are unrestricted. For example,
    Code:
    UPDATE emp1
    SET sal = v_same_sal;
    will update all records in "emp1" table as this update statements lacks in another WHERE clause (that is, "and empno = v_empno").

    And, if I'm not wrong, you don't need any of "v_sal" variables at all as salary can be updated easily without them.

  3. #3
    Join Date
    Oct 2004
    Posts
    2
    Hi,

    I think I did what was suggested, but the code is still not updating the table/s.

    Any help is appreciated.

    SET SERVEROUTPUT ON

    DECLARE

    v_empno emp1.empno%type := &p_empno;
    v_new_sal_mgr emp1.sal%type := 1.15;
    v_new_sal_no_mgr emp1.sal%type := 1.5;
    v_same_sal emp1.sal%type := 1;
    v_loc dept1.loc%type;
    v_mgr emp1.mgr%type;
    v_job emp1.job%type;


    BEGIN

    SELECT e.mgr
    INTO v_mgr
    from emp1 e
    where e.empno = v_empno;

    IF v_mgr is not null and v_loc = 'NEW YORK' THEN
    UPDATE emp1 e
    SET sal = sal*v_new_sal_mgr
    WHERE v_loc =
    (select d.loc from dept1 d where
    e.deptno = d.deptno)
    AND empno = v_empno;

    IF v_mgr is null THEN
    UPDATE emp1
    SET sal = sal*v_new_sal_no_mgr
    WHERE empno = v_empno;


    IF v_job = 'CLERK' THEN
    UPDATE emp1
    SET sal = sal*v_same_sal
    WHERE empno = v_empno;

    END IF;
    END IF;
    END IF;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('That is not a valid employee number.');
    WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);


    end;

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Similar problem to before. You have:
    Code:
    IF v_mgr is not null and v_loc = 'NEW YORK' THEN
    But v_loc has not been set to anything at this stage!

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    a COMMIT might prove useful, too.
    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.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Another issue:
    Code:
    IF v_mgr is not null and v_loc = 'NEW YORK' THEN
    UPDATE emp1 e
    SET sal = sal*v_new_sal_mgr
    WHERE v_loc =
    (select d.loc from dept1 d where
    e.deptno = d.deptno)
    AND empno = v_empno;
    
    IF v_mgr is null THEN
    UPDATE emp1
    SET sal = sal*v_new_sal_no_mgr
    WHERE empno = v_empno;
    How could v_mgr be null at the second IF it we already found it to be not null at the first IF and we have not changed it in between?

    Probably all these issues are based on the false belief that declaring the variables like "v_mgr emp1.mgr%type" means that they are magically populated by any subsequent select from the emp1 table. This is not so - as Littlefoot pointed out earlier.

    Another mistake:
    Code:
     WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    Those 2 lines should be deleted. While it may do no harm here, this is a bad habit to get into. Unexpected exceptions (OTHERS) should not be handled like this, they should be left to raise an Oracle error, which will roll back any prior changes made in the PL/SQL block.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    not to mention clerks would still be getting one of the bonuses with your
    existing code.

    I would suggest a CASE statement or using ELSIF instead of 3 IF statements.
    With 3 seperate IF statements you are not really excluding much.

    Notice, if I am a CLERK in NEW YORK with a MANAGER then I get a raise (first IF).
    The 3rd IF I also qualify for but my RAISED salary is multiplied by 1.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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