动态SQL小结

服务器的连接：

//connect to local database 连接服务器 szUsrId为用户名 szUsrPwd为密码 con_local 连接代名词 szSrv为数据源 结构为如 192.168.1.1;9000 ip加端口
 exec sql CONNECT :szUsrId IDENTIFIED BY :szUsrPwd at :con_local using :szSrv; 

 exec sql CONNECT :szUsrId2 IDENTIFIED BY :szUsrPwd2 at :con_remote using :szSrv2; 

 exec sql set close on endtran off;

 exec sql disconnect all;

SQL操作（sql循环游标提取数据事例）

// 说明SQL变量  

  exec sql begin declare section;
       CS_CHAR task_id[2048];
       CS_INT  task_ref_id;
       CS_INT  task_status;
   exec sql end declare section;

   memset(szSQLString,0,sizeof(szSQLString));
   sprintf(szSQLString,"select task_id,task_ref_id,task_status from aex_int_task where task_status = 3",szModule);
   exec sql at :con_local prepare mod_pre from :szSQLString;/*准备SQL语句*/// 用PREPARE语句分析当前的动态sql语句，语句名是mod_pre

   exec sql at :con_local declare mod_cursor cursor for mod_pre;//申明游标mod_pre的游标名为mod_cursor
   if(Check_Error(szSQLString,__LINE__,__FILE__)==1)
   {
        exec sql rollback;
        return -1;
   }
    exec sql at :con_local open mod_cursor; //打开游标
    if(Check_Error(szSQLString,__LINE__,__FILE__)==1)
   {
        exec sql rollback;
        return -1;
   }
   exec sql at :con_local fetch mod_cursor into :task_id,:task_ref_id, :task_status;//从游标中提取SQL所执行的数据
    while(sqlca.sqlcode == 0)//循环执行游标 循环提取数据
    {
         //数组处理 存放在内存数据空间
         strcpy(aex_int_task_List[aex_int_task_ListCount].task_id,task_id);
         aex_int_task_List[aex_int_task_ListCount].task_ref_id=task_ref_id;
         aex_int_task_List[aex_int_task_ListCount].task_status=task_status;
         WriteLog(__LINE__,"task_ref_id=%d,task_status=%d\n",task_ref_id,task_status);   
         aex_int_task_ListCount++;
         exec sql at :con_local fetch mod_cursor into :task_id,:task_ref_id, :task_status;
     }
    exec sql at :con_local close mod_cursor;/*关闭游标*/
    exec sql at :con_local deallocate cursor mod_cursor;/*销毁游标*/

SQL操作（sql提取数据事例）

     memset(szSQLString,0,sizeof(szSQLString));
     sprintf(szSQLString,"select isnull(sum(case result_flag when 1 then 1 else 0 end),0)  as sccuess, isnull(sum(case result_flag when 2 then 1 else 0 end),0)  as fail       from pub_cmd_result  where log_code='%s'  and result_module='aex'",str_task_id);
     exec sql at :con_remote prepare count_flag_pre from :szSQLString;
     if(Check_Error(szSQLString,__LINE__,__FILE__)==1)
     {
       exec sql at :con_remote rollback;
       return -1;
     }
     exec sql at :con_remote execute count_flag_pre into :successSQL,:failSQL;//获取成功数和失败数

     exec sql at :con_remote commit;//提交事务

SQL操作（sql获取数据参数事例）

// 说明SQL变量  
EXEC SQL BEGIN DECLARE SECTION;  
      char* username=USERNAME;  
      char* password=PASSWORD;  
      VARCHAR sqlstmt[80];  
      int emp_number;  
      VARCHAR emp_name[15];  
      VARCHAR job[50],job1[50],job2[50];  
      float salary;  
EXEC SQL END DECLARE SECTION;   
    //EXEC SQL CONNECT :username IDENTIFIED BY :password;   
    // 构造动态SQL语句  
    sqlstmt.len=sprintf(sqlstmt.arr,"INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)VALUES(:V1,:V2,:V3,:V4)");   
    // 用PREPARE语句分析当前的动态INSERT语句，语句名是S  
    EXEC SQL PREPARE S FROM :sqlstmt;  
    // 循环插表  
    for(;;)  
    {  
        printf("/nEnter employee number:");    scanf("%d",&emp_number);   
        printf("/nEnter employee name:");   scanf("%s",&emp_name.arr);  
        emp_name.len=strlen(emp_name.arr);  
        printf("/nEnter employee job:");     scanf("%s",&job.arr);     job.len=strlen(job.arr);  
        salary = 0; // With VC6, Missing this line will cause C Run-Time Error R6002.  
        printf("/nEnter salary:");     scanf("%f",&salary);  
        EXEC SQL EXECUTE S USING :emp_number,:emp_name,:job,:salary;   //获取参数
    }   

    EXEC SQL COMMIT RELEASE;   // 提交事务
    // 回滚事务，退出ORACLE  
    EXEC SQL WHENEVER SQLERROR CONTINUE;  
    EXEC SQL ROLLBACK RELEASE;