This is the 3rd set of the PL/SQL interview questions series.
Oracle PL/SQL is an advanced version of SQL. There are given a top list of PL/SQL interview questions for Experienced. You can also visit our PL/SQL Tutorial.
1. What is a correlated sub Query? write an example.
A correlated subquery is a SQL query that depends on values executed by an outer query in order to complete.
Because a correlated subquery requires the outer query to be executed first, the correlated subquery must run once for every row in the outer query. This causes correlated subqueries to be less efficient than other subqueries.
SELECT employee_number, name
FROM employees emp
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = emp.department);2. What is Decode? with an example.
it works as if-else if in SQL decode. decode(expression, result, value, default)
3. What are Oracle PL/SQL records? write a simple record procedure?
Records are composite data structures made up of different components called fields. These fields can have different data types. We can also say, A record is a group of related data items stored in fields. each with its own name and datatype.
Variable _name table_name%rowtype;
4. What is the trigger? with example?
Triggers in PL/SQL
5. Difference between rownum and rowid?
The basic difference between rownum and rowid is, that rowid is a permanent unique identifier for that row of a table. However, the rownum is temporary.
ROWID is a pseudo column in a table that store and return row address in HEXADECIMAL format with database tables.ROWID is the permanent unique identifier for each row in the database it consists of 18-character strings with the format. BBBBBBBBB.RRRR.FFFF Where B is Block, R is Row, F is FIle.
ROWNUM: ROWNUM is nothing but a logical sequence number given to the rows fetched from the table.
6. What is Oracle partitioning?
Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.
7. What is the ‘with’ clause?
The clause is used for defining a temporary relation such that the output of this temporary relation is available and is used by the query that is associated with the WITH clause.
The usage of WITH clause is very similar to creating tables. When you create a table, you give it a name. Similarly, when you use the WITH clause, you also give it a name, and this name essentially acts as a table name in the main SQL statement.
WITH temporaryTable (averageValue) as
(SELECT avg(Attr1)
FROM Table)
SELECT Attr1
FROM Table, temporaryTable
WHERE Table.Attr1 > temporaryTable.averageValue;8. What is an Implicit Cursor?
Implicit Cursors are also known as Default Cursors of SQL SERVER. These Cursors are allocated by SQL SERVER when the user performs any DML and selects a statement.
9. When does an Implicit Cursor open?
When a user executes any DML and selects a statement.
10. If we run a select statement, will the implicit cursor open?
If we run a SELECT INTO statement in PL/SQL block, then the implicit cursor attribute can be used to find out whether any row has been returned by that SELECT statement or not
11. Can we create two procedures with the same name in the same package?
We can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family. An example of an overloaded procedure in a package.
12. Is it possible to write a package with a package body? why do we use such kind of package?
Yes, these are exceptionally useful when the package only has variable or type declarations.