Oracle PL/SQL Programming, Third Edition

Chapter 9. Numbers

Where would we be without numbers? While those of us who are math-challenged might prefer a text-only view of the world, the reality is that much of the data in any database is numeric. How much inventory do we have? How much money do we owe? At what rate is our business growing? These are just some of the questions that we expect to answer using numbers from databases.

When working with numbers in PL/SQL, you need to have at least a passing familiarity with the following:

• The numeric datatypes at your disposal. It also helps to know in what situations they are best used.

• Conversion between numbers and their textual representations. How else do you expect to get those numbers into and out of your database?

• PL/SQL’s rich library of built-in numeric functions. After all, you don’t want to reinvent the wheel.

Each of these topics is discussed in this chapter. We’ll begin by looking at the datatypes themselves.

Numeric Datatypes

PL/SQL, just like the Oracle RDBMS, offers a variety of numeric datatypes to suit different purposes. There are basically three numeric datatypes you need to know about:

• NUMBER

• PLS_INTEGER

• BINARY_INTEGER

In practice, you may encounter other numeric types, such as FLOAT and DECIMAL. These are really nothing more than alternate names for the three core numeric types just listed. We’ll talk about these alternate names later, in Section 9.1.4.

Oracle’s numeric types are designed to perform identically across all platforms that Oracle supports, ...