Oracle 动态sql 实现方式
时间：2021-07-01 10:21:17
帮助过：10人阅读
/*******************************************************************
2 Sample Program 10: Dynamic SQL Method 4
3
4 This program connects you to ORACLE using your username and
5 password, then prompts you for a SQL statement. You can enter
6 any legal SQL statement. Use regular SQL syntax, not embedded SQL.
7 Your statement will be processed. If it is a query, the rows
8 fetched are displayed.
9 You can enter multiline statements. The limit is 1023 characters.
10 This sample program only processes up to MAX_ITEMS bind variables and
11 MAX_ITEMS select-list items. MAX_ITEMS is #defined to be 40.
12 *******************************************************************/
13
14 #include <stdio.h>
15 #include <
string.h>
16 #include <setjmp.h>
17 #include <sqlda.h>
18 #include <stdlib.h>
19 #include <sqlcpr.h>
20
21 #define SQL_SINGLE_RCTX ((void *)0)
22
23 /* Maximum number of select-list items or bind variables. */
24 #define MAX_ITEMS 40
25
26 /* Maximum lengths of the _names_ of the
27 select-list items or indicator variables. */
28 #define MAX_VNAME_LEN 30
29 #define MAX_INAME_LEN 30
30
31 #ifndef NULL
32 #define NULL 0
33 #endif
34
35 /* Prototypes */
36 #if defined(__STDC__)
37 void sql_error(
void);
38 int oracle_connect(
void);
39 int alloc_descriptors(
int,
int,
int);
40 int get_dyn_statement(
void);
41 void set_bind_variables(
void);
42 void process_select_list(
void);
43 void help(
void);
44 #else
45 void sql_error(
/*_ void _*/);
46 int oracle_connect(
/*_ void _*/);
47 int alloc_descriptors(
/*_ int, int, int _*/);
48 int get_dyn_statement(
/* void _*/);
49 void set_bind_variables(
/*_ void -*/);
50 void process_select_list(
/*_ void _*/);
51 void help(
/*_ void _*/);
52 #endif
53
54 char *dml_commands[] = {
"SELECT",
"select",
"INSERT",
"insert",
55 "UPDATE",
"update",
"DELETE",
"delete"};
56
57 EXEC SQL INCLUDE sqlda;
58 EXEC SQL INCLUDE sqlca;
59
60 EXEC SQL BEGIN DECLARE SECTION;
61 char dyn_statement[
1024];
62 EXEC SQL VAR dyn_statement IS STRING(
1024);
63 EXEC SQL END DECLARE SECTION;
64
65 SQLDA *
bind_dp;
66 SQLDA *
select_dp;
67
68 /* Define a buffer to hold longjmp state info. */
69 jmp_buf jmp_continue;
70
71 /* A global flag for the error routine. */
72 int parse_flag =
0;
73
74 void main()
75 {
76 int i;
77
78 /* Connect to the database. */
79 if (oracle_connect() !=
0)
80 exit(
1);
81
82 /* Allocate memory for the select and bind descriptors. */
83 if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) !=
0)
84 exit(
1);
85
86 /* Process SQL statements. */
87 for (;;)
88 {
89 (
void) setjmp(jmp_continue);
90
91 /* Get the statement. Break on "exit". */
92 if (get_dyn_statement() !=
0)
93 break;
94
95 /* Prepare the statement and declare a cursor. */
96 EXEC SQL WHENEVER SQLERROR DO sql_error();
97
98 parse_flag =
1;
/* Set a flag for sql_error(). */
99 EXEC SQL PREPARE S FROM :dyn_statement;
100 parse_flag =
0;
/* Unset the flag. */
101
102 EXEC SQL DECLARE C CURSOR FOR S;
103
104 /* Set the bind variables for any placeholders in the
105 SQL statement. */
106 set_bind_variables();
107
108 /* Open the cursor and execute the statement.
109 * If the statement is not a query (SELECT), the
110 * statement processing is completed after the
111 * OPEN.
112 */
113
114 EXEC SQL OPEN C USING DESCRIPTOR bind_dp;
115
116 /* Call the function that processes the select-list.
117 * If the statement is not a query, this function
118 * just returns, doing nothing.
119 */
120 process_select_list();
121
122 /* Tell user how many rows processed. */
123 for (i =
0; i <
8; i++
)
124 {
125 if (strncmp(dyn_statement, dml_commands[i],
6) ==
0)
126 {
127 printf(
"\n\n%d row%c processed.\n", sqlca.sqlerrd[
2],
128 sqlca.sqlerrd[
2] ==
1 ?
‘\0‘ :
‘s‘);
129 break;
130 }
131 }
132 }
/* end of for(;;) statement-processing loop */
133
134 /* When done, free the memory allocated for
135 pointers in the bind and select descriptors. */
136 for (i =
0; i < MAX_ITEMS; i++
)
137 {
138 if (bind_dp->V[i] != (
char *)
0)
139 free(bind_dp->
V[i]);
140 free(bind_dp->I[i]);
/* MAX_ITEMS were allocated. */
141 if (select_dp->V[i] != (
char *)
0)
142 free(select_dp->
V[i]);
143 free(select_dp->I[i]);
/* MAX_ITEMS were allocated. */
144 }
145
146 /* Free space used by the descriptors themselves. */
147 SQLSQLDAFree(SQL_SINGLE_RCTX, bind_dp);
148 SQLSQLDAFree(SQL_SINGLE_RCTX, select_dp);
149
150 EXEC SQL WHENEVER SQLERROR CONTINUE;
151 /* Close the cursor. */
152 EXEC SQL CLOSE C;
153
154 EXEC SQL COMMIT WORK RELEASE;
155 puts(
"\nHave a good day!\n");
156
157 EXEC SQL WHENEVER SQLERROR DO sql_error();
158 return;
159 }
160
161
162 int oracle_connect()
163 {
164 EXEC SQL BEGIN DECLARE SECTION;
165 VARCHAR username[
128];
166 VARCHAR password[
32];
167 EXEC SQL END DECLARE SECTION;
168
169 printf(
"\nusername: ");
170 fgets((
char *) username.arr,
sizeof username.arr, stdin);
171 username.arr[strlen((
char *) username.arr)-
1] =
‘\0‘;
172 username.len = (unsigned
short)strlen((
char *
) username.arr);
173
174 printf(
"password: ");
175 fgets((
char *) password.arr,
sizeof password.arr, stdin);
176 password.arr[strlen((
char *) password.arr) -
1] =
‘\0‘;
177 password.len = (unsigned
short)strlen((
char *
) password.arr);
178
179
180 EXEC SQL WHENEVER SQLERROR GOTO connect_error;
181
182 EXEC SQL CONNECT :username IDENTIFIED BY :password;
183
184 printf(
"\nConnected to ORACLE as user %s.\n", username.arr);
185
186 return 0;
187
188 connect_error:
189 fprintf(stderr,
"Cannot connect to ORACLE as user %s\n", username.arr);
190 return -
1;
191 }
192
193
194 /*
195 * Allocate the BIND and SELECT descriptors using SQLSQLDAAlloc().
196 * Also allocate the pointers to indicator variables
197 * in each descriptor. The pointers to the actual bind
198 * variables and the select-list items are realloc‘ed in
199 * the set_bind_variables() or process_select_list()
200 * routines. This routine allocates 1 byte for select_dp->V[i]
201 * and bind_dp->V[i], so the realloc will work correctly.
202 */
203
204 alloc_descriptors(size, max_vname_len, max_iname_len)
205 int size;
206 int max_vname_len;
207 int max_iname_len;
208 {
209 int i;
210
211 /*
212 * The first SQLSQLDAAlloc parameter is the runtime context.
213
214 * The second parameter determines the maximum number of
215 * array elements in each variable in the descriptor. In
216 * other words, it determines the maximum number of bind
217 * variables or select-list items in the SQL statement.
218 *
219 * The third parameter determines the maximum length of
220 * strings used to hold the names of select-list items
221 * or placeholders. The maximum length of column
222 * names in ORACLE is 30, but you can allocate more or less
223 * as needed.
224 *
225 * The fourth parameter determines the maximum length of
226 * strings used to hold the names of any indicator
227 * variables. To follow ORACLE standards, the maximum
228 * length of these should be 30. But, you can allocate
229 * more or less as needed.
230 */
231
232 if ((bind_dp =
233 SQLSQLDAAlloc(SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
234 (SQLDA *)
0)
235 {
236 fprintf(stderr,
237 "Cannot allocate memory for bind descriptor.");
238 return -
1;
/* Have to exit in this case. */
239 }
240
241 if ((select_dp =
242 SQLSQLDAAlloc (SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
243 (SQLDA *)
0)
244 {
245 fprintf(stderr,
246 "Cannot allocate memory for select descriptor.");
247 return -
1;
248 }
249 select_dp->N =
MAX_ITEMS;
250
251 /* Allocate the pointers to the indicator variables, and the
252 actual data. */
253 for (i =
0; i < MAX_ITEMS; i++
) {
254 bind_dp->I[i] = (
short *)
malloc(
sizeof (
short));
255 select_dp->I[i] = (
short *)
malloc(
sizeof(
short));
256 bind_dp->V[i] = (
char *)
malloc(
1);
257 select_dp->V[i] = (
char *)
malloc(
1);
258 }
259
260 return 0;
261 }
262
263
264 int get_dyn_statement()
265 {
266 char *cp, linebuf[
256];
267 int iter, plsql;
268
269
270 for (plsql =
0, iter =
1; ;)
271 {
272 if (iter ==
1)
273 {
274 printf(
"\nSQL> ");
275 dyn_statement[
0] =
‘\0‘;
276 }
277
278 fgets(linebuf,
sizeof linebuf, stdin);
279
280 cp = strrchr(linebuf,
‘\n‘);
281 if (cp && cp !=
linebuf)
282 *cp =
‘ ‘;
283 else if (cp ==
linebuf)
284 continue;
285
286 if ((strncmp(linebuf,
"EXIT",
4) ==
0) ||
287 (strncmp(linebuf,
"exit",
4) ==
0))
288 {
289 return -
1;
290 }
291
292 else if (linebuf[
0] ==
‘?‘ ||
293 (strncmp(linebuf,
"HELP",
4) ==
0) ||
294 (strncmp(linebuf,
"help",
4) ==
0))
295 {
296 help();
297 iter =
1;
298 continue;
299 }
300
301 if (strstr(linebuf,
"BEGIN") ||
302 (strstr(linebuf,
"begin")))
303 {
304 plsql =
1;
305 }
306
307 strcat(dyn_statement, linebuf);
308
309 if ((plsql && (cp = strrchr(dyn_statement,
‘/‘))) ||
310 (!plsql && (cp = strrchr(dyn_statement,
‘;‘))))
311 {
312 *cp =
‘\0‘;
313 break;
314 }
315 else
316 {
317 iter++
;
318 printf(
"%3d ", iter);
319 }
320 }
321 return 0;
322 }
323
324
325
326 void set_bind_variables()
327 {
328 int i, n;
329 char bind_var[
64];
330
331 /* Describe any bind variables (input host variables) */
332 EXEC SQL WHENEVER SQLERROR DO sql_error();
333
334 bind_dp->N = MAX_ITEMS;
/* Initialize count of array elements. */
335 EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
336
337 /* If F is negative, there were more bind variables
338 than originally allocated by SQLSQLDAAlloc(). */
339 if (bind_dp->F <
0)
340 {
341 printf (
"\nToo many bind variables (%d), maximum is %d\n.",
342 -bind_dp->
F, MAX_ITEMS);
343 return;
344 }
345
346 /* Set the maximum number of array elements in the
347 descriptor to the number found. */
348 bind_dp->N = bind_dp->
F;
349
350 /* Get the value of each bind variable as a
351 * character string.
352 *
353 * C[i] contains the length of the bind variable
354 * name used in the SQL statement.
355 * S[i] contains the actual name of the bind variable
356 * used in the SQL statement.
357 *
358 * L[i] will contain the length of the data value
359 * entered.
360 *
361 * V[i] will contain the address of the data value
362 * entered.
363 *
364 * T[i] is always set to 1 because in this sample program
365 * data values for all bind variables are entered
366 * as character strings.
367 * ORACLE converts to the table value from CHAR.
368 *
369 * I[i] will point to the indicator value, which is
370 * set to -1 when the bind variable value is "null".
371 */
372 for (i =
0; i < bind_dp->F; i++
)
373 {
374 printf (
"\nEnter value for bind variable %.*s: ",
375 (
int)bind_dp->C[i], bind_dp->
S[i]);
376 fgets(bind_var,
sizeof bind_var, stdin);
377
378 /* Get length and remove the new line character. */
379 n = strlen(bind_var) -
1;
380
381 /* Set it in the descriptor. */
382 bind_dp->L[i] =
n;
383
384 /* (re-)allocate the buffer for the value.
385 SQLSQLDAAlloc() reserves a pointer location for
386 V[i] but does not allocate the full space for
387 the pointer. */
388
389 bind_dp->V[i] = (
char *)
realloc(bind_dp->
V[i],
390 (bind_dp->L[i] +
1));
391
392 /* And copy it in. */
393 strncpy(bind_dp->
V[i], bind_var, n);
394
395 /* Set the indicator variable‘s value. */
396 if ((strncmp(bind_dp->V[i],
"NULL",
4) ==
0) ||
397 (strncmp(bind_dp->V[i],
"null",
4) ==
0))
398 *bind_dp->I[i] = -
1;
399 else
400 *bind_dp->I[i] =
0;
401
402 /* Set the bind datatype to 1 for CHAR. */
403 bind_dp->T[i] =
1;
404 }
405 return;
406 }
407
408
409
410 void process_select_list()
411 {
412 int i, null_ok, precision, scale;
413
414 if ((strncmp(dyn_statement,
"SELECT",
6) !=
0) &&
415 (strncmp(dyn_statement,
"select",
6) !=
0))
416 {
417 select_dp->F =
0;
418 return;
419 }
420
421 /* If the SQL statement is a SELECT, describe the
422 select-list items. The DESCRIBE function returns
423 their names, datatypes, lengths (including precision
424 and scale), and NULL/NOT NULL statuses. */
425
426 select_dp->N =
MAX_ITEMS;
427
428 EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
429
430 /* If F is negative, there were more select-list
431 items than originally allocated by SQLSQLDAAlloc(). */
432 if (select_dp->F <
0)
433 {
434 printf (
"\nToo many select-list items (%d), maximum is %d\n",
435 -(select_dp->
F), MAX_ITEMS);
436 return;
437 }
438
439 /* Set the maximum number of array elements in the
440 descriptor to the number found. */
441 select_dp->N = select_dp->
F;
442
443 /* Allocate storage for each select-list item.
444
445 SQLNumberPrecV6() is used to extract precision and scale
446 from the length (select_dp->L[i]).
447
448 sqlcolumnNullCheck() is used to reset the high-order bit of
449 the datatype and to check whether the column
450 is NOT NULL.
451
452 CHAR datatypes have length, but zero precision and
453 scale. The length is defined at CREATE time.
454
455 NUMBER datatypes have precision and scale only if
456 defined at CREATE time. If the column
457 definition was just NUMBER, the precision
458 and scale are zero, and you must allocate
459 the required maximum length.
460
461 DATE datatypes return a length of 7 if the default
462 format is used. This should be increased to
463 9 to store the actual date character string.
464 If you use the TO_CHAR function, the maximum
465 length could be 75, but will probably be less
466 (you can see the effects of this in SQL*Plus).
467
468 ROWID datatype always returns a fixed length of 18 if
469 coerced to CHAR.
470
471 LONG and
472 LONG RAW datatypes return a length of 0 (zero),
473 so you need to set a maximum. In this example,
474 it is 240 characters.
475
476 */
477
478 printf (
"\n");
479 for (i =
0; i < select_dp->F; i++
)
480 {
481 char title[MAX_VNAME_LEN];
482 /* Turn off high-order bit of datatype (in this example,
483 it does not matter if the column is NOT NULL). */
484 SQLColumnNullCheck (
0, (unsigned
short *)&(select_dp->
T[i]),
485 (unsigned
short *)&(select_dp->T[i]), &
null_ok);
486
487 switch (select_dp->
T[i])
488 {
489 case 1 :
/* CHAR datatype: no change in length
490 needed, except possibly for TO_CHAR
491 conversions (not handled here). */
492 break;
493 case 2 :
/* NUMBER datatype: use SQLNumberPrecV6() to
494 extract precision and scale. */
495 SQLNumberPrecV6( SQL_SINGLE_RCTX,
