PLSQL Interview Questions And Answers For Practice – Part 3
This is the Part-3 of PLSQL Interview Questions And Answers For Practice. These are most common PLSQL Interview Questions with Answers that can help you crack Interviews and leave a good Impression on the interviewer.
PLSQL means Procedural Language SQL. PL/SQL enhances the capabilities of SQL by adding control Structures found in different procedural language. PLSQL provides both, the flexibility of SQL along with Powerful feature of 3rd generation Language. PLSQL can be used in both Client Side Application development tools and in Oracle Server Database.
What are the Advantages of using PLSQL over Traditional SQL
PLSQL has support for SQL and supports Object-Oriented Programming. It’s integration with Oracle provides better performance, portability and higher productivity.
- PLSQL Supports the declaration and manipulation of object types and collections.
- PLSQL Allows the calling of external functions and procedures.
- PLSQL Contains new libraries of built in packages.d] with PL/SQL , an multiple sql statements can be processed in a single command line statement.
Developers and Database Administrators use PLSQL coding on a daily basis, whether for application development, finding problems, fine-tuning solutions to those problems, or other critical Database Administration tasks. Our aim here is to provide answers to PLSQL questions that Database Administrators come across daily.
PLSQL Interview Questions And Answers For Practice - Part 3
declare
r emp_dup%rowtype;
begin
select * into r from emp_dup where ename='KEVIN';
if r.ename='KEVIN' then
r.job:='SA_CSR';
r.deptno:=100;
update emp_dup set job=r.job where ename='KEVIN';
update emp_dup set deptno=r.deptno where ename='KEVIN';
end if;
end;
SQL> select * from emp_dup where ename=’KEVIN’;
declare
p emp_dup%rowtype;
begin
select * into p from emp_dup where ename='KEVIN';
if p.ename ='KEVIN' and p.sal>10000 then
p.deptno:=100;
update emp_dup set deptno=p.deptno where ename='KEVIN';
end if;
end;
SQL> select * from emp_dup where ename=’KEVIN’;
declare
p emp_dup%rowtype;
begin
select * into p from emp_dup where ename='KEVIN';
if p.deptno=100 or p.hiredate>'01-jan-2000' then
p.mgr:=100;
update emp_dup set mgr=p.mgr where ename='KEVIN';
end if;
end;
SQL> select * from emp_dup where ename=’KEVIN’;
declare
value_grade varchar2(5):=('&p_grade');
value_appraisal varchar2(15);
begin
if value_grade='A' then
value_appraisal :='EXCELLENT';
elsif value_grade='B' then
value_appraisal :='VERY GOOD';
elsif value_grade='C' then
value_appraisal :='GOOD';
elsif value_grade='D' then
value_appraisal :='BAD';
end if;
dbms_output.put_line('THE APPRISAL CATEGORY WAS'||value_appraisal );
end;
begin
for i in 1..15
loop
insert into messages values(i);
end loop;
insert into messages values(17);
for i in 19..20
loop
insert into messages values(i);
end loop;
commit;
end;
SQL> select * from messages;
declare
cursor emp_cursor is select empno,ename from emp_csr;
r emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into r;
dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||r.empno||r.ename);
exit when emp_cursor%rowcount>=10;
end loop;
close emp_cursor;
end;
declare
cursor dept_cursor is select * from emp_dup where deptno=100;
r emp_dup%rowtype;
begin
open dept_cursor;
loop
fetch dept_cursor into r;
dbms_output.put_line('THE EMPLOYEE DETAILS FOR DEPT 100 WAS'||r.empno||' '
||r.ename||' '||r.job||' '||r.sal||r.deptno);
exit when dept_cursor%rowcount>=5;
end loop;
close dept_cursor;
end;
declare
cursor emp_20 is select empno,ename from emp_dup;
r emp_20%rowtype;
begin
open emp_20;
loop
fetch emp_20 into r;
dbms_output.put_line('THE EMPLOYEE DETAILS WAS'||r.empno||' '||r.ename);
exit when emp_20 %rowcount>=20;
end loop;
dbms_output.put_line('THE NO OF RECORDS DISPLAYED ARE'||emp_20%rowcount);
close emp_20 ;
end;
declare
cursor temp_insert is select empno,ename from emp_dup;
emp_record temp_insert%rowtype;
begin
open temp_insert;
loop
fetch temp_insert into emp_record;
exit when temp_insert%notfound;
insert into temp_list(empid,tname) values(emp_record.empno,emp_record.ename);
end loop;
close temp_insert;
end;
declare v_deptid number; v_loc varchar2(15):='NRT'; v_dname varchar2(15):='CREDIT'; v_counter number:=1; begin select max(deptno) into v_deptid from dept; loop insert into dept(deptno,dname,loc) values((v_deptid+v_counter),v_dname,v_loc); v_counter:=v_counter+1; exit when v_counter>3; end loop; end;
declare v_counter number:=1; v_ename varchar2(15):='GVREDDY'; v_job varchar2(15):='SRINU'; v_empno number; begin select max(empno) into v_empno from emp_dup; while v_counter<=3 loop insert into emp_dup(empno,ename,job) values((v_empno+v_counter), v_ename,v_job); v_counter:=v_counter+1; end loop; end;SQL> SELECT * FROM EMP_DUP;
declare v_counter number:=1; v_ename varchar2(15):='GVREDDY'; v_job varchar2(15):='SRINU'; v_empno number; begin select max(empno) into v_empno from emp_dup; while v_counter<=3 loop insert into emp_dup(empno,ename,job) values((v_empno+v_counter), v_ename,v_job); v_counter:=v_counter+1; end loop; end;SQL> SELECT * FROM EMP_DUP;
Also Read
: PLSQL Interview Questions And Answers
PLSQL Interview Questions And Answers For Practice - Part 4
