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

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

So PL/SQL procedures
are distinct from things

like anonymous block PL/SQL,
because it's actually compiled

and stored in the database.

So just as a table,
as a database object,

a procedure is a
database object as well.

And what this does
is it facilitates

the use of shared code.

So rather than having
PL/SQL scripts running out

on a file system or
someone's desktop, when

we create procedures and
compile them into the database,

they're more easily accessible
by a number of processes,

be it developers, users,
applications, or what have you.

So what I want to do
first is show you just

how easy it is to convert
anonymous block PL/SQL scripts

into a procedure.

So here we have an
existing anonymous block,

declare section with the
variables and the cursors.

The begin section opens.

The cursor then does
the work, and then ends.

In order to convert
this, I simply

take the declare
statement and change

it to create or
replace procedure,

and then give it a name.

And then the is defines where
the declarative section begins.

And if I want to
create this object,

I simply highlight and execute.

And it shows that the
procedure employee

report has been compiled.

It's just really a
matter of convenience

that we've added this
create or replace.

We don't need to have
the or replace in here,

but what it allows us to do is
recompile the object on the fly

without dropping it first.

So it's really just a
matter of convenience.

So this procedure
does the same thing

that our anonymous block
was programmed to do.

So let's extend it
just a little bit.

Let's use some of the
advantages of procedures

in order to change this
to do a little more work.

What we can do in a procedure
that's easier, really,

than an anonymous
block is pass values

into the procedure itself.

So here, we're going to create
a variable called p exclude emp.

It's an IN variable.

And it is a varchar2.

So p exclude emp is going
to be a variable that

allows us to pass in a value to
the employee report procedure

that will exclude an employee.

We say that it's an IN
variable, because it

comes into the
procedure as opposed

to an out that
leaves the procedure.

And it is a varchar2 data type,
meaning a character data type.

So now I'm going to modify my
cursor to use this parameter

value that we pass in.

Now we need to recompile it,
because we've changed it.

Compile successfully.

And just to give you
an example of what

would happen if
we made a mistake,

notice that the compiler
log comes up and gives us

an indication that there
is a problem with the code.

Then we go out and debug it.

And these problems and
compiler log information

isn't always readily
available, and it

takes some practice being able
to read these and find out

what's wrong.

We know what's wrong,
because we did it.

And now it compiles
successfully.

So all we've done at this
point is actually just

create the code.

We haven't done anything
that would actually

execute the code.

So before we do
this again, we're

going to make sure that's
set server output on

has been run, because we want
output to come to the screen.

And we need to execute
this procedure.

We use the exec command,
the name of the procedure,

and then we pass in a
value to the procedure.

So we're going to pass the
value Alan into the procedure.

When it's executed, Alan gets
passed into this variable.

It is accessed here.

And so this will
be the same thing

as saying select the
columns from emp,

where e name not equal Alan.

So let's execute this.

So it comes through and uses
every row in the emp table

to output this string
of variables and strings

concatenated together.

But notice that Alan
is excluded, because we

passed in that value.

So this, essentially,
is a simple example

of how a procedure, or what we
might call a stored procedure,

works in PL/SQL.