SQL是一種基于集合的語言(a set-based language) ,他更擅長操作和提出一組數據,而不是對
數據進行一行一行的處理。
SQL is a set-based language ,meaning that is excels at mantipulating and retrieving
set of rows ,rather than performing single row-by-row processing.
如果你的程序里一定要一條一條的執行,那么一定要先考慮使用如while循環,子查詢,
臨時表,表變量等等,如果這些都不能滿足要求,在考慮使用游標。
T-SQL中游標的生存周期:
1.用返回一個有效結果集的sql語句來定義一個游標。
a cursor is defined via a SQL statement that returns a valid result set.
2. 打開游標
3. 一旦游標被打開就可以從游標中每次取出一行數據,要根據游標的定義可以向前去數據或
向后取數據
the rows can be fetched moving forword or backword ,depending on the original cursor definition.
4. 根據游標的類型,數據可以被修改或者只能讀。
5.最后,用完游標后,必須被顯示的關閉,并且從內存中移除。
游標定義格式:
declare cursor_name cursor
[local|global]
[forword_only|scroll]
[static|keyset|dynamic|fast_forword]
[read_only| scroll_locks|optimistic]
[type_warning]
for select_statement[for update [of column[,...]]]
The select_statement argument is the query used to define the data within the cursor. Avoid
using a query that hasmore columns and rows than will actually be used, because cursors, while
open, are kept inmemory. The UPDATE [OF column_name [,...n]] is used to specify those columns
that are allowed to be updated by the cursor.