fetch.sgml

关系型数据库 Postgresql 6.5.2

<refentry id="SQL-FETCH">
 <refmeta>
  <refentrytitle>
   FETCH
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   FETCH
  </refname>
  <refpurpose>
   Gets rows using a cursor
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1998-09-01</date>
  </refsynopsisdivinfo>
  <synopsis>
FETCH [ <replaceable class="PARAMETER">selector</replaceable> ] [ <replaceable class="PARAMETER">count</replaceable> ] { IN | FROM } <replaceable class="PARAMETER">cursor</replaceable>
FETCH [ RELATIVE ] [ { [ <replaceable class="PARAMETER">#</replaceable> | ALL | NEXT | PRIOR ] } ] FROM ] <replaceable class="PARAMETER">cursor</replaceable>
  </synopsis>

  <refsect2 id="R2-SQL-FETCH-1">
   <refsect2info>
    <date>1998-09-01</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><replaceable class="PARAMETER">selector</replaceable></term>
      <listitem>
       <para>
	<replaceable class="PARAMETER">selector</replaceable>
	defines the fetch direction. It can be one
	the following:

	<variablelist>
	 <varlistentry>
	  <term>FORWARD</term>
	  <listitem>
	   <para>
	    fetch next row(s). This is the default
	    if <replaceable class="PARAMETER">selector</replaceable> is omitted.
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>BACKWARD</term>
	  <listitem>
	   <para>
	    fetch previous row(s).
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>RELATIVE</term>
	  <listitem>
	   <para>
	    Noise word for SQL92 compatibility.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">count</replaceable></term>
      <listitem>
       <para>
	<replaceable class="PARAMETER">count</replaceable>
	determines how many rows to fetch. It can be one of the following:

	<variablelist>
	 <varlistentry>
	  <term><replaceable class="PARAMETER">#</replaceable></term>
	  <listitem>
	   <para>
	    A signed integer that specify how many rows to fetch.
	    Note that a negative integer is equivalent to changing the sense of
	    FORWARD and BACKWARD.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>
	   ALL
	  </term>
	  <listitem>
	   <para>
	    Retrieve all remaining rows.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>
	   NEXT
	  </term>
	  <listitem>
	   <para>
	    Equivalent to specifying a count of <command>1</command>.
	   </para>
	  </listitem>
	 </varlistentry>

	 <varlistentry>
	  <term>
	   PRIOR
	  </term>
	  <listitem>
	   <para>
	    Equivalent to specifying a count of <command>-1</command>.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">cursor</replaceable></term>
      <listitem>
       <para>
	An open cursor's name.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

  <refsect2 id="R2-SQL-FETCH-2">
   <refsect2info>
    <date>1998-04-15</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   <para>
    <command>FETCH</command> returns the results of the query defined by the specified cursor.
    The following messages will be returned if the query fails:

    <variablelist>
     <varlistentry>
      <term><computeroutput>
NOTICE:  PerformPortalFetch: portal "<replaceable class="PARAMETER">cursor</replaceable>" not found
       </computeroutput></term>
      <listitem>
       <para>
	If <replaceable class="PARAMETER">cursor</replaceable>
	is not previously declared.
	The cursor must be declared within a transaction block.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><computeroutput>
NOTICE:  FETCH/ABSOLUTE not supported, using RELATIVE
       </computeroutput></term>
      <listitem>
       <para>
	<productname>Postgres</productname> does not support absolute
	positioning of cursors.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><computeroutput>
ERROR:  FETCH/RELATIVE at current position is not supported
       </computeroutput></term>
      <listitem>
       <para>
	<acronym>SQL92</acronym> allows one to repetatively retrieve the cursor
	at its "current position" using the syntax
	<synopsis>
FETCH RELATIVE 0 FROM <replaceable class="PARAMETER">cursor</replaceable>
	</synopsis>
       </para>

       <para>
	<productname>Postgres</productname> does not currently support
	this notion; in fact the value zero is reserved to indicate that
	all rows should be retrieved and is equivalent to specifying the ALL keyword.
	If the RELATIVE keyword has been used, the <productname>Postgres</productname> 
	assumes that the user intended <acronym>SQL92</acronym> behavior
	and returns this error message.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-FETCH-1">
  <refsect1info>
   <date>1998-04-15</date>
  </refsect1info>
  <title>
   Description
  </title>
  <para>
   FETCH allows a user to retrieve rows using a cursor.
   The number of rows retrieved is specified by
   <replaceable class="PARAMETER">#</replaceable>.
   If the number of rows remaining in the cursor is less
   than <replaceable class="PARAMETER">#</replaceable>,
   then only those available are fetched.
   Substituting the keyword ALL in place of a number will
   cause all remaining rows in the cursor to be retrieved.
   Instances may be fetched in both FORWARD and BACKWARD
   directions. The default direction is FORWARD.

   <tip>
    <para>
     Negative numbers are now allowed to be specified for the
     row count. A negative number is equivalent to reversing
     the sense of the FORWARD and BACKWARD keywords. For example,
     <command>FORWARD -1</command> is the same as <command>BACKWARD 1</command>.
    </para>
   </tip>
  </para>
  <para>
   Note that the FORWARD and BACKWARD keywords are
   <productname>Postgres</productname> extensions.
   The <acronym>SQL92</acronym> syntax is also supported, specified
   in the second form of the command. See below for details
   on compatibility issues.
  </para>
  <para>
   Once all rows are fetched, every other fetch access returns
   no rows.
  </para>

  <para>
   Updating data in a cursor is not supported by 
   <productname>Postgres</productname>,
   because mapping cursor updates back to base tables is
   not generally possible, as is also the case with VIEW updates.
   Consequently,
   users must issue explicit UPDATE commands to replace data.
</para>

  <para>
   Cursors may only be used inside of transactions because
   the data that they store spans multiple user queries.
  </para>

  <refsect2 id="R2-SQL-FETCH-3">
   <refsect2info>
    <date>1998-04-15</date>
   </refsect2info>
   <title>
    Notes
   </title>
   <para>
    Use <command>MOVE</command> to change cursor position.
    <command>DECLARE</command> will define a cursor.
    Refer to <command>BEGIN</command>, <command>COMMIT</command>, and
    <command>ROLLBACK</command>
    for further information about transactions.
   </para>
  </refsect2>
 </refsect1>

 <refsect1 id="R1-SQL-FETCH-2">
  <title>
   Usage
  </title>
  <para>
   <programlisting>
   --set up and use a cursor:
   --
   BEGIN WORK;
     DECLARE liahona CURSOR
        FOR SELECT * FROM films;

   --Fetch first 5 rows in the cursor liahona:
   --
     FETCH FORWARD 5 IN liahona;

     code |title                  |did| date_prod|kind      |len
     -----+-----------------------+---+----------+----------+------
     BL101|The Third Man          |101|1949-12-23|Drama     | 01:44
     BL102|The African Queen      |101|1951-08-11|Romantic  | 01:43
     JL201|Une Femme est une Femme|102|1961-03-12|Romantic  | 01:25
     P_301|Vertigo                |103|1958-11-14|Action    | 02:08
     P_302|Becket                 |103|1964-02-03|Drama     | 02:28
 

   --Fetch previous row:
   --
     FETCH BACKWARD 1 IN liahona;

     code |title                  |did| date_prod|kind      |len
     -----+-----------------------+---+----------+----------+------
     P_301|Vertigo                |103|1958-11-14|Action    | 02:08

   -- close the cursor and commit work:
   --
     CLOSE liahona;
   COMMIT WORK;
   </programlisting>
  </para>        
 </refsect1>

 <refsect1 id="R1-SQL-FETCH-3">
  <title>
   Compatibility
  </title>
  <para>
   The non-embedded use of cursors is a <productname>Postgres</productname>
   extension. The syntax and usage of cursors is being compared
   against the embedded form of cursors defined in <acronym>SQL92</acronym>.
  </para>

  <refsect2 id="R2-SQL-FETCH-4">
   <refsect2info>
    <date>1998-09-01</date>
   </refsect2info>
   <title>
    SQL92
   </title>
   <para>
    <acronym>SQL92</acronym> allows absolute positioning of the cursor for
    FETCH, and allows placing the results into explicit variables.

    <synopsis>
FETCH ABSOLUTE <replaceable class="PARAMETER">#</replaceable>
    FROM <replaceable class="PARAMETER">cursor</replaceable>
    INTO :<replaceable class="PARAMETER">variable</replaceable> [, ...]
    </synopsis>

    <variablelist>
     <varlistentry>
      <term>ABSOLUTE</term>
      <listitem>
       <para>
	The cursor should be positioned to the specified absolute
	row number. All row numbers in <productname>Postgres</productname>
	are relative numbers so this capability is not supported.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term>:<replaceable class="PARAMETER">variable</replaceable></term>
      <listitem>
       <para>
	Target host variable(s).
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->