[转载]如何掌握与使用游标
<p>信息来源:CCIDNET</p><p><a name="_Toc112150288"><font size="2"><span lang="EN-US">1<span></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: Arial; mso-hansi-font-family: Arial">游标的属性</span></font></a></p><p class="MsoNormal" style="TEXT-INDENT: 21pt; TEXT-ALIGN: left" align="left" /><div id="3"><font size="2"><span lang="EN-US"> oracle </span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">游标有</span><span lang="EN-US">4</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">个属性：</span><span lang="EN-US">%ISOPEN</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">，</span><span lang="EN-US">%FOUND</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">，</span><span lang="EN-US">%NOTFOUND</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">，</span><span lang="EN-US">%ROWCOUNT</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">。</span></font></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt; TEXT-ALIGN: left" align="left" /><div id="4"><font size="2"><span lang="EN-US"> %ISOPEN</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">判断游标是否被打开，如果打开</span><span lang="EN-US">%ISOPEN</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">等于</span><span lang="EN-US">true,</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">否则等于</span><span lang="EN-US">false</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">；</span></font></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt; TEXT-ALIGN: left" align="left" /><div id="5"><font size="2"><span lang="EN-US"> %FOUND %NOTFOUND</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">判断游标所在的行是否有效，如果有效，则</span><span lang="EN-US">%FOUNDD</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">等于</span><span lang="EN-US">true</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">，否则等于</span><span lang="EN-US">false</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">；</span></font></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt; TEXT-ALIGN: left" align="left" /><div id="6"><font size="2"><span lang="EN-US"> %ROWCOUNT</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">返回当前位置为止游标读取的记录行数。</span></font></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt; TEXT-ALIGN: center" align="center" /><div id="7"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> 隐式游标和显式游标</font></span></div><p /><div id="8"><div align="center"><table style="mso-cellspacing: 0cm" cellspacing="0" cellpadding="0" width="500" border="1"><tbody><tr><td><div></div><p class="MsoNormal" style="TEXT-INDENT: 21pt; TEXT-ALIGN: center" align="center" /><div id="9"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">隐式游标</span><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: "><o /></o /> </span></font></div><p /><div id="10"><div id="14"><div id="18"><div id="22"><div id="26"></div></div></div></div></div></td><td><div></div><div></div><div></div><div></div><div></div><p class="MsoNormal" style="TEXT-INDENT: 21pt; TEXT-ALIGN: center" align="center" /><div id="11"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">显式游标</span><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: "><o /></o /> </span></font></div><p /><div id="12"><div id="16"><div id="20"><div id="24"></div></div></div></div></td></tr><tr><td><div></div><div></div><div></div><div></div><p class="MsoNormal" /><div id="13"><font size="2"><span lang="EN-US">PL/SQL</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">维护，当执行查询时自动打开和关闭</span> <span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: "><o /></o /></span></font></div><p /><div id="10"><div id="14"><div id="18"><div id="22"><div id="26"></div></div></div></div></div></td><td><div></div><div></div><div></div><div></div><div></div><p class="MsoNormal" /><div id="15"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">在程序中显式定义、打开、关闭，游标有一个名字。</span><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: "><o /></o /> </span></font></div><p /><div id="12"><div id="16"><div id="20"><div id="24"></div></div></div></div></td></tr><tr><td><div></div><div></div><div></div><div></div><p class="MsoNormal" /><div id="17"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">游标属性前缀是</span><span lang="EN-US">SQL </span><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: "><o /></o /></span></font></div><p /><div id="10"><div id="14"><div id="18"><div id="22"><div id="26"></div></div></div></div></div></td><td><div></div><div></div><div></div><div></div><div></div><p class="MsoNormal" /><div id="19"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">游标属性的前缀是游标名</span><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: "><o /></o /> </span></font></div><p /><div id="12"><div id="16"><div id="20"><div id="24"></div></div></div></div></td></tr><tr><td><div></div><div></div><div></div><div></div><p class="MsoNormal" /><div id="21"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">属性</span><span lang="EN-US">%ISOPEN</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">总是为</span><span lang="EN-US">FALSE </span><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: "><o /></o /></span></font></div><p /><div id="10"><div id="14"><div id="18"><div id="22"><div id="26"></div></div></div></div></div></td><td><div></div><div></div><div></div><div></div><div></div><p class="MsoNormal" /><div id="23"><font size="2"><span lang="EN-US">%ISOPEN</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">根据游标的状态确定值</span><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: "><o /></o /> </span></font></div><p /><div id="12"><div id="16"><div id="20"><div id="24"></div></div></div></div></td></tr><tr><td><div></div><div></div><div></div><div></div><p class="MsoNormal" /><div id="25"><font size="2"><span lang="EN-US">SELECT</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">语句带有</span><span lang="EN-US">INTO</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">子串，只有一行数据被处理</span><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: "><o /></o /> </span></font></div><p /><div id="10"><div id="14"><div id="18"><div id="22"><div id="26"></div></div></div></div></div></td><td><div></div><div></div><div></div><div></div><div></div><p class="MsoNormal" /><div id="27"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">可以处理多行数据，在程序中设置循环，取出每一行数据。</span><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: "><o /></o /> </span></font></div><p /><div id="28"></div></td></tr></tbody></table></div></div><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="29"><span lang="EN-US"><font size="2"><o /></o /></font></span></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21.1pt; TEXT-ALIGN: left" align="left" /><div id="30"><strong><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"> 记录变量</span><span lang="EN-US"><o /></o /> </span></font></strong></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="31"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"> 定义一个记录变量使用</span><span lang="EN-US">TYPE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">命令和</span><span lang="EN-US">%ROWTYPE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">，</span></font></div><p /><div id="32"><div id="48"><span lang="EN-US"></span></div></div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="33"><div><!--Code highlighting produced by Actipro CodeHighlighter (freeware)[url]http://www.CodeHighlighter.com/--[/url]><font size="2"><span style="COLOR: #0000ff">DECLARE</span></font><span style="COLOR: #000000"><font size="2">R_emp EMP</font></span><span style="COLOR: #808080"><font size="2">%</font></span><font size="2"><span style="COLOR: #000000">ROWTYPE;</span><span style="COLOR: #0000ff">CURSOR</span><span style="COLOR: #000000"> c_emp </span><span style="COLOR: #0000ff">IS</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> emp;</span></font></div></div></pre></div><div id="34"><div id="38"></div></div><p class="MsoNormal" style="TEXT-INDENT: 21pt; TEXT-ALIGN: left" align="left" /><div id="35"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> 或：</font></span></div><p /><div id="36"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"></span></div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="37"><div><!--Code highlighting produced by Actipro CodeHighlighter (freeware)[url]http://www.CodeHighlighter.com/--[/url]><span style="COLOR: #0000ff">DECLARE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">CURSOR</span><span style="COLOR: #000000"> c_emp </span><span style="COLOR: #0000ff">IS</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> ename,salary </span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> emp;R_emp c_emp</span><span style="COLOR: #808080">%</span><span style="COLOR: #000000">ROWTYPE;</span></div></div></pre></div><div id="34"><div id="38"></div></div><p class="MsoNormal" style="TEXT-INDENT: 21.1pt" /><div id="39"><strong><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"> 带参数的游标</span><span lang="EN-US"><o /></o /> </span></font></strong></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="40"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> 与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下：</font></span></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="41"><span lang="EN-US"><font size="2"> CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;</font></span></div><p /><p class="MsoNormal" style="MARGIN-BOTTOM: 12pt; TEXT-INDENT: 21pt" /><div id="42"><span lang="EN-US"></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> 定义参数的语法如下：</font></span></div><p /><p class="MsoNormal" style="MARGIN-BOTTOM: 12pt; TEXT-INDENT: 21pt" /><div id="43"><span lang="EN-US"><font size="2"> Parameter_name [IN] data_type[{:=|DEFAULT} value]</font></span></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt; TEXT-ALIGN: left" align="left" /><div><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> 与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型，没有大小。</font></span><span lang="EN-US"></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> </font></span></div><div><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> </font></span></div><div><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> 另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。</font></span><span lang="EN-US"><br /></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> </font></span></div><div><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> 在打开游标时给参数赋值，语法如下：</font></span></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt; TEXT-ALIGN: left" align="left" /><div id="45"><span lang="EN-US"><font size="2"> OPEN cursor_name[value[,value]....];<strong><span><o /></o /> </span></strong></font></span></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21.1pt; TEXT-ALIGN: left" align="left" /><div id="46"><strong><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"> 游标</span><span lang="EN-US">FOR</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">循环</span><span lang="EN-US"><o /></o /> </span></font></strong></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="47"><font size="2"><span lang="EN-US"> FOR</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">循环的游标按照正常的声明方式声明，它的优点在于不需要显式的打开、关闭、取数据，测试数据的存在、定义存放数据的变量等等。游标</span><span lang="EN-US">FOR </span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">循环的语法如下：</span></font></div><p /><div id="32"><div id="48"><span lang="EN-US"></span></div></div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="49"><div><!--Code highlighting produced by Actipro CodeHighlighter (freeware)[url]http://www.CodeHighlighter.com/--[/url]><font size="2"><span style="COLOR: #0000ff">FOR</span><span style="COLOR: #000000"> record_name </span><span style="COLOR: #808080">IN</span></font><span style="COLOR: #000000"><font size="2">(corsor_name</font></span><font size="2"><span style="COLOR: #ff0000">[</span><span style="COLOR: #ff0000">(parameter[,parameter</span><span style="COLOR: #ff0000">]</span></font><font size="2"><span style="COLOR: #000000">...)]</span><span style="COLOR: #808080">|</span></font><font size="2"><span style="COLOR: #000000"> (query_difinition)LOOPstatements</span><span style="COLOR: #0000ff">END</span><span style="COLOR: #000000"> LOOP;</span></font></div></div></pre></div><p><font size="2">2<span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: Arial; mso-hansi-font-family: Arial">游标的更新和删除</span></font></p><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="2"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"> 在</span><span lang="EN-US">PL/SQL</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">中依然可以使用</span><span lang="EN-US">UPDATE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">和</span><span lang="EN-US">DELETE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。</span><span lang="EN-US">PL/SQL</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">提供了仅仅使用游标就可以执行删除或更新记录的方法。</span></font></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="3"><font size="2"><span lang="EN-US"> UPDATE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">或</span><span lang="EN-US">DELETE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">语句中的</span><span lang="EN-US">WHERE CURRENT OF</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">子串专门处理要执行</span><span lang="EN-US">UPDATE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">或</span><span lang="EN-US">DELETE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用</span><span lang="EN-US">FOR UPDATE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">子串，当对话使用</span><span lang="EN-US">FOR UPDATE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">子串打开一个游标时，所有返回集中的数据行都将处于行级（</span><span lang="EN-US">ROW-LEVEL</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">）独占式锁定，其他对象只能查询这些数据行，不能进行</span><span lang="EN-US">UPDATE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">、</span><span lang="EN-US">DELETE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">或</span><span lang="EN-US">SELECT...FOR UPDATE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">操作。</span></font></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="4"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> 语法：</font></span></div><p /><p class="MsoNormal" style="TEXT-ALIGN: left" align="left" /><div id="5"><span lang="EN-US"><font size="2"> FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..<br />[nowait]</font></span></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="6"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"> 在多表查询中，使用</span><span lang="EN-US">OF</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">子句来锁定特定的表</span><span lang="EN-US">,</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">如果忽略了</span><span lang="EN-US">OF</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下</span><span lang="EN-US">ORACLE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">将等待，直到数据行解锁。</span></font></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="7"><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"> 在</span><span lang="EN-US">UPDATE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">和</span><span lang="EN-US">DELETE</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">中使用</span><span lang="EN-US">WHERE CURRENT OF</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'">子串的语法如下：</span></font></div><p /><p style="MARGIN-BOTTOM: 12pt; TEXT-INDENT: 24pt" /><div id="8"><span lang="EN-US"><font size="2"> WHERE{CURRENT OF cursor_name|search_condition}</font></span></div><p /><p class="MsoNormal" style="TEXT-INDENT: 21pt" /><div id="9"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'"><font size="2"> 例： </font></span></div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="10"><div><!--Code highlighting produced by Actipro CodeHighlighter (freeware)[url]http://www.CodeHighlighter.com/--[/url]><font size="2"></font><font size="2"><span style="COLOR: #000000">DELCARE</span><span style="COLOR: #0000ff">CURSOR</span><span style="COLOR: #000000"> c1 </span><span style="COLOR: #0000ff">IS</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span></font><font size="2"><span style="COLOR: #000000"> empno,salary</span><span style="COLOR: #0000ff">FROM</span></font><font size="2"><span style="COLOR: #000000"> emp</span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"> comm </span><span style="COLOR: #0000ff">IS</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">NULL</span></font><span style="COLOR: #000000"><font size="2"></font></span><font size="2"><span style="COLOR: #0000ff">FOR</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">UPDATE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">OF</span></font><font size="2"><span style="COLOR: #000000"> comm;v_comm </span><span style="FONT-WEIGHT: bold; COLOR: #000000">NUMBER</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">10</span><span style="COLOR: #000000">,</span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span></font><font size="2"><span style="COLOR: #000000">);</span><span style="COLOR: #0000ff">BEGIN</span></font><span style="COLOR: #000000"><font size="2"></font></span><font size="2"><span style="COLOR: #0000ff">FOR</span><span style="COLOR: #000000"> r1 </span><span style="COLOR: #808080">IN</span></font><font size="2"><span style="COLOR: #000000"> c1 LOOP</span><span style="COLOR: #0000ff">IF</span><span style="COLOR: #000000"> r1.salary</span><span style="COLOR: #808080"><</span><span style="FONT-WEIGHT: bold; COLOR: #800000">500</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">THEN</span></font><span style="COLOR: #000000"><font size="2">v_comm:</font></span><font size="2"><span style="COLOR: #808080">=</span><span style="COLOR: #000000">r1.salary</span><span style="COLOR: #808080">*</span><span style="FONT-WEIGHT: bold; COLOR: #800000">0.25</span></font><font size="2"><span style="COLOR: #000000">;ELSEIF r1.salary</span><span style="COLOR: #808080"><</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1000</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">THEN</span></font><span style="COLOR: #000000"><font size="2">v_comm:</font></span><font size="2"><span style="COLOR: #808080">=</span><span style="COLOR: #000000">r1.salary</span><span style="COLOR: #808080">*</span><span style="FONT-WEIGHT: bold; COLOR: #800000">0.20</span></font><font size="2"><span style="COLOR: #000000">;ELSEIF r1.salary</span><span style="COLOR: #808080"><</span><span style="FONT-WEIGHT: bold; COLOR: #800000">3000</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">THEN</span></font><span style="COLOR: #000000"><font size="2">v_comm:</font></span><font size="2"><span style="COLOR: #808080">=</span><span style="COLOR: #000000">r1.salary</span><span style="COLOR: #808080">*</span><span style="FONT-WEIGHT: bold; COLOR: #800000">0.15</span></font><font size="2"><span style="COLOR: #000000">;</span><span style="COLOR: #0000ff">ELSE</span></font><span style="COLOR: #000000"><font size="2">v_comm:</font></span><font size="2"><span style="COLOR: #808080">=</span><span style="COLOR: #000000">r1.salary</span><span style="COLOR: #808080">*</span><span style="FONT-WEIGHT: bold; COLOR: #800000">0.12</span></font><font size="2"><span style="COLOR: #000000">;</span><span style="COLOR: #0000ff">END</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">IF</span></font><font size="2"><span style="COLOR: #000000">;</span><span style="COLOR: #0000ff">UPDATE</span></font><font size="2"><span style="COLOR: #000000"> emp;</span><span style="COLOR: #0000ff">SET</span><span style="COLOR: #000000"> comm</span><span style="COLOR: #808080">=</span></font><font size="2"><span style="COLOR: #000000">v_comm</span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">CURRENT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">OF</span></font><font size="2"><span style="COLOR: #000000"> c1l;</span><span style="COLOR: #0000ff">END</span></font><font size="2"><span style="COLOR: #000000"> LOOP;</span><span style="COLOR: #0000ff">END</span></font></div></div></pre></div><p><a name="_Toc112150290"><font size="2"><span lang="EN-US">3<span></span></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: Arial; mso-hansi-font-family: Arial">游标的打开机制</span></font></a></p><p class="MsoNormalIndent" style="TEXT-INDENT: 21pt" /><div id="2"><div id="9"><span lang="EN-GB"><div id="12"><font size="2"><o /></o /></font></div></span></div></div><p /><div id="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Book Antiqua'; mso-hansi-font-family: 'Book Antiqua'"></span></div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="4"><div><!--Code highlighting produced by Actipro CodeHighlighter (freeware)[url]http://www.CodeHighlighter.com/--[/url]><font size="2"><strong></strong><span style="COLOR: #0000ff">DECLARE</span></font><span style="COLOR: #000000"><font size="2"></font></span><font size="2"><span style="COLOR: #008080">--</span><span style="COLOR: #008080">Created by xsb on 2005-08-17 For：演示游标的打开机制</span></font><span style="COLOR: #008080"><font size="2"></font></span><span style="COLOR: #000000"><font size="2">I </font></span><span style="FONT-WEIGHT: bold; COLOR: #000000"><font size="2">INTEGER</font></span><font size="2"><span style="COLOR: #000000">;T </span><span style="FONT-WEIGHT: bold; COLOR: #000000">NUMBER</span></font><font size="2"><span style="COLOR: #000000">;C SYS_REFCURSOR;</span><span style="COLOR: #0000ff">BEGIN</span></font><span style="COLOR: #000000"><font size="2">T :</font></span><span style="COLOR: #808080"><font size="2">=</font></span><font size="2"><span style="COLOR: #000000"> DBMS_UTILITY.GET_TIME;</span><span style="COLOR: #0000ff">OPEN</span><span style="COLOR: #000000"> C </span><span style="COLOR: #0000ff">FOR</span><span style="COLOR: #000000">(</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">SELECT 1 FROM dba_objects a GROUP BY a.owner</span><span style="COLOR: #ff0000">'</span></font><font size="2"><span style="COLOR: #000000">);DBMS_OUTPUT.PUT_LINE(</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">游标打开时间：</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">||</span><span style="COLOR: #000000"> (DBMS_UTILITY.GET_TIME </span><span style="COLOR: #808080">-</span></font><font size="2"><span style="COLOR: #000000"> T));T :</span><span style="COLOR: #808080">=</span></font><font size="2"><span style="COLOR: #000000"> DBMS_UTILITY.GET_TIME;</span><span style="COLOR: #0000ff">FETCH</span><span style="COLOR: #000000"> C </span><span style="COLOR: #0000ff">INTO</span></font><font size="2"><span style="COLOR: #000000"> I;DBMS_OUTPUT.PUT_LINE(</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">第一次fetch时间：</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">||</span><span style="COLOR: #000000"> (DBMS_UTILITY.GET_TIME </span><span style="COLOR: #808080">-</span></font><font size="2"><span style="COLOR: #000000"> T));T :</span><span style="COLOR: #808080">=</span></font><font size="2"><span style="COLOR: #000000"> DBMS_UTILITY.GET_TIME;</span><span style="COLOR: #0000ff">FETCH</span><span style="COLOR: #000000"> C </span><span style="COLOR: #0000ff">INTO</span></font><font size="2"><span style="COLOR: #000000"> I;DBMS_OUTPUT.PUT_LINE(</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">第二次fetch时间：</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">||</span><span style="COLOR: #000000"> (DBMS_UTILITY.GET_TIME </span><span style="COLOR: #808080">-</span></font><font size="2"><span style="COLOR: #000000"> T));T :</span><span style="COLOR: #808080">=</span></font><font size="2"><span style="COLOR: #000000"> DBMS_UTILITY.GET_TIME;</span><span style="COLOR: #0000ff">FETCH</span><span style="COLOR: #000000"> C </span><span style="COLOR: #0000ff">INTO</span></font><font size="2"><span style="COLOR: #000000"> I;DBMS_OUTPUT.PUT_LINE(</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">第三次fetch时间：</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">||</span><span style="COLOR: #000000"> (DBMS_UTILITY.GET_TIME </span><span style="COLOR: #808080">-</span></font><font size="2"><span style="COLOR: #000000"> T));</span><span style="COLOR: #0000ff">close</span></font><font size="2"><span style="COLOR: #000000"> c;</span><span style="COLOR: #0000ff">END</span></font><span style="COLOR: #000000"><font size="2">;<strong></strong></font></span></div></div></pre></div><p class="MsoNormalIndent" style="TEXT-INDENT: 21.1pt" /><div id="5"><font size="2"><strong> 游标打开时仅是分配了一块内存和一个系统的<span lang="EN-GB">SCN</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Book Antiqua'; mso-hansi-font-family: 'Book Antiqua'">号（版本号）。</span><span lang="EN-GB"><o /></o /> </span></strong></font></div><p /><div id="6"></div><p class="MsoNormalIndent" style="TEXT-INDENT: 21.1pt" /><div id="7"><strong><font size="2"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Book Antiqua'; mso-hansi-font-family: 'Book Antiqua'"> 游标打开后，表数据变化不影响游标记录集，原因就是</span><span lang="EN-GB">SCN</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Book Antiqua'; mso-hansi-font-family: 'Book Antiqua'">号已分配！</span><span lang="EN-GB"><o /></o /> </span></font></strong></div><p /><p class="MsoNormalIndent" style="TEXT-INDENT: 21pt" /><div id="8"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Book Antiqua'; mso-hansi-font-family: 'Book Antiqua'"><font size="2"> 如：</font></span></div><p /><p class="MsoNormalIndent" style="TEXT-INDENT: 21pt" /><div id="2"><div id="9"><span lang="EN-GB"><div id="12"><font size="2"><o /></o /></font></div></span></div></div><p /><div id="10"><span lang="EN-GB"></span></div><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="11"><div><!--Code highlighting produced by Actipro CodeHighlighter (freeware)[url]http://www.CodeHighlighter.com/--[/url]><font size="2"><span style="COLOR: #0000ff">DECLARE</span></font><span style="COLOR: #000000"><font size="2"></font></span><font size="2"><span style="COLOR: #008080">--</span><span style="COLOR: #008080">Created by xsb on 2005-08-17 For：演示游标的打开机制2</span></font><span style="COLOR: #008080"><font size="2"></font></span><span style="COLOR: #000000"><font size="2">I </font></span><span style="FONT-WEIGHT: bold; COLOR: #000000"><font size="2">INTEGER</font></span><font size="2"><span style="COLOR: #000000">;</span><span style="COLOR: #0000ff">CURSOR</span><span style="COLOR: #000000"> C </span><span style="COLOR: #0000ff">IS</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> c </span><span style="COLOR: #0000ff">FROM</span></font><font size="2"><span style="COLOR: #000000"> t1;</span><span style="COLOR: #0000ff">BEGIN</span></font><span style="COLOR: #000000"><font size="2"></font></span><font size="2"><span style="COLOR: #0000ff">EXECUTE</span><span style="COLOR: #000000"> IMMEDIATE </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">drop table t1</span><span style="COLOR: #ff0000">'</span></font><font size="2"><span style="COLOR: #000000">;</span><span style="COLOR: #0000ff">EXECUTE</span><span style="COLOR: #000000"> IMMEDIATE </span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">create table t1 (c number)</span><span style="COLOR: #ff0000">'</span></font><font size="2"><span style="COLOR: #000000">;</span><span style="COLOR: #0000ff">FOR</span><span style="COLOR: #000000"> I </span><span style="COLOR: #808080">IN</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"> .. </span><span style="FONT-WEIGHT: bold; COLOR: #800000">10</span></font><font size="2"><span style="COLOR: #000000"> LOOP</span><span style="COLOR: #0000ff">INSERT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INTO</span><span style="COLOR: #000000"> T1 </span><span style="COLOR: #0000ff">VALUES</span></font><font size="2"><span style="COLOR: #000000"> (I);</span><span style="COLOR: #0000ff">END</span></font><font size="2"><span style="COLOR: #000000"> LOOP;</span><span style="COLOR: #0000ff">COMMIT</span></font><font size="2"><span style="COLOR: #000000">;</span><span style="COLOR: #0000ff">OPEN</span></font><font size="2"><span style="COLOR: #000000"> C ;</span><span style="COLOR: #0000ff">DELETE</span></font><font size="2"><span style="COLOR: #000000"> T1;</span><span style="COLOR: #0000ff">COMMIT</span></font><font size="2"><span style="COLOR: #000000">;LOOP</span><span style="COLOR: #0000ff">FETCH</span><span style="COLOR: #000000"> C </span><span style="COLOR: #0000ff">INTO</span></font><font size="2"><span style="COLOR: #000000"> I;</span><span style="COLOR: #0000ff">EXIT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">WHEN</span><span style="COLOR: #000000"> C</span><span style="COLOR: #808080">%</span></font><font size="2"><span style="COLOR: #000000">NOTFOUND;DBMS_OUTPUT</span></font></div></div></pre></div><p /><p align="center"><span class="content01"></span></p>页:
[1]