今天就来大概说一下ORACLE提出的 Utility - SQL*Loader 这里以 Oracle 9i 为例说明... 以下参考自 ORACLE9i DBA Fundamentals 2-II Oracle9i Database Utilities Release 2 (9.2) ( 10g 有些新的功能 - datapump 讲起来有点复杂 耶~~以後再说 = =") 1. SQL*Loader 概念 将外部的 file (external file) 之data load到Oracle database的table中 SQL*Loader 有着很高的弹性, 几乎文字档都可以不错的效率load入table SQL*Loader 有以下的功能 在同一个 session load 多个file或 load data到多个table 文字档可以是固定或变动长度 文字档内容可以是 character, binary... 任何format 指定data2的 character set 选择性load data (就是过滤噜) 使用SQL function 处理data再load入table 指定某栏位产生 unique sequential key 可由disk, tape, named pipe load data 产生error reports 使用Secondary datafiles (SDFs) loading LOBs and collections 可选择 conventional (弹性高)/direct(效率好) path loading 2. SQL*Loader 之使用 如教材上 直接举例会比较好了解 $ sqlldr hr/hr control=case1.ctl log=case1.log direct=Y control 指 control file (不是 database的control file) 就是sqlldr的参数控制档 log当然是 log file direct=Y 使用 direct path ; =N 使用conventional path 3. Control file 别与 database 的control file 搞混了喔 功能: 告诉 loader 去哪找资料load data 的 format configuration - Memory Management, Record Rejection, Interrupted load handling details 如何处理data syntax: Free Format, case insensitive, " -- " 为注解该行, CONSTANT 为保留字 例子: 将 data及 控制全写在 control file中 ulcase1.ctl ----------------------- ulcase1.ctl --------------------------------- LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC) BEGINDATA 12,RESEARCH,"SARATOGA" 10,"ACCOUNTING",CLEVELAND 11,"ART",SALEM 13,FINANCE,"BOSTON" 21,"SALES",PHILA. 22,"SALES",ROCHESTER 42,"INT'L","SAN FRAN" ----------------------- END of ulcase1.ctl ---------------------------- data (ulcase2.dat) 与 control file(ulcase2.ctl) 分开 ulcase2.ctl ----------------------- ulcase2.ctl --------------------------------- LOAD DATA INFILE 'ulcase2.dat' INTO TABLE EMP ( EMPNO POSITION(01:04) INTEGER EXTERNAL, ENAME POSITION(06:15) CHAR, JOB POSITION(17:25) CHAR, MGR POSITION(27:30) INTEGER EXTERNAL, SAL POSITION(32:39) DECIMAL EXTERNAL, COMM POSITION(41:48) DECIMAL EXTERNAL, DEPTNO POSITION(50:51) INTEGER EXTERNAL) ----------------------- END of ulcase2.ctl -------------------------- ulcase2.dat ----------------------- ulcase2.dat --------------------------------- 7782 CLARK MANAGER 7839 2572.50 10 7839 KING PRESIDENT 5500.00 10 7934 MILLER CLERK 7782 920.00 10 7566 JONES MANAGER 7839 3123.75 20 7499 ALLEN SALESMAN 7698 1600.00 300.00 30 7654 MARTIN SALESMAN 7698 1312.50 1400.00 30 7658 CHAN ANALYST 7566 3450.00 20 ----------------------- END of ulcase2.dat -------------------------- 将data与control file写在一起, 资料以"," 为分隔, 在 " 中为同一栏位(并分大小写) ulcase3.ctl ----------------------- ulcase3.ctl --------------------------------- LOAD DATA INFILE * APPEND INTO TABLE EMP FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (empno, ename, job, mgr, hiredate DATE(20) "DD-Month-YYYY", sal, comm, deptno CHAR TERMINATED BY ':', projno, loadseq SEQUENCE(MAX,1) ) BEGINDATA 7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,, 10:101 7839, "King", "President", , 17-November-1981, 5500.00,, 10:102 7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,, 10:102 7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,, 20:101 7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00, 300.00, 30:103 7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50, 1400.00, 30:103 7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101 ----------------------- END of ulcase3.ctl --------------------------- 还有其他的例子 可以到 ?/rdbms/demo 中去找(上面也是抄出来的 XD ) 4. Conventional Path and Direct Path Conventional COMMITs to Make Changes 总是产生Redo logs 所有Constraints均作用 触发Insert triggers Can Load into Cluster Tables Other Users 可对该table做异动 Direct Data Saves 只有特定情况产生Redo PK, U-Key, Not Null 才作用 不触发Insert triggers Can't Load into Cluster Tables Other Users 不可对该table做异动 以上只说到最简单的 data loading 当然如果需要 load LOB 亦可 其他的变化或是参数等, 请大家找 Manual的Utilities(每个版本应该都是放在这一本吧)翻一翻 XD ... = =" 我承认我懒.... ( ￣ c￣)y▂ξ --

※ 发信站: 批踢踢实业坊(ptt.cc) ◆ From: 202.145.230.2