chap10_8.html

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

<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"><B>10.9 How do I匒chieve array functionality with PL/SQL Index-By tables?</B></font></b>

<p>

<B>Problem</B>

<p>

I need to declare variables in my PL/SQL code to represent columns retrieved from the database. Errors can occur if I don抰 create the variable with the correct datatype and column width. If the column changes in the table, my code needs to be modified. I want to create variables to represent columns. I also want to create structures to represent records in my PL/SQL code. How do I create variables to represent records and columns?

<p>

<B>Technique</B>

<p>

In PL/SQL, it is possible to declare the datatype of a variable as the datatype of a table column using the %TYPE attribute. The %TYPE attribute ensures that the datatype of the variable is suitable for handling the column. The usage of the %TYPE attribute is shown here:<p>
variable_name table_name.column_name%TYPE;

<p>

A separate variable is declared for each table column, using the %TYPE attribute. A single variable can be created to represent the entire row, by using the %ROWTYPE attribute. Variables declared with the %ROWTYPE attribute are structures containing all the columns in the table. The usage of the %ROWTYPE attribute is shown here:

<p>

<code><b>variable_name table_name%ROWTYPE;</b></code>

<p>
When a variable is declared using the %ROWTYPE attribute, its components are referenced using the syntax variable_name.column_name. When using cursors, the %ROWTYPE attribute can be used to declare a variable representing the columns returned by a cursor as:

<p>

<code><b>variable_name cursor_name%ROWTYPE;</b></code>

<p>

This is particularly useful when you are not dealing with all the columns of a table but only those returned by the cursor. Obviously, the cursor declaration has to appear before using the %ROWTYPE attribute with the cursor_name. Refer to Chapter 11 for more information on the use of cursors. You can also define a PL/SQL record type as

<p>

<code><b>TYPE record_type IS

<p>

RECORD

<p>

(field_name1 datatype1 [NOT NULL] [DEFAULT default_value1],

<p>

field_name2 datatype2 [NOT NULL] [DEFAULT default_value2],

<p>

...</b></code>

<p>

field_namen datatypen [NOT NULL] [DEFAULT default_valuen]);<p>
where record_type is the name of the datatype representing the record. The field_name is the name of the field within the record, and data_type represents the datatype of the field. A NOT NULL constraint and a DEFAULT initial value for each field in the record can be optionally specified. A DEFAULT value must be provided for any NOT NULL fields in the record.
<p>


<B>Steps</B>

<p>


<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. CHP10_28.SQL, as shown in <A HREF="image/10oht28.gif" TARGET="_blank">Figure 10.28</A>, creates the sample table used in this How-To. Run the file to create the sample table and data.

<p>

The DEPT10 table created by the script contains two columns used to define column and record type variables in the following steps.

<p>

<B>2.</B>	Run the CHP10_29.SQL file in SQL*Plus. The PL/SQL block contained in the file uses the %TYPE attribute when declaring variables to represent database columns. The code and results are shown in <A HREF="image/10oht29.gif" TARGET="_blank">Figure 10.29</A>.

<p>

Lines 1 through 3 contain the declarative section of the block. Lines 2 and 3 declare variables using the %TYPE attribute. The D_NO and D_NAME variables are declared with the datatype of the DEPT_NO and DEPT_NAME columns in the sample table, even though the datatypes are not specified. Lines 4 through 9 contain the execution section of the block. The query contained in lines 5 through 7 selects a record from the sample table and puts the results in the variables declared with the %TYPE attribute. Even though the datatype is not known to the programmer when the module is created, the %TYPE attribute ensures its correctness. The DBMS_OUTPUT package is used in lines 8 and 9 to display the values returned by the query. The exception section contained in lines 10 through 12 handles the exception raised if no data is returned by the query.

<p>

<B>3.</B>	Run the CHP10_30.SQL file in SQL*Plus. The PL/SQL block contained in the file declares a record variable using the %ROWTYPE attribute (see <A HREF="image/10oht30.gif" TARGET="_blank">Figure 10.30</A>).

<p>

Lines 1 and 2 contain the declarative section of the block. Line 2 uses the %ROWTYPE attribute to declare a record variable representing all the columns in the DEPT10 table. Lines 3 through 8 contain the executable section of the block. The query defined in lines 4 through 6 retrieves a record from the sample table in Step 1 and places the columns in the components of the record variable. Line 5 shows how the columns of a record-type variable are referenced using dot notation. Lines 7 and 8 use the DBMS_OUTPUT package to display the values contained in the components of the record variable. The exception section contained in lines 9 through 11 handles the exception if no data is returned by the query.

<p>

<B>4.</B>	Run the CHP10_31.SQL file in SQL*Plus. The PL/SQL block contained in the file declares a record variable of a user-defined RECORD type (see <A HREF="image/10oht31.gif" TARGET="_blank">Figure 10.31</A>).

<p>

Lines 2 through 5 contain the declarative section of the block. A user-defined composite type is defined in lines 2 through 4 using the TYPE IS RECORD syntax. Line 5 declares a record variable of the newly defined record type. Lines 6 through 11 contain the executable section of the block. The query defined in lines 7 through 9 retrieves a record from the sample table created in Step 1 and places the columns in the fields of the record variable. Line 8 shows how the columns of a record-type variable are referenced using dot notation. Lines 10 and 11 use the DBMS_OUTPUT package to display the values contained in the components of the record variable. The exception section contained in lines 12 through 14 handles the exception if the query fails to return a row from the table.

<p>

<B>How It Works</B>

<p>

The %TYPE attribute can be used to declare variables of the datatype contained in the column of a table. This enables you to declare variables without knowing the datatype of the value it will receive. The %ROWTYPE attribute enables you to declare a composite datatype containing all the columns in a table. Step 1 creates a sample table used in the following steps. Step 2 demonstrates the use of the %TYPE attribute by declaring variables to represent the columns in the sample table. Step 3 uses the %ROWTYPE attribute to declare a composite variable containing all the columns in the sample table. Step 4 uses the TYPE IS RECORD syntax to define a record type and then declare a record variable of that record type. A PL/SQL record type can contain another table or a record with composite fields.

<p>

<B>Comments</B>

<p>

It is good practice to use the %TYPE and %ROWTYPE attributes whenever you create a variable to represent a table column or create multiple variables to represent an entire record. Using the %TYPE and %ROWTYPE attributes ensures that your PL/SQL code will continue to work if the structure of the table or datatypes of a column change. A record type variable provides a means to group logically related variables to be treated as a single unit. Records can be passed as parameters to subprograms.

<p>

</blockquote>

<!-- </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>