Oracle Database:
Introduction to SQL
TOPIC: USING SINGLE-ROW FUNCTIONS TO CUSTOMIZE OUTPUT
COMPUTER PRIDE
TRAINER: ROBERT BUNDI
Using Single-Row Functions to
Customize Output
A function is a subprogram that returns a value.
Functions continued…
Functions are a very powerful feature of SQL as they can be used to do
the following:
Perform calculations on data.
Modify individual data items.
Manipulate output for groups of rows.
Format dates and numbers for display.
Convert column data types.
SQL functions sometimes take arguments and always return a value.
Types of SQL Functions
Single-Row Functions
Single-row functions:
Manipulate data items.
Accept arguments and return one value.
Act on each row that is returned.
Return one result per row.
May modify the data type.
Can be nested.
Accept arguments that can be a column or an expression.
Single-Row Functions
Character Functions
Character Functions continued…
Character Functions continued…
Numeric Functions
ROUND: Rounds values to a specified decimal.
TRUNC: Truncates value to a specified decimal.
MOD: Returns remainder of division.
Numeric Functions
Working with Dates
The Oracle Database stores dates in an internal numeric format of:
Century, year, month, day, hours, minutes, and seconds.
The default date display format is DD_MON_RR.
The format enables you to store 21st century dates in the 20th century by
specifying only the last two digits of the year.
The format also enables you to store 20th century in the same way.
Doing Arithmetic with Dates
You can add to or subtract a number from a date for a resultant data
value.
Also, you can subtract two dates to find the number of days between
those dates.
You can also, add hours to a date by dividing the number of hours by 24.
Date-Manipulation Functions
More on Date Functions
Date functions operate on Oracle dates.
All date functions return a value of the DATE data type except MONTHS_BETWEEN which
returns a numeric value.
MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and
date2.
The result can be positive or negative. If date1 is later than date2, the result is positive and
vice versa.
ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must
be an integer and can be negative.
NEXT_DAY(date, ‘char’): Finds the date of the next specified day of the week. The value
char may be a number representing a day or a character string.
LAST_DAY(date): Finds the date of the last day of the month that contains date.
Using ROUND and TRUNC Functions
with Dates
ROUND(date[, ‘fmt’]): Returns date rounded to the unit that is specified by
the format model fmt. If the format model is omitted, date is rounded to
the nearest day.
TRUNC(date[, ‘fmt’]): Returns date with the time portion of the day
truncated to the unit that is specified by the format model fmt. If the
format model fmt is omitted, date is truncated to the nearest day.
In the below image, lets assume the SYSDATE = ’25-JUL-03’:
Continued….
You can round dates to the nearest year or month.
If the format model is month, dates 1-15 result in the first day of the current
month.
Dates 16-31 result in the first day of the next month.
If the format model is year, months 1-6 result in January 1 of the current
year.
Months 7-12 result in January 1 of the next year.
Nesting Functions
Single-row functions can be nested to any level.
Nested functions are evaluated from the deepest level to the least deep
level.