PROC＋＋批量导入导出ORACLE数据库表数据库教程 电脑资料

PROC批量导入导出ORACLE数据库表数据库教程 电脑资料 oracle|数据|数据库 最近在开发一个项目中，为了解决数据库瓶颈，不得不把数据库中的数据导出为文本文件， 一、准备工作 计算机环境：Win 2000 PRO,ORACLE 9i,C+ Builder 5.5 引入必要的ORACLE内部函数：要用的函数在$(ORACEL_HOME)bin qlora9.dll链接库中。为了能在C+ Builder中使用，先得生成LIB：implib sqlora9.lib sqlora9.dll 二、源文件分析 /- /加入必要的头文件 #include #include #include #include #include #include #include #include #include #include /说明DLL的输出函数 extern C _declspec(dllexport) int _stdcall ConnectDB(const char *Username, const char *Password, const char *Dbname); extern C _declspec(dllexport) int _stdcall ImportTxtfile(TList *LengthArray, String *FieldArray, const char *TableName, const char *FileName); extern C _declspec(dllexport) int _stdcall ExportTxtfile(const char *Sql, const char *FileName); #pragma hdrstop /- #define MAX_ITEMS 20 /定义最大字段数 #define MAX_VNAME_LEN 30 /定义选择表项最大长度 #define MAX_INAME_LEN 30 /定义指示器变量名字的最大长度 EXEC SQL INCLUDE sqlca; /说明SQL通讯区 EXEC SQL INCLUDE oraca; /说明ORACLE通讯区 EXEC SQL INCLUDE sqlda; /说明SQL语句描述结构/*SQLDA结构体请查相关资料*/ EXEC ORACLE OPTION (ORACA = YES); EXEC ORACLE OPTION (RELEASE_CURSOR = YES); /说明ORACLE外部函数 extern C _declspec(dllimport) void _stdcall sqlclu(SQLDA*); extern C _declspec(dllimport) void _stdcall sqlnul(short*, short*, int*); extern C _declspec(dllimport) void _stdcall sqlprc(int*, int*, int*); extern C _declspec(dllimport) struct SQLDA * _stdcall sqlald(int, unsigned int, unsigned int); SQLDA *SelectUnit; /定义选择项描述 SQLDA *BindUnit; /定义输入项空间 /定义变量，以存放连接数据库的参数 EXEC SQL BEGIN DECLARE SECTION; char User20;/用户名 char Pwd20;/密码 char DB20;/数据库服务名 EXEC SQL END DECLARE SECTION; bool bConnect = false;/是否连接标志 #pragma hdrstop #pragma argsused /C+ Builder DLL的主函数 BOOL WINAPI DllMain(HINSTANCE hinstDLL, DWORD fwdreason, LPVOID lpvReserved) return 1; /*- 连接数据库 -*/ int _stdcall ConnectDB(const char *Username, const char *Password, const char *Dbname) strcpy(User, Username); strcpy(Pwd, Password); strcpy(DB, Dbname); EXEC SQL CONNECT :User IDENTIFIED BY :Pwd USING :DB; if (sqlca.sqlcode N = MAX_ITEMS; for (int i=0; i Ii = (short *)malloc(sizeof(short *); BindUnit-Vi = (char *)malloc(MAX_VNAME_LEN); for (int i=0; i Ii = (short *)malloc(sizeof(short *); SelectUnit-Vi = (char *)malloc(MAX_VNAME_LEN); EXEC SQL WHENEVER SQLERROR GOTO sqlerr;/DO sql_error(导出出错); /设置语句 EXEC SQL PREPARE SQLSA FROM :sqlstr; EXEC SQL DECLARE Cursorbase CURSOR FOR SQLSA; /输入描述处理 BindUnit-N = MAX_ITEMS; EXEC SQL DESCRIBE BIND VARIABLES for SQLSA INTO BindUnit; if (BindUnit-F N = BindUnit-F; /打开光标 EXEC SQL OPEN Cursorbase USING DESCRIPTOR BindUnit; /选择项处理 EXEC SQL DESCRIBE SELECT LIST for SQLSA INTO SelectUnit; if (SelectUnit-F N = SelectUnit-F; /因为所有格式，类型都是不确定的，所以要得到正确的返回值就要处理格式 for (int i=0; i F; i+) sqlnul(&(SelectUnit-Ti), &(SelectUnit-Ti), &null_ok); switch (SelectUnit-Ti) case 1:/CHAR break; case 2:/NUMBER sqlprc(&(SelectUnit-Li), &precision, &scale); if (precision = 0) precision = 40; SelectUnit-Li = precision + 2; break; case 8:/LONG SelectUnit-Li = 240; break; case 11:/ROWID SelectUnit-Li = 18; break; case 12:/DATE SelectUnit-Li = 9; break; case 23:/RAW break; case 24:/LONGRAW SelectUnit-Li = 240; break; SelectUnit-Vi = (char *)realloc(SelectUnit-Vi, SelectUnit-Li+1); SelectUnit-Ti = 1;/把所有类型转换为字符型 EXEC SQL WHENEVER NOT FOUND goto EndFor; for (;) EXEC SQL FETCH Cursorbase USING DESCRIPTOR SelectUnit; /输出各字段 for (int i=0; i F; i+) char buffer256; if (i != SelectUnit-F-1) sprintf(buffer, %s, SelectUnit-Vi); else sprintf(buffer, %srn, SelectUnit-Vi); int length = strlen(buffer); if (write(handle, buffer, length) != length) return -5; /写文件失败 exit(1); EndFor: close(handle); for (int i=0; i Vi != (char *)NULL) free(SelectUnit-Vi); free(SelectUnit-Ii); for (int j=0; j Vj != (char *)NULL) free(BindUnit-Vj); free(BindUnit-Ij); sqlclu(SelectUnit); sqlclu(BindUnit); EXEC SQL CLOSE Cursorbase; return 0; sqlerr: return -6; /*- 导入文本 为了批量导入，在此我调用的sqlldr工具 首先生成SQL*Loader控制文件，后运行sqlldr -*/ int _stdcall ImportTxtfile(TList LengthArray/*导入文本的字段长度链表*/, String *FieldArray/*数据库表的了段名数组*/, const char TableName/*导入的目标表*/, const char FileName/*导入的源文本文件*/) /产生SQL*Loader控制文件 FILE *fout, *fp; char Exemand256; char sqlload = . qlload.ctl; /检查是否连接数据库 if (bConnect = false) return -2; if (fout=fopen(sqlload, w) = NULL) /建立控制文件出错 return -1 ; fprintf(fout, LOAD DATAn); fprintf(fout, INFILE %sn, FileName); fprintf(fout, APPEND INTO TABLE %s (n, TableName); int iStart = 1; for(int i=0; i Count; i+) fprintf(fout, %11s POSITION(%d:%d), FieldArrayi, iStart, *(int*)LengthArray-Itemsi+iStart-1); iStart += *(int*)LengthArray-Itemsi; fprintf(fout, CHAR); if(i Count-1) fprintf(fout, ,n); fprintf(fout