Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
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.
00:18
And in this lesson, we're going to finish that procedure
00:20
now, just to recap that procedures needed to do a couple of checks before inserting and updating a salary
00:27
it needed to check that the employee exists. We don't want to add salaries for employees that do not exist.
00:34
It did that by doing a simple query to find the employee I D and Employees Table.
00:40
And then, if that was no throwing an heir.
00:43
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.
00:52
Let's start quoting that so we need to set
00:55
date test, which is the variable we have at the top,
00:59
equal to
01:00
select
01:03
Count
01:03
Star. Now that function will simply return a number that represents how many records we got back. So it's a count
01:11
from salaries
01:15
where
01:19
EMP underscore Number equals EMP
01:23
underscore Isay
01:25
and
01:26
from
01:27
dates
01:32
between
01:34
from dates from the table and to date from the table
01:42
and
01:42
two dates
01:45
not equal.
01:47
And Dave,
01:49
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
02:05
because they would all exist before this state and after the current salaries date.
02:13
So once we have that count, we need to check to make sure it's not greater than zero. So if date
02:19
test greater than zero,
02:22
then
02:25
signal
02:29
sequel state
02:31
and I'm gonna use code 1103
02:38
Set message
02:43
texts
02:45
equal
02:46
date
02:47
provided
02:49
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.
03:08
Because sometimes a problem could be
03:12
just simply a typo.
03:15
I've done plenty of those
03:17
we need to go ahead and provide the updates statement.
03:21
So
03:23
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
03:38
set
03:40
to day
03:43
equal to the from date that was passed in
03:46
where EMP
03:50
number
03:51
equals. And I didn't know what would happen if we made a mistake and forgot that
03:55
what we would update every employee,
04:00
every employee salary record incorrectly, and that would be pretty bad thing.
04:03
So it's always good to have a test at the base
04:06
and to make sure things are working correctly
04:10
and always have a way to back off in case you make a mistake.
04:14
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.
04:23
There you go.
04:24
And next we're going to insert and we're gonna go ahead, then
04:27
get that statement out a little bit. So we're gonna generate sequel by right clicking on this table, selecting, insert,
04:33
grabbing that with a copy and just pacing it in
04:38
on Dhe here. We need to Paride the EMP.
04:41
I d
04:42
you provide the salary amounts
04:46
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.
04:56
Um, and end date. So there we go. That should be completely See if that runs, See if I made any errors
05:06
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,
05:15
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.
05:28
So be sure to look around closely if you if you don't If you're create statement, fails to run
05:34
with that said, let's go ahead and left. Clicked on the procedures. Refresh.
05:40
There's a procedure
05:43
we're going to generate the sequel, the call sequel.
05:46
So we're going to copy that
05:49
We're going to go into our sequel editor, new sequel editor.
05:55
And we're going to call the procedure, so we need to replace this stuff first. Gonna try if it doesn't work.
06:02
So native five,
06:04
500
06:06
and Ah,
06:09
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.
06:21
Okay, employee, I d does not exist in a bloody table. Okay, Perfect.
06:26
Now let's take a look at the cellar stable. 10 001
06:30
on DA. We have a date that ends on the
06:36
Actually, this guy is a little bit bad. I've kind of messed him up, so let's change to another guy.
06:42
Okay? So his last several salary was on 2001 is related to
06:47
Okay, 10 002
06:50
So
06:53
10 002
06:55
500.
06:56
And we're going to say he has a salary that starts on the first of
07:01
January 2019. Let's go on and run this.
07:11
It's missing a zero. They're running again.
07:14
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.
07:28
Day provide collides with date range already in table. So that works.
07:32
Can we go back to,
07:34
uh, the data we filter
07:38
by the guy were interested in. And that's the employees table. When you go to salaries table
07:44
on, we already got that filter we hit. Refresh.
07:47
And there's our new salary. We updated his previous salary correctly
07:53
to the to date. Now is the from date from the one we added.
07:58
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.
08:07
Gonna run that
08:09
Iran running again.
08:13
All right, day collision. Okay, good, Good.
08:16
Let's go ahead and re first. That data
08:20
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,
08:33
this is just an If statement. We were throwing an heir.
08:35
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.
08:43
The sequel statement by itself is not is not complex by any means.
08:46
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.
09:01
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
09:11
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
09:31
data entry using a procedure
09:33
and how they could be very useful to use.
09:37
And I hope to see you in the next lesson. Thank you.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor