问题描述
我想读取一个平面文件以获取值的动态列表(值的数量不固定)&那么我想在Pro * C中的select查询的IN子句中使用此值列表.使用Pro * C实现此目标的最佳方法是什么?我在此处 a>& 此处，但我只想检查是否有更适合我的用例的解决方案.权衡我可能拥有的选择的利弊也将是有用的.
I want to read a flat file to fetch a dynamic list of values (number of values is not fixed) & then I want to use this list of values in the IN clause of an select query in Pro *C. What is the best way to achieve this using Pro *C? I came across a few useful articles here & here but I just want to check if there is a more suited solution to my use case. It will be also useful to weigh the pros and cons of options that I might have.
仅举一个例子，以下是查询:
Just to give an example, the following is the query:
SELECT ca.co_id
INTO :host_co_id_1
FROM contr1 ch1, contr2 ca
WHERE ch_seqno = (SELECT MAX (ch_seqno) FROM ontr1 ch2
WHERE ch1.co_id = ch2.co_id)
and ch1.ch_status IN ('a','s')
AND ca.co_id = ch1.co_id
AND ca.tmcode IN (14,16,36,37,38,39,40,41,42,79,60);
电话号码列表:
14,16,36,37,38,39,40,41,42,79,60
是从文件读取的动态列表.
is the dynamic list read from a file.
推荐答案
使用 Oracle动态SQL:方法4 .演示中包含的sample10.pc说明了此方法.
下面的示例代码
可以使用具有多个动态值的IN子句从文件中读取变量stmt.
This is possible using Oracle Dynamic SQL:Method 4.sample10.pc included in the demo illustrates this method.
sample code below
The variable stmt can be read from a file with an IN clause with multiple dynamic values.
/*
* A very simple program that demonstrates how to do
* array fetches using dynamic SQL Method 4.
* file name: sample.pc
* environment: Win 7,code::blocks(mingw/gcc),oracle 11g(on solaris)
* Make sure to precompile with MODE=ORACLE.
example:
C:\oracle_instant_client\instantclient_11_2\sdk\proc.exe DYNAMIC=ORACLE C:\code\sample.pc
*/
#include <stdio.h>
#include <stdlib.h>
#include <oci.h>
EXEC SQL INCLUDE sqlcpr;
EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE oraca;
EXEC SQL INCLUDE sqlca;
#define MAX_SELECT_ITEMS 15
#define FETCH_SIZE 100 /* Fetch in 100 row chunks. */
#define MAX_CHARS 20 /* Change this for max colume size */
#define MAX_NAME_SIZE 20 /* Maximum size of a select-list item name. */
SQLDA *selda;
/* Data buffer. */
char c_data[MAX_SELECT_ITEMS][FETCH_SIZE][MAX_CHARS];
void print_rows(n)
int n;
{
int row, sli;
for (row = 0; row < n; row++)
{
for (sli = 0; sli < selda->N; sli++)
{
printf("%.20s ", c_data[sli][row]);
}
printf("\n");
}
}
EXEC SQL BEGIN DECLARE SECTION;
int array_size = FETCH_SIZE; /* needs to be a host var for FOR */
char *username = "ORACLE_USER/ORACLE_PASS";
char sid[]="ORACLE_SID";
char *stmt = "select t.a,t.b,t.c,p.d,t.e,t.f,t.g,t.h,t.i,t.j from table_t t,table_p p where p.error_code = t.error_code and rownum <= 50";
EXEC SQL END DECLARE SECTION;
void sql_error()
{
char msgbuf[512];/*
size_t msgbuf_len, msg_len;
msgbuf_len = sizeof(msgbuf);
sqlglm(msgbuf, &msgbuf_len, &msg_len);
printf ("\n\n%.*s\n", msg_len, msgbuf);*/
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
exit(EXIT_FAILURE);
}
void main()
{
int row_count;
int sli; /* select-list item */
EXEC SQL CONNECT :username USING :sid;
if (sqlca.sqlcode == 0)
printf("Connected.\n");
else
{
printf("Connection failed.\n");
exit(EXIT_FAILURE);
}
EXEC SQL WHENEVER SQLERROR DO sql_error();
selda = SQLSQLDAAlloc (SQL_SINGLE_RCTX, MAX_SELECT_ITEMS, MAX_NAME_SIZE, 0);
EXEC SQL PREPARE S FROM :stmt;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO selda;
selda->N = selda->F; /* Assumed not negative. */
for (sli = 0; sli < selda->N; sli++)
{
/* Set addresses of heads of the arrays in the V element. */
selda->V[sli] = c_data[sli][0];
/* Convert everything to varchar on output. */
selda->T[sli] = 1;
/* Set the maximum lengths. */
selda->L[sli] = MAX_CHARS;
}
for (row_count = 0; ;)
{
/* Do the fetch. The loop breaks on NOT FOUND. */
EXEC SQL FOR :array_size FETCH C USING DESCRIPTOR selda;
print_rows(sqlca.sqlerrd[2] - row_count);
row_count = sqlca.sqlerrd[2];
if (sqlca.sqlcode == 1403)
break;
}
/* if (sqlca.sqlerrd[2] - row_count > 0)
print_rows(sqlca.sqlerrd[2] - row_count); */
printf("\n%d rows retrieved\n", sqlca.sqlerrd[2]);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_SUCCESS);
}
这篇关于Pro * C查询IN子句中的多个动态值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！