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

af Afrikaans
ak Akan
sq Albanian
am Amharic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.