Tip:
Highlight text to annotate it
X
Once again welcome to ITSE 1345, this is Competency 2. This is the first big event in 1345. And
actually we spend quite a bit of time, actually of all the competencies we will do this is
the one that we're going to invest a lot of resources a lot of time to get done, because
this sets the foundation for everything that's going to get done and you have to have a very,
very strong foundation. Now, remember I told you, in ah, one of the to her ones that you
have, you're going to export and you're going to have some kind of mechanism that's going
to hold the data. Ok, so you're going to have some sort of mechanism that's going to hold
the data, so you have data. Ok, so you're going to have a query and you're going to
hold the data. Now, what Chapters 1,2, and 3 talk about is
the mechanism, the structure, that will hold the data. Now, if you'll remember back in
that payroll scenario, I listed about 110 different items that we're going to have to
track. Alright, well, okay so now what we have to look at is the type of placeholder,
that we're going to have to consider to hold this information. So Chapter 1 talks about
how you develop this mechanism that's going to capture this information. Now it also talks
about some of the theories of PL/SQL, now those of you who are familiar with SQL, you
know, you know that that its kind of not that user friendly because every time you want
to do something you have to type something in.
You cannot really call a program to do the work for you. You know every time you do it
you have to sit there and hot the keyboard to make it work because its really it's not
that user friendly, usually what you wind up doing is saving all your code in Notepad
then copy paste from Notepad to make it work. well, why PL/SQL does it allows you to take
that code and store it as a program that you can give a name to.
So part of the mechanisms in chapters 1, 2 and 3 is to develop an idea that you're going
to create a program you're going to give it a name, you're going to store it and you're
going to call that program back to do some work. Now, in that process also there is also
a on the fly mechanism you really don't have to give it a name you just create it and that's
called anonymous block. We're going to look at a structure that hold the data. Now when
I talk to you about the data, there is a fundamental building structure that is used in
PL/SQL, and it is called the Block, OK? Now within this block, that is how everything
happens. So when I told you had to develop a structure to hold the data, the block is
that structure. Within that block you have what they call the declare section, you have
the begin, you have the exception and you have the end. Now if you remember in that
intro video I told you the exception,is that item
you find, you trap things that are outside the normal things that you are looking for,
it could be a mistake, or it could be an issue where its really not what you are looking
for. Now, in the declare section, this is where
you are going to have to develop the placeholders that are going to hold the data, so you run
the data, and you have to hold it here. Now once you hold the data you have to process
that date through some event. and this is where the event is. So this is where the event
is that happens that will do the work. But this is the placeholders that are going to
hold the information now, I told you that you needed to know select from and where.
Ok, so this is your stander SQL scenario: Select, From, Where. So this is where you
put the select from, where. You put the select, from, where in here. Now this will extract
the data, this is the place holders and then within this process is where the work is actually
done. Then when you get through you exit and then you end. The Block structure is the ore
of everything we are going to be working with. Now, that said lets get back to the place
holders. When you were learning languages, when you
were learning English, do you guys remember big, bigger and biggest? That was a progression
on precision. Ok, so you had progression on precision, big, bigger, biggest. OK, and then,
then you wind up with good, better and best, which is a little kind of out of the norm
because here you have the "er" here you have the "est" to show the increase in precision,
which kind of violates the idea of increasing precision. Well those idiosyncrasies are common
to all languages and no different when we have to learn with while we are working with
all the precision and the variables that we have to deal with. Now the other thing that
we have to look at is, when you have a, over here in the database, when you have a table,
the data is stored in some kind of order, or you have some kind of index. That data
is, is used. So when you extract a whole bunch of data ingot he PL/SQL side, how are you
going to organize it on the PL/SQL side. Remember I told you that you have to create a mirror
image of the database? in the intro part of it? So if you have to create a mirror image
you also have to create a mirror structure that will also organize the information after
you extract that information. So the structure that is used, on the database side its called
an Index on the PL/SQL side we call it a collection. So you have a similar concept as Indexing
its called collection on the PL/SQL side and then that collection is haw you organize the
information that you have extracted. now once again, when extract a lot of data, you need
a big place to hold it. Now on the database side we know its called a table. Well, hmm,
you also need a mechanism that will mimic a record, from the database side into PL/SQL
you will also have a structure just like you have one record, over here you have a table
with multiple records, you have to develop a placeholder mechanism that will hold a bunch
of records in a virtual type table mechanism. Now as we're doing this understanding that
you also have to develop indexing, so if you're going to extract 100 records you're going
to hold 100 records over here, you're going to have to sequence all those items. So We
have a sequence generator that we're going to have to consider.
So competency 1, I'm sorry, Competency 2—Chapters 1, 2 and 3 speaks to the idea where are you
going to store information, one. Once you store it you have to develop a structure which
is the block, now when you're going to process data, you have to develop the structure and
the placeholders and the precision. Now in PL/SQL this concept of precision is called
scaler—so when you look at a scaler event, you are looking at the idea of developing
a mechanism that has levels of precision. Now, well actually to kind of wrap this thing
up for Chapters 1, 2 and 3, what we are looking at is 1) the Block structure, that is critical.
2) the variables that are going to hop that and 3) the definitions that are given to the
variables that are going to be held in that block structure. And that concludes competency
2.