Functions

Functions are very handy in order to customize your own computation logic. Unlike procedures, they return a value and therefore might be used in a SELECT statement. Functions are sometimes referred to as UDF (stands for User Defined Functions).

Functions accept parameters as well as procedures and, of course, all three types: IN, OUT, INOUT.

Here is the template for functions:

CREATE [OR REPLACE] FUNCTION my_function [parameter_1, ... ]

   RETURN data_type

IS|AS -- there is no difference; choose one

   -- here you can declare variables, cursors, collections, ...

BEGIN
   
 -- here comes the body of the function where your logic is executed
 
  EXCEPTION    --- even though this one is not mandatory I recommend using it all the time
     WHEN ...   -- here you can define all types of exceptions
END; -- alternatively you can use "END my_function;"

To sum up, there are four main parts:

• CREATE [OR REPLACE] FUNCTION
  • here you can name your function and define parameters
  • use OR REPLACE keyword if you want to overwrite an existing function with the same name inside the same schema (be careful!);
  • all parameters must have an indicator of whether they are input (IN) parameters, output (OUT) parameters or input & output (INOUT) parameters. For the input, it is not mandatory to explicitly define the IN keyword (all parameters are considered as IN if not specified), although I highly recommend using those as well for the readability’s sake.
    • example of parameters:
      • CREATE OR REPLACE FUNCTION my_test_fucntion (p1 IN NUMBER, p2 INOUT NUMBER, p3 OUT DATE)
  • after the parameter type declaration, you have to declare the data type
  • this statement follows by AS or IS  … they are both equal so pick one
• BEGIN
  • this part marks the start of the processing of your business logic implemented inside
• EXCEPTION
  • to learn more read the article about Exceptions
• END
  • this part marks the end of the function.

See the example below to get an idea of how a function might look like:

CREATE OR REPLACE FUNCTION multiply(p_a IN NUMBER, p_b IN NUMBER) 
RETURN number
IS
BEGIN
   return p_a * p_b;
   EXCEPTION
     WHEN OTHERS THEN
        RAISE;
END;


--- call the function inside the SELECT statement
SELECT multiply(5,9) as rslt
FROM dual;

/* output

rslt
-------
45
*/

-- call the function inside a block (anonymous in this case but you can call it as well in a stored procedure or another function)
DECLARE 

   v_result NUMBER;

BEGIN
   v_result := multiply(6,7);
   dbms_output.put_line(v_result);
END;

/* output

42
*/

This is a just simple example but of course, you can use it on string, dates or any other data types or complex queries. You can also perform DML or DDL based on the input parameters or the result of the function body computations. See another function below that will tell return Y or N whether the given parameters comply with the rules defined inside. In this example, I will validate an email address:

CREATE OR REPLACE FUNCTION validate_email (p_email IN VARCHAR)
RETURN varchar
IS

  v_is_valid VARCHAR2(1) := 'N'; -- set to NO by default
 
BEGIN

   IF REGEXP_LIKE (p_email, '^[A-Za-z]+[A-Za-z0-9.]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$') THEN 
      v_is_valid := 'Y';
   END IF; 

   return v_is_valid;

END;


-- check out the results
SELECT validate_email('john.doe.com') as invalid_email
, validate_email('john@doe.com') as valid_email
FROM dual;


/* output

invalid_email | valid_email
---------------------------
     N        |     Y 
*/

I have to warn you, that these functions might be performance killers. Try to stick with the simplest logic you can come up with and incorporate built-in functions instead.