Pro*C 第一次练习

 ***********************************

proc test.pc

gcc -o test test.c $ORACLE_HOME/lib/libclntsh.so

./test

************************************

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE ORACA;
EXEC SQL INCLUDE SQLDA;

EXEC SQL INCLUDE SQLCPR;

EXEC SQL BEGIN DECLARE SECTION;
    VARCHAR usr[20], pass[20], server[20];
EXEC SQL END   DECLARE SECTION;

void
update()
{
   
    EXEC SQL BEGIN DECLARE SECTION;
        int id;
        char name[20];
        char job[20];
    EXEC SQL END   DECLARE SECTION;
   
    char slID[8];
    printf("\nplease input the employee number\n");
    gets(slID);
    id=atoi(slID);
   
    printf("\nplease input the employee name\n");
    gets(name);
   
    printf("\nplease input the employee job\n");
    gets(job);
   
    printf("\nthe id is %d, the name is %s, the job is %s",id, name, job);
   
    EXEC SQL UPDATE emp
         SET emp_name=:name, emp_job=:job
         WHERE emp_id=:id;
   
    printf("\n\nemployee %d update.\n",id);
    EXEC SQL COMMIT;
   
    return;
}

void
delete(void)
{
    EXEC SQL BEGIN DECLARE SECTION;
        int id;
    EXEC SQL END DECLARE SECTION;
   
    char clID[8];
   
    printf("\nplease input the id\n");
    gets(clID);
    id = atoi(clID);
   
    EXEC SQL DELETE
         FROM EMP
         WHERE emp_id=:id;
   
    printf("\nemployee %d is deleted",id);
    EXEC SQL COMMIT;
    return;
}

void
insert(void)
{
    EXEC SQL BEGIN DECLARE SECTION;
        int id;
        char name[20];
        char job[20];
    EXEC SQL END   DECLARE SECTION;
   
    char slID[8];
    printf("\nplease input the employee number\n");
    gets(slID);
    id=atoi(slID);
   
    printf("\nplease input the employee name\n");
    gets(name);
   
    printf("\nplease input the employee job\n");
    gets(job);
   
    printf("\nthe name is %s, the job is %s",name, job);

    EXEC SQL INSERT INTO EMP(emp_id,emp_name,emp_job)
         VALUES(:id,:name,:job);
   
    printf("\nemployee %d inserted\n",id);
    EXEC SQL COMMIT;
    return;
}

void
Print(char *s)
{
    printf("%s\n",s);
}

void
 selection(void)
{   
    char slID[8];
    int ilID;
    printf("\nplease input the employee number\n");
    gets(slID);
    ilID=atoi(slID);
    printf("ID=%d\n",ilID);
   
    EXEC SQL BEGIN DECLARE SECTION;
        int id;
        char name[20];
        char job[20];
    EXEC SQL END   DECLARE SECTION;
   
    EXEC SQL SELECT emp_id,emp_name,emp_job
         INTO :id,name,job
         FROM EMP
         WHERE emp_id=:ilID;

    printf("\n+++the empid   = %d\n",id);
    printf("\n+++the empname = %s\n",name);
    printf("\n+++the empjob  = %s\n",job);
}

int
oracle_connect(void)
{   
    strcpy(usr.arr, "ceda");
    usr.len=(unsigned short)strlen((char *)usr.arr);
   
    strcpy(pass.arr, "ceda");
    pass.len=(unsigned short)strlen((char *)pass.arr);
   
    //strcpy(server.arr, "satsvm");
    //server.len=(unsigned short)strlen((char *)server);   
   
    EXEC SQL CONNECT :usr IDENTIFIED BY :pass;
   
    EXEC ORACLE OPTION (ORACA=YES);
    oraca.oradbgf=1;
    oraca.oracchf=1;
    oraca.orastxtf=3;

    printf("%d\n",sqlca.sqlcode);   

    if(sqlca.sqlcode == 0)
    {
        Print("db connection is ok");
    }
    else
    {
        Print("db connection is error");
    }   
   
    return 0;           
}

static int oracle_disconnect(void)
{   
    EXEC SQL commit work release;
    Print("db is disconnected");
}

int
main()
{
    char clchoice[5];
    //char choice;
    oracle_connect();
    while(1)
    {
        printf("\n************");
        printf("\n* 1.Select *");
        printf("\n* 2.Update *");
        printf("\n* 3.Insert *");
        printf("\n* 4.Delect *");
        printf("\n* 5.Exit   *");
        printf("\n************");
        printf("\nEnter choice: \n");
        gets(clchoice);
        //choice = getchar();       
       
        //switch(choice)
        switch(clchoice[0])
        {
            case '1':
                selection();
                break;
            case '2':
                update();
                break;
            case '3':
                insert();
                break;
            case '4':
                delete();
                break;
            case '5':
                Print("exit");
                break;
            default:
                printf("\n\ninvalid choice\n");
                break;   
        }
        //if(choice == '5')
        if(clchoice[0] == '5')
        {
            break;
        }   
       
    }   
    oracle_disconnect();
    return 0;
}