我是c++写的程序,ibm的aix操作系统,oracle 9i数据库。
我将一sql语句放到了一个表里,语句如下:
select count(*) from dBillCustDetail where id_no = :v1 and begin_time < round(sysdate,'month') and end_time >sysdate and detail_type = 'b' 我已经从数据库中取得了该sql语句,并赋给了字符变量v_detail我现在想执行这个语句,取得count(*)的值并赋给v_num变量,并根据v_num变量的值来执行不同的操作。可现在的问题是,我无法取得count(*)这个值。 long v_id_no=129387128;
EXEC SQL select conddetail into :v_detail from sshortmsgcond;printf("[%s]\n",v_detail);
EXEC SQL PREPARE sql_stmt FROM :v_detail;
EXEC SQL EXECUTE sql_stmt into :v_num using :v_id_no;
/*
EXEC SQL select count(*) into :v_num from dBillCustDetail where id_no = :v_id_no and begin_time < round(sysdate,'month') and end_time >sysdate and detail_type = 'b';
*/
if (SQLCODE !=0 && SQLCODE != 1403)
{
printf("[%d]\n",SQLCODE);}
}if (v_num>0) {
printf("插入短信\n");
}
而我直接执行被我注销掉的语句,就没有任何问题,请大家帮我看看,哪出了问题??
我将一sql语句放到了一个表里,语句如下:
select count(*) from dBillCustDetail where id_no = :v1 and begin_time < round(sysdate,'month') and end_time >sysdate and detail_type = 'b' 我已经从数据库中取得了该sql语句,并赋给了字符变量v_detail我现在想执行这个语句,取得count(*)的值并赋给v_num变量,并根据v_num变量的值来执行不同的操作。可现在的问题是,我无法取得count(*)这个值。 long v_id_no=129387128;
EXEC SQL select conddetail into :v_detail from sshortmsgcond;printf("[%s]\n",v_detail);
EXEC SQL PREPARE sql_stmt FROM :v_detail;
EXEC SQL EXECUTE sql_stmt into :v_num using :v_id_no;
/*
EXEC SQL select count(*) into :v_num from dBillCustDetail where id_no = :v_id_no and begin_time < round(sysdate,'month') and end_time >sysdate and detail_type = 'b';
*/
if (SQLCODE !=0 && SQLCODE != 1403)
{
printf("[%d]\n",SQLCODE);}
}if (v_num>0) {
printf("插入短信\n");
}
而我直接执行被我注销掉的语句,就没有任何问题,请大家帮我看看,哪出了问题??
用动态sql比较复杂,因为你用了select,所以prepare之后还要定义cursor
然后打开游标来读取。
你不能说你知道这个只返回一条记录就不用游标,而是说你用到了动态select的话就必须要用游标
此语句表示“预编译/执行”。此语句能够预编译一次而执行多次。语法为:
EXEC SQL PREPARE 〈语句名〉FROM:主变量;
EXEC SQL EXECUTE〈语句名〉[USING:替换主变量];
PREPARE语句做两件事:
(1) 预编译SQL语句;
(2) 给出SQL语句的语句名。
注意:
l SQL语句不能是查询语句;
l PREPARE和EXECUTE可包含主变量;
l PREPARE不能多次执行。
例如:
#define USERNAME “SCOTT”
#define PASSWORD “TIGER”
#include
EXEC SQL INCLUDE sqlca;
EXEC SQL BEGIN DECLARE SECTION;
Char * username=USERNAME;
Char * password=PASSWORD;
VARCHAR sqlstmt[80];
Int emp_number;
VARCHAR emp_name[15];
VARCHAR job[50];
EXEC SQL END DECLARE SECTION;
Main()
{
EXEC SQL WHENEVER SQLERROR GOTO :sqlerror;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
Sqlstmt.len=sprintf(sqlstmt.arr,”INSERT INTO EMP (EMPNO,ENAME,JOB,SAL)
VALUES(:V1,:V2,:V3,:V4)”);
Puts(sqlstmt.arr);
EXEC SQL PREPARE S FROM :sqlstmt;
For(;;)
{
printf(“\nenter employee number:”);
scanf(“%d”,&emp_number);
if (emp_number==0) break;
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);
printf(“\nenter employee salary:”);
scanf(“%f”,&salary);
}
EXEC SQL EXECUTE S USING :emp_number,:emp_name,:job,:salary;
}
3. FETCH语句和OPEN语句
FETCH语句和OPEN语句这组动态语句是对游标进行操作的,其执行过程如下:
PREPARE〈语句名〉FROM 〈主变量字符串〉;
DECLARE〈游标名〉FOR〈语句名〉;
OPEN 〈游标名〉[USING:替换变量1[,:替换变量变…]]
FETCH〈游标名〉INTO: 主变量1[,:主变量2…]
CLOSE〈游标名〉注意:
l SQL语句允许使用查询语句;
l SELECT子句中的列名不能动态改变,只能预置;
l WHERE和ORDER BY 子句可以动态改变条件。-------------------------所以
楼主要想取得某数据
不能这样:
EXEC SQL PREPARE sql_stmt FROM :v_detail;
EXEC SQL EXECUTE sql_stmt into :v_num using :v_id_no;
而应改为:
EXEC SQL PREPARE sql_stmt FROM :v_detail;
EXEC SQL DECLARE c_sql_stmt FOR sql_stmt;
EXEC SQL OPEN c_sql_stmt USING :v_id_no;
EXEC SQL FETCH c_sql_stmt INTO :v_num;
EXEC SQL CLOSE c_sql_stmt;