Here is Logic to Write UDF to Check Input is Number or not

Here is a logic to write udf in PL/SQL. Udfs you can use wherever required in the application program to save time.

A function that needs input and it validates it and returns the output. The learning item is – function always returns a value.

Here, the below function checks the input value supplied is Varchar2 of number or not. If it is not a number, it displays NULL. If the input value is a number, then it displays a number.

PL/SQL code to create a function

CREATE OR REPLACE FUNCTION to_number_or_null (
aiv_number IN     varchar2 )
return     number is 
begin
    return to_number(aiv_number);
exception
   when OTHERS then
     return NULL;
end to_number_or_null;

Here each statement ends with a semi-colon. The return value is the number. If the input is not a number, the logic goes to an exception where it returns NULL.

How to test the function

I used here DUAL to test the function in SQL developer. When you give a number, it displays the same.

select to_number_or_null('2') from DUAL;

The output is:

2

When you give alpha value, it displays as NULL

select to_number_or_null('A') from DUAL;

The output is:

NULL

Summary

• When you find return in PL/SQL code, you can say – that is definition of a function
• A function always returns a value

Related posts