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

In this lesson,
we're going to be

taking a look at an
anonymous block of PL/SQL.

So this is going to be
the PL/SQL programming

language, specifically in the
form of an anonymous block.

Now, the anonymous block we
could think of as a script.

So this is a PL/SQL script.

It's not a compiled
object, but it is a script,

and it would live in a file
outside the database somewhere,

and then could be run
against the database.

So let's connect up and
type in the basic framework

of an anonymous block.

So this is the basic format
of an anonymous block

PL/SQL script.

So we begin with
the DECLARE section.

And the DECLARE
section is where we

have variable declaration,
data types and values,

and any definitions
of any cursors--

and we'll discuss what
those are in a moment.

The next is the BEGIN block.

This block will have the
executable statements

that actually do the work.

And then we have
an END statement

that puts an end to it.

So our declarations
will be in this area,

and our statements
will be in this area.

Before we go too
much further, it's

usually a good habit to
type serveroutput on--

and we'll highlight
that and execute it.

This will allow us to get the
output to come to the screen.

PL/SQL is built for doing
work against database data,

and so by default doesn't
output things to the screen.

It's not as interactive as that.

But if we set serveroutput
on that tells it to output

visually the output
to the screen.

So what we're going to do is
modify our basic structure

to do a little bit of work.

So I'm going to
declare some variables.

So these are our
variable declarations.

We have a variable
called lv_name, lv_job,

and lv_hiredate, and the lv is
a standard for local variable.

You can name them job, name,
and hiredate if you like.

This is the data type.

So they're character
data, max width of 20,

and lv_hiredate is a date.

Next we'll define a cursor.

So this is a cursor definition.

Now, what a cursor is--

it's an object that we set
up, basically encapsulates

a SELECT statement.

So when we create a cursor,
we define it as being,

in this case, select these three
columns from the emp table.

PL/SQL has constructs
like this to enable

us to more easily work
with database data.

You won't see these
types of constructs

in other third-generation
languages like C.

They have to be done
more explicitly.

But because PL/SQL is
designed to work with data,

we get to use
constructs like this.

So now we're in
our BEGIN section,

where we have our
actual statements.

So we're going to
open our cursor.

We're going to set up a loop.

Now, this statement will
bring back more than one row,

and we want to do
something to each row

as it comes into the loop.

So we set up a loop.

We cursor through these
rows, operate on each one,

and then exit when we come
to the end of the table.

So that's what our looping
constructs are all about.

We say fetch the cursor
into our local variables.

So notice, here, that
we're fetching our cursor--

which is this SELECT statement--
into these variables--

ename into lv_name,
job into lv_job,

and hiredate into lv_hiredate.

And we type exit when
the CURSOR%NOTFOUND--

and again, the code
is not case-sensitive.

The %NOTFOUND is a keyword to
signal the exit of the cursor

when the condition occurs
that there's no more data

in the cursor itself.

So now we're looping.

We're telling it to exit
whenever we don't find any more

data in the cursor.

So now let's do the work that
we intended to do with it.

dbms_output is an
Oracle-supplied package

that basically prints data.

So here I have a string literal
for employee, double bar that

symbolizes
concatenation-- so we're

going to concatenate this
string to a variable.

All right, so what we're
doing in this case is

we're looping
through this cursor,

and each time a
new row comes in,

it has new values for
lv_name, job, and hiredate,

because they're fetched
into the active cursor.

And then we're using
dbms_output_line to just write

these strings concatenated
with the variable.

But because of the
nature of the strings

we put in here and the
data that it goes with,

we can see some logic
that comes from this.

So now we need to end the loop,
and then end the statement.

So server output is on.

Let's highlight it
and attempt to run it.

So notice we get the
notification anonymous block

completed.

See this a little better.

And so it fetches the cursor
and goes through each row of emp

and gets ename,
job, and hire date,

and it concats it
with the string.

So we get some sort of
useful information from this.

So based on the information
in the emp table,

we could say employee
Smith works as a clerk

and was hired on this date.

Employee Allen works as
a salesman-- et cetera,

et cetera.

And it does it 14 times
to go through every row

in the ename database.

And so that's the basic
structure and operation

of an anonymous block PL/SQL.