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

printf("3: AVG_salary\n");

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

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

switch (i)

{

case 1:

{

a.add();

break;

}

case 2:

{

a.print();

break;

}

case 3:

{

a.AVG_salary();

break;

}

default: return(0);

}

}

}

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

/*  S005                                                                                          */

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

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

#include <stdlib.h>

#include <stdio.h>

EXEC SQL INCLUDE SQLCA;

char s??(30??);

class WORK

{

private:

EXEC SQL BEGIN DECLARE SECTION;

char firstname??(10??);

char lastname??(12??);

char address??(13??);

char phone??(8??);

char datetime??(23??);

long int salary;

short int identifier;

EXEC SQL END DECLARE SECTION;

public:

WORK();

~WORK();

void print(void);

void add(void);

void print_column(void);

};

WORK::WORK()

{

EXEC SQL BEGIN DECLARE SECTION;

struct {short len;

char s??(40??);

}grastr;

EXEC SQL END DECLARE SECTION;

EXEC SQL CREATE TABLE PIOPLE                                 

(FIRSTNAME  VARCHAR(9)   NOT NULL,            

LASTNAME   CHAR(11)     NOT NULL,            

ADDRESS    VARCHAR(12)  NOT NULL,                          

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);

} 

::strcpy(grastr.s,"GRANT SELECT ON PIOPLE TO DB2USER");

grastr.len=::strlen(grastr.s);

EXEC SQL EXECUTE IMMEDIATE :grastr;

if (sqlca.sqlcode != 0)

{

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

"grant select on PIOPLE, received %d\n",sqlca.sqlcode);

::exit(-1);

}  

}

void WORK::add(void)

{

EXEC SQL BEGIN DECLARE SECTION;

struct {char fn??(10??);

char ln??(12??);

char a??(13??);

char ph??(8??);

long s;

short i;

}d;

struct {short len;

char s??(100??);

}insstr;

EXEC SQL END DECLARE SECTION;

::printf("\nFirstname? "); ::gets(d.fn);

::printf("Lastname? "); ::gets(d.ln);

::printf("Address? "); ::gets(d.a);

::printf("Phone? "); ::gets(d.ph);

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

::printf("Identifier? "); ::gets(s); d.i=::atoi(s);

::strcpy(insstr.s,"INSERT INTO PIOPLE "

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

"VALUES (?,?,?,?,?,?)");

insstr.len=::strlen(insstr.s);

EXEC SQL PREPARE SENTENCE1 FROM :insstr;

if (sqlca.sqlcode != 0)

{

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

"prepare SENTENCE1, received %d\n",sqlca.sqlcode);

return;

}

EXEC SQL EXECUTE SENTENCE1 USING :d;

if (sqlca.sqlcode != 0)

{

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

"execute SENTENCE1, received %d\n",sqlca.sqlcode);

return;

}

EXEC SQL COMMIT;

}

void WORK::print(void)

{

EXEC SQL BEGIN DECLARE SECTION;

struct {short len;

char s??(80??);

}selstr;

EXEC SQL END DECLARE SECTION;

::strcpy(selstr.s,"SELECT "

"FIRSTNAME,LASTNAME,ADDRESS,PHONE,DATETIME,SALARY,IDENTIFIER "