Pranav BakareSure! 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 getsalary (pemployeeid IN NUMBER)
RETURN NUMBER
IS
vsalary NUMBER;
BEGIN
SELECT salary INTO vsalary
FROM employees
WHERE employeeid = pemployeeid;
RETURN vsalary;
END;
Procedure Example
CREATE OR REPLACE PROCEDURE raisesalary (pemployeeid IN NUMBER, ppercent IN NUMBER)
IS
BEGIN
UPDATE employees
SET salary = salary + (salary * ppercent / 100)
WHERE employeeid = pemployeeid;
COMMIT;
END;
Package Example
CREATE OR REPLACE PACKAGE employeepkg IS
FUNCTION getsalary (pemployeeid IN NUMBER) RETURN NUMBER;
PROCEDURE raisesalary (pemployeeid IN NUMBER, ppercent IN NUMBER);
END employeepkg;
2. 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 empcursor IS SELECT employeeid, firstname, lastname FROM employees;
vemployeeid employees.employeeid%TYPE;
vfirstname employees.firstname%TYPE;
vlastname employees.lastname%TYPE;
BEGIN
OPEN empcursor;
LOOP
FETCH empcursor INTO vemployeeid, vfirstname, vlastname;
EXIT WHEN empcursor%NOTFOUND;
DBMSOUTPUT.PUTLINE(vfirstname || ' ' || vlastname);
END LOOP;
CLOSE empcursor;
END;
3. 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 trgafterinsertemployee
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employeeaudit (employeeid, actiondate, action)
VALUES (:NEW.employeeid, SYSDATE, 'INSERT');
END;
4. 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 emptable IS TABLE OF employees%ROWTYPE;
lemployees emptable;
BEGIN
-- Bulk Collect to fetch multiple rows
SELECT * BULK COLLECT INTO lemployees FROM employees WHERE departmentid = 10;
-- Forall to perform bulk insert
FORALL i IN 1..lemployees.COUNT
INSERT INTO employeesarchive VALUES lemployees(i);
END;
5. Exception Handling
Definition: PL/SQL handles exceptions (errors) using the EXCEPTION block. There are predefined exceptions (e.g., NODATAFOUND) and user-defined exceptions.
Example:
DECLARE
vsalary NUMBER;
BEGIN
SELECT salary INTO vsalary FROM employees WHERE employeeid = 100;
DBMSOUTPUT.PUTLINE('Salary: ' || vsalary);
EXCEPTION
WHEN NODATAFOUND THEN
DBMSOUTPUT.PUTLINE('No employee found with that ID');
WHEN TOOMANYROWS THEN
DBMSOUTPUT.PUTLINE('Multiple employees found with that ID');
END;
6. Dynamic SQL
Definition: Dynamic SQL allows the execution of SQL statements that are constructed at runtime. It is implemented using EXECUTE IMMEDIATE or DBMSSQL.
Example:
-- Dynamic SQL Example using EXECUTE IMMEDIATE
DECLARE
vtablename VARCHAR2(30) := 'employees';
vsql VARCHAR2(100);
BEGIN
vsql := 'DELETE FROM ' || vtablename || ' WHERE departmentid = :deptid';
EXECUTE IMMEDIATE vsql USING 10;
END;
7. 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 idxempdept ON employees(departmentid);
Avoiding Full Table Scans:
-- Use WHERE conditions to avoid scanning the entire table
SELECT * FROM employees WHERE departmentid = 10;
Bulk Operations: Use BULK COLLECT and FORALL to improve performance when handling large datasets.
8. 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 empnames IS TABLE OF VARCHAR2(100);
lempnames empnames := empnames();
BEGIN
-- Adding elements to the collection
lempnames.EXTEND;
lempnames(1) := 'John';
lempnames.EXTEND;
lempnames(2) := 'Jane';
-- Accessing elements from the collection
FOR i IN 1..lempnames.COUNT LOOP
DBMSOUTPUT.PUTLINE(lempnames(i));
END LOOP;
END;
9. %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
vemployeeid employees.employeeid%TYPE;
vemployeerecord employees%ROWTYPE;
BEGIN
-- Using %TYPE
SELECT employeeid INTO vemployeeid FROM employees WHERE employeeid = 100;
-- Using %ROWTYPE
SELECT * INTO vemployeerecord FROM employees WHERE employeeid = 100;
DBMSOUTPUT.PUTLINE(vemployeerecord.firstname || ' ' || vemployeerecord.lastname);
END;
10. Debugging PL/SQL Code
Definition: Debugging involves identifying and fixing errors in PL/SQL code using tools like DBMSOUTPUT.PUTLINE or third-party tools like Oracle SQL Developer.
Example:
DECLARE
vemployeeid NUMBER := 100;
vsalary NUMBER;
BEGIN
DBMSOUTPUT.PUTLINE('Fetching salary for employee ID: ' || vemployeeid);
SELECT salary INTO vsalary FROM employees WHERE employeeid = vemployeeid;
DBMSOUTPUT.PUTLINE('Salary: ' || vsalary);
EXCEPTION
WHEN OTHERS THEN
DBMSOUTPUT.PUTLINE('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 trgbeforeupdateemployee
BEFORE UPDATE ON employees
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUSTRANSACTION;
BEGIN
INSERT INTO employeelog (employeeid, actiondate, oldsalary, newsalary)
VALUES (:OLD.employeeid, 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!
