编写PRO*C程序实现对oracle数据库常用操作并导出文本文件
来源：互联网 发布：七台河中天网络诈骗 编辑：程序博客网 时间：2024/05/03 15:19
编写proc程序实现对account账户，trans_record交易记录进行相应操作。
account （id，name，balance）
trans_record (id, trans_date,record,balance)
添加新账户，查询，更新，查询一段时间内交易记录。
将trans_record内数据导出为文本文档。
#########################################################################
以下程序为本人初学oracle编写的第一个proc程序，有很多瑕疵，望见谅！
#########################################################################
修改：
进一步修改程序游标无法跳出的问题，以及导出为文本时无法将全部数据导出的问题。2013-13-14 15:17
EXEC SQL INCLUDE SQLCA.H#include <stdio.h>#include <string.h>#include <stdlib.h>/**************************************************************************############### Main ############## *************************************************************************/int main(int argc, char **argv){ EXEC SQL BEGIN DECLARE SECTION; char username[100]; char password[100]; char servername[100]; char sysdate[100]; int n = 0,m = 0,i = 0,z = 0,r = 0; char bdate[10],edate[10]; char user_name[10]; int user_balance = 0; char user_record[20]; char user_trans_date[10];struct myaccount{int id;char name[10];int balance;};struct myaccount myaccount; struct record{ int id; char date[10]; char rec[20]; int balance; }; struct record myrecord; EXEC SQL END DECLARE SECTION; if (argc<4) { printf("dbtest username passowrd servicename \n"); return (-1); }sprintf(username, argv[1]);sprintf(password, argv[2]);sprintf(servername, argv[3]);EXEC SQL CONNECT :username IDENTIFIED BY :password USING :servername;if(sqlca.sqlcode != 0) {printf("connect database %s/%s@%s error \n", username, password, servername);printf("sqlca.sqlcode=[%d]\n",sqlca.sqlcode); \printf("sqlerr=[%s]\n",sqlca.sqlerrm.sqlerrmc); \printf("filename=[%s]\n",__FILE__);\printf("fileline=[%d]\n",__LINE__);\fflush(stdout);return -1;}/******************************************************************** create account&&trans_record. ******************************************************************/EXEC SQL CREATE TABLE trans_record(id NUMBER ,trans_date DATE, record VARCHAR2(50) ,balance NUMBER );if (sqlca.sqlcode == 0) { printf("create table trans_record ok \n"); } else { printf("create table trans_record FAILED \n"); }EXEC SQL CREATE TABLE account(id NUMBER ,name VARCHAR2(20) ,balance NUMBER);if (sqlca.sqlcode == 0) { printf("create table account ok \n"); } else { printf("create table account FAILED \n"); } /******************************************************************** insert into account&&trans_record ******************************************************************/ EXEC SQL INSERT INTO account VALUES(1,'xiaoming',3000);EXEC SQL INSERT INTO account VALUES(2,'xiaohua',4000);EXEC SQL INSERT INTO account VALUES(3,'xiaolei',5000);EXEC SQL INSERT INTO account VALUES(4,'xiaozhan',6000);if (sqlca.sqlcode == 0) { printf("insert table account ok \n"); } else { printf("insert table account FAILED \n"); } EXEC SQL INSERT INTO trans_record VALUES(2,to_date('2012-11-12','yyyy-mm-dd'),'expense1000',3000); EXEC SQL INSERT INTO trans_record VALUES(1,to_date('2012-11-12','yyyy-mm-dd'),'expense1000',3000);if (sqlca.sqlcode == 0) { printf("insert table trans_record ok \n"); } else { printf("insert table trans_record FAILED \n"); } EXEC SQL COMMIT ;/******************************************************************** Select account&&trans_record ******************************************************************/ printf("please input the number which you want select!\n");scanf("%d",&n); EXEC SQL DECLARE mycursor2 CURSOR FOR SELECT * FROM account \WHERE id = :n;if (sqlca.sqlcode == 0) {printf("select account ok \n");}else {printf("sqlca.sqlcode=[%d]\n",sqlca.sqlcode); \printf("sqlerr=[%s]\n",sqlca.sqlerrm.sqlerrmc); \printf("filename=[%s]\n",__FILE__);\printf("fileline=[%d]\n",__LINE__);\fflush(stdout);}EXEC SQL OPEN mycursor2;EXEC SQL WHENEVER NOT FOUND DO break;while(1){EXEC SQL FETCH mycursor2 INTO :myaccount.id,:myaccount.name, \:myaccount.balance; printf("ID=%d,NAME=%s,BALANCE=%d\n",myaccount.id,myaccount.name, \myaccount.balance); }EXEC SQL CLOSE mycursor2;printf("Select record from trans_record!\n");printf("please input the number which you want select!\n");scanf("%d",&m); printf("please input the begin date(example:20120101): \n");for(i = 0; i < 9; i++)scanf("%c",&bdate[i]);bdate[9]='\0';//printf("%s\n",to_date(bdate,'yyyy-mm-dd'));printf("please input the end date(example:20121222): \n");for(i = 0; i < 9; i++)scanf("%c",&edate[i]);edate[9]='\0';//printf("%s\n",to_date(edate,'yyyy-mm-dd'));EXEC SQL DECLARE mycursor CURSOR FOR SELECT id,to_char(trans_date,'yyyy-mm-dd'),record,balance FROM trans_record WHERE id = :m AND trans_date > to_date(:bdate,'yyyy-mm-dd') AND trans_date < to_date(:edate,'yyyy-mm-dd'); if (sqlca.sqlcode == 0) { printf("select account ok .\n");}else { printf("sqlca.sqlcode=[%d]\n",sqlca.sqlcode); \printf("sqlerr=[%s]\n",sqlca.sqlerrm.sqlerrmc); \printf("filename=[%s]\n",__FILE__);\printf("fileline=[%d]\n",__LINE__);\fflush(stdout);}EXEC SQL OPEN mycursor;EXEC SQL WHENEVER NOT FOUND DO break;while(1){EXEC SQL FETCH mycursor INTO :myrecord.id,:myrecord.date, \:myrecord.rec,:myrecord.balance;printf("ID=%d,TRANS_DATE=%s,RECORD=%s,BALANCE=%d\n",myrecord.id,myrecord.date,myrecord.rec,myrecord.balance);}EXEC SQL CLOSE mycursor;/*EXEC SQL SELECT trans_date,record,balance INTO :user_trans_date,user_record,:user_balance FROM trans_record WHERE id = :m AND trans_date > to_date(:bdate,'yyyy-mm-dd') AND trans_date < to_date(:edate,'yyyy-mm-dd');printf("ID=%d,TRANS_DATE=%s,RECORD=%s,BALANCE=%d\n",m,user_trans_date,user_record,user_balance); if (sqlca.sqlcode == 0) { printf("select account ok .\n"); } else { printf("sqlca.sqlcode=[%d]\n",sqlca.sqlcode); \printf("sqlerr=[%s]\n",sqlca.sqlerrm.sqlerrmc); \printf("filename=[%s]\n",__FILE__);\printf("fileline=[%d]\n",__LINE__);\fflush(stdout); }*//******************************************************************** Update account&&trans_record ******************************************************************/ /*printf("please input the number which you want update!\n");scanf("%d",&z);EXEC SQL UPDATE account SET name = 'xxx' WHERE id = :z;if (sqlca.sqlcode == 0) { printf("update table account OK .\n"); } else { printf("update table account FAILED .\n"); }*//******************************************************************** import&&export trans_record ******************************************************************/ /********************* import ***************************/FILE *fp = fopen("/home/oracle/recfile.txt", "w+t");if (fp == NULL) {perror("Open file recfile");exit(1);}EXEC SQL DECLARE mycursor1 CURSOR FOR SELECT id,to_char(trans_date,'yyyy-mm-dd'),record,balance FROM trans_record; if (sqlca.sqlcode == 0) { printf("select trans_record for write is ok.\n"); } else {printf("sqlca.sqlcode=[%d]\n",sqlca.sqlcode); \printf("sqlerr=[%s]\n",sqlca.sqlerrm.sqlerrmc); \printf("filename=[%s]\n",__FILE__);\printf("fileline=[%d]\n",__LINE__);\fflush(stdout); }EXEC SQL OPEN mycursor1;EXEC SQL WHENEVER NOT FOUND DO break;while(1){EXEC SQL FETCH mycursor1 INTO :myrecord.id,:myrecord.date, \:myrecord.rec,:myrecord.balance;r += fwrite(&myrecord, sizeof(struct record), 3, fp);fwrite( "\n", 1, 1, fp );}fclose(fp);EXEC SQL CLOSE mycursor1;printf("write line : %d\n",r);EXEC SQL TRUNCATE TABLE trans_record;if (sqlca.sqlcode == 0) { printf("truncate table trans_record ok \n"); } else { printf("truncate table trans_record FAILED \n"); }/********************* export ***************************/fp = fopen("/home/oracle/recfile.txt", "r+t");if (fp == NULL) {perror("Open file recfile");exit(1);}i = 0;while(i < r){fread(&myrecord, sizeof(struct record), r, fp);EXEC SQL INSERT INTO trans_record VALUES(:myrecord.id, \to_date(:myrecord.date,'yyyy-mm-dd'),:myrecord.rec, \:myrecord.balance);if (sqlca.sqlcode == 0) { printf("insert table trans_record ok \n"); } else { printf("insert table trans_record FAILED \n"); }printf("%d,%s,%s,%d\n",myrecord.id,myrecord.date,myrecord.rec,\myrecord.balance);i++;}fclose(fp);EXEC SQL COMMIT;/******************************************************************** Drop account&&trans_record ******************************************************************/ printf("check data and drop the table!\n");int q;scanf("%d",&q);EXEC SQL DROP TABLE account;if (sqlca.sqlcode == 0) { printf("drop table account OK .\n"); } else { printf("drop table account FAILED .\n"); } EXEC SQL DROP TABLE trans_record;if (sqlca.sqlcode == 0) { printf("drop table trans_record OK .\n"); } else { printf("drop table trans_record FAILED .\n"); } EXEC SQL COMMIT WORK RELEASE; return 0;}
- 编写PRO*C程序实现对oracle数据库常用操作并导出文本文件
- Pro*C实现导出Oracle表为文本文件
- C语言实现对文本文件的操作
- Oracle数据库操作（pro*c/c++方式）
- 文本文件编写并运行Java程序
- c#数据库对文本文件的导入导出
- ASP 连接数据库并对数据库的一些常用操作
- oracle数据库导出程序
- Linux 环境下Oracle PRO*C程序的编写简单范例
- oracle spool定期导出文本文件数据，并加载到teradata
- 使用Pro *C连接Oracle 数据库
- Oracle导出txt文本文件
- Oracle数据库导入导出程序
- Oracle导出数据库相关操作
- Oracle 数据库导入导出 操作
- ORACLE数据库常用操作
- oracle数据库常用操作
- Oracle数据库常用操作
- 单利设计模式
- Dreamweaver:使用问题和技巧汇总
- Oracle 中序列（sequence）和触发器（trigger）的使用
- Android学习札记15：对Android中View绘制流程的一些理解
- 怎样让程序重新启动自己
- 编写PRO*C程序实现对oracle数据库常用操作并导出文本文件
- jquery下的提交，点击按钮没反应，post方法不执行 JSON方式在FORM表单下不起作用
- lua基础小记
- ThinkPHP小小的经验总结
- win7下的Object Header结构
- yii框架下输出hive结果
- jQuery 屏蔽页面再次被按和延迟加载函数
- C#遍历指定文件夹中的所有文件
- 初识ExtJS
