Hi. In this lesson, let's talk about how to deal with stored procedures. So what is this stored procedure? And how do we create it? a stored procedure is a PL SQL block that is given a name and it is stored in the database. We can call this procedure with the given name.

And it is meant to perform an operation in our business application. It is used to implement business logic. For example, we can use it to raise the salary of an employee. But while you're raising the salary, there might be a lot of operations to perform. There might be many business rules to implement, all that will be taken care by stored procedure from our applications like Java, Python, C sharp which called the stored procedure and the rest of the operations are performed in the stored procedure. So stored procedure is used to centralize our business logic in the database.

Now the syntax is pretty simple. We have to use create procedure followed by the name of the procedure. procedure may take parameters. If you have parameters listed out the parameters in parenthesis, and if you use or replace, you're saying if the procedure is already present, replace the existing one with the new version. If the procedure is not there, it is created. So generally it's a good practice to use or replace so that if the procedure is already there, you create the new version.

If it is not there, a new procedure is created and then Then comes the local declarations, they are the variables or other artifacts like exceptions cursors that you want to use in the stored procedure and then the executable statements followed by optional exception handler. You can handle exceptions within the procedure. So, let us see how to create a procedure. But before that, we want to know what are the advantages of a stored procedure. There are four significant advantages with stored procedure number one, it reduces the network traffic because from our application we are going to call stored procedure only by its name. We are not going to send the whole data we are not going to pass any PL SQL block from client application to database.

Because the stored procedure is there within the database, all that you need to do is just simply send the name from application to database, the procedure is executed in the database. This saves a lot of network traffic. Number two procedures generally run faster than the corresponding PL SQL block for two reasons. Number one, procedures are compiled and stored. When they are stored in the database, they are first compiled. Number two, once the procedure is executed, it's loaded into memory and Oracle stores in the memory.

So subsequent executions of procedure will not need to load the processor again from the database. They are already there in the memory so The procedure runs faster second time onwards, because it need not be loaded from database into memory. So combining these two, we are making the procedures much faster than the equal and PL SQL block. easy maintenance number three, it's because we have the whole business logic centralized in the form of procedures in the database. If I have to change any of those business rules or business logic, all that I need to do is just go to my procedure change it once all my applications will be immediately affected by the change in the stored procedure, because all applications are designed to invoke stored procedures. For example, if I have to log the change to salary of the employ, then I can no incorporate that new feature in the procedure.

And whenever you call the processor, automatically logging takes place because the procedure is modified. We don't need to change anything in our applications like our Java application and other applications. Finally, the fourth point is security. Instead of giving other users access to the tables views, we now give them procedures which internally use those tables and views. Let's look at this diagram. Normally, you would give users access to the table if you want user to make any changes.

But this time, we are not giving access to table. User two does not need any privilege on the table. Instead, he needs a privilege on the processor. A would call the processor and then the processor is going to make changes to the table. But how can you use that to make changes to a table that belongs to user one, when user two has no privilege on the table? The answer is, when user two is running the procedure, he runs the procedure as if he were user one.

In other words, user two is running the procedure with the privileges of user one. So when user two is invoking processor, or achill, takes user two as user one. Whatever privileges user one enjoys the same privileges user two is also going to enjoy but only when is running the processor. So, when user invokes processor is going to update the table because he is enjoying the privileges of user one. This is also known as a definer rights. So we use the rights of the definer of the procedure, that means the owner of the process.

So those are the four major advantages of using a procedure versus creating a normal PL SQL block. Now, let's talk about how to create a simple procedure called change salary. It takes two parameters, employee ID salary, it's updating the salary of the employee to the new salary, and if it is done, it is going to come it if you don't find employee ID, it is not going to do anything but you can change the assays that to raise an error. And we have seen in the previous lesson, there is a way to RAISE APPLICATION ERROR. And we generally use that in processors. So when the job is not done successfully, you might want to inform to the caller about the problem.

So you RAISE APPLICATION ERROR, we'll change this to understand how that is going to change the scenario. So let us switch to our SQL Developer and see how we create the procedure. So this is how we create the processor. So just execute this command and it is going to create a procedure and you must see the message just like this procedure signed, so compiled. And if you see any other message, it means your procedure is not successfully created. Let me make a small error.

I'm not using the right table name. And I'm trying to now create the procedure. Let's clear the screen and go there. And now you see, it is giving you error messages. And it is telling you that there are errors, the table or view does not exist. But you might not always get all these details, especially if you're using SQL plus to create procedures, you don't get to see all these error messages.

But never mind. You can still get all the details. But for now, this is what you need to know. So if you're not successful in creating processor, you get to see all these error messages. So check out what is word and go back and recreate the processor like this. So now the procedure is compiled with no errors.

So this is how we create a procedure And now how do you call the procedure. Once the procedure is created, there are two ways in which you can call the procedure number one, you can call the procedure using execute command. If you're calling the procedure from SQL class, you can give execute followed by procedure name and required parameters. And if you are calling it from another PL SQL block, you just need to give the name of the procedure followed by the parameters. And also if you are using execute privilege, this is the privilege we use to grant the execution privilege on the procedure to a user. So this is a privilege applicable or relevant only to program units like stored procedures and functions.

So I'm glad During executive privilege on change, salary to demo demo is the user and execute is the privilege and change salary is the name of the program unit that is our procedure. And then what if you don't succeed in creating the processor? What if there are some syntax errors, the first thing you can do is use show errors. By using show errors, you can display all the errors. This is the case if you're using SQL plus, but I strongly recommend use SQL Developer if you're creating more procedures because that's a more sophisticated environment. You can also get errors from user errors.

And this is going to give you errors for all the procedures but if you're very particular about one procedure, use name Then ask for that. So there, we say where name is equal to science. And then another important thing is getting the source code of a stored procedure. After you create that processor, if you ever want to know what is the source code, you can use user underscore source. That's the data dictionary view. And again, mentioned the name.

And don't forget, object names are always given in uppercase. We discussed about that in the past, do keep it on your mind. So you should give change salary in uppercase. Let's switch back and then let's change this. So if I'm not able to find the employ, then it doesn't do anything. Okay, let us first call this so I say begin.

And I say change salary, and I'm giving some numbers. And some salary, I'm giving some 7000 let's just run only this and clear all that. And you can even clear this log so that we have only what we need. And we can even close this. So just go ahead and run the procedure successfully completed. That means the salary for imply 120 should be 7000.

Let's check that select salary from employees where employee ID is 120. Let's go around and you should see 7000 Well, we are spot on. But now what if I gave something like 320 we don't have any employee with the 320. So try running it and you're going to see no error message. It says procedure successfully completed but that's misleading because we didn't have any employees. With id 320.

So we can make a small change here, we can put an else here and say, if the change is not made, obviously it indicates that the employee ID is not found, we can RAISE APPLICATION ERROR. So we say RAISE APPLICATION ERROR minus 2000, sorry, 20,200. And we can say error message is employee ID not found. So, let's recreate the procedure as it is create or replace, we can create the procedure again, it'll replace the existing one, the procedure compiled successfully. And now if you go and run this, you're going to see that error message and the message is clearly telling you it is in the procedure change salary, and the message is employee ID not found Also check out the number that is the number you have assigned. So, that is the purpose of Ray's APPLICATION ERROR in the context of stored procedures we use it very regularly.

So when the job is not done, you want to raise error and inform to the caller that there is a problem. Well, the caller is a PL SQL block and it can handle that error by using Exception Handling if it wants. So, this is how you can modify recreate, and then you can raise an error if the job is not done. So apart from processors, we also have another artifact called function. The difference is simple. As third function returns a value add function is always expected to return a single value View.

Other than that, it's exactly same as stored procedure. And one more important thing, you can see return statement to return the value. And also you need to use return to specify what type of value the function is expected to return, the function is returning this type of value. So you must give the return type and eventually return a value from the function. So let us see an example. This is a function that is going to return a number.

That's what we mentioned here. We mentioned that it returns a number and the name of the function is get total salary and it is taking department ID. And this is where we calculate the sum and we put it into variable and this is where we are going to return value. That's it. So one new thing is you mentioned the return type, and then you return the value. And how do we call it we can call it from any PL SQL block.

And you can call it event from normal select command. And as it is shown, I can say get me the department name and also the total salary for that department. So we pass the department ID from every row and we are going to get the total salary so we display both department name and total salary. So let us switch and see how this works. And here is my get total salary. I'm going to create this and here we are, it's done.

And now I can call it in two ways. I can have a normal PL SQL block and say DBMS underscore output and port line and I can call that function. So I just need to call the function with the get total salary of 10. And remember, this is not case sensitive. So you can give whichever case you like. And also don't forget set server output on.

So if you go ahead and run this, you're going to see the total, this is the total for department 10. And if you change it to department 60 and try the same, you're going to see a different output. So 28,800 and as we already discussed, you can as well use a normal select so I can say department name, get total salary of department ID from departments. So you are going to get that and we can say or drive by to. So you're going to sort everything by this and that in the descending God. Well, you can apply all your SQL knowledge.

So this is where you're going to get the data. Well, when we have the null values, we have a problem okay. But other than null values in if you see this is coming in the descending order. So sales is having the highest total salary, then shipping and so on. So this is how it goes. And you can modify this slightly and say, if at all This is null, we want to treat it as zero.

You can. So we know a function if you remember NVL. I say if total salary is null, I want to treat it as zero. So let's go and recreate and this time the function is going to return not now but zero. When we don't have any employee in the department and now you come here and execute this and you should be saying this time only the values and not nulls at the top and the nulls are now converted to zeros. So, they go at the bottom.

So, this is the way you can always modify your function according to your requirement and there is no scope for error. So, we are not going to raise any errors. Now, there is another function which is going to return a string where character and we don't mention the the size of the data type. This is one important point. When we use the parameters we are not expected to we are not supposed to mention the size or we don't say where character of 20 instead, we simply say it is where can That's it. So get implies takes a string and returns a string.

And here, we created a cursor, we can create a cursor like this. So a stored procedure or a function can create cursor can create exceptions can raise them, do all sorts of things. So let us create this. This is where I take all the employees who are in the given job. And I want to concatenate all the employee names, separating them with a comma. And that's what eventually I'm going to return.

And this is the loop where I take one name at a time, concatenate that to names and also put a comma at the end. That's arrays and they will be an extra comma at the end, and we are trimming it using our train. So now if you go and run this part, we create a new function. So get employees is created. And now you can call that whichever way you can. For example, we say select job ID.

And then we say get employees of job ID from jobs they can also say where job ID in select a job ID from employees. So we are only considering those jobs for which we have employees. So this is how we can execute a query that is using our get employees and we are going to now see the job ID and the details of employees. I can make it even more interesting by going with the job title. Now go ahead, run this and you will see or something like this. Too small While somewhere, it is too small.

So this 200 is not enough. And we need to increase this, let's go for 2000. That's fine, because we have enough space. And go ahead, run that that's a kind of truncation error. Now go run. And you're going to see something like this, you see, you have the job title, and then the employees who are doing the job like this.

So, you can read a cursor, you can deal with exceptions, all sorts of things can be done. So this is how we create a stored function. And you can see what are the functions available? What are the procedures available by going to your SQL developers three here on the left, and there you can see all the objects and now you can see these are the three different ones and There might be a few more I created already. And both functions and procedures, you can see them. And you can also double click on that to get more details about that, it's going to show you the function.

So you get the source code straight away if you do this, so that's the way it goes. a stored procedure and stored function are stored in the database with a name. And with the help of the name, we can invoke them and get the job done. And on both you need to have execute privilege if you're not the owner, and you run them with the privileges of the owner, which is called as definer rights. So in this lesson, we talked about how to deal with the stored procedures. We talked about how to deal with the stored functions.

We understood, execute privilege. We understood you can errors if you want using show errors, because when the procedure is not created successfully, you don't get all the errors in SQL plus expecially. So we can use show errors to get error messages and user source is use it to get access to source code, we can use select the text, that is a column where the whole text is stored, but you need to mention which procedure you are interested in. So this is just part one of our program units. In this we understood how to create procedure and function. But there is a lot more to come.

So we will see more about procedures, functions, and then a few more related objects in the next lesson. See you in the next lesson.