Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
Hi. Welcome back to Muzzle Five. This is less than 5.9 Transactional Procedure One. And this. Listen, we're gonna begin implementing a transactional procedure that will span two lessons in our migration files so that when we migrate our database, we get this procedure as well.
00:17
Now, let's go ahead and go over the two concepts that we're gonna be using. In this lesson, as a review, we're gonna be using transactions. We can use transactions to process logical batches of updates and inserts so we can set up a logical batch of inserts, maybe three or mawr two or more
00:36
that all need to succeed or they all fail
00:39
Now. You can also use rollbacks for segmented logical batches within the procedure. But we're just going to use one transaction that needs to do multiple inserts. And it's going to be all or none. Either all the inserts work or none of them work,
00:54
and a procedure is similar to a function. However, unlike functions in my sequel, they permit transactions and they can return multiple values. So we're going to be using procedures to capitalize on the fact that they permit transactions
01:08
now with that, said Let's get started.
01:11
I welcome back to the development environment I've already designed the code that will implement the procedure that will add a user to our courses are user underscore courses table. So let's go ahead and step through this line by line and discuss everything that is going on here because it is short. But there does seem to be some complexity in here,
01:30
so the procedure name is add user. To course, it's accepting some parameters. School I D. Big manager unsigned, So we know the school. I D is probably referencing the school idea on the school's table, and that is of a data type begin unsigned. You want to be consistent
01:48
with your data types across your procedures, your functions in your tables
01:52
when there is a reference to each other. And that's because if you, for example, made this just an imager and then an energy that was the size of a big in injure like a you know, a 1,000,000,000 something was passed in Well, what my sequel would do is it would truncate that into a smaller number and then a user of this procedure, my end up
02:12
adding
02:13
data records to the wrong school without realizing it, which would get very confusing for everyone involved.
02:20
So the next parameter is the user i d.
02:23
And the next is the type ideal Biggin unsigned. And then we have a courses
02:27
and with a data type of bar chart to 55. So here is where a little bit of the coating the magic is happening. Now the I. D. On courses is a big and unsigned, so it's not a string. So what is going on here?
02:43
Well, my sequel doesn't have a way for us to pass in an array. So Honore, in a lot of languages would look something like this like one,
02:50
23
02:53
And because my sequel doesn't have a way for us, the passing rate we're going to use a string.
02:58
We're gonna use a comma delimited string 123 like this
03:01
now, because we're don't have some natural occurring array
03:07
options in this programming language area right here within my sequel. We have to do some of our own creative programming to get around that,
03:19
so that we will discuss that soon as we get down to it.
03:23
So at the top were declaring some variables loop count, course count both imagers. Course I D. And next I d both big in injures, unsigned
03:32
and the next line of code. We are testing the length of courses if the length of courses is greater than 2 55
03:40
Actually, we're gonna go ahead and change this because our max length is 2 55 So we're going to
03:47
make it one
03:50
smaller. So we're gonna say to 54
03:53
we're say to 54 is the max length
03:55
because what would happen if someone passed in a string of 300 is it would get shrunk down to a size of 2 55 So if we just test for 2 55 we would pass even if the string was larger than 2 55 when it entered the
04:13
procedure. So by doing this, this will get shrunk down to 2 55 If it's larger and then it'll fail this test because 2 55 is greater than 2 54 and we'll tell the user that to 54 is the max length and we will leave the procedure based on the P. R. O. C. Label that occurs here.
04:30
So my secret will check. Where does this label occur?
04:32
The curse here, the being of the procedure. I need to leave the procedure
04:38
so nothing would be done. At that point, the procedure would just exit
04:42
on the next couple of lines. We have the course count being set to the select length of courses, which was, ah, parameter that came in. It's that string again,
04:50
minus the length of the replaced courses.
04:54
They creates the course count. Now it's OK if this looks a bit arcane to you.
05:00
Ah, a lot of programmers. If they were just walking up on this code and they've never dealt with it, they would not immediately realize what's going on.
05:09
But a quick task is to take out a line of code is, say, 123 So I know what the courses is going to be.
05:16
So I'm gonna replace it, make sure they're both the same cause. That's how it's being used in the procedure.
05:23
And I'll select this and run the code. I get a to back.
05:28
Let's add one more,
05:30
make sure we added to both.
05:34
Okay, so I expect a three back and I get a three back so it's measuring how many elements exists in the limited string,
05:43
and three is correct. If that look strange to you, that's because we actually start counting from zero. So zero
05:48
123 And if we count these, it's 1234 And this is something that occurs in a lot of programming environments is the fact that we started zero. We don't start at one because if we did start at one, then it would look like this.
06:03
What we don't waste. So we started zero.
06:08
So let's go back to the,
06:10
uh, procedure.
06:12
I then set the loop count to zero.
06:14
I am then starting a transaction. So we're starting a transaction because we accepting multiple courses via string.
06:21
So we're gonna take the stance of either they all will be successful or none of them will be successful.
06:29
And that's why we're using the transaction
06:31
on the next line. Because set the next underscore I d equal to a police call off the I. D column from the user courses where the school I d equals the school I d. That was passed in. And this is to respect this code right here is to respect the composite key that we made with the school I d.
06:48
Because it is possible that we would have the same user taking
06:53
the same course at different schools so user to at one school is not the same as user to at another school.
07:00
And that's part of that composite key.
07:03
And the Kohli's function is used basically to prevent one condition. And that's you just brought this application up and you're running this for the first time because
07:14
on the first run,
07:15
with no records on the table, I d is going to be no, which is going to make the answer to this selection? No, and we can't use Nola's the next i d. It needs to be a number. So police protects us because we swap in a zero. If it comes back, is no.
07:31
On the next line, we have an ad underscore courses label. We've labeled this loop, and with that label we can leave the loop when the time is appropriate and when is the time appropriate? In this case, it's when the loop underscore. Count variable is greater than the course. Underscore count variable.
07:49
If this ends up being true, then we leave this label. The my civil database engine will look for this label. See, that's attached to a loop and no to leave the loop.
07:59
Generally, in all programming environments, you want to have control of your loops, and you want to have control when you leave your loop to prevent the outcome of an infinite loop and infinite loop. Burns Resource is can crash an application and can cause a lot of problems. Some environments will automatically terminate an infinite loop if it's detected,
08:18
but you definitely want to have control and avoid infinite loops whenever possible,
08:24
which should be pretty much all the time.
08:28
So these two variables the loop count, of course, count our incriminated at the bottom. So we have Luke. Count is equal to Luke Count plus one. Next I D is equal to next I d plus one.
08:39
And on the next line we set the course I d equal to a sub string index.
08:46
Two of those of the courses variable plus the loop count. I can tell this again the course I d from the courses that came in as a parameter, but it's kind of archaic to me or are came to me as well. So I'm gonna take it into a script pain
09:00
and running by itself to see how it's working. So I'm gonna go ahead and copy this because I know this is the type of string that would be passed in for this variable. And Luke count is gonna be an ANC remaining variable in that loop. So it first it's gonna be zero. So let's me go ahead and run that.
09:16
So I get one.
09:16
All right,
09:18
changes to a five.
09:20
Because I suspect when I implement this loop count by one, I'm gonna get five,
09:26
and I do get five. And then if I increment this again, I should get three. Because we're going through this.
09:31
Very We're going through this course I d String course by course. 15 and three.
09:39
All right, so that is what that is doing.
09:45
And on the next line, we then use the data that we've gathered and we inserted into the education dot user courses table
09:54
with the data that's been derived from various components of the code above. For example, the course I d gets inserted
10:01
right here. The user i d the type I D the next idea.
10:07
Then we have the current time stamps and the school I D. Of course.
10:11
And then at the end, we go through that increment blocks that we set Luke count equal to loop count plus one. Next I D to next I d plus one,
10:20
we end the loop,
10:22
and then if everything successful, we commit and we end the procedure and we're done,
10:26
so this code will be available for download. So feel free to review it and run it to make sure that it's working the way you expect it to work
10:35
and in the next lesson will implement this in our migration files. And we will also change a couple things in our database that need to be changed that were missed in the previous lessons. So let's go ahead and head over to the summary,
10:50
and that brings us to the 5.9 summary and this lesson. We discussed the scope and purpose of transactions. We discussed the scope and purpose of procedures. We began implementation in our migration falls. We did not complete that, however, will complete it in the following lesson.
11:05
At the end of the next lesson, we should have a procedure in attract transaction that as a user and it's type two the indicated courses, all the in church should succeed or they should all fail. And that completes this lesson I hope to see in the next Thank you.

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor