declare man page on UnixWare

Man page or keyword search:  
man Server   3616 pages
apropos Keyword Search (all sections)
Output format
UnixWare logo
[printable version]

DECLARE()			 SQL Commands			     DECLARE()

       DECLARE - define a cursor

       DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
	   CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
	   [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

       DECLARE	allows a user to create cursors, which can be used to retrieve
       a small number of rows at a time out of a  larger  query.  Cursors  can
       return data either in text or in binary format using FETCH [fetch(5)].

       Normal  cursors	return data in text format, the same as a SELECT would
       produce. Since data is stored natively in  binary  format,  the	system
       must  do	 a conversion to produce the text format. Once the information
       comes back in text form, the client application may need to convert  it
       to a binary format to manipulate it. In addition, data in the text for‐
       mat is often larger in size than in the binary format.  Binary  cursors
       return  the  data  in  a	 binary representation that may be more easily
       manipulated.  Nevertheless, if you intend to display the data  as  text
       anyway,	retrieving  it	in  text form will save you some effort on the
       client side.

       As an example, if a query returns a value of one from an	 integer  col‐
       umn,  you  would get a string of 1 with a default cursor whereas with a
       binary cursor you would get a 4-byte field containing the internal rep‐
       resentation of the value (in big-endian byte order).

       Binary  cursors	should be used carefully. Many applications, including
       psql, are not prepared to handle binary cursors and expect data to come
       back in the text format.

	      Note:  When  the	client application uses the ``extended query''
	      protocol to issue a FETCH command,  the  Bind  protocol  message
	      specifies whether data is to be retrieved in text or binary for‐
	      mat.  This choice overrides the way that the cursor is  defined.
	      The  concept  of	a  binary cursor as such is thus obsolete when
	      using extended query protocol — any cursor  can  be  treated  as
	      either text or binary.

       name   The name of the cursor to be created.

       BINARY Causes  the  cursor to return data in binary rather than in text

	      Indicates that data retrieved from the cursor  should  be	 unaf‐
	      fected  by updates to the tables underlying the cursor while the
	      cursor exists. In PostgreSQL, all cursors are insensitive;  this
	      key  word currently has no effect and is present for compatibil‐
	      ity with the SQL standard.


       NO SCROLL
	      SCROLL specifies that the cursor may be used to retrieve rows in
	      a	 nonsequential	fashion	 (e.g.,	 backward). Depending upon the
	      complexity of the query's execution plan, specifying SCROLL  may
	      impose  a performance penalty on the query's execution time.  NO
	      SCROLL specifies that the cursor cannot be used to retrieve rows
	      in a nonsequential fashion. The default is to allow scrolling in
	      some cases; this is not the same as specifying SCROLL. See Notes
	      [declare(5)] for details.

       WITH HOLD

	      WITH  HOLD  specifies  that  the	cursor may continue to be used
	      after the transaction  that  created  it	successfully  commits.
	      WITHOUT HOLD specifies that the cursor cannot be used outside of
	      the transaction that created it. If  neither  WITHOUT  HOLD  nor
	      WITH HOLD is specified, WITHOUT HOLD is the default.

       query  A	 SELECT	 [select(5)]  or VALUES [values(5)] command which will
	      provide the rows to be returned by the cursor.


	      FOR READ ONLY indicates that the cursor will be used in a	 read-
	      only  mode. FOR UPDATE indicates that the cursor will be used to
	      update tables. Since cursor updates are not currently  supported
	      in PostgreSQL, specifying FOR UPDATE will cause an error message
	      and specifying FOR READ ONLY has no effect.

       column Column(s) to be updated by the cursor. Since cursor updates  are
	      not  currently  supported	 in  PostgreSQL, the FOR UPDATE clause
	      provokes an error message.

       The key words BINARY, INSENSITIVE, and SCROLL may appear in any order.

       Unless WITH HOLD is specified, the cursor created by this  command  can
       only be used within the current transaction. Thus, DECLARE without WITH
       HOLD is useless outside a transaction block: the cursor	would  survive
       only  to	 the completion of the statement. Therefore PostgreSQL reports
       an error if this command is used	 outside  a  transaction  block.   Use
       BEGIN  [begin(5)],  COMMIT  [commit(5)]	and  ROLLBACK [rollback(5)] to
       define a transaction block.

       If WITH HOLD is specified and the transaction that created  the	cursor
       successfully  commits, the cursor can continue to be accessed by subse‐
       quent transactions in the same session. (But if the  creating  transac‐
       tion  is	 aborted,  the	cursor is removed.) A cursor created with WITH
       HOLD is closed when an explicit CLOSE command is issued on it,  or  the
       session	ends. In the current implementation, the rows represented by a
       held cursor are copied into a temporary file or	memory	area  so  that
       they remain available for subsequent transactions.

       The  SCROLL option should be specified when defining a cursor that will
       be used to fetch backwards. This is required by the SQL standard.  How‐
       ever,  for  compatibility  with earlier versions, PostgreSQL will allow
       backward fetches without SCROLL, if the cursor's query plan  is	simple
       enough  that no extra overhead is needed to support it. However, appli‐
       cation developers are advised not to rely  on  using  backward  fetches
       from  a	cursor	that has not been created with SCROLL. If NO SCROLL is
       specified, then backward fetches are disallowed in any case.

       The SQL standard only makes provisions for cursors in embedded SQL. The
       PostgreSQL  server  does not implement an OPEN statement for cursors; a
       cursor is considered to be open when it is  declared.   However,	 ECPG,
       the embedded SQL preprocessor for PostgreSQL, supports the standard SQL
       cursor conventions, including those involving DECLARE and  OPEN	state‐

       You  can	 see  all  available cursors by querying the pg_cursors system

       To declare a cursor:

       DECLARE liahona CURSOR FOR SELECT * FROM films;

       See FETCH [fetch(5)] for more examples of cursor usage.

       The SQL standard allows cursors only in embedded SQL  and  in  modules.
       PostgreSQL permits cursors to be used interactively.

       The  SQL	 standard  allows cursors to update table data. All PostgreSQL
       cursors are read only.

       Binary cursors are a PostgreSQL extension.

       CLOSE [close(5)], FETCH [fetch(l)], MOVE [move(l)]

SQL - Language Statements	  2008-01-03			     DECLARE()
                             _         _         _ 
                            | |       | |       | |     
                            | |       | |       | |     
                         __ | | __ __ | | __ __ | | __  
                         \ \| |/ / \ \| |/ / \ \| |/ /  
                          \ \ / /   \ \ / /   \ \ / /   
                           \   /     \   /     \   /    
                            \_/       \_/       \_/ 
More information is available in HTML format for server UnixWare

List of man pages available for UnixWare

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
Vote for polarhome
Free Shell Accounts :: the biggest list on the net