Functions

00:00

I welcome the module seven. This is less than 7.1 functions,

00:04

functions, air code that performs a specific task. We've been using the Native my sequel functions up to this point, such as Count some sub String as well as others.

00:14

In this lesson, we're gonna discuss user defined functions, which are functions that we define ourselves to use as we please in our programs.

00:22

User defined functions help foster co grease ability and help avoid copy paste programming.

00:28

Copy paste programming is when you take complicated code that you could put in a function but choose not to and instead copy it to different areas of your parent program or multiple reports, query reports. And the problem arises when, later on, you need to make a change to that code.

00:45

And now you have to find all those different areas where you copy that code

00:48

and make that change accordingly. And that creates a lot of opportunities for mistakes. For example, you could forget to change a part of the program, or you could actually make a mistake. Implement implementing it in one area of the program, so functions help you avoid that

01:03

something else to keep in mind about functions is that they're called per row, so large data sets are queries could cause a slowdown,

01:10

and it's really good. Depend on what that function is programmed to do and how well its program to do it. Because you can be very efficient and how you program a function and you could be very inefficient and how you program a function. A quick example would be not using an index

01:27

when you should have used an index that was available on a table.

01:30

Something else about functions is that they do not permit transactions. If you need to change database data. Perhaps the procedure is a better choice now, with all that said, let's go ahead and go to the development environment and implement a function.

01:48

Welcome back to the development environment part of some my sequel documentation to highlight a new requirement of creating functions in my sequel and previous versions of my sequel, you didn't have to do this, but in the updated versions there, there might be a way to disable it, but it's best to just leave it on and go ahead and implement what it's requesting.

02:07

So when you create a new function you have to define it is at least one of the following, which is

02:13

deterministic. No sequel or read sequel data, and that tells the my civil database engine how your function behaves.

02:21

For example, deterministic means that if your function is provided the same parameters,

02:27

then it will return the same value. So that allows the my sickle database engine to only need to call that function to compute it.

02:36

If you're parameters change. If you pass in the same parameters, it will return the same value. So if you run a query, which and remember that functions he had called per row and you return 1000 rows, that function gets computed 1000 times. However, if it's deterministic,

02:53

the and all those parameters on those 1000 rooms are the same.

02:57

The database engine can assume that it's getting the same value back and actually Onley compute that function one time, which can provide a significant performance boost, especially if that's a heavier function.

03:10

Okay, so let's go ahead and take a look at the function I wrote, which is based on a previous challenge. We're gonna go ahead and walk through it.

03:19

So at the top here. We have dropped function if exist department hours so that allows the script to be run over and over and over without worrying about it. Not working. That's because this drops the function. Then we create the function. If you get rid of this,

03:35

then of course they won't work, will get a object already exists There

03:42

we do a delimit er swap so that we can terminate the statement with a semicolon here and then at the very bottom we swap back to the semicolon Delimit er

03:53

if we don't do the delimit er swap at the top here when we use the semi colon in the statement, the bicycle database engine gets confused and it produces an heir.

04:02

So this query is based on a previous challenge

04:08

and I've modified it a little bit.

04:12

So we returning just the hours

04:14

and we're passing in two parameters. The company i D, which is an energy field.

04:18

The department I D, which is a bar chart 10. Now, why did I decide on bar chart 10? Well, I took a look at the table that I expected to come from, which was the E p department table.

04:32

So the department I D Field

04:36

on this table is a bar chart. 10. So that's why I decided to make the department I d parameter of our chart in

04:44

something else to be aware of when defining functions or procedures is that my sequel will get confused if you name the parameters the same as a column name, for example, if department I d.

04:56

Was this instead, the database would get confused by that, and it would actually produce hard detective airs because the function would still run. But it would be like you passed in a non existent department, I d.

05:11

So we're returning

05:13

the query, the value of that query that comes back, which is a summing of the policing of the hours, which is based on that challenge from a previous lesson.

05:21

I do want to do one more thing to improve this

05:25

and case parameters are passed in that do not exist like a company that doesn't exist or a department that doesn't exist. I want return zero. I don't want to return. No. So I'm gonna go ahead and police the whole query

05:39

like so

05:43

And then, of course, after that we have our end statement and our delimit er swap back.

05:47

So let me go ahead and run this.

05:51

Let's go ahead and refresh our procedures.

05:56

So I'm going to get rid of my filter there

06:01

on refresh the procedures

06:05

department hours.

06:06

Right click.

06:09

I'm gonna generate a call.

06:12

I'm gonna go ahead and copy that. I must go down to the bottom of this sequel Pain

06:19

when I paste it,

06:23

I don't get a company to,

06:27

and I have a consult department. So let's go ahead and pass those in its parameters

06:32

and see what we get back.

06:35

Okay?

06:38

I know. Have admin department, too.

06:44

Now, let's try a department that doesn't exist, like test,

06:49

all right. And I get zero back.

06:53

All right? So that is an implementation of a function that helps us abstract some complexity. Getting the department ours is a bit of a complicated query. Now, if that's a common idea in our company, we can just call this function to return it on a report

07:08

or return it and some application code if we want to. And then if we need to change it down the road, we just change dysfunction.

07:13

So that completes distortion of lesson. Let's head over to the summary and review what we went over.

07:20

And that brings us to the 7.1 review. And this lesson we reviewed functions their scope in their implementation. We implemented an active employees hours by department function, so this converted a complex query to a simple function call. And doing this helps combat copy paste programming

07:39

and also abstracts complexity.