All language subtitles for [SubtitleTools.com] Procedures - 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 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.

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