1. Simple Function: Returns a constant value.
CREATE OR REPLACE FUNCTION get_pi
RETURN NUMBER
IS
BEGIN
RETURN 3.14159;
END;
2. Addition Function: Adds two numbers.
CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER)
RETURN NUMBER IS
BEGIN
RETURN a + b;
END;
3. String Length: Returns the length of a string.
CREATE OR REPLACE FUNCTION string_length(str VARCHAR2)
RETURN NUMBER
IS
BEGIN
RETURN LENGTH(str);
END;
4. Concatenate Strings: Concatenates two strings.
CREATE OR REPLACE FUNCTION concat_strings(str1 VARCHAR2, str2 VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN str1 || str2;
END;
5. Check Even/Odd: Checks if a number is even or odd.
CREATE OR REPLACE FUNCTION is_even(num NUMBER)
RETURN BOOLEAN
IS
BEGIN
RETURN MOD(num, 2) = 0;
END;
6. Factorial: Computes the factorial of a number.
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;
7. Square Root: Returns the square root of a number.
CREATE OR REPLACE FUNCTION square_root(num NUMBER) RETURN NUMBER IS
BEGIN
RETURN SQRT(num);
END;
8. To Upper Case: Converts a string to uppercase.
CREATE OR REPLACE FUNCTION to_upper_case(str VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN UPPER(str);
END;
9. To Lower Case: Converts a string to lowercase.
CREATE OR REPLACE FUNCTION to_lower_case(str VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN LOWER(str);
END;
10. Substring: Extracts a substring from a string.
CREATE OR REPLACE FUNCTION get_substring(str VARCHAR2, start_pos NUMBER, length NUMBER)
RETURN VARCHAR2
IS
BEGIN
RETURN SUBSTR(str, start_pos, length);
END;
11. Reverse String: Reverses a string.
CREATE OR REPLACE FUNCTION reverse_string(str VARCHAR2)
RETURN VARCHAR2
IS
reversed_str VARCHAR2(4000);
BEGIN
FOR i IN REVERSE 1..LENGTH(str) LOOP
reversed_str := reversed_str || SUBSTR(str, i, 1);
END LOOP;
RETURN reversed_str;
END;
12. Generate Random Number: Generates a random number between a specified range.
CREATE
OR
REPLACE
FUNCTION
generate_random(min
NUMBER,
max
NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN DBMS_RANDOM.VALUE(min, max);
END;
13. Check Prime: Checks if a number is a prime.
CREATE OR REPLACE FUNCTION is_prime(num NUMBER)
RETURN BOOLEAN
IS
i NUMBER;
BEGIN
IF num <= 1 THEN
RETURN FALSE;
END IF;
FOR i IN 2..FLOOR(SQRT(num)) LOOP
IF MOD(num, i) = 0 THEN
RETURN FALSE;
END IF;
END LOOP;
RETURN TRUE;
END;
14. Format Date: Formats a date to a specified format.
CREATE OR REPLACE FUNCTION format_date(date_in DATE, format_str VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(date_in, format_str);
END;
15. Calculate Age: Calculates the age from a birthdate.
CREATE OR REPLACE FUNCTION calculate_age(birthdate DATE)
RETURN NUMBER
IS
BEGIN
RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, birthdate) / 12);
END;
16. Check Null: Checks if a value is null.
CREATE OR REPLACE FUNCTION is_null(val IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
RETURN val IS NULL;
END;
17. Calculate Discount: Calculates discount on a price.
CREATE OR REPLACE FUNCTION calculate_discount(price NUMBER, discount_rate NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN price * (1 - discount_rate / 100);
END;
18. Get Max Value: Returns the maximum of two numbers.
CREATE OR REPLACE FUNCTION get_max(a NUMBER, b NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN CASE WHEN a > b THEN a ELSE b END;
END;
19. Get Min Value: Returns the minimum of two numbers.
CREATE OR REPLACE FUNCTION get_min(a NUMBER, b NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN CASE WHEN a < b THEN a ELSE b END;
END;
20. Concatenate with Separator: Concatenates a list of strings with a separator.
CREATE OR REPLACE FUNCTION concat_with_separator(separator VARCHAR2,str_list SYS.ODCIVARCHAR2LIST)
RETURN VARCHAR2
IS
result VARCHAR2(4000);
BEGIN
FOR i IN 1..str_list.COUNT LOOP
IF i > 1 THEN
result := result || separator;
END IF;
result := result || str_list(i);
END LOOP;
RETURN result;
END;
21. Calculate Rectangle Area: Calculates the area of a rectangle.
CREATE OR REPLACE FUNCTION rectangle_area(length NUMBER, width NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN length * width;
END;
22. Calculate Circle Area: Calculates the area of a circle.
CREATE OR REPLACE FUNCTION circle_area(radius NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN 3.14159 * radius * radius;
END;
23. Get Employee Salary: Returns the salary of an employee.
CREATE OR REPLACE FUNCTION get_employee_salary(emp_id NUMBER)
RETURN NUMBER
IS
salary NUMBER;
BEGIN
SELECT salary INTO salary FROM employees WHERE employee_id = emp_id;
RETURN salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
24. Convert to Number: Converts a string to a number.
CREATE OR REPLACE FUNCTION to_number_conversion(str VARCHAR2)
RETURN NUMBER
IS
BEGIN
RETURN TO_NUMBER(str);
END;
25. Get Employee Full Name: Returns the full name of an employee.
CREATE OR REPLACE FUNCTION get_employee_full_name(emp_id NUMBER)
RETURN VARCHAR2
IS
full_name VARCHAR2(100);
BEGIN
SELECT first_name || ' ' || last_name INTO full_name FROM employees WHERE employee_id = emp_id;
RETURN full_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
26. Calculate Simple Interest: Calculates simple interest.
CREATE OR REPLACE FUNCTION simple_interest(principal NUMBER, rate NUMBER, time NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (principal * rate * time) / 100;
END;
27. Validate Email: Validates if an email is in correct format.
CREATE OR REPLACE FUNCTION validate_email(email VARCHAR2)
RETURN BOOLEAN IS
BEGIN
RETURN REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');
END;
28. Count Words: Counts the number of words in a string.
CREATE OR REPLACE FUNCTION count_words(str VARCHAR2) RETURN NUMBER IS
words NUMBER;
BEGIN
words := LENGTH(REGEXP_REPLACE(str, '[^ ]+', NULL)) + 1;
RETURN words;
END;
29. Get Maximum Salary: Returns the maximum salary from the employees table.
CREATE OR REPLACE FUNCTION get_max_salary RETURN NUMBER IS
max_salary NUMBER;
BEGIN
SELECT MAX(salary) INTO max_salary FROM employees;
RETURN max_salary;
END;
30. Get Department Name: Returns the department name from a department ID.
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;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
31. Convert Date to Timestamp: Converts a date to a timestamp.
CREATE OR REPLACE FUNCTION date_to_timestamp(date_in
DATE) RETURN TIMESTAMP IS
BEGIN
RETURN CAST(date_in AS TIMESTAMP);
END;
32. Get Product Price: Returns the price of a product.
CREATE OR REPLACE FUNCTION get_product_price(prod_id NUMBER) RETURN NUMBER IS
price NUMBER;
BEGIN
SELECT price INTO price FROM products WHERE product_id = prod_id;
RETURN price;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
33. Get Total Order Amount: Calculates the total amount of an order.
CREATE OR REPLACE FUNCTION get_order_amount(order_id NUMBER) RETURN NUMBER IS
total_amount NUMBER;
BEGIN
SELECT SUM(quantity * unit_price) INTO total_amount FROM order_items WHERE order_id = order_id;
RETURN total_amount;
END;
34. Check Valid Date: Checks if a date is valid.
CREATE OR REPLACE FUNCTION is_valid_date(date_str VARCHAR2) RETURN BOOLEAN IS
BEGIN
RETURN REGEXP_LIKE(date_str, '^\d{4}-\d{2}-\d{2}$');
END;
35. Calculate Age from DOB: Calculates age from date of birth.
CREATE OR REPLACE FUNCTION calculate_age_from_dob(dob DATE) RETURN NUMBER IS
BEGIN
RETURN FLOOR(MONTHS_BETWEEN(SYSDATE, dob) / 12);
END;
36. Convert Temperature: Converts temperature from Celsius to Fahrenheit.
CREATE OR REPLACE FUNCTION celsius_to_fahrenheit(celsius NUMBER) RETURN NUMBER IS
BEGIN
RETURN (celsius * 9/5) + 32;
END;
37. Check Leap Year: Checks if a year is a 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;
38. Find Maximum in Array: Finds the maximum value in an array.
CREATE OR REPLACE FUNCTION find_max(arr SYS.ODCINUMBERLIST) RETURN NUMBER IS
max_val NUMBER;
BEGIN
max_val := arr(1);
FOR i IN 2..arr.COUNT LOOP
IF arr(i) > max_val THEN
max_val := arr(i);
END IF;
END LOOP;
RETURN max_val;
END;
39. Generate UUID: Generates a UUID.
CREATE OR REPLACE FUNCTION generate_uuid RETURN VARCHAR2 IS
BEGIN
RETURN SYS_GUID();
END;
40. Calculate Compound Interest: Calculates compound interest.
CREATE OR REPLACE FUNCTION compound_interest(principal NUMBER, rate NUMBER, times NUMBER, periods NUMBER) RETURN NUMBER IS
BEGIN
RETURN principal * POWER((1 + rate / times), times * periods);
END;
41. Check Valid Phone Number: Checks if a phone number is valid.
CREATE OR REPLACE FUNCTION is_valid_phone(phone VARCHAR2) RETURN BOOLEAN IS
BEGIN
RETURN REGEXP_LIKE(phone, '^\d{10}$');
END;
42. Get Current Timestamp: Returns the current timestamp.
CREATE OR REPLACE FUNCTION get_current_timestamp RETURN TIMESTAMP IS
BEGIN
RETURN SYSTIMESTAMP;
END;
43. Format Phone Number: Formats a phone number.
CREATE OR REPLACE FUNCTION format_phone(phone VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN '(' || SUBSTR(phone, 1, 3) || ') ' || SUBSTR(phone, 4, 3) || '-' || SUBSTR(phone, 7);
END;
44. Calculate Body Mass Index (BMI): Calculates BMI from weight and height.
CREATE OR REPLACE FUNCTION calculate_bmi(weight NUMBER, height NUMBER) RETURN NUMBER IS
BEGIN
RETURN weight / (height * height);
END;
45. Get Last Login Date: Returns the last login date of a user.
CREATE OR REPLACE FUNCTION get_last_login(user_id NUMBER) RETURN DATE IS
last_login_date DATE;
BEGIN
SELECT last_login INTO last_login_date FROM users WHERE user_id = user_id;
RETURN last_login_date;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
46. Check Valid Username: Checks if a username is valid.
CREATE OR REPLACE FUNCTION is_valid_username(username VARCHAR2) RETURN BOOLEAN IS
BEGIN
RETURN REGEXP_LIKE(username, '^[a-zA-Z0-9_]{3,15}$');
END;
47. Calculate Total Price: Calculates the total price including tax.
CREATE OR REPLACE FUNCTION total_price(price NUMBER, tax_rate NUMBER) RETURN NUMBER IS
BEGIN
RETURN price * (1 + tax_rate / 100);
END;
48. Get Employee Department: Returns the department of an employee.
CREATE OR REPLACE FUNCTION get_employee_department(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;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
49. Calculate Days Between Dates: Calculates the number of days between two dates.
CREATE OR REPLACE FUNCTION days_between_dates(start_date DATE, end_date DATE) RETURN NUMBER IS
BEGIN
RETURN end_date - start_date;
END;
50. Convert String to Date: Converts a string to a date.
CREATE OR REPLACE FUNCTION string_to_date(date_str VARCHAR2, format_str VARCHAR2) RETURN DATE IS
BEGIN
RETURN TO_DATE(date_str, format_str);
END;
No comments:
Post a Comment