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.

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.

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:
  1. All INSERT statements
  2. All UPDATE statements
  3. All DELETE statements
  4. All SELECT .. INTO statements
What are the Attributes of Implicit Cursors ?
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 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.

 

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

http://techhowdy.com/wp-content/uploads/2018/05/PLSQL-Interview-Questions-And-Answers-For-Practice-Part-4.pnghttp://techhowdy.com/wp-content/uploads/2018/05/PLSQL-Interview-Questions-And-Answers-For-Practice-Part-4-150x150.pngDemonDatabase Programmingbasic pl sql questions answers,deloitte pl sql interview questions,download interview questions of pl sql,dumps questions for pl sql,high level pl sql questions,interview questions plsql blogspot,oca pl sql sample questions,oracle pl sql interview questions 10g,oracle pl sql interview questions 2015,oracle pl sql interview questions books,oracle pl sql interview questions by tcs,oracle pl sql mcq questions and answers,oracle pl sql quiz questions,oracle pl sql sample questions,oracle pl/sql interview questions 11g,oracle pl/sql interview questions you'll most likely be asked,oracle pl/sql interview questions youtube,pl sql 147 questions and answers,pl sql apti questions,pl sql aptitude questions,pl sql assignment questions,pl sql basic questions,pl sql certification exam questions,pl sql certification questions,pl sql certification questions answers,pl sql challenge questions,pl sql challenging questions,pl sql collections questions,pl sql complex questions,pl sql conceptual questions,pl sql databases questions,pl sql developer interview questions and answers for experienced pdf,pl sql developer interview questions and answers pdf,pl sql developer questions,pl sql difficult questions,pl sql exam questions,pl sql exam questions pdf,pl sql example questions,pl sql exception handling questions,pl sql function questions,pl sql general interview questions,pl sql interview questions,pl sql interview questions 2014,pl sql interview questions 2015,pl sql interview questions 3 years experience,pl sql interview questions and answers,pl sql interview questions and answers for 2+ experience,pl sql interview questions and answers for 2+ experience pdf,pl sql interview questions and answers for 4+ experience,pl sql interview questions and answers for experienced pdf,pl sql interview questions and answers pdf,pl sql interview questions blogs,pl sql interview questions capgemini,pl sql interview questions cognizant,pl sql interview questions doc,pl sql interview questions download free,pl sql interview questions experienced pdf,pl sql interview questions for 2 years experience,pl sql interview questions for 3 years,pl sql interview questions for 3 years experience,pl sql interview questions for 3 years experienced,pl sql interview questions for experienced pdf,pl sql interview questions geekinterview,pl sql interview questions hcl,pl sql interview questions honeywell,pl sql interview questions java developers,pl sql interview questions javarevisited,pl sql interview questions javatpoint,pl sql interview questions mcq,pl sql interview questions multiple choice,pl sql interview questions on collections,pl sql interview questions online test,pl sql interview questions queries,pl sql interview questions ref cursor,pl sql interview questions sql server,pl sql interview questions usa,pl sql interview questions with answers pdf,pl sql job interview questions,pl sql lab questions,pl sql lab questions and answers,pl sql latest interview questions,pl sql lead interview questions,pl sql logical interview questions,pl sql logical questions,pl sql loop questions,pl sql mcq questions,pl sql mcq questions with answers,pl sql mcq questions with answers pdf,pl sql objective questions,pl sql objective questions with answers,pl sql objective questions with answers pdf,pl sql ocp exam questions,pl sql online questions,pl sql packages questions,pl sql placement questions,pl sql practice questions,pl sql practice questions with answers,pl sql practise questions,pl sql procedure questions,pl sql programming questions,pl sql programming questions and answers pdf,pl sql programming questions with answers,pl sql qa interview questions,pl sql queries interview questions answers,pl sql query questions,pl sql questions,pl sql questions and answers,pl sql questions and answers for experienced,pl sql questions and answers for oracle interview,pl sql questions and answers multiple choice,pl sql questions and answers pdf,pl sql questions answers,pl sql questions for experienced,pl sql questions for interview,pl sql questions for practice,pl sql questions for testers,pl sql questions interview,pl sql questions multiple choice,pl sql questions on cursors,pl sql questions on triggers,pl sql questions pdf,pl sql questions with answers,pl sql questions with answers pdf,pl sql quiz questions and answers,pl sql quiz questions with answers,pl sql real time questions,pl sql review questions,pl sql sample questions,pl sql scenario based questions,pl sql security questions,pl sql technical questions with answers,pl sql test questions and answers,pl sql test questions and answers pdf,pl sql tough questions,pl sql tricky questions,pl sql triggers questions,pl sql tutorial questions,pl sql unix interview questions,pl sql views interview questions,pl sql viva questions answers,pl/sql advanced questions,pl/sql brainbench questions,pl/sql exam questions and answers,pl/sql fundamental questions,pl/sql hard questions,pl/sql interview questions 2011 ed pdf,pl/sql interview questions book,pl/sql interview questions career ride,pl/sql interview questions for 1 year experienced,pl/sql interview questions for 5 years experience,pl/sql interview questions for 7 years experience,pl/sql interview questions for experienced professionals,pl/sql interview questions guru99,pl/sql interview questions on joins,pl/sql interview questions scenarios,pl/sql interview questions with answers,pl/sql interview questions youtube,pl/sql job interview questions and answers,pl/sql job questions,pl/sql oracle questions,pl/sql questions and answers in oracle,pl/sql questions for freshers,pl/sql questions in oracle,pl/sql related interview questions,pl/sql scenario questions,pl/sql screening questions,pl/sql short questions,pl/sql technical questions,pl/sql test questions,pl/sql viva questions and answers pdf,plsql critical questions,plsql questions,plsql questions and answers,plsql questions for interview,rbs interview questions pl/sql,sample pl/sql exam questions,scribd pl sql questionsThis 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...Latest technology news