SQL Server 【提高】 游标

sql语言按照标准处理对象是集合，但有时候也需要针对单行的处理，所以就有了游标，类似C语言的指针一样。

使用方法

创建游标

<span style="color: #0000ff;">declare</span> cursor_name <span style="color: #008080;">--</span><span style="color: #008080;">游标名称，唯一标识</span>
<span style="color: #ff0000;">[</span><span style="color: #ff0000;">insensitive</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">scroll</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">cursor</span> 
<span style="color: #0000ff;">for</span><span style="color: #000000;">
select_statement </span><span style="color: #008080;">--</span><span style="color: #008080;">查询语句</span>
<span style="color: #ff0000;">[</span><span style="color: #ff0000;">for {read only| update [of column_name [,…n</span><span style="color: #ff0000;">]</span>]}]

read only
设置游标数据只读，指定read only后，对底层表的改动不会更新其游标数据。

示例

<span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">table</span> #<span style="color: #0000ff;">temp</span>
<span style="color: #008080;">--</span><span style="color: #008080;">创建游标</span>
<span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span> #<span style="color: #0000ff;">temp</span><span style="color: #000000;">(
code </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">128</span><span style="color: #000000;">),
name </span><span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">256</span><span style="color: #000000;">)
)
</span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> #<span style="color: #0000ff;">temp</span> <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1111</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">aaaa</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> #<span style="color: #0000ff;">temp</span> <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1112</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">bbbb</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> #<span style="color: #0000ff;">temp</span> <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1113</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">cccc</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">declare</span> cursor_demo scroll <span style="color: #0000ff;">cursor</span> 
<span style="color: #0000ff;">for</span>
<span style="color: #0000ff;">select</span> code,name <span style="color: #0000ff;">from</span> #<span style="color: #0000ff;">temp</span> <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span><span style="color: #000000;"> code
</span><span style="color: #008080;">--</span><span style="color: #008080;">打开游标</span>
<span style="color: #0000ff;">open</span><span style="color: #000000;"> cursor_demo
</span><span style="color: #008080;">--</span><span style="color: #008080;">定义变量</span>
<span style="color: #0000ff;">declare</span> <span style="color: #008000;">@code</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">128</span>), <span style="color: #008000;">@name</span> <span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">256</span><span style="color: #000000;">)
</span><span style="color: #008080;">--</span><span style="color: #008080;">提取最后一行学校信息</span>
<span style="color: #0000ff;">fetch</span> last <span style="color: #0000ff;">from</span> cursor_demo <span style="color: #0000ff;">into</span> <span style="color: #008000;">@code</span>, <span style="color: #008000;">@name</span>
<span style="color: #0000ff;">print</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">code：</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #008000;">@code</span> <span style="color: #808080;">+</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;"> name：</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">+</span> <span style="color: #008000;">@name</span>
<span style="color: #008080;">--</span><span style="color: #008080;">关闭游标</span>
<span style="color: #0000ff;">close</span><span style="color: #000000;"> cursor_demo
</span><span style="color: #008080;">--</span><span style="color: #008080;">释放游标</span>
<span style="color: #0000ff;">deallocate</span> cursor_demo

SQL Server 【提高】 游标

标签：sans   inline   对象   span   order   名称   play   dealloc   isp