Hi. In this lesson, we go into more details of programming units. We understand more about procedures, functions, and also bringing a new object called package. And we understand primarily more about how to deal with parameters in the context of procedures and functions. Let's get started. We have seen already how to pass parameters in the previous lesson.

And now, the parameters are of three different modes. So when you create a parameter, if you don't mention anything about the parameter mode, it's considered to be in which is the default parameter mode. But you may want to have parameters which are of mode output that is specified you using the keyboard out, you may also want to have parameters which are both in and out. So, what do I mean by out parameter in parameter is saying we are sending a value into the procedure out parameter is saying the value is coming outside the processor. So, the processor sends a value to the caller using out parameter in out parameter is saying it is going to receive a value from the caller and it is also sending a value back that means, the actual parameter which is what we are sending from the caller will be assigned to the formal parameter, the parameter that we declare in procedure and if you receive the value from actual to formal We call it as in, but if you are sending the data from your formal parameter back to actual parameter, it is out.

And if you want to do both it is called as in out. So let's examine these options in parameter is the default. So you don't need to mention it and it is going to receive the value from the actual parameter. So here formal parameter is acting like a constant. So the parameter we declared in the procedure is working like a constant you can't change it. And formal parameter cannot be assigned a value because it behaves like a constant.

And actual parameter means whatever we are passing to formal parameter can be a constant or it can be any variable or an expression whereas, in case of an out parameter, you have to provide not any constant the actual parameter must be a variable, it cannot be an expression or a constant and formal parameter must be assigned a value because the value is coming back from the procedure to the caller and formal parameter behaves like an uninitialized variable. So, it is like a variable that is not assigned any value. So, we need to make sure we assign a value to that formal parameter inside the procedure. Only then the value can go back to actual parameter and in out is actually the best of these two. So here you need to make And in out very specifically, even that is the case for out because default is in passes a value in both ways.

So the actual parameter is copied to formal parameter changes made too formal parameter can go back to actual parameter. And in this case formal parameter is an initialized variable. And then actual parameter must be a variable because its value is copied to formal parameter and the value from formal parameter goes back to actual parameter. An example would make the whole process easy. So let's look at this. I have a procedure that is going to get me the minimum and maximum salaries for the given job.

So it is going to take A job ID as in parameter. And I don't need to mention it, but it doesn't harm if I mentioned it explicitly. The next thing is out parameter and I must explicitly gave out here and there. So both these are considered to be uninitialized variables, and they must be assigned a value in the body of the processor. And if you don't assign the value, it's considered to be an error. So minimum salary, maximum salary are called out parameters.

And all three are called formal parameters. Please be aware of this terminology. And we use the same terminology in many other languages. It's not anything specific to PL SQL. So job ID is what we are receiving from the caller. We get this as parameter and then we are talking about two out parameters.

One is minimum salary, the other one maximum salary. Now, this query is going to retrieve the data and copying that into those two out parameters. That's all. That's all we need to do in the procedure. Once the procedure is done, you are guaranteed values for minimum salary and maximum salary. Then how do you call this procedure?

Well, here it is. to call the procedure we need to provide three parameters. The first parameter can be any constant or a variable does not matter. But second, and third parameters must be variables, because those two parameters are out parameters. When you're dealing without parameter, the actual parameter must be aware of Remember this is this is what we call as actual parameter, this is the formal parameter. So, it is going to receive the value from your formal parameter to corresponding actual parameter.

That is what out is doing. By any chance if you want to send data from this actual parameter to formal parameter, and also receive the data back from that today's you should declare that as in out just out won't do, because we want to receive something from actual parameter and send something back to actual parameter. So, this is what we mean by in our parameter. So, here we are, let's execute the code and see how things go. This is our procedure So let's go create the procedure. And procedure is successfully compiled means created.

And the next step is to run that, to run that procedure to invoke that we have to give this and to print the output that is coming in to minimum and maximum salary variables, we need to first start this, set the server output on as usual, and then go run this and as you run, you're going to see the output there. So the processor is giving the output 4000 209,000. Let's clear everything and then run it again and you can see the output 4000 209,000. That's how it works. And you have to be careful about what to use here whether out are in out, are in it's not such a difference. decision, but still you need to be aware of the implications.

So, if you have any doubt go to this chart there you get all the details. Now, the next interesting thing is parameter notation. So how do you pass values to parameter? Not that whether you can modify or not modify, but how do you pass values to parameter? Let's understand an example first, I have now three parameters for my procedure. The first parameter is n one and we are assigning a value to that which is called as default value.

So, we have a facility called default value. That means, if no value is provided to this parameter at the time you call the procedure I want one to be assigned to that this is called default value. The syntax is simple, you need to give this assignment operator followed by the default value. So one will be assigned to N One, two will be assigned to N two, and three, four n three. That's how we designed this procedure. Now, the first invocation is where we pass three values to those three parameters.

So and to get 20 entry gates that the N one is 10. So those three are passed based on the position. That means first value goes to first parameter, second value goes to second parameter. This is what we have been using right from the beginning. And right from the beginning racquel pl SQL supported this, but as of late, we got some nice new additions where you can pass parameters by giving the name of the parameter. So I don't need to know the order of the parameters.

For example, I don't know whether n two is given first or third position or in fourth position. So I can just directly give into and gave the value. And again, you need to use a different operator. And this is known as fat arrow. It's just equal to followed by greater than, so give the parameter name and give the value the same I'm doing for entry. What does it mean?

It means your n one is not given any value, that's fine because we already have a default value for n one. Of course, you can't ignore any parameter that does not have a default value. So if you have a default value, that is fine. So you If you have that default value, then that will be automatically assigned to anyone. Now enter gets 20 and three gets 30. So, the output is going to be one and then 20 and then 30.

Now comes the last option, where we are mixing positional parameter notation with the name of the parameter notation. So, what does it mean? The first parameter is not using any name, so, it goes to the first parameter here. So, 10 goes to N 1am not giving anything for n 2am giving a value for n three that directly goes there. So, some parameters are given by the position and the remaining are given by the name. This is called mix it notation and the advantage with the positional AES, you don't need to remember or don't need to know anything about parameter names, but you got to know the order of parameters.

And the advantage with the name, you don't need to worry about the position of the parameters, you just need to know the names of the parameters. And they might be in any order in the procedure. And then if you have a lot of parameters like say 10 parameters, and you want to give first three, so you don't need to bother about giving the name directly give the first three values for reminding you have to use if you like, the name of the parameter notation, that means you provide the name instead of position. So you are free to mix it. But if you're planning to mix it, do not forget you need to give all positional parameters first Then only named parameters. That is the order you need to go with.

So let's see how that works. And I have just a different example here. So this is where we create a procedure. Let's create it first. And let's just clear everything. And also we don't need the log, just push it down.

And now let's go create it, the procedure is created. And now it's time to call that and if you call this in this way, let's comment out some of them just to understand one by one. So if you go and run this, that's the output of it, the output is going to be 10 2030. And then if you try to run the same thing in this way, now I comment out that and then uncomment this, I gave 22 and two and then 32 and three and if I go and run this piece of code, I'm going to get 120 30. Of course, I can change the order, I can say something like n one is equal to 30. And then you know, the result is going to be different.

Because we are passing values to N one, and two, but not to entry. So the output will be 3020, and three, So play around and understand what's really happening. And then the last two is where we are talking about positional place the named so 10 goes to N one, and 10 goes to N three, and n two is not given a value. So it assumes automatically the default, which is two. So go run it, you're going to say 10 to 10. But you have to be careful as I mentioned, you have to give them in the correct order and if you're trying to do it In this way, what happens, you're going to get an error, a positional parameter might not follow a named Association.

So, it is saying a positional parameter Association cannot be given after a named Association. So all positional parameters should be given first and only then your named parameters can be used like this, whatever you want to give, okay, so when you have a procedure with a lot of parameters, like 10, sometimes 15, then it is very useful to go with named parameters, especially if you're not planning to provide values for all of them. So, whatever is your case, these are the features of the language. So, try to make the most of it. And also make sure you have a default value because, by any chance if you don't have a default value, then every invocation must make sure it is including a value for anyone. For example, if I recreate my procedure with days, and then if I try to call this, it's not going to work, because we are saying a wrong number of types of arguments, because we are not passing any value to the mandatory parameter.

The mandatory parameter here is n one. So you must give value for n one and then you can give value for any other parameter. So this is now Fine. Let's play it this and run this. And what if you're trying to give this using named parameter like this is that fine? Well, as long as the value is given, whether you use positional or named, it should not make any difference.

So that's the way the positional parameters work. Let's get back to our slides. And here. The next important thing is Invoker rights. We already understood what is defined and right. Define a right is where you run the procedure with the privileges of definer.

That means, whoever is the owner of the procedure, you will be invoking procedure with the privileges of the owner. But sometimes it is designed to have the privileges of Invoker to be used rather than the define that so let's look at this scenario. User two is having privilege to run procedure, which is created by user one. So procedure belongs to user one, but the user two is trying to enter Bakit when user two is invoking procedure, in normal cases, you get the privileges of user one, because user two is invoking the procedure created by user one, a runs the processor with the privileges of user one. So, in that case, whenever user two is invoking the procedure, procedure is using table that is belonging to user one, because procedure is run with the privileges of user one. But now I want it to be different.

I want the procedure to use the schema objects of the Invoker. So when user two is invoking the procedure, I want procedure to use the object of user two. And when the procedure is invoked by users One, I want the procedure to use the table belonging to user one. So in case if you have user three, invoking the procedure, it should use the table that is present in user three, not in user one. Well, if that is your requirement, yes, you can achieve that. And this is possible by using what is called as Invoker rights.

Here is an example for Invoker rights. I'm creating a procedure called add message. And this procedure is associated with the order ID current user. And by giving this you're telling a racquel that this procedure should be called with privileges of Invoker not the privileges of definer. If you don't give that class then the default is define a right so now add message is invoke it with the privileges of the caller. So if the procedure is using any table, the table is taken from the schema of invoke, not the definer.

So that's about Invoker rights and how it is different from definer rights. Well, when we have a lot of procedures and functions, sometimes even hundreds of procedures and functions, then we want to divide them into different groups so that we can more easily manage them. Also, at times, we want to have some variables and some artifacts that are to be accessed all over the application. In such cases, we can bring a new concept called package into picture. So a package is a collection of procedures and functions generally related to a particular topic. So I can create a package related to all the procedures and functions of jobs.

And another package for employs another package for departments and so on. A package can also have variables and data types that you can use globally. That means you can use some variables. those variables can be used even outside the package just like global variables. A package is having two different steps. You create the specification, first list out all the public members, public members are the ones we can access from outside the package.

Then you create the body where you define those public members. But you're also free to have some private members, the members that are not accessible outside the package. So here is my package is called department package department package is a package that is having two members, they're called as members. The first one is changement as a procedure, the second one is employee count which is a function. So, to create the package, we start by saying create package and followed by the package name. And within that we need to give the list of public members.

So public members are declared in pack a specification, and then they are to be created in package body. Create package body is another command and here is the example. So we are writing code for Change Manager and employee count and these are the members of the package. In addition to these two if you want, you can have some more procedures and functions, but they cannot be invoked outside the package. There's nothing new about the procedures and functions. They're very similar to what we have been creating, but they are just now part of the package.

And then if you see how we call those procedures and functions, just a small extra step, you need to use the package name when you call the procedure or a function that is part of the package. So I have to use DPT pkg that's the name of the package, followed by dot followed by the member of the package. Apart from the packages that we created, we can use a lot of packages provided by Oracle. So Oracle provides a lot of predefined packages that are called Oracle supplied packages and You see we have been using one such package called DBMS OUTPUT. So DBMS OUTPUT is a package provided by Oracle. Apart from that we have packages like DBMS refresh, which is used in the context of materialized views DBMS SQL is used for advanced dynamic SQL lob, which is used for lobs row ID DBMS.

Row ID is to manipulate row ID. And then utl is about utility packages utl file is very interesting pack is providing lots of functions and procedures using which you can manipulate files. For more information about the utl file, please check out my blog in my website. There I explained how to use utl file. So this is About program units part two. In this we talked about how to use parameter mods, parameter notations, how to deal with the Invoker rights and how is it different from definer rights.

And we created a package with the two different steps package specification first, then package body, we can create packages anytime we like. And Oracle also supplies a lot of packages providing various functionalities. So this is about programming units. It's a very important topic in the context of PL SQL. So do practice a lot. And I'll catch you with more about programming with PL SQL in the next lesson.