一个使用Pro*C实现增删改查的小例子

环境：

WinXPSP3 + VC6 + ORACLE 11g

步骤：

1）新建demo.pc，内容如下：

#include <stdio.h>// 全程说明段EXEC SQL BEGIN DECLARE SECTION;VARCHAR username[20];VARCHAR password[20];EXEC SQL END DECLARE SECTION;// 说明通讯区EXEC SQL INCLUDE sqlca;// 外部函数说明void insert(),update(),del(),query();main(){char operate[5];// 登录到oraclestrcpy(username.arr,"SCOTT"); // 用户名username.len=strlen(username.arr);strcpy(password.arr,"x"); // 密码password.len=strlen(password.arr);EXEC SQL WHENEVER SQLERROR GOTO logon_error;EXEC SQL CONNECT :username IDENTIFIED BY :password;printf("/nConnect to ORACLE as user: %s",username.arr);for(;;){printf("/n***********************");printf("/n*1.Query*");printf("/n*2.Update*");printf("/n*3.Insert*");printf("/n*4.Delete*");printf("/n*5.Exit*");printf("/n***********************");printf("/n Enter selection:/n");gets(operate);switch(operate[0]){case '1':query(); break;case '2':update(); break;case '3':insert(); break;case '4':del(); break;case '5':break;default :printf("/n/n invalid selection/n"); break;}if(operate[0]=='5')break;}// 结束处理 EXEC SQL COMMIT RELEASE;printf("/n/n very good !/n/n");exit(0);// 登录出错处理logon_error:printf("/n invalid username/password/n");printf("/n%.70s/n",sqlca.sqlerrm.sqlerrmc);EXEC SQL WHENEVER SQLERROR CONTINUE;EXEC SQL ROLLBACK RELEASE;exit(1);}// 更新处理void update(){EXEC SQL BEGIN DECLARE SECTION;int emp_number;VARCHAR emp_name[20];VARCHAR job[50];short ind_job;EXEC SQL END DECLARE SECTION;char empnum[8];printf("/n/nEnter employee number(press RETURN to abort):");gets(empnum);if(!strcmp(empnum,"")){printf("/n");return;}emp_number = atoi(empnum);EXEC SQL WHENEVER SQLERROR GOTO sqlerror;EXEC SQL WHENEVER NOT FOUND GOTO notfound;EXEC SQL SELECT ENAME,JOBINTO :emp_name,:job:ind_jobFROM EMPWHERE EMPNO = :emp_number;emp_name.arr[emp_name.len]='/0';switch(ind_job){case -1:strcpy(job.arr,"NULL");job.len=strlen(job.arr);break;case 0:job.arr[job.len]='/0';break;default:if(ind_job>0)printf("/n/nWARNING: Job truncated./n");break;}printf("/n/nNumber Employee Name Job/n");printf("----------------------------------/n");printf("%-9d%-15s%-8s/n",emp_number,emp_name.arr,job.arr);printf("/nEnter new employee name:");gets(emp_name.arr);emp_name.len=strlen(emp_name.arr);printf("/n/nEnter new job:");gets(job.arr);job.len=strlen(job.arr);ind_job=0;if(!strcmp(job.arr,""))ind_job=-1;EXEC SQL UPDATE EMPSET ENAME= :emp_name,JOB=:job:ind_jobWHERE EMPNO= :emp_number;printf("/n/nEmployee %d updated./n",emp_number);EXEC SQL COMMIT;return;notfound:printf("/n/nWARNING: Employee %d does not exist./n",emp_number);return;sqlerror:printf("/n%.70s/n",sqlca.sqlerrm.sqlerrmc);EXEC SQL WHENEVER SQLERROR CONTINUE;EXEC SQL ROLLBACK;exit(1);}// 插入处理void insert(){EXEC SQL BEGIN DECLARE SECTION;int emp_number;VARCHAR emp_name[20];VARCHAR job[20];short ind_job;EXEC SQL END DECLARE SECTION;char empnum[8];printf("/n/nEnter employee number(press RETURN to abort):");gets(empnum);if(!strcmp(empnum,"")){printf("/n");return;}emp_number = atoi(empnum);EXEC SQL WHENEVER SQLERROR GOTO sqlerror;EXEC SQL WHENEVER NOT FOUND GOTO resume;EXEC SQL SELECT ENAME INTO :emp_nameFROM EMPWHERE EMPNO = :emp_number;printf("/n/nWARNING: Employee %d alread exist./n",emp_number);return;resume:printf("/n/nEnter employee name:");gets(emp_name.arr);emp_name.len=strlen(emp_name.arr);printf("/n/nEnter job:");gets(job.arr);job.len=strlen(job.arr);ind_job=0;if(!strcmp(job.arr,""))ind_job = -1;EXEC SQL INSERT INTO EMP(EMPNO,ENAME,JOB)VALUES(:emp_number,:emp_name,:job:ind_job);printf("/n/nEmployee %d inserted. /n",emp_number);EXEC SQL COMMIT;return;sqlerror:printf("/n%.70s/n",sqlca.sqlerrm.sqlerrmc);EXEC SQL WHENEVER SQLERROR CONTINUE;EXEC SQL ROLLBACK;exit(1);}// 查询处理void query(){EXEC SQL BEGIN DECLARE SECTION;int emp_number;VARCHAR emp_name[20];VARCHAR job[20];short ind_job;EXEC SQL END DECLARE SECTION;char empnum[8];printf("/n/nEnter Employee number (press RETURN to abort):");gets(empnum);if(!strcmp(empnum,"")){printf("/n");return;}emp_number = atoi(empnum);EXEC SQL WHENEVER SQLERROR GOTO sqlerror;EXEC SQL WHENEVER NOT FOUND GOTO notfound;EXEC SQL SELECT ENAME,JOBINTO :emp_name,:job:ind_jobFROM EMPWHERE EMPNO = :emp_number;emp_name.arr[emp_name.len]='/0';switch(ind_job){case -1:strcpy(job.arr,"NULL");job.len=strlen(job.arr);break;case 0:job.arr[job.len]='/0';break;default:if(ind_job >0 )printf("/n/nWARNING: Job truncated./n");break;}printf("/n/nNumber Employee Name Job/n");printf("------------------------------------------/n");printf("%-9d%-15s%-8s/n",emp_number,emp_name.arr,job.arr);return;notfound:printf("/n/nWARING: Employee %d does not exist./n",emp_number);return;sqlerror:printf("/n%.70s/n",sqlca.sqlerrm.sqlerrmc);EXEC SQL WHENEVER SQLERROR CONTINUE;EXEC SQL ROLLBACK;exit(1);}// 删除处理void del(){EXEC SQL BEGIN DECLARE SECTION;int emp_number;EXEC SQL END DECLARE SECTION;char empnum[8];printf("/n/nEnter employee number(press RETURN to abort):");gets(empnum);if(!strcmp(empnum,"")){printf("/n");return;}emp_number = atoi(empnum);EXEC SQL WHENEVER SQLERROR GOTO sqlerror;EXEC SQL WHENEVER NOT FOUND GOTO notfound;EXEC SQL DELETE FROM EMPWHERE EMPNO = :emp_number;printf("/n/nEmployee %d deleted./n",emp_number);EXEC SQL COMMIT;return;notfound:printf("/n/nWARING: Employee %d does not exist./n",emp_number);return;sqlerror:printf("/n%.70s/n",sqlca.sqlerrm.sqlerrmc);EXEC SQL WHENEVER SQLERROR CONTINUE;EXEC SQL ROLLBACK;exit(1);}

2）将demo.pc预编译成demo.c：

proc demo.pc

3）将demo.c编译成demo.obj：

cl demo.c /nologo /c /I F:/app/tonko/product/11.1.0/db_1/precomp/public

4）将demo.obj链接成demo.exe：

link demo.obj /nologo /LIBPATH:F:/app/tonko/product/11.1.0/db_1/precomp/LIB orasql11.lib

5）执行demo.exe进行测试。