This repository was archived by the owner on Oct 28, 2023. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 2
Expand file tree
/
Copy pathdbms_sql.out
More file actions
439 lines (432 loc) · 11.4 KB
/
Copy pathdbms_sql.out
File metadata and controls
439 lines (432 loc) · 11.4 KB
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
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
do $$
declare
c int;
strval varchar;
intval int;
nrows int default 30;
begin
c := dbms_sql.open_cursor();
call dbms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, :nrows) g(i)');
call dbms_sql.bind_variable(c, 'nrows', nrows);
call dbms_sql.define_column(c, 1, strval);
call dbms_sql.define_column(c, 2, intval);
perform dbms_sql.execute(c);
while dbms_sql.fetch_rows(c) > 0
loop
call dbms_sql.column_value(c, 1, strval);
call dbms_sql.column_value(c, 2, intval);
raise notice 'c1: %, c2: %', strval, intval;
end loop;
call dbms_sql.close_cursor(c);
end;
$$;
NOTICE: c1: ahoj1, c2: 1
NOTICE: c1: ahoj2, c2: 2
NOTICE: c1: ahoj3, c2: 3
NOTICE: c1: ahoj4, c2: 4
NOTICE: c1: ahoj5, c2: 5
NOTICE: c1: ahoj6, c2: 6
NOTICE: c1: ahoj7, c2: 7
NOTICE: c1: ahoj8, c2: 8
NOTICE: c1: ahoj9, c2: 9
NOTICE: c1: ahoj10, c2: 10
NOTICE: c1: ahoj11, c2: 11
NOTICE: c1: ahoj12, c2: 12
NOTICE: c1: ahoj13, c2: 13
NOTICE: c1: ahoj14, c2: 14
NOTICE: c1: ahoj15, c2: 15
NOTICE: c1: ahoj16, c2: 16
NOTICE: c1: ahoj17, c2: 17
NOTICE: c1: ahoj18, c2: 18
NOTICE: c1: ahoj19, c2: 19
NOTICE: c1: ahoj20, c2: 20
NOTICE: c1: ahoj21, c2: 21
NOTICE: c1: ahoj22, c2: 22
NOTICE: c1: ahoj23, c2: 23
NOTICE: c1: ahoj24, c2: 24
NOTICE: c1: ahoj25, c2: 25
NOTICE: c1: ahoj26, c2: 26
NOTICE: c1: ahoj27, c2: 27
NOTICE: c1: ahoj28, c2: 28
NOTICE: c1: ahoj29, c2: 29
NOTICE: c1: ahoj30, c2: 30
do $$
declare
c int;
strval varchar;
intval int;
nrows int default 30;
begin
c := dbms_sql.open_cursor();
call dbms_sql.parse(c, 'select ''ahoj'' || i, i from generate_series(1, :nrows) g(i)');
call dbms_sql.bind_variable(c, 'nrows', nrows);
call dbms_sql.define_column(c, 1, strval);
call dbms_sql.define_column(c, 2, intval);
perform dbms_sql.execute(c);
while dbms_sql.fetch_rows(c) > 0
loop
strval := dbms_sql.column_value_f(c, 1, strval);
intval := dbms_sql.column_value_f(c, 2, intval);
raise notice 'c1: %, c2: %', strval, intval;
end loop;
call dbms_sql.close_cursor(c);
end;
$$;
NOTICE: c1: ahoj1, c2: 1
NOTICE: c1: ahoj2, c2: 2
NOTICE: c1: ahoj3, c2: 3
NOTICE: c1: ahoj4, c2: 4
NOTICE: c1: ahoj5, c2: 5
NOTICE: c1: ahoj6, c2: 6
NOTICE: c1: ahoj7, c2: 7
NOTICE: c1: ahoj8, c2: 8
NOTICE: c1: ahoj9, c2: 9
NOTICE: c1: ahoj10, c2: 10
NOTICE: c1: ahoj11, c2: 11
NOTICE: c1: ahoj12, c2: 12
NOTICE: c1: ahoj13, c2: 13
NOTICE: c1: ahoj14, c2: 14
NOTICE: c1: ahoj15, c2: 15
NOTICE: c1: ahoj16, c2: 16
NOTICE: c1: ahoj17, c2: 17
NOTICE: c1: ahoj18, c2: 18
NOTICE: c1: ahoj19, c2: 19
NOTICE: c1: ahoj20, c2: 20
NOTICE: c1: ahoj21, c2: 21
NOTICE: c1: ahoj22, c2: 22
NOTICE: c1: ahoj23, c2: 23
NOTICE: c1: ahoj24, c2: 24
NOTICE: c1: ahoj25, c2: 25
NOTICE: c1: ahoj26, c2: 26
NOTICE: c1: ahoj27, c2: 27
NOTICE: c1: ahoj28, c2: 28
NOTICE: c1: ahoj29, c2: 29
NOTICE: c1: ahoj30, c2: 30
create table foo(a int, b varchar, c numeric);
do $$
declare c int;
begin
c := dbms_sql.open_cursor();
call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)');
for i in 1..100
loop
call dbms_sql.bind_variable(c, 'a', i);
call dbms_sql.bind_variable(c, 'b', 'Ahoj ' || i);
call dbms_sql.bind_variable(c, 'c', i + 0.033);
perform dbms_sql.execute(c);
end loop;
end;
$$;
select * from foo;
a | b | c
-----+----------+---------
1 | Ahoj 1 | 1.033
2 | Ahoj 2 | 2.033
3 | Ahoj 3 | 3.033
4 | Ahoj 4 | 4.033
5 | Ahoj 5 | 5.033
6 | Ahoj 6 | 6.033
7 | Ahoj 7 | 7.033
8 | Ahoj 8 | 8.033
9 | Ahoj 9 | 9.033
10 | Ahoj 10 | 10.033
11 | Ahoj 11 | 11.033
12 | Ahoj 12 | 12.033
13 | Ahoj 13 | 13.033
14 | Ahoj 14 | 14.033
15 | Ahoj 15 | 15.033
16 | Ahoj 16 | 16.033
17 | Ahoj 17 | 17.033
18 | Ahoj 18 | 18.033
19 | Ahoj 19 | 19.033
20 | Ahoj 20 | 20.033
21 | Ahoj 21 | 21.033
22 | Ahoj 22 | 22.033
23 | Ahoj 23 | 23.033
24 | Ahoj 24 | 24.033
25 | Ahoj 25 | 25.033
26 | Ahoj 26 | 26.033
27 | Ahoj 27 | 27.033
28 | Ahoj 28 | 28.033
29 | Ahoj 29 | 29.033
30 | Ahoj 30 | 30.033
31 | Ahoj 31 | 31.033
32 | Ahoj 32 | 32.033
33 | Ahoj 33 | 33.033
34 | Ahoj 34 | 34.033
35 | Ahoj 35 | 35.033
36 | Ahoj 36 | 36.033
37 | Ahoj 37 | 37.033
38 | Ahoj 38 | 38.033
39 | Ahoj 39 | 39.033
40 | Ahoj 40 | 40.033
41 | Ahoj 41 | 41.033
42 | Ahoj 42 | 42.033
43 | Ahoj 43 | 43.033
44 | Ahoj 44 | 44.033
45 | Ahoj 45 | 45.033
46 | Ahoj 46 | 46.033
47 | Ahoj 47 | 47.033
48 | Ahoj 48 | 48.033
49 | Ahoj 49 | 49.033
50 | Ahoj 50 | 50.033
51 | Ahoj 51 | 51.033
52 | Ahoj 52 | 52.033
53 | Ahoj 53 | 53.033
54 | Ahoj 54 | 54.033
55 | Ahoj 55 | 55.033
56 | Ahoj 56 | 56.033
57 | Ahoj 57 | 57.033
58 | Ahoj 58 | 58.033
59 | Ahoj 59 | 59.033
60 | Ahoj 60 | 60.033
61 | Ahoj 61 | 61.033
62 | Ahoj 62 | 62.033
63 | Ahoj 63 | 63.033
64 | Ahoj 64 | 64.033
65 | Ahoj 65 | 65.033
66 | Ahoj 66 | 66.033
67 | Ahoj 67 | 67.033
68 | Ahoj 68 | 68.033
69 | Ahoj 69 | 69.033
70 | Ahoj 70 | 70.033
71 | Ahoj 71 | 71.033
72 | Ahoj 72 | 72.033
73 | Ahoj 73 | 73.033
74 | Ahoj 74 | 74.033
75 | Ahoj 75 | 75.033
76 | Ahoj 76 | 76.033
77 | Ahoj 77 | 77.033
78 | Ahoj 78 | 78.033
79 | Ahoj 79 | 79.033
80 | Ahoj 80 | 80.033
81 | Ahoj 81 | 81.033
82 | Ahoj 82 | 82.033
83 | Ahoj 83 | 83.033
84 | Ahoj 84 | 84.033
85 | Ahoj 85 | 85.033
86 | Ahoj 86 | 86.033
87 | Ahoj 87 | 87.033
88 | Ahoj 88 | 88.033
89 | Ahoj 89 | 89.033
90 | Ahoj 90 | 90.033
91 | Ahoj 91 | 91.033
92 | Ahoj 92 | 92.033
93 | Ahoj 93 | 93.033
94 | Ahoj 94 | 94.033
95 | Ahoj 95 | 95.033
96 | Ahoj 96 | 96.033
97 | Ahoj 97 | 97.033
98 | Ahoj 98 | 98.033
99 | Ahoj 99 | 99.033
100 | Ahoj 100 | 100.033
(100 rows)
truncate foo;
do $$
declare c int;
begin
c := dbms_sql.open_cursor();
call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)');
for i in 1..100
loop
perform dbms_sql.bind_variable_f(c, 'a', i);
perform dbms_sql.bind_variable_f(c, 'b', 'Ahoj ' || i);
perform dbms_sql.bind_variable_f(c, 'c', i + 0.033);
perform dbms_sql.execute(c);
end loop;
end;
$$;
select * from foo;
a | b | c
-----+----------+---------
1 | Ahoj 1 | 1.033
2 | Ahoj 2 | 2.033
3 | Ahoj 3 | 3.033
4 | Ahoj 4 | 4.033
5 | Ahoj 5 | 5.033
6 | Ahoj 6 | 6.033
7 | Ahoj 7 | 7.033
8 | Ahoj 8 | 8.033
9 | Ahoj 9 | 9.033
10 | Ahoj 10 | 10.033
11 | Ahoj 11 | 11.033
12 | Ahoj 12 | 12.033
13 | Ahoj 13 | 13.033
14 | Ahoj 14 | 14.033
15 | Ahoj 15 | 15.033
16 | Ahoj 16 | 16.033
17 | Ahoj 17 | 17.033
18 | Ahoj 18 | 18.033
19 | Ahoj 19 | 19.033
20 | Ahoj 20 | 20.033
21 | Ahoj 21 | 21.033
22 | Ahoj 22 | 22.033
23 | Ahoj 23 | 23.033
24 | Ahoj 24 | 24.033
25 | Ahoj 25 | 25.033
26 | Ahoj 26 | 26.033
27 | Ahoj 27 | 27.033
28 | Ahoj 28 | 28.033
29 | Ahoj 29 | 29.033
30 | Ahoj 30 | 30.033
31 | Ahoj 31 | 31.033
32 | Ahoj 32 | 32.033
33 | Ahoj 33 | 33.033
34 | Ahoj 34 | 34.033
35 | Ahoj 35 | 35.033
36 | Ahoj 36 | 36.033
37 | Ahoj 37 | 37.033
38 | Ahoj 38 | 38.033
39 | Ahoj 39 | 39.033
40 | Ahoj 40 | 40.033
41 | Ahoj 41 | 41.033
42 | Ahoj 42 | 42.033
43 | Ahoj 43 | 43.033
44 | Ahoj 44 | 44.033
45 | Ahoj 45 | 45.033
46 | Ahoj 46 | 46.033
47 | Ahoj 47 | 47.033
48 | Ahoj 48 | 48.033
49 | Ahoj 49 | 49.033
50 | Ahoj 50 | 50.033
51 | Ahoj 51 | 51.033
52 | Ahoj 52 | 52.033
53 | Ahoj 53 | 53.033
54 | Ahoj 54 | 54.033
55 | Ahoj 55 | 55.033
56 | Ahoj 56 | 56.033
57 | Ahoj 57 | 57.033
58 | Ahoj 58 | 58.033
59 | Ahoj 59 | 59.033
60 | Ahoj 60 | 60.033
61 | Ahoj 61 | 61.033
62 | Ahoj 62 | 62.033
63 | Ahoj 63 | 63.033
64 | Ahoj 64 | 64.033
65 | Ahoj 65 | 65.033
66 | Ahoj 66 | 66.033
67 | Ahoj 67 | 67.033
68 | Ahoj 68 | 68.033
69 | Ahoj 69 | 69.033
70 | Ahoj 70 | 70.033
71 | Ahoj 71 | 71.033
72 | Ahoj 72 | 72.033
73 | Ahoj 73 | 73.033
74 | Ahoj 74 | 74.033
75 | Ahoj 75 | 75.033
76 | Ahoj 76 | 76.033
77 | Ahoj 77 | 77.033
78 | Ahoj 78 | 78.033
79 | Ahoj 79 | 79.033
80 | Ahoj 80 | 80.033
81 | Ahoj 81 | 81.033
82 | Ahoj 82 | 82.033
83 | Ahoj 83 | 83.033
84 | Ahoj 84 | 84.033
85 | Ahoj 85 | 85.033
86 | Ahoj 86 | 86.033
87 | Ahoj 87 | 87.033
88 | Ahoj 88 | 88.033
89 | Ahoj 89 | 89.033
90 | Ahoj 90 | 90.033
91 | Ahoj 91 | 91.033
92 | Ahoj 92 | 92.033
93 | Ahoj 93 | 93.033
94 | Ahoj 94 | 94.033
95 | Ahoj 95 | 95.033
96 | Ahoj 96 | 96.033
97 | Ahoj 97 | 97.033
98 | Ahoj 98 | 98.033
99 | Ahoj 99 | 99.033
100 | Ahoj 100 | 100.033
(100 rows)
truncate foo;
do $$
declare
c int;
a int[];
b varchar[];
ca numeric[];
begin
c := dbms_sql.open_cursor();
call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)');
a := ARRAY[1, 2, 3, 4, 5];
b := ARRAY['Ahoj', 'Nazdar', 'Bazar'];
ca := ARRAY[3.14, 2.22, 3.8, 4];
call dbms_sql.bind_array(c, 'a', a);
call dbms_sql.bind_array(c, 'b', b);
call dbms_sql.bind_array(c, 'c', ca);
raise notice 'inserted rows %d', dbms_sql.execute(c);
end;
$$;
NOTICE: inserted rows 3d
select * from foo;
a | b | c
---+--------+------
1 | Ahoj | 3.14
2 | Nazdar | 2.22
3 | Bazar | 3.8
(3 rows)
truncate foo;
do $$
declare
c int;
a int[];
b varchar[];
ca numeric[];
begin
c := dbms_sql.open_cursor();
call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)');
a := ARRAY[1, 2, 3, 4, 5];
b := ARRAY['Ahoj', 'Nazdar', 'Bazar'];
ca := ARRAY[3.14, 2.22, 3.8, 4];
call dbms_sql.bind_array(c, 'a', a, 2, 3);
call dbms_sql.bind_array(c, 'b', b, 3, 4);
call dbms_sql.bind_array(c, 'c', ca);
raise notice 'inserted rows %d', dbms_sql.execute(c);
end;
$$;
NOTICE: inserted rows 1d
select * from foo;
a | b | c
---+-------+-----
3 | Bazar | 3.8
(1 row)
truncate foo;
do $$
declare
c int;
a int[];
b varchar[];
ca numeric[];
begin
c := dbms_sql.open_cursor();
call dbms_sql.parse(c, 'select i, ''Ahoj'' || i, i + 0.003 from generate_series(1, 35) g(i)');
call dbms_sql.define_array(c, 1, a, 10, 1);
call dbms_sql.define_array(c, 2, b, 10, 1);
call dbms_sql.define_array(c, 3, ca, 10, 1);
perform dbms_sql.execute(c);
while dbms_sql.fetch_rows(c) > 0
loop
call dbms_sql.column_value(c, 1, a);
call dbms_sql.column_value(c, 2, b);
call dbms_sql.column_value(c, 3, ca);
raise notice 'a = %', a;
raise notice 'b = %', b;
raise notice 'c = %', ca;
end loop;
call dbms_sql.close_cursor(c);
end;
$$;
NOTICE: a = {1,2,3,4,5,6,7,8,9,10}
NOTICE: b = {Ahoj1,Ahoj2,Ahoj3,Ahoj4,Ahoj5,Ahoj6,Ahoj7,Ahoj8,Ahoj9,Ahoj10}
NOTICE: c = {1.003,2.003,3.003,4.003,5.003,6.003,7.003,8.003,9.003,10.003}
NOTICE: a = {11,12,13,14,15,16,17,18,19,20}
NOTICE: b = {Ahoj11,Ahoj12,Ahoj13,Ahoj14,Ahoj15,Ahoj16,Ahoj17,Ahoj18,Ahoj19,Ahoj20}
NOTICE: c = {11.003,12.003,13.003,14.003,15.003,16.003,17.003,18.003,19.003,20.003}
NOTICE: a = {21,22,23,24,25,26,27,28,29,30}
NOTICE: b = {Ahoj21,Ahoj22,Ahoj23,Ahoj24,Ahoj25,Ahoj26,Ahoj27,Ahoj28,Ahoj29,Ahoj30}
NOTICE: c = {21.003,22.003,23.003,24.003,25.003,26.003,27.003,28.003,29.003,30.003}
NOTICE: a = {31,32,33,34,35}
NOTICE: b = {Ahoj31,Ahoj32,Ahoj33,Ahoj34,Ahoj35}
NOTICE: c = {31.003,32.003,33.003,34.003,35.003}