Curso de
Desarrollo de Aplicaciones
Corporativas
3-1
3
Single-Row Functions
Objectives
After completing this lesson, you should be able to
do the following:
• Describe various types of functions available
in SQL
• Use character, number, and date functions in
SELECT statements
• Describe the use of conversion functions
3-3
SQL Functions
Input Output
Function
arg 1 Function
performs action
arg 2
Result
value
arg n
3-4
Two Types of SQL Functions
Functions
Single-row Multiple-row
functions functions
3-5
Single-Row Functions
Single row functions:
• Manipulate data items
• Accept arguments and return one value
• Act on each row returned
• Return one result per row
• May modify the data type
• Can be nested
• Accept arguments which can be a column or an
expression
function_name
function_name [(arg1,
[(arg1, arg2,...)]
arg2,...)]
3-6
Single-Row Functions
Character
General Number
Single-row
functions
Conversion Date
3-7
Character Functions
Character
functions
Case-manipulation Character-manipulation
functions functions
LOWER CONCAT
UPPER SUBSTR
INITCAP LENGTH
INSTR
LPAD | RPAD
TRIM
REPLACE
3-8
Case Manipulation Functions
These functions convert case for character strings.
Function Result
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL Course') Sql Course
3-10
Using Case Manipulation Functions
Display the employee number, name, and department
number for employee Higgins:
SELECT
SELECT employee_id,
employee_id, last_name,
last_name, department_id
department_id
FROM
FROM employees
employees
WHERE
WHERE last_name
last_name == 'higgins';
'higgins';
no
no rows
rows selected
selected
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';
3-11
Character-Manipulation Functions
These functions manipulate character strings:
Function Result
CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld', 'W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary, 10, '*') 24000*****
TRIM('H' FROM 'HelloWorld') elloWorld
3-12
Using the Character-Manipulation
Functions
1
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name), 2
INSTR(last_name, 'a') "Contains 'a'?"
FROM employees
3
WHERE SUBSTR(job_id, 4) = 'REP';
1 2 3
3-13
Number Functions
• ROUND: Rounds value to specified decimal
ROUND(45.926, 2) 45.93
• TRUNC: Truncates value to specified decimal
TRUNC(45.926, 2) 45.92
• MOD: Returns remainder of division
MOD(1600, 300) 100
3-14
Using the ROUND Function
1 2
SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1) 3
FROM DUAL;
1 2 3
DUAL is a dummy table you can use to view results
from functions and calculations.
3-15
Using the TRUNC Function
1 2
SELECT TRUNC(45.923,2), TRUNC(45.923),
TRUNC(45.923,-2) 3
FROM DUAL;
1 2 3
3-16
Using the MOD Function
Calculate the remainder of a salary after it is divided
by 5000 for all employees whose job title is sales
representative.
SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';
3-17
Working with Dates
• Oracle database stores dates in an internal numeric
format: century, year, month, day, hours, minutes,
seconds.
• The default date display format is DD-MON-RR.
– Allows you to store 21st century dates in the 20th
century by specifying only the last two digits of the
year.
– Allows you to store 20th century dates in the 21st
century in the same way.
SELECT last_name, hire_date
FROM employees
WHERE last_name like 'G%';
3-18
Working with Dates
SYSDATE is a function that returns:
• Date
• Time
3-19
Arithmetic with Dates
• Add or subtract a number to or from a date for a
resultant date value.
• Subtract two dates to find the number of days
between those dates.
• Add hours to a date by dividing the number of
hours by 24.
3-20
Using Arithmetic Operators
with Dates
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
3-21
Date Functions
Function Description
MONTHS_BETWEEN Number of months
between two dates
ADD_MONTHS Add calendar months to
date
NEXT_DAY Next day of the date
specified
LAST_DAY Last day of the month
ROUND Round date
TRUNC Truncate date
3-22
Using Date Functions
• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
19.6774194
• ADD_MONTHS ('11-JAN-94',6) '11-JUL-94'
• NEXT_DAY ('01-SEP-95','FRIDAY')
'08-SEP-95'
• LAST_DAY('01-FEB-95') '28-FEB-95'
3-23
Using Date Functions
Assume SYSDATE = '25-JUL-95':
• ROUND(SYSDATE,'MONTH') 01-AUG-95
• ROUND(SYSDATE ,'YEAR') 01-JAN-96
• TRUNC(SYSDATE ,'MONTH') 01-JUL-95
• TRUNC(SYSDATE ,'YEAR') 01-JAN-95
3-24
Practice 3, Part One: Overview
This practice covers the following topics:
• Writing a query that displays the current date
• Creating queries that require the use of numeric,
character, and date functions
• Performing calculations of years and months of
service for an employee
3-25
Explicit Data Type Conversion
TO_NUMBER TO_DATE
NUMBER CHARACTER DATE
TO_CHAR TO_CHAR
3-26
Explicit Data Type Conversion
TO_NUMBER TO_DATE
NUMBER CHARACTER DATE
TO_CHAR TO_CHAR
3-27
Using the TO_CHAR Function with Dates
TO_CHAR(date,
TO_CHAR(date, 'format_model')
'format_model')
The format model:
• Must be enclosed in single quotation marks and is case
sensitive
• Can include any valid date format element
• Has an fm element to remove padded blanks or
suppress leading zeros
• Is separated from the date value by a comma
3-29
Elements of the Date Format Model
YYYY Full year in numbers
YEAR Year spelled out
MM Two-digit value for month
MONTH Full name of the month
Three-letter abbreviation of the
MON
month
Three-letter abbreviation of the
DY
day of the week
DAY Full name of the day of the week
DD Numeric day of the month
3-30
Elements of the Date Format Model
• Time elements format the time portion of the date.
HH24:MI:SS AM [Link] PM
• Add character strings by enclosing them in double
quotation marks.
DD "of" MONTH 12 of OCTOBER
• Number suffixes spell out numbers.
ddspth fourteenth
3-32
Using the TO_CHAR Function with Dates
SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY')
AS HIREDATE
FROM employees;
3-34
Using the TO_CHAR Function with
Numbers
TO_CHAR(number,
TO_CHAR(number, 'format_model')
'format_model')
These are some of the format elements you can use
with the TO_CHAR function to display a number value
as a character:
9 Represents a number
0 Forces a zero to be displayed
$ Places a floating dollar sign
L Uses the floating local currency symbol
. Prints a decimal point
, Prints a thousand indicator
3-35
Using the TO_CHAR Function with Numbers
SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';
3-36
Using the TO_NUMBER and TO_DATE
Functions
• Convert a character string to a number format
using the TO_NUMBER function:
TO_NUMBER(char[,
TO_NUMBER(char[, 'format_model'])
'format_model'])
• Convert a character string to a date format using
the TO_DATE function:
TO_DATE(char[,
TO_DATE(char[, 'format_model'])
'format_model'])
• These functions have an fx modifier. This modifier
specifies the exact matching for the character
argument and date format model of a TO_DATE
function
3-37
Nesting Functions
• Single-row functions can be nested to any level.
• Nested functions are evaluated from deepest level
to the least deep level.
F3(F2(F1(col,arg1),arg2),arg3)
Step 1 = Result 1
Step 2 = Result 2
Step 3 = Result 3
3-38
Nesting Functions
SELECT last_name,
NVL(TO_CHAR(manager_id), 'No Manager')
FROM employees
WHERE manager_id IS NULL;
3-39
General Functions
These functions work with any data type and pertain
to using nulls.
• NVL (expr1, expr2)
• NVL2 (expr1, expr2, expr3)
• NULLIF (expr1, expr2)
• COALESCE (expr1, expr2, ..., exprn)
3-40
NVL Function
Converts a null to an actual value.
• Data types that can be used are date, character,
and number.
• Data types must match:
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'No Job Yet')
3-41
Using the NVL Function
SELECT last_name, salary, NVL(commission_pct, 0),
1
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL 2
FROM employees;
1 2
3-42
Using the NVL2 Function
SELECT last_name, salary, commission_pct, 1
NVL2(commission_pct, 2
'SAL+COMM', 'SAL') income
FROM employees WHERE department_id IN (50, 80);
1 2
3-43
Using the NULLIF Function
1
SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2",
2
NULLIF(LENGTH(first_name), LENGTH(last_name)) result 3
FROM employees;
1 2 3
3-44
Using the COALESCE Function
• The advantage of the COALESCE function over the
NVL function is that the COALESCE function can
take multiple alternate values.
• If the first expression is not null, it returns that
expression; otherwise, it does a COALESCE of the
remaining expressions.
3-45
Using the COALESCE Function
SELECT last_name,
COALESCE(commission_pct, salary, 10) comm
FROM employees
ORDER BY commission_pct;
3-46
Conditional Expressions
• Provide the use of IF-THEN-ELSE logic within a
SQL statement
• Use two methods:
– CASE expression
– DECODE function
3-47
The CASE Expression
Facilitates conditional inquiries by doing the work of
an IF-THEN-ELSE statement:
CASE
CASE expr
expr WHEN
WHEN comparison_expr1
comparison_expr1 THEN
THEN return_expr1
return_expr1
[WHEN
[WHEN comparison_expr2
comparison_expr2 THEN
THEN return_expr2
return_expr2
WHEN
WHEN comparison_exprn
comparison_exprn THEN
THEN return_exprn
return_exprn
ELSE
ELSE else_expr]
else_expr]
END
END
3-48
Using the CASE Expression
Facilitates conditional inquiries by doing the work of
an IF-THEN-ELSE statement:
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
3-49
The DECODE Function
Facilitates conditional inquiries by doing the work of
a CASE or IF-THEN-ELSE statement:
DECODE(col|expression,
DECODE(col|expression, search1,
search1, result1
result1
[,
[, search2,
search2, result2,...,]
result2,...,]
[,
[, default])
default])
3-50
Using the DECODE Function
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;
3-51
Using the DECODE Function
Display the applicable tax rate for each employee in
department 80.
SELECT last_name, salary,
DECODE (TRUNC(salary/2000, 0),
0, 0.00,
1, 0.09,
2, 0.20,
3, 0.30,
4, 0.40,
5, 0.42,
6, 0.44,
0.45) TAX_RATE
FROM employees
WHERE department_id = 80;
3-52
Summary
In this lesson, you should have learned how to:
• Perform calculations on data using functions
• Modify individual data items using functions
• Manipulate output for groups of rows using
functions
• Alter date formats for display using functions
• Convert column data types using functions
• Use NVL functions
• Use IF-THEN-ELSE logic
3-53
Practice 3, Part Two: Overview
This practice covers the following topics:
• Creating queries that require the use of numeric,
character, and date functions
• Using concatenation with functions
• Writing case-insensitive queries to test the
usefulness of character functions
• Performing calculations of years and months of
service for an employee
• Determining the review date for an employee
3-54
3-55
3-57