Oracle SQL Functions

SQL Functions

Functions are a very powerful feature of SQL. 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
# Covert column data types

SQL functions sometimes take arguments and always return a value.
Note: Most of the functions that are described in this lesson are specific to the Oracle version of SQL.



SQL Functions (Continued)
There are two types of functions
#Single-row functions
#Multiple-row functions

Single-Row Functions
These functions operate on single rows only and return one result per row. There are different types of single-row functions. This lesson covers the following ones:

# Character
# Number
# Date
# Conversion
# General

Multiple-Row Functions

Functions can manipulate groups of rows to give one result per group of rows. These functions are also known as group 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.

function_name [(arg1, arg2,....)]

Single-Row Functions:

Single-Row functions are used to manipulate data items. They accept one or more arguments and return one value for each row that is returned by the query. An argument can be one of the following:

# User-supplied constant
# Variable value
# Column name
# Expression

Features of single-row functions include:

# Acting on each row that is returned in the query
# Returning one result per row
# Possibly returning a data value of a different type than the one that is referenced
# Possibly expecting one or more arguments
# Can be used in SELECT, WHERE, and ORDER BY clauses; can be nested

In the syntax:

function_name ---- is the name of the function
arg1, arg2 ------- is any argument to be used by the function. This can be represented by a column name or expression.




Single-Row Functions(continued)
# Character functions: Accept character input and can return both character and number values.
# Number functions: Accept numeric input and return numeric values.
# Date functions: Operate on vaues of the DATE data type ( All date functions return a value of DATE data type except the MONTHS_BETWEEN functions, which returns a number)
# Conversion functions: Convert a value from one data type to another
# General functions:
- NVL
- NVL2
- NULLIF
- COALESCE
- CASE
- DECODE

Character Functions


Character Functions:

Single-row character functions accept character data as input and can return both character and numeric values. Character functions can be divided into the following:

# Case-manipulation functions
# Character-manipulation functions



SELECT 'The job id for '||UPPER (last_name)||'is'
||LOWER(job_id) AS "EMPLOYEE DETAILS"
FROM employees;

The slide example displays the employee number, name, and department no of employee Higgins.
The WHERE clause of the first SQL statement specifies the employee name as higgins. Because all the data in the EMPLOYEES table is stored in proper case, the nmae higgins does not find a match in the table, and no rows are selected.
The WHERE clause of the second SQL statement specifies that the employee name in the EMPLOYEES table is compared to higgins, converting the LAST_NAME column to lowercase for comparison purposes. Because both names are now lowercase, a match is found and one row is selected. The WHERE clause can be rewritten in the following mannaer to produce the same result.

....WHERE last_name = 'Higgins'
The name in the output appears as it was stored in the database. To display the name in upercase, use the UPPER function in the SELECT statement.

SELECT employee_id, UPPER (last_name), department_id
FROM employees
WHERE INITCAP(last_name) = 'Higgins';

Character-Manipulation Functions

CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, and TRIM are the character-manipulation functions that are convered in this lession.
# CONCAT: Joins values together (You are limited to using two parameters with CONCAT)
# SUBSTR: Extracts a string of determined length
# LENGTH: Shows the length of a string as a numeric value
# INSTR: Find the numeric position of a named character
# LPAD: Pads the character value right-justified
# RPAD: Pads the character value left-justified
# TRIM: Trims heading or trailing characters (or both)from a character string (If trim_character or trim_source is a character literal, you must enclose it in single quotation marks.)

Note: You can use functions such as UPPER and LOWER with ampersand substitution. For example, use UPPER ('&job_title') so that the user does not have to enter the job title in a specific case.

Example:

SELECT employee_id, CONCAT (first_name, last_name) NAME,
LENGTH (last_name), INSTR (last_name, 'a') "Contains 'a'?"
FROM employees
WHERE SUBSTR (last_name, -1, 1)= 'n';

Number Functions
# ROUND: Round value to specified decimal
# TRUNC: Truncates value to specified decimal
# MOD: Returns remainder of division

Number Functions

Number functions accept numeric input and return numeric values.