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

最新推荐文章于 2025-04-18 20:11:50 发布

原创最新推荐文章于 2025-04-18 20:11:50 发布 · 1.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #编程 #c #sql #list #table

数据库技术专栏收录该内容

17 篇文章

订阅专栏

博客主要介绍了SQL中select into、using和for的用法。指出select into语句不能用动态语句拼接，表名动态时需用游标获取动态sql；还说明了for后对应int型变量，using后是结构数组，并给出了相应的代码示例及错误处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 ;
    }