淘寶網(wǎng)開發(fā)人員數(shù)據(jù)庫知識手冊

淘寶網(wǎng)開發(fā)人員數(shù)據(jù)庫知識參考手

冊

編寫、修訂、審核記錄

編號文檔版本修訂章節(jié)修訂原因修訂日期修訂人

11.0全文新建文檔2007-9-27勝通

22.0補充文檔2008-1-6勝通

3

4

5

6

7

8

9

10

目錄

第一章基本元素..................................................................6

第一節(jié)數(shù)據(jù)類型................................................................6

1.VARCHAR2(size)：...............................................................................................................6

2.CHAR(size)...........................................................................................................................6

3.LONG.........................................................................................................................................7

4.CLOB.........................................................................................................................................7

5.NUMBER(p,s)....................................................................................................................7

6.DATE.........................................................................................................................................8

7.TIMESTAMP(factional_secondsprecision)....................................................................8

第二節(jié)NULL.......................................................................................................................................8

第三節(jié)偽列...................................................................11

1.CURRVALfaNEXTVAL........................................................................................................11

2.LEVEL.....................................................................................................................................12

3.ROWID....................................................................................................................................12

4.ROWNUM................................................................................................................................13

5.XMLDATA................................................................................................................................15

第四節(jié)注釋...................................................................15

第五節(jié)數(shù)據(jù)庫的對象...........................................................16

I.模式對象....................................................................16

2.非模式對象..................................................................17

3.部分模式對象................................................................17

第六節(jié)格式模型...............................................................17

1.時間格式....................................................................17

第七節(jié)操作符.................................................................19

1.操作符列表..................................................................19

2.操作符優(yōu)先級...............................................................19

3.連接操作符..................................................................19

4.集合操作符..................................................................19

第二章表達式...................................................................19

第三章條件.....................................................................19

第一節(jié)條件的優(yōu)先級...........................................................20

第二節(jié)比較條件...............................................................21

第三節(jié)邏輯條件...............................................................23

第四節(jié)成員關系條件...........................................................23

第五節(jié)范圍條件...............................................................23

第六節(jié)NULL條件..............................................................23

第四章函數(shù).....................................................................23

第一節(jié)單值函數(shù)...............................................................23

1.數(shù)值型函數(shù).................................................................24

<MOD.............................................................................................................................................................24

4ROUND........................................................................................................................................................24

令TRUNC...........................................................................................................................................................26

令ABS................................................................................................................................................................26

令ACOS.............................................................................................................................................................26

令ASIN...............................................................................................................................................................27

令ATAN..............................................................................................................................................................27

令ATAN2............................................................................................................................................................28

0BIN_TO_NUM..............................................................................................................................................29

令BITAND.........................................................................................................................................................29

?CEIL...............................................................................................................................................................29

令COS................................................................................................................................................................29

OFLOOR...........................................................................................................................................................29

令SIN..................................................................................................................................................................29

QSINH...............................................................................................................................................................29

令........................................................................................................29

2.返回字符值的字符型函數(shù).....................................................................................................................29

令LOWER..........................................................................................................................................................29

令UPPER............................................................................................................................................................30

令LPAD..............................................................................................................................................................30

令RPAD..............................................................................................................................................................31

令TRIM..............................................................................................................................................................31

令UTRIM............................................................................................................................................................32

令RTRIM............................................................................................................................................................32

0REPLACE.....................................................................................................................................................33

令SOUNDEX....................................................................................................................................................33

?SUBSTR.........................................................................................................................................................34

令TRANSLATE................................................................................................................................................35

O..............................................................................................................................................................................36

3.返回數(shù)字值的字符型函數(shù).......................................................................................................................36

QINSTR..............................................................................................................................................................36

令LENGTH.........................................................................................................................................................37

。........................................................................................................37

4.日期函數(shù)........................................................................................................................................................37

令ADD_MONTHS............................................................................................................................................38

令CURRENT.DATE........................................................................................................................................39

令CURRENT_TIMESTAMP..........................................................................................................................40

令SYSDATE......................................................................................................................................................40

令SYSTIMESTAMP........................................................................................................................................41

令LAST.DAY...................................................................................................................................................42

0NEXT_DAY..................................................................................................................................................42

令MONTHS.BETWEEN.................................................................................................................................42

?ROUND..........................................................................................................................................................43

令TRUNC...........................................................................................................................................................44

5.轉(zhuǎn)換函數(shù).......................................................................................................................................................45

令TOCHAR.....................................................................................................................................................45

令TO_DATE.....................................................................................................................................................47

6.其他輔助函數(shù)...........................................................................................................47

令DECODE48

令GREATEST.................................................................................................................................................49

令LEAST...........................................................................................................................................................49

0NULLIF.........................................................................................................................................................50

令NVL...............................................................................................................................................................50

令NVL2.............................................................................................................................................................51

令SYS_CONNECT_BY_PATH.....................................................................................................................52

OSYS_CONTEXT..........................................................................................................................................52

第二節(jié)聚集函數(shù)...............................................................52

°AVG...........................................................................................................................54

0DENSE_RANK.........................................................................................................56

。RANK.........................................................................................................................57

。FIRST.........................................................................................................................58

°MAX...........................................................................................................................58

。MIN............................................................................................................................59

0SUM..........................................................................................................................59

。...................................................................................60

第三節(jié)分析函數(shù)...............................................................60

7.概述...........................................................................................................................60

2.工作機制...................................................................................................................61

3語法62

4.常用的分析函數(shù).........................................................................................................65

。LAST_VALUE.............................................................................................................................................67

令MIN...............................................................................................................................................................69

令DENSE.RANK............................................................................................................................................70

令RANK...........................................................................................................................................................71

5.不太常用的分析函數(shù)..................................................................................................73

6.常見問題...................................................................................................................73

。計算動態(tài)累計問題..............................................................73

<TOP-N問題....................................................................73

。交叉查詢問題...................................................................73

令移除重復值問題............................................................73

第四節(jié)對象引用函數(shù)..............................................................73

第五節(jié)函數(shù)列表（字母序）........................................................73

7.ABS..........................................................................................................................................73

2.ACOS......................................................................................................................................73

3.ADD_MONTHS......................................................................................................................73

4.ASCII......................................................................................................................................74

5.ASIN........................................................................................................................................74

6.ATAN.......................................................................................................................................74

7.ATAN2.....................................................................................................................................74

8.AVG.........................................................................................................................................75

9.BIN_TO_NUM........................................................................................................................75

10.BINAND................................................................................................................................75

11.CHARTOROWID..................................................................................................................76

12.COUNT.................................................................................................................................76

13.CURRENT_DATE.................................................................................................................76

14.CURRENT_TIMESTAMP.....................................................................................................76

75.DECODE.............................................................................................................................76

16.DENSE_RANK....................................................................................................................77

77.DUMP..................................................................................................................................77

18.FIRST...................................................................................................................................77

79.FIRST_VALUE.....................................................................................................................77

20.FLOOR.................................................................................................................................77

21.GREATEST...........................................................................................................................77

22.GROUPJD..........................................................................................................................77

23.GROUPING..........................................................................................................................77

24.GROUPINGJD....................................................................................................................77

25.INSTR...................................................................................................................................77

26.MG......................................................................................................................................77

27.LAST.....................................................................................................................................78

28.LAST_DAY............................................................................................................................78

29.LAST_VALUE.......................................................................................................................78

30.LEAD....................................................................................................................................78

31.LEAST...................................................................................................................................78

32.LENGTH...............................................................................................................................78

33.LOWER.................................................................................................................................78

34.LPAD.....................................................................................................................................78

35.MAX......................................................................................................................................78

36.MIN.......................................................................................................................................79

37.MOD.....................................................................................................................................79

38.MONTHS_BETWEEN..........................................................................................................79

39.NEXT_DAY...........................................................................................................................79

40.NULLIF................................................................................................................................79

41.NVL.......................................................................................................................................79

42.NVL2....................................................................................................................................79

43.RANK...................................................................................................................................79

44.REPLACE............................................................................................................................79

45.ROUND(date).....................................................................................................................80

46.ROUND(number)................................................................................................................80

47.ROW_NUMBER.................................................................................................................80

48.RPAD..................................................................................................................................80

49.RTRIM................................................................................................................................80

50.SOUNDEX..........................................................................................................................80

51.SUBSTR..............................................................................................................................80

52.SUM....................................................................................................................................80

53.SYSDATE............................................................................................................................80

54.SYSTIMESTAMP................................................................................................................81

55.TO_CHAR...........................................................................................................................81

56.TO_DATE...........................................................................................................................81

57.TO_NUMBER.....................................................................................................................81

58.TRANSLATE.......................................................................................................................81

59.TRIM...................................................................................................................................81

60.TRUNC(number)................................................................................................................81

61.TRUNC(date)......................................................................................................................81

62.UPPER...............................................................................................................................81

第五章后記.....................................................................82

第一章基本元素

第一節(jié)數(shù)據(jù)類型

1.VARCHAR2(size)：

可變長度的字符串，最大size個字節(jié)。size最大可取值是4000,如果你指定了size是

4000,然后插入超過4000子句的字符串，oracle會字段截取先進入庫的4000字節(jié)，后面

的它就不管了。如果你知道的size沒有4000,而是其他一個數(shù)字如200,那么如果你插入

的字符串超過200,oracle就會返回一個錯誤。size必須指定個正數(shù)。

有關VARCHAR類型，它其實和VARCHAR2一樣，是它的同義詞。但是不宜指定

VARCHAR作為數(shù)據(jù)類型，因為你不確定oracle哪天頭腦發(fā)熱，把VARCHAR的含義改掉

了或者不支持它，那就完了。

2.CHAR(size)

固定長度的字符串，最大size個字節(jié)。如果你插入的字符串沒有size個字節(jié),那么oracle

就會使用空白來填充它。size最大可取值是2000,如果你指定了size是2000,然后插入

超過2000子句的字符串，oracle會字段截取先進入庫的4000字節(jié)，后面的它就不管了。

如果你知道的size沒有2000,而是其他一個數(shù)字如200,那么如果你插入的字符串超過200,

oracle就會返回一個錯誤。如果你不指定size,那么它的默認值就是1。

3.LONG

用于存儲字符串，最多可以存儲2G個字節(jié)的字符。LONG類型有很多的限制，已經(jīng)不

被oracle推薦使用，大家最好不要用，會有一系列操作限制，導致一些隱形的問題。

4.CLOB

存儲字符串，即支持可變長度的，也支持固定長度的字符串，最大可支持到4G字節(jié)。