宿主数组变量实例

例 5-1

/*****************************************************************************
esql*C 使用宿主数组实例 *
****************************************************************************/
#include <stdio.h>
#include <stdlib.h>

void error()
{
	/* 对错误情况不予处理，以防止死循环*/
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	/* 输出错误信息*/
	sqlprint();
	EXEC SQL ROLLBACK WORK;
	exit(1);
}

int main()
{
	int n;
	long SQLCODE;
	/*声明宿主数组变量*/
	EXEC SQL BEGIN DECLARE SECTION;
	char hnum[10][4];
	char hname[10][21];
	char hcity[10][16];
	int hgrade[10];
	int ind_city[10];
	EXEC SQL END DECLARE SECTION;


	for (n = 0; n < 10; n++)
	{
		hnum[n][0] = '\0';
		hname[n][0] = '\0';
		hcity[n][0] = '\0';
		hgrade[n] = -1;
		ind_city[n] = -1;
	}

	EXEC SQL WHENEVER SQLERROR DO error();
	EXEC SQL WHENEVER NOT FOUND GOTO not_found;

	EXEC SQL CONNECT TO osrdb@localhost USER
	sysdba USING szoscar55;

	EXEC SQL WHENEVER SQLERROR CONTINUE;
	/*建表之前先删除已经存在的表*/
	EXEC SQL DROP TABLE employee;
	EXEC SQL WHENEVER SQLERROR do error();
	/* 创建表employee */
    EXEC SQL CREATE TABLE employee(
        empnum CHARACTER(3) PRIMARY KEY,
        empname CHAR(20),
        grade DECIMAL(4,0),
        city VARCHAR(15));
    if (SQLCODE == 0)
	{
		printf("表employee成功创建!\n");
	}
	
	EXEC SQL INSERT INTO employee VALUES('1','张三',10,'北京');
	EXEC SQL INSERT INTO employee VALUES('2','李四',15,'天津');
	EXEC SQL INSERT INTO employee VALUES('3','王五',8,'杭州');
	EXEC SQL INSERT INTO employee VALUES('4','宋六',20,'上海');
	EXEC SQL INSERT INTO employee VALUES('5','陆七',1,'南京');
	
	/*执行下面的SELECT语句有一个前提条件，表employee中的元组数不能大于10*/
	EXEC SQL SELECT empnum,empname, grade, city INTO :hnum, :hname, :hgrade, :hcity:ind_city \
	FROM employee;

	for (n = 0; n < 10; n++)
	{
		if (hnum[n][0] == '\0')
		{
			break;
		}
		printf("%s, %s, %s,%d\n", hnum[n], hname[n], hcity[n], hgrade[n]);
	}

	EXEC SQL DROP TABLE employee;
	EXEC SQL COMMIT WORK;
	EXEC SQL DISCONNECT;
	exit(0);

	not_found:
		printf("表中没有数据了.\n");
		EXEC SQL DROP TABLE employee;
		EXEC SQL ROLLBACK WORK;
		EXEC SQL DISCONNECT;
		exit(1);
}