EXEC SQL SELECT name, age, city, INTO :name, :age, :city FROM student where (city <> 'Guangzhou') and (city <> 'Shanghai') and ... and (rownum <= 1)由于可能有多个city名字,不知如何处理
可以参考下面的方法: EXEC SQL SELECT name, age, city, INTO :name, :age, :city FROM student where city NOT EXIST(select city ...) and (rownum <= 1) 要求是,你能select city ...查出象Guangzhou, Shanghai...这样的城市名字
俺初学数据库,谢谢你的提议,google了一下游标的用法。觉得可以用一下的办法,不知道对不对,请帮忙看看。 还有想问下嵌套表是怎么回事。EXEC SQL BEGIN DECLARE SECTION; char hstmt[] = "SELECT name, age, city FROM student WHERE (city <> 'Guangzhou') and (city <> 'Shanghai')"; char name[20 + 1]; int age; char city[20 + 1]; EXEC SQL END DECLARE SECTION;EXEC SQL PREPARE stmt FROM :hstmt; EXEC SQL DECLARE cur CURSOR FROM stmt; EXEC SQL OPEN cur; EXEC SQL FETCH cur INTO :name, :age, :city; EXEC SQL CLOSE cur;
印象中这个不需要用 prepare语句,prepare语句是用来处理动态sql的。稍加修改:EXEC SQL BEGIN DECLARE SECTION; char name[20 + 1]; int age[20 + 1]; char city[20 + 1]; EXEC SQL END DECLARE SECTION;EXEC SQL DECLARE cur CURSOR for SELECT name, age, city FROM student WHERE (city <> 'Guangzhou') and (city <> 'Shanghai'); EXEC SQL OPEN cur; 开始循环fetch EXEC SQL FETCH cur INTO :name, :age, :city; 然后再循环中写退出条件:一般是sqlcode =100,数据没有找到的时候。 循环结束。EXEC SQL CLOSE cur;
我知兄弟是C/C++ 高手,不过你上面的SQL代码实在是不太好,请仔细看看4楼的建议:)
谢谢, 但是我的问题是我的SQL语句WHERE是需要根据参数改变的. WHERE (city <> 'Guangzhou') and (city <> 'Shanghai') 我需要根据函数传进来的参数char *cities[] 进行改变: 大致如下char hstmt[1024] = "SELECT name, age, city FROM student"; char where[1024]; char *cities[] = {"Guangzhou", "Shangehai", "Beijing", NULL}; char *ptr; int ret;if (cities[0] != NULL) { ptr = where; ret = sprintf(ptr, " WHERE ( city <> '%s') ", cities[0]); ptr += ret; for (i = 1; citis[i] != NULL; i++) { ret = sprintf(where, " AND ( city <> '%s' )", cities[i]); ptr += ret; } strcat(hstmt, where); } else { /* Nothing to do */ }
这样楼主就要用dynamic sql了,上面的语句主要部分是对的,但是 声明游标的语句不对,应该是EXEC SQL DECLARE cur CURSOR for stmt; fetch之后要做判断。
EXEC SQL SELECT name, age, city, INTO :name, :age, :city FROM student where city NOT EXIST(select city ...) and (rownum <= 1) 要求是,你能select city ...查出象Guangzhou, Shanghai...这样的城市名字
还有想问下嵌套表是怎么回事。EXEC SQL BEGIN DECLARE SECTION;
char hstmt[] = "SELECT name, age, city FROM student WHERE (city <> 'Guangzhou') and (city <> 'Shanghai')";
char name[20 + 1];
int age;
char city[20 + 1];
EXEC SQL END DECLARE SECTION;EXEC SQL PREPARE stmt FROM :hstmt;
EXEC SQL DECLARE cur CURSOR FROM stmt;
EXEC SQL OPEN cur;
EXEC SQL FETCH cur INTO :name, :age, :city;
EXEC SQL CLOSE cur;
char name[20 + 1];
int age[20 + 1];
char city[20 + 1];
EXEC SQL END DECLARE SECTION;EXEC SQL DECLARE cur CURSOR for SELECT name, age, city FROM student WHERE (city <> 'Guangzhou') and (city <> 'Shanghai');
EXEC SQL OPEN cur;
开始循环fetch
EXEC SQL FETCH cur INTO :name, :age, :city;
然后再循环中写退出条件:一般是sqlcode =100,数据没有找到的时候。
循环结束。EXEC SQL CLOSE cur;
我知兄弟是C/C++ 高手,不过你上面的SQL代码实在是不太好,请仔细看看4楼的建议:)
但是我的问题是我的SQL语句WHERE是需要根据参数改变的.
WHERE (city <> 'Guangzhou') and (city <> 'Shanghai')
我需要根据函数传进来的参数char *cities[] 进行改变:
大致如下char hstmt[1024] = "SELECT name, age, city FROM student";
char where[1024];
char *cities[] = {"Guangzhou", "Shangehai", "Beijing", NULL};
char *ptr;
int ret;if (cities[0] != NULL) {
ptr = where;
ret = sprintf(ptr, " WHERE ( city <> '%s') ", cities[0]);
ptr += ret;
for (i = 1; citis[i] != NULL; i++) {
ret = sprintf(where, " AND ( city <> '%s' )", cities[i]);
ptr += ret;
}
strcat(hstmt, where);
} else {
/* Nothing to do */
}
这样楼主就要用dynamic sql了,上面的语句主要部分是对的,但是
声明游标的语句不对,应该是EXEC SQL DECLARE cur CURSOR for stmt;
fetch之后要做判断。