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

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.