1 Reply Latest reply: Jun 7, 2014 7:14 AM by Solomon Yakobson RSS

    Loop going on unnecessarily.

    2687005

      My loop goes on unnecessarily for the whole table even though i need only 7 outputs .

      DECLARE

          TYPE dept_ty is table of departments%ROWTYPE

          INDEX BY binary_integer;

          my_dept_table dept_ty;

          v_count number(2);

          v_dept_id departments.department_id%TYPE;

      BEGIN

          SELECT count(*) INTO v_count FROM departments;

          FOR i IN 1..v_count

          LOOP

          BEGIN

              IF i=1 THEN

                  v_dept_id:=10;

              ELSIF i=2 THEN

                  v_dept_id:=20;

              ELSIF i=3 THEN

                  v_dept_id:=50;

              ELSIF i=4 THEN

                  v_dept_id:=60;

              ELSIF i=5 THEN

                  v_dept_id:=80;

              ELSIF i=6 THEN

                  v_dept_id:=90;

              ELSIF i=7 THEN

                  v_dept_id:=110;

              END IF;

          SELECT *  INTO my_dept_table(i)

          FROM departments WHERE department_id=v_dept_id;

          EXCEPTION

          WHEN no_data_found THEN

          DBMS_OUTPUT.PUT_LINE(' No record for department_id :- '||v_dept_id);

          END;

          END LOOP;

          FOR j IN my_dept_table.first..my_dept_table.last

          LOOP

              DBMS_OUTPUT.PUT_LINE('Department Number:- '||my_dept_table(j).department_id|| ' , '||'Department Name:- '||my_dept_table(j).department_name||' , '||'Manager Id:-'||my_dept_table(j).manager_id||' , '||' Location id:- '||my_dept_table(j).location_id);

          END LOOP;

       

      The outout which i receive is:-

      Department Number:- 10 , Department Name:- Administration , Manager Id:-200 ,  Location id:- 1700

      Department Number:- 20 , Department Name:- Marketing , Manager Id:-201 ,  Location id:- 1800

      Department Number:- 50 , Department Name:- Shipping , Manager Id:-122 ,  Location id:- 1500

      Department Number:- 60 , Department Name:- IT , Manager Id:-103 ,  Location id:- 1400

      Department Number:- 80 , Department Name:- Sales , Manager Id:-145 ,  Location id:- 2500

      Department Number:- 90 , Department Name:- Executive , Manager Id:-100 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

      Department Number:- 110 , Department Name:- Accounting , Manager Id:-205 ,  Location id:- 1700

       

       

      Please help.....

        • 1. Re: Loop going on unnecessarily.
          Solomon Yakobson

          Look at your code. You get department count and assign department numbers looping form 1 to department count. However, if department count is greater that 7 all my_dept_table elements starting 7th element will get value 110. So I can tell, based on your output, query:

           

          SELECT count(*) INTO v_count FROM departments;

           

          returned 20. As a result my_dept_table(7), my_dept_table(8),...my_dept_table(20) were all assigned value 110. That's why you got duplicate output. Change:

           

          FOR i IN 1..v_count

           

          to

           

          FOR i IN 1..v_count least(7,v_count)

           

          Also, I assume you are just practicing - code you wrote is quit inefficient.

           

          SY.