Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I welcome back to model. Foreseeable. Programming to this is less than two simple procedures were currently in the first sub. Listen, 1.2
00:07
procedures. One This lesson. We're gonna take a look at procedures and we're gonna implement one that adds a salary to the salary table. Appear
00:15
now what is the procedure? Procedure is similar to a function they both process procedural sequel code. Our function is normally used to return a value to the collar,
00:26
like in the previous example of functions. We processed a string field and returned a first name or last name, depending on what the caller asked for. Now, procedure is very similar, except it's typically used to update database data and not return values. Although you can use it for that.
00:45
However you cannot use typically cannot use functions to, ah manipulate database data, which is the feature that procedures have.
00:53
So in this lesson, we're going to add a salary to the salary stable. But we're going to do a couple safety checks. We're gonna pretend this constraint is not here because a lot of times you'll run into a database where you know it's not there. So
01:07
when we add a salary, We're gonna accept three parameters. We're gonna accept the employee number, We're going to accept the salary date, and we're also going to accept the from date.
01:19
Now,
01:21
when we accept those parameters, when I need a check,
01:23
that employee exists in employees table. We do not want salaries in the salary table
01:30
that do not belong to a national employees,
01:34
and we're gonna throw in air. If that happens
01:36
next, we're gonna need to check that thief from date makes sense.
01:40
So we will need to check that the date doesn't clash with other previous date ranges and that this employee,
01:49
um,
01:49
has a period of time where they earned two salaries. We don't want that now. This is going to be an ad salary function. There could very well be other salary functions that would more or less complete the salary table. Logical
02:05
procedure groupings, for example, weak. Right now we're making the ad salary function. There could have been a remove salary function and an edit salary function, each serving that specific purpose, each having its own checks to make sure that the data in the tables makes sense and follows business rules.
02:23
So with that said, Let's get started.
02:27
So we're gonna be using the employees database, and we're gonna be using the salaries table. So if you give that a double click and get into that table, you can click this filter item filter down to a specific record, um, of interest. So, for example, I I chose Georgie.
02:44
Now, if we look at his results, we can see that there's a couple of things to be aware of in this database, and that is the from date and the to date
02:53
for the current and the previous salary or the
02:58
salary before the salary that you're looking at
03:01
are the same date. Now. That's not necessarily a great way to, ah, lay out a salary table.
03:07
In fact, I would not do it this way because on
03:10
1989 06 25 it would appear that he had two salaries. So if you ask me, the question said, What was this guy's salary on this state? I wouldn't really know how to respond because I would get to answers back. Sometimes this is handled in the application layer because
03:30
the person programming the application knows, Oh, I'm always just gonna use the highest ah, date value between the two as the ah for that date.
03:38
Of course, you have to know what they've decided to do and you can't just read it.
03:42
And the axiomatic A database. If you actually look at Thea
03:46
salaries table, you won't see an instance where you could say that an employee had ah two salaries at the same time, which is a better way to do it.
03:55
The other thing to notice is that the current salary always ends in this date, which is four nines death 0101
04:04
time. So we're gonna have to program this into our procedure.
04:09
So let's get started
04:11
now. I've already laid out a starting template, but don't worry, we're going to code the main meat of it together. But I wouldn't walk you through this starting code as well.
04:20
So first we drop the procedure. If exists, we're calling it add salary. We do that just because as we're working through it, we want to be able to run this statement and not worry that you know it already exists or it does not exist.
04:33
Next, we used the delimit er and this is so it doesn't clash with the end statement right here.
04:41
And then we switched the eliminator back after that.
04:45
So we run next, we run the crate procedure with the procedure name, which is add salary.
04:49
We provide three parameters. Thea impiety the salary amount and the from date
04:57
in brackets, we have the begins statement. We also have the end statement. Remember, You need all these. These are syntax rules, but one of these air missing me. Ah, the function will course fail.
05:08
Next, we declare two variables at the top. We declare a date test, which is an end. We're going to use that to count the number of dates that pier,
05:18
um, that the from date
05:20
appears between other dates in the salary table making sure that dates don't clash with the previous state.
05:28
Next, we're going to declare an end date
05:30
date.
05:31
We're gonna set that end date to the UN date That's used for the, um
05:38
uh, the last
05:39
date in the salary index of the employees.
05:43
So this is so we don't have to type this throughout our procedure. We could just call the variable
05:49
next. We need to run our first
05:53
test and This is a very simple sequel statement.
05:56
We select EMP number from employees
06:00
were EMP underscore number equals amped on the score I d and underscore idea comes from it, er
06:04
so we're passing that in
06:06
and what we're doing is we're actually mutating or changing the parameter,
06:12
but it's okay. We're changing it either to the same thing that it ISS because we're selecting the same thing from this table.
06:18
But if we get nothing back, that means this number doesn't exist in this table, and we will run a test for that. So in the next line,
06:28
we're going to write
06:30
If EMP underscore, i d is no.
06:33
Then
06:34
now the reason we do is no
06:36
is because if this does not find the employee, I d.
06:42
This value will become don't cause it will say there's nothing here.
06:46
And it will put nothing in the AMP. I d. Which is represented by this idea. The idea of being no.
06:54
So then
06:55
we're gonna put signal
07:00
you smell right signal
07:02
sequel, state
07:06
H Y, and there will be a link to the air Koji can use, um, you could pick the air codes that you want to use but it's good idea that pick ones that makes sense to what you are. Ah, indicating the problem is
07:17
we're gonna set
07:19
message.
07:23
Text
07:26
equal dropped down.
07:30
Don't say employees
07:31
i d.
07:33
Does not exist.
07:36
An employee table.
07:42
Okay,
07:44
we'll put a comma.
07:46
The signal sequel State and the My simple air number is the method we're using to catch airs. In our procedure,
07:53
you can look up the numbers you can use on the my sequel documentation. I will provide a link to that documentation online as well.
07:59
So after the message we need to provide the air number, that's
08:03
to type to provide the air number.
08:05
My sequel.
08:07
Air number
08:09
equal 14 31.
08:13
We're going to end if
08:16
now let's walk through this again just because we have a couple of things going on. And then we will pause this and put the rest in the next lesson.
08:24
So
08:26
after we declare variables and said are variable,
08:28
we're setting the employee i d to a query where we check if the employee exists in employee table.
08:35
If this is no comes back empty,
08:37
we then throw this air with this message,
08:41
and then we end that if
08:43
now in the next lesson. What we're going to do is we're going to do the date test, and if the date test passed, we will update and insert the salary info
08:52
and finish the procedure out.
08:56
So I hope you enjoy this lesson. But what? We didn't finish it, so there was nothing to get working on. The next lesson will finish it out and get it working. I hope to see you there. 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