Hi. In this lesson, we understand how to deal with control structures in PL SQL. In the previous lesson, we just got started, we wrote a small, anonymous block executed to understand what are the important ingredients of a PL SQL program. In this lesson, let's get into more details. To start with, just like any other language, pl SQL does support operators. The first of the operators is assignment operator, which is colon equal to.

It's not like many other languages where it is just a single equal to here it is called an equal to, you need to remember that and this expression is saying it is going to add amounted to total and then put the result back into it. Total. So, V total is the variable to which we assign the value and the value is coming from the right side expression. You just have to make sure the result of this expression is compatible with the data type of the variable that is simple assignment. Then the next thing is a dometic operator. These are automatic operators and we are talking about very standard a domestic operators.

Of course, you don't find operators like a plus plus minus minus, which are very common in languages like C Java etc. So, plus minus slash star are available everywhere. The missing operator is a modulus operator. We don't have any operator operator for getting the remainder of a division. But instead we have a function called mod, which is one of the other Matic functions in Oracle. So we still have to use the same.

And then we have an operator, the double star to raise bass to power. So if I say to double star, and if I give a it's going to give me 256. That's to raise it to the power of eight. So those are automatic operators in PL SQL. Then coming to functions, what functions can I use in PL SQL blocks, almost everything that you have in SQL except a few. Number one, you can't use decode, decode is only used in sacred number two, no growth functions can be used all these groups functions like min max, sum, average etc can be used only in SQL, not in PL SQL.

What I mean by that is, I can't do anything like this, this is not valid. But if you really want to get the total salary of all employees, then you can use it with the normal select and put the result into the variable using into this is very much valid. So though you can't use these functions directly in the procedural statement like this, you can use it in SQL statement, and the result can be copied into a variable of PL SQL block. So this is something you need to keep on your mind. Then, we have the same standard control statements everywhere. We have a statement and this is the syntax You just need to get used to the syntax.

If condition, then the statements, you can give any number of statements, then else if it's just l s, if there is no S II, okay? It's just LS if condition two, and then the statements. If this condition is satisfied, then these statements are executed, but it goes in the order. If first condition fails, then it goes to second. If second condition also fails, it might then go to third, fourth, and so on, because these three dots would indicate you can repeat this any number of times, but this is optional. That's what the square brackets would indicate.

So if you're not giving anything like L, F, then we directly go to ELLs. So that is Going to be a simple if else, if condition is true do this. If condition is false then do this. But if you have more conditions you can use this elsif okay though it is LS if we just pronounce it as elsif. Now, the rules says the control goes to else if condition is false or null null can really jeopardize a lot if you don't handle it carefully. So if condition happens to be null, it's considered to be false.

So, you have to be aware of it. When expression in condition contains null value the condition becomes null. So if I have something like a greater than B, and if a is now the whole result is null, that means it is false. You have to be aware of this point. So, if you feel there could be null values in the expressions being used in the condition take care of null values. So here is a relational operators list and we have the same operators we have been using even in SQL.

And we have the same greater than less than not a call to like between and and so on. So there is nothing new. It's the same old stuff. And then here is a small example. I want to find out the average salary of employees who are in the same job as employ 110. So find out the job ID of 110 and find out the average salary of all the employees where the job ID is whatever is the job ID of 110 let us say 110 is a sales manager, then we get average salary of sales managers and then I want to find out what is the salary of 110.

And then comes a simple if statement, if the salary of 110 is greater than average salary, then we are going to update a salary by 10% and we are updating the salary of employee 110 by 10% we are hiking we are incrementing it by 10% This is the expression. So, take the current salary multiplied with 1.1 which happens to be 10% if not, if a salary is not greater than the average salary, then we go to else portion and we do this and This is where we are incrementing salary by 20%. And whatever you do, eventually you need to give endif. And also it's a good practice, though I didn't do it here to commit the changes that are made. So this is how you execute some select command update commands in the context of a PL SQL block. So let us see how this works.

Here is the program. And this is the program where we take the average salary of employees who are doing the same job as 110. And if the salary of 110 is greater than average salary, we increment salary by 10%. So let's go run this and then go ahead and run and it says the procedures are successfully completed. That means it has either applied at 10% higher Got 20 depending on the case, you can as well put some extra messages here, just to know which way it is going. So select command, then if statement, and then we go either this way or that way.

That's about a simple if statement. Now, coming back, this is another very powerful case expression, where you have more than one condition, it's better use instead of multiple if statements like if elsif elsif. A case is always more readable. In programming languages like C we call it as switch, but here it is called as case expression. We also have another flavor called case statement, but to start with, case expression is saying it will take an expression They're called a selector. And it is going to compare this with the given values.

So the selector is being compared with first expression. If these two are same, it'll return this, it always returns a value. The value is going to be result one if selector is expression one, but if selector is not expression one, it will compare selector with expression two. If it is same, it returns result two and so on. But if none of the values matched, then it goes to else if given and it is going to return this value. So eventually case is going to return a value this whole thing will be returning a value.

Then we have another more flexible version of the same This time this is without selector, there is no selector. So you're not comparing one value with one. Instead you can give a full blown condition like that. So any condition can be given. This can be something like a greater than 10. Or it can be something like salary is greater than 10,000 are multiple conditions combined with logical operators, anything you like.

And then it returns one of these values based on the condition. Again, do remember, the first condition that is true is going to stop the whole process. If this fails, but this succeeds, we returned result two, that's where it stops. So eventually it is going to return a value but the way the comparison is done Different in case with selector and without selector. Then the next one is a small example. This is where I want to find out what is the bonus of employ 120 how much bonus are we giving to employ 120 It depends on the salary of the employee.

So, get the salary of employ 120 into variable. Well as usual we declared that variable with the same data type as salary and bonus person taste is a variable of number of five. Now, take this case this is what we have to use. So, we are saying if salary is less than 5,020% if salary is less than 10,000 that means it is in between 5000 to 10,000 Then I get 15 If not, we go further down is this salary less than 15,000? We already checked for 10. So this is effectively asking is it in the range 10,000 to 15,000.

If so, eight returns 10 and if none of these conditions satisfied, then it goes to ELS and returns five whatever it returns this whole case expression is returning a value and that goes into this variable. And now I want to display the value. We are saying bonus percentages for employ 120 ease and we are giving the value and this is our string concatenation operator. Of course we can use it even in PL SQL. And also do not forget whenever you are using port line with the DBMS app. But you need to first ensure you turn on server output.

Otherwise your program runs successfully, but no output is displayed. So, just be aware of that that is just an extra step. Then we have case statement, case statement is not returning a value instead taking an action, it is going to take an action, the same overall picture when condition then the statements in the previous case we were returning a value, but this time we execute some statements and we can have any number of such when clauses and if all of them fail, then it comes to else. Similarly, a slight modification to this is this, where we compare the case of operand with operate. This is like our case expression with the selector. How we compare selector with the different values, we do the same, the only difference is here we execute statements not just returning a value.

So, let us see an example for this. I take the salary for employee 120. And based on the salary of the employee 120, I'm going to display a message. So, if his salary is greater than 15,000, I say very costly employ. And if it is not greater than 15 in between 10 and 15, then I'm going to match this I'm going to satisfy this condition. Then I display a message says costly employ, and if the salary is not even greater than 10,000, then we disagree.

Played the message saying not so costly employ. So, this is how we can take salary of an employ and try to classify employ as very costly costly not so costly. So, we are using case statement in this context, in the previous cases we used case expression. So, in this lesson, we understood what to do with assignment operator, how our domestic operators work, what functions are allowed in PL SQL blocks, if statement and relational operators. Of course, you can always use logical operators as well. case expression and case statement.

There are subtle differences in between case expression and case statement. case expression always returns a value case expression takes action, execute some statements. And in both again we have two variants with selector without selector. So, we have seen the syntaxes a few examples, try to write some more programs using these elementary control structures. And we will see more about programming using PL SQL in the next lesson. See you there.