DB2 – система управления реляционными базами данных. Заданное число упорядоченных столбцов и неупорядоченные строки, страница 23

EXEC SQL END DECLARE SECTION;

public:

WORK();

~WORK();

void print_phone (void);

void add(void);

};

WORK::WORK()

{

EXEC SQL CREATE TABLE PIOPLE                                 

(FIRSTNAME  VARCHAR(9)   NOT NULL,            

LASTNAME   CHAR(11)     NOT NULL,            

ADDRESS    VARCHAR(12),                         

PHONE      CHAR(7)      NOT NULL UNIQUE,     

DATETIME   TIMESTAMP    DEFAULT,             

SALARY     INTEGER      NOT NULL,            

IDENTIFIER SMALLINT     NOT NULL,             

CONSTRAINT CHECK_PHONE                            

CHECK(PHONE BETWEEN '210____' AND '299____'));

if (sqlca.sqlcode != 0)

{

::printf("ERROR - SQL code returned non-zero for "

"creation of PIOPLE, received %d\n",sqlca.sqlcode);

::exit(-1);

}

EXEC SQL CREATE UNIQUE INDEX PHONEX

ON PIOPLE(PHONE);

if (sqlca.sqlcode != 0)

{

::printf("ERROR - SQL code returned non-zero for "

"create unique index, received %d\n",sqlca.sqlcode);

::exit(-1);

}    

EXEC SQL ALTER TABLE PIOPLE

PRIMARY KEY(PHONE);

if (sqlca.sqlcode != 0)

{

::printf("ERROR - SQL code returned non-zero for "

"alter table PIOPLE, received %d\n",sqlca.sqlcode);

::exit(-1);

}  

}

void WORK::add(void)

{

::printf("\nFirstname? "); ::gets(firstname);

::printf("Lastname? "); ::gets(lastname);

::printf("Address (y/n)? "); ::gets(s);

if(::strcmp(s,"y")==0)

{indicator=0;

::printf("Address? "); ::gets(address);

}

else indicator=-1;

::printf("Phone? "); ::gets(phone);

::printf("Salary? "); ::gets(s); salary=::atoi(s);

::printf("Identifier? "); ::gets(s); identifier=::atoi(s);

EXEC SQL INSERT INTO PIOPLE

(FIRSTNAME,LASTNAME,ADDRESS,PHONE,SALARY,IDENTIFIER)

VALUES

(:firstname,:lastname,:address :indicator,:phone,:salary,:identifier);

if (sqlca.sqlcode != 0)

{

::printf("ERROR - SQL code returned non-zero for "

"insert into PIOPLE, received %d\n",sqlca.sqlcode);

return;

}

}

void WORK::print_phone(void)

{

EXEC SQL BEGIN DECLARE SECTION;

char ph??(8??);

EXEC SQL END DECLARE SECTION;

::printf("\nPhone? "); ::gets(ph);

EXEC SQL SELECT

FIRSTNAME,LASTNAME,ADDRESS,DATETIME,SALARY,IDENTIFIER

INTO

:firstname,:lastname,:address :indicator,:datetime,:salary,:identifier

FROM PIOPLE

WHERE PHONE = :ph;

if (sqlca.sqlcode != 0)

{

::printf("ERROR - SQL code returned non-zero for "

"select where phone, received %d\n",sqlca.sqlcode);

return;

}

::printf("\nFirstname Lastname    Address      "

"Datetime               Salary Ident");

::printf("\n========= =========== ============ "

"====================== ====== =====");

::printf("\n%-9s %-11s ",firstname,lastname);

if(indicator<0) ::printf("____________ ");

else ::printf("%-12s ",address);

::printf("%-22s %-6d %-5d\n\n",datetime,salary,identifier);

}

WORK::~WORK()

{

EXEC SQL DROP TABLE PIOPLE;

if (sqlca.sqlcode != 0)

{

::printf("ERROR - SQL code returned non-zero for "

"drop of PIOPLE received %d??\n",sqlca.sqlcode);

::exit(-1);

}

EXEC SQL COMMIT;

}

int main(void)

{

WORK a;

for(int i;;)

{printf("\n\n1: add\n");

printf("2: print_phone\n");

printf("OTHER NUMBER: exit\n? ");

gets(s);i=atoi(s);

switch (i)

{

case 1:

{

a.add();

break;

}

case 2:

{

a.print_phone();

break;

}

default: return(0);

}

}

}

/************************************************************/

/*   S002                                                                                                         */

/*   Пример использования курсора для доступа к более чем                 */

/*   одной строке таблицы. Оператор WHENEVER используется         */