Databases Reference
In-Depth Information
7876...............................Adams
7900...............................James
7902................................Ford
7934..............................Miller
14 rows selected.
This query uses three string functions: two of them are nested within another
function, plus a concatenation operation. Let's break down the query to clarify
how it works.
As you've seen, the function call INITCAP(ename) changes the first letter of each
word to uppercase. The function call LENGTH(empno) returns the length of a char-
acter string. In this case, there is an implicit conversion of a numeric type to a string
type. An
implicit conversion
occurs automatically when Oracle evaluates an expres-
sion; conversely, an
explicit conversion
occurs when the SQL statement makes no
assumptions about how Oracle will convert one datatype to another and uses one or
more of the built-in functions to perform the conversion. The column is converted to
a character string, and the length of the converted character string is returned.
implicit conversion
Conversion of one datatype to another
that occurs automatically when columns
or constants with dissimilar datatypes
appear in an expression.
The LPAD function will left-pad a character string to a specified number of
characters with the character you specify. Scott wants to end up with a total
of 40 characters, so he subtracts the number of characters that the employee
number would take up. Here, he will left-pad the employee name with periods,
less the amount of space taken up by the employee number. Once the LPAD func-
tion is evaluated, he will concatenate the employee number at the front, and once
again, he will allow the implicit conversion of the employee number from
numeric to string.
Finally, Scott wants the title for the report to look readable, so he assigns a
column alias to the result of the concatenated function calls. The column alias
can act as a report title.
explicit conversion
Conversion of one datatype to another
in an expression using function calls
such as
TO_CHAR
instead of relying
on automatic conversion rules (implicit
conversion).
Numeric Functions
Numeric functions
are functions that perform some kind of transformation
on a numeric literal, a column containing a number, or an expression consist-
ing of numeric literals and table columns. Numeric functions will return a
number as the result of the transformation. Table 3.3 briefly describes the
built-in numeric functions.
numeric function
A function that operates on numeric
literals, columns containing numbers,
or an expression containing numeric
literals and table columns, returning a
number as the result.
Table 3.3
Built-In Numeric Functions
Function
Description
ABS
Returns the absolute value of the argument
ACOS
Returns the arc cosine
