Oracle PL/SQL

CREATING NEW VALUES WITH NUMERIC AND DATE FUNCTIONS

1

Numeric Functions Function + * / ABS CEIL

Syntax Value1 + value2 Value2 – value6 Value3 * value4 Value1 / value2 Abs(value) Ceil(value)

COS COSH

Cos(value) Cosh(value)

EXP

Exp(value)

FLOOR

Floor(value)

LN

Ln(value)

LOG

Log(base, value)

MOD

Mod(value, divisor)

NULLIF

Nullif(expression1, expression2)

Description Addition Subtraction Multiplication Division Absolute value Determines the smallest integer greater than or equal to the value Cosine of the value. Hyperbolic cosine of the value. E raised to the valueth power.

Largest integer less than or equal to the value. Natural (base e) logarithm of the value. Base base logarithm of the value. Determines the remainder. This function compares the two expressions. If they are equal, it returns a null value. If they are not equal, it returns expression1.

2

NVL

Nvl(column name, substitute value)

NVL2

POWER

Nvl2(column name, not null substitute value, null substitute value) Power(value, exponent)

ROUND

Round(value, precision)

SIGN

Sign(value)

SIN SINH

Sin(value) Sinh(value)

SQRT

Sqrt(value)

TAN TANH

Tan(value) Tanh(value)

TO_NUMBER

To_number(value)

TRUNC

Trunc(value, precision)

Substitutes a new value for a null value. Substitutes a new value for both null values and not null values. Value raised to an exponent. Rounds a value to the specified precision. Displays a minus one if the value is negative and a positive one if the value is positive. Sine of the value. Hyperbolic sine of the value. Square root of the value. Tangent of the value. Hyperbolic tangent of the value. Changes a value to a numeric format value. Truncates the value to the specified precision

The ROUND function Using the Round function to round to the nearest hundredth and hundred

SQL> select last_name, wages/8, round(wages/3, 2), round(wages/3, -2) 2 from employee 3 where fk_department = 'INT' 4 group by last_name, wages; LAST_NAME WAGES/8 ROUND(WAGES/3,2) ROUND(WAGES/3,-2) --------------- --------- ---------------- ----------------BUSH 1750 4666.67 4700 COOLIDGE 1187.5 3166.67 3200 EISENHOWER FORD 1625 4333.33 4300 MILLER 1187.5 3166.67 3200 ROOSEVELT 1000 2666.67 2700 TRUMAN 1375 3666.67 3700 7 rows selected. SQL> End listing

3

The TRUNC function Using the Trunc function

SQL> select last_name, wages/3, trunc(wages/3,2), trunc(wages/3,-2) 2 from employee 3 where fk_department='INT' 4 group by last_name, wages; LAST_NAME --------------BUSH COOLIDGE EISENHOWER FORD MILLER ROOSEVELT TRUMAN

WAGES/3 TRUNC(WAGES/3,2) TRUNC(WAGES/3,-2) --------- ---------------- ----------------4666.6667 4666.66 4600 3166.6667 3166.66 3100 4333.3333 3166.6667 2666.6667 3666.6667

4333.33 3166.66 2666.66 3666.66

4300 3100 2600 3600

7 rows selected. SQL> End listing

DATES AND DATE FUNCTIONS

Date values have the following components: ?

Date of the month

?

Month

?

Year (including the century)

?

Hour of the day

?

Minutes of the hour

?

Seconds of the minute

4

Oracle uses a special calculation when populating the century by default. If the two-digityear value is between 50 and 99, Oracle will consider the 20th century the default (i.e. 1950 – 1999). If the two-digit-year value is between 00 and 49, Oracle will consider the 21st century the default (i.e. 2000 – 2049). The characters “DD-MON-RR” are used to denote the characteristics of the default date. This set of characters is called a date picture. The “DD” characters represent the day-of-the-month. The “MON” characters represent the month in the format of “APR”. The “RR” characters represent a two-digit year. The “RR” characters tell Oracle to use the discussed formula for determining the default century. This format is the default. If the “YY” characters are used, the default century will always be the current century.

To display more than the default date picture components, the To_char function must be used. This function changes the default date picture. The date picture is also used to tell Oracle the format of any non-default-inputted dates. This function is discussed in the next section. You might also note that the dates in our practice database range from the 1800’s to the 2000’s. If you do not put a century component in the arguments for your Where clauses, Oracle will place a “20” as the century by default. This may be the cause of some unexpected results.

5