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 语句,不能使用动态语句来拼,上述写法中是错误的。
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;
}
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;
}
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 ;
}
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 ;
}