PLSQL Top 10 Interview Questions

Sure! Let's break down each of the top 10 interview questions for PL/SQL developers with a basic definition and examples.

1. FUNCTION vs. PROCEDURE vs. PACKAGE

Definition:

Function: A PL/SQL subprogram that returns a single value.

Procedure: A PL/SQL subprogram that performs an action but may or may not return a value.

Package: A collection of related functions, procedures, variables, and other PL/SQL types.

Example:

-- Function Example
CREATE OR REPLACE FUNCTION get_salary (p_employee_id IN NUMBER)
RETURN NUMBER
IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id;
RETURN v_salary;
END;

-- Procedure Example
CREATE OR REPLACE PROCEDURE raise_salary (p_employee_id IN NUMBER, p_percent IN NUMBER)
IS
BEGIN
UPDATE employees SET salary = salary + (salary * p_percent / 100) WHERE employee_id = p_employee_id;
COMMIT;
END;

-- Package Example
CREATE OR REPLACE PACKAGE employee_pkg IS
FUNCTION get_salary (p_employee_id IN NUMBER) RETURN NUMBER;
PROCEDURE raise_salary (p_employee_id IN NUMBER, p_percent IN NUMBER);
END employee_pkg;

1. Cursor

Definition: A cursor is a pointer to a result set of a query. There are two types: Implicit (automatically handled) and Explicit (defined and controlled by the developer).

Example:

-- Explicit Cursor Example
DECLARE
CURSOR emp_cursor IS SELECT employee_id, first_name, last_name FROM employees;
v_employee_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_id, v_first_name, v_last_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name);
END LOOP;
CLOSE emp_cursor;
END;

1. Trigger

Definition: A trigger is a stored program that runs automatically in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table.

Example:

-- After Insert Trigger Example
CREATE OR REPLACE TRIGGER trg_after_insert_employee
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (employee_id, action_date, action)
VALUES (:NEW.employee_id, SYSDATE, 'INSERT');
END;

1. Bulk Collect and Forall

Definition:

BULK COLLECT: Used to fetch multiple rows in a single fetch operation.

FORALL: Used to execute the same operation for all elements of a collection efficiently.

Example:

DECLARE
TYPE emp_table IS TABLE OF employees%ROWTYPE;
l_employees emp_table;
BEGIN
-- Bulk Collect to fetch multiple rows
SELECT * BULK COLLECT INTO l_employees FROM employees WHERE department_id = 10;

-- Forall to perform bulk insert
FORALL i IN 1..l_employees.COUNT
INSERT INTO employees_archive VALUES l_employees(i);
END;

1. Exception Handling

Definition: PL/SQL handles exceptions (errors) using the EXCEPTION block. There are predefined exceptions (e.g., NO_DATA_FOUND) and user-defined exceptions.

Example:

DECLARE
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with that ID');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple employees found with that ID');
END;

1. Dynamic SQL

Definition: Dynamic SQL allows the execution of SQL statements that are constructed at runtime. It is implemented using EXECUTE IMMEDIATE or DBMS_SQL.

Example:

-- Dynamic SQL Example using EXECUTE IMMEDIATE
DECLARE
v_table_name VARCHAR2(30) := 'employees';
v_sql VARCHAR2(100);
BEGIN
v_sql := 'DELETE FROM ' || v_table_name || ' WHERE department_id = :dept_id';
EXECUTE IMMEDIATE v_sql USING 10;
END;

1. Performance Tuning Techniques

Definition: Techniques used to improve the performance of PL/SQL code and SQL queries.

Example:

Using Indexes:

-- Ensure that frequently queried columns are indexed
CREATE INDEX idx_emp_dept ON employees(department_id);

Avoiding Full Table Scans:

-- Use WHERE conditions to avoid scanning the entire table
SELECT * FROM employees WHERE department_id = 10;

Bulk Operations: Use BULK COLLECT and FORALL to improve performance when handling large datasets.

1. Collections

Definition: Collections are PL/SQL data structures that can store multiple elements. There are three types: Associative Arrays, Nested Tables, and VARRAYs.

Example:

DECLARE
TYPE emp_names IS TABLE OF VARCHAR2(100);
l_emp_names emp_names := emp_names();
BEGIN
-- Adding elements to the collection
l_emp_names.EXTEND;
l_emp_names(1) := 'John';
l_emp_names.EXTEND;
l_emp_names(2) := 'Jane';

-- Accessing elements from the collection
FOR i IN 1..l_emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(l_emp_names(i));
END LOOP;
END;

1. %TYPE and %ROWTYPE

Definition:

%TYPE: Declares a variable that has the same data type as a column in a table.

%ROWTYPE: Declares a record that has the same structure as a row in a table.

Example:

DECLARE
v_employee_id employees.employee_id%TYPE;
v_employee_record employees%ROWTYPE;
BEGIN
-- Using %TYPE
SELECT employee_id INTO v_employee_id FROM employees WHERE employee_id = 100;

-- Using %ROWTYPE
SELECT * INTO v_employee_record FROM employees WHERE employee_id = 100;

DBMS_OUTPUT.PUT_LINE(v_employee_record.first_name || ' ' || v_employee_record.last_name);
END;

1. Debugging PL/SQL Code

Definition: Debugging involves identifying and fixing errors in PL/SQL code using tools like DBMS_OUTPUT.PUT_LINE or third-party tools like Oracle SQL Developer.

Example:

DECLARE
v_employee_id NUMBER := 100;
v_salary NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Fetching salary for employee ID: ' || v_employee_id);
SELECT salary INTO v_salary FROM employees WHERE employee_id = v_employee_id;
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

Bonus: Mutating Table Error

Definition: A mutating table error occurs when a trigger tries to modify the same table on which it's defined.

Example:

-- Example to avoid mutating table error
CREATE OR REPLACE TRIGGER trg_before_update_employee
BEFORE UPDATE ON employees
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO employee_log (employee_id, action_date, old_salary, new_salary)
VALUES (:OLD.employee_id, SYSDATE, :OLD.salary, :NEW.salary);
COMMIT;
END;

---

These are detailed definitions and examples for each of the common interview questions for a PL/SQL developer. By understanding these concepts and practicing the examples, you'll be well-prepared for most interviews!