最近在開(kāi)發(fā)一個(gè)項(xiàng)目中，為了解決數(shù)據(jù)庫(kù)ＩＯ瓶頸，不得不把數(shù)據(jù)庫(kù)中的數(shù)據(jù)導(dǎo)出為文本文件。文本傳到客戶端后又要導(dǎo)入到數(shù)據(jù)庫(kù)。本人用Ｃ＋＋Builder嵌入PROC++寫(xiě)了一個(gè)導(dǎo)入導(dǎo)出的DLL。假如對(duì)你有用深感榮幸！具體內(nèi)容如下： 一、預(yù)備工作計(jì)算機(jī)環(huán)境：Win 2000 PRO,Oracle 9i,C++ Builder 5.5引入必要的ORACLE內(nèi)部函數(shù)：要用的函數(shù)在$(ORACEL_HOME)bin qlora9.dll鏈接庫(kù)中。為了能在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>//說(shuō)明DLL的輸出函數(shù)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;;;;;//定義最大字段數(shù)#define MAX_VNAME_LEN 30; //定義選擇表項(xiàng)最大長(zhǎng)度#define MAX_INAME_LEN 30; //定義指示器變量名字的最大長(zhǎng)度EXEC SQL INCLUDE sqlca;;//說(shuō)明SQL通訊區(qū)EXEC SQL INCLUDE oraca;;//說(shuō)明ORACLE通訊區(qū)EXEC SQL INCLUDE sqlda;;//說(shuō)明SQL語(yǔ)句描述結(jié)構(gòu)/*SQLDA結(jié)構(gòu)體請(qǐng)查相關(guān)資料*/EXEC ORACLE OPTION (ORACA = YES);EXEC ORACLE OPTION (RELEASE_CURSOR = YES);//說(shuō)明ORACLE外部函數(shù)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//定義選擇項(xiàng)描述SQLDA *BindUnit//定義輸入項(xiàng)空間//定義變量，以存放連接數(shù)據(jù)庫(kù)的參數(shù)EXEC SQL BEGIN DECLARE SECTION; char User[20];//用戶名 char Pwd[20];//密碼 char DB[20];//數(shù)據(jù)庫(kù)服務(wù)名EXEC SQL END DECLARE SECTION;bool bConnect = false;//是否連接標(biāo)志#pragma hdrstop#pragma argsused//C++ Builder DLL的主函數(shù)BOOL WINAPI DllMain(HINSTANCE hinstDLL, DWORD fwdreason, LPVOID lpvReserved){ ;;return 1;}/*--------------------------------------------------------------------------- 連接數(shù)據(jù)庫(kù)---------------------------------------------------------------------------*/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;}/*---------------------------------------------------------------------------導(dǎo)出文本函數(shù)因?yàn)椴淮_定SELECT語(yǔ)句的表及字段，所以我使用動(dòng)態(tài)語(yǔ)句(ORACLE DYNAMIC SQL)的//第四種方式。 動(dòng)態(tài)SQL方法四是在不確定SQL語(yǔ)句的選擇項(xiàng)與輸入項(xiàng)，且不知個(gè)數(shù)與數(shù)據(jù)類(lèi)型的情況下使用的一種復(fù)雜程序設(shè)計(jì)技術(shù)。---------------------------------------------------------------------------*/int _stdcall ExportTxtfile(const char *Sql/*SQL選擇語(yǔ)句*/, const char FileName/*導(dǎo)出目標(biāo)文本文件名*/){ int null_ok, precision, scale; int handle; if ((handle = open(FileName, O_CREATO_TEXTO_APPENDO_RDWR, S_IREADS_IWRITE)) == -1) { //文件打開(kāi)出錯(cuò) return -1; }//定義變量，以存放SQL語(yǔ)句 EXEC SQL BEGIN DECLARE SECTION; char sqlstr[256]; EXEC SQL END DECLARE SECTION; //檢查是否連接數(shù)據(jù)庫(kù) if (bConnect == false) return -2; strcpy(sqlstr/*.arr*/, Sql); //; sqlstr.len = strlen(sql); //給描述區(qū)分配空間; 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; } //給查詢返回值存儲(chǔ)區(qū)分配空間 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('導(dǎo)出出錯(cuò)');//設(shè)置ＳＱＬ語(yǔ)句 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; //輸入項(xiàng)過(guò)多 } BindUnit->N = BindUnit->F; //打開(kāi)光標(biāo) EXEC SQL OPEN Cursorbase USING DESCRIPTOR BindUnit; //選擇項(xiàng)處理 EXEC SQL DESCRIBE SELECT LIST for SQLSA INTO SelectUnit; if (SelectUnit->F < 0) { return -4; //選擇表項(xiàng)過(guò)多 } SelectUnit->N = SelectUnit->F;//因?yàn)樗懈袷剑?lèi)型都是不確定的，所以要得到正確的返回值就要處理格式 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;//把所有類(lèi)型轉(zhuǎn)換為字符型 } 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; //寫(xiě)文件失敗 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;}/*----------------------------------------------------------------------------導(dǎo)入文本為了批量導(dǎo)入，在此我調(diào)用的sqlldr工具首先生成SQL*Loader控制文件，后運(yùn)行sqlldr----------------------------------------------------------------------------*/int _stdcall ImportTxtfile(TList LengthArray/*導(dǎo)入文本的字段長(zhǎng)度鏈表*/,String *FieldArray/*數(shù)據(jù)庫(kù)表的了段名數(shù)組*/, const char TableName/*導(dǎo)入的目標(biāo)表*/, const char FileName/*導(dǎo)入的源文本文件*/){ //產(chǎn)生SQL*Loader控制文件 FILE *fout, *fp; char Execommand[256]; char sqlload[] = '. qlload.ctl'; //檢查是否連接數(shù)據(jù)庫(kù) if (bConnect == false) return -2; if ((fout=fopen(sqlload, 'w')) == NULL) { //建立控制文件出錯(cuò) 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執(zhí)行錯(cuò)誤 return -1; } return 0 ;}//----------------------------------------------------------------------------三、編譯用ORACLE的PROC預(yù)編譯器預(yù)編后，放入C++ Builder中聯(lián)編。 聯(lián)編時(shí)需加入前面生成的sqlora9.lib。聯(lián)編時(shí)還要注重，所有PROC生成的ORACLE內(nèi)部函數(shù)調(diào)用都要說(shuō)明為extern 'C' _declspec(dllexport) TYPE _stdcall類(lèi)型。水平有限還請(qǐng)見(jiàn)諒！！！請(qǐng)多多指點(diǎn)。ＱＱ：5005647