PL/SQL questions along with examples

2. How do you handle exceptions in PL/SQL?

• Question: Write a PL/SQL block that handles the NO_DATA_FOUND and TOO_MANY_ROWS exceptions.
• Example:
  DECLARE
  v_employee_name VARCHAR2(50);
  BEGIN
  SELECT first_name INTO v_employee_name
  FROM employees
  WHERE employee_id = 9999; — Assuming this ID does not exist

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(‘More than one employee found with that ID.’);
END;

3. What are cursors in PL/SQL?

• Question: Explain the difference between an implicit and explicit cursor. Write a PL/SQL block that uses an explicit cursor to fetch and display all employee names from the EMPLOYEES table.
• Example:

DECLARE
CURSOR emp_cursor IS
SELECT first_name FROM employees;
v_first_name employees.first_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_first_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_first_name);
END LOOP;
CLOSE emp_cursor;
END;

4. How can you create a stored procedure in PL/SQL?

• Question: Write a stored procedure that accepts an employee ID and a percentage, then increases the employee’s salary by that percentage.
• Example:
  CREATE OR REPLACE PROCEDURE increase_salary (
  p_employee_id IN employees.employee_id%TYPE,
  p_percentage IN NUMBER
  ) AS
  BEGIN
  UPDATE employees
  SET salary = salary + (salary * p_percentage / 100)
  WHERE employee_id = p_employee_id;

IF SQL%ROWCOUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(‘Salary updated.’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Employee not found.’);
END IF;
END increase_salary;

5. What are triggers in PL/SQL?

• Question: Write a BEFORE INSERT trigger on the ORDERS table that automatically sets the order_date to the current date if it is not provided
• Example:

CREATE OR REPLACE TRIGGER set_order_date
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
IF :NEW.order_date IS NULL THEN
:NEW.order_date := SYSDATE;
END IF;
END;

6. How do you use collections in PL/SQL?

• Question: Write a PL/SQL block that uses a nested table to store a list of employee IDs and then displays them.
• Example:
  DECLARE
  TYPE emp_table_type IS TABLE OF employees.employee_id%TYPE;
  emp_table emp_table_type := emp_table_type(100, 101, 102);
  BEGIN
  FOR i IN 1..emp_table.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE(‘Employee ID: ‘ || emp_table(i));
  END LOOP;
  END;

6. What is dynamic SQL in PL/SQL?

• Question: Write a PL/SQL block that uses dynamic SQL to delete a record from any table, based on the table name and the condition provided.
• Example:
  DECLARE
  v_table_name VARCHAR2(30) := ’employees’;
  v_condition VARCHAR2(100) := ’employee_id = 102′;
  v_sql VARCHAR2(200);
  BEGIN
  v_sql := ‘DELETE FROM ‘ || v_table_name || ‘ WHERE ‘ || v_condition;
  EXECUTE IMMEDIATE v_sql;
  DBMS_OUTPUT.PUT_LINE(‘Record deleted.’);
  END;

6. Explain pipelined table functions.

• Question: Write a pipelined table function that returns a list of even numbers from 1 to a given number.
• Example:
  CREATE OR REPLACE FUNCTION get_even_numbers(p_max_num IN NUMBER)
  RETURN TABLE OF NUMBER PIPELINED
  AS

BEGIN
FOR i IN 1..p_max_num LOOP
IF MOD(i, 2) = 0 THEN
PIPE ROW(i);
END IF;
END LOOP;
RETURN;
END;

9. How do you implement bulk collect in PL/SQL?

• Question: Write a PL/SQL block that uses BULK COLLECT to fetch all employee names into a collection and then displays them.
• Example:
  DECLARE
  TYPE emp_name_table IS TABLE OF employees.first_name%TYPE;
  emp_names emp_name_table;

BEGIN
SELECT first_name BULK COLLECT INTO emp_names FROM employees;
FOR i IN 1..emp_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(emp_names(i));
END LOOP;
END;

10. How do you create and use a package in PL/SQL?

• Question: Write a PL/SQL package that contains a function to calculate the factorial of a number and a procedure to display it.
• Example:
  CREATE OR REPLACE PACKAGE math_pkg AS
  FUNCTION factorial(n NUMBER) RETURN NUMBER;
  PROCEDURE display_factorial(n NUMBER);
  END math_pkg;
  CREATE OR REPLACE PACKAGE BODY math_pkg AS
  FUNCTION factorial(n NUMBER) RETURN NUMBER IS
  v_result NUMBER := 1;

BEGIN
FOR i IN 1..n LOOP
v_result := v_result * i;
END LOOP;
RETURN v_result;
END factorial;
PROCEDURE display_factorial(n NUMBER) IS
v_result NUMBER;

BEGIN
v_result := factorial(n);

DBMS_OUTPUT.PUT_LINE(‘Factorial of ‘ || n || ‘ is: ‘ || v_result);

END display_factorial;

END math_pkg;