最近在開發一個項目中，為了解決數據庫ＩＯ瓶頸，不得不把數據庫中的數據導出為文本文件。文本傳到客戶端后又要導入到數據庫。本人用Ｃ＋＋Builder嵌入PROC++寫了一個導入導出的DLL。假如對你有用深感榮幸！具體內容如下： 一、預備工作計算機環境：Win 2000 PRO,Oracle 9i,C++ Builder 5.5引入必要的ORACLE內部函數：要用的函數在$(ORACEL_HOME)bin qlora9.dll鏈接庫中。為了能在C++ Builder中使用，先得生成LIB：implib sqlora9.lib sqlora9.dll二、源文件分析//-------------------------------------------------------------------------//加入必要的頭文件#include<vcl.h> #include<windows.h> #include<stdio.h> #include<stdlib.h> #include<string.h>#include<time.h> #include<math.h> #include<fcntl.h> #include<io.h> #include<sys tat.h>//說明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 User[20];//用戶名 char Pwd[20];//密碼 char DB[20];//數據庫服務名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 < 0) return -1; bConnect = true; return 0;}/*---------------------------------------------------------------------------導出文本函數因為不確定SELECT語句的表及字段，所以我使用動態語句(ORACLE DYNAMIC SQL)的//第四種方式。 動態SQL方法四是在不確定SQL語句的選擇項與輸入項，且不知個數與數據類型的情況下使用的一種復雜程序設計技術。---------------------------------------------------------------------------*/int _stdcall ExportTxtfile(const char *Sql/*SQL選擇語句*/, const char FileName/*導出目標文本文件名*/){ int null_ok, precision, scale; int handle; if ((handle = open(FileName, O_CREATO_TEXTO_APPENDO_RDWR, S_IREADS_IWRITE)) == -1) { //文件打開出錯 return -1; }//定義變量，以存放SQL語句 EXEC SQL BEGIN DECLARE SECTION; char sqlstr[256]; EXEC SQL END DECLARE SECTION; //檢查是否連接數據庫 if (bConnect == false) return -2; strcpy(sqlstr/*.arr*/, Sql); //; sqlstr.len = strlen(sql); //給描述區分配空間; if ((SelectUnit = sqlald(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *)NULL) { //空間分配失敗 return -3; } if ((BindUnit = sqlald(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *)NULL) { //空間分配失敗 return -3; } //給查詢返回值存儲區分配空間 SelectUnit->N = MAX_ITEMS; for (int i=0; i < MAX_ITEMS; i++) { BindUnit->I[i] = (short *)malloc(sizeof(short *)); BindUnit->V[i] = (char *)malloc(MAX_VNAME_LEN); } for (int i=0; i < MAX_ITEMS; i++) { SelectUnit->I[i] = (short *)malloc(sizeof(short *)); SelectUnit->V[i] = (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 < 0) { return -4; //輸入項過多 } BindUnit->N = BindUnit->F; //打開光標 EXEC SQL OPEN Cursorbase USING DESCRIPTOR BindUnit; //選擇項處理 EXEC SQL DESCRIBE SELECT LIST for SQLSA INTO SelectUnit; if (SelectUnit->F < 0) { return -4; //選擇表項過多 } SelectUnit->N = SelectUnit->F;//因為所有格式，類型都是不確定的，所以要得到正確的返回值就要處理格式 for (int i=0; i < SelectUnit->F; i++) { sqlnul(&(SelectUnit->T[i]), &(SelectUnit->T[i]), &null_ok); switch (SelectUnit->T[i]) { ;;;;case 1://CHAR ;;;;;;;;;break; ;;;;case 2://NUMBER ;;;;;;;;;sqlprc(&(SelectUnit->L[i]), &precision, &scale); ;;;;;;;;;if (precision == 0) ;;;;;;;;;;;precision = 40; ;;;;;;;;;SelectUnit->L[i] = precision + 2; ;;;;;;;;;break; case 8://LONG ;;;SelectUnit->L[i] = 240; break; case 11://ROWID SelectUnit->L[i] = 18; break; case 12://DATE SelectUnit->L[i] = 9; break; case 23://RAW break; case 24://LONGRAW SelectUnit->L[i] = 240; break; } SelectUnit->V[i] = (char *)realloc(SelectUnit->V[i], SelectUnit->L[i]+1); SelectUnit->T[i] = 1;//把所有類型轉換為字符型 } EXEC SQL WHENEVER NOT FOUND goto EndFor; for (;;) { EXEC SQL FETCH Cursorbase USING DESCRIPTOR SelectUnit; //輸出各字段 for (int i=0; i < SelectUnit->F; i++) { char buffer[256]; if (i != SelectUnit->F-1) sprintf(buffer, '%s', SelectUnit->V[i]); else sprintf(buffer, '%srn', SelectUnit->V[i]); int length = strlen(buffer); if (write(handle, buffer, length) != length) { return -5; //寫文件失敗 exit(1); } } }EndFor: close(handle); for (int i=0; i < MAX_ITEMS; i++) { if (SelectUnit->V[i] != (char *)NULL) free(SelectUnit->V[i]); free(SelectUnit->I[i]); } for (int j=0; j < MAX_ITEMS; j++) { if (BindUnit->V[j] != (char *)NULL) free(BindUnit->V[j]); free(BindUnit->I[j]); } 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 Execommand[256]; char sqlload[] = '. qlload.ctl'; //檢查是否連接數據庫 if (bConnect == false) return -2; if ((fout=fopen(sqlload, 'w')) == NULL) { //建立控制文件出錯 return -1 ; } fprintf(fout, 'LOAD DATAn'); fprintf(fout, 'INFILE '%s'n', FileName); fprintf(fout, 'APPEND INTO TABLE %s (n', TableName); int iStart = 1; for(int i=0; i < LengthArray->Count; i++) { fprintf(fout, '%11s POSITION(%d:%d)', FieldArray[i], iStart, *(int*)LengthArray->Items[i]+iStart-1); iStart += *(int*)LengthArray->Items[i]; fprintf(fout, ' CHAR'); if(i < LengthArray->Count-1) fprintf(fout, ',n'); } fprintf(fout, ')n'); fclose(fout); sprintf(Execommand, 'sqlldr.exe userid=%s/%s@%s control=%s', User, Pwd, DB, sqlload); if (system(Execommand) == -1) { //SQL*Loader執行錯誤 return -1; } return 0 ;}//----------------------------------------------------------------------------三、編譯用ORACLE的PROC預編譯器預編后，放入C++ Builder中聯編。 聯編時需加入前面生成的sqlora9.lib。聯編時還要注重，所有PROC生成的ORACLE內部函數調用都要說明為extern 'C' _declspec(dllexport) TYPE _stdcall類型。水平有限還請見諒！！！請多多指點。ＱＱ：5005647