指示数组变量实例

例 5-2

/*****************************************************************************
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 hbrchnum[10];
	int ind_grade[10];
	int ind_city[10];
	int ind_brchnum[10];
	EXEC SQL END DECLARE SECTION;

	/*
	初始化要插入的宿主数组变量和指示数组变量，其中员工编号从E7-E17，为了简单起见，员工的名字都是卢比，级别均未评定，都来自建德，子公司未定。*/

	for (n = 0; n < 10; n++)
	{
		hnum[n][0] = 'E';
		if (7 + n >= 10)
		{
			hnum[n][1] = '1';
			hnum[n][2] = 7 + n - 10 + '0';
			hnum[n][3] = '\0';
		}
		else
		{
			hnum[n][1] = 7 + n + '0';
			hnum[n][2] = '\0';
		}

		strcpy(hname[n], "卢比");
		strcpy(hcity[n], "建德") ;
		hgrade[n] = 5;
		hbrchnum[n] = 571;
		ind_brchnum[n] = -1;
		ind_grade[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),
		brchnum INT);
    if (SQLCODE == 0)
	{
		printf("表employee成功创建!\n");
	}	
	
	EXEC SQL INSERT INTO employee \
	VALUES(:hnum, :hname, :hgrade, :hcity, :hbrchnum);
	if (SQLCODE == 0)
	{
		printf("已经成功插入10个来自建德在杭州工作的员工的元组!\n");
	}
	
	/* 重置存放结构的宿主数组变量和指示数组变量 */

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

	/* 执行下面的SELECT语句有一个前提条件，即表employee中city值为"建德"的元组数不能大于10 */

	EXEC SQL SELECT empnum,empname, grade, city, brchnum \
	INTO :hnum, :hname, :hgrade:ind_grade, :hcity:ind_city, \
	:hbrchnum:ind_brchnum \
	FROM employee \
	WHERE city = '建德';

	/* ind_grade<0,表示对应的宿主数组元素为null*/

	for (n = 0; n < 10; n++) 
	{
		printf("员工信息#%d：%s, %s, %s,%d, %d\n", \
		n+1, hnum[n], hname[n], ind_city < 0? "null":hcity[n], \
		ind_grade < 0 ? -1 : hgrade[n], hbrchnum[n]);
	}

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

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

}