Original subtitles

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.