Transcription
If we would like to go line by line, or if you want to read a par line by line, so in general, what do we use? We use a pointer, right? So either we use a finger so that we can go through it, or we will use a pointer, or some people just fold the paper and they'll just use it as a pointer and know.
When we do, when we are in a schooling, we have also done the same way. And in the same way, if you are in front of a system, if you would like to go through it in a row by row, based on a line by line, we generally use a mouse, right? So the cursor will be there where we will go and read it by line by line. So in the same way, if you want to process something in a table on a row by row basis, then what do we use in SQL Server?
Hi, I am M. Asif Hussein from Noof Academy. What are we going to learn in this video? We are going to learn and understand what are cursors in SQL Server, different types of cursors in SQL Server with one practical example. So, my dear viewers, by the end of this video, you could be able to learn and write the cursor with an example, and as well as you will also gain knowledge on the types of cursors and why they are used in SQL Server. So watch till the end.
When, if it is necessary to process the row on a row by row basis, then we use a cursor. As I told you, if you want to go in a row by row basis in a table for each column and each row in a table, so then we use it as a cursor in SQL Server. But the very important point that we have to focus when we are talking about the cursor is that cursors are very, very bad and poor in performance. So as much as required, the suggestion for this cursor is that we have to avoid it to use. Okay?
So, as these are very bad in performance, so if I would like to talk the different types of cursors that we have it in SQL Server, there are four types. Okay? So we generally talk about the forward only, static, keyset, and dynamic cursor. So when I'm telling forward only cursor, in the name itself, we have forward only cursor. It is a read-only cursor that moves in one direction from the first record to the last record, and it cannot go backward. Okay? So most of the reports or most of the data sets that, uh, we would like to, uh, see the process of the complete report from the starting point to the end point, then we can go and use this forward only cursors in SQL Server.
And now, if I talk to you about the another type of cursor that we have is a static cursor. Now, static cursor, what it will do? It will create a snapshot of the data when it is first opened. Okay? And it shows a consistent view of the result set. So now, let us consider the data is getting changed, the data is getting updated. Now, this will not affect or it, it will not be impacted on this static cursor because it is already taken a snapshot of the data. So whatever the data that we are updating in the back end, that will not get, you know, uh, changed or in the snapshot that static cursor has taken.
Now, the another type of cursor that we have is a keyset cursor. Okay? In the keyset itself, we have the, in the keyset cursor, the keys, or we can call it as an identifiers, okay, of the row are stored when the cursor is open. So whenever it is required for us to view it, then we can go with the keyset cursor, then it will redirect us to the particular, um, row that we have it selected.
Now, another type of cursor that we have is a dynamic cursor. Okay? It will reflect all the changes made to the row in a result set as you move through it. So most probably, the dynamic cursor we will use it if you are using some live data. So let us consider the live cricket data or live population data or Sensex data, so where the data is getting updated, know, changed in every, every particular moment. So then we go with the dynamic cursor. So this particular dynamic cursor, it includes the updates, deletes, inserts made by the other users after the cursor is open. All right? So these, these are the different types of cursors we have it in SQL Server.
So, my dear viewers, now let me just show you one table called customer_cart. Okay? Let's have an overview of this particular table. So here, in this particular table, we have cart_id column where we have ID numbers, and we have the customer email details, every email of a customer. We have menu_group_id, we have menu_id, the quantity, price, and subtotal. Okay? So there, there is some product, and for that product, there are some price, quantity, subtotal. Okay? Customer email, cart IDs, and, and so on. Okay?
So now, the point is that if I would like to go through this particular table on a row by row basis, if you want to process this in a row by row basis, then I have to use a cursor. All right? I have to use a cursor. So now, how I have to use a cursor? What are the process that we have to follow in order to create a cursor? That we are going to learn it now.
So here, if we see that at first, what we are going to do, we are declaring, we are declaring the variables. What variables? Here I'm choosing the customer email and the price as a variable to declare it first. So, as the customer email, for that I'm choosing the data type as varchar and the size, it's, let's consider maximum size as well. And the price, as it is in a decimal, I'm choosing it as a decimal data type.
So now, once I declare my variables, then I have to declare my product called cursor. Okay? I have to declare the cursor name. This is the cursor name. So here I have considered cursor_product. Okay? So this is a cursor name. You have to declare a cursor name. Right? Declare cursor name cursor for what? For selecting. For what all we would like to see in the output? The columns. So I will go with the customer email, and I will go with the price column. So these are the two columns I would like to go with it. Now, from which particular table? I'm choosing all these. I'm choosing it from the customer_cart table. I'm choosing the customer email column and the price column which I would like to see it in the output. I am selecting it from the table called customer_cart.
Now, what I'm doing, my dear viewers, I'm opening my cursor. The cursor name is cursor_product. Okay? So if you're are feeling that this particular name is so big, so you can reduce the size of it or you can just write a very simple name for an easy understanding. It's up to us. Okay? So if you would like to consider, I'll just go with the product like this. Okay? Okay, I'll go with like this, cart_product.
So now, what I'm doing, I'm fetching next, know, one by one, next thing from the cursor name, cursor_product, into what? Into the variables that I have declared at first, which is customer email and the price. Okay? So now, what I'm doing, I'm using a while loop. I'm using a while loop. While fetching_status = 0, if I start it from a zero, then I'm beginning the statement and I'm printing what? I'm printing the customer email plus a small space, then a hyphen, then a small space, then the price that I'm considering here. The function cast, that's why I'm using this cast. It will convert the data type. So price earlier, here we have taken decimal. Now I'm converting that as a varchar data type. Okay?
So now, again, fetch next from the cursor name that we have it here into my customer email. So what all the details that I'm getting here, I want to fetch it for the next one also. From where? From the cursor name that we have, and into the variables that we have considered. As we have begin the statement, as we had begin the statement, we have to end the statement. We have to end the statement.
So now, my dear viewers, here, as we have opened the cursor, as we have opened the cursor, after we end the statement of this while loop, then we have to close the cursor. What we have to do? We have to close the cursor. So now, as we can see that I have opened the cursor here, I am closing the cursor when I'm closing it when I'm done with my while loop. What while loop does? Until or unless the condition satisfies, it completely iterates multiple times over and over again until the condition satisfies. So now, as I had opened the cursor, I am closing the cursor.
Now, what I am doing, I am deallocating the cursor. It means I'm removing the connectivity which is there between the cursor. So I'm deallocating the cursor that I have created. So now, if you would like to see the output, if you would like to see, I'm just executing this one. I'm just executing this one. Now, as you can see here, what's happening? Customer email, we have got it. We have got a small space here, we have got a hyphen, and we have got price here, right? So now, as you can see that it is getting in a row by row basis. It is getting in a row by row basis. All right? As you can see, price column and the customer email that we have it here.
So let's just go and see the, uh, table. Okay? As you can see, the first customer price is 4. Again, the next email ID price is 12. Next email ID 12. So I think we have got the same answer in the output, but rather than like this in a column wise, we are allocating it in a row by row basis. We would like to process it. As we have given the format, I would like to get the customer email first, a small space, then a hyphen, then a small space, then I would like to get my price. That's what we have, know, created it, and that is the result that we are going to get it.
So, as you can, you can see that it's taking a multiple steps to, know, getting executed, and it's very, you know, poor in the performance as well. That's why major of them, they would like to avoid using the cursors in the projects. So, as you can see, 4, 12, 12. So whatever the format you would like to get in the output, it's up to you. So under the print statement, whatever we would like to write it, we can write it as per our choice. But creating a cursor, processing it in a row by row basis, that's very important to understand in this particular concept.
So in this video, we have learned what is a cursor, different types of cursors, practically we have learned how to write a cursor in SQL Server. So thank you for choosing Noof Academy as your source for IT knowledge. We are always here to help you to navigate the tech world. If you have any questions, do not hesitate to reach out. It's been a pleasure sharing this information with you. Stay curious and keep learning.