ORACLE與excel的數(shù)據(jù)互傳方法

ORACLE與excel的數(shù)據(jù)互傳方法眾所周知，MicrosoftExcel能很直觀而方便地進行數(shù)據(jù)輸入，統(tǒng)計，生成圖表，但它的數(shù)據(jù)管理能力有限，對大量的數(shù)據(jù)查詢能力不足，如果利用它數(shù)據(jù)計算方面的優(yōu)點和大型數(shù)據(jù)庫ORACLE的數(shù)據(jù)查詢優(yōu)點，可以設(shè)計出功能強大的數(shù)據(jù)處理系統(tǒng)。假設(shè)與Excel數(shù)據(jù)文件相對應(yīng)的職工情況表EMP已在ORACLE系統(tǒng)中創(chuàng)建，此表中各字段按照順序分別是職工代號(Emp—No),職工姓名(Ename),工資(Salary)，雇用時間(Hiredate)。1、將Excel中的數(shù)據(jù)裝入ORACLE數(shù)據(jù)庫通過ORACLE數(shù)據(jù)庫系統(tǒng)的高級應(yīng)用工具SQL*Loader可以將原來的Excel中存儲的數(shù)據(jù)直接裝入ORACLE數(shù)據(jù)庫的表中。首先，在MicrosoftExcel中完成表格數(shù)據(jù)輸入后，選擇“保存文件”命令，在出現(xiàn)對話框的“文件類型”欄中選取“格式化文本文件(空格分隔)”以形成一個標準格式化文本文件(*.PRN)或是選取“CSV(逗號分隔)”形成一個逗號定界文件(*.CSV)。這兩者的區(qū)別是：標準格式的文本文件中每個記錄等長，數(shù)據(jù)間按原庫文件字段長度緊湊排列，字符型字段數(shù)據(jù)左對齊，數(shù)據(jù)型字段數(shù)據(jù)右對齊，不足部分用空格補足。逗號定界文件特點是各記錄可不等長，字段按其定義順序依次排列，字段間用逗號分隔，字符型字段和數(shù)據(jù)型字段的左右端空格被消去。因為這兩種格式文件有不同的數(shù)據(jù)裝入方法，假如我們將職工情況EMP表存為EMP.CSV，數(shù)據(jù)變成如下形式：120，JOE，1192.64，93/04/11121，KERT，309.60，87/01/1423，PETER，1500.25，84/03/12124，SMITH，678.00，91/01/0715，YOUNG，2390.12，80/05/03接著，通過記事本建立以下控制文件，它包含了數(shù)據(jù)文件的名稱及其格式，文件字段如何定界，數(shù)據(jù)類型是怎樣等內(nèi)容，將該裝載控制文件保存為EMP.CTL：LOADDATAINFILE'C：\EMP.CSV‘BADFILE'EMP.BAD'DISCARDFILE'EMP.DSC'APPENDINTOTABLEEMPFILEDSTERMINATEDBY"，"（Emp-No，Ename，Salary，HiredateDATA（8）"YY/MM/DD"）之后，雙擊SQL*Loader圖標，進入ORACLE7的'數(shù)據(jù)裝載工具，在彈出的SQLLOADER主屏幕對話框中，分別輸入用戶登錄名，密碼，數(shù)據(jù)庫名稱和控制文件名，確認后進行數(shù)據(jù)裝載。這樣SQL*Loader—邊轉(zhuǎn)換、裝入數(shù)據(jù)，一邊將執(zhí)行過程中的錯誤信息和統(tǒng)計信息存入登錄文件（LogFile）中，把ORACLE拒絕裝入的原始數(shù)據(jù)存入拒絕文件（RejectFile）。用戶可以在裝載完成后，打開登錄文件文件查看數(shù)據(jù)的裝載情況，了解因為何種原因使得數(shù)據(jù)被拒絕，是因為數(shù)據(jù)本身不符合數(shù)據(jù)庫的數(shù)表定義還是違反了完整性原則等其它原因。由此可見，數(shù)據(jù)裝載不僅快速而且安全。特別要提到的是可以通過SQL*Loader同時將數(shù)據(jù)文件裝入多個數(shù)表。例如在Excel表格中輸入數(shù)據(jù)時，可以對應(yīng)輸入該職工參加的項目代號（Proj-No）,現(xiàn)在需要將這些數(shù)據(jù)分別裝入EMP表和PROJ表。數(shù)據(jù)文件按標準格式存儲如下所示，其中第5，6，7列均為該職工參加的項目代號：120JOE1192.6493/04/11101112121KERT309.6087/01/14281223PETER1500.2584/03/124017124SMITH678.0091/01/0710202115YOUNG2390.1280/05/03432617編寫的裝載數(shù)據(jù)控制文件可以是：LOADDATAINFILE'C：\EMP.PRN'BADFILE'EMP.BAD'DISCARDFILE'EMP.DSC'APPENDINTOTABLEEMP（Emp-NoPOSITION（01：05）INTEGEREXTERNAL，EnamePOSITION（06：15）CHAR，SalaryPOSITION（16：25）DECIMALEXTERNAL，HiredatePOSITION（26：34）INTEGEREXTERNAL）INTOTABLEPROJWHENProj-No!K''（Emp-NoPOSITION（1：4）INTEGEREXTERNAL，Proj-NoPOSITION（35：38）INTEGEREXTERNAL）INTOTABLEPROJWHENProj-No!K''（Emp-NoPOSITION（1：4）INTEGEREXTERNAL，Proj-NoPOSITION（39：42）INTEGEREXTERNAL）INTOTABLEPROJWHENProj-No!K''（Emp-NoPOSITION（1：4）INTEGEREXTERNAL，Proj-NoPOSITION（43：46）INTEGEREXTERNAL）注意，CHAR，INTEGEREXTERNAL和DECIMALEXTERNAL這些數(shù)據(jù)類型是指數(shù)據(jù)文件中的數(shù)據(jù)類型，而不是數(shù)據(jù)庫中數(shù)表的數(shù)據(jù)類型。數(shù)字字段的EXTERNAL暗示了它不是以二進制格式存儲的數(shù)字，而是以人可以識別的ASCII碼格式存儲。這樣運行SQL*Loader之后，所需數(shù)據(jù)被分別裝入兩個數(shù)表，非常方便。2、將ORACLE數(shù)據(jù)文件轉(zhuǎn)化為Excel文件當需要在Excel下獲取ORACLE數(shù)據(jù)時，可以通過ODBC（假設(shè)ORACLE是安裝在WindowsNT操作系統(tǒng)下）。ODBC的英文意思是OpenDatabaseConnectivity（開放式數(shù)據(jù)庫連接），它是Microsoft提供的一組標準應(yīng)用程序編程接口（API）。ODBC建立了一組應(yīng)用程序直接操作數(shù)據(jù)庫數(shù)據(jù)的規(guī)范，允許用戶的應(yīng)用程序使用基于SQL語言的不同類型的數(shù)據(jù)庫管理系統(tǒng)。在Excel的“數(shù)據(jù)”菜單中獲取“獲取外部數(shù)據(jù)”，MicrosoftQuery被啟動。這是一個非常有用的數(shù)據(jù)查詢程序，它能在網(wǎng)絡(luò)上以客戶機/服務(wù)器形式快速查詢數(shù)據(jù)。在其中的File菜單中選取NewQuery，“SelectDataSource"窗口出現(xiàn)，提示用戶在DataSource列表中選取需獲取的數(shù)據(jù)所在的數(shù)據(jù)源。點按“Other"按鈕，“ODBCDataSource"窗口出現(xiàn)，列出了機器中已被定義的所有ODBC數(shù)據(jù)源，如果在其中未能找到ORACLE數(shù)據(jù)源，說明對應(yīng)于ORACLE數(shù)據(jù)源的ODBCDriver驅(qū)動程序未有安裝。此時，只要點選New按紐，在出現(xiàn)的“AddDataSource"對話框中選取ORACLE7.1Driver，“Oracle7ODBCSetup”窗口出現(xiàn)，分別輸入數(shù)據(jù)庫名字和有效的SQL*Net主機連接字符串（格式如：P/ServerName/InstantId），確認后MicrosoftQrery就與ORACLE中的數(shù)據(jù)庫相連。這之