All language subtitles for [SubtitleTools.com] Single Row Functions - Learning Oracle 12c [Video]

In this lesson, we're going
to be looking at functions.

And functions are something
that are a very powerful way

to do data processing.

Specifically, we'll be looking
at single row functions.

Single row functions
are designed

to do a certain
operation on every row

in a particular
column of a table.

So let's get connected.

And we'll look at
our table list.

And let's do a simple query.

Select ename from the
emp table and execute.

So here, we see the ename column
with employee's last names

that are stored in the database.

But let's say that
they're in uppercase,

and we want to display
them in a different way.

We want to do some operation.

We can use a
function to do that.

So the function is going to
change that data as we see it

in whatever way we specify.

So we'll use a
function, for instance,

to show the lower case
values of the ename column.

So notice what
we're seeing here.

This is, in essence,
simple data transformation.

So we have the values stored
in uppercase in the database.

But we're wanting to
see them in lowercase.

And that's what a function does.

So in this case, our
function is called lower.

We always use parentheses.

And then we surrounded
it with a column name.

So go to each value of
the ename column, Smith,

and change it to
lowercase, Allen,

and change it to lowercase.

So a single row function
goes and operates

on each individual row.

And there are a number
of different string type

functions like this.

So we're going to look
at a couple of them.

There are some
that we won't take

a look at because they're
less common or a little more

obscure.

The idea here is to get
how a function works

and what it can do for
your data processing.

So let's change to a different
function called a initcap

and execute.

So here, we're using mixed case.

So are initcap function
projects this data

and transforms it
into mixed case.

So we might like to see the
data be in mixed case instead

of all capitals if we're
running a report generating

labels or something like that.

So the initcap function will
do that operation on each row.

That's what a single
row function does.

Let's take a look at
the length function.

So here, our function is length.

And we're using the
ename column, execute.

And I'll add ename in here
just to make this more visually

obvious.

So what length is going to do,
it's going to look at a string,

and then present the
number of characters

in each one of the values.

So for instance, Smith
has a length of five,

Ward has a length of four,
Martin a length of six,

and so on and so forth.

So the length function is
usually used in conjunction

with other functions
that we'll look at

to give us that information
and maybe pass that information

on to another function.

Let's look at the substring
string manipulation

function, ename.

And again for clarity, I'll
show the before and after.

So the substring is
going to take and look

at a string value, such
as any of these string

values, these character values
and in the ename column.

And then it's going to return
a portion of that string.

So here, we're seeing the first
three characters in each name.

And that's because we've
used the substring function,

but we're passing it three
values in this case instead

of just the column.

So we give it the column.

The next value that we pass
is the starting character, so

the first character.

And the third value that
we're passing into substring

is the total number of
characters to be returned.

So start at the first character
and return the first three

characters.

We can change this.

Start at the second character
and return four characters,

for instance.

And notice that it
starts at the second

and returns four characters.

In the case of Ward, it
starts at the second character

and doesn't have
four to present.

And so it does the
three that it can.

And that's just because Ward
is equal to four characters,

and we're starting
at the second.

Let's take a look at
the to_char function.

This is a really
powerful function.

It's a little more complex.

To-char is going to be used
to take a different data type

and present it as a character.

And execute.

All right, so what are
we looking at here?

Well, let's start with
the hire date column.

All right, so hire
data is stored

in the database as a two digit
day, three digit month, and two

digit year.

But what if we want more
information than that?

How do we extract
time information,

change the formatting
of the data?

What if we wanted it to say
December 17th, 1980, instead

of 17, December?

That's what the to_char
function is used to do.

It's often used to manipulate
dates, probably most often,

to a format that
we want to display.

So what we've got here is
what's called a format mask.

So what does all this mean?

Well, we start out
is a format mask

is really giving us
a template for how

we want to display the data.

So the DD part of the format
mask means two digit day.

MON means three digit
month abbreviation.

YYYY means four digit year.

Notice that differentiation.

HH24 means hours on
the 24 hour clock.

MI is minutes.

SS is seconds from
the emp table.

And so it's taking this
data and presenting it

in a different way.

So let's change the format
mask to be something

that's even more visible.

All right, so now
we're not extracting

time information, but just
the date information itself.

And we're showing it
in a different format.

So now instead of
MON in capitals,

we use mixed case
Month, and that's

going to actually show
the month value itself.

Let's turn to a different
type of function.

And we're looking at
the round function.

So at this point,
we don't see a lot

of difference between these
values because of the way

that we're rounding them.

We're really passing in a
default value for the rounding

location, which is just to
the nearest one's value.

Let's try something like this.

So notice here that we
have a SAL value of 1250.

But in the rounded
column, we have 1300.

So 1250 has been
rounded up to 1300.

The round function is going to
take a value, such as the SAL

column, and round it to
the place that we specify.

So here, we're specifying
to the negative two place.

So it's going to go back
two from the decimal point

and round to that spot.

So a 1250 is going
to round to 1300.

For instance, 2975
is going to round

to 3000, so on and so forth.

So the round function
is used to round values.

Now normally what you would
round are decimal values.

In that case, we would
use non-negative numbers

to round to the
second decimal point

to the right of the
decimal, for instance.