一个使用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]; // 登录到oracle strcpy(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,JOB INTO :emp_name,:job:ind_job FROM EMP WHERE 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 EMP SET ENAME= :emp_name,JOB=:job:ind_job WHERE 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_name FROM EMP WHERE 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,JOB INTO :emp_name,:job:ind_job FROM EMP WHERE 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 EMP WHERE 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进行测试。