1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
|
<REFENTRY ID="SQL-DECLARESTATEMENT-1">
<REFMETA>
<REFENTRYTITLE>
DECLARE STATEMENT
</REFENTRYTITLE>
<REFMISCINFO>SQL - Language Statements</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>
DECLARE STATEMENT
</REFNAME>
<REFPURPOSE>
Declares a cursor
</REFPURPOSE>
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1998-04-15</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
DECLARE <replaceable class="parameter">cursor</replaceable> [ BINARY ]
FOR SELECT <replaceable class="parameter">query</replaceable>
</SYNOPSIS>
<REFSECT2 ID="R2-SQL-DECLARESTATEMENT-1">
<REFSECT2INFO>
<DATE>1998-04-15</DATE>
</REFSECT2INFO>
<TITLE>
Inputs
</TITLE>
<PARA>
</PARA>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
</TERM>
<LISTITEM>
<PARA>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
<ReturnValue>BINARY</ReturnValue>
</TERM>
<LISTITEM>
<PARA>
The BINARY keyword causes the cursor to fetch data in binary
rather than in ASCII format.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
<ReturnValue><replaceable class="parameter">cursor</replaceable></ReturnValue>
</TERM>
<LISTITEM>
<PARA>
The cursor's name.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
<ReturnValue><replaceable class="parameter">query</replaceable></ReturnValue>
</TERM>
<LISTITEM>
<PARA>
An SQL query which will provide the rows to be governed by the
cursor.
</PARA>
<PARA>
Refer to the SELECT statement for further information about
valid arguments.
</PARA>
</LISTITEM>
</VARLISTENTRY>
</variablelist>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>
<REFSECT2 ID="R2-SQL-DECLARESTATEMENT-2">
<REFSECT2INFO>
<DATE>1998-04-15</DATE>
</REFSECT2INFO>
<TITLE>
Outputs
</TITLE>
<PARA>
</PARA>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
</TERM>
<LISTITEM>
<PARA>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
<ReturnValue>SELECT</ReturnValue>
</TERM>
<LISTITEM>
<PARA>
The message returned if the SELECT is run successfully.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
<ReturnValue>NOTICE
BlankPortalAssignName: portal "<replaceable class="parameter">cursor</replaceable>" already exists</ReturnValue>
</TERM>
<LISTITEM>
<PARA>
This error occurs if cursor "<replaceable class="parameter">cursor</replaceable>" is already declared.
</PARA>
</LISTITEM>
</VARLISTENTRY>
</variablelist>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>
</REFSYNOPSISDIV>
<REFSECT1 ID="R1-SQL-DECLARESTATEMENT-1">
<REFSECT1INFO>
<DATE>1998-04-15</DATE>
</REFSECT1INFO>
<TITLE>
Description
</TITLE>
<PARA>
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 ASCII or in binary foramt.
</PARA>
<PARA>
Normal cursors return data in ASCII format. Since
data is stored natively in binary format, the system must
do a conversion to produce the ASCII format. In addition,
ASCII formats are often larger in size than binary format.
Once the information comes back in ASCII, the client
application often has to convert it to a binary format to
manipulate it anyway.
</PARA>
<PARA>
BINARY cursors give you back the data in the native binary
representation. So binary cursors will tend to be a
little faster since they suffer less conversion overhead.
For example, for an integer column, you get a C integer number like ^A
using a binary cursor, while you get a string value like '1'
using the non binary cursor.
</PARA>
<PARA>
However, ASCII is architecture-neutral whereas binary
representation can differ between different machine architectures.
Therefore, if your client machine and server machine use different
representations, you will probably not want your data returned in
binary format.
Again, if you intend to display the data in
ASCII, getting it back in ASCII will save you some
effort on the client side.
</PARA>
<REFSECT2 ID="R2-SQL-DECLARESTATEMENT-3">
<REFSECT2INFO>
<DATE>1998-04-15</DATE>
</REFSECT2INFO>
<TITLE>
Notes
</TITLE>
<PARA>
Cursors are only available in transactions.
</PARA>
<PARA>
PostgreSQL does not have an explicit <command>OPEN cursor</command>
statement; a cursor is considered to be open when it is DECLAREd.
</PARA>
</REFSECT2>
</refsect1>
<REFSECT1 ID="R1-SQL-DECLARESTATEMENT-2">
<TITLE>
Usage
</TITLE>
<PARA>
To declare a cursor:
</PARA>
<ProgramListing>
DECLARE liahona CURSOR
FOR SELECT * FROM films;
</ProgramListing>
</REFSECT1>
<REFSECT1 ID="R1-SQL-DECLARESTATEMENT-3">
<TITLE>
Compatibility
</TITLE>
<PARA>
</PARA>
<REFSECT2 ID="R2-SQL-DECLARESTATEMENT-4">
<REFSECT2INFO>
<DATE>1998-04-15</DATE>
</REFSECT2INFO>
<TITLE>
SQL92
</TITLE>
<PARA>
SQL92 specifies some additional capabilities for the DECLARE statement:
</PARA>
<synopsis>
DECLARE cursor [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR SELECT expression
[ ORDER BY column [, ... ] [ ASC | DESC ]
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
</synopsis>
<variablelist>
<varlistentry>
<term></term>
<listitem>
<variablelist>
<varlistentry>
<term>INSENSITIVE</term>
<listitem>
<para>
UPDATE and DELETE CURRENT operations are not allowed
if the cursor is declared to be INSENSITIVE.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>SCROLL</term>
<listitem>
<para>
If SCROLL is not specified, only FETCH NEXT will be allowed.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>FOR READ ONLY/UPDATE</term>
<listitem>
<para>
If READ ONLY is specified, UPDATE/DELETE CURRENT operations
will not be allowed.
</para>
</listitem>
</varlistentry>
</variablelist>
</listitem>
</varlistentry>
</variablelist>
</REFENTRY>
<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:t
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:
-->
|
