CODEWITHSUNDEEP
plsql
Shraddha Gupta
Shraddha Gupta

Reputation: 1

PL/SQL : loops, fetching records

I am trying to display the first name, salary and job-id of first 5 records from a table "employees" using a simple loop. However, in the output only 1st record is getting displayed. Kindly suggest where I am going wrong in the code I have written and what I should do to rectify it. Given below is the code. Also, I have attached the screenshot of the result.

declare
v_i number(2) ;
v_fname employees.first_name%type;
v_sal employees.salary%type;
v_job employees.job_id%type;

begin 
      v_i:=1;
      loop
      select first_name, salary, job_id into v_fname, v_sal, v_job from 
      employees where rownum=v_i;
      dbms_output.put_line('Record ' || v_i || ': ');
      dbms_output.put_line('First Name: ' || v_fname);
      dbms_output.put_line( 'Salary: ' || v_sal);
      dbms_output.put_line( 'Job Title: ' || v_job);
      dbms_output.put_line( chr(10));
      v_i := v_i +1;
      exit when v_i >= 5;
      end loop;
end;

Upvotes: 0

Views: 777

Answers (1)

J. Chomel
J. Chomel

Reputation: 8395

You can't use ROWNUM the way you do. It only works for the first select where v_i=1; Try it: a select with ROWNUM=2 will return nothing!

Here is how to do it with implicit cursor:

declare
  v_i number(2) ;

begin 
      v_i:=1;
    for x in (
      select first_name, salary, job_id into v_fname, v_sal, v_job from 
      employees where rownum <=5 ) loop
          dbms_output.put_line('Record ' || v_i || ': ');
          dbms_output.put_line('First Name: ' || x.first_name);
          dbms_output.put_line( 'Salary: ' || x.salary);
          dbms_output.put_line( 'Job Title: ' || x.job_id);
          dbms_output.put_line( chr(10));
          v_i := v_i +1;
      end loop;
end;

Upvotes: 1

Related Questions