PLSQL Interview Questions And Answers For Practice – Part 4
This is the PLSQL Interview Questions And Answers For Practice – Part 4. 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.
- 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.
What are Cursors
Oracle will allocate an area of memory , which is known as Context Area. It will be used to process SQL statements. The pointer that points to the context area is called a Cursor.
STATIC CURSORS
WHAT IS STATIC CURSOR
These are cursors whose SQL statement is determined at design time. There are three types of Static Cursors EXPLICIT CURSOR and IMPLICIT CURSOR.
What is EXPLICIT CURSOR ?
Multiple row SELECT statement is called as an explicit cursor.
In order to execute a multi-row query, Oracle will open an unnamed work area that will store process information. To access the information, you can use an explicit cursor, which names the work area.
When to Use Explicit Cursors ?
If the SELECT statement returns more that one row then explicit cursor should be used.
How to Declare an Explicit Cursor?
STEP 1 – Declare a Cursor.
STEP 2 – Open a Cursor.
STEP 3 – Fetch data from the Cursor.
STEP 4 – Close the Cursor.
What are the Attributes for Explicit Cursors ?
Attribute 1 – %FOUND (This Returns true if the cursor has a value)
Attribute 2 – %NOTFOUND (This Returns true if the cursor does not contain any value)
Attribute 3 – %ROWCOUNT (This Returns the number of rows selected by the cursor)
Attribute 4 – %ISOPEN (This Returns the cursor is opened or not)
What is IMPLICIT CURSOR ?
An IMPLICIT Cursor is associated with any SQL DML statement that does not have a Explicit Cursor associated with it. These Statements Include:
Attribute 1 – SQL%FOUND (Returns true if the DML operation is valid)
Attribute 2 – SQL%NOTFOUND (Returns true if the DML operation is invalid)
Attribute 3 – SQL%ROWCOUNT (Returns the no. of rows affected by the DML operation)
What is IMPLICIT CURSOR ?
An IMPLICIT Cursor is associated with any SQL DML statement that does not have a Explicit Cursor associated with it. These Statements Include:
- All INSERT statements
- All UPDATE statements
- All DELETE statements
- All SELECT .. INTO statements
Attribute 1 – SQL%FOUND (Returns true if the DML operation is valid)
Attribute 2 – SQL%NOTFOUND (Returns true if the DML operation is invalid)
Attribute 3 – SQL%ROWCOUNT (Returns the no. of rows affected by the DML operation)
DYNAMIC CURSORS
WHAT IS DYNAMIC CURSOR
We can use Dynamic Cursor along with DBMS_SQL package .A SQL statement is dynamic, if it is constructed at run time and then executed.
REF CURSORS
WHAT IS STATIC CURSOR
When a cursor variable is Declared, it will create a pointer, not an item. In PL/SQL, pointers have datatype REF X,where REF is short form for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR.
PLSQL Interview Questions And Answers For Practice - Part 4
declare
cursor emp_cursor is select empno,ename from emp_dup;
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=10;
r emp_dup%rowtype;
begin
open dept_cursor;
loop
fetch dept_cursor into r;
dbms_output.put_line('THE EMPLOYEE DETAILS FOR DEPT20 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 emp_job is select * from emp_dup;
r emp_job%rowtype;
begin
open emp_job;
loop
fetch emp_job into r;
dbms_output.put_line('EMPLOYEE #:'||r.empno||'held the job of'
||r.job||'from'||r.hiredate);
exit when emp_job%rowcount>5;
end loop;
close emp_job;
end;
declare
cursor upd_curr is select e.empno,e.ename,e.job,e.sal,
d.deptno,d.loc,d.dname from emp e,dept d where e.deptno=d.deptno and
d.deptno=30 for update of sal NOWAIT;
begin
for emp_rec in upd_curr
loop
if emp_rec.sal<5000 then
update emp set sal=emp_rec.sal*1.10 where current of upd_curr;
end if;
end loop;
end;
declare
cursor emp_dept is select d.deptno,d.dname,e.ename,e.job,
e.hiredate,e.sal from emp e,dept_id d where e.deptno=d.deptno;
begin
for emp_record in emp_dept
loop
if emp_record.deptno <>30 then
dbms_output.put_line('departmentnumber:'||emp_record.deptno||
'department name'||emp_record.dname);
end if;
end loop;
for emp_record in emp_dept
loop
if emp_record.deptno<>30 then
dbms_output.put_line(emp_record.ename||emp_record.job||emp_record.hiredate
||emp_record.sal);
end if;
end loop;
for emp_record in emp_dept
loop
if emp_record.deptno=30 then
dbms_output.put_line('departmentnumber:'||emp_record.deptno||
'department name'||emp_record.dname);
end if;
end loop;
for emp_record in emp_dept
loop
if emp_record.deptno=30 then
dbms_output.put_line(emp_record.ename||emp_record.job||emp_record.hiredate
||emp_record.sal);
end if;
end loop;
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 DEPT20 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;
Also Read
: PLSQL Interview Questions And Answers
PLSQL Interview Questions And Answers For Practice - Part 4
