CODEWITHSUNDEEP
sqloracle-databaseplsql
John Watkins
John Watkins

Reputation: 1

PL/SQL Loop/bulk output

I'm learning PL/SQL and trying to write some code that would calculate the salary, yearly salary tax and monthly tax of all employees in a table Then essentially output the information for each employee to the screen

I did some research and learned about the bulk into function in plsql. I'm currently not in the position to test the code right now, but would this work? Am I missing anything?

What I'm really looking for is if there is an easier way to do all of this.

SET serveroutput ONDECLARE
TYPE t_employee_test
IS
  TABLE OF employee%ROWTYPE;
  b_firstname T_EMPLOYEE_TEST;
  b_lastname T_EMPLOYEE_TEST;
  b_salary T_EMPLOYEE_TEST;
  yr_salary HR,employees.salary%TYPE;
  name  VARCHAR2(100);
  taxes NUMBER(7,2);
  sal hr.employees.salary%TYPE;
BEGIN
  SELECT salary,
         firstname
                || ' '
                || lastname bulk collect
  INTO   b_salary,
         b_name
  FROM   employees;

  FOR indx IN 1 .. b_name.count
  LOOP
    yr_salary:= B_salary(indx) * 12 taxes := yr_salary(indx) * 0.25;
    csal := L_salary(indx)     * 0.25;
    dbms_output.put_line(‘employee: ‘
    || name
    || ’ monthly salari tax
  IS
    : ‘
    || taxes);
    dbms_output.put_line(‘25 % OF employee: ‘
    || name
    || ’salary
  IS
    : ‘
    || csal);
    dbms_output.put_line(‘employee: ‘
    || name
    || ’ has a yearly salary OF ‘
    || yr_sal);
  END LOOP;
END;

Upvotes: 0

Views: 71

Answers (2)

Alen Oblak
Alen Oblak

Reputation: 3325

You don't need collections for this task. It's easier to just use a cursor and print out the needed values, like this:

begin
   for r_emp in (select salary * 0.25 salary_tax
                 , first_name || ' ' || last_name name
                 , salary * 12 yearly_salary
                 from employees )
   loop
      dbms_output.put_line(‘employee: ‘ || r_emp.name || ’ monthly salary tax is : ‘ || r_emp.salary_tax);
      dbms_output.put_line(‘employee: ‘ || r_emp.name || ’ has a yearly salary of ‘ || r_emp.yearly_salary);
   end loop;
end;

Upvotes: 2

Tenzin
Tenzin

Reputation: 2505

The only way to know if you code realy works as you think/hope is to try it out. Even experienced programmers have code that they think works, and then a bug/error pops up when they try it. :-)

About you code. If you want to do things for every member in the table you could also make a cursor that fetches the information you want. Then you can use a record to grab the information one by one and do somthing with it.

Link: Cursor and Records

Upvotes: 0

Related Questions