Oracle Function Example (Return Number)

Oracle Function Example (Return Number)

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

In this article, I am giving some Oracle Function examples returning number values.

Oracle Function Example (Return Number)

The following Oracle function example will return the percentage (number) of the first parameter by calculating it with the second parameter.

CREATE OR REPLACE FUNCTION calc_percentage (p_1 IN NUMBER, p_2 IN NUMBER)
RETURN NUMBER
IS
n_pct NUMBER := 0;
BEGIN
IF p_1 IS NOT NULL AND p_2 IS NOT NULL
THEN
n_pct := (p_1 * p_2) / 100;
END IF;

RETURN n_pct;
END calc_percentage;
/

Test:

SELECT calc_percentage (40, 5) percentage FROM DUAL;

Output:

PERCENTAGE
----------
2
1 row selected.

In the following example, the function will return the number by converting the current date to a number without any parameter.

CREATE OR REPLACE FUNCTION date_in_number
RETURN NUMBER
IS
BEGIN
RETURN (TO_NUMBER (TO_CHAR (SYSDATE, 'yyyymmdd')));
END date_in_number;
/

Test:

SELECT date_in_number FROM DUAL;

Output:

DATE_IN_NUMBER
--------------
20180807
1 row selected.

See also: