БД 5

#include <sqlca.h>
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <sys/time.h>
#include <unistd.h>
#include <sqltypes.h>

void error_func(char* message) {
    printf("Message: %s\n", message);
    printf("Error code: %d\n", sqlca.sqlcode);
    printf("Error message: %s\n", sqlca.sqlerrm.sqlerrmc);
}

int main() {
    EXEC SQL begin declare section;
    char queryFirst[]  = "SELECT ROUND(SUM(t.sves), 2) sum                "
                         "FROM (SELECT spj.n_post, AVG(spj.kol*p.ves) sves"
                         "      FROM spj                                  "
                         "      JOIN p ON p.n_det = spj.n_det             "
                         "      GROUP BY spj.n_post                       "
                         "      ORDER BY 1) t                             ";

    char querySecond[] = "SELECT t.n_izd, j.name, j.town, t.sves                    "
                         "FROM (SELECT spj.n_izd, ROUND (AVG (spj.kol*p.ves),2) sves"
                         "      FROM spj                                            "
                         "      JOIN p ON p.n_det = spj.n_det                       "
                         "      WHERE spj.n_det = ?                                 "
                         "      GROUP BY spj.n_izd) t                               "
                         "JOIN j ON t.n_izd = j.n_izd                               "
                         "ORDER BY n_izd                                            ";

    char queryThird[]  = "SELECT a.n_izd, a.sves, b.oves, ROUND(a.sves*100.0/b.oves,2) proc "
                         "FROM (SELECT spj.n_izd, ROUND(SUM(spj.kol), 2) sves               "
                         "      FROM spj                                                    "
                         "      WHERE spj.n_det = ?                                         "
                         "      GROUP BY spj.n_izd )a                                       "
                         "CROSS JOIN (SELECT SUM (spj.kol) oves                             "
                         "            FROM spj                                              "
                         "            WHERE spj.n_det = ?) b                                ";

    char arg[20];  // аргумент для запросов

    char n_izd[5];
    char name[40];
    char town[40];
    float sum;
    float sves;
    float oves;
    float proc;

    EXEC SQL end declare section;


    /*********************\
    |* Подключаемся к БД *|
    \*********************/
    exec sql connect to students@fpm2.ami.nstu.ru user "pmi-b6103" using "juIppOw";
    if(sqlca.sqlcode != 0) {
        error_func("Authentication failed");
        return 1;
    }

    /*******************************************************\
    |* Устанавливаем схему, в которой будем искать таблицу *|
    \*******************************************************/
    EXEC SQL set search_path to pmib6103;

    if(sqlca.sqlcode != 0) {
        printf("Error\n");
        return 2;
    }


    int num= 1 ;
    while(num!=0) {
        printf("\nEnter query number: 1,2,3 or 0 for exit\n");
        scanf("%d", &num);   // ввод номера запроса

        switch(num) {
            case 1:
                printf("First query\n");

                EXEC SQL prepare query1 FROM :queryFirst;
                if(sqlca.sqlcode < 0) {
                    error_func("Preparing error in first query");
                    break;
                }

                Exec SQL execute query1 INTO :sum;
                if(sqlca.sqlcode < 0) {
                    error_func("Execution error in first query");
                    break;
                } else {
                    printf("Result query: %.2f\n", sum);
                }
                break;

            case 2:
                printf("Second query\n");

                printf("\nEnter arg: ");
                scanf("%s", arg);

                EXEC SQL prepare query2 FROM :querySecond;
                if(sqlca.sqlcode < 0) {
                    error_func("Preparing error in second query");
                    break;
                }

                EXEC SQL declare curs2 cursor for query2; // определение курсора для нашего запроса
                EXEC SQL open curs2 using :arg;

                if(sqlca.sqlcode < 0) {
                    error_func("Opening cursor error in second query");
                    break;
                }

                EXEC SQL fetch curs2 INTO :n_izd, :name, :town, :sves;// результат запроса - множество значений
                while(sqlca.sqlcode==0) {
                    printf("%s  %s  %s  %.2f\n", n_izd, name, town, sves);
                    EXEC SQL fetch curs2 INTO :n_izd, :name, :town, :sves;
                }

                if (sqlca.sqlcode != 100) error_func("Internal error");
                break;

            case 3:
                printf("Third query\n");

                printf("\nEnter arg: ");
                scanf("%s", arg);

                EXEC SQL prepare query3 FROM :queryThird;
                if(sqlca.sqlcode < 0) {
                    error_func("Preparing error in third query");
                    break;
                }

                EXEC SQL declare curs3 cursor for query3; // определение курсора для нашего запроса
                EXEC SQL open curs3 using :arg, :arg;

                if(sqlca.sqlcode < 0) {
                    error_func("Opening cursor error in third query");
                    break;
                }

                EXEC SQL fetch curs3 INTO :n_izd, :sves, :oves, :proc;// результат запроса - множество значений
                while(sqlca.sqlcode==0) {
                    printf("%.2s  %.2f  %.2f  %.2f\n", n_izd, sves, oves, proc);
                    EXEC SQL fetch curs3 INTO :n_izd, :sves, :oves, :proc;
                }

                if (sqlca.sqlcode != 100) error_func("Internal error");
                break;
        }
    }
    return 0;
}