7 hours 36 minutes
all right, Welcome back to Model Four. Sequel programming to this is lesson to sequel procedures were, in sub lesson 2.3 procedures to the previous lesson. We started coding, a procedure that dealt with adding a salary to the salaries table within the employees database.
And in this lesson, we're going to finish that procedure
now, just to recap that procedures needed to do a couple of checks before inserting and updating a salary
it needed to check that the employee exists. We don't want to add salaries for employees that do not exist.
It did that by doing a simple query to find the employee I D and Employees Table.
And then, if that was no throwing an heir.
And it also needs to make sure that the dates do not collide with any previous salary data. So we don't want an employee who makes multiple salaries during the same date range.
Let's start quoting that so we need to set
date test, which is the variable we have at the top,
Star. Now that function will simply return a number that represents how many records we got back. So it's a count
EMP underscore Number equals EMP
from dates from the table and to date from the table
the end a variable was was defined up here. So we're making it a requirement. The end dates not equal to this number. And we have to pass that in because if we excluded this and just ran this pretty much, all our checks would fail
because they would all exist before this state and after the current salaries date.
So once we have that count, we need to check to make sure it's not greater than zero. So if date
test greater than zero,
and I'm gonna use code 1103
collides with date, range already and table. Now, of course, you could type in any air message that you want, but it's usually a good idea to provide a meaningful air message that will allow programmer to realize what the problem is and hopefully resolve it.
Because sometimes a problem could be
just simply a typo.
I've done plenty of those
we need to go ahead and provide the updates statement.
and the update statement is not too complicated. You'll notice that a lot of these procedures and functions end up being a lot of statements that by themselves, are not complicated, but added together make kind of a complex idea that gets executed, update salaries
equal to the from date that was passed in
equals. And I didn't know what would happen if we made a mistake and forgot that
what we would update every employee,
every employee salary record incorrectly, and that would be pretty bad thing.
So it's always good to have a test at the base
and to make sure things are working correctly
and always have a way to back off in case you make a mistake.
So update salaries set to date equal to the from day that was passed in were amp number equals EMP. Underscore I d. And two day equals and dates.
There you go.
And next we're going to insert and we're gonna go ahead, then
get that statement out a little bit. So we're gonna generate sequel by right clicking on this table, selecting, insert,
grabbing that with a copy and just pacing it in
on Dhe here. We need to Paride the EMP.
you provide the salary amounts
and this will be the from date that was passed in. Don't accidentally typed the same from date from the table. You'll get a whole value if you do that.
Um, and end date. So there we go. That should be completely See if that runs, See if I made any errors
and Iran Great success. Now, if you have an heir, the things that check is to make sure you put this semi colons at the end of each statement,
make sure you didn't forget the comma here. Make sure that your sequel statements are written correctly. I have one instance where I have forgotten the e here. So I had that which is incorrect, and it failed to resolve when I ran it.
So be sure to look around closely if you if you don't If you're create statement, fails to run
with that said, let's go ahead and left. Clicked on the procedures. Refresh.
There's a procedure
we're going to generate the sequel, the call sequel.
So we're going to copy that
We're going to go into our sequel editor, new sequel editor.
And we're going to call the procedure, so we need to replace this stuff first. Gonna try if it doesn't work.
So native five,
See 2019 0 one. Dash 16. January 16 2019 was run this. We should get the semicolon. We need that. We should get the employees. Doesn't exist.
Okay, employee, I d does not exist in a bloody table. Okay, Perfect.
Now let's take a look at the cellar stable. 10 001
on DA. We have a date that ends on the
Actually, this guy is a little bit bad. I've kind of messed him up, so let's change to another guy.
Okay? So his last several salary was on 2001 is related to
Okay, 10 002
And we're going to say he has a salary that starts on the first of
January 2019. Let's go on and run this.
It's missing a zero. They're running again.
And that's why we got that air catching. Made a mistake. I would have put in a salary for an employee that didn't exist, so it ran. Now, if we run it again, we should get the date collision check. Because we set the same dates.
Day provide collides with date range already in table. So that works.
Can we go back to,
uh, the data we filter
by the guy were interested in. And that's the employees table. When you go to salaries table
on, we already got that filter we hit. Refresh.
And there's our new salary. We updated his previous salary correctly
to the to date. Now is the from date from the one we added.
So let's go ahead and try it one more time just to be certain that we're doing this right. So we're gonna say, Let's do a edge case. We're gonna start on the next day.
Gonna run that
Iran running again.
All right, day collision. Okay, good, Good.
Let's go ahead and re first. That data
And there we go. It worked again. So this procedure is now working. Um, I hope you got it working. It was a little complicated there, but each statement by itself is not that bad. If you look here,
this is just an If statement. We were throwing an heir.
If this result is not what we expected when we expect to get an imager back. So if it's no, you know, we didn't get a result back.
The sequel statement by itself is not is not complex by any means.
The day tests the date. I mean, the date underscore tested a little more complicated, but by itself again, you're selecting from one table no complex joins or sub queries or table expressions. So that's not too bad. And again, if you don't get what we expect, we throw in air.
And finally, if everything works out, we update the salaries and we insert into the employee's salary table. The data. They came through the parameters. Now
there are a couple issues with this procedure still, and we will discuss that in some upcoming lessons and really seal the deal for making sure this procedure is complete. And we will discuss those features again in an upcoming lesson. Now, I hope you got this working. I hope you're able to see ah, how we can control
data entry using a procedure
and how they could be very useful to use.
And I hope to see you in the next lesson. Thank you.