Hi. In this lesson, we understand how to deal with cursors. Till now, we were restricted to retrieving only one row at a time, because select command could retrieve only one row, as it could copy the data into only one set of variables. But what if I want to retrieve multiple rows and do some process with each row? Well, the answer is a cursor. A cursor is where we can load data from the table and access those rows one by one.

We have two types of cursors implicit or explicit. Let us first use explicit cursor, because this is what we need to use to get multiple rows from the table into memory and process those rows one by one implicit cursor also plays a vital role, and it is providing information about the most recently executed SQL command. We'll see how implicit cursor can be used. Now, this is how explicit cursor is used. You read the data from database using a select statement into cursor. And then you will be taking one row from cursor, which is called the current row and copy the data from that row into some variables.

And then those variables can be used in the process. And your process starts with the first row. We keep moving down until we reach the end of the cursor. The whole process is very similar to how you open a file, read line by line and stop once you reach the end of the file. In the same manner, we read the data into cursor takes row by row process each row. And when we hit the end of the cursor, we stop the process.

So here are the steps we have to take. Number one, you declare the cursor. At the time of declaration, you give the name for cursor, what is the name we use to refer to it, and what is the SQL statement that you want to associate with the cursor? Well, of course, the statement is SELECT statement, and that is what we use to retrieve the data into cursor. And then you need to open the cursor. This is exactly where the select statement is executed, and the data is loaded into memory.

And then you need to fetch one row at a time into variables. So take one row from the cursor using fetch statement and copy the columns of the rows into variable You must know how many columns are there in the cursor, so that you can give appropriate variables after into. But you can't go on fetching forever. So you need to know whether you reach the end of the cursor. When you reach the end up the cursor, you're going to stop, but to know that you need to use an attribute called not found. So cursor name percent is not found, will tell you whether the previous fetch succeeded or failed.

If the previous fetch failed, then you're going to exit. So when not found a screw, you exit the loop. So the third step is iterative. You have to repeatedly execute those statements until exit is executed. And finally, always close the cursor once it is done. So let us put this Do a program.

Here is my program. I want to retrieve details about jobs. I want to take the details of jobs for which we have some employees. Well, it can be any query any simple or complex query. So step one, you're declaring a cursor with the name jobs cursor. The select command is retrieving job ID and title.

Then we create two variables, because we need to copy the data into those variables. Open the cursor, that's where the data is actually loaded into cursor. Then set a loop, fetch one row at a time. And we know the row contains two columns. And those two columns are copied into the job ID and job title. But we can't proceed if the previous fetch failed.

So find out whether fetch succeeded or not by using Jobs cursor, that's the name of the cursor person is not found. That is the attribute. That's the syntax to use attribute. So when not found is true, we stop. Otherwise, we just displayed the details. This is just a demo program.

But in place of the display, you can put any amount of process any amount of business logic related to that row. For example, you can take one employ at a time and find out how much increment the employee should get with lots of calculations and finally, update the details of the employ. So any amount of business logic can be put in place of this. This is just a demo. Our focus is on knowing how the pieces are to be put together. But once you know this, you can go and do that on a large scale.

Once you exit the loop, close the cursor. So declare, open fetch, and close, these are four important steps. Now let us run the program and see how it works. So here is my program. The program is same as what we discussed. And we are going to display the details.

And here is the query. You can have anything you like as long as you are consistent with the columns and the variables you're using. Now go ahead and run that. It's going to display all the titles for which we have some employees. And you can change that to something simpler if you like. I want to remove all that.

I want to just get all the jobs, that's fine. Go ahead, run and you're going to see all the jobs, whatever jobs we have in Job stable, it's going to get you all. So you have to have these four steps, but this is a little lengthy. So PL SQL felt there must be a simpler way and they brought what is called as cursor for loop. So when it comes to cursor for loop, you have to declare the cursor exactly in the way you did before. So there is no change.

And I can simplify that if I like with this, that's fine. And then we don't open the cursor. We are not going to do all that stuff. The cursor for loop is specifically designed for loop. It is not expecting you to open the cursor, it does it for you. It's not expecting you to fetch rows and then check whether you reach the end of the cursor.

All that is done automatically. So it is going to take your cursor it'll read one row from cursor into this variable. And this is called a record variable. And you don't need to declare it, because this is automatically created by PL SQL. And if you see what it has, it has two fields, it's going to have the job ID, because that's what we have and the job title. And we can refer to job ID in this way, job record dot job ID.

And the second one can be referred using job record that job title, so no need to open the cursor, no need to fetch rows yourself and check whether you reach the end of the cursor. Instead, your for loop is going to do that. It will execute the body of the loop for each row by copying the row into this variable so you can get access to Job ID and job title in this way. And finally at the end of the whole process, it stops the loop, and it closes the cursor. So it does the same as before, there is no change in the way The output is displayed. So if you go run this, you'll get the same result.

But only thing is you're not doing all that stuff that you did before. Well, if it is suitable to your requirement, if you feel cursor for loop is fine, go ahead and do that. Now, this is about the cursor for loop we already discussed about it. The syntax is simple row type variable, but you don't need to declare that cursor name and then the statements whatever statements you like, and this opens the cursor fetches the rows terminating the loop automatically when you reach it, end up the cursor and closing it. So this is the best thing. option if you are dealing with the cursor.

Now, the cursor for loop as we discussed is using a record variable and you have to access the columns using the record variable in this way, this is what we have seen already. Now, cursor is having four attributes, these attributes are called cursor attributes and they are slightly different for implicit and explicit cursor. So, not found returns true if previous fetch failed, found returns true if it succeeded. Row count tells you how many rows were fetched so far it's a useful one and is open tells you where the cursor is open or closed. Here is a program that sprains Levante to 15 the highest salaried employees Want to get employees who are drawing the 11th to 15th, the highest salaries. So I'm going to pick up all the employees in the descending order of salary.

And I go with the cursor for loop. And I say, if the count is greater than 10, then I want to display the name. That means we fetch 11 rows, this is 11th row. And if it is the 15th row, that means 11 to 15 you already displayed and you want to exit the loop. So this is just a small example to demonstrate how you can use a row count to know how many rows were fetched. By understanding these possibilities, you will be able to use them in your business requirements.

Then comes the implicit cursor. So explicit cursor is all about retrieving multiple rows and storing them in memory and accessing those rows one by one. But implicit cursor is all about information. It provides information about what happened with the most recent SQL command expecially commands like update and delete, because it can tell you how many rows were affected by those commands, whether any row was affected or not. So here is the example. After the update command, I want to know how many rows were affected.

And if it is updating more than 10 rows, I don't want to proceed with this update. I say roll back. Otherwise I say commit the change. So I can know how many rows were affected by an update command. Similarly, I'm deleting a row, but I don't know whether I could really delete do remember In the previous lesson we discussed, update and delete, do not raise any node data found exception, even though no row is affected. So you don't get any exception even if 130 is not found.

This is the way we can find out. If sequel percent is found is true. That means we found a row and we deleted that we go ahead and commit. So sequel percent is the attribute. The implicit cursor plays a very important role in the context of detecting what happened with update and delete because they don't otherwise tell you what exactly happened. Then, this is a slightly advanced feature associated with cursors.

You can create a cursor with input argument. I want to create a cursor that is Going to get implies for a particular job, but I don't know which job I want the open statement to provide the job. So, at the time of you opening this cursor, you can give a value and this is copied into the parameter and then that parameter is used in our select command. Do remember, at the time of declaration, the statement is not actually executed, it is executed only when you open the cursor. So this query is executed only at the time of view opening the cursor. So if you provide the input that is used there, and it will get all the employees where job ID is it programmer, in case if you are using not open but cursor for Lou, then right after the name of the curse You need to give the parameters in parenthesis.

So this is the parameter we want to pass to the p underscore job ID of your cursor. Well, you can have multiple parameters if you like. So let us see an example for that. Well, this is the previous program where I discussed about displaying 11th to 15th. Go ahead and run, you're going to see the employees, those are the 11th to 15th, the highest paid employees. And this is about our cursor with input argument.

And this is where I'm passing it programmer, the cursor is opened, I can do some process and I can close it. And if I'm using cursor for loop, do this in this way. And whatever might be the case, you can process the data. So if I run this program I'm going to get the employees from IT department. Well, I should not select the line, I should select the whole thing. So there you are.

These are the it programmers. But if I change this from it programmers to sales managers and run that you're going to see a different set of people. So those are the sales managers in your employees table. So, this is the way it is possible for us to pass some parameters to cursor. This is about cursors. And we can create a cursor to accommodate multiple rows retrieved by a query.

Implicit cursor plays important role in knowing what happened with update and delete because it tells you how many rows were affected, whether any row was affected, etc. cursor for loop is the best way to handle it. cursor, because it minimizes your burden. You can have an argument to cursor so that when you open the cursor, you can provide some input. And cursor can bring different sets of rows, depending on the input. So that's about cursor for now.

And you can use cursor in any context. So later, you can use it in procedures, functions, triggers. In fact, anywhere you can use PL SQL, you can use cursors. So that's for now about cursors. See you in the next lesson.