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
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.