Skip to content Skip to main navigation Skip to footer

Introduction to Functions

Objectives

This tutorial covers the following objectives:

  • Identify appropriate applications of single-row functions in query statements
  • Classify a function as a single-row or multi-row function
  • Differentiate between single-row functions and multi-row functions and the results returned by each

Purpose

  • When you put money in a drink machine, something happens between the time the money is deposited and your favorite drink is dispensed.
  • The transaction is processed internally by the machine.
  • Your money is the input and the drink is the output.
  • The machine performs a function.
  • The machine:
    • Counts your money
    • Makes sure your selection is chosen
    • Returns change, if necessary
  • In SQL, there are many types of functions that are used
  • to transform input in one form to output in another form.
  • These functions are used to manipulate data values.
  • Functions are small programs that perform an action on a value or column and produce something different as output.

How could you find out whether the first mane and last name of employees in the employees table is stored in uppercase, lowercase or mixed case? Execute a SELECT statement to look at the output.

SELECT first_name, last_name
FROM employees;

Functions

  • Functions have both input and output. Input into a function is referred to as an argument.
Functions
  • In the drink machine example, the input is money and the output is a drink.
Drink machine example,

Oracle has two distinct types of functions

  • Single-Row
  • Multiple-Row
Oracle function types.

Single-Row Versus Multiple-Row Functions

  • Single-row functions operate on single rows only and return one result per row.
  • There are different types of single-row functions including character, number, date, and conversion functions.
Single-Row Versus Multiple-Row Functions
  • Multiple-row functions can manipulate groups of rows to give one result per group of rows.
  • These functions are also known as group functions.
Single-Row Versus Multiple-Row Functions

Single-Row Functions

  • In SQL, Single-Row functions can be used to:
    • Perform calculations such as rounding numbers to a specified decimal place
    • Modify individual data items such as converting character values from uppercase to lowercase
  • Format dates and numbers for display such as converting the internal numeric database date format to a standard format
  • Convert column data types such as converting a character string to a number or date
Single-Row Functions
  • Single-Row Functions accept one or more arguments and will return a single result per row.
  • So if you apply the single row function to 12 rows, you will get 12 results out of the single row function.
  • In summary, single-row functions do the following:
    • Manipulate data items
    • Accept arguments and return one value
    • Act on each row returned
    • Return one result per row
    • Can modify the data type
    • Can be nested

Multiple-Row Functions

  • Multiple-Row (or Group) functions take many rows as input, and return a single value as output.
  • The rows input may be the whole table or the table split into smaller groups.
  • Examples of Multiple-Row (Group) functions include:
    • MAX: finds the highest value in a group of rows
    • MIN: finds the lowest value in a group of rows
    • AVG: finds the average value in a group of rows

Terminology

Key terms used in this tutorial included:

  • Single Row Function
  • Multiple Row Function

Summary

In this tutorial, you should have learned how to:

  • Identify appropriate applications of single-row functions in query statements
  • Classify a function as a single-row or multi-row function
  • Differentiate between single-row functions and multirow functions and the results returned by each

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *