📘 PL/SQL Practice Guide: Functions, Procedures, Triggers
📌 Functions in PL/SQL
🔹 Syntax
CREATE OR REPLACE FUNCTION function_name (
param1 IN data_type,
param2 IN data_type
) RETURN return_data_type IS
BEGIN
-- logic
RETURN some_value;
END;
/
✅ Practice Questions with Solutions
1. Check Even or Odd
CREATE OR REPLACE FUNCTION check_even_odd(n IN NUMBER) RETURN VARCHAR2 IS
BEGIN
IF MOD(n, 2) = 0 THEN
RETURN 'EVEN';
ELSE
RETURN 'ODD';
END IF;
END;
/
2. Get Maximum of Two Numbers
CREATE OR REPLACE FUNCTION get_max(a IN NUMBER, b IN NUMBER) RETURN NUMBER IS
BEGIN
IF a > b THEN
RETURN a;
ELSE
RETURN b;
END IF;
END;
/
3. Count Vowels in a String
CREATE OR REPLACE FUNCTION count_vowels(str IN VARCHAR2) RETURN NUMBER IS
count NUMBER := 0;
ch CHAR;
BEGIN
FOR i IN 1..LENGTH(str) LOOP
ch := LOWER(SUBSTR(str, i, 1));
IF ch IN ('a', 'e', 'i', 'o', 'u') THEN
count := count + 1;
END IF;
END LOOP;
RETURN count;
END;
/
4. Check Palindrome
CREATE OR REPLACE FUNCTION is_palindrome(str IN VARCHAR2) RETURN VARCHAR2 IS
reversed_str VARCHAR2(1000) := '';
BEGIN
FOR i IN REVERSE 1..LENGTH(str) LOOP
reversed_str := reversed_str || SUBSTR(str, i, 1);
END LOOP;
IF LOWER(str) = LOWER(reversed_str) THEN
RETURN 'YES';
ELSE
RETURN 'NO';
END IF;
END;
/
5. Sum of Digits
CREATE OR REPLACE FUNCTION sum_of_digits(n IN NUMBER) RETURN NUMBER IS
sum NUMBER := 0;
digit NUMBER;
num NUMBER := n;
BEGIN
WHILE num > 0 LOOP
digit := MOD(num, 10);
sum := sum + digit;
num := FLOOR(num / 10);
END LOOP;
RETURN sum;
END;
/
🔧 Procedures in PL/SQL
🔹 Syntax
CREATE OR REPLACE PROCEDURE procedure_name (param1 IN/OUT data_type) IS
BEGIN
-- logic
END;
/
✅ Practice Questions with Solutions
1. Procedure to Print Square
CREATE OR REPLACE PROCEDURE print_square(n IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Square: ' || (n * n));
END;
/
2. Procedure to Update Employee Salary
CREATE OR REPLACE PROCEDURE increase_salary(empId IN NUMBER) IS
BEGIN
UPDATE employees
SET salary = salary * 1.10
WHERE emp_id = empId;
COMMIT;
END;
/
3. Procedure with OUT Parameter for Factorial
CREATE OR REPLACE PROCEDURE find_factorial(n IN NUMBER, result OUT NUMBER) IS
fact NUMBER := 1;
BEGIN
FOR i IN 1..n LOOP
fact := fact * i;
END LOOP;
result := fact;
END;
/
🔥 Triggers in PL/SQL
🔹 Syntax
CREATE OR REPLACE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
-- logic
END;
/
✅ Practice Questions with Solutions
1. BEFORE INSERT Trigger
CREATE OR REPLACE TRIGGER trg_set_created_at
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
:NEW.created_at := SYSDATE;
END;
/
2. AFTER UPDATE Trigger for Salary Audit
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit(emp_id, old_salary, new_salary, change_date)
VALUES(:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/
3. BEFORE DELETE Trigger to Prevent Deletion
CREATE OR REPLACE TRIGGER trg_prevent_delete
BEFORE DELETE ON departments
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Deletion is not allowed on departments table.');
END;
/
📚 Additional Practice Questions
🔧 Functions
🔹 6. Reverse a String
Question: Write a function to reverse a given string.
