关于SQL硬解析的查找和PRO*C优化方法
[TOC]
1. 硬解析的原理
提到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：
1、语法检查(syntax check)
检查此sql的拼写是否语法。
2、语义检查(semantic check)
诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
3、对sql语句进行解析(prase)
利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。
4、执行sql，返回结果(execute and return)
其中，软、硬解析就发生在第三个过程里。
Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；
假设存在，则将此sql与cache中的进行比较；
假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。
诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。
创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。
这就是在很多项目中，倡导开发设计人员对功能相同的代码要努力保持代码的一致性，以及要在程序中多使用绑定变量的原因。
2. 如何查看硬解析
2.1 按时间段查看全系统的硬解析的排名
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE,
COUNT(*) cnt
FROM gv$sqlarea
WHERE FORCE_MATCHING_SIGNATURE!=0
and first_load_time between '2019-01-01/12:00:00' and '2019-12-30/12:00:00'
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 20
),
sq AS
(SELECT sql_text ,
FORCE_MATCHING_SIGNATURE,
row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM gv$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE
FROM c
)
)
SELECT sq.sql_text ,
sq.FORCE_MATCHING_SIGNATURE,
c.cnt "unshared count"
FROM c,
sq
WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND sq.p =1
ORDER BY c.cnt DESC;
unshared count就是未命中的
查看当前用户的硬解析比例和数量
oracle性能视图：select * from v$sysstat where 1=1;
parse count (hard)：在shared pool中解析调用的未命中次数。当sql语句执行并且该语句不在shared pool或虽然在shared pool但因为两者存在部分差异而不能被使用时产生硬解析。如果一条sql语句原文与当前存在的相同，但查询表不同则认为它们是两条不同语句，则硬解析即会发生。硬解析会带来cpu和资源使用的高昂开销，因为它需要oracle在shared pool中重新分配内存，然后再确定执行计划，最终语句才会被执行。
parse count (total)：解析调用总数，包括软解析和硬解析。当session执行了一条sql语句，该语句已经存在于shared pool并且可以被使用则产生软解析。当语句被使用(即共享) 所有数据相关的现有sql语句(如最优化的执行计划)必须同样适用于当前的声明。这两项统计可被用于计算软解析命中率。
我的数据库版本是550代表硬解析数，因此条件为:
select * from v$sysstat where statistic#=550 or statistic#=549;
更通用的使用如下命令：
select * from v$sysstat where 1=1 and name like 'parse%';
可以执行一下语句，跟踪一下，只能跟踪当前用户的。
查看具体的硬解析语句
有三条判断标准：
1.在gv$sql 视图里有大量相同语句，且每条语句的执行次数只有1次。
2.同一个sql语句，对应着大量的sql_id
3.在生产环境中会导致交易偶尔很慢，甚至超时
查询命令为:
select t.ACTION,t.MODULE,t.SQL_TEXT,t.SQL_ID,t.EXECUTIONS,t.LAST_ACTIVE_TIME,t.* from gv$sql t where sql_text like '%epcc_trlog%';
其中后面的 sql_text like是条件，可以自己更换内容。
查看执行计划:
select * from table(dbms_xplan.display_cursor(sql_id=>'1vp9gxcfccaa2'));
另查看索引命中情况（TODO）
select * from user_tables where 1=1;
select t.table_name,t.num_rows,t.blocks,
t.empty_blocks,
t.avg_space,
t.chain_cnt,
t.avg_row_len,
t.LAST_ANALYZED --最后收集统计信息时间
from user_tables t
where t.num_rows != 0
and t.TABLE_NAME = 'epcc_trlog';--该方法仅适用于非分区表
3. proc中如何使用绑定变量
考虑到业务程序的复杂性、修改工作量和测试限制，最终选用的是方法三。
方法一 静态SQL（需要同时修改PC和业务程序，最稳定）
以epcc_trlog为例，复制epcc_trlog_nhp.pc, nhp的意思为：no hard parse,即使用绑定变量
由于是静态SQL，因此不能使用传入的动态sql，有以下几点需要说明:
- 必须要传入参数进来，不能使用动态的sql，因此业务程序和pc程序都要修改。
- 一个查询条件对应一套程序.
- 参数的个数和绑定变量（EXEC SQL BEGIN DECLARE SECTION中)的个数必须相同，且需要赋值。
- 静态SQL不需要PREPARE游标，而是在DELCEARE的时候声明条件，条件中要写
platdate = :platdate模式。 - 定义游标和后面的FETCH,CLOSE必须要在同一个文件里面，这是oracle的限制，游标的名字必须跟着改。
以下是一个例子
int Epcc_trlog_Dec_Sel_datetrace(char *reply, long date, long trace)
{
EXEC SQL BEGIN DECLARE SECTION;
long plat_date; /*hp*/
long plat_trace; /*hp*/
EXEC SQL END DECLARE SECTION;
plat_date = date;
plat_trace = trace;
EXEC SQL DECLARE sel_epcc_trlog_datetrace CURSOR FOR SELECT *FROM bdepcc.epcc_trlog WHERE plat_date = : plat_date and plat_trace = : plat_trace;
if (sqlca.sqlcode)
{
APPLOG("E", "declare error %d", sqlca.sqlcode);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
EXEC SQL OPEN sel_epcc_trlog_datetrace;
if (sqlca.sqlcode)
{
APPLOG("E", "open epcc_trlog error %d", sqlca.sqlcode);
strcpy(reply, "D102");
return sqlca.sqlcode;
}
return 0;
}
方法二 使用oracle的动态SQLDA, pc程序不用改了，业务程序要传入变参进来
参见oracle的官网:（用google浏览器可以网页翻译，来回转换看基本准确)
12_2:https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpcc/Oracle-dynamic-SQL.html#GUID-56E41F14-669F-4CFD-8672-601EE3EDAD62
看不懂的可以用chrome打开，然后来回点翻译对照着看。关于方法四，他有一个官方的例子，代码基本如下，写的非常不好，冗长而且编译不通过，有的函数参数个数都不对（主动要求去IOE的节奏）
官网例子程序
/*******************************************************************
Sample Program 10: Dynamic SQL Method 4
This program connects you to ORACLE using your username and
password, then prompts you for a SQL statement. You can enter
any legal SQL statement. Use regular SQL syntax, not embedded SQL.
Your statement will be processed. If it is a query, the rows
fetched are displayed.
You can enter multiline statements. The limit is 1023 characters.
This sample program only processes up to MAX_ITEMS bind variables and
MAX_ITEMS select-list items. MAX_ITEMS is #defined to be 40.
*******************************************************************/
#include <stdio.h>
#include <string.h>
#include <setjmp.h>
#include <sqlda.h>
#include <stdlib.h>
#include <sqlcpr.h>
/* Maximum number of select-list items or bind variables. */
#define MAX_ITEMS 40
/* Maximum lengths of the _names_ of the
select-list items or indicator variables. */
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30
#ifndef NULL
#define NULL 0
#endif
/* Prototypes */
#if defined(__STDC__)
void sql_error(void);
int oracle_connect(void);
int alloc_descriptors(int, int, int);
int get_dyn_statement(void);
void set_bind_variables(void);
void process_select_list(void);
void help(void);
#else
void sql_error(/*_ void _*/);
int oracle_connect(/*_ void _*/);
int alloc_descriptors(/*_ int, int, int _*/);
int get_dyn_statement(/* void _*/);
void set_bind_variables(/*_ void -*/);
void process_select_list(/*_ void _*/);
void help(/*_ void _*/);
#endif
char *dml_commands[] = {"SELECT", "select", "INSERT", "insert",
"UPDATE", "update", "DELETE", "delete"};
EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlca;
EXEC SQL BEGIN DECLARE SECTION;
char dyn_statement[1024];
EXEC SQL VAR dyn_statement IS STRING(1024);
EXEC SQL END DECLARE SECTION;
SQLDA *bind_dp;
SQLDA *select_dp;
/* Define a buffer to hold longjmp state info. */
jmp_buf jmp_continue;
/* A global flag for the error routine. */
int parse_flag = 0;
void main()
{
int i;
/* Connect to the database. */
if (oracle_connect() != 0)
exit(1);
/* Allocate memory for the select and bind descriptors. */
if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
exit(1);
/* Process SQL statements. */
for (;;)
{
(void) setjmp(jmp_continue);
/* Get the statement. Break on "exit". */
if (get_dyn_statement() != 0)
break;
/* Prepare the statement and declare a cursor. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
parse_flag = 1; /* Set a flag for sql_error(). */
EXEC SQL PREPARE S FROM :dyn_statement;
parse_flag = 0; /* Unset the flag. */
EXEC SQL DECLARE C CURSOR FOR S;
/* Set the bind variables for any placeholders in the
SQL statement. */
set_bind_variables();
/* Open the cursor and execute the statement.
* If the statement is not a query (SELECT), the
* statement processing is completed after the
* OPEN.
*/
EXEC SQL OPEN C USING DESCRIPTOR bind_dp;
/* Call the function that processes the select-list.
* If the statement is not a query, this function
* just returns, doing nothing.
*/
process_select_list();
/* Tell user how many rows processed. */
for (i = 0; i < 8; i++)
{
if (strncmp(dyn_statement, dml_commands[i], 6) == 0)
{
printf("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],
sqlca.sqlerrd[2] == 1 ? '\0' : 's');
break;
}
}
} /* end of for(;;) statement-processing loop */
/* When done, free the memory allocated for
pointers in the bind and select descriptors. */
for (i = 0; i < MAX_ITEMS; i++)
{
if (bind_dp->V[i] != (char *) 0)
free(bind_dp->V[i]);
free(bind_dp->I[i]); /* MAX_ITEMS were allocated. */
if (select_dp->V[i] != (char *) 0)
free(select_dp->V[i]);
free(select_dp->I[i]); /* MAX_ITEMS were allocated. */
}
/* Free space used by the descriptors themselves. */
SQLSQLDAFree( SQL_SINGLE_RCTX, bind_dp);
SQLSQLDAFree( SQL_SINGLE_RCTX, select_dp);
EXEC SQL WHENEVER SQLERROR CONTINUE;
/* Close the cursor. */
EXEC SQL CLOSE C;
EXEC SQL COMMIT WORK RELEASE;
puts("\nHave a good day!\n");
EXEC SQL WHENEVER SQLERROR DO sql_error();
return;
}
int oracle_connect()
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[128];
VARCHAR password[32];
EXEC SQL END DECLARE SECTION;
printf("\nusername: ");
fgets((char *) username.arr, sizeof username.arr, stdin);
username.arr[strlen((char *) username.arr)-1] = '\0';
username.len = (unsigned short)strlen((char *) username.arr);
printf("password: ");
fgets((char *) password.arr, sizeof password.arr, stdin);
password.arr[strlen((char *) password.arr) - 1] = '\0';
password.len = (unsigned short)strlen((char *) password.arr);
EXEC SQL WHENEVER SQLERROR GOTO connect_error;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user %s.\n", username.arr);
return 0;
connect_error:
fprintf(stderr, "Cannot connect to ORACLE as user %s\n", username.arr);
return -1;
}
/*
* Allocate the BIND and SELECT descriptors using SQLSQLDAAlloc().
* Also allocate the pointers to indicator variables
* in each descriptor. The pointers to the actual bind
* variables and the select-list items are realloc'ed in
* the set_bind_variables() or process_select_list()
* routines. This routine allocates 1 byte for select_dp->V[i]
* and bind_dp->V[i], so the realloc will work correctly.
*/
alloc_descriptors(size, max_vname_len, max_iname_len)
int size;
int max_vname_len;
int max_iname_len;
{
int i;
/*
* The first SQLSQLDAAlloc parameter is the runtime context.
* The second parameter determines the maximum number of
* array elements in each variable in the descriptor. In
* other words, it determines the maximum number of bind
* variables or select-list items in the SQL statement.
*
* The third parameter determines the maximum length of
* strings used to hold the names of select-list items
* or placeholders. The maximum length of column
* names in ORACLE is 30, but you can allocate more or less
* as needed.
*
* The fourth parameter determines the maximum length of
* strings used to hold the names of any indicator
* variables. To follow ORACLE standards, the maximum
* length of these should be 30. But, you can allocate
* more or less as needed.
*/
if ((bind_dp =
SQLSQLDAAlloc(SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
(SQLDA *) 0)
{
fprintf(stderr,
"Cannot allocate memory for bind descriptor.");
return -1; /* Have to exit in this case. */
}
if ((select_dp =
SQLSQLDAAlloc (SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
(SQLDA *) 0)
{
fprintf(stderr,
"Cannot allocate memory for select descriptor.");
return -1;
}
select_dp->N = MAX_ITEMS;
/* Allocate the pointers to the indicator variables, and the
actual data. */
for (i = 0; i < MAX_ITEMS; i++) {
bind_dp->I[i] = (short *) malloc(sizeof (short));
select_dp->I[i] = (short *) malloc(sizeof(short));
bind_dp->V[i] = (char *) malloc(1);
select_dp->V[i] = (char *) malloc(1);
}
return 0;
}
int get_dyn_statement()
{
char *cp, linebuf[256];
int iter, plsql;
for (plsql = 0, iter = 1; ;)
{
if (iter == 1)
{
printf("\nSQL> ");
dyn_statement[0] = '\0';
}
fgets(linebuf, sizeof linebuf, stdin);
cp = strrchr(linebuf, '\n');
if (cp && cp != linebuf)
*cp = ' ';
else if (cp == linebuf)
continue;
if ((strncmp(linebuf, "EXIT", 4) == 0) ||
(strncmp(linebuf, "exit", 4) == 0))
{
return -1;
}
else if (linebuf[0] == '?' ||
(strncmp(linebuf, "HELP", 4) == 0) ||
(strncmp(linebuf, "help", 4) == 0))
{
help();
iter = 1;
continue;
}
if (strstr(linebuf, "BEGIN") ||
(strstr(linebuf, "begin")))
{
plsql = 1;
}
strcat(dyn_statement, linebuf);
if ((plsql && (cp = strrchr(dyn_statement, '/'))) ||
(!plsql && (cp = strrchr(dyn_statement, ';'))))
{
*cp = '\0';
break;
}
else
{
iter++;
printf("%3d ", iter);
}
}
return 0;
}
void set_bind_variables()
{
int i, n;
char bind_var[64];
/* Describe any bind variables (input host variables) */
EXEC SQL WHENEVER SQLERROR DO sql_error();
bind_dp->N = MAX_ITEMS; /* Initialize count of array elements. */
EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
/* If F is negative, there were more bind variables
than originally allocated by SQLSQLDAAlloc(). */
if (bind_dp->F < 0)
{
printf ("\nToo many bind variables (%d), maximum is %d\n.",
-bind_dp->F, MAX_ITEMS);
return;
}
/* Set the maximum number of array elements in the
descriptor to the number found. */
bind_dp->N = bind_dp->F;
/* Get the value of each bind variable as a
* character string.
*
* C[i] contains the length of the bind variable
* name used in the SQL statement.
* S[i] contains the actual name of the bind variable
* used in the SQL statement.
*
* L[i] will contain the length of the data value
* entered.
*
* V[i] will contain the address of the data value
* entered.
*
* T[i] is always set to 1 because in this sample program
* data values for all bind variables are entered
* as character strings.
* ORACLE converts to the table value from CHAR.
*
* I[i] will point to the indicator value, which is
* set to -1 when the bind variable value is "null".
*/
for (i = 0; i < bind_dp->F; i++)
{
printf ("\nEnter value for bind variable %.*s: ",
(int)bind_dp->C[i], bind_dp->S[i]);
fgets(bind_var, sizeof bind_var, stdin);
/* Get length and remove the new line character. */
n = strlen(bind_var) - 1;
/* Set it in the descriptor. */
bind_dp->L[i] = n;
/* (re-)allocate the buffer for the value.
SQLSQLDAAlloc() reserves a pointer location for
V[i] but does not allocate the full space for
the pointer. */
bind_dp->V[i] = (char *) realloc(bind_dp->V[i],
(bind_dp->L[i] + 1));
/* And copy it in. */
strncpy(bind_dp->V[i], bind_var, n);
/* Set the indicator variable's value. */
if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||
(strncmp(bind_dp->V[i], "null", 4) == 0))
*bind_dp->I[i] = -1;
else
*bind_dp->I[i] = 0;
/* Set the bind datatype to 1 for CHAR. */
bind_dp->T[i] = 1;
}
return;
}
void process_select_list()
{
int i, null_ok, precision, scale;
if ((strncmp(dyn_statement, "SELECT", 6) != 0) &&
(strncmp(dyn_statement, "select", 6) != 0))
{
select_dp->F = 0;
return;
}
/* If the SQL statement is a SELECT, describe the
select-list items. The DESCRIBE function returns
their names, datatypes, lengths (including precision
and scale), and NULL/NOT NULL statuses. */
select_dp->N = MAX_ITEMS;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
/* If F is negative, there were more select-list
items than originally allocated by SQLSQLDAAlloc(). */
if (select_dp->F < 0)
{
printf ("\nToo many select-list items (%d), maximum is %d\n",
-(select_dp->F), MAX_ITEMS);
return;
}
/* Set the maximum number of array elements in the
descriptor to the number found. */
select_dp->N = select_dp->F;
/* Allocate storage for each select-list item.
SQLNumberPrecV6() is used to extract precision and scale
from the length (select_dp->L[i]).
sqlcolumnNullCheck() is used to reset the high-order bit of
the datatype and to check whether the column
is NOT NULL.
CHAR datatypes have length, but zero precision and
scale. The length is defined at CREATE time.
NUMBER datatypes have precision and scale only if
defined at CREATE time. If the column
definition was just NUMBER, the precision
and scale are zero, and you must allocate
the required maximum length.
DATE datatypes return a length of 7 if the default
format is used. This should be increased to
9 to store the actual date character string.
If you use the TO_CHAR function, the maximum
length could be 75, but will probably be less
(you can see the effects of this in SQL*Plus).
ROWID datatype always returns a fixed length of 18 if
coerced to CHAR.
LONG and
LONG RAW datatypes return a length of 0 (zero),
so you need to set a maximum. In this example,
it is 240 characters.
*/
printf ("\n");
for (i = 0; i < select_dp->F; i++)
{
char title[MAX_VNAME_LEN];
/* Turn off high-order bit of datatype (in this example,
it does not matter if the column is NOT NULL). */
SQLColumnNullCheck ((unsigned short *)&(select_dp->T[i]),
(unsigned short *)&(select_dp->T[i]), &null_ok);
switch (select_dp->T[i])
{
case 1 : /* CHAR datatype: no change in length
needed, except possibly for TO_CHAR
conversions (not handled here). */
break;
case 2 : /* NUMBER datatype: use SQLNumberPrecV6() to
extract precision and scale. */
SQLNumberPrecV6( SQL_SINGLE_RCTX,
(unsigned long *)&(select_dp->L[i]), &precision, &scale);
/* Allow for maximum size of NUMBER. */
if (precision == 0) precision = 40;
/* Also allow for decimal point and
possible sign. */
/* convert NUMBER datatype to FLOAT if scale > 0,
INT otherwise. */
if (scale > 0)
select_dp->L[i] = sizeof(float);
else
select_dp->L[i] = sizeof(int);
break;
case 8 : /* LONG datatype */
select_dp->L[i] = 240;
break;
case 11 : /* ROWID datatype */
select_dp->L[i] = 18;
break;
case 12 : /* DATE datatype */
select_dp->L[i] = 9;
break;
case 23 : /* RAW datatype */
break;
case 24 : /* LONG RAW datatype */
select_dp->L[i] = 240;
break;
}
/* Allocate space for the select-list data values.
SQLSQLDAAlloc() reserves a pointer location for
V[i] but does not allocate the full space for
the pointer. */
if (select_dp->T[i] != 2)
select_dp->V[i] = (char *) realloc(select_dp->V[i],
select_dp->L[i] + 1);
else
select_dp->V[i] = (char *) realloc(select_dp->V[i],
select_dp->L[i]);
/* Print column headings, right-justifying number
column headings. */
/* Copy to temporary buffer in case name is null-terminated */
memset(title, ' ', MAX_VNAME_LEN);
strncpy(title, select_dp->S[i], select_dp->C[i]);
if (select_dp->T[i] == 2)
if (scale > 0)
printf ("%.*s ", select_dp->L[i]+3, title);
else
printf ("%.*s ", select_dp->L[i], title);
else
printf("%-.*s ", select_dp->L[i], title);
/* Coerce ALL datatypes except for LONG RAW and NUMBER to
character. */
if (select_dp->T[i] != 24 && select_dp->T[i] != 2)
select_dp->T[i] = 1;
/* Coerce the datatypes of NUMBERs to float or int depending on
the scale. */
if (select_dp->T[i] == 2)
if (scale > 0)
select_dp->T[i] = 4; /* float */
else
select_dp->T[i] = 3; /* int */
}
printf ("\n\n");
/* FETCH each row selected and print the column values. */
EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
for (;;)
{
EXEC SQL FETCH C USING DESCRIPTOR select_dp;
/* Since each variable returned has been coerced to a
character string, int, or float very little processing
is required here. This routine just prints out the
values on the terminal. */
for (i = 0; i < select_dp->F; i++)
{
if (*select_dp->I[i] < 0)
if (select_dp->T[i] == 4)
printf ("%-*c ",(int)select_dp->L[i]+3, ' ');
else
printf ("%-*c ",(int)select_dp->L[i], ' ');
else
if (select_dp->T[i] == 3) /* int datatype */
printf ("%*d ", (int)select_dp->L[i],
*(int *)select_dp->V[i]);
else if (select_dp->T[i] == 4) /* float datatype */
printf ("%*.2f ", (int)select_dp->L[i],
*(float *)select_dp->V[i]);
else /* character string */
printf ("%-*.*s ", (int)select_dp->L[i],
(int)select_dp->L[i], select_dp->V[i]);
}
printf ("\n");
}
end_select_loop:
return;
}
void help()
{
puts("\n\nEnter a SQL statement or a PL/SQL block at the SQL> prompt.");
puts("Statements can be continued over several lines, except");
puts("within string literals.");
puts("Terminate a SQL statement with a semicolon.");
puts("Terminate a PL/SQL block (which can contain embedded semicolons)");
puts("with a slash (/).");
puts("Typing \"exit\" (no semicolon needed) exits the program.");
puts("You typed \"?\" or \"help\" to get this message.\n\n");
}
void sql_error()
{
/* ORACLE error handler */
printf ("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);
if (parse_flag)
printf
("Parse error at character offset %d in SQL statement.\n",
sqlca.sqlerrd[4]);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK;
longjmp(jmp_continue, 1);
}
转换后自己写的程序
参考网上的例子，自己编写了一个pc程序，可以正常运行，oracle还一顿用malloc加relloc,这种炫技的技巧还增加了程序的不稳定性。
malloc和relloc部分已经被我注释掉了，改用栈空间来增加效率和防止内存溢出。
#include "kernel/syspub.h"
EXEC SQL INCLUDE sqlca.h;
#include "epcc/tabincl/epcc_trlog_c.h"
EXEC SQL INCLUDE epcc/tabincl/epcc_trlog.h;
/*选择列表项或绑定变量的最大数量。*/
EXEC SQL INCLUDE sqlda.h;
#define MAX_ITEMS 10
/* _names_的最大长度
选择列表项或指示符变量。*/
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30
/* 主机变量值的长度 */
#define MAX_VVALUE_LEN 100
/************************
声明查询游标--网联三要素
************************/
int Epcc_trlog_Dec_Sel_nphlist(char *reply, char * epcc_trace, long plat_date)
{
EXEC SQL BEGIN DECLARE SECTION;
char comm[1000];
EXEC SQL END DECLARE SECTION;
/*** sqlda 初始化 ***/
short i_item[MAX_ITEMS];
char v_item[MAX_ITEMS][MAX_VVALUE_LEN];
memset( i_item, 0x00, sizeof( i_item));
memset( v_item, 0x00, sizeof( v_item));
SQLDA *bind_dp = NULL;
if ((bind_dp = SQLSQLDAAlloc(0, MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL)
{
APPLOG("E", "Cannot allocate memory for bind descriptor.");
strcpy(reply, "D101");
return sqlca.sqlcode;
}
/* 为绑定变量和分配内存 */
int i = 0;
for (i = 0; i < MAX_ITEMS; i++) {
bind_dp->I[i] = i_item[i];
bind_dp->V[i] = v_item[i];
}
printf("三要素数据-[%s][%ld]\n", epcc_trace, plat_date);
sprintf( comm, "SELECT * FROM bdepcc.epcc_trlog WHERE epcc_trace = :epcc_trace and plat_date = :plat_date");
EXEC SQL PREPARE epcc_trlog_nphlist_sel FROM :comm;
if (sqlca.sqlcode)
{
APPLOG("E", "prepare error %d", sqlca.sqlcode);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
EXEC SQL DECLARE sel_epcc_trlog_nphlist CURSOR FOR epcc_trlog_nphlist_sel;
if (sqlca.sqlcode)
{
APPLOG("E", "declare error %d", sqlca.sqlcode);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
/* 设置绑定变量初始值为MAX_ITEMS */
bind_dp->N = MAX_ITEMS;
EXEC SQL DESCRIBE BIND VARIABLES FOR epcc_trlog_nphlist_sel INTO bind_dp;
/* BIND会寻找绑定变量，将个数赋值给bind_dp->F,如果小于0,则说明绑定变量个数太多 */
printf("bind_dp->F:[%d]\n", bind_dp->F);
if (bind_dp->F < 0)
{
APPLOG("E", "Too many bind variables (%d), maximum is %d\n.", bind_dp->F, MAX_ITEMS);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
bind_dp->N = bind_dp->F;
/* Get the value of each bind variable as a
* character string.
*
* C[i] contains the length of the bind variable
* name used in the SQL statement.
* S[i] contains the actual name of the bind variable
* used in the SQL statement.
*
* L[i] will contain the length of the data value
* entered.
*
* V[i] will contain the address of the data value
* entered.
*
* T[i] is always set to 1 because in this sample program
* data values for all bind variables are entered
* as character strings.
* ORACLE converts to the table value from CHAR.
*
* I[i] will point to the indicator value, which is
* set to -1 when the bind variable value is "null".
*/
i = 0;
bind_dp->C[i] = strlen("epcc_trace");
strcpy( bind_dp->S[i], "epcc_trace");
bind_dp->L[i] = strlen(epcc_trace);
/*重复调整内存大小*/
/*
bind_dp->V[i] = (char *) realloc(bind_dp->V[i], (bind_dp->L[i] + 1));
*/
memset( bind_dp->V[i], 0x00, bind_dp->L[i] + 1 );
strncpy(bind_dp->V[i], epcc_trace, bind_dp->L[i]);
/* Set the indicator variable's value. */
if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) || (strncmp(bind_dp->V[i], "null", 4) == 0))
*bind_dp->I[i] = -1;
else
*bind_dp->I[i] = 0;
bind_dp->T[i] = 1;
i = 1;
bind_dp->C[i] = strlen("plat_date");
strcpy( bind_dp->S[i], "plat_date");
char tmp[9];
memset( tmp, 0x00, sizeof(tmp));
sprintf( tmp, "%08ld", plat_date);
bind_dp->L[i] = strlen(tmp);
/*
bind_dp->V[i] = (char *) realloc(bind_dp->V[i], (bind_dp->L[i] + 1));
*/
memset( bind_dp->V[i], 0x00, bind_dp->L[i] + 1 );
strncpy(bind_dp->V[i], tmp, bind_dp->L[i]);
/* Set the indicator variable's value. */
if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) || (strncmp(bind_dp->V[i], "null", 4) == 0))
*bind_dp->I[i] = -1;
else
*bind_dp->I[i] = 0;
printf("----plat_date--[%s][%s][%ld]-strlen[%ld]\n", bind_dp->V[i], tmp, plat_date, bind_dp->L[i], strlen(bind_dp->V[i]));
bind_dp->T[i] = 1;/*NUMBER*/
EXEC SQL OPEN sel_epcc_trlog_nphlist USING DESCRIPTOR bind_dp;;
if (sqlca.sqlcode)
{
APPLOG("E", "open epcc_trlog error %d", sqlca.sqlcode);
strcpy(reply, "D102");
return sqlca.sqlcode;
}
/*
for (i = 0; i < MAX_ITEMS; i++)
{
if (bind_dp->V[i] != (char *) 0)
free(bind_dp->V[i]);
free(bind_dp->I[i]); * MAX_ITEMS were allocated. *
}
*/
/* Free space used by the descriptors themselves. */
SQLSQLDAFree( 0, bind_dp);
return 0;
}
/*查找*/
int Epcc_trlog_Fet_Sel_nphlist(struct epcc_trlog_c *epcc_trlog_c, char *reply)
{
EXEC SQL BEGIN DECLARE SECTION;
struct epcc_trlog epcc_trlog;
EXEC SQL END DECLARE SECTION;
int ret;
memset(&epcc_trlog, 0x00, sizeof(struct epcc_trlog_c));
EXEC SQL FETCH sel_epcc_trlog_nphlist INTO : epcc_trlog;
if (sqlca.sqlcode && sqlca.sqlcode != 1403)
{
APPLOG("E", "fetch epcc_trlog error %d", sqlca.sqlcode);
strcpy(reply, "D103");
return sqlca.sqlcode;
}
else if (sqlca.sqlcode == 1403)
{
strcpy(reply, "D104");
return 100;
}
memcpy(epcc_trlog_c, &epcc_trlog, sizeof(struct epcc_trlog_c));
return 0;
}
/*关闭*/
int Epcc_trlog_Clo_Sel_nphlist()
{
EXEC SQL CLOSE sel_epcc_trlog_nphlist;
return 0;
}
/* 单笔查询 */
int Epcc_trlog_Sel_nphlist(char *reply, struct epcc_trlog_c *epcc_trlog_c, char *epcc_trace, long plat_date)
{
int ret;
ret = Epcc_trlog_Dec_Sel_nphlist(reply, epcc_trace, plat_date);
if (ret)
{
Epcc_trlog_Clo_Sel_nphlist();
return ret;
}
ret = Epcc_trlog_Fet_Sel_nphlist(epcc_trlog_c, reply);
if (ret)
{
Epcc_trlog_Clo_Sel_nphlist();
return ret;
}
Epcc_trlog_Clo_Sel_nphlist();
return 0;
}
上面的程序没有写完，参数都是写死的，但是主要为了验证oracle方法4的可行性，从测试结果来看，是可行的，因此加工了一下pc程序，改成变参输入。
这样业务程序还是要改，但是pc程序只要改一次就行了，具体代码如下：
#include "kernel/syspub.h"
EXEC SQL INCLUDE sqlca.h;
#include "epcc/tabincl/epcc_trlog_c.h"
EXEC SQL INCLUDE epcc/tabincl/epcc_trlog.h;
EXEC SQL INCLUDE sqlda.h;
/* 选择绑定变量的最大数量,特殊的表查询可以修改这个值 */
#define MAX_ITEMS 10
/* 指示变量的最大长度 */
#define MAX_VNAME_LEN 30
/* 绑定变量的最大长度 */
#define MAX_INAME_LEN 30
/* 列的值最大长度 */
#define MAX_VVALUE_LEN 100
/************************
声明查询游标
plat_date=20190101 and (plat_trace = 100 or plat_trace > 500)
ret = Epcc_trlog_Dec_Sel_Cnd( Mxip_rc, "ASC",
"epcc_trace","=", epcc_trace, "and", "epcc_date","=", epcc_date);
ret = Epcc_trlog_Dec_Sel_Cnd( Mxip_rc, "epcc_trace=:epcc_trace and plat_date=:plat_date",
"char", epcc_trace, "long", plat_date);
************************/
int Epcc_trlog_Dec_Sel_Cnd(char *reply, char *fmtstr, ...)
{
EXEC SQL BEGIN DECLARE SECTION;
char comm[1000];
EXEC SQL END DECLARE SECTION;
va_list ap;
va_start(ap, fmtstr);
/*** sqlda域 初始化 ***/
SQLDA *bind_dp = NULL;
if ((bind_dp = SQLSQLDAAlloc(0, MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL)
{
APPLOG("E", "Cannot allocate memory for bind descriptor.");
strcpy(reply, "D101");
return sqlca.sqlcode;
}
/* 为宿主变量和指示变量分配内存 */
short i_item[MAX_ITEMS];
char v_item[MAX_ITEMS][MAX_VVALUE_LEN];
memset( i_item, 0x00, sizeof( i_item));
memset( v_item, 0x00, sizeof( v_item));
int i = 0;
for (i = 0; i < MAX_ITEMS; i++) {
bind_dp->I[i] = &i_item[i];
bind_dp->V[i] = v_item[i];
}
sprintf( comm, "SELECT * FROM bdepcc.epcc_trlog WHERE %s", fmtstr);
EXEC SQL PREPARE epcc_trlog_cnd_sel FROM :comm;
if (sqlca.sqlcode)
{
APPLOG("E", "prepare error %d", sqlca.sqlcode);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
EXEC SQL DECLARE sel_epcc_trlog_cnd CURSOR FOR epcc_trlog_cnd_sel;
if (sqlca.sqlcode)
{
APPLOG("E", "declare error %d", sqlca.sqlcode);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
/* 设置绑定变量初始值为MAX_ITEMS */
bind_dp->N = MAX_ITEMS;
/* BIND会寻找绑定变量，将个数赋值给bind_dp->F,如果小于0,则说明绑定变量个数太多 */
EXEC SQL DESCRIBE BIND VARIABLES FOR epcc_trlog_cnd_sel INTO bind_dp;
printf("bind_dp->F:[%d]\n", bind_dp->F);
if (bind_dp->F < 0)
{
APPLOG("E", "Too many bind variables (%d), maximum is %d\n.", bind_dp->F, MAX_ITEMS);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
bind_dp->N = bind_dp->F;
char * type=0;
char * s_value=NULL;
int i_value =0;
long l_value =0;
float f_value =0;
double d_value =0;
for ( i = 0; i < bind_dp->N; i++)
{
type = va_arg(ap, char *);
if( strcmp(type, "char") == 0) /* 字符型 */
{
s_value = va_arg(ap, char *);
bind_dp->L[i] = strlen(s_value);
memset( bind_dp->V[i], 0x00, bind_dp->L[i] + 1 );
strncpy(bind_dp->V[i], s_value, bind_dp->L[i]);
}
else if( strcmp(type, "int") == 0) /* 整形 */
{
i_value = va_arg(ap, int);
char tmp[9];
memset( tmp, 0x00, sizeof(tmp));
sprintf( tmp, "%08ld", i_value);
bind_dp->L[i] = strlen(tmp);
}
else if( strcmp(type, "long") == 0) /* 整形 */
{
l_value = va_arg(ap, int);
char tmp[9];
memset( tmp, 0x00, sizeof(tmp));
sprintf( tmp, "%08ld", l_value);
bind_dp->L[i] = strlen(tmp);
}
else if( strncmp(type, "float", 5) == 0) /* 浮点型 */
{
f_value = va_arg(ap, int);
char tmp[20];
memset( tmp, 0x00, sizeof(tmp));
sprintf( tmp, "%.2lf", f_value);
bind_dp->L[i] = strlen(tmp);
}
else if( strncmp(type, "double", 6) == 0) /* 双精度浮点型 */
{
d_value = va_arg(ap, int);
char tmp[20];
memset( tmp, 0x00, sizeof(tmp));
sprintf( tmp, "%.2lf", d_value);
bind_dp->L[i] = strlen(tmp);
}
memset( bind_dp->V[i], 0x00, bind_dp->L[i] + 1 );
strncpy(bind_dp->V[i], tmp, bind_dp->L[i]);
bind_dp->T[i] = 1;
}
va_end(ap);
EXEC SQL OPEN sel_epcc_trlog_cnd USING DESCRIPTOR bind_dp;;
if (sqlca.sqlcode)
{
APPLOG("E", "open epcc_trlog error %d", sqlca.sqlcode);
strcpy(reply, "D102");
return sqlca.sqlcode;
}
/* Free space used by the descriptors themselves. */
SQLSQLDAFree( 0, bind_dp);
return 0;
}
/*查找*/
int Epcc_trlog_Fet_Sel_Cnd(struct epcc_trlog_c *epcc_trlog_c, char *reply)
{
EXEC SQL BEGIN DECLARE SECTION;
struct epcc_trlog epcc_trlog;
EXEC SQL END DECLARE SECTION;
int ret;
memset(&epcc_trlog, 0x00, sizeof(struct epcc_trlog_c));
EXEC SQL FETCH sel_epcc_trlog_cnd INTO : epcc_trlog;
if (sqlca.sqlcode && sqlca.sqlcode != 1403)
{
APPLOG("E", "fetch epcc_trlog error %d", sqlca.sqlcode);
strcpy(reply, "D103");
return sqlca.sqlcode;
}
else if (sqlca.sqlcode == 1403)
{
strcpy(reply, "D104");
return 100;
}
memcpy(epcc_trlog_c, &epcc_trlog, sizeof(struct epcc_trlog_c));
return 0;
}
/*关闭*/
int Epcc_trlog_Clo_Sel_Cnd()
{
EXEC SQL CLOSE sel_epcc_trlog_cnd;
return 0;
}
/* 单笔查询 */
int Epcc_trlog_Sel_Cnd(char *reply, struct epcc_trlog_c *epcc_trlog_c, char *epcc_trace, long plat_date)
{
int ret;
ret = Epcc_trlog_Dec_Sel_Cnd(reply, epcc_trace, plat_date);
if (ret)
{
Epcc_trlog_Clo_Sel_Cnd();
return ret;
}
ret = Epcc_trlog_Fet_Sel_Cnd(epcc_trlog_c, reply);
if (ret)
{
Epcc_trlog_Clo_Sel_Cnd();
return ret;
}
Epcc_trlog_Clo_Sel_Cnd();
return 0;
}
方法三 业务程序不改，重组硬解析sql+使用oracle方法s：
考虑到修改业务程序工作量太大，基本不现实，因此在方法二的基础上，增加了一个不太好的硬解析sql转换成绑定变量sql的函数。
目前主要解决=号情况，可以解决99%的场景，特殊场景可以人工修改pc程序或者结合方法1和方法2来做。
修改后的代码如下，最终再按这个代码样式，修改生成pc的模板即可。
换了一张表，因为出差docker的环境没有这张表，但是原理一致，而且还发现了一个char和varchar的问题。
#include "kernel/syspub.h"
EXEC SQL INCLUDE sqlca.h;
#include "kernel/tabincl/xip_8583conv_c.h"
EXEC SQL INCLUDE kernel / tabincl / xip_8583conv.h;
EXEC SQL INCLUDE sqlda.h;
/* 默认最大值,特殊情况调整 */
#define MAX_ITEMS 20
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30
#define MAX_VVALUE_LEN 100
static int sXipSqlSoftParse( char * in_sql, char * out_sql, char ** arr, int arr_len);
/************************
声明查询游标
************************/
int Xip_8583conv_Dec_Sel_Cnd(char *reply, char *fmtstr, ...)
{
EXEC SQL BEGIN DECLARE SECTION;
char comm[1000];
EXEC SQL END DECLARE SECTION;
char wherelist[800];
va_list ap;
va_start(ap, fmtstr);
vsprintf(wherelist, fmtstr, ap);
va_end(ap);
short i_item[MAX_ITEMS];
char v_item[MAX_ITEMS][MAX_VVALUE_LEN];
memset(i_item, 0x00, sizeof(i_item));
memset(v_item, 0x00, sizeof(v_item));
char para_wherelist[800];
int num = 0;
num = sXipSqlSoftParse(wherelist, para_wherelist, v_item, MAX_VVALUE_LEN);
if (num < 0)
{
APPLOG("E", "解析SQL异常[%d],请检查输入的sql语句!!!", num);
strcpy(reply, "D101");
return -1;
}
else if (num > 0)
{
SQLDA *bind_dp = NULL;
if ((bind_dp = SQLSQLDAAlloc(0, MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == NULL)
{
APPLOG("E", "Cannot allocate memory for bind descriptor.");
strcpy(reply, "D101");
return -1;
}
sprintf(comm, "SELECT * FROM stdxip.xip_8583conv WHERE %s", para_wherelist);
EXEC SQL PREPARE xip_8583conv_sel FROM : comm;
if (sqlca.sqlcode)
{
XIPLOG("E", "prepare error %d", sqlca.sqlcode);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
EXEC SQL DECLARE sel_xip_8583conv CURSOR FOR xip_8583conv_sel;
if (sqlca.sqlcode)
{
XIPLOG("E", "declare error %d", sqlca.sqlcode);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
bind_dp->N = MAX_ITEMS;
EXEC SQL DESCRIBE BIND VARIABLES FOR xip_8583conv_sel INTO bind_dp;
if (bind_dp->F < 0)
{
APPLOG("E", "绑定变量的数量[%d]超过了最大数量[%d]\n.", bind_dp->F, MAX_ITEMS);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
bind_dp->N = bind_dp->F;
if (num != bind_dp->N)
{
APPLOG("E", "绑定变量的数量[%d]与解析值[%d]不符.\n", num, bind_dp->F);
strcpy(reply, "D101");
return -1;
}
int i = 0;
for (i = 0; i < bind_dp->N; i++)
{
bind_dp->I[i] = &i_item[i];
*bind_dp->I[i] = 0;
bind_dp->V[i] = v_item[i];
bind_dp->L[i] = strlen(bind_dp->V[i]);
bind_dp->T[i] = 96;
printf("[%s]--[%s]\n", bind_dp->S[i],bind_dp->V[i]);
}
EXEC SQL OPEN sel_xip_8583conv USING DESCRIPTOR bind_dp;
if (sqlca.sqlcode)
{
APPLOG("E", "open xip_8583conv error %d", sqlca.sqlcode);
strcpy(reply, "D102");
return sqlca.sqlcode;
}
SQLSQLDAFree(0, bind_dp);
return 0;
}
sprintf(comm, "SELECT * FROM stdxip.xip_8583conv WHERE %s", wherelist);
EXEC SQL PREPARE xip_8583conv_sel FROM : comm;
if (sqlca.sqlcode)
{
XIPLOG("E", "prepare error %d", sqlca.sqlcode);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
EXEC SQL DECLARE sel_xip_8583conv CURSOR FOR xip_8583conv_sel;
if (sqlca.sqlcode)
{
XIPLOG("E", "declare error %d", sqlca.sqlcode);
strcpy(reply, "D101");
return sqlca.sqlcode;
}
EXEC SQL OPEN sel_xip_8583conv;
if (sqlca.sqlcode)
{
XIPLOG("E", "open xip_8583conv error %d", sqlca.sqlcode);
strcpy(reply, "D102");
return sqlca.sqlcode;
}
return 0;
}
/************************
读取查询游标
************************/
int Xip_8583conv_Fet_Sel_Cnd(struct xip_8583conv_c *xip_8583conv_c, char *reply)
{
EXEC SQL BEGIN DECLARE SECTION;
struct xip_8583conv xip_8583conv;
EXEC SQL END DECLARE SECTION;
int ret;
memset(&xip_8583conv, 0x00, sizeof(struct xip_8583conv_c));
EXEC SQL FETCH sel_xip_8583conv INTO : xip_8583conv;
if (sqlca.sqlcode && sqlca.sqlcode != 1403)
{
XIPLOG("E", "fetch xip_8583conv error %d", sqlca.sqlcode);
strcpy(reply, "D103");
return sqlca.sqlcode;
}
else if (sqlca.sqlcode == 1403)
{
return 100;
}
memcpy(xip_8583conv_c, &xip_8583conv, sizeof(struct xip_8583conv_c));
return 0;
}
/************************
关闭查询游标
************************/
int Xip_8583conv_Clo_Sel_Cnd()
{
EXEC SQL CLOSE sel_xip_8583conv;
return 0;
}
/************************
单笔查询函数
************************/
int Xip_8583conv_Sel_Cnd(char *reply, struct xip_8583conv_c *xip_8583conv_c, char *fmtstr, ...)
{
int ret;
char wherelist[800];
va_list ap;
va_start(ap, fmtstr);
vsprintf(wherelist, fmtstr, ap);
va_end(ap);
ret = Xip_8583conv_Dec_Sel_Cnd(reply, "%s", wherelist);
if (ret)
{
return ret;
}
ret = Xip_8583conv_Fet_Sel_Cnd(xip_8583conv_c, reply);
if (ret)
{
return ret;
}
Xip_8583conv_Clo_Sel_Cnd();
return 0;
}
static int sXipSqlSoftParse( char * in_sql, char * out_sql, char ** arr, int arr_len)
{
/** 暂时就支持等号 **/
int num = 0;
char col[32];
char * value= NULL;
char * p = NULL;
char * p_start = in_sql;
char * p_end = NULL;
out_sql[0] = '\0';
/** 校验过滤 **/
if( strstr(in_sql, ">") != NULL
|| strstr(in_sql, "<") != NULL
|| strstr(in_sql, "in") != NULL
|| strstr(in_sql, "or ") != NULL
|| strstr(in_sql, "(") != NULL
|| strstr(in_sql, ")") != NULL
)
{
printf("软解析暂不支持该种查询条件\n");
return 0;
}
if( arr_len == 0)
{
printf("列的长度不能为空!!!\n");
return -5;
}
/** 解析 **/
while( p_start != NULL && (p_end = strstr(p_start, "=")) != NULL)
{
memset( col, 0x00, sizeof( col));
/** 取列名 **/
memcpy( col, p_start, p_end - p_start);
strtrim(col);
if( p_start != in_sql)
sprintf(out_sql+strlen(out_sql), " and ");
/** 移动p_start,开始下一个,跳过and **/
p_start = strstr(p_end+1, "and");
if( p_start == NULL)
p_start = strstr(p_end+1, "AND");
if( p_start != NULL)
p_start += 3;
/* 特殊情况处理 1=1情况 */
if( strcmp(col, "1") == 0)
{
sprintf(out_sql+strlen(out_sql), " 1=1 ");
continue;
}
else
{
sprintf(out_sql+strlen(out_sql), " %s=:%s ", col, col);
}
/** 取绑定变量的值 **/
value = (char *)arr + num*arr_len;
if( p_start == NULL)
memcpy( value, p_end + 1, (in_sql + strlen(in_sql)) - (p_end + 1));
else
memcpy( value, p_end + 1, (p_start - 3) - (p_end + 1));
strtrim(value);
if( value[0] == '\'')
{
memmove(value, value+1, strlen(value) - 1 );
p = strstr(value, "\'");
if( p == NULL)
return -10;
value[p-value]=0x00;
}
else
{
p = strstr(value, " ");
if( p != NULL)
value[p-value]=0x00;
}
num++;
}
/** 处理 order **/
if( p_end != NULL)
p = strstr( p_end +1 , "order");
if( p == NULL)
p = strstr( p_end +1 , "ORDER");
if( p != NULL)
sprintf(out_sql+strlen(out_sql), "%s", p);
APPLOG("D", "%d个绑定变量的SQL:[%s]", num, out_sql);
return num;
}
两个头文件的内容如下：
#ifndef __xip_8583conv_c_h
#define __xip_8583conv_c_h
struct xip_8583conv_c /*渠道8583格式报文体转换表*/
{
char xip_id[11]; /*渠道标识*/
char xip_convflag[2]; /*转换标志(I:转入O:转出)*/
char xip_convlevel[2]; /*转换级别(C:渠道级转换T:交易级转换)*/
char xip_trcode[11]; /*交易代码*/
char xip_datacode[7]; /*数据编码*/
long xip_seq; /*顺序号*/
char xip_datatype[2]; /*数据类型(0:字符1:整型2:长整型3:单精度浮点型4:双精度浮点型5:金额型)*/
long xip_datalen; /*数据长度*/
long xip_datadec; /*数据小数位数*/
char xip_dbcode[7]; /*平台数据总线数据编码*/
char xip_fmtconv[21]; /*数据格式转换*/
char xip_cvfunclib[21]; /*数据转换函数库*/
char xip_convfunc[21]; /*数据转换函数*/
char xip_desc[51]; /*数据编码说明*/
};
int Xip_8583conv_Dec_Sel(char *reply, char *fmtstr, ... );
int Xip_8583conv_Fet_Sel(struct xip_8583conv_c *xip_8583conv_c, char *reply);
int Xip_8583conv_Clo_Sel( );
int Xip_8583conv_Dec_Upd(char *reply, char *fmtstr, ... );
int Xip_8583conv_Fet_Upd(struct xip_8583conv_c *xip_8583conv_c, char *reply);
int Xip_8583conv_Upd_Upd(struct xip_8583conv_c xip_8583conv_c, char *reply);
int Xip_8583conv_Del_Upd(char *reply);
int Xip_8583conv_Clo_Upd( );
int Xip_8583conv_Ins(struct xip_8583conv_c xip_8583conv_c, char *reply);
int Xip_8583conv_Sel(char *reply,struct xip_8583conv_c *xip_8583conv_c,char *fmtstr, ...);
#endif
#ifndef __xip_8583conv_h
#define __xip_8583conv_h
EXEC SQL BEGIN DECLARE SECTION;
struct xip_8583conv /*渠道8583格式报文体转换表*/
{
char xip_id[11]; /*渠道标识*/
char xip_convflag[2]; /*转换标志(I:转入O:转出)*/
char xip_convlevel[2]; /*转换级别(C:渠道级转换T:交易级转换)*/
char xip_trcode[11]; /*交易代码*/
char xip_datacode[7]; /*数据编码*/
long xip_seq; /*顺序号*/
char xip_datatype[2]; /*数据类型(0:字符1:整型2:长整型3:单精度浮点型4:双精度浮点型5:金额型)*/
long xip_datalen; /*数据长度*/
long xip_datadec; /*数据小数位数*/
char xip_dbcode[7]; /*平台数据总线数据编码*/
char xip_fmtconv[21]; /*数据格式转换*/
char xip_cvfunclib[21]; /*数据转换函数库*/
char xip_convfunc[21]; /*数据转换函数*/
char xip_desc[51]; /*数据编码说明*/
};
EXEC SQL END DECLARE SECTION;
#endif
select for update的原理和纯select一模一样，不再赘述。
问题点：
varchar2和char的问题
bind_dp->T[i] = 96;
上述代码是针对char类型表的写法，如果查询的字段都是varchar，可以改成1.
char在用绑定变量查询的时候，需要补齐空格，否则查不出来。
遇到这个问题一度绝望，开始考虑了三个办法：
- 生产所有表都改成varchar，并压缩空格,具体命令例子如下：
alter table stdxip.xip_8583conv modify (xip_id varchar(10));
update stdxip.xip_8583conv set xip_id=trim(xip_id) where 1=1;
但是全生产这么做，一定要停机处理，而且个别流水表的数据量上亿，耗时太长。
2.头文件有对应的表结构体，增加名字存储和对应结构体字段的对应关系。
这块并未修改，但是思路基本同上，因为C语言没有typeof(不能判断变量本身类型)，结构体的变量名称也没办法获取。
计划采用三维char数组的模式进行：column[][][]
第一位是位移：比如0，1，2，3，4
第二位是变量的名字：比如"xip_id"
第三位是对应结构体的变量名：比如"xip_8583conv.xipid"
还好头文件和pc可以自动生成，这个也可以尝试一下。
3.每次使用这个列之前，先去oracle的 user_table中查一下这个列的信息
会产生极大的性能损耗以及oracle的交互变多，考虑到业务量，不能采用。
最后不甘心，又去官网仔细阅读说明，有如下的一段说明，犹如久旱逢甘露：
image.png
赶紧赋值bind_dp->T[i] = 96;问题解决。
bind_dp的参数设置问题
由于select的结果集不是变量，因此select_list部分没有仔细研究。但是真多bind_pd的参数，总结如下：
struct SQLDA
{
long N; /* Descriptor size in number of entries */
char **V; Ptr to Arr of addresses of main variables */
int *L; /* Ptr to Arr of lengths of buffers */
short *T; /* Ptr to Arr of types of buffers */
short **I; * Ptr to Arr of addresses of indicator vars */
long F; /* Number of variables found by DESCRIBE */
char **S; /* Ptr to Arr of variable name pointers */
short *M; /* Ptr to Arr of max lengths of var. names */
short *C; * Ptr to Arr of current lengths of var. names */
char **X; /* Ptr to Arr of ind. var. name pointers */
short *Y; /* Ptr to Arr of max lengths of ind. var. names */
short *Z; /* Ptr to Arr of cur lengths of ind. var. names */
};
N：是解析出来的绑定变量的个数
**V: 是指向绑定变量值的指针，可以malloc也可以指向栈中的数组。
L: 是变量的长度，字符串就是strlen来计算，其他类型根据类型判断。
T: 变量的类型，在查询条件的时候，可以都设置为字符串，本次使用的是96，具体参见上面的oracle列表。
I: 没设置，没仔细检查有什么用，但是也是个指针，必须要malloc或者赋值栈空间的值。
F：oracle找到的绑定变量的值，一般赋值给N
S：绑定变量名称，不设置会自动赋值。
M：没用到。
C：绑定变量名称长度，不设置会自动赋值。
X,Y,Z没用到。
