Top Answers to PL-SQL Interview Questions
|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|
Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration and conditional branching.
Go through this PL/SQL Tutorial to learn ‘What is PL/SQL?’
PL/SQL uses a block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for:
- Audit data modifications
- Log events transparently
- Enforce complex business rules
- Maintain replica tables
- Derive column values
- Implement Complex security authorizations
Any constant, variable, or parameter has a data type depending on which the storage constraints, format, and the range of values and operations are determined.
The compilation process includes syntax check, bind, and p-code generation processes. Syntax checking checks the PL/SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL/SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.
Go through the Handling PL/SQL Errors tutorial page to know how error handling is done in PL/SQL!
A PL/SQL package consists of:
- PL/SQL table and record TYPE statements
- Procedures and functions
- Variables ( tables, scalars, records, etc.) and constants
- Exception names and pragmas for relating an error number with an exception
Check out the insightful PL/SQL tutorial to learn more about Pl/SQL Packages!
PL/SQL packages provide several benefits as follows:
- Enforced information hiding: It offers the liberty to choose whether to keep data private or public.
- Top-down design: We can design the interface to the code hidden in the package before we actually implemented the modules.
- Object persistence: Objects declared in a package specification behave like global data for all PL/SQL objects in the application. We can modify the package in one module and then reference those changes to another module.
- Object-oriented design: The package gives developers stronghold over how the modules and data structures inside the package can be used.
- Guaranteeing transaction integrity: It provides a level of transaction integrity.
- Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.
Master PL/SQL from this top-rated PL/SQL Certification Training!
Tracing the code is a crucial technique to measure its performance during the runtime. Different methods for tracing the code includes:
- DBMS_SESSION and DBMS_MONITOR
- trcsess and tkprof utilities
- Function: The main purpose of a PL/SQL function is to compute 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 does not have a return type and should not return any value, but it can have a return statement that simply stops its execution 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 which groups logically related PL/SQL types, items, and subprograms. You can also say that it is a group of functions, procedures, variables, and record TYPE statement. It provides modularity, due to which it aids application development. It is used to hide information from unauthorized users.
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 a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as a schema object. It can be nested, invoked, and parameterized.
Wish to learn more? Visit the PL/SQL Collections and Records tutorial page!
A cursor is a temporary work area created in system memory when a SQL statement is executed. A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data, retrieved from the database, to manipulate it. A cursor can hold more than one row but can process only one row at a time. A cursor is required to process rows individually for queries.
- Create database objects—tables, synonyms, sequences, etc.
- To implement business rules, create procedures, functions, etc.
- To impose business rules, create constraints, triggers, etc.
- For data manipulation, create cursors
Select * from emp where sal= (select max(sal) from emp)
If you have any doubts or queries related to PL/SQL, get them clarified from our PL/SQL experts on our SQL Community!
Use the following code for displaying the highest salary from an employee table:
Select max(sal) from emp;
Select max(sal) from emp where sal not in ( select max(sal) from emp
Join is a keyword used to query data from multiple tables based on the relationship between the fields of tables. Keys play a major role in Joins.
- A View is a virtual table consisting of data contained in a table.
- Views do not need any memory space.
- Views can be created on multiple tables.
A subquery is a query within another query. The outer query is known as the main query and the inner query is called the subquery. A subquery is executed first, and the result of the subquery is passed to the main query.
There are two types of subqueries: correlated and non-correlated
A trigger is a database object that automatically executes in response to some events on the tables or views. It is used to apply the integrity constraint to the database objects.