Gift Kamogelo Gift Kamogelo - 1 year ago 52
SQL Question

associative arrays and loops

I need to select employees with

employee_id
from 100 to 110, loop through
emp
table and add 10% to each
emp_salary
, then display results from the
emp
table

DECLARE
vname VARCHAR(100);
TYPE emp_table_type is TABLE OF employees.last_name%TYPE
INDEX BY PLS_INTEGER;

vname_table emp_table_type;

BEGIN
for i in 100..110
SELECT last_name into vname from employees where employee_id = i;
end loop;

vname_table(1) := vname;

DBMS_OUTPUT.PUT_LINE(vname_table(1));
End;

Answer Source

I need to select employees with employee_id from 100 to 110, loop through emp table and add 10% to each emp_salary, then display results from the emp table

You can do it as :

DECLARE
   sal           NUMBER;    
   TYPE emp_table_type IS TABLE OF employee.employee_id%TYPE
      INDEX BY PLS_INTEGER;    
   vname_table   emp_table_type;

BEGIN
  ---Selecting employee
   SELECT employee_id
     BULK COLLECT INTO vname_table
     FROM employee
    WHERE employee_id BETWEEN 100 AND 110;

   FOR i IN 1 .. vname_table.LAST
   LOOP
        --- Updating Salary 
         UPDATE Employee
            SET salary = salary + (salary * 0.10)
          WHERE employee_id = vname_table (i)
      RETURNING salary
           INTO sal;

     --- Displaying updated salary
      DBMS_OUTPUT.PUT_LINE (sal);
   END LOOP;
END;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download