PROC＋＋大量匯入匯出ORACLE資料庫表
來源：互聯網
上載者：User
oracle|資料|資料庫
最近在開發一個項目中，為瞭解決資料庫ＩＯ瓶頸，不得不把資料庫中的資料匯出為文字檔。文本傳到用戶端後又要匯入到資料庫。本人用Ｃ＋＋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_CREAT|O_TEXT|O_APPEND|O_RDWR, S_IREAD|S_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, "%s\r\n", 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 DATA\n");
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
