For a PL/SQL Developer with 3 years of experience, interview questions typically cover a range of basic, intermediate, and advanced topics. Here are the top 10 questions commonly asked, along with explanations and example answers.
- What is PL/SQL and How Does it Differ from SQL?
Explanation: SQL is a language for querying databases and performing operations such as SELECT, INSERT, UPDATE, and DELETE. PL/SQL, which stands for Procedural Language/SQL, is Oracle's procedural extension to SQL. It includes programming constructs like loops, conditions, and exception handling, making it more powerful for complex operations.
Example:
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_count);
END;
- Explain Cursors in PL/SQL. What Types of Cursors Are There?
Explanation: Cursors are pointers to a result set, allowing row-by-row processing. Cursors can be Implicit (automatically created by Oracle for SELECT statements that return one row) or Explicit (defined by the programmer for multiple-row queries).
Example of Explicit Cursor:
DECLARE
CURSOR emp_cursor IS SELECT employee_id, first_name FROM employees;
v_employee_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_employee_id, v_first_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee_id || ', Name: ' || v_first_name);
END LOOP;
CLOSE emp_cursor;
END;
- What is the Purpose of a Trigger, and How is it Used in PL/SQL?
Explanation: A trigger is a stored procedure that executes automatically in response to certain events (e.g., INSERT, UPDATE, or DELETE) on a table. Triggers are often used for logging, enforcing rules, or validating data.
Example:
CREATE OR REPLACE TRIGGER trg_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.hire_date := SYSDATE;
END;
- Explain the Difference Between Procedures and Functions in PL/SQL.
Explanation: Both are subprograms in PL/SQL, but Procedures perform actions and don’t return values directly, while Functions must return a single value. Functions are typically used in queries, while procedures are used for executing tasks.
Example of a Function:
CREATE OR REPLACE FUNCTION get_employee_count RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
RETURN v_count;
END;
Example of a Procedure:
CREATE OR REPLACE PROCEDURE increase_salary(p_emp_id IN NUMBER, p_amount IN NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + p_amount WHERE employee_id = p_emp_id;
END;
- What is Exception Handling in PL/SQL? Provide Examples of Predefined Exceptions.
Explanation: Exception handling in PL/SQL is used to manage errors gracefully. Predefined exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE help handle specific issues that may arise during execution.
Example:
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such employee found.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More than one employee found.');
END;
- Explain PL/SQL Collections and the Different Types Available.
Explanation: Collections in PL/SQL allow the storage of multiple elements. The three main types are:
Associative Arrays (Index-By tables): Key-value pairs, similar to dictionaries.
Nested Tables: Unordered sets of elements, allowing duplicates.
VARRAYs (Variable-size Arrays): Fixed-size arrays.
Example of Associative Array:
DECLARE
TYPE EmpArray IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
v_emps EmpArray;
BEGIN
v_emps(1) := 'John';
v_emps(2) := 'Jane';
DBMS_OUTPUT.PUT_LINE(v_emps(1) || ', ' || v_emps(2));
END;
- How Do You Optimize PL/SQL Code?
Explanation: Optimizing PL/SQL code involves several practices:
Using BULK COLLECT and FORALL for bulk operations to reduce context switches between PL/SQL and SQL engines.
Avoiding unnecessary computations and SQL statements inside loops.
Using NOCOPY with OUT/IN OUT parameters to improve performance in procedures.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
emp_ids NumList;
BEGIN
SELECT employee_id BULK COLLECT INTO emp_ids FROM employees;
FORALL i IN emp_ids.FIRST..emp_ids.LAST
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_ids(i);
END;
- Explain the Use of %TYPE and %ROWTYPE Attributes in PL/SQL.
Explanation: %TYPE and %ROWTYPE are used to declare variables that automatically adopt the datatype of a table column or the entire row structure of a table.
%TYPE is used for single column types.
%ROWTYPE is used for a whole row structure.
Example:
DECLARE
v_emp_id employees.employee_id%TYPE;
v_emp_record employees%ROWTYPE;
BEGIN
SELECT employee_id INTO v_emp_id FROM employees WHERE first_name = 'John';
SELECT * INTO v_emp_record FROM employees WHERE employee_id = v_emp_id;
END;
- What are Packages in PL/SQL and Their Advantages?
Explanation: A package is a collection of PL/SQL objects like procedures, functions, variables, and types grouped together. Packages promote modularization, encapsulate logic, and allow for easier management and security.
Example:
CREATE OR REPLACE PACKAGE emp_pkg AS
PROCEDURE update_salary(p_emp_id NUMBER, p_amount NUMBER);
FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER;
END emp_pkg;
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
PROCEDURE update_salary(p_emp_id NUMBER, p_amount NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + p_amount WHERE employee_id = p_emp_id;
END;
FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
RETURN v_salary;
END;
END emp_pkg;
- Describe the Use of Bulk Collect and Forall in PL/SQL.
Explanation: BULK COLLECT fetches multiple rows at once into a collection, while FORALL allows performing DML operations on collections, both reducing context switching between PL/SQL and SQL.
Example:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
emp_ids NumList;
salaries NumList;
BEGIN
SELECT employee_id, salary BULK COLLECT INTO emp_ids, salaries FROM employees;
FORALL i IN emp_ids.FIRST..emp_ids.LAST
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_ids(i);
END;
These questions and examples test a candidate’s understanding of core PL/SQL concepts, performance optimization techniques, and experience with programming structures and error handling essential for PL/SQL development. Practicing these will strengthen one’s technical skills and enhance interview readiness.
Top comments (0)