This content originally appeared on DEV Community and was authored by Pranav Bakare
Sure! Let’s break down each of the top 10 interview questions for PL/SQL developers with a basic definition and examples.
- 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;
- 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;
- 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;
- 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;
- 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;
- 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;
- 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.
- 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;
- %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;
- 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!
This content originally appeared on DEV Community and was authored by Pranav Bakare