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

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

So Oracle includes certain
functions within its database

product by default, things
like single-row functions,

such as upper or
lower or substring,

and multi-row functions, such
as average or min or max.

When we program a PL/SQL
function, what we're actually

doing is creating our own.

So rather than using an
Oracle-supplied function that

may not do the job
that we want, we're

creating our own function
to operate in the same way.

So to begin, let's
just take a quick look

at some of those
supplied functions.

So in this case, the emp
table has the ename column.

All of those values
are in uppercase.

But we're passing in each
row value in the column

into the lower function.

And it returns in lowercase.

So the sal column, for
salary, in the emp table

has values for salary and
has many of them-- actually,

14 of them-- in this
particular table.

And when we use the
average function,

we're passing those
values into the function.

And it comes up with an average.

It computes an average
for all of the values

that are passed to it.

So when we create
a PL/SQL function,

we're doing essentially
the same thing,

only we're writing the
code behind the function.

So let's take a look
at how this works.

We're going to find that
the structure of a function

is much like the
structure of a procedure.

So we create a replace
function, give it a name.

We're going to have
a parameter variable

that we pass in called ename.

However, we do have one thing
that's distinct in this case.

We use the statement
RETURN number

to indicate the data
type of the value

that we're going to return
out of the function.

What makes a function distinct
from PL/SQL, like a procedure,

is that it's actually going to
return a value to the calling

statement.

Next, we have our
declarative section.

So we declare our
local variables

and begin our execution.

We're going to select.

So let's look at our
processing section here.

What we're doing is we're
using a select statement

within our processing.

However, we're using it in a
PL/SQL context, if you will,

because we're selecting the
column sal plus sal times 0.10.

And we're selecting it
into one of our variables,

the lv_new_sal from
emp where the employee

name, ename, equals p_ename,
which is our parameter that we

pass in.

So we're going to pass in
the name of an employee.

And where that row is matched,
it's going to select the salary

plus salary times 0.10--

so in essence, a 10% raise--

hence the name of the
function, give_raise.

So now we'll compile it.

It compiled correctly.

And let's take a look at
how we access this function.

When we just did the
examples of functions--

single-row functions,
multi-row functions,

like lower and
average-- we used them

in the context of
a select statement.

And we do the same
in this case as well.

So we say select
give_raise, which

is the name of our function.

We pass in a value from dual.

So what does dual mean?

Well, dual in Oracle is
what we call a pseudo table.

So it's not really a table.

But it's actually a way
to process statements

like functions.

It's used in many context.

It's sort of a Swiss army
knife to be used as a table.

So when you need to
do a select statement

and you don't have a real
table to select against,

you use the dual table.

So if we select
sysdate from dual,

it comes back with the current
date of the server's time,

if you will.

So here, we're using
give_raise just in the same way

that we did the average
function or the lower function,

passing in the value ALLEN,
which comes to p_ename.

We do this query, where ename
equals the value we passed in,

which was ALLEN.

We select that into
the lv_new_sal variable

that we declared here.

And then we return that
to the calling statement,

which is here.

So let's see how it works.

And to compare this, let's see
what his salary was before--

1,600.

And so when we used
the give_raise function

to calculate what his
new salary would be,

it returns the value of 1,760.

So this is a PL/SQL
function at work.