Hi,
Can anyone suggest to me how to write a PLSQL block to print the list of employee names?
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;
/