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;
/
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 Number
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;
/
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
i NUMBER;
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;
/
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 Employees 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;
/
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);
BEGIN
WHILE n > 0 LOOP
binary_str := MOD(n, 2) || binary_str;
n := FLOOR(n / 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;
i NUMBER;
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 Employees 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;
/
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 NUMBER := 0;
BEGIN
WHILE n > 0 LOOP
sum := sum + MOD(n, 10);
n := FLOOR(n / 10);
END LOOP;
RETURN sum;
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
BEGIN
WHILE b != 0 LOOP
a := a MOD b;
b := a + b;
a := b - a;
b := b - a;
END LOOP;
RETURN a;
END;
/
50. Function to Calculate Least Common Multiple (LCM)
CREATE OR REPLACE FUNCTION lcm (a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN (a * b) / gcd(a, b);
END;
/
51. Function to Get Employees 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 Employees 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);
BEGIN
WHILE n > 0 LOOP
binary_str := MOD(n, 2) || binary_str;
n := FLOOR(n / 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;
i NUMBER;
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 Employees 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 Employees 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 Employees 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 Temperature from Fahrenheit to Celsius
CREATE OR REPLACE FUNCTION fahrenheit_to_celsius (fahrenheit NUMBER) RETURN NUMBER IS
BEGIN
RETURN (fahrenheit - 32) * 5/9;
END;
/
61. Function to Get Employees 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 Employees 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 Employees 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 Employees 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;
/
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 Employees 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 Employees 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 Employees 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);
val NUMBER;
symb VARCHAR2(10);
BEGIN
FOR val, symb IN (SELECT 1000, 'M' FROM DUAL
UNION ALL
SELECT 900, 'CM' FROM DUAL
UNION ALL
SELECT 500, 'D' FROM DUAL
UNION ALL
SELECT 400, 'CD' FROM DUAL
UNION ALL
SELECT 100, 'C' FROM DUAL
UNION ALL
SELECT 90, 'XC' FROM DUAL
UNION ALL
SELECT 50, 'L' FROM DUAL
UNION ALL
SELECT 40, 'XL' FROM DUAL
UNION ALL
SELECT 10, 'X' FROM DUAL
UNION ALL
SELECT 9, 'IX' FROM DUAL
UNION ALL
SELECT 5, 'V' FROM DUAL
UNION ALL
SELECT 4, 'IV' FROM DUAL
UNION ALL
SELECT 1, 'I' FROM DUAL) LOOP
WHILE n >= val LOOP
roman_str := roman_str || symb;
n := n - val;
END LOOP;
END LOOP;
RETURN roman_str;
END;
/
77. Function to Calculate Employees 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 Employees 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 Employees 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;
/
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 DATE := start_date;
BEGIN
WHILE current_date <= end_date LOOP
IF TO_CHAR(current_date, 'DY') NOT IN ('SAT', 'SUN') THEN
workdays := workdays + 1;
END IF;
current_date := current_date + 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
weekends NUMBER := 0;
current_date DATE := start_date;
BEGIN
WHILE current_date <= end_date LOOP
IF TO_CHAR(current_date, 'DY') IN ('SAT', 'SUN') THEN
weekends := weekends + 1;
END IF;
current_date := current_date + 1;
END LOOP;
RETURN weekends;
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 := principal * monthly_rate * POWER((1 + monthly_rate), time);
denominator NUMBER := POWER((1 + monthly_rate), time) - 1;
BEGIN
RETURN numerator / denominator;
END;
/
85. Function to Get Employees 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 Employees 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 Employees 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 Employees 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;
/
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 Employees 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 Employees 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 Employees 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);
BEGIN
WHILE n > 0 LOOP
octal_str := MOD(n, 8) || octal_str;
n := FLOOR(n / 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;
i NUMBER;
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;
/
No comments:
Post a Comment