Data Manipulation   «Prev  Next»
Lesson 1

Introduction to Manipulating Data using Functions in Oracle

When using data from the database, you may find that you want to massage the data to fit your needs.
For example, you may want to combine the city, state, and zip code together when printing out a mailing label.
This module covers several built-in SQL functions that you have seen before but that have a slightly different syntax when done in Oracle. This module also covers functions that are exclusive Oracle extensions to SQL.

Module objectives

By the end of this module, you will be able to:
  1. List character (string) functions unique to Oracle or different in Oracle
  2. Interpret the effect of number functions
  3. Identify valid date-related date functions and Oracle's standard date format
  4. Identify valid time-related date/time functions
  5. Use DECODE and NVL to modify query results
The module looks at functions by focusing on functions for character fields, for number fields, and for date/time fields.
The next several lessons discuss some functions that operate on character strings.
A function is a built-in PL/SQL program that always returns a single value. You can use the predefined functions or you can create your own. A function always returns a single value, as opposed to a procedure, which is a similar type of program but is able to return more than one value. You can call a function within a query or other SQL command. Use the NVL function to convert null values before comparing them.