-
Notifications
You must be signed in to change notification settings - Fork 18
/
sqlexecution.html
397 lines (314 loc) · 13.6 KB
/
sqlexecution.html
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
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
<!-- Creator : groff version 1.22.3 -->
<!-- CreationDate: Mon Mar 18 08:51:06 2024 -->
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta name="generator" content="groff -Thtml, see www.gnu.org">
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<meta name="Content-Style" content="text/css">
<style type="text/css">
p { margin-top: 0; margin-bottom: 0; vertical-align: top }
pre { margin-top: 0; margin-bottom: 0; vertical-align: top }
table { margin-top: 0; margin-bottom: 0; vertical-align: top }
h1 { text-align: center }
</style>
<title>sqlexecution</title>
</head>
<body>
<h1 align="center">sqlexecution</h1>
<a href="#NAME">NAME</a><br>
<a href="#SYNTAX">SYNTAX</a><br>
<a href="#DESCRIPTION">DESCRIPTION</a><br>
<a href="#USAGE">USAGE</a><br>
<a href="#AMPERSAND REPLACEMENT">AMPERSAND REPLACEMENT</a><br>
<a href="#EXAMPLES">EXAMPLES</a><br>
<a href="#NOTES">NOTES</a><br>
<a href="#COPYRIGHT">COPYRIGHT</a><br>
<a href="#SEE ALSO">SEE ALSO</a><br>
<hr>
<h2>NAME
<a name="NAME"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">sqlexecution
− RWP*Load Simulator execute sql, PL/SQL or cursor
loops</p>
<h2>SYNTAX
<a name="SYNTAX"></a>
</h2>
<pre style="margin-left:11%; margin-top: 1em">terminatedstatement ::=
embeddedsqltext
| embeddedsqltext atclause<b> ;
</b>| identifier [ atclause ]<b> ;
</b>| immediatesql [ atclause ]<b> ;
</b>cursorloop ::=
<b> for</b> { embeddedsqltext | immediatesql | identifier }
[ atclause ]
[<b> and</b> expression ]
<b> loop
</b> statementlist
<b> end</b> [<b> loop</b> ]
embeddedsqltext ::=
SQLtext<b> ;</b> newline
| SQLtext newline whitespace (<b> /</b> |<b> .</b> ) newline
| PLSQLblock newline whitespace (<b> /</b> |<b> .</b> ) newline</pre>
<h2>DESCRIPTION
<a name="DESCRIPTION"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">In rwloadsim,
you can execute SQL statement in three different ways:</p>
<table width="100%" border="0" rules="none" frame="void"
cellspacing="0" cellpadding="0">
<tr valign="top" align="left">
<td width="11%"></td>
<td width="3%">
<p>1.</p></td>
<td width="3%"></td>
<td width="83%">
<p>You can simply embed your SQL or PL/SQL directly in your
code via <b>embeddedsqltext</b>. This approach is useful in
many cases, but it does have some limitations.</p></td></tr>
<tr valign="top" align="left">
<td width="11%"></td>
<td width="3%">
<p>2.</p></td>
<td width="3%"></td>
<td width="83%">
<p>You can execute immediate SQL or PL/SQL, which relaxes
some of the limitations.</p></td></tr>
<tr valign="top" align="left">
<td width="11%"></td>
<td width="3%">
<p>3.</p></td>
<td width="3%"></td>
<td width="83%">
<p>You can separate the declaration and execution into two
rwloadsim statements, which gives you full control. This was
the only method available in previous versions.</p></td></tr>
</table>
<p style="margin-left:11%; margin-top: 1em">For details,
see <a href="sqldeclaration.html">sqldeclaration(1rwl)</a>.</p>
<h2>USAGE
<a name="USAGE"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em"><b>embeddedsqltext
<br>
for embeddedsqltext loop</b> s;s;s; <b>end</b></p>
<p style="margin-left:17%; margin-top: 1em">The most simple
way to execute sql is to embed the sql text directly in your
program, which can be done if only implicit bind and define
is needed, and if any dynamic parts of your sql can be
handled using simple ampersand replacement as described
below. Hence, <i>all</i> bind variables must exist as
variables of the same name, and bindout will be done only if
the <b>$bindoutname:on</b> directive is in effect. For
queries <i>all</i> select list elements must have simple
names or have an alias as a simple name, and these must
exist as variables.</p>
<p style="margin-left:17%; margin-top: 1em">The names for
both placeholders and select list elements are by default
converted to lowercase before matching to variables declared
in your rwloadsim program. If the $implicit:keepcase
directive is in effect, this conversion to lowercase does
not take place.</p>
<p style="margin-left:17%; margin-top: 1em">If an at clause
is provided, the execution will be against the specified
database.</p>
<p style="margin-left:17%; margin-top: 1em">It is highly
recommended that if the sql statement is a query, it is
expected to return (at most) one row. For queries returning
an unknown number of rows, use the cursor loop syntax, which
can include an at clause and/or an and clause to terminate
the fetch early if needed.</p>
<p style="margin-left:17%; margin-top: 1em">You can provide
array size for query respectively dml using the directive
<b>$embeddedqueryarray</b> or <b>$embeddeddmlarray</b>. By
default, queries use OCI prefetch based on memory, and DML
does not use array.</p>
<p style="margin-left:17%; margin-top: 1em">You can make
directly embedded sql dynamic using ampersand replacement as
described below.</p>
<p style="margin-left:11%; margin-top: 1em"><b>identifier</b></p>
<p style="margin-left:17%;">An identifier that is not
followed by an open parenthesis must be a SQL statement
which is executed once. If an at clause is provided, the
execution will be against the specified database. It is
highly recommended that if the sql statement is a query, it
is expected to return (at most) one row.</p>
<p style="margin-left:17%; margin-top: 1em">If the SQL
statement is a dml that has an array size set, rwloadsim
will buffer executions until the array is filled or a commit
or rollback is executed. If needed, the array can be flushed
using <b>modify sql array execute</b>.</p>
<p style="margin-left:11%; margin-top: 1em"><b>for</b> c
<b>at</b> d <b>and</b> e <b>loop</b> s;s;s; <b>end</b></p>
<p style="margin-left:17%;">The identifier c must refer to
a SQL statement that is a query, and the statement list
between <b>loop</b> and <b>end</b> will be executed once for
each row fetched from the query. If no rows are returned, no
execution will take place. The optional at clause can be
used to choose a particular database, d, otherwise the
currently chosen one will be used.</p>
<p style="margin-left:17%; margin-top: 1em">If array fetch
is active (which is the case by default), rwloadsim will use
the Oracle Call Interface array fetch capabilities; if
<b>define array</b> is used, rwloadsim will create a client
side array. In either case, actual fetch of rows from the
database will take place initially and subsequently only
when the array has been exhausted in the loop.</p>
<p style="margin-left:17%; margin-top: 1em">The optional
<b>and</b> followed by an expression e before the
<b>loop</b> keyword will cause the cursor to be canceled
when the expression is zero. Note that due to array
fetching, some rows may have been fetched from the database
but not yet processed when the condition after <b>and</b>
becomes false. The cursor will also be canceled if you
perform a <b>break</b> or a <b>return</b> from inside the
loop.</p>
<p style="margin-left:11%; margin-top: 1em"><b>sql execute
... end <br>
for sql execute ... end at</b> d <b>and</b> e <b>loop</b>
s;s;s; <b>end</b></p>
<p style="margin-left:17%;">In stead of an identifier that
refers to a declared SQL statement you can immediately
execute a SQL statement or a cursor loop by using the syntax
for immediate sql beginning with <b>sql execute</b> and
terminating with <b>end</b> as documented in
<a href="sqldeclaration.html">sqldeclaration(1rwl)</a>. This has advantage over embedded sql
as it allows sql specifications such as <b>array</b>. With
this syntax, the at clause and the <b>and</b> keyword
followed by an expression have the same effects as they do
when executing named SQL statements.</p>
<h2>AMPERSAND REPLACEMENT
<a name="AMPERSAND REPLACEMENT"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">Inspired by
SQL*Plus, you can make directly embedded sql dynamic by
including ampersand replacement in the sql text. To enable
this, use the <b>$ampersand:on</b> directive.</p>
<p style="margin-left:11%; margin-top: 1em">An ampersand
replacement consist of the ampersand character <b>&</b>
followed by the name of a string, integer or double variable
and by a dot (decimal point) character. As an example, if
your embedded sql contains <b>&myvar.</b>, it will be
replaced by the contents of the variable myvar when
executed. The dot character is not optional as in SQL*Plus
even when followed by white space. If you need to include an
actual & character in your sql statement when ampersand
replacement is on, it must be written <b>&&</b> i.e.
two & characters immediately after each other. Note that
the use of && in SQL*Plus is irrelevant in
rwloadsim.</p>
<p style="margin-left:11%; margin-top: 1em">If the variable
is of type integer or double, the text that will be put in
your sql statement is the text representation of your
variable according to your current $iformat or $dformat
setting. Note that this may imply different rounding if
compared to using a placeholder.</p>
<p style="margin-left:11%; margin-top: 1em">If the variable
is of type string, the text will simply be the contents of
your variable, this means you should have single quote
around it if it is to be taken as an actual text string by
Oracle. If the string variable actually contains e.g. the
name of a table, you may consider putting double quotes
around it unless you want Oracle’s standard automatic
conversion to upper case.</p>
<p style="margin-left:11%; margin-top: 1em">Ampersand
replacement has similar functionality as immediate sql, when
used with a dynamically generated sql text. The benefit of
ampersand replacement is the simpler syntax similar to
SQL*Plus.</p>
<p style="margin-left:11%; margin-top: 1em">When ampersand
replacement is in use, the <b>$embeddedqueryarray</b> is in
effect for cursor loop queries, but <b>$embeddeddmlarray</b>
is not as the actual sql text potentially is different for
each execution. The same constraint applies for dynamically
generated immediate sql.</p>
<p style="margin-left:11%; margin-top: 1em">Ampersand
replacement is only available when the sql is embedded
directly in your code. A warning will be shown if you
attempt using it in any other place such as when declaring a
sql variable.</p>
<p style="margin-left:11%; margin-top: 1em">Ampersand
replace can only be used to replace <i>parts</i> of your sql
with the contents of variable; your sql statement must still
explicitly start with a relevant keyword such as
<b>select</b>, <b>create</b> or <b>insert</b>, as rwloadsim
needs such a keyword to identify the beginning of a sql
statement.</p>
<h2>EXAMPLES
<a name="EXAMPLES"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em"><b>Cursor loop
with embedded sql</b></p>
<p style="margin-left:11%; margin-top: 1em">This example
shows how to declare some variables that match select list
elements and bind variables in your SQL statement. The sql
statement is not declared, but is embedded as a cursor loop
against some specified database.</p>
<pre style="margin-left:17%; margin-top: 1em">string ename, dname;
integer empno;
double monthsal;
integer deptno := 10; $useroption:deptno
for
select e.empno, e.ename, d.dname
, e.sal/12 monthsal
from emp e join dept d
on e.deptno = d.deptno
where d.deptno=:deptno
/
at mydatabase
loop
printf "%5d %10s %6.2f %10s0, empno, ename, monthsal, dname;
end loop;</pre>
<p style="margin-left:11%; margin-top: 1em"><b>Ampersand
replacement</b></p>
<p style="margin-left:11%; margin-top: 1em">This example
shows how you can use ampersand replacement to create a
number of identically shaped tables.</p>
<pre style="margin-left:17%; margin-top: 1em">$ampersand:on
string tablename;
integer t;
for t := 1 .. 10 loop
# generate the tablename
sprintf tablename, "tab%02d", t;
# execute the ddl using ampersand replacement for the name of the table
create table &tablename. (a number primary key, b varchar2(10));
end loop;</pre>
<p style="margin-left:11%; margin-top: 1em">For more
examples, see <a href="sqldeclaration.html">sqldeclaration(1rwl)</a>.</p>
<h2>NOTES
<a name="NOTES"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">Embedded sql
text does itself include a terminator (for both SQL and
PL/SQL a line with only white space terminated by <b>/</b>
or <b>.</b> or for SQL a <b>;</b> at the end of a line) and
that it therefore should not be followed by another
terminating <b>;</b> when used as a simple statement. In
other cases, such as when an at clause is present, the
complete statement must be terminated with an <b>;</b> as
any other statement in rwloadsim.</p>
<p style="margin-left:11%; margin-top: 1em">After executing
DML, you should always have either a <b>commit</b> or
<b>rollback</b>, and rwloadsim will display a warning and
force a rollback if this is missing. In your main program,
such statements must be wrapped in an executionblock unless
you have a default database declared as dedicated.</p>
<h2>COPYRIGHT
<a name="COPYRIGHT"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em">Copyright
© 2023 Oracle Corporation <br>
Licensed under the Universal Permissive License v 1.0 as
shown at https://oss.oracle.com/licenses/upl</p>
<h2>SEE ALSO
<a name="SEE ALSO"></a>
</h2>
<p style="margin-left:11%; margin-top: 1em"><a href="statement.html">statement(1rwl)</a>,
<a href="identifier.html">identifier(1rwl)</a>, <a href="sqldeclaration.html">sqldeclaration(1rwl)</a>, <a href="sqlstatement.html">sqlstatement(1rwl)</a>,
<a href="atclause.html">atclause(1rwl)</a>, <a href="databasestatement.html">databasestatement(1rwl)</a>, <a href="expression.html">expression(1rwl)</a>,
<a href="compoundstatement.html">compoundstatement(1rwl)</a></p>
<hr>
</body>
</html>