什么是sql server中的Cursor [英] What is Cursor in sql server

问题描述

任何人都可以用sql server的简单语言向我解释一个Cursor，例如

解决方案

我给你的小例如。

 声明  @ ID   as   varchar （ 500 ）
 声明  @ Name   as   varchar （ 30 ）
 声明  @ MOBILE   as   varchar （ 20 ） 
 
 声明 MY_data  CURSOR   FOR  
 
 选择名称，手机来自 My_table（ Nolock ）
 
  OPEN  MY_data 
  FETCH  NEXT  FROM  MY_data  INTO   @ Name ， @ MOBILE  
  WHILE   @@ FETCH_STATUS  =  0  
  BEGIN  
 
 设置  @ ID  =（生成最大ID）
 插入记录 @ ID ， @ Name ， @ MOBILE  
 
  FETCH  NEXT  FROM  MY_da ta  INTO   @ Name ， @ MOBILE  
  END  
  CLOSE  MY_data 
  DEALLOCATE  MY_data 

这里我想生成一个Max ID并插入数据，所以我使用了Cursor。



让我说一件事：不要使用游标。它们应该是您杀死整个系统性能的首选方式。大多数初学者都使用游标，并没有意识到他们所拥有的性能。他们使用记忆;他们以奇怪的方式锁定桌子，而且速度慢。最糟糕的是，它们击败了DBA可以执行的大部分性能优化。您是否知道正在执行的每个FETCH具有与执行SELECT相同的性能？这意味着如果您的光标有10,000条记录，它将执行大约10,000个SELECT！如果你可以在几个SELECT，UPDATE或DELETE中执行此操作，它会快得多。



初学者SQL程序员在游标中找到一种舒适而熟悉的编码方式。好吧，不幸的是，这会导致糟糕的表现。 SQL的整个目的是指定你想要的，而不是它应该如何完成。



我曾经重写过基于游标的存储过程并替换了一些代码对于一对传统的SQL查询。该表只有100,000条记录，而存储过程需要40分钟才能处理。当新的存储过程花费10秒钟运行时，您应该看到可怜的程序员的脸！



有时创建一个小应用程序甚至更快所有数据，处理它并更新服务器。 T-SQL没有考虑到循环性能。



如果你正在阅读这篇文章，我需要提一下：游标没有很好的用处;除了DBA工作之外，我从未见过使用游标的方法。大多数时候，优秀的DBA都知道他们在做什么。但是，如果您正在阅读本文，那么您不是DBA，对吗？

A 游标是一组行以及标识当前行的指针。

或

光标是一个数据库对象，用于处理数据中的数据在逐行基础上设置，在ASP和visual basic中类似于记录集。



语法：

  DECLARE  cursor_name [INSENSITIVE] [SCROLL]  CURSOR  
  FOR  select_statement 
 [ FOR  {阅读仅限| 更新 [ OF  column_name [，... n]]}] 
 [;] 
 Transact-SQL扩展语法
  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  < span class =code-keyword> UPDATE  [ OF  column_name [，... n]]] 
 [;] 

查看更多声明光标 [ ^ ]

光标的简单示例 [ ^ ]

sqlserver中的游标 [ ^ ]

http：//www.dotnetfunda .com / interview / exam296-what-c​​ursor-in-sql-server.aspx [ ^ ]

Can any one explain me a Cursor in simple language for sql server with example

解决方案

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?

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[^]

http://www.dotnetfunda.com/interview/exam296-what-is-cursor-in-sql-server.aspx[^]

这篇关于什么是sql server中的Cursor的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！