Chinaunix
标题:
Proc问题, 如何使用动态语句创建Cursor
[打印本页]
作者:
kelvin
时间:
2006-09-27 17:26
标题:
Proc问题, 如何使用动态语句创建Cursor
方案一没问题
方案二总提示ora-1001错。请高手看看问题出在哪里?
#include <stdio.h>
//test.pc
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
int main(int argc, char* argv[])
{
EXEC SQL BEGIN DECLARE SECTION;
int cnt=0;
char username[20];
char password[20];
char *databasename = "GSM";
char v_tabname[30];
char sql[2000];
EXEC SQL END DECLARE SECTION;
strcpy(username,"SCOTT");
strcpy(password, "TIGER");
strcpy(sql, "select table_name from user_tables");
printf("sql=%s\n",sql);
EXEC SQL PREPARE stmt FROM :sql;
/*方案一*/
EXEC SQL DECLARE C CURSOR for select table_name from user_tables;
/*方案二*/
EXEC SQL DECLARE C CURSOR for stmt ;
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :databasename;
EXEC SQL SELECT count(*) into :cnt FROM dual;
printf("count of dual: %d\n", cnt);
EXEC SQL OPEN C;
while(1)
{
EXEC SQL FETCH C into :v_tabname;
if(sqlca.sqlcode ){
printf("sqlca.sqlcode=%d\n",sqlca.sqlcode);
break;
}
/*
if(sqlca.sqlcode < 0) {
perror("fetching data error");
EXEC SQL ROLLBACK WORK RELEASE;
exit(sqlca.sqlcode);
}
*/
printf("Table_name: %s\n", v_tabname);
}
EXEC SQL ROLLBACK WORK RELEASE;
}
复制代码
作者:
chen_gxing
时间:
2006-10-11 10:27
如果把方案二的语句,放在EXEC SQL OPEN C;之前,
是否有改善。
作者:
ketos
时间:
2006-10-11 16:10
--先连接数据库
--拼好SQL
sprintf(SqlStmt, "select BUSISN, SvcNum, KEYID, CustId, BUSITYPE,substr(KEYID,1,1) AreaID,deptid,to_char(optdate,'YYYYMMDD HH24:MI:SS') from BOSS.T_INTER_ASYBUSIINFO where RMKI='1' AND KEYID like '%s%%' order by optdate ",V_AreaID);
--OPEN CURSOR
EXEC SQL AT :bossDB DECLARE STA_AREASvcNum STATEMENT;
EXEC SQL PREPARE STA_AREASvcNum FROM :SqlStmt;
EXEC SQL DECLARE C_AREASvcNum CURSOR FOR STA_AREASvcNum;
EXEC SQL OPEN C_AREASvcNum;
欢迎光临 Chinaunix (http://bbs.chinaunix.net/)
Powered by Discuz! X3.2