These PL/SQL questions have been designed for various interviews, competitive exams and entrance tests. We have covered questions on both basic and advanced concepts which will help you improve your skills to face interview questions on PL/SQL.
Who are these PL/SQL interview questions designed for?
All the experienced programmer, database administrator, database developers, Oracle developers, DBA, tester, web developers, application developers, programmers and software engineer will find these questions extremely useful. All freshers, BCA, BE, BTech, MCA and college students wanting to make a career in front end designing will be highly benefited by these questions.
PL/SQL interview questions topics
This section covers PL/SQL topics like - PL/SQL Operators, Transactions, data types, variables, constants, records, conditional statements, iterative statements, cursors, procedures, functions, exception handling, triggers, packages, collections, external procedures etc.
Video: PL/SQL Interview Questions for Freshers
1. What is PL/SQL?
Answer:
PL/SQL is a Procedural Language extension to SQL and is used in oracle. It combines SQL with procedural feature of programming language and allows to write code in procedural format. As the PL/SQL program are compiled by Oracle database server, it has robustness, security and portability of Oracle.
PL/SQL architecture comprises of 3 components, namely:
PL/SQL block: It has actual PL/SQL code.
PL/SQL Engine: Here processing of the code takes place.
Database Server: This stores data. PL/SQL uses SQL to interact with database server.
2. What are the advantages of using PL/SQL?
Answer:
PL/SQL engine provides fast code execution. It processes a block with multiple SQL statements simultaneously that reduces network traffic, thereby giving better performance.
With PL/SQL, we can use procedural language constructs such as conditional statements and loops along with SQL data manipulation.
PL/SQL offers error handling features to handle exceptions and give user-friendly message to resolve the issue.
PL/SQL application is highly portable to any OS where Oracle is operational.
It supports Object Oriented Programming concepts.
3. Explain the difference between PL/SQL and SQL.
Answer:
Unlike SQL, PL/SQL supports procedural language capabilities like conditional statements, looping etc.
PL/SQL sends entire block of statements to the database server for processing, thereby reducing network traffic. SQL statements are processed one at a time.
PL/SQL offers highly effective error handling mechanism. SQL doesn't support error handling.
4. What are the datatypes available in PL/SQL?
Answer:
A datatype of a variable, constant or parameter specifies a storage format.
PL/SQL offers following datatypes:
Scalar: such as CHAR, VARCHAR2, NUMBER, BOOLEAN, DATE, LONG.
Large Object (LOB): It is pointer to larger objects that are stored separately from other data item such as video clips, graphic image etc.
Composite: This type is made up of other internal components such as RECORD, TABLE, and VARRAY.
Reference: This type holds pointers to other data item. REF CURSOR is an example of reference type.
5. What are the purposes of %Type and %RowType data types?
Answer:
%Type attribute provides the datatype of a variable, constant or column. It is used to declare a variable that has the same datatype as a table column.
Example:
m_studentid student.studentid%TYPE;
Here, m_studentid has the same data type and size as the column studentid in table student.
%ROWTYPE is used to declare a variable of a record having a same structure as a row in a table.
Example:
stud_rec student%ROWTYPE;
This declares a record that can store an entire row for STUDENT table.
6. Explain packages in PL/SQL.
Answer:
PL/SQL package is a logical grouping of a related subprogram (procedure/function) into a single element. Package is compiled and stored as a database object that can be used later.
PL/SQL package have two components:
Package Specification: consists of a declaration of all the public variables, cursors, objects, procedures, functions, and exception
Package Body: consists of definition of all the elements that are present in the package specification.
7. Explain PL/SQL cursors.
Answer:
Cursors allows to select multiple rows of the data from the database and manipulate each row. It is a temporary work area created in the memory to hold rows return by a SQL Statement. It holds multiple rows but can process only one row at a time.
There are two types of cursors:
Implicit cursors: This gets created automatically when a SQL statement is executed. All DML statements like Insert, Update and Delete are associated with an implicit cursor. For Insert statement, the cursor holds the data to be inserted. It has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.
Explicit cursors: An explicit cursor names the unnamed work area that stores rows returned by the SQL statement. The programmer can access and process rows using explicit cursor.
8. What are triggers in PL/SQL?
Answer:
A Trigger is a special type of stored procedure that fires automatically when a DML statements like Insert, Delete, Update are executed on a database table.
Triggers are used for the following purposes:
It can be used to audit changes occurred in the table.
It can be used to enforce complex business rules.
It can also prevent invalid transactions.
Types of triggers based on which level it is triggered:
Row level trigger - An event is triggered for each row updated, inserted or deleted.
Statement level trigger - An event is triggered for each SQL statement executed.
