Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.