admin
Desk of Contents
- Clarify What’s a cursor in SQL Server?
- Clarify the Use of the cursors.
- Clarify the Life Cycle of the cursor.
- Clarify the cursor With an Instance.
Requirement of Instance
- Create a Momentary desk for college students.
- Insert some dummy data into the created desk for demonstrations.
- Generate/Replace scholar enrollment numbers primarily based on the department, 12 months, and scholar roll no utilizing the cursor.
What’s a cursor in SQL Server?
A Cursor is a SQL Server database object that’s used to govern information in a consequence set on a row-by-row foundation. It acts as a loop similar to the looping mechanism present in some other programming language like C#, VB.Internet, C, C++, Java and and many others. We are able to use cursors once we wish to do information manipulation operations like replace, delete and and many others on a SQL Server database desk in a singleton style in different phrases row by row.
Use of the cursors
You recognize that In relational databases, operations are made on a set of rows known as consequence units. let’s take an instance, In an SQL Server database SELECT assertion returns a set of rows known as a consequence set. Typically the applying logic must work in a singleton style on a brief row-by-row foundation with one row at a time slightly than the complete consequence set directly. This may be performed utilizing cursors in SQL Server.
In any programming language, we use a loop like FOREACH, FOR, WHILE, DO WHILE to iterate by one merchandise at a time, the cursor follows the identical strategy, therefore it is perhaps most popular as a result of it follows the identical logic because the looping mechanism within the programming language.
Life Cycle of the cursor
Right here we’ll break up the life cycle of the cursor into the next 5 completely different sections.
1. Declare Cursor
Earlier than utilizing a cursor, you first should declare the cursor. So, On this part, we’ll declare variables and restore an association of values.
2. Open
That is the second part of the life cycle and As soon as a cursor has been declared, you’ll be able to open it and fetch from it
3. Fetch
That is the third part of the life cycle and that is used to get well the knowledge push by push from a cursor, briefly, you’ll be able to fetch row by row and make a number of operations like insert, replace, delete and and many others on the presently energetic row within the cursor.
4. Shut
That is the fourth part of the life cycle When you might have completed working with a cursor, it is best to shut the cursor. This leaves some portion of the cursor and is used to shut a cursor.
5. Deallocate
That is the fifth and remaining part of the life cycle and on this part, we erase the cursor definition and discharge all of the assets associated to the cursor.
Implementation of Instance
So, Let’s begin to Implement an instance of the cursor within the SQL server, as per our requirement we’ll take into account an instance of a scholar database the place we have to generate the enrollment no of the coed primarily based on his/her department, the 12 months roll variety of the coed.
Earlier than beginning with the cursor I’ll present you the syntax of the cursor in an SQL server and how one can declare a cursor in an SQL server.
Syntex of Cursor
DECLARE @YourVariables nvarchar(50) -- Declare All Required Variables DECLARE MyCursor_Name CURSOR --- Declare The Identify of Your Cursor [LOCAL | GLOBAL]--- Outline Scope of Your Cursor [FORWARD_ONLY | SCROLL] --Outline Motion Route of Your Cursor [ KEYSET | DYNAMIC |STATIC | FAST_FORWARD]--Outline The Fundamental Sort of Your Cursor [ SCROLL_LOCKS | OPTIMISTIC |READ_ONLY ]--Outline Locks for Your Cursor OPEN MyCursor_Name --Open Your Cursor FETCH NEXT FROM MyCursor_Name --Fetch information From Your Cursor -- Software Logic, Implement SQL QUery Insert, Replace, Delete and many others. CLOSE MyCursor_Name --Shut Your Cursor DEALLOCATE MyCursor_Name
Now, We’ll begin with our instance for demonstration. So, Let’s create a desk for college students and insert some dummy data within the scholar desk as per our requirement.
Create Desk
DECLARE @College students AS TABLE ( Id INT , RollNo INT , EnrollmentNo NVARCHAR(15) , Identify NVARCHAR(50) , Department NVARCHAR(50) , College NVARCHAR(50) )
Insert Data on Tabel
INSERT INTO @College students ( Id, RollNo, EnrollmentNo, Identify, Department, College ) VALUES ( 1, 1, N'', N'Nikunj Satasiya', N'CE', N'RK College' ), ( 2, 2, N'', N'Hiren Dobariya', N'CE', N'RK College' ), ( 3, 3, N'', N'Sapna Patel', N'IT', N'RK College' ), ( 4, 4, N'', N'Vivek Ghadiya', N'CE', N'RK College' ), ( 5, 5, N'', N'Pritesh Dudhat', N'CE', N'RK College' ), ( 5, 5, N'', N'Hardik Goriya', N'EC', N'RK College' ), ( 6, 6, N'', N'Sneh Patel', N'ME', N'RK College' )
Create/ Declare Cursor
DECLARE @Id INT , @RollNo INT, @Department NVARCHAR(50) , @12 months AS INT SET @12 months = RIGHT(YEAR(GETDATE()), 2) DECLARE MY_data CURSOR FOR SELECT Id , Department, RollNo, @12 months FROM @College students OPEN MY_data FETCH NEXT FROM MY_data INTO @Id, @Department, @RollNo,@12 months WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @EnrollmentNo NVARCHAR(15) SET @EnrollmentNo = 'SOE' + CAST(@12 months AS VARCHAR(2)) + CAST(@Department AS NVARCHAR(50)) + '000' + CAST(@RollNo AS NVARCHAR(10)) UPDATE @College students SET EnrollmentNo = @EnrollmentNo WHERE Id = @Id FETCH NEXT FROM MY_data INTO @Id, @Department, @RollNo,@12 months END CLOSE MY_data DEALLOCATE MY_data
Fetch Data from the Database
SELECT * FROM @College students
Clarification
If You analyzed the above instance then first I’ve created a brief desk with the title @College students and inserted some dummy data within the desk for performing information manipulation operations on information. Now in case you retrieve the consequence set utilizing a SELECT assertion then you’ll be able to see there’s within the desk the column with the title « EnrollmentNo » has a clean worth.
GIPHY App Key not set. Please check settings