数据库编程笔记（七） Pro*C: USING和SELECT INTO用法

1、select into的用法：
 sprintf(dynamicSql,"select count(*) INTO :b1 from mid_block_info_gsm");
 EXEC SQL EXECUTE IMMEDIATE :dynamicSql;
 if (sqlca.sqlcode != 0)
  {
   sprintf(err_list,"select count(*) from mid_block_info_ Error! Statement : %s,sqlca.sqlcode--[%d] ",dynamicSql,sqlca.sqlcode);
    WriteLogBlock(err_list);
    goto _failed;
  }
  
  对于select into 语句，不能使用动态语句来拼，上述写法中是错误的。
  

 EXEC SQL EXECUTE select count(*) INTO :icount from mid_block_info_gsm;
 if (sqlca.sqlcode != 0)
  {
   sprintf(err_list,"select count(*) from mid_block_info_ Error! Statement : %s,sqlca.sqlcode--[%d] ",dynamicSql,sqlca.sqlcode);
    WriteLogBlock(err_list);
    goto _failed;
  }
  
  对于表名也是动态的，则第二种也不能用，只能用游标获得动态sql,下列用法是正确的
  sprintf(dynamicSql,"select count(*) from %s",t_mid_block_info_user);
 EXEC SQL PREPARE block_sql FROM :dynamicSql;
 EXEC SQL DECLARE block_cur CURSOR for block_sql;
 EXEC SQL OPEN block_cur;
 if (sqlca.sqlcode != 0)
  {
   sprintf(err_list,"select count(*) from mid_block_info_ Error! Statement : %s ",dynamicSql);
    WriteLogBlock(err_list);
    goto _failed;
  } 

 EXEC SQL fetch block_cur into :icount;
   if (sqlca.sqlcode != 0)
  {
   sprintf(err_list,"select count(*) from mid_block_info_ Error! Statement : %s ",dynamicSql);
    WriteLogBlock(err_list);
    goto _failed;
  }

2、using和for的用法；for 后对应int型变量;using后是结构数组  
  strcpy(t_table_name[3],"account_info_gsm");
  uimIdx = 1000;
  sprintf(dynamic_account_gsm,"INSERT INTO %s(service_id,device_number,package_number,oper_flag,from_dept_no,to_dept_no,res_status,local_net,oper_date,memo) VALUES (to_number(:v1),:v2,:v3,:v4,:v5,:v6,:v7,:v8,to_date(:v9,'yyyymmdd hh24:mi:ss'),:v10)",t_table_name[3]
  EXEC SQL PREPARE gsm_account FROM :dynamic_account_gsm;
    EXEC SQL for :uimIdx EXECUTE gsm_account USING :inCdmaAccount; 
    if (sqlca.sqlcode < 0){
            sprintf(ora_code,"插入PPC号台帐入库出错ORA-%d",abs(sqlca.sqlcode));
            OraModule();
            return ;
    }