亚洲va精品中文字幕,午夜亚洲国产理论秋霞,国产99在线|亚洲

Case具有兩種格式。簡單Case函數(shù)和Case搜索函數(shù)。
--簡單Case函數(shù)
CASE sex
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函數(shù)
CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END

這兩種方式，可以實(shí)現(xiàn)相同的功能。簡單Case函數(shù)的寫法相對(duì)比較簡潔，但是和Case搜索函數(shù)相比，功能方面會(huì)有些限制，比如寫判斷式。
還有一個(gè)需要注意的問題，Case函數(shù)只返回第一個(gè)符合條件的值，剩下的Case部分將會(huì)被自動(dòng)忽略。
--比如說，下面這段SQL，你永遠(yuǎn)無法得到“第二類”這個(gè)結(jié)果
CASE WHEN col_1 IN ( 'a', 'b') THEN '第一類'
         WHEN col_1 IN ('a')       THEN '第二類'
ELSE'其他' END

下面我們來看一下，使用Case函數(shù)都能做些什么事情。

一，已知數(shù)據(jù)按照另外一種方式進(jìn)行分組，分析。

有如下數(shù)據(jù):(為了看得更清楚，我并沒有使用國家代碼，而是直接用國家名作為Primary Key)
國家（country）    人口（population）
中國    600
美國    100
加拿大    100
英國    200
法國    300
日本    250
德國    200
墨西哥    50
印度    250

根據(jù)這個(gè)國家人口數(shù)據(jù)，統(tǒng)計(jì)亞洲和北美洲的人口數(shù)量。應(yīng)該得到下面這個(gè)結(jié)果。
洲    人口
亞洲    1100
北美洲    250
其他    700

想要解決這個(gè)問題，你會(huì)怎么做？生成一個(gè)帶有洲Code的View，是一個(gè)解決方法，但是這樣很難動(dòng)態(tài)的改變統(tǒng)計(jì)的方式。
如果使用Case函數(shù)，SQL代碼如下:
SELECT SUM(population),
        CASE country
                WHEN '中國'     THEN '亞洲'
                WHEN '印度'     THEN '亞洲'
                WHEN '日本'     THEN '亞洲'
                WHEN '美國'     THEN '北美洲'
                WHEN '加拿大' THEN '北美洲'
                WHEN '墨西哥' THEN '北美洲'
        ELSE '其他' END
FROM    Table_A
GROUP BY CASE country
                WHEN '中國'     THEN '亞洲'
                WHEN '印度'     THEN '亞洲'
                WHEN '日本'     THEN '亞洲'
                WHEN '美國'     THEN '北美洲'
                WHEN '加拿大' THEN '北美洲'
                WHEN '墨西哥' THEN '北美洲'
        ELSE '其他' END;

同樣的，我們也可以用這個(gè)方法來判斷工資的等級(jí)，并統(tǒng)計(jì)每一等級(jí)的人數(shù)。SQL代碼如下；
SELECT
        CASE WHEN salary <= 500 THEN '1'
             WHEN salary > 500 AND salary <= 600 THEN '2'
             WHEN salary > 600 AND salary <= 800 THEN '3'
             WHEN salary > 800 AND salary <= 1000 THEN '4'
        ELSE NULL END salary_class,
        COUNT(*)
FROM    Table_A
GROUP BY
        CASE WHEN salary <= 500 THEN '1'
             WHEN salary > 500 AND salary <= 600 THEN '2'
             WHEN salary > 600 AND salary <= 800 THEN '3'
             WHEN salary > 800 AND salary <= 1000 THEN '4'
        ELSE NULL END;

二，用一個(gè)SQL語句完成不同條件的分組。

有如下數(shù)據(jù)
國家（country）    性別（sex）    人口（population）
中國    1    340
中國    2    260
美國    1    45
美國    2    55
加拿大    1    51
加拿大    2    49
英國    1    40
英國    2    60

按照國家和性別進(jìn)行分組，得出結(jié)果如下
國家    男    女
中國    340    260
美國    45    55
加拿大    51    49
英國    40    60

普通情況下，用UNION也可以實(shí)現(xiàn)用一條語句進(jìn)行查詢。但是那樣增加消耗(兩個(gè)Select部分)，而且SQL語句會(huì)比較長。
下面是一個(gè)是用Case函數(shù)來完成這個(gè)功能的例子
SELECT country,
       SUM( CASE WHEN sex = '1' THEN
                      population ELSE 0 END), --男性人口
       SUM( CASE WHEN sex = '2' THEN
                      population ELSE 0 END)   --女性人口
FROM Table_A
GROUP BY country;

這樣我們使用Select，完成對(duì)二維表的輸出形式，充分顯示了Case函數(shù)的強(qiáng)大。

三，在Check中使用Case函數(shù)。

在Check中使用Case函數(shù)在很多情況下都是非常不錯(cuò)的解決方法。可能有很多人根本就不用Check，那么我建議你在看過下面的例子之后也嘗試一下在SQL中使用Check。
下面我們來舉個(gè)例子
公司A，這個(gè)公司有個(gè)規(guī)定，女職員的工資必須高于1000塊。如果用Check和Case來表現(xiàn)的話，如下所示
CONSTRAINT check_salary CHECK
           ( CASE WHEN sex = '2'
                  THEN CASE WHEN salary > 1000
                        THEN 1 ELSE 0 END
                  ELSE 1 END = 1 )

如果單純使用Check，如下所示
CONSTRAINT check_salary CHECK
           ( sex = '2' AND salary > 1000 )

女職員的條件倒是符合了，男職員就無法輸入了。

posted @ 2010-03-26 18:14 xzc 閱讀(169) | 評(píng)論 (0) | 編輯收藏

sqlldr詳解

Oracle 的SQL*LOADER可以將外部數(shù)據(jù)加載到數(shù)據(jù)庫表中。下面是SQL*LOADER的基本特點(diǎn)：

1）能裝入不同數(shù)據(jù)類型文件及多個(gè)數(shù)據(jù)文件的數(shù)據(jù)
2）可裝入固定格式，自由定界以及可度長格式的數(shù)據(jù)
3）可以裝入二進(jìn)制，壓縮十進(jìn)制數(shù)據(jù)
4）一次可對(duì)多個(gè)表裝入數(shù)據(jù)
5）連接多個(gè)物理記錄裝到一個(gè)記錄中
6）對(duì)一單記錄分解再裝入到表中
7）可以用數(shù)對(duì)制定列生成唯一的KEY
8）可對(duì)磁盤或磁帶數(shù)據(jù)文件裝入制表中
9）提供裝入錯(cuò)誤報(bào)告
10）可以將文件中的整型字符串，自動(dòng)轉(zhuǎn)成壓縮十進(jìn)制并裝入列表中。

1.2控制文件
控制文件是用一種語言寫的文本文件，這個(gè)文本文件能被SQL*LOADER識(shí)別。SQL*LOADER根據(jù)控制文件可以找到需要加載的數(shù)據(jù)。并且分析和解釋這些數(shù)據(jù)。控制文件由三個(gè)部分組成：

l 全局選件，行，跳過的記錄數(shù)等；
l INFILE子句指定的輸入數(shù)據(jù)；
l 數(shù)據(jù)特性說明。

1.3輸入文件
對(duì)于 SQL*Loader, 除控制文件外就是輸入數(shù)據(jù)。SQL*Loader可從一個(gè)或多個(gè)指定的文件中讀出數(shù)據(jù)。如果數(shù)據(jù)是在控制文件中指定，就要在控制文件中寫成 INFILE * 格式。當(dāng)數(shù)據(jù)固定的格式（長度一樣）時(shí)且是在文件中得到時(shí)，要用INFILE "fix n"

load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,

當(dāng)數(shù)據(jù)是可變格式（長度不一樣）時(shí)且是在文件中得到時(shí)，要用INFILE "var n"。如：

load data
infile 'example.dat' "var 3"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
009hello,cd,010world,im,
012my,name is,

1.4壞文件
壞文件包含那些被SQL*Loader拒絕的記錄。被拒絕的記錄可能是不符合要求的記錄。
壞文件的名字由 SQL*Loader命令的BADFILE 參數(shù)來給定。

1.5日志文件及日志信息
當(dāng)SQL*Loader 開始執(zhí)行后，它就自動(dòng)建立日志文件。日志文件包含有加載的總結(jié)，加載中的錯(cuò)誤信息等。

控制文件語法
控制文件的格式如下：

OPTIONS （ { [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )
LOAD[DATA]
[ { INFILE | INDDN } {file | * }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { 'string' | X 'hex' } } ]
INTO TABLE [user.]table
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]...]
[FIELDS [delimiter] ]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,...]
)
[INTO TABLE...]
[BEGINDATA]

1）要加載的數(shù)據(jù)文件：

1．INFILE 和INDDN是同義詞，它們后面都是要加載的數(shù)據(jù)文件。如果用 * 則表示數(shù)據(jù)就在控制文件內(nèi)。在INFILE 后可以跟幾個(gè)文件。
2．STRAM 表示一次讀一個(gè)字節(jié)的數(shù)據(jù)。新行代表新物理記錄（邏輯記錄可由幾個(gè)物理記錄組成）。
3．RECORD 使用宿主操作系統(tǒng)文件及記錄管理系統(tǒng)。如果數(shù)據(jù)在控制文件中則使用這種方法。
3． FIXED length 要讀的記錄長度為length字節(jié)，
4． VARIABLE 被讀的記錄中前兩個(gè)字節(jié)包含的長度，length 記錄可能的長度。缺傷為8k字節(jié)。
5． BADFILE和BADDN同義。Oracle 不能加載數(shù)據(jù)到數(shù)據(jù)庫的那些記錄。
6． DISCARDFILE和DISCARDDN是同義詞。記錄沒有通過的數(shù)據(jù)。
7． DISCARDS和DISCARDMAX是同義詞。Integer 為最大放棄的文件個(gè)數(shù)。

2）加載的方法：

1．APPEND 給表添加行。
2．INSERT 給空表增加行（如果表中有記錄則退出）。
3．REPLACE 先清空表在加載數(shù)據(jù)。
4． RECLEN 用于兩種情況，1）SQLLDR不能自動(dòng)計(jì)算記錄長度，2）或用戶想看壞文件的完整記錄時(shí)。對(duì)于后一種，Oracle只能按常規(guī)把壞記錄部分寫到錯(cuò)誤的地方。如果看整條記錄，則可以將整條記錄寫到壞文件中。

3）指定最大的記錄長度：

1． CONCATENATE 允許用戶設(shè)定一個(gè)整數(shù)，表示要組合邏輯記錄的數(shù)目。

4）建立邏輯記錄：

1．THIS 檢查當(dāng)前記錄條件，如果為真則連接下一個(gè)記錄。
2．NEXT 檢查下一個(gè)記錄條件。如果為真，則連接下一個(gè)記錄到當(dāng)前記錄來。
2． Start: end 表示要檢查在THIS或NEXT字串是否存在繼續(xù)串的列，以確定是否進(jìn)行連接。如：continueif next(1-3)='WAG' 或continueif next(1-3)=X'0d03if'

5）指定要加載的表：

1．INTO TABLE 要加的表名。
2．WHEN 和select WHERE類似。用來檢查記錄的情況，如：when(3-5)='SSM' and (22)='*"

6）介紹并括起記錄中的字段：

1． FIELDS給出記錄中字段的分隔符，F(xiàn)IELDS格式為：

FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'} ]
[ [ OPTIONALLY] ENCLOSE [BY] [X]'charcter' ]

TERMINATED 讀完前一個(gè)字段即開始讀下一個(gè)字段直到介紹。
WHITESPACE 是指結(jié)束符是空格的意思。包括空格、Tab、換行符、換頁符及回車符。如果是要判斷但字符，可以用單引號(hào)括起，如X'1B'等。
OPTIONALLY ENCLOSED 表示數(shù)據(jù)應(yīng)由特殊字符括起來。也可以括在TERMINATED字符內(nèi)。使用OPTIONALLY要同時(shí)用TERMINLATED。
ENCLOSED 指兩個(gè)分界符內(nèi)的數(shù)據(jù)。如果同時(shí)用 ENCLOSED和TERMINAED ，則它們的順序決定計(jì)算的順序。

7）定義列：

column 是表列名。列的取值可以是：
BECHUM 表示邏輯記錄數(shù)。第一個(gè)記錄為1,第2個(gè)記錄為2。
CONSTANT 表示賦予常數(shù)。
SEQUENCE 表示序列可以從任意序號(hào)開始，格式為：
SEQUENCE （ { integer | MAX |COUNT} [,increment]
POSITION 給出列在邏輯記錄中的位置。可以是絕對(duì)的，或相對(duì)前一列的值。格式為：
POSITION ( {start[end] | * [+integer] } )
Start 開始位置
* 表示前字段之后立刻開始。
+ 從前列開始向后條的位置數(shù)。

8）定義數(shù)據(jù)類型：
可以定義14種數(shù)據(jù)類型：
CHAR
DATE
DECIMAL EXTERNAL
DECIMAL
DOUBLE
FLOAT
FLOAT EXTERNAL
GRAPHIC EXTERNAL
INTEGER
INTEGER EXTERNAL
SMALLINT
VARCHAR
VARGRAPHIC

1．字符類型數(shù)據(jù)
CHAR[ (length)] [delimiter]
length缺省為 1.
2.日期類型數(shù)據(jù)
DATE [ ( length)]['date_format' [delimiter]
使用to_date函數(shù)來限制。

3．字符格式中的十進(jìn)制
DECIMAL EXTERNAL [(length)] [delimiter]
用于常規(guī)格式的十進(jìn)制數(shù)（不是二進(jìn)制=> 一個(gè)位等于一個(gè)bit）。

4．壓縮十進(jìn)制格式數(shù)據(jù)
DECIMAL （digtial [,precision]）

5．雙精度符點(diǎn)二進(jìn)制
DOUBLE

6．普通符點(diǎn)二進(jìn)制
FLOAT

7．字符格式符點(diǎn)數(shù)
FLOAT EXTERNAL [ (length) ] [delimiter]

8．雙字節(jié)字符串?dāng)?shù)據(jù)
GRAPHIC [ (legth)]

9．雙字節(jié)字符串?dāng)?shù)據(jù)
GRAPHIC EXTERNAL[ (legth)]

10．常規(guī)全字二進(jìn)制整數(shù)
INTEGER

11．字符格式整數(shù)
INTEGER EXTERNAL

12．常規(guī)全字二進(jìn)制數(shù)據(jù)
SMALLINT

13．可變長度字符串
VARCHAR

14．可變雙字節(jié)字符串?dāng)?shù)據(jù)
VARGRAPHIC

2.2寫控制文件CTL

1．各數(shù)據(jù)文件的文件名;
2．各數(shù)據(jù)文件格式;
3．各數(shù)據(jù)文件里各數(shù)據(jù)記錄字段的屬性；
4．接受數(shù)據(jù)的ORACLE表列的屬性；
5．?dāng)?shù)據(jù)定義；
6．其它

數(shù)據(jù)文件的要求：

數(shù)據(jù)類型的指定
CHAR 字符型
INTEGER EXTERNAL 整型
DECIMAL EXTERNAL 浮點(diǎn)型

3.1數(shù)據(jù)文件的內(nèi)容

可以在OS下的一個(gè)文件；或跟在控制文件下的具體數(shù)據(jù)。數(shù)據(jù)文件可以是：
1、二進(jìn)制與字符格式：LOADER可以把二進(jìn)制文件讀（當(dāng)成字符讀）列表中
2、固定格式：記錄中的數(shù)據(jù)、數(shù)據(jù)類型、數(shù)據(jù)長度固定。
3、可變格式：每個(gè)記錄至少有一個(gè)可變長數(shù)據(jù)字段，一個(gè)記錄可以是一個(gè)連續(xù)的字符串。
數(shù)據(jù)段的分界（如姓名、年齡）如用“，”作字段的分；用，"’作數(shù)據(jù)
括號(hào)等
4、 LOADER可以使用多個(gè)連續(xù)字段的物理記錄組成一個(gè)邏輯記錄，記錄文件運(yùn)行情況文件：包括以下內(nèi)容：
1、運(yùn)行日期：軟件版本號(hào)
2、全部輸入，輸出文件名；對(duì)命令行的展示信息，補(bǔ)充信息，
3、對(duì)每個(gè)裝入信息報(bào)告：如表名，裝入情況；對(duì)初始裝入，加截入或更新裝
入的選擇情況，欄信息
4、數(shù)據(jù)錯(cuò)誤報(bào)告：錯(cuò)誤碼；放棄記錄報(bào)告
5、每個(gè)裝X報(bào)告：裝入行；裝入行數(shù)，可能跳過行數(shù)；可能拒絕行數(shù)；可能放
棄行數(shù)等
6、統(tǒng)計(jì)概要：使用空間（包大小，長度）；讀入記錄數(shù)，裝入記錄數(shù)，跳過記
錄數(shù)；拒絕記錄數(shù)，放棄記錄數(shù)；運(yùn)行時(shí)間等。

==========================================================================================================

sql load的一點(diǎn)小總結(jié)

sqlldr userid=lgone/tiger control=a.ctl
LOAD DATA
INFILE 't.dat' // 要導(dǎo)入的文件
// INFILE 'tt.date' // 導(dǎo)入多個(gè)文件
// INFILE * // 要導(dǎo)入的內(nèi)容就在control文件里下面的BEGINDATA后面就是導(dǎo)入的內(nèi)容

INTO TABLE table_name // 指定裝入的表
BADFILE 'c:bad.txt' // 指定壞文件地址

************* 以下是4種裝入表的方式
APPEND // 原先的表有數(shù)據(jù) 就加在后面
// INSERT // 裝載空表如果原先的表有數(shù)據(jù) sqlloader會(huì)停止默認(rèn)值
// REPLACE // 原先的表有數(shù)據(jù) 原先的數(shù)據(jù)會(huì)全部刪除
// TRUNCATE // 指定的內(nèi)容和replace的相同會(huì)用truncate語句刪除現(xiàn)存數(shù)據(jù)

************* 指定的TERMINATED可以在表的開頭也可在表的內(nèi)部字段部分
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// 裝載這種數(shù)據(jù): 10,lg,"""lg""","lg,lg"
// 在表中結(jié)果: 10 lg "lg" lg,lg
// TERMINATED BY X '09' // 以十六進(jìn)制格式 '09' 表示的
// TERMINATED BY WRITESPACE // 裝載這種數(shù)據(jù): 10 lg lg

TRAILING NULLCOLS ************* 表的字段沒有對(duì)應(yīng)的值時(shí)允許為空

************* 下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER 關(guān)鍵字此列的數(shù)值不會(huì)被裝載
// 如: lg,lg,not 結(jié)果 lg lg
)
// 當(dāng)沒聲明FIELDS TERMINATED BY ',' 時(shí)
// (
// col_1 [interger external] TERMINATED BY ',' ,
// col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
// col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
// )
// 當(dāng)沒聲明FIELDS TERMINATED BY ','用位置告訴字段裝載數(shù)據(jù)
// (
// col_1 position(1:2),
// col_2 position(3:10),
// col_3 position(*:16), // 這個(gè)字段的開始位置在前一字段的結(jié)束位置
// col_4 position(1:16),
// col_5 position(3:10) char(8) // 指定字段的類型
// )

BEGINDATA // 對(duì)應(yīng)開始的 INFILE * 要導(dǎo)入的內(nèi)容就在control文件里
10,Sql,what
20,lg,show

=====================================================================================
//////////// 注意begindata后的數(shù)值前面不能有空格

1 ***** 普通裝載
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10,Sales,"""USA"""
20,Accounting,"Virginia,USA"
30,Consulting,Virginia
40,Finance,Virginia
50,"Finance","",Virginia // loc 列將為空
60,"Finance",,Virginia // loc 列將為空

2 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情況
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
-- FIELDS TERMINATED BY x'09'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia

3 ***** 指定不裝載那一列
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
( DEPTNO,
FILLER_1 FILLER, // 下面的 "Something Not To Be Loaded" 將不會(huì)被裝載
DNAME,
LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,"Virginia,USA"

4 ***** position的列子
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(*:16), // 這個(gè)字段的開始位置在前一字段的結(jié)束位置
LOC position(*:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA

5 ***** 使用函數(shù) 日期的一種表達(dá) TRAILING NULLCOLS的使用
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS // 其實(shí)下面的ENTIRE_LINE在BEGINDATA后面的數(shù)據(jù)中是沒有直接對(duì)應(yīng)
// 的列的值的如果第一行改為 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME "upper(:dname)", // 使用函數(shù)
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy', // 日期的一種表達(dá)方式還有'dd-mon-yyyy' 等
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finance,Virginia,15/3/2001

6 ***** 使用自定義的函數(shù) // 解決的時(shí)間問題
create or replace
function my_to_date( p_string in varchar2 ) return date
as
type fmtArray is table of varchar2(25);

l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',
'dd/mm/yyyy',
'dd/mm/yyyy hh24:mi:ss' );
l_return date;
begin
for i in 1 .. l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
when others then null;
end;
EXIT when l_return is not null;
end loop;

if ( l_return is null )
then
l_return :=
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
p_string, 'GMT', 'EST' );
end if;

return l_return;
end;
/

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )" // 使用自定義的函數(shù)
)
BEGINDATA
10,Sales,Virginia,01-april-2001
20,Accounting,Virginia,13/04/2001
30,Consulting,Virginia,14/04/2001 12:02:02
40,Finance,Virginia,987268297
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Not a date

7 ***** 合并多行記錄為一行記錄
LOAD DATA
INFILE *
concatenate 3 // 通過關(guān)鍵字concatenate 把幾行的記錄看成一行記錄
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales, // 其實(shí)這3行看成一行 10,Sales,Virginia,1/5/2000
Virginia,
1/5/2000
// 這列子用 continueif list="," 也可以
告訴sqlldr在每行的末尾找逗號(hào) 找到逗號(hào)就把下一行附加到上一行

LOAD DATA
INFILE *
continueif this(1:1) = '-' // 找每行的開始是否有連接字符 - 有就把下一行連接為一行
// 如 -10,Sales,Virginia,
// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
// 其中1:1 表示從第一行開始并在第一行結(jié)束還有continueif next 但continueif list最理想
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA // 但是好象不能象右面的那樣使用
-10,Sales,Virginia, -10,Sales,Virginia,
1/5/2000 1/5/2000
-40, 40,Finance,Virginia,13/04/2001
Finance,Virginia,13/04/2001

8 ***** 載入每行的行號(hào)

load data
infile *
into table t
replace
( seqno RECNUM //載入每行的行號(hào)
text Position(1:1024))
BEGINDATA
fsdfasj //自動(dòng)分配一行號(hào)給載入表t 的seqno字段此行為 1
fasdjfasdfl // 此行為 2 ...

9 ***** 載入有換行符的數(shù)據(jù)
注意: unix 和 windows 不同 n & /n
< 1 > 使用一個(gè)非換行符的字符
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS "replace(:comments,'n',chr(10))" // replace 的使用幫助轉(zhuǎn)換換行符
)
BEGINDATA
10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia

< 2 > 使用fix屬性
LOAD DATA
INFILE demo17.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo17.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia

// 這樣裝載會(huì)把換行符裝入數(shù)據(jù)庫下面的方法就不會(huì) 但要求數(shù)據(jù)的格式不同

LOAD DATA
INFILE demo18.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo18.dat
10,Sales,Virginia,01-april-2001,"This is the Sales
Office in Virginia"
20,Accounting,Virginia,13/04/2001,"This is the Accounting
Office in Virginia"
30,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting
Office in Virginia"
40,Finance,Virginia,987268297,"This is the Finance
Office in Virginia"

< 3 > 使用var屬性
LOAD DATA
INFILE demo19.dat "var 3"
// 3 告訴每個(gè)記錄的前3個(gè)字節(jié)表示記錄的長度如第一個(gè)記錄的 071 表示此記錄有 71 個(gè)字節(jié)
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo19.dat
07110,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
07820,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
07140,Finance,Virginia,987268297,This is the Finance
Office in Virginia

< 4 > 使用str屬性
// 最靈活的一中可定義一個(gè)新的行結(jié)尾符 win 回車換行 : chr(13)||chr(10)

此列中記錄是以 a|rn 結(jié)束的
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;
結(jié)果 7C0D0A

LOAD DATA
INFILE demo20.dat "str X'7C0D0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo20.dat
10,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia|
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia|
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia|
40,Finance,Virginia,987268297,This is the Finance
Office in Virginia|

==============================================================================
象這樣的數(shù)據(jù) 用 nullif 子句

10-jan-200002350Flipper seemed unusually hungry today.
10510-jan-200009945Spread over three meals.

id position(1:3) nullif id=blanks // 這里可以是blanks 或者別的表達(dá)式
// 下面是另一個(gè)列子第一行的 1 在數(shù)據(jù)庫中將成為 null
LOAD DATA
INFILE *
INTO TABLE T
REPLACE
(n position(1:2) integer external nullif n='1',
v position(3:8)
)
BEGINDATA
1 10
20lg
------------------------------------------------------------

如果是英文的日志格式，可能需要修改環(huán)境變量 nls_lang or nls_date_format

==========================================================================================================

Oracle SQL*Loader 使用指南（轉(zhuǎn)載）

SQL*Loader是Oracle數(shù)據(jù)庫導(dǎo)入外部數(shù)據(jù)的一個(gè)工具.它和DB2的Load工具相似,但有更多的選擇，它支持變化的加載模式，可選的加載及多表加載.
如何使用 SQL*Loader 工具
我們可以用Oracle的sqlldr工具來導(dǎo)入數(shù)據(jù)。例如:
sqlldr scott/tiger control=loader.ctl
控制文件(loader.ctl) 將加載一個(gè)外部數(shù)據(jù)文件（含分隔符). loader.ctl如下:
load data
infile 'c:datamydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )

mydata.csv 如下:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
下面是一個(gè)指定記錄長度的示例控制文件。"*" 代表數(shù)據(jù)文件與此文件同名，即在后面使用BEGINDATA段來標(biāo)識(shí)數(shù)據(jù)。
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE

Unloader這樣的工具
Oracle 沒有提供將數(shù)據(jù)導(dǎo)出到一個(gè)文件的工具。但是，我們可以用SQL*Plus的select 及 format 數(shù)據(jù)來輸出到一個(gè)文件：
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off

另外，也可以使用使用 UTL_FILE PL/SQL 包處理:
rem Remember to update initSID.ora, utl_file_dir='c:oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %sn', 'TextField', 55);
utl_file.fclose(fp);
end;
/

當(dāng)然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。

加載可變長度或指定長度的記錄
如：
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"

下面是導(dǎo)入固定位置（固定長度）數(shù)據(jù)示例：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

跳過數(shù)據(jù)行：
可以用 "SKIP n" 關(guān)鍵字來指定導(dǎo)入時(shí)可以跳過多少行數(shù)據(jù)。如：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB

導(dǎo)入數(shù)據(jù)時(shí)修改數(shù)據(jù)：
在導(dǎo)入數(shù)據(jù)到數(shù)據(jù)庫時(shí)，可以修改數(shù)據(jù)。注意，這僅適合于常規(guī)導(dǎo)入，并不適合 direct導(dǎo)入方式.如：
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112

LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)

將數(shù)據(jù)導(dǎo)入多個(gè)表：
如:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)

導(dǎo)入選定的記錄：
如下例： (01) 代表第一個(gè)字符, (30:37) 代表30到37之間的字符:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)

導(dǎo)入時(shí)跳過某些字段：
可用 POSTION(x:y) 來分隔數(shù)據(jù). 在Oracle8i中可以通過指定 FILLER 字段實(shí)現(xiàn)。FILLER 字段用來跳過、忽略導(dǎo)入數(shù)據(jù)文件中的字段.如：
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)

導(dǎo)入多行記錄：
可以使用下面兩個(gè)選項(xiàng)之一來實(shí)現(xiàn)將多行數(shù)據(jù)導(dǎo)入為一個(gè)記錄:

CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.

CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

SQL*Loader 數(shù)據(jù)的提交：
一般情況下是在導(dǎo)入數(shù)據(jù)文件數(shù)據(jù)后提交的。
也可以通過指定 ROWS= 參數(shù)來指定每次提交記錄數(shù)。

提高 SQL*Loader 的性能：
1) 一個(gè)簡單而容易忽略的問題是，沒有對(duì)導(dǎo)入的表使用任何索引和/或約束(主鍵)。如果這樣做，甚至在使用ROWS=參數(shù)時(shí)，會(huì)很明顯降低數(shù)據(jù)庫導(dǎo)入性能。
2) 可以添加 DIRECT=TRUE來提高導(dǎo)入數(shù)據(jù)的性能。當(dāng)然，在很多情況下，不能使用此參數(shù)。
3) 通過指定 UNRECOVERABLE選項(xiàng)，可以關(guān)閉數(shù)據(jù)庫的日志。這個(gè)選項(xiàng)只能和 direct 一起使用。
4) 可以同時(shí)運(yùn)行多個(gè)導(dǎo)入任務(wù).

常規(guī)導(dǎo)入與direct導(dǎo)入方式的區(qū)別：
常規(guī)導(dǎo)入可以通過使用 INSERT語句來導(dǎo)入數(shù)據(jù)。Direct導(dǎo)入可以跳過數(shù)據(jù)庫的相關(guān)邏輯(DIRECT=TRUE)，而直接將數(shù)據(jù)導(dǎo)入到數(shù)據(jù)文件中。

posted @ 2010-03-24 21:04 xzc 閱讀(17847) | 評(píng)論 (2) | 編輯收藏

oracle sql loader全攻略

oracle sql loader全攻略(一)

一：sql loader 的特點(diǎn)

oracle自己帶了很多的工具可以用來進(jìn)行數(shù)據(jù)的遷移、備份和恢復(fù)等工作。但是每個(gè)工具都有自己的特點(diǎn)。

比如說exp和imp可以對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行導(dǎo)出和導(dǎo)出的工作，是一種很好的數(shù)據(jù)庫備份和恢復(fù)的工具，因此主要用在數(shù)據(jù)庫的熱備份和恢復(fù)方面。有著速度快，使用簡單，快捷的優(yōu)點(diǎn)；同時(shí)也有一些缺點(diǎn)，比如在不同版本數(shù)據(jù)庫之間的導(dǎo)出、導(dǎo)入的過程之中，總會(huì)出現(xiàn)這樣或者那樣的問題，這個(gè)也許是oracle公司自己產(chǎn)品的兼容性的問題吧。

sql loader 工具卻沒有這方面的問題，它可以把一些以文本格式存放的數(shù)據(jù)順利的導(dǎo)入到oracle數(shù)據(jù)庫中，是一種在不同數(shù)據(jù)庫之間進(jìn)行數(shù)據(jù)遷移的非常方便而且通用的工具。缺點(diǎn)就速度比較慢，另外對(duì)blob等類型的數(shù)據(jù)就有點(diǎn)麻煩了。

二：sql loader 的幫助

C:\>sqlldr

SQL*Loader: Release 9.2.0.1.0 - Production on 星期六 10月 9 14:48:12 2004

用法: SQLLDR keyword=value [,keyword=value,...]

有效的關(guān)鍵字:

userid -- ORACLE username/password

control -- Control file name

log -- Log file name

bad -- Bad file name

data -- Data file name

discard -- Discard file name

discardmax -- Number of discards to allow (全部默認(rèn))

skip -- Number of logical records to skip (默認(rèn)0)

load -- Number of logical records to load (全部默認(rèn))

errors -- Number of errors to allow (默認(rèn)50)

rows -- Number of rows in conventional path bind array or between direct p

ath data saves

（默認(rèn): 常規(guī)路徑 64, 所有直接路徑）

bindsize -- Size of conventional path bind array in bytes(默認(rèn)256000)

silent -- Suppress messages during run (header,feedback,errors,discards,part

itions)

direct -- use direct path (默認(rèn)FALSE)

parfile -- parameter file: name of file that contains parameter specification

parallel -- do parallel load (默認(rèn)FALSE)

file -- File to allocate extents from

skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默

認(rèn)FALSE)

skip_index_maintenance -- do not maintain indexes, mark affected indexes as unus

able(默認(rèn)FALSE)

readsize -- Size of Read buffer (默認(rèn)1048576)

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(

默認(rèn)NOT_USED)

columnarrayrows -- Number of rows for direct path column array(默認(rèn)5000)

streamsize -- Size of direct path stream buffer in bytes(默認(rèn)256000)

multithreading -- use multithreading in direct path

resumable -- enable or disable resumable for current session(默認(rèn)FALSE)

resumable_name -- text string to help identify resumable statement

resumable_timeout -- wait time (in seconds) for RESUMABLE(默認(rèn)7200)

date_cache -- size (in entries) of date conversion cache(默認(rèn)1000)

PLEASE NOTE: 命令行參數(shù)可以由位置或關(guān)鍵字指定

。前者的例子是 'sqlload

scott/tiger foo'; 后一種情況的一個(gè)示例是 'sqlldr control=foo

userid=scott/tiger'.位置指定參數(shù)的時(shí)間必須早于

但不可遲于由關(guān)鍵字指定的參數(shù)。例如,

允許 'sqlldr scott/tiger control=foo logfile=log', 但是

不允許 'sqlldr scott/tiger control=foo log', 即使

參數(shù) 'log' 的位置正確。

C:\>

三：sql loader使用例子

a）SQLLoader將 Excel 數(shù)據(jù)導(dǎo)出到 Oracle

1.創(chuàng)建SQL*Loader輸入數(shù)據(jù)所需要的文件,均保存到C:\，用記事本編輯：

控制文件：input.ctl，內(nèi)容如下：

load data --1、控制文件標(biāo)識(shí)

infile 'test.txt' --2、要輸入的數(shù)據(jù)文件名為test.txt

append into table test--3、向表test中追加記錄

fields terminated by X'09'--4、字段終止于X'09'，是一個(gè)制表符（TAB）

(id,username,password,sj) -----定義列對(duì)應(yīng)順序

a、insert，為缺省方式，在數(shù)據(jù)裝載開始時(shí)要求表為空

b、append，在表中追加新記錄

c、replace，刪除舊記錄，替換成新裝載的記錄

d、truncate，同上

在DOS窗口下使用SQL*Loader命令實(shí)現(xiàn)數(shù)據(jù)的輸入

C:\>sqlldr userid=system/manager control=input.ctl

默認(rèn)日志文件名為：input.log

默認(rèn)壞記錄文件為：input.bad

2.還有一種方法

可以把EXCEL文件另存為CSV（逗號(hào)分隔）(*.csv),控制文件就改為用逗號(hào)分隔

LOAD DATA

INFILE 'd:\car.csv'

APPEND INTO TABLE t_car_temp

FIELDS TERMINATED BY ","

(phoneno,vip_car)

b）在控制文件中直接導(dǎo)入數(shù)據(jù)

1、控制文件test.ctl的內(nèi)容

-- The format for executing this file with SQL Loader is:

-- SQLLDR control= Be sure to substitute your

-- version of SQL LOADER and the filename for this file.

LOAD DATA

INFILE *

BADFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.BAD'

DISCARDFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.DSC'

INSERT INTO TABLE EMCCOUNTRY

Fields terminated by ";" Optionally enclosed by '"'

(

COUNTRYID NULLIF (COUNTRYID="NULL"),

COUNTRYCODE,

COUNTRYNAME,

CONTINENTID NULLIF (CONTINENTID="NULL"),

MAPID NULLIF (MAPID="NULL"),

CREATETIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (CREATETIME="NULL"),

LASTMODIFIEDTIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (LASTMODIFIEDTIME="NULL")

)

BEGINDATA

1;"JP";"Japan";1;9;"09/16/2004 16:31:32";NULL

2;"CN";"China";1;10;"09/16/2004 16:31:32";NULL

3;"IN";"India";1;11;"09/16/2004 16:31:32";NULL

4;"AU";"Australia";6;12;"09/16/2004 16:31:32";NULL

5;"CA";"Canada";4;13;"09/16/2004 16:31:32";NULL

6;"US";"United States";4;14;"09/16/2004 16:31:32";NULL

7;"MX";"Mexico";4;15;"09/16/2004 16:31:32";NULL

8;"GB";"United Kingdom";3;16;"09/16/2004 16:31:32";NULL

9;"DE";"Germany";3;17;"09/16/2004 16:31:32";NULL

10;"FR";"France";3;18;"09/16/2004 16:31:32";NULL

11;"IT";"Italy";3;19;"09/16/2004 16:31:32";NULL

12;"ES";"Spain";3;20;"09/16/2004 16:31:32";NULL

13;"FI";"Finland";3;21;"09/16/2004 16:31:32";NULL

14;"SE";"Sweden";3;22;"09/16/2004 16:31:32";NULL

15;"IE";"Ireland";3;23;"09/16/2004 16:31:32";NULL

16;"NL";"Netherlands";3;24;"09/16/2004 16:31:32";NULL

17;"DK";"Denmark";3;25;"09/16/2004 16:31:32";NULL

18;"BR";"Brazil";5;85;"09/30/2004 11:25:43";NULL

19;"KR";"Korea, Republic of";1;88;"09/30/2004 11:25:43";NULL

20;"NZ";"New Zealand";6;89;"09/30/2004 11:25:43";NULL

21;"BE";"Belgium";3;79;"09/30/2004 11:25:43";NULL

22;"AT";"Austria";3;78;"09/30/2004 11:25:43";NULL

23;"NO";"Norway";3;82;"09/30/2004 11:25:43";NULL

24;"LU";"Luxembourg";3;81;"09/30/2004 11:25:43";NULL

25;"PT";"Portugal";3;83;"09/30/2004 11:25:43";NULL

26;"GR";"Greece";3;80;"09/30/2004 11:25:43";NULL

27;"IL";"Israel";1;86;"09/30/2004 11:25:43";NULL

28;"CH";"Switzerland";3;84;"09/30/2004 11:25:43";NULL

29;"A1";"Anonymous Proxy";0;0;"09/30/2004 11:25:43";NULL

30;"A2";"Satellite Provider";0;0;"09/30/2004 11:25:43";NULL

31;"AD";"Andorra";3;0;"09/30/2004 11:25:43";NULL

32;"AE";"United Arab Emirates";1;0;"09/30/2004 11:25:43";NULL

33;"AF";"Afghanistan";1;0;"09/30/2004 11:25:43";NULL

34;"AG";"Antigua and Barbuda";7;0;"09/30/2004 11:25:43";NULL

35;"AI";"Anguilla";7;0;"09/30/2004 11:25:43";NULL

36;"AL";"Albania";3;0;"09/30/2004 11:25:43";NULL

37;"AM";"armenia";3;0;"09/30/2004 11:25:43";NULL

38;"AN";"Netherlands Antilles";3;0;"09/30/2004 11:25:43";NULL

39;"AO";"Angola";2;0;"09/30/2004 11:25:43";NULL

40;"AP";"Asia/Pacific Region";2;0;"09/30/2004 11:25:43";NULL

41;"AQ";"Antarctica";8;0;"09/30/2004 11:25:43";NULL

42;"AR";"Argentina";5;0;"09/30/2004 11:25:43";NULL

43;"AS";"American Samoa";6;0;"09/30/2004 11:25:43";NULL

44;"AW";"Aruba";5;0;"09/30/2004 11:25:43";NULL

45;"AZ";"Azerbaijan";1;0;"09/30/2004 11:25:43";NULL

46;"BA";"Bosnia and Herzegovina";3;0;"09/30/2004 11:25:43";NULL

47;"BB";"Barbados";5;0;"09/30/2004 11:25:43";NULL

48;"BD";"Bangladesh";1;0;"09/30/2004 11:25:43";NULL

49;"BF";"Burkina Faso";2;0;"09/30/2004 11:25:43";NULL

50;"BG";"Bulgaria";3;0;"09/30/2004 11:25:43";NULL

51;"BH";"Bahrain";1;0;"09/30/2004 11:25:43";NULL

52;"BI";"Burundi";2;0;"09/30/2004 11:25:43";NULL

53;"BJ";"Benin";2;0;"09/30/2004 11:25:43";NULL

54;"BM";"Bermuda";4;0;"09/30/2004 11:25:43";NULL

55;"BN";"Brunei Darussalam";1;0;"09/30/2004 11:25:43";NULL

56;"BO";"Bolivia";5;0;"09/30/2004 11:25:43";NULL

57;"BS";"Bahamas";7;0;"09/30/2004 11:25:43";NULL

58;"BT";"Bhutan";1;0;"09/30/2004 11:25:43";NULL

59;"BV";"Bouvet Island";5;0;"09/30/2004 11:25:43";NULL

60;"BW";"Botswana";2;0;"09/30/2004 11:25:43";NULL

61;"BY";"Belarus";3;0;"09/30/2004 11:25:43";NULL

2、執(zhí)行導(dǎo)入命令

C:\>sqlldr userid=system/manager control=test.ct

part ii

SQL*Loader是Oracle數(shù)據(jù)庫導(dǎo)入外部數(shù)據(jù)的一個(gè)工具.它和DB2的Load工具相似,但有更多的選擇，它支持變化的加載模式，可選的加載及多表加載.

如何使用 SQL*Loader 工具

我們可以用Oracle的sqlldr工具來導(dǎo)入數(shù)據(jù)。例如:

sqlldr scott/tiger control=loader.ctl

控制文件(loader.ctl) 將加載一個(gè)外部數(shù)據(jù)文件（含分隔符). loader.ctl如下:

load data

infile 'c:\data\mydata.csv'

into table emp

fields terminated by "," optionally enclosed by '"'

( empno, empname, sal, deptno )

mydata.csv 如下:

10001,"Scott Tiger", 1000, 40

10002,"Frank Naude", 500, 20

下面是一個(gè)指定記錄長度的示例控制文件。"*" 代表數(shù)據(jù)文件與此文件同名，即在后面使用BEGINDATA段來標(biāo)識(shí)數(shù)據(jù)。

load data

infile *

replace

into table departments

( dept position (02:05) char(4),

deptname position (08:27) char(20)

)

begindata

COSC COMPUTER SCIENCE

ENGL ENGLISH LITERATURE

MATH MATHEMATICS

POLY POLITICAL SCIENCE

Unloader這樣的工具

Oracle 沒有提供將數(shù)據(jù)導(dǎo)出到一個(gè)文件的工具。但是，我們可以用SQL*Plus的select 及 format 數(shù)據(jù)來輸出到一個(gè)文件：

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on

spool oradata.txt

select col1 || ',' || col2 || ',' || col3

from tab1

where col2 = 'XYZ';

spool off

另外，也可以使用使用 UTL_FILE PL/SQL 包處理:

rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter

declare

fp utl_file.file_type;

begin

fp := utl_file.fopen('c:\oradata','tab1.txt','w');

utl_file.putf(fp, '%s, %s\n', 'TextField', 55);

utl_file.fclose(fp);

end;

當(dāng)然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。

加載可變長度或指定長度的記錄

如：

LOAD DATA

INFILE *

INTO TABLE load_delimited_data

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

( data1,

data2

)

BEGINDATA

11111,AAAAAAAAAA

22222,"A,B,C,D,"

下面是導(dǎo)入固定位置（固定長度）數(shù)據(jù)示例：

LOAD DATA

INFILE *

INTO TABLE load_positional_data

( data1 POSITION(1:5),

data2 POSITION(6:15)

)

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

跳過數(shù)據(jù)行：

可以用 "SKIP n" 關(guān)鍵字來指定導(dǎo)入時(shí)可以跳過多少行數(shù)據(jù)。如：

LOAD DATA

INFILE *

INTO TABLE load_positional_data

SKIP 5

( data1 POSITION(1:5),

data2 POSITION(6:15)

)

BEGINDATA

11111AAAAAAAAAA

22222BBBBBBBBBB

導(dǎo)入數(shù)據(jù)時(shí)修改數(shù)據(jù)：

在導(dǎo)入數(shù)據(jù)到數(shù)據(jù)庫時(shí)，可以修改數(shù)據(jù)。注意，這僅適合于常規(guī)導(dǎo)入，并不適合 direct導(dǎo)入方式.如：

LOAD DATA

INFILE *

INTO TABLE modified_data

( rec_no "my_db_sequence.nextval",

region CONSTANT '31',

time_loaded "to_char(SYSDATE, 'HH24:MI')",

data1 POSITION(1:5) ":data1/100",

data2 POSITION(6:15) "upper(:data2)",

data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"

)

BEGINDATA

11111AAAAAAAAAA991201

22222BBBBBBBBBB990112

LOAD DATA

INFILE 'mail_orders.txt'

BADFILE 'bad_orders.txt'

APPEND

INTO TABLE mailing_list

FIELDS TERMINATED BY ","

( addr,

city,

state,

zipcode,

mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",

mailing_city "decode(:mailing_city, null, :city, :mailing_city)",

mailing_state

)

將數(shù)據(jù)導(dǎo)入多個(gè)表：

如:

LOAD DATA

INFILE *

REPLACE

INTO TABLE emp

WHEN empno != ' '

( empno POSITION(1:4) INTEGER EXTERNAL,

ename POSITION(6:15) CHAR,

deptno POSITION(17:18) CHAR,

mgr POSITION(20:23) INTEGER EXTERNAL

)

INTO TABLE proj

WHEN projno != ' '

( projno POSITION(25:27) INTEGER EXTERNAL,

empno POSITION(1:4) INTEGER EXTERNAL

)

導(dǎo)入選定的記錄：

如下例： (01) 代表第一個(gè)字符, (30:37) 代表30到37之間的字符:

LOAD DATA

INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'

APPEND

INTO TABLE my_selective_table

WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'

(

region CONSTANT '31',

service_key POSITION(01:11) INTEGER EXTERNAL,

call_b_no POSITION(12:29) CHAR

)

導(dǎo)入時(shí)跳過某些字段：

可用 POSTION(x:y) 來分隔數(shù)據(jù). 在Oracle8i中可以通過指定 FILLER 字段實(shí)現(xiàn)。FILLER 字段用來跳過、忽略導(dǎo)入數(shù)據(jù)文件中的字段.如：

LOAD DATA

TRUNCATE INTO TABLE T1

FIELDS TERMINATED BY ','

( field1,

field2 FILLER,

field3

)

導(dǎo)入多行記錄：

可以使用下面兩個(gè)選項(xiàng)之一來實(shí)現(xiàn)將多行數(shù)據(jù)導(dǎo)入為一個(gè)記錄:

CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.

CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

SQL*Loader 數(shù)據(jù)的提交：

一般情況下是在導(dǎo)入數(shù)據(jù)文件數(shù)據(jù)后提交的。

也可以通過指定 ROWS= 參數(shù)來指定每次提交記錄數(shù)。

提高 SQL*Loader 的性能：

1) 一個(gè)簡單而容易忽略的問題是，沒有對(duì)導(dǎo)入的表使用任何索引和/或約束(主鍵)。如果這樣做，甚至在使用ROWS=參數(shù)時(shí)，會(huì)很明顯降低數(shù)據(jù)庫導(dǎo)入性能。

2) 可以添加 DIRECT=TRUE來提高導(dǎo)入數(shù)據(jù)的性能。當(dāng)然，在很多情況下，不能使用此參數(shù)。

3) 通過指定 UNRECOVERABLE選項(xiàng)，可以關(guān)閉數(shù)據(jù)庫的日志。這個(gè)選項(xiàng)只能和 direct 一起使用。

4) 可以同時(shí)運(yùn)行多個(gè)導(dǎo)入任務(wù).

常規(guī)導(dǎo)入與direct導(dǎo)入方式的區(qū)別：

常規(guī)導(dǎo)入可以通過使用 INSERT語句來導(dǎo)入數(shù)據(jù)。Direct導(dǎo)入可以跳過數(shù)據(jù)庫的相關(guān)邏輯(DIRECT=TRUE)，而直接將數(shù)據(jù)導(dǎo)入到數(shù)據(jù)文件中。

posted @ 2010-03-24 15:34 xzc 閱讀(792) | 評(píng)論 (1) | 編輯收藏

j2ee異常處理機(jī)制

轉(zhuǎn)自：http://mofeichen.javaeye.com/blog/557426

異常的處理是每個(gè)Java程序員時(shí)常面對(duì)的問題，但是很多人沒有原則，遇到異常也不知道如何去處理，于是遇到檢查異常就胡亂try...catch...一把，然后e.printStackTrace()一下了事，這種做法通常除了調(diào)試排錯(cuò)有點(diǎn)作用外，沒任何價(jià)值。對(duì)于運(yùn)行時(shí)異常，則干脆置之不理。

　　原因是很多開發(fā)者缺乏對(duì)異常的認(rèn)識(shí)和分析，首先應(yīng)該明白Java異常體系結(jié)構(gòu)，一種分層繼承的關(guān)系，你必須對(duì)層次結(jié)構(gòu)熟爛于心：

　　Throwable(必須檢查)

　　Error(非必須檢查)

　　Exception(必須檢查)

　　RuntimeException(非必須檢查)

　　一般把Exception異常及其直接子類(除了RuntimeException之外)的異常稱之為檢查異常。把RuntimeException以及其子類的異常稱之為非檢查異常，也叫運(yùn)行時(shí)異常。

　　對(duì)于Throwable和Error，則用的很少，一般會(huì)用在一些基礎(chǔ)框架中，這里不做討論。

　　下面針對(duì)J2EE的分層架構(gòu)：DAO層、業(yè)務(wù)層、控制層、展示層的異常處理做個(gè)分析，并給出一般處理準(zhǔn)則。

　　一、DAO層異常處理

　　如果你用了Spring的DAO模板來實(shí)現(xiàn)，則DAO層沒有檢查異常拋出，代碼非常的優(yōu)雅。但是，如果你的DAO采用了原始的JDBC來寫，這時(shí)候，你不能不對(duì)異常做處理了，因?yàn)殡y以避免的SQLException會(huì)如影隨形的跟著你。對(duì)已這種DAO級(jí)別的異常，異常了你又能如何呢?與其這樣胡亂try...catch...，囫圇吞棗消滅了異常不如讓異常以另外一種非檢查的方式向外傳遞。這樣做好處有二：

　　1)、DAO的接口不被異常所污染，假設(shè)你拋出了SQLException，以后要是換了Spring DAO模板，那DAO接口就不再拋出了SQLException，這樣，你的接口拋出異常就是對(duì)接口的污染。

　　2)、DAO異常向外傳播給更高層處理，以便異常的錯(cuò)誤原因不丟失，便于排查錯(cuò)誤或進(jìn)行捕獲處理。

　　這里還有一個(gè)設(shè)計(jì)上常常令人困擾的問題：很多人會(huì)問，那定義一個(gè)什么樣的異常拋出呢，或者是直接拋出一個(gè)throw RuntimeException(e)? 對(duì)于這個(gè)問題，需要分場合，如果系統(tǒng)小，你可以直接拋出一個(gè)throw RuntimeException(e)，但對(duì)于一個(gè)龐大的多模塊系統(tǒng)來說，不要拋這種原生的非檢查異常，而要拋出自定義的非檢查異常，這樣不但利于排錯(cuò)，而且有利于系統(tǒng)異常的處理，通常針對(duì)每一個(gè)模塊，粗粒度的定義一個(gè)運(yùn)行時(shí)DAO異常。比如：throw new ModelXxxDAORuntimeException(".....",e)，對(duì)于msg信息，你可寫也可不寫，根據(jù)需要靈活拋出。

　　這里常見一個(gè)很愚昧的處理方式，為每個(gè)DAO定義一個(gè)異常，呵呵，這樣累不累啊，有多大意義，在Service層中調(diào)用時(shí)候，如果要捕獲，還要捕獲出一堆異常。這樣致命的問題是代碼混亂，維護(hù)困難，閱讀也困難，DAO的異常應(yīng)該是粗粒度的。

　　二、業(yè)務(wù)層異常處理

　　習(xí)慣上把業(yè)務(wù)層稱之為Service層或者服務(wù)層，Service層的代表的是業(yè)務(wù)邏輯，不要迷信分太多太多層有多大好處，除非需要，否則別盲目劃分不必要的層，層越多，效率越差，根據(jù)需要夠用就行了。

　　Service接口中的每個(gè)方法代表一個(gè)特定的業(yè)務(wù)，而這個(gè)業(yè)務(wù)一定是一個(gè)完整的業(yè)務(wù)，通常會(huì)看到一些傻X的做法，數(shù)據(jù)庫事務(wù)配置在Service層，而Service的實(shí)現(xiàn)就是DAO的直接調(diào)用，然后在控制層(Action)中，調(diào)用了好多Service去完成一個(gè)業(yè)務(wù)，你氣得已經(jīng)無語了，低頭找磚頭去!!!

　　搞明白以上兩個(gè)問題后再回過頭看異常怎么處理，Service層通常依賴DAO，而Service層的通常也會(huì)因?yàn)檎{(diào)用別的非檢查異常方法而必須面對(duì)異常處理的問題，這里和DAO層又有所不同，彼一時(shí)，此一時(shí)嘛!

　　一般來說一個(gè)小模塊對(duì)應(yīng)一個(gè)Service，當(dāng)然也許有兩個(gè)或多個(gè)，針對(duì)這個(gè)模塊的Service定義一個(gè)非檢查異常，以應(yīng)付那些不可避免的異常檢查，這個(gè)自定義異常可以簡單的命名為XxxServiceRuntimeException，將捕獲到的異常順勢(shì)轉(zhuǎn)譯為非檢查異常后拋出。我喜歡這么做，因?yàn)榍芭_(tái)是J2EE應(yīng)用，前臺(tái)是web頁面，它們的Struts2等框架會(huì)自動(dòng)捕獲所有Service層的異常，并把異常交給開發(fā)者去自由處理。

　　但是還有一種情況，由于一些特殊的限制，如果某個(gè)異常一旦發(fā)生，必須做什么什么處理，而這種處理時(shí)硬性要求，或者調(diào)用某個(gè)Service方法，必須檢查處理什么異常，也可以拋出非檢查的自定義異常，往往出現(xiàn)這種情況的是政治原因。不推崇這種做法，但也不排斥。

　　總之，對(duì)于接口，盡可能不去用異常污染她!

　　三、控制層異常

　　控制層說的簡單些就是常見的Action層，主要是控制頁面請(qǐng)求的處理。控制層通常都依賴于Service層，現(xiàn)在比較流行的框架對(duì)控制層做得都相當(dāng)?shù)牡轿唬热鏢truts2、SpringMVC等等，他們的控制層框架會(huì)捕獲業(yè)務(wù)層的所有異常，并在控制層中聲明可能拋出Exception，因此控制層一般不處理什么異常。

　　如果是控制層中因?yàn)檎{(diào)用了一些非檢查異常的方法，比如IO操作等，可以簡單處理下異常，保證流的安全，這才是目的。

　　四、顯示層異常處理

　　對(duì)于頁面異常，處理的方式多種多樣，一是不處理異常，一旦異常了，頁面就報(bào)錯(cuò)。二是定義出錯(cuò)頁面，根據(jù)異常的類型以及所在的模塊，導(dǎo)航到出錯(cuò)頁面。

　　一般來說，出錯(cuò)頁面是更友好的做法。

　　另外還有特殊的處理方式，展示頁面的模板可以捕獲異常，并根據(jù)情況將異常信息鋪到相應(yīng)的位置，這樣就更友好了，不過復(fù)雜度較高。

　　怎么處理，就看需要了。

　　五、總結(jié)

　　1)、對(duì)于異常處理，應(yīng)該從設(shè)計(jì)、需要、維護(hù)等多個(gè)角度綜合考慮，有一個(gè)通用準(zhǔn)則：千萬別捕獲了異常什么事情都不干，這樣一旦出現(xiàn)異常了，你沒法依據(jù)異常信息來排錯(cuò)。

　　2)、對(duì)于J2EE多層架構(gòu)系統(tǒng)來說，盡可能避免(因拋出異常帶來的)接口污染。

posted @ 2010-03-13 11:59 xzc 閱讀(636) | 評(píng)論 (0) | 編輯收藏

rowid 刪除重復(fù)記錄！！！

平時(shí)工作中可能會(huì)遇到當(dāng)試圖對(duì)庫表中的某一列或幾列創(chuàng)建唯一索引時(shí)，系統(tǒng)提示 ORA-01452 ：不能創(chuàng)建唯一索引，發(fā)現(xiàn)重復(fù)記錄。

下面總結(jié)一下幾種查找和刪除重復(fù)記錄的方法（以表CZ為例）：
表CZ的結(jié)構(gòu)如下：
SQL> desc cz
Name Null? Type
----------------------------------------- -------- ------------------

C1 NUMBER(10)
C10 NUMBER(5)
C20 VARCHAR2(3)

刪除重復(fù)記錄的方法原理：
(1).在Oracle中，每一條記錄都有一個(gè)rowid，rowid在整個(gè)數(shù)據(jù)庫中是唯一的，rowid確定了每條記錄是在Oracle中的哪一個(gè)數(shù)據(jù)文件、塊、行上。

(2).在重復(fù)的記錄中，可能所有列的內(nèi)容都相同，但rowid不會(huì)相同，所以只要確定出重復(fù)記錄中那些具有最大rowid的就可以了，其余全部刪除。

重復(fù)記錄判斷的標(biāo)準(zhǔn)是：
C1,C10和C20這三列的值都相同才算是重復(fù)記錄。

經(jīng)查看表CZ總共有16條記錄：
SQL>set pagesize 100
SQL>select * from cz;

C1 C10 C20
---------- ---------- ---
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
2 3 che
2 3 che
2 3 che
3 4 dff
3 4 dff
3 4 dff
4 5 err
5 3 dar
6 1 wee
7 2 zxc

20 rows selected.

1.查找重復(fù)記錄的幾種方法：
(1).SQL>select * from cz group by c1,c10,c20 having count(*) >1;
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff

(2).SQL>select distinct * from cz;C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff

(3).SQL>select * from cz a where rowid=(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);
C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff

2.刪除重復(fù)記錄的幾種方法：
(1).適用于有大量重復(fù)記錄的情況(在C1,C10和C20列上建有索引的時(shí)候，用以下語句效率會(huì)很高)：

SQL>delete cz where (c1,c10,c20) in (select c1,c10,c20 from cz group by c1,c10,c20 having count(*)>1) and rowid not in
(select min(rowid) from cz group by c1,c10,c20 having count(*)>1);

SQL>delete cz where rowid not in(select min(rowid) from cz group by c1,c10,c20);

(2).適用于有少量重復(fù)記錄的情況(注意，對(duì)于有大量重復(fù)記錄的情況，用以下語句效率會(huì)很低)：

SQL>delete from cz a where a.rowid!=(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);

SQL>delete from cz a where a.rowid<(select max(rowid) from cz b where a.c1=b.c1 and a.c10=b.c10 and a.c20=b.c20);

SQL>delete from cz a where rowid <(select max(rowid) from cz where c1=a.c1 and c10=a.c10 and c20=a.c20);

(3).適用于有少量重復(fù)記錄的情況(臨時(shí)表法) --超級(jí)土的辦法
SQL>create table test as select distinct * from cz; (建一個(gè)臨時(shí)表test用來存放重復(fù)的記錄）

SQL>truncate table cz; (清空cz表的數(shù)據(jù)，但保留cz表的結(jié)構(gòu)）

SQL>insert into cz select * from test; (再將臨時(shí)表test里的內(nèi)容反插回來）

(4).適用于有大量重復(fù)記錄的情況(Exception into 子句法)： --很有意思的一個(gè)辦法
采用alter table 命令中的 Exception into 子句也可以確定出庫表中重復(fù)的記錄。這種方法稍微麻煩一些，為了使用“excepeion into ”子句，必須首先創(chuàng)建 EXCEPTIONS 表。創(chuàng)建該表的 SQL 腳本文件為 utlexcpt.sql 。對(duì)于win2000系統(tǒng)和 UNIX 系統(tǒng)， Oracle 存放該文件的位置稍有不同，在win2000系統(tǒng)下，該腳本文件存放在$ORACLE_HOME\Ora90\rdbms\admin 目錄下；而對(duì)于 UNIX 系統(tǒng)，該腳本文件存放在$ORACLE_HOME/rdbms/admin 目錄下。

具體步驟如下：
SQL>@?/rdbms/admin/utlexcpt.sql

Table created.

SQL>desc exceptions
Name Null? Type
----------------------------------------- -------- --------------

ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)

SQL>alter table cz add constraint cz_unique unique(c1,c10,c20) exceptions into exceptions;
*
ERROR at line 1:
ORA-02299: cannot validate (TEST.CZ_UNIQUE) - duplicate keys found

SQL>create table dups as select * from cz where rowid in (select row_id from exceptions);

Table created.

SQL>select * from dups;

C1 C10 C20
---------- ---------- ---
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
1 2 dsf
1 2 dsf
1 2 dsf
1 2 dsf
2 3 che
2 3 che
2 3 che
2 3 che
3 4 dff
3 4 dff
3 4 dff

16 rows selected.

SQL>select row_id from exceptions;

ROW_ID
------------------
AAAHD/AAIAAAADSAAA
AAAHD/AAIAAAADSAAB
AAAHD/AAIAAAADSAAC
AAAHD/AAIAAAADSAAF
AAAHD/AAIAAAADSAAH
AAAHD/AAIAAAADSAAI
AAAHD/AAIAAAADSAAG
AAAHD/AAIAAAADSAAD
AAAHD/AAIAAAADSAAE
AAAHD/AAIAAAADSAAJ
AAAHD/AAIAAAADSAAK
AAAHD/AAIAAAADSAAL
AAAHD/AAIAAAADSAAM
AAAHD/AAIAAAADSAAN
AAAHD/AAIAAAADSAAO
AAAHD/AAIAAAADSAAP

16 rows selected.

SQL>delete from cz where rowid in ( select row_id from exceptions);

16 rows deleted.

SQL>insert into cz select distinct * from dups;

3 rows created.

SQL>select *from cz;

C1 C10 C20
---------- ---------- ---
1 2 dsf
2 3 che
3 4 dff
4 5 err
5 3 dar
6 1 wee
7 2 zxc

7 rows selected.

從結(jié)果里可以看到重復(fù)記錄已經(jīng)刪除。

posted @ 2010-03-06 12:03 xzc 閱讀(1029) | 評(píng)論 (8) | 編輯收藏

Oracle中start with...connect by子句的用法

Oracle中start with…connect by prior子句用法

connect by 是結(jié)構(gòu)化查詢中用到的，其基本語法是：
select … from tablename

start with 條件1
connect by 條件2
where 條件3;
例：
select * from table
start with org_id = ‘HBHqfWGWPy’
connect by prior org_id = parent_id;

簡單說來是將一個(gè)樹狀結(jié)構(gòu)存儲(chǔ)在一張表里，比如一個(gè)表中存在兩個(gè)字段:
org_id，parent_id那么通過表示每一條記錄的parent是誰，就可以形成一個(gè)樹狀結(jié)構(gòu)。

        用上述語法的查詢可以取得這棵樹的所有記錄。
        其中：
        條件1 是根結(jié)點(diǎn)的限定語句，當(dāng)然可以放寬限定條件，以取得多個(gè)根結(jié)點(diǎn)，實(shí)際就是多棵樹。
        條件2 是連接條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR org_id = parent_id；就是說上一條記錄的org_id 是本條記錄的parent_id，即本記錄的父親是上一條記錄。
        條件3 是過濾條件，用于對(duì)返回的所有記錄進(jìn)行過濾。

        簡單介紹如下：
        在掃描樹結(jié)構(gòu)表時(shí)，需要依此訪問樹結(jié)構(gòu)的每個(gè)節(jié)點(diǎn)，一個(gè)節(jié)點(diǎn)只能訪問一次，其訪問的步驟如下：
        第一步：從根節(jié)點(diǎn)開始；
        第二步：訪問該節(jié)點(diǎn)；
        第三步：判斷該節(jié)點(diǎn)有無未被訪問的子節(jié)點(diǎn)，若有，則轉(zhuǎn)向它最左側(cè)的未被訪問的子節(jié)，并執(zhí)行第二步，否則執(zhí)行第四步；
        第四步：若該節(jié)點(diǎn)為根節(jié)點(diǎn)，則訪問完畢，否則執(zhí)行第五步；
        第五步：返回到該節(jié)點(diǎn)的父節(jié)點(diǎn)，并執(zhí)行第三步驟。
        總之：掃描整個(gè)樹結(jié)構(gòu)的過程也即是中序遍歷樹的過程。

1．樹結(jié)構(gòu)的描述
        樹結(jié)構(gòu)的數(shù)據(jù)存放在表中，數(shù)據(jù)之間的層次關(guān)系即父子關(guān)系，通過表中的列與列間的關(guān)系來描述，如EMP表中的EMPNO和MGR。EMPNO表示該雇員的編號(hào)，MGR表示領(lǐng)導(dǎo)該雇員的人的編號(hào)，即子節(jié)點(diǎn)的MGR值等于父節(jié)點(diǎn)的EMPNO值。在表的每一行中都有一個(gè)表示父節(jié)點(diǎn)的MGR（除根節(jié)點(diǎn)外），通過每個(gè)節(jié)點(diǎn)的父節(jié)點(diǎn)，就可以確定整個(gè)樹結(jié)構(gòu)。
         在SELECT命令中使用CONNECT BY 和START WITH 子句可以查詢表中的樹型結(jié)構(gòu)關(guān)系。其命令格式如下：
SELECT . . .
CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 裂名2}
[START WITH]；
       其中：CONNECT BY子句說明每行數(shù)據(jù)將是按層次順序檢索，并規(guī)定將表中的數(shù)據(jù)連入樹型結(jié)構(gòu)的關(guān)系中。PRIOR運(yùn)算符必須放置在連接關(guān)系的兩列中某一個(gè)的前面。對(duì)于節(jié)點(diǎn)間的父子關(guān)系，PRIOR運(yùn)算符在一側(cè)表示父節(jié)點(diǎn)，在另一側(cè)表示子節(jié)點(diǎn)，從而確定查找樹結(jié)構(gòu)是的順序是自頂向下還是自底向上。

在連接關(guān)系中，除了可以使用列名外，還允許使用列表達(dá)式。START WITH 子句為可選項(xiàng)，用來標(biāo)識(shí)哪個(gè)節(jié)點(diǎn)作為查找樹型結(jié)構(gòu)的根節(jié)點(diǎn)。若該子句被省略，則表示所有滿足查詢條件的行作為根節(jié)點(diǎn)。
START WITH：不但可以指定一個(gè)根節(jié)點(diǎn)，還可以指定多個(gè)根節(jié)點(diǎn)。

2．關(guān)于PRIOR
        運(yùn)算符PRIOR被放置于等號(hào)前后的位置，決定著查詢時(shí)的檢索順序。
        PRIOR被置于CONNECT BY子句中等號(hào)的前面時(shí)，則強(qiáng)制從根節(jié)點(diǎn)到葉節(jié)點(diǎn)的順序檢索，即由父節(jié)點(diǎn)向子節(jié)點(diǎn)方向通過樹結(jié)構(gòu)，我們稱之為自頂向下的方式。如：
         CONNECT BY PRIOR EMPNO=MGR
         PIROR運(yùn)算符被置于CONNECT BY 子句中等號(hào)的后面時(shí)，則強(qiáng)制從葉節(jié)點(diǎn)到根節(jié)點(diǎn)的順序檢索，即由子節(jié)點(diǎn)向父節(jié)點(diǎn)方向通過樹結(jié)構(gòu)，我們稱之為自底向上的方式。例如：
         CONNECT BY EMPNO=PRIOR MGR
         在這種方式中也應(yīng)指定一個(gè)開始的節(jié)點(diǎn)。

3．定義查找起始節(jié)點(diǎn)
在自頂向下查詢樹結(jié)構(gòu)時(shí)，不但可以從根節(jié)點(diǎn)開始，還可以定義任何節(jié)點(diǎn)為起始節(jié)點(diǎn)，以此開始向下查找。這樣查找的結(jié)果就是以該節(jié)點(diǎn)為開始的結(jié)構(gòu)樹的一枝。

4．使用LEVEL
在具有樹結(jié)構(gòu)的表中，每一行數(shù)據(jù)都是樹結(jié)構(gòu)中的一個(gè)節(jié)點(diǎn)，由于節(jié)點(diǎn)所處的層次位置不同，所以每行記錄都可以有一個(gè)層號(hào)。層號(hào)根據(jù)節(jié)點(diǎn)與根節(jié)點(diǎn)的距離確定。不論從哪個(gè)節(jié)點(diǎn)開始，該起始根節(jié)點(diǎn)的層號(hào)始終為1，根節(jié)點(diǎn)的子節(jié)點(diǎn)為2，依此類推。圖1.2就表示了樹結(jié)構(gòu)的層次。

5．節(jié)點(diǎn)和分支的裁剪
在對(duì)樹結(jié)構(gòu)進(jìn)行查詢時(shí)，可以去掉表中的某些行，也可以剪掉樹中的一個(gè)分支，使用WHERE子句來限定樹型結(jié)構(gòu)中的單個(gè)節(jié)點(diǎn)，以去掉樹中的單個(gè)節(jié)點(diǎn)，但它卻不影響其后代節(jié)點(diǎn)（自頂向下檢索時(shí)）或前輩節(jié)點(diǎn)（自底向頂檢索時(shí)）。

6．排序顯示
象在其它查詢中一樣，在樹結(jié)構(gòu)查詢中也可以使用ORDER BY 子句，改變查詢結(jié)果的顯示順序，而不必按照遍歷樹結(jié)構(gòu)的順序。

posted @ 2010-03-05 18:02 xzc 閱讀(49754) | 評(píng)論 (2) | 編輯收藏

PowerDesigner 中將Comment(注釋)及Name(名稱)內(nèi)容互相COPY的VBS代碼

【轉(zhuǎn)】PowerDesigner 中將Comment(注釋)及Name(名稱)內(nèi)容互相COPY的VBS代碼

2009-12-03 15:06

PowerDesigner->Tools->Execute Commands->Edit/Run Scripts

代碼一:將Name中的字符COPY至Comment中

'******************************************************************************

'* File: name2comment.vbs

'* Purpose: Database generation cannot use object names anymore

' in version 7 and above.

' It always uses the object codes.

' In case the object codes are not aligned with your

' object names in your model, this script will copy

' the object Name onto the object Comment for

' the Tables and Columns.

'* Title:

'* Version: 1.0

'* Company: Sybase Inc.

'******************************************************************************

Option Explicit

ValidationMode = True

InteractiveMode = im_Batch

Dim mdl ' the current model

' get the current active model

Set mdl = ActiveModel

If (mdl Is Nothing) Then

MsgBox "There is no current Model "

ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then

MsgBox "The current model is not an Physical Data model. "

Else

ProcessFolder mdl

End If

' This routine copy name into comment for each table, each column and each view

' of the current folder

Private sub ProcessFolder(folder)

Dim Tab 'running table

for each Tab in folder.tables

if not tab.isShortcut then

tab.comment = tab.name

Dim col ' running column

for each col in tab.columns

col.comment= col.name

end if

Dim view 'running view

for each view in folder.Views

if not view.isShortcut then

view.comment = view.name

end if

' go into the sub-packages

Dim f ' running folder

For Each f In folder.Packages

if not f.IsShortcut then

ProcessFolder f

end if

end sub

代碼二:將Comment中的字符COPY至Name中

Option Explicit

ValidationMode = True

InteractiveMode = im_Batch

Dim mdl ' the current model

' get the current active model

Set mdl = ActiveModel

If (mdl Is Nothing) Then

MsgBox "There is no current Model "

ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then

MsgBox "The current model is not an Physical Data model. "

Else

ProcessFolder mdl

End If

Private sub ProcessFolder(folder)

On Error Resume Next

Dim Tab 'running table

for each Tab in folder.tables

if not tab.isShortcut then

tab.name = tab.comment

Dim col ' running column

for each col in tab.columns

if col.comment="" then

else

col.name= col.comment

end if

Dim view 'running view

for each view in folder.Views

if not view.isShortcut then

view.name = view.comment

end if

' go into the sub-packages

Dim f ' running folder

For Each f In folder.Packages

if not f.IsShortcut then

ProcessFolder f

end if

end sub

posted @ 2010-03-03 16:09 xzc 閱讀(1047) | 評(píng)論 (0) | 編輯收藏

ORA-29538、ORA-29532、ORA-29913問題解決

轉(zhuǎn)：http://www.cnblogs.com/jimeper/archive/2009/02/16/1391805.html

問題一：
ERROR at line 1: ORA-29538: Java not installed
解決方法
1.檢查有沒有安裝JAVA組件
select * from v$option t where t.PARAMETER='Java';
如果返回行說明已安裝，如果沒有返回行，運(yùn)行Oracle Universal Installer安裝JAVA組件
2.如果在第1步返回行，則檢查oracle中有沒有dbms_java.
select distinct owner,name from dba_source where lower(NAME)='dbms_java';
如果沒有返回行，執(zhí)行第3步
3.在sqlplus下以sys登陸，執(zhí)行$ORACLE_HOME/javavm/install/initjvm.sql
SQL>@?/javavm/install/initjvm.sql;

問題二：
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.io.FilePermission
/home/accmgrctl/src/server read) has not been granted to SQLVIEW. The PL/SQL to
grant this is dbms_java.grant_permission( 'SQLVIEW',
'SYS:java.io.FilePermission', '/home/accmgrctl/src/server', 'read' )
ORA-06512: at "SQLVIEW.PKG_FILE_API", line 1
解決方法：
這是由于oracle用戶沒有訪問那個(gè)目錄的權(quán)限，把源代碼入在oracle有權(quán)限訪問的目錄下
或者用以下語句授權(quán)
EXEC Dbms_Java.Grant_Permission('oracle_username','java.io.FilePermission', '*','read ');

問題三：
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00552: internal XAD package failed to load
ORA-06512: at "SYS.ORACLE_LOADER", line 19
解決方法：
這是110202 上的又一新BUG(外部表的讀取)
Need to replace the language specific (non-english) kup<lang>.msb file
with the english version.

1. cd $ORACLE_HOME/rdbms/mesg
2. Replace <lang> with your installed languages file.
mv KUP<lang>.msb to KUP<lang>.msb.BAK
3. Copy <us> version over current <lang> copy of kup msb file.
cp kupus.msb to KUP<lang>.msb
4. re-run the select against the external table

我實(shí)際的操作過程，就是：

該目錄：$ORACLE_HOME/rdbms/mesg 下有兩個(gè)文件：
kupzhs.msb 和 kupus.msb, 其默認(rèn)使用了kupzhs.msb
此時(shí)，我把kupzhs.msb 重新命名為 kupzhs.msb.bak
之后在測試， OK：

人生有三寶:終身運(yùn)動(dòng),終身學(xué)習(xí),終身反醒.吸收新知,提高效率,懂得相處,成就自己,也成就他人,創(chuàng)造最高價(jià)值。

Tag標(biāo)簽: ORA-29538,ORA-29532,ORA-29913

posted @ 2010-02-09 17:43 xzc 閱讀(2268) | 評(píng)論 (0) | 編輯收藏

proxool連接池配置詳細(xì)說明

摘要: 目前市面上三個(gè)主流連接池從性能上排名如下：proxool>c3p0>dbcp，proxool還提供了可視化的連接池實(shí)時(shí)監(jiān)控工具，所以既穩(wěn)定又方便，配置也是非常容易的事情。下面我來講講我如何配置proxool連接池的。 1、下載相關(guān)資源。從http://pr... 閱讀全文

posted @ 2010-01-30 12:02 xzc 閱讀(2003) | 評(píng)論 (0) | 編輯收藏

Hibernate的三種連接池設(shè)置C3P0、Proxool和DBCP

Xml代碼

<property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.url">jdbc:mysql://localhost:3306/struts?useUnicode=true&characterEncoding=GBK</property>
<property name="connection.username">root</property>
<property name="connection.password">8888</property>

<!-- JDBC驅(qū)動(dòng)程序 --> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.url">jdbc:mysql://localhost:3306/struts?useUnicode=true&characterEncoding=GBK</property> <!-- 數(shù)據(jù)庫用戶名 --> <property name="connection.username">root</property> <!-- 數(shù)據(jù)庫密碼 --> <property name="connection.password">8888</property>

上面的一段配置，在c3p0和dbcp中，都是必需的，因?yàn)閔ibernate會(huì)根據(jù)上述的配置來生成connections，再交給c3p0或dbcp管理.

1 C3P0

只需在hibernate.cfg.xml中加入

Xml代碼

<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">30</property>
<property name="c3p0.time_out">1800</property>
<property name="c3p0.max_statement">50</property>

<property name="c3p0.min_size">5</property> <property name="c3p0.max_size">30</property> <property name="c3p0.time_out">1800</property> <property name="c3p0.max_statement">50</property>

還有在classespath中加入c3p0-0.8.4.5.jar

2 dbcp

在hibernate.cfg.xml中加入

Xml代碼

<property name="dbcp.maxActive">100</property>
<property name="dbcp.whenExhaustedAction">1</property>
<property name="dbcp.maxWait">60000</property>
<property name="dbcp.maxIdle">10</property>
<property name="dbcp.ps.maxActive">100</property>
<property name="dbcp.ps.whenExhaustedAction">1</property>
<property name="dbcp.ps.maxWait">60000</property>
<property name="dbcp.ps.maxIdle">10</property>

<property name="dbcp.maxActive">100</property> <property name="dbcp.whenExhaustedAction">1</property> <property name="dbcp.maxWait">60000</property> <property name="dbcp.maxIdle">10</property> <property name="dbcp.ps.maxActive">100</property> <property name="dbcp.ps.whenExhaustedAction">1</property> <property name="dbcp.ps.maxWait">60000</property> <property name="dbcp.ps.maxIdle">10</property>

還有在classespath中加入commons-pool-1.2.jar 和commons-dbcp-1.2.1.jar.

3 proxool

由于數(shù)據(jù)庫connection在較長時(shí)間沒有訪問下會(huì)自動(dòng)斷開連接，導(dǎo)致瀏覽出錯(cuò)，增加proxool作為數(shù)據(jù)庫pool。它有自動(dòng)連接功能。
1)、從http://proxool.sourceforge...下載proxool，釋放proxool.jar到WEB-INF/lib

2)、在hibernate.cfg.xml中增加：

Xml代碼

<property name="hibernate.proxool.pool_alias">dbpool</property>
<property name="hibernate.proxool.xml">proxool.xml</property>
<property name="connection.provider_class">org.hibernate.connection.ProxoolConnectionProvider</property>

<property name="hibernate.proxool.pool_alias">dbpool</property> <property name="hibernate.proxool.xml">proxool.xml</property> <property name="connection.provider_class">org.hibernate.connection.ProxoolConnectionProvider</property>

3)、在與hibernate.cfg.xml同級(jí)目錄（src根目錄下）增加proxool.xml文件：

Xml代碼

<?xml version="1.0" encoding="utf-8"?>
<!-- the proxool configuration can be embedded within your own application's.
Anything outside the "proxool" tag is ignored. -->
<something-else-entirely>
<proxool>
<alias>dbpool</alias>
<driver-url>
jdbc:mysql://127.0.0.1:3306/wlsh?characterEncoding=GBK&useUnicode=true&autoReconnect=true </driver-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver-properties>
<property name="user" value="root" />
<property name="password" value="123456" />
</driver-properties>
<house-keeping-sleep-time>90000</house-keeping-sleep-time>
<prototype-count>5</prototype-count>
<maximum-connection-count>100</maximum-connection-count>
<minimum-connection-count>10</minimum-connection-count>
</proxool>
</something-else-entirely>

<?xml version="1.0" encoding="utf-8"?> <!-- the proxool configuration can be embedded within your own application's. Anything outside the "proxool" tag is ignored. --> <something-else-entirely> <proxool> <alias>dbpool</alias> <!--proxool只能管理由自己產(chǎn)生的連接--> <driver-url> jdbc:mysql://127.0.0.1:3306/wlsh?characterEncoding=GBK&useUnicode=true&autoReconnect=true </driver-url> <driver-class>com.mysql.jdbc.Driver</driver-class> <driver-properties> <property name="user" value="root" /> <property name="password" value="123456" /> </driver-properties> <!-- proxool自動(dòng)偵察各個(gè)連接狀態(tài)的時(shí)間間隔(毫秒),偵察到空閑的連接就馬上回收,超時(shí)的銷毀--> <house-keeping-sleep-time>90000</house-keeping-sleep-time> <!-- 最少保持的空閑連接數(shù)--> <prototype-count>5</prototype-count> <!-- 允許最大連接數(shù),超過了這個(gè)連接，再有請(qǐng)求時(shí)，就排在隊(duì)列中等候，最大的等待請(qǐng)求數(shù)由maximum-new-connections決定--> <maximum-connection-count>100</maximum-connection-count> <!-- 最小連接數(shù)--> <minimum-connection-count>10</minimum-connection-count> </proxool></something-else-entirely>

于在hibernate3.0中，已經(jīng)不再支持dbcp了，hibernate的作者在hibernate.org中，明確指出在實(shí)踐中發(fā)現(xiàn)dbcp有 BUG,在某些種情會(huì)產(chǎn)生很多空連接不能釋放，所以拋棄了對(duì)dbcp的支持。至于c3p0，有評(píng)論說它的算法不是最優(yōu)的，因?yàn)榫W(wǎng)上查資料得知：有網(wǎng)友做了一個(gè)實(shí)驗(yàn)，在同一項(xiàng)目中分別用了幾個(gè)常用的連接池，然后測試其性能，發(fā)現(xiàn)c3p0占用資源比較大，效率也不高。所以，基于上述原因，proxool不少行家推薦使用，而且暫時(shí)來說，是負(fù)面評(píng)價(jià)是最少的一個(gè)。在三星中也有項(xiàng)目是用proxool的。從性能和出錯(cuò)率來說，proxool稍微比前兩種好些。C3P0，穩(wěn)定性似乎不錯(cuò)，在這方面似乎有很好的口碑。至于性能，應(yīng)該不是最好的，算是中規(guī)中矩的類型。
　　Proxool的口碑似乎很好，不大見到負(fù)面的評(píng)價(jià)，從官方資料上來看，有許多有用的特性和特點(diǎn)，也是許多人推薦的。

posted @ 2010-01-30 12:00 xzc 閱讀(3987) | 評(píng)論 (0) | 編輯收藏

僅列出標(biāo)題

常用鏈接

留言簿(12)

隨筆分類

隨筆檔案

收藏夾

搜索

最新評(píng)論

閱讀排行榜

評(píng)論排行榜