PL/SQL Interview Questions & Answers

Basic PL/SQL Concepts:

Q: What is PL/SQL?
- A: PL/SQL is Oracle Corporation's procedural extension for SQL. It includes procedural language elements such as conditions and loops, allowing for the creation of stored procedures, functions, and triggers.
Q: Explain the difference between SQL and PL/SQL.
- A: SQL is a query language used for database operations, while PL/SQL is a procedural language that extends SQL by adding control structures and error handling.
Q: How do you declare variables in PL/SQL?
- A: Variables are declared using the DECLARE keyword. Example: DECLARE variable_name datatype;
Q: What is a PL/SQL block?
- A: A PL/SQL block is a group of PL/SQL statements enclosed by the DECLARE, BEGIN, and END keywords.
Q: Explain the purpose of the EXCEPTION block in PL/SQL.
- A: The EXCEPTION block is used to handle errors that might occur during the execution of a PL/SQL block.

PL/SQL Procedures and Functions:

Q: How do you create a procedure in PL/SQL?

CREATE OR REPLACE PROCEDURE procedure_name
IS
-- Declarations
BEGIN
  -- Statements
END procedure_name;

Q: What is a PL/SQL function, and how is it different from a procedure?
- A: A function returns a value, while a procedure does not. Functions are called in SQL expressions, while procedures are called as standalone statements.
Q: Explain the OUT parameter in PL/SQL.
- A: The OUT parameter is used to pass values from the stored procedure or function back to the calling program.
Q: How do you call a stored procedure in PL/SQL?
- A: Using the EXECUTE or EXEC statement. Example: EXECUTE procedure_name;
Q: What is a PL/SQL package?
- A: A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. It provides modularity and encapsulation.

PL/SQL Cursors and Exceptions:

Q: Explain the difference between Implicit and Explicit Cursors in PL/SQL.
- A: Implicit cursors are automatically created by Oracle for SQL statements, while explicit cursors are explicitly declared and managed by the programmer.

Q: How do you use a cursor in PL/SQL?

A: Declare a cursor, open it, fetch rows, and close it. Example:

DECLARE
  CURSOR cursor_name IS SELECT column1, column2 FROM table_name;
  variable1 datatype;
  variable2 datatype;
BEGIN
  OPEN cursor_name;
  FETCH cursor_name INTO variable1, variable2;
  CLOSE cursor_name;
END;

Q: What is the purpose of the FORALL statement in PL/SQL?
- A: FORALL is used to bulk-bind collections, allowing multiple rows to be processed in a single DML statement.
Q: How do you handle exceptions in PL/SQL?
- A: Use the EXCEPTION block to catch and handle errors. Example:
```
BEGIN
  -- Statements
EXCEPTION
  WHEN others THEN
    -- Handle the exception
END;
```
Q: Explain the concept of the RAISE statement in PL/SQL.
- A: The RAISE statement is used to propagate an exception to the calling environment or to a higher level of an exception block.

PL/SQL Triggers:

Q: What is a PL/SQL trigger?
- A: A trigger is a set of instructions that are automatically executed in response to certain events on a particular table or view.

Q: How do you create a trigger in PL/SQL?

CREATE OR REPLACE TRIGGER trigger_name
BEFORE/AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
BEGIN
  -- Trigger logic
END;

Q: What is the difference between a row-level trigger and a statement-level trigger?
- A: A row-level trigger is executed once for each row affected by the triggering statement, while a statement-level trigger is executed once for the triggering statement.
Q: How do you refer to the old and new values in a row-level trigger?
- A: Use the `:OLD

and:NEW` qualifiers. Example: plsql :OLD.column_name :NEW.column_name

Q: Explain the use of the INSTEAD OF trigger in PL/SQL.
- A: An INSTEAD OF trigger is used to replace the triggering statement with the logic defined in the trigger. It is often used with views.