Procedures

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.