7 hours 36 minutes
I welcome back to model. Foreseeable. Programming to this is less than two simple procedures were currently in the first sub. Listen, 1.2
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
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,
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.
However you cannot use typically cannot use functions to, ah manipulate database data, which is the feature that procedures have.
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
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.
when we accept those parameters, when I need a check,
that employee exists in employees table. We do not want salaries in the salary table
that do not belong to a national employees,
and we're gonna throw in air. If that happens
next, we're gonna need to check that thief from date makes sense.
So we will need to check that the date doesn't clash with other previous date ranges and that this employee,
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
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.
So with that said, Let's get started.
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.
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
for the current and the previous salary or the
salary before the salary that you're looking at
are the same date. Now. That's not necessarily a great way to, ah, lay out a salary table.
In fact, I would not do it this way because on
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
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.
Of course, you have to know what they've decided to do and you can't just read it.
And the axiomatic A database. If you actually look at Thea
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.
The other thing to notice is that the current salary always ends in this date, which is four nines death 0101
time. So we're gonna have to program this into our procedure.
So let's get started
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.
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.
Next, we used the delimit er and this is so it doesn't clash with the end statement right here.
And then we switched the eliminator back after that.
So we run next, we run the crate procedure with the procedure name, which is add salary.
We provide three parameters. Thea impiety the salary amount and the from date
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.
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,
um, that the from date
appears between other dates in the salary table making sure that dates don't clash with the previous state.
Next, we're going to declare an end date
We're gonna set that end date to the UN date That's used for the, um
uh, the last
date in the salary index of the employees.
So this is so we don't have to type this throughout our procedure. We could just call the variable
next. We need to run our first
test and This is a very simple sequel statement.
We select EMP number from employees
were EMP underscore number equals amped on the score I d and underscore idea comes from it, er
so we're passing that in
and what we're doing is we're actually mutating or changing the parameter,
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.
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,
we're going to write
If EMP underscore, i d is no.
now the reason we do is no
is because if this does not find the employee, I d.
This value will become don't cause it will say there's nothing here.
And it will put nothing in the AMP. I d. Which is represented by this idea. The idea of being no.
we're gonna put signal
you smell right signal
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
we're gonna set
equal dropped down.
Don't say employees
Does not exist.
An employee table.
we'll put a comma.
The signal sequel State and the My simple air number is the method we're using to catch airs. In our procedure,
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.
So after the message we need to provide the air number, that's
to type to provide the air number.
equal 14 31.
We're going to end if
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.
after we declare variables and said are variable,
we're setting the employee i d to a query where we check if the employee exists in employee table.
If this is no comes back empty,
we then throw this air with this message,
and then we end that if
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
and finish the procedure out.
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.
Penetration Testing and Ethical Hacking
The Penetration Testing and Ethical Hacking course prepares students for certifications, like CEH. This course ...
7 CEU/CPE Hours Available
Certificate of Completion Offered
This free course introduces the student to intermediate concepts found in the implementation and application ...
10 CEU/CPE Hours Available
Certificate of Completion Offered