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;
/