PL SQL Interview Questions and Answers
PL/SQL stands for Procedural Language/ Structured Query Language. It is basically an extension of SQL with operational features of the programming language. It runs within the Oracle executable and inherits all the security features, portability, and swiftness of Oracle Database. Below is a list of the most common PL SQL interview questions for you to have a read.
PL/SQL is used to integrate data-manipulation of SQL with the processing abilities of procedural languages. PL/SQL has many benefits like having better performance and productivity, access to predefined packages, support for Object-Oriented Programming and Developing Web Pages and Applications easily.
- PL/SQL is tightly integrated with SQL.
- It offers extensive error checking.
- It contains multiple data types.
- It offers and supports a wide variety of programming structures.
- Enabled support for structured programming through functions and procedures.
- Supports and based on object-oriented programming.
- It supports the development of web applications and server pages.
Most Frequently Asked PL SQL Interview Questions
| SQL | PL/SQL |
|---|---|
| Stands for Structured Query Language. | Stands for Procedural Language SQL, an extension for SQL. |
| Only one single query can be executed at a time. | Multiple codes can be executed at a single time. |
| It is used to store data to be displayed | It's the language through which an application is created to display data |
| PL SQL statements cannot be embedded in the code | SQL statements can be embedded in the code |
| Used to write queries and commands using DDL and DML methods | Used to write a block of code for having procedures, variables, functions and packages |
Delimiter in PL/SQL is a simple/compound symbol. In PL/SQL, delimiters are used to represent arithmetic operations like addition and subtraction.
In PL/SQL, Rowtype is used to provide a record type/data type in the form of rows in a database. Variables declared using the %ROWTYPE attribute are considered to be declared using a data type name.
In PL/SQL, there are two types of triggers:
- Row level triggers: Used to trigger an event once any changes are made in rows.
- Statement level trigger: It is used to trigger an event for each executed SQL statement.
In PL/SQL modules, packages are used to encapsulate logically related data types, constants, variables, subprograms, cursors, and any other exceptions. Each package can be used to simplify the level of understanding inside an interface using simple, clear and well-defined functions within.
| COMMIT | ROLLBACK |
|---|---|
| Syntax: COMMIT; | Syntax: ROLLBACK; |
| Used to validate modifications made by current transactions | Used to erase modifications made by current transactions |
| After executing this, a transaction can’t be used for ROLLBACK | After executing this, a transaction can be modified and sent for COMMIT |
| Occurs after successful completion of a transaction | Occurs if transaction is abrupted unexpectedly |
In, PL/SQL, a Savepoint is a TCL(Transition Control Language), statement is used to create a breakpoint during a specific/current transaction at a particular location. A Savepoint is basically a pit stop for transactions and ROLLBACK can be used to erase any part of a transaction.
Attributes in PL/SQL are properties which let the user reference the data type and structure of a present item without having to repeat its definition. Attributes are mainly used for easy maintenance of database tables.
| CASE | DECODE |
|---|---|
| Is a statement | Is a function |
| Can be used in the WHERE clause | Cannot be used in the WHERE clause |
Server processes assigned in a client program to be used as a memory for private data are known as Process Global Area. UGA or User Global Area is used to retain data during a session database such as Package Variables and Private SQL areas.