PL / SQL is an extension of SQL and can be referred to as Procedural / SQL language which allows users to create complex database applications with the help of procedures, modules, functions, control structures and more. In this Oracle PL / SQL Interview Questions blog, we aim to discuss some of the most popular questions that recruiters from major companies generally ask during job interviews.
This is a list of key questions and answers for PL / SQL interviews compiled by industry experts working in the PL / SQL domain. From these interview questions, you will be able to understand "What is the importance of PL / SQL?", Database triggers, build process, packages, code trace, functions, procedures, joins and visualizations in PL / SQL. Learn PL / SQL from Intellipaat’s PL / SQL certification training to excel in your career!
Compare SQL and PL/SQL
|What is it?||A single query or command execution||A full programming language|
|What does it comprise?||The data source for reports, web pages, etc.||An application language to build, format, and display reports, web pages, etc.|
|Characteristic||Declarative in nature||Procedural in nature|
|Used for||Manipulating data||Creating applications|
What is PL/SQL?Oracle PL / SQL is a procedural language that has both interactive SQL and procedural programming language constructs, such as iteration and conditional branching. Follow this PL/SQL tutorial to learn what PL/SQL is.
What is the basic structure of PL SQL?PL/SQL uses a block structure as the basic structure. Anonymous blocks or nested blocks can be used in PL / SQL.
Explain the use of a database triggerA PL/SQL program unit associated with a specific database table is known as a database trigger. Is used for:
- Audit data changes
- Record events transparently
- Enforce complex business rules
- Maintain replica tables
- Derive column values
- Implement complex security permissions
- Database trigger
Why do we use database links?Database links are used to communicate across multiple databases or environments, e.g. B. Test, development and production. Other information can be accessed through the read-only database links.
What types of data are available in PL SQL?There are several types of data types available in PL / SQL:
- Scalar data types: A scalar data type is a one-dimensional data type with no internal components. CHAR, DATE, LONG, VARCHAR2, NUMBER, BOOLEAN are some examples of scalar data types.
- Composite data types - A compound data type is made up of several data types that are easy to update and have internal components that can be shared and modified. For example, RECORD, TABLE, VARRAY, etc.
- Reference data types: A reference data type stores pointers that are values that refer to other programs or data items. REF, CURSOR is an example of a reference data type.
- Large object data type: A large object data type stores locators that define the position of large objects that are stored out of sequence (for example, video clips, graphics, etc.). BLOB, BFILE, CLOB, NCLOB, and others are examples of large object data types.
What are expressions?An expression consists of a series of literals and variables separated by operators. Operators are used in PL / SQL to manipulate, compare, and calculate data. An expression has two parts: operators and operands.
What is the difference between functions, procedures, and packages in PL/SQL?
Function: The main purpose of a PL / SQL function is to calculate and return a single value. A function has a return type in its specification and must return a value specified in that type.
Procedure: A procedure has no return type and should not return a value, but it can have a return statement that simply stops executing and returns to the caller. A procedure is used to return multiple values; otherwise it is generally similar to a function.
Package: A package is a schema object that groups logically related PL / SQL types, elements, and applets. You can also say it is a group of functions, procedures, variables, and a record TYPE declaration. Offers modularity and thus helps with application development. It is used to hide information from unauthorized users.
What is the procedural effort?Repeating the name of the same procedure with parameters of different data types and parameters in different places is known as procedure overload.
What is a stored procedure?A stored procedure is a sequence of statements or a named PL / SQL block that performs one or more specific functions. It is similar to how it is done in other programming languages. It is stored in the database and can be run repeatedly. It is saved as a schema object. It can be nested, called and parameterized.
What is a pl-SQL block?Like any procedural language, a block is the smallest group of valuable code. A block provides scope and execution limits for exception handling and variable declaration. PL / SQL allows you to create unnamed blocks and named blocks. These blocks can be triggers, packages, objects, procedures, or types of functions. A PL / SQL block consists of 4 sections. The 4 sections are the declaration, exception, header, and execution sections, but only one is required. The header section is an optional section, as is the exception and declaration. Only the execution section is mandatory or mandatory.
What are PL / SQL cursors?PL / SQL controls the context area through cursors. PL / SQL requires cursors to retrieve and process more than one row. A cursor is a pointer to the area of memory that contains SQL statements and information about the processing of the statements. PL / SQL is a mechanism by which multiple rows are selected from the database. Then each selected row is processed individually in PL / SQL programs.
What is a mutant table and a restrictive table?A mutant table is one that is modified with a DML declaration or a table with defined triggers. A constraint table is one that is read for a referential integrity constraint.
What is the difference between ROLLBACK and ROLLBACK TO statements?There is a significant difference between the ROLLBACK and ROLLBACK TO statements. When you use the ROLLBACK command, a transaction is completely rolled back and all blocks are released. As for the ROLLBACK TO command, the transaction is rolled back, but to a SAVEPOINT. Therefore, the transaction remains alive and active even after the command has been executed.
PL SQL Interview questions Summary 2021Are you preparing for PL / SQL interviews, if so then are we listing a list of PL / SQL interview questions here? PL (Procedural Language) is an extension of SQL (Structured Query Language) that allows a developer to write complex database interactions using procedures, control structures such as branches and iterations, modules and functions. It has tight integration with SQL and supports both dynamic and static SQL. Understanding the difference between SQL and PL / SQL is also important, among other questions during an interview. Some of the advantages of PL / SQL over SQL are:
- Supports procedural processing, conditional statements, loops, and other functions similar to high-level languages.
- Multiple statements can be sent to the database server at the same time in the form of a procedure, which saves time and network traffic.
- Customer-specific error handling is possible.
- Completely portable.