最近帮做的一个数据库实验，过程中觉得网上 Pro*C 的资源和实例不多，于是想记录一下。
简单说下 Pro*C 程序简单原理：编写一个后缀名为 .pc 的源代码，内容是正常的 C 代码嵌入 Pro*C 语句，然后预编译成后缀名为 .c 的文件，再编译为 exe 可执行文件。
如Linux下执行命令：
precompiledimensxcn1:pgirard> chmod 700 precompile dimensxcn1:pgirard> chmod 700 compile dimensxcn1:pgirard> ./precompile Pro*C/C++: Release 8.1.7.0.0 - Production on Sat Mar 8 17:48:12 2008 (c) Copyright 2000 Oracle Corporation. All rights reserved. System default option values taken from: /disk/disk1/oracle/OraHome1/precomp/adg dimensxcn1:pgirard> ./compile dimensxcn1:pgirard> ./user_tec预编译语句：
proc INAME=user_tec.pc CODE=ANSI_C SQLCHECK=semantics mode=oracle userid=user/password char_map=charz编译语句：
gcc user_tec.c -o user_tec -include /$ORACLE_HOME/precomp/public/sqlca.h -lclntsh -B$ORACLE_HOME/lib/ -B$ORACLE_HOME/lib 所谓嵌入 简单来说就是，在即将进行 SQL 语句时声明 EXEC SQL ,在 SQL 语句结束时声明 EXEC END，并将 select 结果通过 INTO :变量, :变量, :变量 的形式放入 C 语言变量中。从而使查询结果能够在 C 代码中进行显示，或者操作后再次调用嵌入的 SQL 更新数据库。值得注意的是，通过 C 语言变量接收 SELECT 语句返回的结果有两种形式，一种是刚才说到的 INTO :[已经声明好的C变量]，但这种方法有局限性，只有在结果集为一行时有效，若结果集为多行则会报错，这是需要通过一个 cursor (游标)对结果集进行遍历，如 user_tec.pc 中的函数。
话不多说，对于 Pro*C 略有掌握的可以直接看代码，看不懂的可以往下拉看下题目大意。
采购部门管理员界面：
/* user_pds.pc Pro*C Programe for Pruchasing Department Supervisor By Mr.Zhangweijian date:June 6, 2017 */ #include<stdio.h> #include<stdlib.h> void sql_error(); void do_connect(); int print_menu(); void modresp();//给定一个part_id，修改其关联的agent void quant();//给定一个agent号和一个日期，输出这个采购商所关联的，订单日期在这个日期以前的订单数量和订单总价值 int main() { EXEC SQL WHENEVER SQLERROR do sql_error("Error at connect"); do_connect(); while(1) { switch(print_menu()) { case 1: modresp(); break; case 2: quant(); break; case 3: puts("\nAu revoir Sayonara Bye bye,Ni Hao"); exit(0); default: puts("\n====>Enter a digit from the menu please?"); break; } } EXEC SQL COMMIT WORK RELEASE; exit(0); } void sql_error(char *msg) { char ora_msg[512]; int buf_len,msg_len; EXEC SQL WHENEVER SQLERROR continue; printf("\n%s\n",msg); buf_len=sizeof(ora_msg); sqlglm(ora_msg, &buf_len, &msg_len); printf("\n%.*s\n",msg_len, ora_msg); EXEC SQL ROLLBACK RELEASE; exit(1); } void do_connect() { char *uid = "ora00067/AbB7dP"; EXEC SQL CONNECT :uid; printf("Connected to Oracle schema\n"); } int print_menu() { int choice; printf("\t user_pds"); printf("\n\tChoose a transaction by entering a number\n"); printf("\t***********************************\n"); printf("\t(1)MODRESPON\n"); printf("\t(2)QUANT\n"); printf("\t(3)QUIT\n"); printf("Enter your choice?"); scanf("%d", &choice); return choice; } /* **************************************************************************************************** * Function to change the responsibility of a given part(part_id) from a agent to another agent * **************************************************************************************************** */ void modresp() { int id,num, tstcode; for(;;) { printf("Enter the part id to change (0 to quit)?"); scanf("%d", id); if(id==0) { printf("End of this transaction\n"); return; } printf("Part number: %d\n", id); printf("Enter the emp_num to change(0 to quit)?"); scanf("%d", num); if(num==0) { printf("End of this transaction\n"); return; } printf("Part number: %d\n", num); EXEC SQL EXECUTE DECLARE procedure mod_agent(id IN integer, num IN integer, stat OUT integer) is BEGIN stat := 1; update responsible set emp_number=num where part_number=id; if SQL%NOTFOUND then stat:=0; end if; END; BEGIN mod_agent(:id, :num, :tstcode); END; END-EXEC; if(tstcode==0) printf("Record not found\n"); else printf("Modification done\n"); } return; } /* **************************************************************************************************** * Function to Display the number of purchase orders done by a given agent (emp_num) * and the total value of these purchase orders since a given date * **************************************************************************************************** */ void quant() { char des1[16]; char des2[16]; char date[10]; int my_empnum, none=1; char my_podate[16]; for(;;) { printf("Number of the Agent(0 to quit)?"); scanf("%d", &my_empnum); if(my_empnum == 0) { EXEC SQL COMMIT; printf("End of this transaction\n"); break; } printf("Agent Number: %d\n", my_empnum); printf("Date of po_date(yyyyMMdd, 0 to quit)?"); scanf("%s", my_podate); if(strcmp(my_podate,"0")==0) { EXEC SQL COMMIT; printf("End of this transaction\n"); break; } printf("Date: %s\n", my_podate); EXEC SQL EXECUTE BEGIN SELECT count(po_number) INTO :des1 FROM pa_task WHERE emp_num=:my_empnum; EXCEPTION WHEN NO_DATA_FOUND THEN :none:=0; END; END-EXEC; if(none==0) { printf("Record not found\n"); none=1; } else { EXEC SQL EXECUTE BEGIN SELECT sum(purchase_order.total) INTO :des2 FROM pa_task,purchase_order WHERE pa_task.emp_num=:my_empnum and purchase_order.po_number=pa_task.po_number and purchase_order.po_date>:my_podate; EXCEPTION WHEN NO_DATA_FOUND THEN :none:=0; END; END-EXEC; if(none==0) { printf("Record not found\n"); none=1; } else { printf("**********Result**********\n"); printf(" count(po_number)\n"); printf("-----------------\n"); printf("\t%s\n", des1); printf("sum(purchase_order.total)\n"); printf("------------------------\n"); printf("\t%s\n", des2); } } EXEC SQL COMMIT; } return； }
技术人员界面：
/* user_tec.pc Pro*C Programe for Technician By Mr.Zhangweijian date:June 6, 2017 */ #include<stdio.h> #include<stdlib.h> void sql_error(); void do_connect(); int print_menu(); void lispart();//给定一个part_id，显示这个part的具体信息 void explosion();//给定一个part_id，显示这个part相关联的component void implosion();//给定一个componentid,显示这个component相关联的part int main() { EXEC SQL WHENEVER SQLERROR do sql_error("Error at connect"); do_connect(); while(1) { switch(print_menu()) { case 1: lispart(); break; case 2: explosion(); break; case 3: implosion(); break; case 4: puts("\nAu revoir Sayonara Bye bye,Ni Hao"); exit(0); default: puts("\n====>Enter a digit from the menu please?"); break; } } EXEC SQL COMMIT WORK RELEASE; exit(0); } int print_menu() { int choice; printf("\t user_tec"); printf("\n\tChoose a transaction by entering a number\n"); printf("\t***********************************\n"); printf("\t(1)LISPART\n"); printf("\t(2)EXPLOSION\n"); printf("\t(3)IMPLOSION\n"); printf("\t(4)QUIT\n"); printf("Enter your choice?"); scanf("%d", &choice); return choice; } /* ****************************************************************************************** * Function to display the contents of a given part * ****************************************************************************************** */ void lispart() { int des1; char des2[16]; char des3[16]; int des4; int des5; int des6; int des7; int my_partid, none=1; for(;;) { printf("Number of the part(0 to quit)?"); scanf("%d", &my_partid); if(my_partid == 0) { EXEC SQL COMMIT; printf("End of this transaction\n"); break; } printf("Part Number: %d\n", my_partid); EXEC SQL DECLARE c1 CURSOR FOR SELECT part_id,part_name,unit,unit_price,stock_qty,order_qty,min_qty FROM part WHERE part_id = :my_partid; EXEC SQL OPEN c1; EXEC SQL WHENEVER NOT FOUND DO break; while(1) { EXEC SQL FETCH c1 INTO :des1, :des2, :des3, :des4, :des5 ,:des6, des7; printf("**********Result**********\n"); printf(" part_id part_name unit unit_price stock_qty order_qty min_qty\n"); printf("---------- ------------ ------- ------------ ------------ ------------- --------\n"); printf("\t%d\t%s\t%s\t%d\t%d\t%d\t%d\n", des1, des2, des3, des4, des5, des6, des7); } EXEC SQL CLOSE c1; EXEC SQL COMMIT; } return; } /* ****************************************************************************************** * Function to display all components of a given part(part_id) * ****************************************************************************************** */ void explosion() { int des1; char des2[16]; int des3; int my_partid, none=1; for(;;) { printf("Number of the part(0 to quit)?"); scanf("%d", &my_partid); if(my_partid == 0) { EXEC SQL COMMIT; printf("End of this transaction\n"); break; } printf("Part Number: %d\n", my_partid); EXEC SQL DECLARE c1 CURSOR FOR SELECT part_id,part_name,componentid FROM part_tec,comp_tec WHERE comp_tec.partid = :my_partid AND comp_tec.partid=part_tec.part_id; EXEC SQL OPEN c1; EXEC SQL WHENEVER NOT FOUND DO break; while(1) { EXEC SQL FETCH c1 INTO :des1, :des2, :des3; printf("**********Result**********\n"); printf(" part_id part_name componentid\n"); printf("---------- ------------ ---------------\n"); printf("\t%d\t%s\t%d\n", des1, des2, des3); } EXEC SQL CLOSE c1; EXEC SQL COMMIT; } return; } /* ****************************************************************************************** * Function to display all parts where a given part(part_id) is a component * ****************************************************************************************** */ void implosion() { char des1[16]; char des2[16]; char des3[16]; int my_partid, none=1; for(;;) { printf("Number of the part(0 to quit)?"); scanf("%d", &my_partid); if(my_partid == 0) { EXEC SQL COMMIT; printf("End of this transaction\n"); break; } printf("Part Number: %d\n", my_partid); EXEC SQL DECLARE c1 CURSOR FOR SELECT componentid,part_id,part_name FROM part_tec,comp_tec WHERE comp_tec.componentid = :my_partid AND comp_tec.partid=part_tec.part_id; EXEC SQL OPEN c1; EXEC SQL WHENEVER NOT FOUND DO break; while(1) { EXEC SQL FETCH c1 INTO :des1, :des2, :des3; printf("**********Result**********\n"); printf(" componentid part_id part_name\n"); printf("------------ ------------- ---------------\n"); printf("\t%s\t%s\t%s\n", des1, des2, des3); } EXEC SQL CLOSE c1; EXEC SQL COMMIT; } return; }
题目大意：
用 ProC 写 5 个用户程序，这 5 个用户程序分别有不同权限，可以对数据库(Linux下的MySQL)进行不同操作。5个角色有技术人员、采购商、仓库管理员等。
每个ProC程序有一个简单的用户界面如下：
然后可以根据提示进行操作：
在任何输入的时候输入0则为退出。
数据库具体的几个表结构如下：
建表的sql语句，其中视图不用理会：
表之间的部门关系如下：
一个采购商pa_agent通过关联表responsible关联一个part。
多个同类part组成一个product(可以理解为多个同学组成一个班，同学都是属于学生种类)。
一种product由一个供应商supplier供应。
一个订单purchase_order由一个采购商pa_agent发出给一个供应商supplier，订单与采购商通过关联表pa_task关联。
订单的发出人，供应人，购买product数量，总价，状态，保存在detail表中。
写得不是太好，但从我学习历程来看，重点难点应该是 Pro*C 程序 SQL 嵌入以及预编译、编译那部分。
实践能力强的小伙伴不妨建一个简单的表，写一个简单查询的 Pro*C 程序。
