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

af Afrikaans
ak Akan
sq Albanian
am Amharic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.