如何才能实现proc 执行动态的SQL.doc

如何才能实现proc 执行动态的SQLPROC 动态SQL的有四种方式方式－：直接执行SQL语句，SQL语句中不带绑定的变量，而且SQL语句不是查询语句方式二：执行SQL语句，带绑定变量，不是查询语句方法三：执行SQL语句，带绑定变量，可以是查询语句，需要使用游标方法四：利用SQLDA结构体，实现动态字段，动态参数等，该方法最为灵活方法－：EXEC SQL EXECUTE IMMEDIATE CREATE TABLE test (test_col VARCHAR2(4)); 方法二：strcpy(sqlstring, "DELETE FROM test WHERE test_col = :?"); EXEC SQL divPARE sqlproc FROM :sqlstring; EXEC SQL EXECUTE sqlproc USING :emp_number; 方法三：if(strcmp(tsospa1.event_table,"TSOSP26")==0) { strcpy(sqlstr,"select count(*) as count from "); strcat(sqlstr,tsospa1.event_table); strcat(sqlstr," where PRICE_SYS=:price_sys"); strcat(sqlstr," and PERIOD_NUM=:period_num"); } else { strcpy(sqlstr,"select count(*) as count from "); strcat(sqlstr,tsospa1.event_table); strcat(sqlstr," where PROD_CODE=:prode_code"); strcat(sqlstr," and PRICE_SYS=:price_sys"); strcat(sqlstr," and PERIOD_NUM=:period_num"); } EDLog(1,1,"sqlstr=%s",sqlstr); EXEC SQL PREPARE S FROM :sqlstr; EXEC SQL DECLARE C CURSOR FOR S; if(strcmp(tsospa1.event_table,"TSOSP26")==0) { EXEC SQL OPEN C USING :tsospa1.price_sys,est_PERIOD_NUM; } /*else if(strcmp(tsospa1.event_table,"TSOSP25")==0) { EXEC SQL OPEN C USING :tsospa1.price_sys,dest_PERIOD_NUM; }*/ else { EXEC SQL OPEN C USING :tsospa1.prod_code,:tsospa1.price_sys,dest_PERIOD_NUM; } for (fetchRowCount=0;;) { EXEC SQL FETCH C INTO :count; if(sqlca.sqlcode==1403) break; fetchRowCount++; EDLog(1,1,"Do"); EDLog(1,1,"count=%d",max_version_num); }第四种：#include #include #include #include #include #include /* 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. 。