Database Reference
In-Depth Information
groups. There will be much more on using functions in later chapters, pri-
marily in Chapter 9 and Chapter 11.
Single-row functions operate on one row at a time.
Datatype conversion functions convert values such as numbers to
strings.
Group functions apply specific functionality to grouping and sum-
mary queries.
Object reference functions reference data across objects.
User-defined functions are custom-written functions for specific tasks
not available in the functions that the Oracle Database provides.
An example function would be the use of the SUBSTR and INSTR
function in the following example, retrieving the first word of each artist's
name.
SELECT SUBSTR(NAME,1,INSTR(NAME,' ')) FROM ARTIST;
4.3.3
Arithmetic Operations
Let's use SYSDATE to calculate the time between a date stored in the data-
base and today's date. The following query determines the number of days
between the due date and today. Figure 4.16 shows the result.
SELECT AMOUNT_CHARGED - AMOUNT_PAID BALANCE, DUE_DATE
, SYSDATE, SYSDATE - DUE_DATE DAYS_LATE
FROM STUDIOTIME
WHERE AMOUNT_CHARGED > AMOUNT_PAID;
In Figure 4.16, there are two subtraction expressions in the SELECT
clause:
Subtracting two numbers (the AMOUNT_CHARGED and the
AMOUNT_PAID).
Subtracting two dates (the SYSDATE and the DUE_DATE). When
you subtract dates, Oracle Database 10
g
will calculate the number of
days between the two dates. Dates are stored internally as Julian dates
and automatically converted with the default data display. A Julian
date is a number in seconds from a specified date, such as January 1,
