6-7. Implicitly Converting Between PLS_INTEGER and NUMBER
Problem
You want to convert a number to PLS_INTEGER datatype so that calculations can be performed.
Solution
In this case, allow Oracle to do the footwork and implicitly convert between the two datatypes. In the following example, the function accepts a NUMBER, converts it to PLS_INTEGER, and performs a calculation returning the result. The function converts to PLS_INTEGER in order to gain a performance boost.
CREATE OR REPLACE FUNCTION mass_energy_calc (mass IN NUMBER,
energy IN NUMBER)
RETURN PLS_INTEGER IS
new_mass PLS_INTEGER := mass;
new_energy PLS_INTEGER := energy;
BEGIN
RETURN ((new_mass * new_energy) * (new_mass * new_energy));
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END;
The function will accept NUMBER values, automatically convert them into PLS_INTEGER, and return a PLS_INTEGER type.
How It Works
Implicit conversion occurs when Oracle automatically converts from one datatype to another. Oracle will implicitly convert some datatypes but not others. As per the solution to this recipe, one of the datatypes that supports implicit conversion is PLS_INTEGER. As a matter of fact, PLS_INTEGER cannot be converted using the TO_NUMBER function; so in this case, implicit is the best way to convert a PLS_INTEGER datatype to anything else. However, if there is a way to explicitly convert the datatype from one to another, then that is the recommended approach. You cannot be certain of the results when Oracle is automatically converting for you; explicit conversion allows you to have more control.
The PLS_INTEGER datatype can be advantageous over using a NUMBER in some cases. For instance, a PLS_INTEGER has performance advantages when compared to a NUMBER for doing calculations because they use machine arithmetic as opposed to library arithmetic. Additionally, the PLS_INTEGER datatype requires less storage than its counterparts. In the solution to this recipe, the function takes advantage of the faster calculation speed that is possible using PLS_INTEGER.