SQL中游标(cursor)的基本使用实例

与善为伍 發表於 2023-6-17 00:00:00

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li>
<span>类型：</span>
</li><li>
1.普通游标</li><li>
2.滚动游标</li><li>
具体FETCH用法：<ul class="second_class_ul"><li>
Arguments</li></ul></li><li>
总结<ul class="second_class_ul"></ul></li></ul></div><div id="navCategory">
<h5 class="catalogue">
目录</h5>
<ul class="first_class_ul">
<li>
<span>类型：</span>
</li>
<li>
1.普通游标</li>
<li>
2.滚动游标</li>
<li>
具体FETCH用法：
<ul class="second_class_ul">
<li>
Arguments</li>
</ul>
</li>
<li>
总结
<ul class="second_class_ul"></ul>
</li>
</ul>
</div>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
<span>类型：</span>
</h2>
<p>
　　1.普通游标只有NEXT操作</p>
<p>
　　2.滚动游标有多种操作</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
1.普通游标</h2>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_176646">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">DECLARE</code> <code class="sql plain">@username </code><code class="sql keyword">varchar</code><code class="sql plain">(20),@UserId </code><code class="sql keyword">varchar</code><code class="sql plain">(100)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">DECLARE</code> <code class="sql plain">cursor_name </code><code class="sql keyword">CURSOR</code> <code class="sql keyword">FOR</code> <code class="sql comments">--定义游标</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces">    </code><code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">10 UserId,UserName </code><code class="sql keyword">FROM</code> <code class="sql plain">UserInfo</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces">    </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">UserId </code><code class="sql keyword">DESC</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">OPEN</code> <code class="sql plain">cursor_name </code><code class="sql comments">--打开游标</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">FETCH</code> <code class="sql keyword">NEXT</code> <code class="sql keyword">FROM</code> <code class="sql plain">cursor_name </code><code class="sql keyword">INTO</code> <code class="sql plain">@UserId,@username </code><code class="sql comments">--抓取下一行游标数据</code>
</div>
<div class="line number7 index6 alt2">
<code class="sql plain">WHILE @@FETCH_STATUS = 0</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql spaces">    </code><code class="sql keyword">BEGIN</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces">        </code><code class="sql plain">PRINT </code><code class="sql string">'用户ID：'</code><code class="sql plain">+@UserId+</code><code class="sql string">'            '</code><code class="sql plain">+</code><code class="sql string">'用户名：'</code><code class="sql plain">+@username</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql spaces">        </code><code class="sql keyword">FETCH</code> <code class="sql keyword">NEXT</code> <code class="sql keyword">FROM</code> <code class="sql plain">cursor_name </code><code class="sql keyword">INTO</code> <code class="sql plain">@UserId,@username</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces">    </code><code class="sql keyword">END</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql keyword">CLOSE</code> <code class="sql plain">cursor_name </code><code class="sql comments">--关闭游标</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql keyword">DEALLOCATE</code> <code class="sql plain">cursor_name </code><code class="sql comments">--释放游标</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
结果：</p>
<blockquote>
<p>
用户ID：zhizhi 用户名：邓鸿芝</p>
<p>
用户ID：yuyu 用户名：魏雨</p>
<p>
用户ID：yujie 用户名：李玉杰</p>
<p>
用户ID：yuanyuan 用户名：王梦缘</p>
<p>
用户ID：YOUYOU 用户名：lisi</p>
<p>
用户ID：yiyiren 用户名：任毅</p>
<p>
用户ID：yanbo 用户名：王艳波</p>
<p>
用户ID：xuxu 用户名：陈佳绪</p>
<p>
用户ID：xiangxiang 用户名：李庆祥</p>
<p>
用户ID：wenwen 用户名：魏文文</p>
</blockquote>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
2.滚动游标</h2>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_809652">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql comments">--带SCROLL选项的游标</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">SET</code> <code class="sql plain">NOCOUNT </code><code class="sql keyword">ON</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">DECLARE</code> <code class="sql plain">C </code><code class="sql keyword">SCROLL</code> <code class="sql keyword">CURSOR</code> <code class="sql keyword">FOR</code> <code class="sql comments">--SCORLL 后，有了更多的游标操作（滚动游标）</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces">    </code><code class="sql keyword">SELECT</code> <code class="sql keyword">TOP</code> <code class="sql plain">10 UserId,UserName </code><code class="sql keyword">FROM</code> <code class="sql plain">UserInfo</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces">    </code><code class="sql keyword">ORDER</code> <code class="sql keyword">BY</code> <code class="sql plain">UserId </code><code class="sql keyword">DESC</code>
</div>
<div class="line number6 index5 alt1">
<code class="sql keyword">OPEN</code> <code class="sql plain">C </code>
</div>
<div class="line number7 index6 alt2">
<code class="sql keyword">FETCH</code> <code class="sql keyword">LAST</code> <code class="sql keyword">FROM</code> <code class="sql plain">C   </code><code class="sql comments">--最后一行的数据，并将当前行为指定行</code>
</div>
<div class="line number8 index7 alt1">
<code class="sql keyword">FETCH</code> <code class="sql keyword">ABSOLUTE</code> <code class="sql plain">4 </code><code class="sql keyword">FROM</code> <code class="sql plain">C </code><code class="sql comments">--从第一行开始的第4行数据，并将当前行为指定行这里的n可正可负，n>0 往下翻，n<0 往上翻</code>
</div>
<div class="line number9 index8 alt2">
<code class="sql keyword">FETCH</code> <code class="sql keyword">RELATIVE</code> <code class="sql plain">3 </code><code class="sql keyword">FROM</code> <code class="sql plain">C </code><code class="sql comments">--相对于当前行的后3行数据，并将当前行为指定行这里的n可正可负</code>
</div>
<div class="line number10 index9 alt1">
<code class="sql keyword">FETCH</code> <code class="sql keyword">RELATIVE</code> <code class="sql plain">-2 </code><code class="sql keyword">FROM</code> <code class="sql plain">C </code><code class="sql comments">--相对于当前行的前2行数据，并将当前行为指定行</code>
</div>
<div class="line number11 index10 alt2">
<code class="sql keyword">FETCH</code> <code class="sql keyword">PRIOR</code> <code class="sql keyword">FROM</code> <code class="sql plain">C   </code><code class="sql comments">----相对于当前行的前1行数据</code>
</div>
<div class="line number12 index11 alt1">
<code class="sql keyword">FETCH</code> <code class="sql keyword">FIRST</code> <code class="sql keyword">FROM</code> <code class="sql plain">C   </code><code class="sql comments">--刚开始第一行的数据，并将当前行为指定行</code>
</div>
<div class="line number13 index12 alt2">
<code class="sql keyword">FETCH</code> <code class="sql keyword">NEXT</code> <code class="sql keyword">FROM</code> <code class="sql plain">C   </code><code class="sql comments">--相对于当前行的后1行数据</code>
</div>
<div class="line number14 index13 alt1">
</div>
<div class="line number15 index14 alt2">
<code class="sql keyword">CLOSE</code> <code class="sql plain">C</code>
</div>
<div class="line number16 index15 alt1">
<code class="sql keyword">DEALLOCATE</code> <code class="sql plain">C</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
结果（可以参考第一个结果分析）：</p>
<p>
<img title="SQL中游标(cursor)的基本使用实例" alt="SQL中游标(cursor)的基本使用实例" src="https://zhuji.jb51.net/uploads/img/202305/e388016bf92282b64d1750b9b14b1167.jpg"></p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
具体FETCH用法：</h2>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_950753">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">FETCH</code>  </div>
<div class="line number2 index1 alt1">
<code class="sql spaces">          </code><code class="sql plain">[ [ </code><code class="sql keyword">NEXT</code> <code class="sql plain">| </code><code class="sql keyword">PRIOR</code> <code class="sql plain">| </code><code class="sql keyword">FIRST</code> <code class="sql plain">| </code><code class="sql keyword">LAST</code>  </div>
<div class="line number3 index2 alt2">
<code class="sql spaces">                    </code><code class="sql plain">| </code><code class="sql keyword">ABSOLUTE</code> <code class="sql plain">{ n | @nvar }   </code>
</div>
<div class="line number4 index3 alt1">
<code class="sql spaces">                    </code><code class="sql plain">| </code><code class="sql keyword">RELATIVE</code> <code class="sql plain">{ n | @nvar }   </code>
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces">               </code><code class="sql plain">]   </code>
</div>
<div class="line number6 index5 alt1">
<code class="sql spaces">               </code><code class="sql keyword">FROM</code>  </div>
<div class="line number7 index6 alt2">
<code class="sql spaces">          </code><code class="sql plain">]   </code>
</div>
<div class="line number8 index7 alt1">
<code class="sql plain">{ { [ </code><code class="sql keyword">GLOBAL</code> <code class="sql plain">] cursor_name } | @cursor_variable_name }   </code>
</div>
<div class="line number9 index8 alt2">
<code class="sql plain">[ </code><code class="sql keyword">INTO</code> <code class="sql plain">@variable_name [ ,...n ] ]</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p class="maodian">
</p>
<p class="maodian"></p><h3>
Arguments</h3>
<p>
<strong>NEXT</strong></p>
<p>
Returns the result row immediately following the current row and increments the current row to the row returned. If FETCH NEXT is the first fetch against a cursor, it returns the first row in the result set. NEXT is the default cursor fetch option.</p>
<p>
<strong>PRIOR</strong></p>
<p>
Returns the result row immediately preceding the current row, and decrements the current row to the row returned. If FETCH PRIOR is the first fetch against a cursor, no row is returned and the cursor is left positioned before the first row.</p>
<p>
<strong>FIRST</strong></p>
<p>
Returns the first row in the cursor and makes it the current row.</p>
<p>
<strong>LAST</strong></p>
<p>
Returns the last row in the cursor and makes it the current row.</p>
<p>
<strong>ABSOLUTE { n| @nvar}</strong></p>
<p>
If n or @nvar is positive, returns the row n rows from the front of the cursor and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows before the end of the cursor and makes the returned row the new current row. If n or @nvar is 0, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.</p>
<p>
<strong>RELATIVE { n| @nvar}</strong></p>
<p>
If n or @nvar is positive, returns the row n rows beyond the current row and makes the returned row the new current row. If n or @nvar is negative, returns the row n rows prior to the current row and makes the returned row the new current row. If n or @nvar is 0, returns the current row. If FETCH RELATIVE is specified with n or @nvar set to negative numbers or 0 on the first fetch done against a cursor, no rows are returned. n must be an integer constant and @nvar must be smallint, tinyint, or int.</p>
<p>
<strong>GLOBAL</strong></p>
<p>
Specifies that cursor_name refers to a global cursor.</p>
<p>
cursor_name</p>
<p>
Is the name of the open cursor from which the fetch should be made. If both a global and a local cursor exist with cursor_name as their name, cursor_name to the global cursor if GLOBAL is specified and to the local cursor if GLOBAL is not specified.</p>
<p>
<strong>@cursor_variable_name</strong></p>
<p>
Is the name of a cursor variable referencing the open cursor from which the fetch should be made.</p>
<p>
<strong>INTO @variable_name[ ,...n]</strong></p>
<p>
Allows data from the columns of a fetch to be placed into local variables. Each variable in the list, from left to right, is associated with the corresponding column in the cursor result set. The data type of each variable must either match or be a supported implicit conversion of the data type of the corresponding result set column. The number of variables must match the number of columns in the cursor select list.</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
总结</h2>
<p>
到此这篇关于SQL中游标(cursor)基本使用的文章就介绍到这了,更多相关SQL游标的使用内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！</p>
<p>
原文链接：https://www.cnblogs.com/feiquan/p/8684925.html</p>

頁: [1]

琼殿技术论坛's Archiver

SQL中游标(cursor)的基本使用实例