chap11_1.html

来自「Oracle资料大集合」· HTML 代码 · 共 426 行 · 第 1/2 页

<td><a href="http://www.itlibrary.com/reference/dir.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>

<b><font size="+1">11.1	How do I匢ssue single record queries with implicit cursors?</font></b>

<B>Problem</B>

<p>

I want to select one record into a variable within my PL/SQL routine. Also, I don抰 want to explicitly step through the OPEN, FETCH, and CLOSE steps that must be specified with an explicit cursor. How do I issue single record queries with implicit cursors?

<P>

<B>Technique</B>

<p>

If a PL/SQL routine uses a SELECT statement but does not explicitly define a cursor, that SELECT statement is considered an implicit cursor. For more information on explicit cursors, see How-To 11.2. After the implicit cursor has run, you can find additional information about the results of the query. The cursor attributes are SQL%ISOPEN, SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT. They are fully
covered in How-To 11.3.

<p>

With an implicit cursor, the query must return exactly one row. The values of the cursor attributes always refer to the most recently executed SQL statement. If no rows are returned, or more than one row is returned, then an error is returned, and control of the flow moves to the exception portion of the PL/SQL routine.

<p>

<B>Steps</B>

<p>

<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. CHP11_1.SQL, shown in <A HREF="image/11oht01.gif" TARGET="_blank">Figure 11.1</A>, creates the sample table used in this How-To.

<p>

<B>2.</B>	The DEPT11 sample table contains two columns, DEPT_NO and DEPT_NAME, and is populated with three records. Run the file to create the sample table with its data.

<p>

<code><b>
SQL> START CHP11_1

<p>

Table created.
<p>

1 row inserted.

<p>

1 row inserted.

<p>

1 row inserted.

<p>

1 row inserted.

<p>

SQL>
</b></code>

<p>

CHP11_2.SQL, shown in <A HREF="image/11oht02.gif" TARGET="_blank">Figure 11.2</A>, has a PL/SQL routine that queries the DEPT11 table for a single record.

<p>

<B>3.</B>	Run CHP11_2.SQL to execute the PL/SQL routine that uses an implicit cursor.

<p>

<code><b>SQL> START CHP11_2.sql

<p>

The Department Name is Marketing

<p>

PL/SQL procedure successfully completed.</b></code>

<p>

<B>How It Works</B>

<p>

Steps 1 and 2 create the DEPT11 table used throughout this chapter and populate it with sample data. Step 3 runs CHP11_2.SQL, which executes a PL/SQL routine that uses an implicit cursor. Line 1 has the SET ECHO ON SERVEROUTPUT ON statement, which enables Oracle to pass text back to the SQL*Plus screen. Without setting this, the user will not have messages from the PL/SQL routine displayed. Lines 2 and 3 declare a variable, DEPARTMENT_NAME, for the PL/SQL routine that will be assigned the value for the department name. Line 4 contains the BEGIN statement, which is necessary for the PL/SQL construct. Lines 5-7 contain the cursor. The cursor is simply a SELECT statement with an additional INTO clause, which assigns the DEPT_NAME to the DEPARTMENT_NAME variable. Line 8 gives a message back to the user account, using the DBMS_OUTPUT.PUT_LINE procedure. Line 9 contains the END statement, also necessary for the PL/SQL construct.

<p>

<B>Comments</B>

<p>
Implicit cursors work well for queries that return exactly one row. In our example, if one additional record is added to the DEPT11 table, with DEPT_NO=1, then the cursor has returned more than one row, which causes a TOO_MANY_ROWS error. To use implicit cursors, you must be sure that at no point in the future will the query return more than one record. Explicit cursors allow for multiple records to be returned and add greater flexibility to PL/SQL routines (for information on using explicit cursors, see How-To 11.2). INSERT and UPDATE statements automatically use implicit cursors.

<p>


<!-- </td>

</td>
</tr>
</table> -->
</td>

</td>
</tr>
</table>

<!-- begin footer information -->

<MAP NAME="footer">
<AREA SHAPE=RECT COORDS="0,0,62,26" HREF="http://www.itlibrary.com/">
<AREA SHAPE=RECT COORDS="62,0,135,26" HREF="http://www.developer.com/about/">
<AREA SHAPE=RECT COORDS="135,0,199,26" HREF="http://www.itlibrary.com/search/">
<AREA SHAPE=RECT COORDS="200,0,274,26" HREF="http://www.itlibrary.com/subscribe/">
<AREA SHAPE=RECT COORDS="275,0,335,25" HREF="http://www.developer.com/contact/adinfo.html">
<AREA SHAPE=RECT COORDS="335,0,417,25" HREF="http://www.developer.com/contact/">
<AREA SHAPE=RECT COORDS="418,0,467,26" HREF="http://www.developer.com/about/faq.html">
</MAP>

<P>
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr>
<TD WIDTH="130"></TD>
<td width="468">
<IMG SRC="../../../images/footer/footerfile.gif" ALT="footer nav" width="467" height="26" BORDER="0" usemap="#footer" ismap>
</td>
</tr>
<tr>
<TD WIDTH="130"></TD>
<td width="468"> 
<font face=arial,helvetica size="1"> Use of this site is subject certain <a href="http://www.developer.com/legal/">Terms &amp; Conditions.</a><br>
Copyright (c) 1996-1999 <A HREF="http://www.earthweb.com/">EarthWeb, Inc.</A>.  All rights reserved.  Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
<a href="http://www.earthweb.com/corporate/privacy.html">Please read our privacy policy for details.</a>
</td>
</tr>
</table>

</BODY>
</HTML>