Forum

Forums
PL SQL
Interview Questions...
How to Write a PLSQ...
 
Share:
Notifications
Clear all

How to Write a PLSQL block to print the list of employee names?

 
    Last Post
RSS

Posts: 2
 Girish N
Guest
Topic starter
May 03, 2021 3:19 pm
(@Girish N)
New Member
Joined: 4 years ago

Hi,

Can anyone suggest to me how to write a PLSQL block to print the list of employee names?

 

1 Reply
Posts: 27
 KV Kumar
Admin
May 03, 2021 3:27 pm
(@vinodkrsetty)
Eminent Member
Joined: 6 years ago

1) Write a PLSQL block to print the list of employee names

I am going to show you the 3 different ways by which you can print the name from the employee table.

To print the employees in department number 10 we can write the below query.

Method1: Using for cursor

declare
lv_departno number :=10;

begin
for i in ( select * from emp where deptno=lv_deptno)
loop
dbms_output.put_line(i.ename);
end loop;
end;
/

OUTPUT

KING
CLARK
MILLER

Now let us concatinate and print some other information from employee table.

declare
lv_departno number :=10;

begin
for i in ( select * from emp where deptno=lv_deptno)
loop
dbms_output.put_line(i.ename||'-'||i.job);
end loop;
end;
/

OUTPUT

KING PRESIDENT
CLARK MANAGER
MILLER CLERK

Let us see one more method using cursor

Second Method: Using Very Simple Cursor

declare
lv_deptno number :=10;

cursor cl is select * from emp where deptno=iv_deptno;
iv_emp_row emp%rowtype;
begin
open c1;
loop
fetch c1 into lv_emp_row;
exit when c1%notfound;
dbms_output_put_line(iv_emp_row.ename);
end loop;
end;
/

Third Method: Using Bulk Collect and Collection Variable

set serveroutput on

declare
lv_deptno number :=10;
type lv_emp_row_type is table of emp%rowtype;
lv_emp_row lv_emp_row_type :=lv_emp_row_type();
begin
select *
bulk collect into lv_emp_rows
from emp
where deptno=lv_deptno;
for i in lv_emp_rows.first..lv_emp_rows.last
loop
dbms_output.put_line(lv_emp_row(i).ename);
end loop;
end;
/

Reply

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: