Original subtitles

In this lesson, we're going to look at the concept of a packet

PL/SQL package.

So a package is a way to take different units of PL/SQL code

and bring them together into a single unit--

a single package, if you will.

And this facilitates the sharing of code.

And makes it easier to access different pieces of code.

So in the same way that having lots of anonymous block

PL/SQL scripts out on a file system

is more cumbersome than using procedures and functions--

those kinds of code that are stored in the database--

packaging takes it one step further.

And allows us rather than to have

lots of different procedures that

have to be accessed separately or different functions

or combinations, we get to bring them all together.

So when code is related--

these different pieces of code- we can bring them together

into a package.

Now a package is composed of two different units-- if you will--

a package specification and a package body.

So let's define a package specification.

So what I'm working with here are two different sets of code.

So we have a function here--

the give raise function.

And then we have the employee report procedure.

So what we do in a package is we define each one

of the units of our code--

the procedure and the function.

And we define those in the package specification.

And that's what we're designating when we

say create or replace package.

So our employee report procedure, our definition

of in parameter variables.

And remember that SQL and PL/SQL are not case

sensitive unless the strings are within quotes.

So we can use uppercase RETURN, lower case RETURN.

Our create or replace can be upper or lowercase--

it doesn't matter.

It usually just comes down to coding standards.

So here, we have a package that contains a procedure called

employee report, that takes this input as a VARCHAR2 data type

and it has this function-- give raise--

that takes this variable in as a VARCHAR2 and returns a number.

So let's compile this.

So our package is compiled.

So now we have the specification,

but these procedures in the packages

don't actually do anything.

So we need to create a package body.

And here-- to save time from rewriting a lot of code--

I'm going to go grab the procedure--

and I'm just going to start at the word procedure, All the way

to the end.

But I want to specify the name of the procedure

at the end statement--

just employee report.

So here's the package body.

And it has the definition of the procedure that's referenced

in the package specification.

Now we'll grab function.

And now an end to the entire package itself.

all the way to the beginning of our package body and Compile,

to see if we got it.

And it looks like we did.

So now we have our package specification and our package

body.

And we can actually see this over here in SQL Developer.

Now that we've created them, we see an EMP package.

We expand that, it shows we have an Employee Report and a Give

Raise.

And then it has the body.

And for each one of these.

And we just click on it and here,

shows us the procedure within the package body.

In order to access or run these packages,

we need to reference our command with the name.

So for instance-- in running the give raise procedure,

we would simply use it in a SELECT statement.

But because the give raise function belongs in a package,

we need to do a different type of call to the package.

So we say select EMPpackage.giveraise,

pass in the value from the dual table.

Notice that it says the package is in an invalid state.

So when this occurs, we'll go all the way to the beginning,

make sure we've got everything, and recompile the package.

Notice that it says, we cannot compile the package without its

specification.

So let's go and do that, first.

Compiles successfully.

Package compiles successfully.

And now the EMP package give raise returns

the value of 1,760.

So why did we have the problems with this?

It's because anytime we change a package body, in any way,

we need to also compile it specification

if there's changes in there that have affected it.

So we always have to keep that in mind

whenever we're compiling packages.

It's best to compile them together at the same time.

So using packages can make the storage and retrieval of code--

PL/SQL code-- much simpler, and more straightforward,

and a little more organized, as well.