Problem: CURSOR with LOOP & EXCEPTION handling
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Problem: CURSOR with LOOP & EXCEPTION handling

  1. #1
    Join Date
    Sep 2001
    Location
    NEW YORK
    Posts
    17

    Question

    I'm having trouble getting this PL/SQL program, (well actually 2 programs, 1 with, and 1 without cursor), to a). display all the records specified(It always displays just the first row in the table only). And b). display the proper output line when the exception is raised,(ie. when the cur_sal is already equal to the high_sal), and c). Also, when the cur_sal is equal to the high_sal the output line displays that that particular empno will reach maximum salary in "1 years" when it should really display "0 years".

    I would appreciate any feedback from those of you that understand how to alleviate such problems.

    Look forward to hearing from you all!

    Here are the 2 programs:

    This one is for returning multiple rows:


    DECLARE
    CURSOR years is
    SELECT empno, firstnme, lastname, salary,
    salary_limit.high_sal
    FROM templ, salary_limit
    WHERE templ.jobcode = salary_limit.job_code
    AND empno = 00260 or empno = 00010 or empno = 00290;
    years_til_high NUMBER(8,2) :=0;
    cur_sal NUMBER(8,2);
    hi_sal NUMBER(8,2);
    e_no CHAR(6);
    f_name varchar2(12);
    l_name varchar2(15);
    j_code NUMBER(3);
    high_sal number;
    BEGIN
    open years;
    LOOP
    FETCH years INTO e_no, f_name, l_name, cur_sal, hi_sal;
    years_til_high := years_til_high + 1;
    cur_sal := cur_sal * 1.05;
    EXIT WHEN cur_sal >= hi_sal;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Employee # '||e_no||' will reach maximum salary in '||years_til_high||' years.');
    CLOSE years;
    END;


    This one is for single row result:


    declare
    salary number(8,2);
    high_sal number(8,2);
    cur_sal number(8,2) := salary;
    hi_sal number(8,2) := high_sal;
    salmax exception;
    yrs_high number := 0;
    empno char(6) := '&e_no';
    e_no varchar2(6) := empno;
    begin
    select salary, high_sal
    into cur_sal, hi_sal
    from templ, salary_limit
    where templ.jobcode = salary_limit.job_code
    and empno = '&e_no';
    loop
    yrs_high := yrs_high + 1;
    cur_sal := cur_sal * 1.05;
    exit when cur_sal >= hi_sal;
    If cur_sal >= hi_sal
    then raise salmax;
    --else null;
    end if;
    end loop;
    DBMS_OUTPUT.PUT_LINE('Employee # '||e_no||' will reach maximum salary in '||yrs_high||' years.');
    exception
    WHEN salmax then
    DBMS_OUTPUT.PUT_LINE('Employee # '||e_no||' has
    already reached maximum salary!');
    when others then
    DBMS_OUTPUT.PUT_LINE('Looks like the s... has hit
    the fan now!');
    end;

    THESE ARE THE TABLES UTILIZED:




    TABLENAME: templ

    EMPNO JOBCODE SALARY
    ------ --------- ---------
    000090 55 29750
    000280 54 26250
    000100 54 26150
    000320 52 29925
    000330 55 25370
    000340 54 23840




    TABLENAME: salary_limit

    JOB_CODE LOW_SAL HIGH_SAL
    --------- --------- ---------
    66 52750 52750
    55 23800 44250
    54 22250 28500
    56 28420 37500
    58 29250 47500
    52 17250 31250

    Hope this covers it!


  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    You have to get dbms_output.put_line within the loop!
    This way, it just displays the last value of your variables.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Oct 2001
    Posts
    2
    Compare the code below with your first program:

    DECLARE
    CURSOR c_sal IS
    SELECT empno, salary, salary_limit.high_sal
    FROM templ, salary_limit
    WHERE templ.jobcode = salary_limit.job_code;

    l_counter NUMBER;
    l_sal NUMBER;

    BEGIN
    FOR r_sal IN c_sal
    LOOP
    l_sal := r_sal.salary;
    l_counter := 0;

    WHILE l_sal <= r_sal.high_sal
    LOOP
    l_sal := l_sal * 1.05;
    l_counter := l_counter + 1;
    END LOOP;

    IF l_counter = 0
    THEN
    dbms_output.put_line('Employee #'||r_sal.empno || ' already reached maximum salary!');
    ELSE
    dbms_output.put_line('Employee #'||r_sal.empno || ' will reach maximum salary in ' || l_counter || ' year(s)');
    END IF;

    END LOOP;

    END;

    note:
    - put dbms_out.put_line inside the cursor-for-loop
    - don't try to get a first- and lastname when there are no columns like that!

    Regards,
    ||-QUAPPO-||
    mail@quappo.cjb.net

Posting Permissions

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



Click Here to Expand Forum to Full Width