Oracle and PL/SQL Recipes: A Problem-Solution Approach

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)); ...