Top 25 Most Important PL/SQL Interview Questions and Answers

1. What is PL/SQL and how does it differ from SQL?
PL/SQL is the abbreviation of, Procedural Language/SQL and is a development by the Database that includes programming features in SQL. SQL is used basically to perform simple operations like SELECT,INSERT,UPDATE or DELETE data . By using PL/SQL, you can perform much more complex operations, for example, you can work with loops, conditions and error handling. Additionally, you can pack several SQL commands in one code block so that it becomes much more powerful and efficient for work with a database.
SQL is a language that is used to communicate with databases, adding, deleting or updating data, but it does not take care of complex logic or operations that involve decision making or repetition. Using PL/SQL, you can write functions, procedures and triggers that directly do complex tasks inside the database, thus minimizing the use of multiple SQL queries.
In other words, SQL is used for basic database queries. PL/SQL adds the functionality of automation and managing more complex database operations.

2.What are the three primary parts of a PL/SQL block? Briefly describe why each is crucial?
There are three sections to the syntax of a PL/SQL block. All have very important jobs. These three sections are the Declaration Section, Executable Section and the Exception Handling Section.
1.Declaration Section: This section declares the variables, constants and other things that you require for your program. It is as if setting up everything before you start working.
For example, you can declare a variable to hold the name of an employee:
DECLARE
v_employee_name VARCHAR2(50);
2. Executable: This is the actual workspace. Here, you describe the logic that is performed by the program.
For example, you query to fetch data from a table, process it or simply make decisions based upon different conditions.
For example :
BEGIN
SELECT name INTO v_employee_name
FROM employees
WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE(v_employee_name);
This section executes SQL statements and PL/SQL commands to retrieve or update data.
3.Exception Handling Section: The exception handling section is done using this section when some error occurs during the execution of the program.
For example, the return of no data found or an unexpected error the program will handle that and does not let it to crash:
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
In short, the three sections of a PL/SQL block help organize your code, handle errors and ensure that everything is running without a smoothly.

3.What is the difference between %TYPE and %ROWTYPE in PL/SQL?
%TYPE and %ROWTYPE are used for variable definition in PL/SQL but both serve different purposes.
%TYPE:
It is the declaration of declaring a variable having the same data type of any column in a table, which means the declared variable constantly complies with the type of its column. Thus, assuming that you would have to assign salary value from your employees table into a declared variable, then you would probably make use of % TYPE so that a given type of employee's table is matched.
Example: emp_salary employees.salary%TYPE
means that the variable emp_salary will have the same data type of salary column in employees table.
%ROWTYPE:
It is declared to express a variable, which in turn represents a whole row from a table or view. The variable, in return, will consist of all columns and of the structure of the whole row. For instance, if you want to process all the data from just one record of an employee, you might declare a variable using the %ROWTYPE that is going to store the data of the whole row from the employees table.
Example: emp_record employees%ROWTYPE
means the variable emp_record will hold a whole row of an employee from the employees table.
In brief, %TYPE is good for matching data type of individual fields, whereas %ROWTYPE is ideal for whole rows of data from some table. Both improve maintenance of code by automatically accepting changes in schema.

4.What are triggers in PL/SQL? Explain the types of triggers with examples.
Triggers in PL/SQL: A trigger in PL/SQL is a stored procedure which automatically gets executed on the occurrence of certain events in the database. Triggers are used to enforce data integrity and manage business rules and for auditing purposes. There are four main types of triggers in PL/SQL.
1.BEFORE Triggers: Fired before a data modification such as INSERT, UPDATE or DELETE. They can be very useful in validating data or making changes to the data prior to storing it.
Example:
CREATE OR REPLACE TRIGGER check_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :new.salary < 5000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary must be at least 5000.');
This trigger prevents the insertion of any employee with a salary below 5000.
2.AFTER Triggers
These occur after a modification of data event. They are often used for such actions as logging or auditing after the change has occurred.
Example:
CREATE OR REPLACE TRIGGER log_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_log (employee_id, change_date)
VALUES (:new.employee_id, SYSDATE);
This will log the employee updates to a separate log table.
3.INSTEAD OF Triggers:
Although primarily used with views, these triggers override the default actions of INSERT, UPDATE or DELETE on a view. They allow one to define specific operations applied to the underlying tables
Example:
CREATE OR REPLACE TRIGGER instead_of_trigger
INSTEAD OF UPDATE ON view_employee
FOR EACH ROW
BEGIN
UPDATE employees
SET salary = :new.salary
WHERE employee_id = :new.employee_id;
This replace trigger handles updates of the view by propagating such updates to the underlying table employees.
4.Compound Triggers:
They enable combining several activities such as BEFORE and AFTER in one body of the trigger.
This prevents any conflict and decreases redundancy when one needs to take pre as well as post-actions together.
Example:
CREATE OR REPLACE TRIGGER compound_trigger_example
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
-- actions before the statement
Compound triggers Combine multiple actions (before and after) within a single trigger to handle complex operations. Triggers automate tasks in PL/SQL in a consistent manner, enforcing business rules directly at the database level.

5.What is a cursor in PL/SQL?
A cursor in PL/SQL is a pointer that holds the result set of a SQL query and lets you process the data row by row. It works as a mechanism to handle multiple rows returned by a query and lets you fetch, manipulate, or loop through data in a controlled manner. Cursors are used in queries that return more than one row, and they are useful in fetching and handling such rows in a sequential order in a PL/SQL block.
There are two kinds of cursors in PL/SQL:
Implicit Cursor:
Oracle automatically creates an implicit cursor when a SQL statement is executed, which will result in a set of records (for example, SELECT, UPDATE, INSERT or DELETE).
No declaration by the programmer is required.
Used for simple SQL operations when you don't need to process rows individually.
Explicit Cursor:
It is declared explicitly by the programmer, it is used for complex queries where you require row-by-row processing. You have more control over the result set since you need to manage the lifecycle of the cursor: open, fetch, close. Used for larger datasets or in case you want to loop through the rows.
Cursors are useful in PL/SQL when you need to process multiple rows returned by a query, giving you that flexibility and control in working with result sets.

Top 25 PL/SQL Interview Questions and Answers for Freshers and Experienced Professionals

6.Explain the difference between implicit and explicit cursors.
In PL/SQL, cursors are used to handle SQL queries that return multiple rows. They allow you to retrieve and process data row by row. Cursors can be classified into two types: implicit cursors and explicit cursors. Both serve the same general purpose of fetching query results, but they differ in how they are managed, controlled and used.
Implicit Cursors
Upon any occurrence of SQL statements like INSERT, UPDATE, DELETE or SELECT that return a single result set, Oracle automatically does the creation of an implicit cursor. Implicit cursors carry a very important feature : they don't need anything from the programmer's perspective to be declared or manipulated explicitly. Oracle can take care of everything instantly open the cursor by itself, fetch the whole of the result set then close the cursor once again right away when the operation is successfully completed. This makes implicit cursors suitable for simple SQL operations where you do not need to manipulate or process individual rows.
For example, the following query is handled automatically by Oracle:
SELECT * FROM employees WHERE department_id = 10;
However, although they make the code much easier to maintain. You can use some attributes like SQL%FOUND, SQL%ROWCOUNT and SQL%NOTFOUND for checking the query status, but you are not allowed to control cursor's lifecycle and row by row fetching of data.
Explicit Cursors
On the other hand, an explicit cursor has to be declared and managed manually by the programmer. It is used for more complex SQL operations where you need to process the result set row by row. With an explicit cursor the programmer has full control over the cursor's lifecycle. You must explicitly open the cursor fetch rows from the result set and close the cursor when done.
For example, the following illustrates how an explicit cursor is declared and used to fetch data:
DECLARE
CURSOR emp_cursor IS SELECT * FROM employees WHERE department_id = 10;
emp_record employees%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO emp_record;
CLOSE emp_cursor;
This level of control makes explicit cursors ideal for handling complex queries, large datasets or operations where you need to apply specific logic to each row, such as performing calculations or transformations. Unlike implicit cursors, explicit cursors allow you to iterate over data in a more granular way.
Key Differences Between Implicit and Explicit Cursors
The main difference between implicit and explicit cursors lies in the control and flexibility they offer. Implicit cursors are automatically managed by oracle for simpler one time SQL operations. They have to be used when data does not need to be manipulated row by row and should be used without explicit declarations of cursors. However the control over the result set can be limited. Explicit declaration and control of cursors is required in explicit cursors to provide more flexibility. They fetch rows individually so that they allow the programmer to get rows in customized ways that better make sense for complex queries where precision control over data processing is required.

7.What is a stored procedure in PL/SQL? How is it different from a function?
PL/SQL Stored Procedure
A stored procedure is a named block of code in PL/SQL which is stored in the database and performs specific tasks such as modification of data or any set of SQL statements and receives input parameters. Stored procedures don't return values directly but can output values through parameters.
Example
CREATE OR REPLACE PROCEDURE update_salary (emp_id IN NUMBER, new_salary IN NUMBER)
AS
BEGIN
UPDATE employees
SET salary = new_salary
WHERE employee_id = emp_id;
COMMIT;
END;
Function in PL/SQL
A function in PL/SQL is similar to a stored procedure but is specifically designed to return a value. Functions always return a value of a specified data type and are commonly used to perform calculations or return data based on input values. Functions can be used in SQL queries and expressions.
Example:
CREATE OR REPLACE FUNCTION get_salary(emp_id IN NUMBER) RETURN NUMBER AS emp_salary NUMBER;
BEGIN SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id; RETURN emp_salary;
Differences Between Stored Procedures and Functions
Return Value
A stored procedure does not directly return a value. Rather, it may return values by means of output parameters after completion of the job. It mainly serves to carry out some operations that could potentially cause data modification or SQL execution but do not necessarily return a result back to the caller.
In contrast, a function always returns a value of defined data type. Functions are defined with the purpose of returning results. These results are actually very often used in SQL commands or expressions to compute some value or retrieve a needed value.
Usage
A stored procedure is typically used for executing tasks that involve multiple steps, such as modifying the database, handling complex logic, or performing business operations. It may execute a series of SQL statements like INSERT, UPDATE, or DELETE, or even call other procedures.
A function is used majorly for a calculation or any operation which should return a value. SQL queries are where it commonly appears, particularly in retrieval or computation, making it best suited to operations like computing a value, type casting, or retrieving a particular record or records given the input parameters.
Invokation
Invoking a Stored Procedure
To call a stored procedure, you employ the CALL or EXECUTE statement in PL/SQL. Usually, these procedures are invoked explicitly to execute some action or set of actions.
A function can be used within SQL statements and expressions. Because a function returns a value, they can be used directly within a SELECT statement or part of other SQL operations and therefore more flexible to apply within SQL-based applications.
Side Effects
A Stored procedure is used to perform tasks such as handling complex logic, performing business operations or modifying data like any operations, where data can be changed via INSERT, UPDATE, or DELETE. This makes a stored procedure ideal for changes in the database.
Ideally, a function should be side-effect free. It should not alter the state of the database and be built to return a computed value that does not execute actions that alter data. Functions are primarily calculated for transformations and data retrieval activities.
Context
Stored procedures are widely used in scenarios where there is a need to execute complex tasks or business logic. It can also be used for administration tasks or batch processing involving multiple SQL statements and logics.
Functions, on the other hand are mostly used when you want to return something to the caller. They often occur in situations where a specific value or calculation is required in a SQL query or program and they are easily embedded within SQL operations.
Transaction Control
The transaction control statements, such as COMMIT or ROLLBACK are included with a stored procedure to roll back the modifications created during its execution. In this regard, the stored procedure gets the ability to control data modification flow as well as error handling.
A function, however cannot include the transaction control statements. The function is intended to be lightweight, for only computation or data retrieval with no handling of transactions.

8.What are IN, OUT and INOUT parameters in PL/SQL? Explain with examples.
IN Parameter
The IN parameter in PL/SQL is used to pass input values from the caller to a procedure or function. It acts as a read-only variable inside the procedure, meaning its value can be accessed and used but cannot be changed. It makes it ideal for providing fixed values or filters for calculations, queries or other operations within the procedure.
For example, in a procedure that greets a user by name an IN parameter can be used to pass the name as input and the procedure simply uses it to display a greeting without modifying it. Here's an example:
CREATE OR REPLACE PROCEDURE greet_user (p_name IN VARCHAR2) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
In this process, the p_name parameter is used to accept the name provided by the caller and the procedure prints a greeting message based on it.
OUT Parameter
The OUT parameter is used to return values from a procedure to the caller. Unlike an IN parameter that is read-only an OUT parameter is write-only within the procedure. It has no persisting value passed by the caller. however, its value can be re-established explicitly in the procedure. Therefore it is used particularly to return outcomes or results from procedures or computation and status messages from procedures to callers.
For instance, a procedure which computes the square of a number given and returns the square computed to the caller using the OUT parameter:
CREATE OR REPLACE PROCEDURE calculate_square (p_number IN NUMBER, p_result OUT NUMBER) AS
BEGIN
p_result := p_number * p_number;
END;
In this example, the p_number is an IN parameter providing the input number, while p_result is an OUT parameter that holds the calculated square which is then returned to the caller.
INOUT Parameter
The INOUT parameter combines the functionalities of both IN and OUT parameters, allowing data to flow both ways. It takes an initial value from the caller as input and allows that value to be modified inside the procedure while returning the modified value back to the caller. It finds particularly useful applications in all those scenarios where a value needs to be updated or accumulated over time.
For instance, a procedure can update a balance by adding an increment and the updated balance is returned to the caller through an INOUT parameter.
Here is an example:
CREATE OR REPLACE PROCEDURE update_balance (p_balance INOUT NUMBER, p_increment IN NUMBER) AS
BEGIN
p_balance := p_balance + p_increment;
In this example, p_balance is an INOUT parameter, which has the input for the starting balance and increments it in the procedure, then returns back to the caller.

9.What are PL/SQL collections? Explain associative arrays, nested tables and VARRAYs.
PL/SQL collections represent composite data structures with the ability to combine numerous elements of the same type under a single identifier. They are similar to the arrays found in other programming languages but are more potent because they offer flexibility related to size, indexing and storage mechanisms. PL/SQL collections are used predominantly for storing and manipulating a set of data. These are useful in scenarios where some batch processing or temporary storage of data is required.
PL/SQL supports three types of collections: associative arrays, nested tables and VARRAYs. Each of them has unique features and usage. They are therefore suited to different purposes.
Associative Arrays
An associative array formerly known as an index-by table is a collection type that uses a unique key (index) to identify and access its elements. The key may be a numeric or string value, which makes it quite flexible.
Associative arrays are unbounded, that is they do not have a predefined size and they exist only in memory, which makes them the ideal choice for temporary data storage during program execution.
Key Features
•Stored with unique keys (numbers or strings).
•Size is unbounded.
•It cannot be stored in a database.
Nested Table
Nested tables are collections acting like one-dimensional arrays. Unlike associative arrays, they may be stored in the database as a column in a table. They are not bounded until initialized this means their elements are allocated in contiguous memory without gap in it. Nested tables help you any time you wish to preserve data between blocks of PL/SQL or put several values in a database column.
Key Features
• Can be stored in the database as a column.
• Initially sparse but can be dense after manipulation.
• Support methods like EXTEND, DELETE and TRIM for dynamic resizing.
VARRAYs (Variable-Size Arrays)
VARRAYs, variable-size arrays are ordered collections with a predefined maximum size. They are useful when one knows the maximum number of elements before time. VARRAYs can be stored in the database as columns and are most effective for fixed-size data structures like a list of predefined options.
Key Features
•Fixed maximum size.
•Dense by default, with no gaps between elements.
•Can be stored in the database as a column.

10.What is dynamic SQL and when would you use it in PL/SQL?
Dynamic SQL in PL/SQL refers to the construction of SQL statements at runtime while executing rather than when coding and compiling. In short, such statements have a use case where the SQL query or command needs to be adaptive dynamically based on runtime conditions or user inputs. For example, you can use dynamic SQL for queries with dynamic table names, conditionally structured WHERE clauses or DDL statements to create or drop tables. It is implemented using the EXECUTE IMMEDIATE statement for one step execution or the DBMS_SQL package for more complex executions. Thus, dynamic SQL is all the more useful for creating flexible, reusable and generic PL/SQL programs that can be used under different requirements.

11.How do you handle NULL values in PL/SQL? Explain with examples.
A missing or an unknown value is termed as NULL in PL/SQL. Handling NULL values will be imperative for not bringing up errors and wrong results. One may determine the use of variables and columns having a NULL value with IS NULL and IS NOT NULL operators respectively. For example use the statement if v_name is NULL, THEN.

PL/SQL provides functions like NVL and COALESCE to handle NULL values by replacing them with a default value. The function NVL returns a specified value if the expression is NULL; COALESCE returns the first non NULL value in a list of expressions.

For example, NVL(v_amount, 0) would replace a NULL value in v_amount with 0.

NULLIF: a very useful function that returns NULL when two expressions are equal to one another. This often proves handy for division by zero where values are compared to yield NULL as the result.
NULLs in arithmetic operations may result in the whole result to become NULL. You can use NVL or COALESCE to replace NULLs with a default value during calculations. Proper handling of NULL values ensures that your PL/SQL code runs efficiently and produces the correct output.

12.What are packages in PL/SQL? Explain their benefits.
A package in PL/SQL groups related procedures, functions, variables and cursors in one unit. A package consists of two main parts specification and body. The specification specifies procedures, functions and variables that are accessible to other programs, whereas the body holds the code to execute procedures and functions.
Advantages of Packages:
Performance: The packages enhance performance because in the same package whole packages are loaded once into memory reducing repeated disk access.
Organization: They help to keep related pieces of code together hence easier to manage.
Security: Packages provide better security since the body hides the implementation details while leaving the package specification visible to users.
Code Reusability: Once a package is developed its components can be reused by different parts of the application saving development time.
Maintainability: Packages make it easier to maintain and modify code since changes are centralized.

13.What is the difference between RAISE and RAISE_APPLICATION_ERROR in PL/SQL?
In PL/SQL, RAISE and RAISE_APPLICATION_ERROR are used for raising exceptions. However, they differ in functionality.
RAISE is a generic keyword to re-raise an exception that has been already handled in a BEGIN EXCEPTION block. It is typically used when you want to propagate an exception after it is caught.
For instance, RAISE can raise the same exception caught.

RAISE_APPLICATION_ERROR procedure is a specific which raise a user-defined exception of an error message along with an error number. The flexibility of giving developers power for defining their own error number and messages which Oracle has no control over.

For example, RAISE_APPLICATION_ERROR(-20001, 'Invalid input'); the user-defined exception will raise with error number -20001 and the message as 'Invalid input'.
In other words RAISE is used for propagation of exceptions and RAISE_APPLICATION_ERROR for custom error handling based on messages and code.

14.What are Autonomous transactions in PL/SQL? How are they created?.
PL/SQL's autonomous transactions are separate independent transactions that don't get mixed up with the original one. It can log information handle errors without impacting the outcome of the original transaction. An autonomous transaction begins with the use of PRAGMA AUTONOMOUS_TRANSACTION inside a PL/SQL block. Therefore, even when the original transaction rolls back any change made in the autonomous transaction is committed or rolled back as well.
For instance, suppose you need to log errors into an audit table while the main transaction is running, but ensure that logging does not interfere with the result of the main transaction. You can use an autonomous transaction for this.
Here is an example:
CREATE OR REPLACE PROCEDURE log_error(p_error_message VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log (error_message, log_date)
VALUES (p_error_message, SYSDATE);
COMMIT; -- Commits independently of the main transaction
In the case study above, even if a rollback occurs on the principal transaction, the error logs in the autonomous transaction would still be committed. One of the advantages of the autonomous transaction is that it supports operations that must persist whatever the outcome of the principal transaction.

15.What is Bulk Binding in PL/SQL?
Bulk Binding in PL/SQL refers to techniques that improve performance by processing the bulk of data. One of the main methods applies to collections such as array or nested table types whereby one can fetch or modify several rows within a call instead of having to treat each row in an individual statement.
There are two core operations for bulk binding.
1.BULK COLLECT: This fetches several rows from a database and populates a PL/SQL collection in a single statement thus reducing the context switches between PL/SQL and SQL engine.
2.FORALL: It is an operation used for performing DML (insert, update, delete) operations on collections in a bulk manner thereby improving the performance by reducing individual SQL execution.

16.How do you optimize PL/SQL code for better performance?
Optimizing PL/SQL codes is done in several techniques:
Use Bulk Binding: It minimizes context switching along with improving data processing using techniques like BULK COLLECT and FORALL.
Minimize Context Switching: Minimize the interaction of SQL with PL/SQL by reducing the number of SQL statements executed within a PL/SQL block.
Use efficient SQL queries: Use of proper indexes, avoiding full table scan and appropriate joining are some of the important factors for optimizing SQL.
Use bind variables: The parsing time decreases and prevents SQL injection attacks. Use set-based DML operation instead of the loop: Set-based DML can use INSERT INTO SELECT rather than inserting data in a loop.
Use Exception Correctly: Avoid premature performance degradation with good exception handling.

17.What is the significance of a SAVEPOINT in PL/SQL? Explain.
A SAVEPOINT in PL/SQL is a specific point within a transaction in PL/SQL that helps do partial rollbacks, ensuring much more control over changing rollbacks without affecting all parts of the transaction. Therefore, it is valuable to commit some changes you'd like to keep intact while others may be rolled back to some previous SAVEPOINT in case of errors. This feature helps users return to any given SAVEPOINT and then rollback the work performed after it was achieved, this therefore means no work done after it. Any changes that are done during and before it remain preserved hence unwanted data loss can be minimized, and it enables transaction control more specifically.
For instance, if you have a transaction updating employee salaries in two steps and the second update fails, you can roll back to a savepoint set after the first update, ensuring that only the first update is retained while the second one is undone.

18.What is the purpose of the DBMS_OUTPUT package in PL/SQL?
The DBMS_OUTPUT package in PL/SQL is used to print output while running the PL/SQL blocks, procedures and functions. This is used by developers to print messages or variable values or debugging information on the console, which is a very important tool for debugging and tracing code while developing. The package has a procedure called PUT_LINE that writes a line of text or variable content to the output buffer. This is very helpful while testing or debugging PL/SQL code as it offers immediate feedback.
For example, using DBMS_OUTPUT.PUT_LINE('Some message') in a PL/SQL block will print the message to the console. But for output to be displayed the SERVEROUTPUT setting has to be turned on at the client tool say SQL Plus or SQL Developer.

19.What is a REF CURSOR in PL/SQL and how is it used?

A REF CURSOR in PL/SQL is a reference or pointer to a set of results returned by the SQL query. A more flexible cursor than a classical one a REF CURSOR can be passed as an argument between procedures and functions, making it perfect for queries that are dynamic and must be reused. It has the ability to create more general programs where the logic that will be used to apply the query can be determined by the program at runtime.
A REF CURSOR can be either strongly typed, associated with a specific record type or weakly typed, general not associated with any specific record type. It is frequently used in stored procedures or functions to return result sets to calling programs.

20.Explain the different types of loops in PL/SQL with examples.
In PL/SQL, there are three primary types of loops: Basic Loop, FOR Loop and WHILE Loop, which are used in specific instances.
Basic Loop: It is the simplest form of a loop in PL/SQL. It does not require a condition when defining it. The loop continues running indefinitely until explicitly told to exit, typically using the EXIT statement with a condition. The syntax is:
LOOP
-- statements to be executed
EXIT WHEN condition;
END LOOP;
FOR Loop: A FOR Loop is used when the number of iterations is known in advance. It automatically handles the iteration by incrementing a loop variable from a starting value to an ending value. The syntax includes the FOR keyword followed by the loop variable and the range of values:
FOR loop_variable IN [lower_bound]..[upper_bound] LOOP
-- statements to be executed
END LOOP;
This loop is to be used when you have the requirement to run a group of statements for a defined number of times.
WHILE Loop: This kind of loop is applied if the number of times, the loop is required to run, is not previously known and depends upon some conditions. The loop continues its execution if the condition comes out to be true. The syntax for WHILE loop is as follows:
This loop best suits the situation when one doesn't know how many iterations will be needed to run and wants the loop to run as long as the condition holds good.
Each type of a loop is chosen based on the number of iterations being known, dependent on a certain condition, or controlled manually.

21.What is PL/SQL Table?
A PL/SQL Table is a Collection type in PL/SQL used to store multiple rows of data in memory. It does not look like an array in other programming languages though, as it is much flexible. A PL/SQL Table can be dynamically re-sized and can hold values of any type and that can be either scalar or record types. The reason for using a PL/SQL Table is to keep intermediate results of PL/SQL blocks. PL/SQL Tables consist of an index and an associated collection of values. They are useful in storing query results or in performing bulk operations efficiently. They are usually used along with BULK COLLECT and FORALL for optimized performance.

22.What is the purpose of the PRAGMA AUTONOMOUS_TRANSACTION directive in PL/SQL?
An autonomous transaction can be specified through the use of the PL/SQL directive PRAGMA AUTONOMOUS_TRANSACTION to define an autonomous transaction nested in an outer transaction. The advantage to this is that the entire change executed in this block or procedure can now commit or roll back independently, without affecting the outer transaction. This is useful in cases of logging ,auditing or exception handling because changes made in the autonomous transaction are not rolled back if the main transaction fails. The directive is normally used whenever you need to carry out operations such as logging of errors or updating audit tables without being influenced by whether the main transaction succeeded or failed.

23.How is recursion implemented in PL/SQL? Provide an example.
Recursion in PL/SQL is implemented by a procedure or function calling itself to solve a problem. It is normally used for problems that can be broken down into smaller subproblems, like calculating factorials, traversing hierarchical data or processing tree structures. To make recursion work there must be a base case that stops the recursive calls from entering infinite recursion.
The syntax involves defining a procedure or function that calls itself with modified parameters. A simple example is calculating the factorial of a number:

CREATE OR REPLACE FUNCTION factorial(n IN NUMBER) RETURN NUMBER IS
BEGIN
IF n = 0 THEN
RETURN 1;
ELSE
RETURN n * factorial(n - 1);
END IF;
END;
In the present example, this function will call itself with n - 1 until reaching the base case: n = 0, which will return 1.

24.How do you commit or rollback in triggers?
In PL/SQL you are not allowed to perform a COMMIT or ROLLBACK statement directly within a trigger as triggers are part of the main transaction. When an attempt is made to do commit or rollback within the context of a trigger the course of the transaction becomes disjointed and inconsistent rather, the changes made inside the trigger are automatically absorbed in the enclosing transaction for a final COMMIT or a ROLLBACK that might follow.
Independent transaction control can be utilized within a trigger only by declaring the trigger block using PRAGMA AUTONOMOUS_TRANSACTION. This lets the trigger commit or rollback changes independently, usually for logging purposes.

25.How do you debug PL/SQL code effectively? Explain common debugging techniques.
Debugging PL/SQL code essentially means tracking down and removing errors to ensure the program operates as it should. Some popular techniques are:
DBMS_OUTPUT.PUT_LINE: prints variable values or messages in the console, which helps trace through the flow of execution as well as identify problems;
Exception Handling: Adding blocks with error messages or logs helps catch runtime errors.
Logging: The logs write in a table or a file that captures details about the program's state for further analysis.
Enabling server output: Debugging messages should be shown at execution. These techniques facilitate debugging while keeping the code reliability at optimum level.

Previous Topic==> Single Row Function to Customize output FAQ || Next Topic==> Conversion Function and conditional Expression FAQ.

SQL All topics Wise Interview Questions Employee Salary Management SQL FAQ!. C FAQ
Topics for Account Management Case Study
CASE Study SQL (Account Management)
Joins With Group by Having Equi Join Joins with Subqueries Self Join Outer Join