Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
well when a module seven. This is less than 7.2 procedures. Procedures are similar to functions in that they are code that perform specific tasks. There's an S on there because they could be doing multiple things, such as multiple batch inserts with multiple rollbacks or commits procedures. Also foster code reusability.
00:20
They help avoid copy paste programming
00:23
and transactional uses permitted. If you want to commit changes to the database or manipulate the database, procedures are a good route to go. They can return multiple values, and they can. You can use when you need store code to manipulate database data by those transactions.
00:42
Now, with that said, let's go ahead and take a look at a procedure in the development environment. Welcome back to the development environment. I've switched back to the education database that we developed in the earlier lessons, and I did that just because the tables in that database are a little bit easier to work with because they have a smaller column set.
01:00
If you were to work with the table with the larger column set, then of course you would need to add more parameters to manipulate the columns that you needed to manipulate.
01:08
So in this example, we have an ad room procedure in which we are
01:12
doing something new that we haven't done previously and procedures in this course and that we were using in variables and out variables. Now, when you don't designate a type in is the default. So in the previous procedures examples that we have used where we don't designate a type and was being used even though we weren't indicating it.
01:33
However, in this one we are using in and out.
01:37
So we take a look at the parameters being passed. Then we could see we have a in school i d a in room name, a incapacity and a in building
01:48
followed finally by a out new I. D. So the idea is that you add a room to a building using this procedure and a variable that it passes out to the calling program is the idea of the room that it just created,
02:02
so that could be useful in some application code. If you're creating rooms and you want to know the I d off the room that was just created by the database.
02:12
So we go into the procedure, we can see that the new I D, which is also the out variable, is getting set to a police off the maximum i d. From the rooms table where the school I D is equal to a school I d passed in. And that's to respect the composite key and allow,
02:29
uh, multiple ID's at different schools to occur, because the school I D and the I D
02:35
are the primary key. And then we insert the values provided as well as the drived i D and to a new room record. Now, one thing that you might notice is that a transaction is not being used this procedure, and that's because we're not doing multiple things in this procedure were inserting one record
02:53
and we were doing multiple inserts.
02:54
Then it might make sense to use a transaction. But if this insert fails, there is not any other inserts that might have succeeded. The one insert that was supposed Teoh occur failed, So nothing happened. And if it's successful, then it was successful.
03:10
Now, when this, after this procedure exits that out, variable will have a value that is available to the calling program. So let's take a look at how that works. Well, go ahead and run this.
03:22
It's to make sure it's in my database correctly.
03:27
I'm gonna go to a another script. Pain
03:30
close this.
03:31
So I'm calling that procedure with the variables.
03:35
I have this at I D, which is a variable declaration in my sequel. And then I'm selecting at I D. So I should see the value that gets generated in that procedure come out by that selection statement. So let's go ahead and run that
03:50
103
03:52
104105 so we can see how that's working.
03:58
So the interesting notes in this lesson as well as the fact that we reviewed procedure, is that where we are also using the end out variables that are available in my sequel procedures. We passed in some variables which just go into that procedure and are used in some way. And then we pass it in out parameter
04:15
that goes into the procedure and then comes out of the procedure with a value
04:20
so that completes this pushing a lesson. Let's go ahead and head over to the summary, and that brings us to the 7.2 summary and this lesson. We reviewed the purpose and scope of procedures. We implemented a procedure without a transaction and we implemented it using in and out parameters.
04:36
So that completes this lesson, and 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