Basic PL/SQL Function Examples
1. Simple Function to Add Two Numbers
CREATE OR REPLACE FUNCTION add_numbers (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN a + b;
END;
/2. Function to Calculate Area of a Circle
CREATE OR REPLACE FUNCTION area_of_circle (radius NUMBER) RETURN NUMBER IS
BEGIN
RETURN 3.14159 * radius * radius;
END;
/3. Function to Get Employee Full Name
CREATE OR REPLACE FUNCTION get_full_name (emp_id NUMBER) RETURN VARCHAR2 IS
first_name VARCHAR2(50);
last_name VARCHAR2(50);
full_name VARCHAR2(100);
BEGIN
SELECT first_name, last_name
INTO first_name, last_name
FROM employees
WHERE employee_id = emp_id;
full_name := first_name || ' ' || last_name;
RETURN full_name;
END;
/4. Function to Calculate Factorial
CREATE OR REPLACE FUNCTION factorial (n NUMBER) RETURN NUMBER IS
result NUMBER := 1;
BEGIN
FOR i IN 1..n LOOP
result := result * i;
END LOOP;
RETURN result;
END;
/5. Function to Check if Number is Even
CREATE OR REPLACE FUNCTION is_even (n NUMBER) RETURN BOOLEAN IS
BEGIN
RETURN MOD(n, 2) = 0;
END;
/6. Function to Convert Celsius to Fahrenheit
CREATE OR REPLACE FUNCTION celsius_to_fahrenheit (celsius NUMBER) RETURN NUMBER IS
BEGIN
RETURN (celsius * 9/5) + 32;
END;
/7. Function to Calculate Age from Date of Birth
CREATE OR REPLACE FUNCTION calculate_age (dob DATE) RETURN NUMBER IS
BEGIN
RETURN FLOOR(MONTHS_BETWEEN(SYSDATE, dob) / 12);
END;
/8. Function to Get Department Name
CREATE OR REPLACE FUNCTION get_department_name (dept_id NUMBER) RETURN VARCHAR2 IS
dept_name VARCHAR2(100);
BEGIN
SELECT department_name
INTO dept_name
FROM departments
WHERE department_id = dept_id;
RETURN dept_name;
END;
/9. Function to Calculate Simple Interest
CREATE OR REPLACE FUNCTION calculate_interest
(
principal NUMBER,
rate NUMBER,
time NUMBER
) RETURN NUMBER IS
BEGIN
RETURN (principal * rate * time) / 100;
END;
/10. Function to Reverse a String
CREATE OR REPLACE FUNCTION reverse_string (input_str VARCHAR2) RETURN VARCHAR2 IS
reversed_str VARCHAR2(4000);
BEGIN
FOR i IN REVERSE 1..LENGTH(input_str) LOOP
reversed_str := reversed_str || SUBSTR(input_str, i, 1);
END LOOP;
RETURN reversed_str;
END;
/Number and String Functions
11. Function to Get the Maximum of Two Numbers
CREATE OR REPLACE FUNCTION max_of_two (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
IF a > b THEN
RETURN a;
ELSE
RETURN b;
END IF;
END;
/12. Function to Get the Minimum of Two Numbers
CREATE OR REPLACE FUNCTION min_of_two (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
IF a < b THEN
RETURN a;
ELSE
RETURN b;
END IF;
END;
/13. Function to Calculate Power of a Number
CREATE OR REPLACE FUNCTION power_of (base NUMBER, exponent NUMBER) RETURN NUMBER IS
result NUMBER := 1;
BEGIN
FOR i IN 1..exponent LOOP
result := result * base;
END LOOP;
RETURN result;
END;
/14. Function to Convert String to Upper Case
CREATE OR REPLACE FUNCTION to_upper_case (input_str VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN UPPER(input_str);
END;
/15. Function to Convert String to Lower Case
CREATE OR REPLACE FUNCTION to_lower_case (input_str VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN LOWER(input_str);
END;
/Employee and Database Functions
16. Function to Count Number of Employees in a Department
CREATE OR REPLACE FUNCTION count_employees_in_dept (dept_id NUMBER) RETURN NUMBER IS
emp_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO emp_count
FROM employees
WHERE department_id = dept_id;
RETURN emp_count;
END;
/17. Function to Get Employee Salary
CREATE OR REPLACE FUNCTION get_salary (emp_id NUMBER) RETURN NUMBER IS
salary NUMBER;
BEGIN
SELECT salary
INTO salary
FROM employees
WHERE employee_id = emp_id;
RETURN salary;
END;
/18. Function to Check if Number is Prime
CREATE OR REPLACE FUNCTION is_prime (n NUMBER) RETURN BOOLEAN IS
BEGIN
IF n < 2 THEN
RETURN FALSE;
END IF;
FOR i IN 2..FLOOR(SQRT(n)) LOOP
IF MOD(n, i) = 0 THEN
RETURN FALSE;
END IF;
END LOOP;
RETURN TRUE;
END;
/19. Function to Get Last Day of the Month
CREATE OR REPLACE FUNCTION get_last_day (input_date DATE) RETURN DATE IS
BEGIN
RETURN LAST_DAY(input_date);
END;
/20. Function to Get First Day of the Month
CREATE OR REPLACE FUNCTION get_first_day (input_date DATE) RETURN DATE IS
BEGIN
RETURN TRUNC(input_date, 'MM');
END;
/Mathematical and Conversion Functions
21. Function to Calculate Square Root
CREATE OR REPLACE FUNCTION calculate_square_root (n NUMBER) RETURN NUMBER IS
BEGIN
RETURN SQRT(n);
END;
/22. Function to Calculate Compound Interest
CREATE OR REPLACE FUNCTION calculate_compound_interest
(
principal NUMBER,
rate NUMBER,
time NUMBER
) RETURN NUMBER IS
BEGIN
RETURN principal * POWER((1 + rate / 100), time) - principal;
END;
/23. Function to Get Employee's Manager Name
CREATE OR REPLACE FUNCTION get_manager_name (emp_id NUMBER) RETURN VARCHAR2 IS
manager_id NUMBER;
manager_name VARCHAR2(100);
BEGIN
SELECT manager_id
INTO manager_id
FROM employees
WHERE employee_id = emp_id;
SELECT first_name || ' ' || last_name
INTO manager_name
FROM employees
WHERE employee_id = manager_id;
RETURN manager_name;
END;
/24. Function to Convert Number to String
CREATE OR REPLACE FUNCTION number_to_string (n NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(n);
END;
/25. Function to Convert String to Number
CREATE OR REPLACE FUNCTION string_to_number (input_str VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN TO_NUMBER(input_str);
END;
/26. Function to Check Leap Year
CREATE OR REPLACE FUNCTION is_leap_year (year NUMBER) RETURN BOOLEAN IS
BEGIN
RETURN MOD(year, 4) = 0
AND (MOD(year, 100) != 0 OR MOD(year, 400) = 0);
END;
/27. Function to Get Employee Job Title
CREATE OR REPLACE FUNCTION get_job_title (emp_id NUMBER) RETURN VARCHAR2 IS
job_title VARCHAR2(100);
BEGIN
SELECT job_id
INTO job_title
FROM employees
WHERE employee_id = emp_id;
RETURN job_title;
END;
/28. Function to Calculate Gross Salary
CREATE OR REPLACE FUNCTION calculate_gross_salary
(
salary NUMBER,
bonus NUMBER
) RETURN NUMBER IS
BEGIN
RETURN salary + bonus;
END;
/29. Function to Calculate Net Salary
CREATE OR REPLACE FUNCTION calculate_net_salary
(
gross_salary NUMBER,
tax NUMBER
) RETURN NUMBER IS
BEGIN
RETURN gross_salary - tax;
END;
/30. Function to Check if String Contains a Substring
CREATE OR REPLACE FUNCTION contains_substring
(
main_str VARCHAR2,
sub_str VARCHAR2
) RETURN BOOLEAN IS
BEGIN
RETURN INSTR(main_str, sub_str) > 0;
END;
/Advanced PL/SQL Function Examples
31. Function to Get Employee Age
CREATE OR REPLACE FUNCTION get_employee_age (emp_id NUMBER) RETURN NUMBER IS
dob DATE;
BEGIN
SELECT date_of_birth INTO dob
FROM employees
WHERE employee_id = emp_id;
RETURN calculate_age(dob);
END;
/32. Function to Calculate Length of String
CREATE OR REPLACE FUNCTION string_length (input_str VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN LENGTH(input_str);
END;
/33. Function to Find Greatest of Three Numbers
CREATE OR REPLACE FUNCTION greatest_of_three
(
a NUMBER,
b NUMBER,
c NUMBER
) RETURN NUMBER IS
BEGIN
RETURN GREATEST(a, b, c);
END;
/34. Function to Find Least of Three Numbers
CREATE OR REPLACE FUNCTION least_of_three
(
a NUMBER,
b NUMBER,
c NUMBER
) RETURN NUMBER IS
BEGIN
RETURN LEAST(a, b, c);
END;
/35. Function to Convert Number to Binary
CREATE OR REPLACE FUNCTION number_to_binary (n NUMBER) RETURN VARCHAR2 IS
binary_str VARCHAR2(100);
v_num NUMBER := n;
BEGIN
IF v_num = 0 THEN
RETURN '0';
END IF;
WHILE v_num > 0 LOOP
binary_str := MOD(v_num, 2) || binary_str;
v_num := FLOOR(v_num / 2);
END LOOP;
RETURN binary_str;
END;
/36. Function to Convert Binary to Number
CREATE OR REPLACE FUNCTION binary_to_number (binary_str VARCHAR2) RETURN NUMBER IS
result NUMBER := 0;
BEGIN
FOR i IN 1..LENGTH(binary_str) LOOP
result := result * 2 + TO_NUMBER(SUBSTR(binary_str, i, 1));
END LOOP;
RETURN result;
END;
/37. Function to Get Department Manager Name
CREATE OR REPLACE FUNCTION get_dept_manager_name (dept_id NUMBER) RETURN VARCHAR2 IS
manager_id NUMBER;
manager_name VARCHAR2(100);
BEGIN
SELECT manager_id INTO manager_id
FROM departments
WHERE department_id = dept_id;
SELECT first_name || ' ' || last_name INTO manager_name
FROM employees
WHERE employee_id = manager_id;
RETURN manager_name;
END;
/38. Function to Count Number of Departments
CREATE OR REPLACE FUNCTION count_departments RETURN NUMBER IS
dept_count NUMBER;
BEGIN
SELECT COUNT(*) INTO dept_count FROM departments;
RETURN dept_count;
END;
/39. Function to Check if a String is Palindrome
CREATE OR REPLACE FUNCTION is_palindrome (input_str VARCHAR2) RETURN BOOLEAN IS
BEGIN
RETURN input_str = reverse_string(input_str);
END;
/40. Function to Calculate Employee's Years of Service
CREATE OR REPLACE FUNCTION years_of_service (emp_id NUMBER) RETURN NUMBER IS
hire_date DATE;
BEGIN
SELECT hire_date INTO hire_date
FROM employees
WHERE employee_id = emp_id;
RETURN FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date) / 12);
END;
/Conversion and Employee Utility Functions
41. Function to Convert Number to Hexadecimal
CREATE OR REPLACE FUNCTION number_to_hexadecimal (n NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(n, 'XXXX');
END;
/42. Function to Convert Hexadecimal to Number
CREATE OR REPLACE FUNCTION hexadecimal_to_number (hex_str VARCHAR2) RETURN NUMBER IS
BEGIN
RETURN TO_NUMBER(hex_str, 'XXXX');
END;
/43. Function to Get Employee Hire Date
CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN DATE IS
hire_date DATE;
BEGIN
SELECT hire_date INTO hire_date
FROM employees
WHERE employee_id = emp_id;
RETURN hire_date;
END;
/44. Function to Calculate Fibonacci Series up to N Terms
CREATE OR REPLACE FUNCTION fibonacci (n NUMBER) RETURN VARCHAR2 IS
a NUMBER := 0;
b NUMBER := 1;
c NUMBER;
fib_series VARCHAR2(4000) := '0';
BEGIN
FOR i IN 2..n LOOP
c := a + b;
a := b;
b := c;
fib_series := fib_series || ', ' || TO_CHAR(c);
END LOOP;
RETURN fib_series;
END;
/45. Function to Check if Employee Exists
CREATE OR REPLACE FUNCTION employee_exists (emp_id NUMBER) RETURN BOOLEAN IS
emp_count NUMBER;
BEGIN
SELECT COUNT(*) INTO emp_count
FROM employees
WHERE employee_id = emp_id;
RETURN emp_count > 0;
END;
/46. Function to Calculate Sum of Digits
CREATE OR REPLACE FUNCTION sum_of_digits (n NUMBER) RETURN NUMBER IS
sum_value NUMBER := 0;
v_num NUMBER := n;
BEGIN
WHILE v_num > 0 LOOP
sum_value := sum_value + MOD(v_num, 10);
v_num := FLOOR(v_num / 10);
END LOOP;
RETURN sum_value;
END;
/47. Function to Get Employee Email
CREATE OR REPLACE FUNCTION get_email (emp_id NUMBER) RETURN VARCHAR2 IS
email VARCHAR2(100);
BEGIN
SELECT email INTO email
FROM employees
WHERE employee_id = emp_id;
RETURN email;
END;
/48. Function to Check if a Year is a Leap Year
CREATE OR REPLACE FUNCTION check_leap_year (year NUMBER) RETURN BOOLEAN IS
BEGIN
RETURN MOD(year, 4) = 0
AND (MOD(year, 100) != 0 OR MOD(year, 400) = 0);
END;
/49. Function to Find Greatest Common Divisor (GCD)
CREATE OR REPLACE FUNCTION gcd (a NUMBER, b NUMBER) RETURN NUMBER IS
x NUMBER := ABS(a);
y NUMBER := ABS(b);
temp NUMBER;
BEGIN
WHILE y != 0 LOOP
temp := y;
y := MOD(x, y);
x := temp;
END LOOP;
RETURN x;
END;
/50. Function to Calculate Least Common Multiple (LCM)
CREATE OR REPLACE FUNCTION lcm (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN ABS(a * b) / gcd(a, b);
END;
/Employee and Business Functions
51. Function to Get Employee's Department Name
CREATE OR REPLACE FUNCTION get_emp_department_name (emp_id NUMBER) RETURN VARCHAR2 IS
dept_name VARCHAR2(100);
BEGIN
SELECT d.department_name
INTO dept_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.employee_id = emp_id;
RETURN dept_name;
END;
/52. Function to Calculate Sum of Natural Numbers
CREATE OR REPLACE FUNCTION sum_of_natural_numbers (n NUMBER) RETURN NUMBER IS
BEGIN
RETURN (n * (n + 1)) / 2;
END;
/53. Function to Get Employee's Job Title
CREATE OR REPLACE FUNCTION get_emp_job_title (emp_id NUMBER) RETURN VARCHAR2 IS
job_title VARCHAR2(100);
BEGIN
SELECT job_id INTO job_title
FROM employees
WHERE employee_id = emp_id;
RETURN job_title;
END;
/54. Function to Convert Decimal to Binary
CREATE OR REPLACE FUNCTION decimal_to_binary (n NUMBER) RETURN VARCHAR2 IS
binary_str VARCHAR2(100);
v_num NUMBER := n;
BEGIN
IF v_num = 0 THEN
RETURN '0';
END IF;
WHILE v_num > 0 LOOP
binary_str := MOD(v_num, 2) || binary_str;
v_num := FLOOR(v_num / 2);
END LOOP;
RETURN binary_str;
END;
/55. Function to Calculate Employee Bonus
CREATE OR REPLACE FUNCTION calculate_employee_bonus (emp_id NUMBER) RETURN NUMBER IS
salary NUMBER;
bonus NUMBER;
BEGIN
SELECT salary INTO salary
FROM employees
WHERE employee_id = emp_id;
bonus := salary * 0.1;
RETURN bonus;
END;
/56. Function to Convert Binary to Decimal
CREATE OR REPLACE FUNCTION binary_to_decimal (binary_str VARCHAR2) RETURN NUMBER IS
result NUMBER := 0;
BEGIN
FOR i IN 1..LENGTH(binary_str) LOOP
result := result * 2 + TO_NUMBER(SUBSTR(binary_str, i, 1));
END LOOP;
RETURN result;
END;
/57. Function to Calculate Employee's Retirement Age
CREATE OR REPLACE FUNCTION retirement_age (emp_id NUMBER) RETURN NUMBER IS
dob DATE;
BEGIN
SELECT date_of_birth INTO dob
FROM employees
WHERE employee_id = emp_id;
RETURN 65 - calculate_age(dob);
END;
/58. Function to Get Employee's Contact Information
CREATE OR REPLACE FUNCTION get_contact_info (emp_id NUMBER) RETURN VARCHAR2 IS
phone VARCHAR2(20);
email VARCHAR2(100);
BEGIN
SELECT phone_number, email
INTO phone, email
FROM employees
WHERE employee_id = emp_id;
RETURN 'Phone: ' || phone || ', Email: ' || email;
END;
/59. Function to Calculate Employee's Total Compensation
CREATE OR REPLACE FUNCTION total_compensation (emp_id NUMBER) RETURN NUMBER IS
salary NUMBER;
bonus NUMBER;
BEGIN
SELECT salary INTO salary
FROM employees
WHERE employee_id = emp_id;
bonus := calculate_employee_bonus(emp_id);
RETURN salary + bonus;
END;
/60. Function to Convert Fahrenheit to Celsius
CREATE OR REPLACE FUNCTION fahrenheit_to_celsius (fahrenheit NUMBER) RETURN NUMBER IS
BEGIN
RETURN (fahrenheit - 32) * 5/9;
END;
/Date, Service and Utility Functions
61. Function to Get Employee's Address
CREATE OR REPLACE FUNCTION get_address (emp_id NUMBER) RETURN VARCHAR2 IS
address VARCHAR2(200);
BEGIN
SELECT address INTO address
FROM employees
WHERE employee_id = emp_id;
RETURN address;
END;
/62. Function to Calculate Length of Service
CREATE OR REPLACE FUNCTION length_of_service (emp_id NUMBER) RETURN NUMBER IS
hire_date DATE;
BEGIN
SELECT hire_date INTO hire_date
FROM employees
WHERE employee_id = emp_id;
RETURN FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date) / 12);
END;
/63. Function to Convert Date to Julian Day
CREATE OR REPLACE FUNCTION date_to_julian (input_date DATE) RETURN NUMBER IS
BEGIN
RETURN TO_NUMBER(TO_CHAR(input_date, 'J'));
END;
/64. Function to Calculate Simple Average
CREATE OR REPLACE FUNCTION simple_average (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN (a + b) / 2;
END;
/65. Function to Get Employee's Job Location
CREATE OR REPLACE FUNCTION get_job_location (emp_id NUMBER) RETURN VARCHAR2 IS
location VARCHAR2(100);
BEGIN
SELECT location_id INTO location
FROM employees
WHERE employee_id = emp_id;
RETURN location;
END;
/66. Function to Calculate Employee's Age Based on Hire Date
CREATE OR REPLACE FUNCTION age_based_on_hire_date (emp_id NUMBER) RETURN NUMBER IS
hire_date DATE;
BEGIN
SELECT hire_date INTO hire_date
FROM employees
WHERE employee_id = emp_id;
RETURN calculate_age(hire_date);
END;
/67. Function to Get Day of the Week
CREATE OR REPLACE FUNCTION day_of_week (input_date DATE) RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(input_date, 'DAY');
END;
/68. Function to Calculate Distance Between Two Points
CREATE OR REPLACE FUNCTION calculate_distance
(
x1 NUMBER,
y1 NUMBER,
x2 NUMBER,
y2 NUMBER
) RETURN NUMBER IS
BEGIN
RETURN SQRT(POWER(x2 - x1, 2) + POWER(y2 - y1, 2));
END;
/69. Function to Get Employee's Manager ID
CREATE OR REPLACE FUNCTION get_manager_id (emp_id NUMBER) RETURN NUMBER IS
manager_id NUMBER;
BEGIN
SELECT manager_id INTO manager_id
FROM employees
WHERE employee_id = emp_id;
RETURN manager_id;
END;
/70. Function to Calculate Number of Days Between Two Dates
CREATE OR REPLACE FUNCTION days_between_dates
(
date1 DATE,
date2 DATE
) RETURN NUMBER IS
BEGIN
RETURN date2 - date1;
END;
/Additional Conversion Functions
71. Function to Convert Kilometers to Miles
CREATE OR REPLACE FUNCTION kilometers_to_miles (kilometers NUMBER) RETURN NUMBER IS
BEGIN
RETURN kilometers * 0.621371;
END;
/72. Function to Get Employee's Birth Year
CREATE OR REPLACE FUNCTION get_birth_year (emp_id NUMBER) RETURN NUMBER IS
dob DATE;
BEGIN
SELECT date_of_birth INTO dob
FROM employees
WHERE employee_id = emp_id;
RETURN EXTRACT(YEAR FROM dob);
END;
/73. Function to Calculate Employee's Service Length in Months
CREATE OR REPLACE FUNCTION service_length_in_months (emp_id NUMBER) RETURN NUMBER IS
hire_date DATE;
BEGIN
SELECT hire_date INTO hire_date
FROM employees
WHERE employee_id = emp_id;
RETURN MONTHS_BETWEEN(SYSDATE, hire_date);
END;
/74. Function to Convert Miles to Kilometers
CREATE OR REPLACE FUNCTION miles_to_kilometers (miles NUMBER) RETURN NUMBER IS
BEGIN
RETURN miles * 1.60934;
END;
/75. Function to Calculate Employee's Service Length in Days
CREATE OR REPLACE FUNCTION service_length_in_days (emp_id NUMBER) RETURN NUMBER IS
hire_date DATE;
BEGIN
SELECT hire_date INTO hire_date
FROM employees
WHERE employee_id = emp_id;
RETURN SYSDATE - hire_date;
END;
/76. Function to Convert Number to Roman Numerals
CREATE OR REPLACE FUNCTION number_to_roman (n NUMBER) RETURN VARCHAR2 IS
roman_str VARCHAR2(100) := '';
v_num NUMBER := n;
BEGIN
FOR rec IN (
SELECT 1000 val, 'M' symb, 1 seq FROM dual UNION ALL
SELECT 900, 'CM', 2 FROM dual UNION ALL
SELECT 500, 'D', 3 FROM dual UNION ALL
SELECT 400, 'CD', 4 FROM dual UNION ALL
SELECT 100, 'C', 5 FROM dual UNION ALL
SELECT 90, 'XC', 6 FROM dual UNION ALL
SELECT 50, 'L', 7 FROM dual UNION ALL
SELECT 40, 'XL', 8 FROM dual UNION ALL
SELECT 10, 'X', 9 FROM dual UNION ALL
SELECT 9, 'IX', 10 FROM dual UNION ALL
SELECT 5, 'V', 11 FROM dual UNION ALL
SELECT 4, 'IV', 12 FROM dual UNION ALL
SELECT 1, 'I', 13 FROM dual
ORDER BY seq
) LOOP
WHILE v_num >= rec.val LOOP
roman_str := roman_str || rec.symb;
v_num := v_num - rec.val;
END LOOP;
END LOOP;
RETURN roman_str;
END;
/77. Function to Calculate Employee's Total Experience
CREATE OR REPLACE FUNCTION total_experience (emp_id NUMBER) RETURN NUMBER IS
hire_date DATE;
BEGIN
SELECT hire_date INTO hire_date
FROM employees
WHERE employee_id = emp_id;
RETURN FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date) / 12);
END;
/78. Function to Get Employee's Last Name
CREATE OR REPLACE FUNCTION get_last_name (emp_id NUMBER) RETURN VARCHAR2 IS
last_name VARCHAR2(50);
BEGIN
SELECT last_name INTO last_name
FROM employees
WHERE employee_id = emp_id;
RETURN last_name;
END;
/79. Function to Convert Time from 24-Hour to 12-Hour Format
CREATE OR REPLACE FUNCTION time_24_to_12 (time24 VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(TO_DATE(time24, 'HH24:MI'), 'HH:MI AM');
END;
/80. Function to Get Employee's First Name
CREATE OR REPLACE FUNCTION get_first_name (emp_id NUMBER) RETURN VARCHAR2 IS
first_name VARCHAR2(50);
BEGIN
SELECT first_name INTO first_name
FROM employees
WHERE employee_id = emp_id;
RETURN first_name;
END;
/Date, Time and Financial Functions
81. Function to Convert Time from 12-Hour to 24-Hour Format
CREATE OR REPLACE FUNCTION time_12_to_24 (time12 VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(TO_DATE(time12, 'HH:MI AM'), 'HH24:MI');
END;
/82. Function to Calculate Number of Working Days Between Two Dates
CREATE OR REPLACE FUNCTION working_days
(
start_date DATE,
end_date DATE
) RETURN NUMBER IS
workdays NUMBER := 0;
current_date_value DATE := start_date;
BEGIN
WHILE current_date_value <= end_date LOOP
IF TO_CHAR(current_date_value, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')
NOT IN ('SAT', 'SUN') THEN
workdays := workdays + 1;
END IF;
current_date_value := current_date_value + 1;
END LOOP;
RETURN workdays;
END;
/83. Function to Calculate Number of Weekends Between Two Dates
CREATE OR REPLACE FUNCTION weekends
(
start_date DATE,
end_date DATE
) RETURN NUMBER IS
weekend_count NUMBER := 0;
current_date_value DATE := start_date;
BEGIN
WHILE current_date_value <= end_date LOOP
IF TO_CHAR(current_date_value, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')
IN ('SAT', 'SUN') THEN
weekend_count := weekend_count + 1;
END IF;
current_date_value := current_date_value + 1;
END LOOP;
RETURN weekend_count;
END;
/84. Function to Calculate Monthly Installment for Loan
CREATE OR REPLACE FUNCTION calculate_installment
(
principal NUMBER,
rate NUMBER,
time NUMBER
) RETURN NUMBER IS
monthly_rate NUMBER := rate / (12 * 100);
numerator NUMBER;
denominator NUMBER;
BEGIN
numerator :=
principal * monthly_rate * POWER((1 + monthly_rate), time);
denominator :=
POWER((1 + monthly_rate), time) - 1;
RETURN numerator / denominator;
END;
/85. Function to Get Employee's Job Grade
CREATE OR REPLACE FUNCTION get_job_grade (emp_id NUMBER) RETURN VARCHAR2 IS
grade VARCHAR2(2);
BEGIN
SELECT job_grade INTO grade
FROM employees
WHERE employee_id = emp_id;
RETURN grade;
END;
/86. Function to Convert String to Title Case
CREATE OR REPLACE FUNCTION to_title_case (input_str VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN INITCAP(input_str);
END;
/87. Function to Get Employee's Bonus Percentage
CREATE OR REPLACE FUNCTION get_bonus_percentage (emp_id NUMBER) RETURN NUMBER IS
bonus_pct NUMBER;
BEGIN
SELECT bonus_pct INTO bonus_pct
FROM employees
WHERE employee_id = emp_id;
RETURN bonus_pct;
END;
/88. Function to Get Employee's Department ID
CREATE OR REPLACE FUNCTION get_department_id (emp_id NUMBER) RETURN NUMBER IS
dept_id NUMBER;
BEGIN
SELECT department_id INTO dept_id
FROM employees
WHERE employee_id = emp_id;
RETURN dept_id;
END;
/89. Function to Calculate Number of Hours Between Two Dates
CREATE OR REPLACE FUNCTION hours_between_dates
(
date1 DATE,
date2 DATE
) RETURN NUMBER IS
BEGIN
RETURN (date2 - date1) * 24;
END;
/90. Function to Get Employee's Location ID
CREATE OR REPLACE FUNCTION get_location_id (emp_id NUMBER) RETURN NUMBER IS
location_id NUMBER;
BEGIN
SELECT location_id INTO location_id
FROM employees
WHERE employee_id = emp_id;
RETURN location_id;
END;
/Additional Utility Functions
91. Function to Convert Days to Hours
CREATE OR REPLACE FUNCTION days_to_hours (days NUMBER) RETURN NUMBER IS
BEGIN
RETURN days * 24;
END;
/92. Function to Convert Hours to Minutes
CREATE OR REPLACE FUNCTION hours_to_minutes (hours NUMBER) RETURN NUMBER IS
BEGIN
RETURN hours * 60;
END;
/93. Function to Get Employee's Commission Percentage
CREATE OR REPLACE FUNCTION get_commission_pct (emp_id NUMBER) RETURN NUMBER IS
commission_pct NUMBER;
BEGIN
SELECT commission_pct INTO commission_pct
FROM employees
WHERE employee_id = emp_id;
RETURN commission_pct;
END;
/94. Function to Convert Minutes to Seconds
CREATE OR REPLACE FUNCTION minutes_to_seconds (minutes NUMBER) RETURN NUMBER IS
BEGIN
RETURN minutes * 60;
END;
/95. Function to Get Employee's Project Name
CREATE OR REPLACE FUNCTION get_project_name (emp_id NUMBER) RETURN VARCHAR2 IS
project_name VARCHAR2(100);
BEGIN
SELECT project_name INTO project_name
FROM projects
WHERE employee_id = emp_id;
RETURN project_name;
END;
/96. Function to Calculate Number of Weeks Between Two Dates
CREATE OR REPLACE FUNCTION weeks_between_dates
(
date1 DATE,
date2 DATE
) RETURN NUMBER IS
BEGIN
RETURN FLOOR((date2 - date1) / 7);
END;
/97. Function to Get Employee's Performance Rating
CREATE OR REPLACE FUNCTION get_performance_rating (emp_id NUMBER) RETURN VARCHAR2 IS
rating VARCHAR2(10);
BEGIN
SELECT performance_rating INTO rating
FROM employees
WHERE employee_id = emp_id;
RETURN rating;
END;
/98. Function to Convert Number to Octal
CREATE OR REPLACE FUNCTION number_to_octal (n NUMBER) RETURN VARCHAR2 IS
octal_str VARCHAR2(100);
v_num NUMBER := n;
BEGIN
IF v_num = 0 THEN
RETURN '0';
END IF;
WHILE v_num > 0 LOOP
octal_str := MOD(v_num, 8) || octal_str;
v_num := FLOOR(v_num / 8);
END LOOP;
RETURN octal_str;
END;
/99. Function to Convert Octal to Number
CREATE OR REPLACE FUNCTION octal_to_number (octal_str VARCHAR2) RETURN NUMBER IS
result NUMBER := 0;
BEGIN
FOR i IN 1..LENGTH(octal_str) LOOP
result := result * 8 + TO_NUMBER(SUBSTR(octal_str, i, 1));
END LOOP;
RETURN result;
END;
/100. Function to Calculate Number of Months Between Two Dates
CREATE OR REPLACE FUNCTION months_between_dates
(
date1 DATE,
date2 DATE
) RETURN NUMBER IS
BEGIN
RETURN MONTHS_BETWEEN(date2, date1);
END;
/Note: These examples are intended for learning Oracle PL/SQL functions. Functions that query tables such as employees, departments, and projects assume that the referenced tables and columns exist in your schema.
No comments:
Post a Comment