Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
Can any one explain me a Cursor in simple language for sql server with example
Posted 16-Aug-12 21:47pm
lalitkr1.5K
Edited 16-Aug-12 21:48pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

I have given u small eg.
Declare @ID as varchar(500)
Declare @Name as varchar(30)
Declare @MOBILE as varchar(20)
 
Declare MY_data CURSOR FOR
 
Select Name ,Mobile from  My_table (Nolock)
 
OPEN MY_data
    FETCH NEXT FROM MY_data INTO @Name ,@MOBILE
        WHILE @@FETCH_STATUS = 0
        BEGIN
 
            Set @ID = (Generate Max ID)
            Insert Records @ID,@Name ,@MOBILE
 
        FETCH NEXT FROM MY_data INTO @Name ,@MOBILE
        END
    CLOSE MY_data
DEALLOCATE MY_data
 
Here I want to generate a Max ID and Insert the data so I have used the Cursor.
 
Let me say one thing: DON'T use cursors. They should be your preferred way of killing the performance of an entire system. Most beginners use cursors and don't realize the performance hit they have. They use memory; they lock tables in weird ways, and they are slow. Worst of all, they defeat most of the performance optimization your DBA can do. Did you know that every FETCH being executed has about the same performance of executing a SELECT? This means that if your cursor has 10,000 records, it will execute about 10,000 SELECTs! If you can do this in a couple of SELECT, UPDATE or DELETE, it will be much faster.
 
Beginner SQL programmers find in cursors a comfortable and familiar way of coding. Well, unfortunately this lead to bad performance. The whole purpose of SQL is specifying what you want, not how it should be done.
 
I've once rewritten a cursor-based stored procedure and substituted some code for a pair of traditional SQL queries. The table had only 100,000 records and the stored procedure used to take 40 minutes to process. You should see the face of the poor programmer when the new stored procedure took 10 seconds to run!
 
Sometimes it's even faster to create a small application that gets all the data, proccess it and update the server. T-SQL was not done with loop performance in mind.
 
If you are reading this article, I need to mention: there is no good use for cursors; I have never seen cursors being well used, except for DBA work. And good DBAs, most of the time, know what they are doing. But, if you are reading this, you are not a DBA, right?
  Permalink  
Comments
Member 8468375 at 6-May-14 11:44am
   
neat
Arunprasath Natarajan at 7-May-14 4:58am
   
Tan Q
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

A cursor is a set of rows together with a pointer that identifies a current row.
Or
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP and visual basic.
 
Syntax:
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
See more Declare Cursor[^]
Simple Example of Cursor[^]
cursors in sqlserver[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Hi,
 
You need to read some books and Articles,
 
MSDN Articles on Cursor[^]
 
MSDN will give you all your required information with example. go through all the links given in above link.
 
Best of luck
 
Thanks
-Amit Gajjar
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.
This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
There are two types of cursors in PL/SQL:

Implicit cursors
These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
Explicit cursors
They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.
Implicit Cursors: Application
When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.
Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected.
When a SELECT... INTO statement is executed in a PL/SQL Block, implicit cursor attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.
The status of the cursor for each of these attributes are defined in the below table.
Attributes Return Value Example
%FOUND The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row. SQL%FOUND
The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row.
%NOTFOUND The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row. SQL%NOTFOUND
The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row.
%ROWCOUNT Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT SQL%ROWCOUNT
 

For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:
DECLARE var_rows number(5);
BEGIN
UPDATE employee
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.
  Permalink  
Comments
manoharansen at 31-Aug-13 9:16am
   
knkjknklklk;lmkmkm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 16 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100