All language subtitles for [SubtitleTools.com] What Is PL And SQL- - Learning Oracle 12c [Video]

In this lesson, we're going
to take a look at PL/SQL.

So what is PL/SQL?

And what do we mean?

How does that relate
to SQL, for instance?

Well, PL/SQL stands for
Programming Language/Structured

Query Language.

So these are programmatic
extensions, just SQL,

to the SQL language.

So why would SQL need
programmatic extensions?

SQL is a 4GL, or
Fourth-Generation Language.

It's designed to be
closer to natural language

and easier for a user to learn.

But a fourth-generation
language that

is that easy to manipulate
and use, as well as learn,

doesn't have all
of the extensions

and the functionality that a
programming language may need.

For instance, if you want
to do if/then logic in SQL,

that's difficult to do.

Things like looping
over variables,

type variable control,
all of those things

are difficult to do in SQL.

And it really wasn't
designed for that.

But what was designed
for that is PL/SQL.

And PL/SQL is a full
third-generation language.

It uses constructs, such as
variables, conditional logic,

iteration, all of the things
that we think of in terms

of a regular programming
language, if you will,

one that we're
more familiar with,

third-generation
languages like C and Java.

PL/SQL is appropriate
for data applications.

So one of the truly
remarkable things about PL/SQL

is how well it processes the
data layer in the database.

So any application has
many different layers.

They may have a
presentation layer,

which is what the user
sees, a logic layer,

where the programmatic
thinking happens.

And then you have
the data layer,

where the data is actually
retrieved and sent

back to the other layers
and eventually to the user.

PL/SQL is extremely useful
in the heavy lifting that's

done at the data layer if
you're using an Oracle database.

PL/SQL is what we
call block-structured.

So it has and is made up of
a number of different blocks

of code within any given
piece of PL/SQL code

that each do a
specific function.

And those work in
concert together in order

to get the job done of
the overall PL/SQL code.

It's important to
understand that PL/SQL

is Oracle-specific, which means
it runs on Oracle databases.

Whereas SQL is an ANSI
standard language that's

used in many different
relational databases,

PL/SQL is specific to Oracle.

So there is no way
to take PL/SQL code

in any kind of persistent
way, if you will,

and move it over to
an IBM DB2 system.

You could take it and rewrite
it because it would be similar.

But you can't take it code
for code, line for line,

and move it over to
SQL Server or DB2

or any other
relational database.

It's Oracle-specific.

We should probably
also understand

that even though PL/SQL
is specific to Oracle,

a lot of those
different RDBMS systems

will have their own version
of something like PL/SQL.

For instance,
Microsoft SQL Server

has a 3GL called TSQL that
performs the same kinds

of duties that PL/SQL does.

Finally, PL/SQL
gives us the ability

to have something
called persistent code.

And persistent code
means code that

lives inside the database
for our purposes.

So we can create pieces
of code in PL/SQL

and actually store
those in the database.

But we don't have to store
them in the database.

We can use PL/SQL as
a scripting language,

almost, to run a script
from the file system,

for instance, that
does that PL/SQL work.

So let's take a look
at what's called

an anonymous block of PL/SQL.

So anonymous block
PL/SQL is more

of the scripting
type of language

that we're talking about.

Here, we say that it
is block-oriented or

block-structured.

And so we have the beginning
of a block here called DECLARE,

where we declare all
the variables that

are going to be involved.

So here we just have
a character variable

that contains the
string Hello World.

And then BEGIN starts
the next block.

And then we use a
call to dbms_output

that just prints this variable,
which contains Hello World.

And so when we run it, it
prints out "Hello World."

And then the last is the END.

Another block that you don't
see here, which is optional,

is the exception block, where
we can write exception handlers

in the code.

So this would be
anonymous block PL/SQL.

The other kind of
PL/SQL we can have

is what's called a
named program unit.

This is the persistent code
that we were discussing earlier.

So a named program
unit is actually

stored in the database
as a database object.

And it can be manipulated, just
as a database object would be.

So we say something
like CREATE PROCEDURE.

And then we give it
a name, hello_there.

And then we have
variables for parameters

that come in to the procedure.

So we're passing a value
into the procedure.

And then it does its
job and completes.

So to call a procedure
called hello_there,

we use the execute commands.

We say execute hello_there,
which is this procedure,

passing in the value Fred.

Fred goes into a
variable for p_name.

And then when we make the
print call, if you will,

it says "Hello there" and then
appends the p_name variable

to the end of it.