Original subtitles

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.