In Oracle, the CREATE FUNCTION statement is used to create a new user-defined function. A function is a named block of code that can accept parameters, perform some operations, and return a result. Here's the basic syntax for creating a function in Oracle:
CREATE [OR REPLACE] FUNCTION function_name
(parameter1 [IN | OUT | IN OUT] data_type [, parameter2 ...])
RETURN return_data_type
IS
[local_variable_declarations]
BEGIN
-- function body
RETURN return_value;
END;
You must have define some parametrs before creating a procedure or a function. These parameters are
# IN: It is a default parameter. It passes the value to the subprogram.
# OUT: It must be specified. It returns a value to the caller.
# IN OUT: It must be specified. It passes an initial value to the subprogram and returns an updated value to the caller.
here's an example of a simple function in Oracle that takes a string as input and returns the number of vowels in the string:
CREATE FUNCTION count_vowels(str IN VARCHAR2) RETURN NUMBER
IS
vowels NUMBER := 0;
BEGIN
FOR i IN 1..LENGTH(str) LOOP
IF UPPER(SUBSTR(str, i, 1)) IN ('A', 'E', 'I', 'O', 'U') THEN
vowels := vowels + 1;
END IF;
END LOOP;
RETURN vowels;
END;
Let's break down this function step by step:
- The CREATE FUNCTION statement begins the function creation process. In this case, we're creating a function named count_vowels that takes a single input parameter, str, which is a VARCHAR2 data type. The function will return a single number that represents the number of vowels in the input string.
- The IS keyword begins the function body. This is where we'll write the actual code that performs the desired operation.
- We initialize a variable named vowels to 0. This variable will keep track of the number of vowels in the input string.
- We use a FOR loop to iterate over each character in the input string. The LOOP keyword signals the beginning of the loop, and END LOOP signals the end. We use the LENGTH function to determine the length of the input string, and the SUBSTR function to extract individual characters from the string.
- For each character in the string, we check if it's a vowel. We use the UPPER function to convert the character to uppercase, and then check if it's equal to any of the vowels ('A', 'E', 'I', 'O', 'U'). If it is, we increment the vowels variable by 1.
- Finally, we use the RETURN keyword to return the final value of the vowels variable, which represents the number of vowels in the input string.
Once this function has been created, we can use it like any other function in SQL. For example:
SELECT count_vowels('Hello world') FROM dual;
This query would return the value 3, since there are three vowels ('e', 'o', and 'o') in the input string 'Hello world'.
