ORACLE數(shù)據(jù)庫分冊

1、經(jīng)營分析系統(tǒng)員工培訓(xùn)文檔 -ORACLE數(shù)據(jù)庫分冊 Vesion 1.0SQLPL/SQL.PL/SQL來源針對SQL92標(biāo)準(zhǔn)，各數(shù)據(jù)庫廠商在標(biāo)準(zhǔn)基礎(chǔ)上做了一定的擴(kuò)展，SQL SERVER的擴(kuò)展語言叫T-SQL，PL/SQL是ORACLE對標(biāo)準(zhǔn)數(shù)據(jù)庫語言的擴(kuò)展，ORACLE公司已經(jīng)將PL/SQL整合到ORACLE 服務(wù)器和其他工具中了，近幾年中更多的開發(fā)人員和DBA開始使用PL/SQL，本文將講述PL/SQL基礎(chǔ)語法，結(jié)構(gòu)和組件、以及如何設(shè)計并執(zhí)行一個PL/SQL程序。 PL/SQL從版本6開始PL/SQL就被可靠的整合到ORACLE中了。PL/SQL 不是一個獨(dú)立的產(chǎn)品，他是一個整合到ORA

2、CLE服務(wù)器和ORACLE工具中的技術(shù)，可以把PL/SQL看作ORACLE服務(wù)器內(nèi)的一個引擎，sql語句執(zhí)行者處理單個的sql語句，PL/SQL引擎處理PL/SQL程序塊。當(dāng)PL/SQL程序塊在PL/SQL引擎處理時，ORACLE服務(wù)器中的SQL語句執(zhí)行器處理pl/sql程序塊中的SQL語句。 PL/SQL的優(yōu)點如下： PL/SQL是一種高性能的基于事務(wù)處理的語言，能運(yùn)行在任何ORACLE環(huán)境中，支持所有數(shù)據(jù)處理命令。通過使用PL/SQL程序單元處理SQL的數(shù)據(jù)定義和數(shù)據(jù)控制元素。 PL/SQL支持所有SQL數(shù)據(jù)類型和所有SQL函數(shù)，同時支持所有ORACLE對象類型 PL/SQL塊可以被命名和

3、存儲在ORACLE服務(wù)器中，同時也能被其他的PL/SQL程序或SQL命令調(diào)用，任何客戶/服務(wù)器工具都能訪問PL/SQL程序，具有很好的可重用性。 可以使用ORACLE數(shù)據(jù)工具管理存儲在服務(wù)器中的PL/SQL程序的安全性?？梢允跈?quán)或撤銷數(shù)據(jù)庫其他用戶訪問PL/SQL程序的能力。 PL/SQL代碼可以使用任何ASCII文本編輯器編寫，所以對任何ORACLE能夠運(yùn)行的操作系統(tǒng)都是非常便利的 對于SQL，ORACLE必須在同一時間處理每一條SQL語句，在網(wǎng)絡(luò)環(huán)境下這就意味作每一個獨(dú)立的調(diào)用都必須被oracle服務(wù)器處理，這就占用大量的服務(wù)器時間，同時導(dǎo)致網(wǎng)絡(luò)擁擠。而PL/SQL是以整個語句塊發(fā)給服務(wù)器

4、，這就降低了網(wǎng)絡(luò)擁擠。.PL/SQL基本結(jié)構(gòu)PL/SQL是Oracle對SQL規(guī)范的擴(kuò)展，是一種塊結(jié)構(gòu)語言，即構(gòu)成一個PL/SQL程序的基本單位（過程、函數(shù)和無名塊）是邏輯塊，可包含任何數(shù)目的嵌套了快。這種程序結(jié)構(gòu)支持逐步求精方法解決問題。一個塊（或子塊）將邏輯上相關(guān)的說明和語句組合在一起，其形式為：DECLARE-說明BEGIN-語句序列EXCEPTION-例外處理程序END；PL/SQL機(jī)可執(zhí)行過程性語句，而將SQL語句發(fā)送到ORACLE服務(wù)器上的SQL語句執(zhí)行器。在ORACLE預(yù)編譯程序或OCI程序中可嵌入無名的PL/SQL塊。如果ORACLE具有PROCEDURAL選件，有名的PL/S

5、QL塊（子程序）可單獨(dú)編譯，永久地存儲在數(shù)據(jù)庫中，準(zhǔn)備執(zhí)行。除非特別指明，SQL不區(qū)分大小寫SQL可以在一行或者多行輸入關(guān)鍵字不能分開子句通常放在單獨(dú)的行中使用縮進(jìn)是為了提高代碼可讀性關(guān)鍵字通常使用大寫字母輸入，其他文字用小寫輸入.數(shù)據(jù)類型.1.預(yù)定義數(shù)據(jù)類型Oracle的數(shù)據(jù)類型可以分為四類，分別是標(biāo)量類型，復(fù)合類型，引用類型和LOB類型。標(biāo)量類型沒有內(nèi)部組件；而復(fù)合類型包含了能夠被單獨(dú)操作的內(nèi)部組件；引用類型類似于3G語言中的指針，能夠引用一個值；LOB類型的值就是一個lob定位器，能夠指示出大對象(如圖像)的存儲位置。下圖是在PL/SQL中可以使用的預(yù)定義類型，其中標(biāo)量類型又分為四類：數(shù)

6、字、字符、布爾和日期/時間。數(shù)字型數(shù)字類型可以存儲整數(shù)、實數(shù)和浮點數(shù)，可以表示數(shù)值的大小，參與計算。 BINARY_INTEGER 我們可以使用BINARY_INTEGER數(shù)據(jù)類型來存儲有符號整數(shù)。它的范圍是-2*31至2*31。跟PLS_INTEGER一樣，BINARY_INTEGER所需的存儲空間也要小于NUMBER。但是,大多數(shù)的BINARY_INTEGER操作要比PLS_INTEGER操作慢。 BINARY_INTEGER子類型 所謂的基類型，就是有子類型繼承于它。子類型在基類型的基礎(chǔ)上添加一些約束限制，也可能重新定義數(shù)值范圍。為了使用方便，PL/SQL預(yù)定義了下面幾個BINARY_I

7、NTEGER的子類。 NATURAL NATURALN POSITIVE POSITIVEN SIGNTYPE 子類型NATURAL和POSITIVE能讓我們將一個整數(shù)變量的取值范圍分別限制在非負(fù)數(shù)和正整數(shù)之內(nèi)。NATURALN和POSITIVEN不允許為整數(shù)類型變量賦空值。SIGNTYPE把整數(shù)的取值范圍限定在-1，0，1，這在編程中很適合表示三態(tài)邏輯(tri-state logic)。 NUMBER 我們可以使用NUMBER數(shù)據(jù)類型來存儲定點或浮點數(shù)。它的范圍是1E-130至10E125。如果表達(dá)式的值超過這個范圍，我們就會得到數(shù)字溢出錯誤(a numeric overflow or un

8、derflow error)。我們可以為要存儲的數(shù)字指定精度，包括數(shù)字的全長和小數(shù)長度。語法如下： NUMBER(precision,scale) 其中precision表示數(shù)字的總長度，scale代表可以有幾位小數(shù)。如果要使用浮點數(shù)的話，就不能指定長度和精度，像下面這樣聲明就可以了：NUMBER 聲明整數(shù)直接使用下面的語法：NUMBER(precision) - same as NUMBER(precision,0) 不可以用常量或變量指定NUMBER的長度和精度。NUMBER類型最大的長度是38位。如果不指定NUMBER類型的最大長度，就會默認(rèn)采用這個長度或是使用系統(tǒng)所支持的最大長度。sc

9、ale的范圍從-84到127，能夠決定舍入規(guī)則。例如，一個scale值為2的數(shù)字，舍入后的小數(shù)部分將是最接原小數(shù)部分的百分位數(shù)(3.456舍入為3.46)。如果scale是負(fù)數(shù)，它就會從小數(shù)點左邊開始進(jìn)行舍入操作。如scale值為-3的數(shù)字舍入后的結(jié)果將是最接近原值的千位數(shù)(3456舍入為3000)。scale為零的數(shù)字舍入后的結(jié)果還是本身。如果我們不指定scale的值，默認(rèn)就為0。NUMBER子類型 為了能和ANSI/ISO和IBM類型兼容或是想使用一個更加有描述性意義的名字，我們就可以使用下面的NUMBER子類型。 DEC DECIMAL DOUBLE PRECISION FLOAT IN

10、TEGER INT NUMERIC REAL SMALLINT 使用DEC、DECIMAL和NUMBERIC可以聲明最大精度為38位十進(jìn)制數(shù)字的定點數(shù)字。而使用DOUBLE PRECISION和FLOAT可以聲明最大精度為126位二進(jìn)制數(shù)字的浮點數(shù)字，大約相當(dāng)于38位十進(jìn)制數(shù)字。或是使用REAL聲明最大精度為63位二進(jìn)制數(shù)字的浮點數(shù)字，大約相當(dāng)于18位十進(jìn)制數(shù)字。INTEGER、INT和SMALLINT可以聲明最大精度為38位十進(jìn)制數(shù)字的整數(shù)。 PLS_INTEGER 我們可以使用PLS_INTEGER數(shù)據(jù)類型來存儲有符號整數(shù)。它的取值范圍在-2*31至2*31之間。PLS_INTEGER所需

11、的存儲空間要比NUMBER少，運(yùn)算的速度要高于NUMBER和BINARY_INTEGER。雖然PLS_INTEGER和BINARY_INTEGER的取值范圍一樣，但它們不完全兼容。PLS_INTEGER在運(yùn)算時如果有溢出，則會有異常拋出，而BIANRY_INTEGER發(fā)生溢出時，如果結(jié)果是要賦給一個NUMBER類型的變量時，就不會有異常拋出。為了考慮兼容性，我們?nèi)钥梢栽谂f的應(yīng)用程序中使用BINARY_INTEGER；但在新的應(yīng)用程序中，PLS_INTEGER會帶來更好的性能。 字符型字符類型可以存放字符和數(shù)字混合的數(shù)據(jù)，表現(xiàn)詞和文章，操作字符串。CHAR 我們可以使用CHAR類型來存儲定長的字

12、符數(shù)據(jù)。但該數(shù)據(jù)的內(nèi)部表現(xiàn)形式是取決于數(shù)據(jù)庫字符集的。CHAR類型有一個用于指定最大長度的可選參數(shù)，長度范圍在1到32767字節(jié)之間。我們可以采用字節(jié)或字符的形式來設(shè)置該參數(shù)。語法如下：CHAR(maximum_size CHAR | BYTE ) maximum_size不能是常量或變量，只能是范圍在1到32767之間的整數(shù)文字。如果我們不指定最大值，它默認(rèn)是1。如果我們用字節(jié)形式指定最大值，有時就會出現(xiàn)空間不足的問題(多字節(jié)字符會占用多于一個字節(jié)的空間)。為了避免這樣的問題發(fā)生，我們可以采用按照字符的方式指定其最大值，這樣，即使是那些包含多個字節(jié)的參數(shù)可以被靈活地存儲下來。按照字符指定長度

13、的方式，上限大小仍舊是32767字節(jié)，所以，對于雙字節(jié)和多字節(jié)的字符集，我們可以使用字節(jié)最大長度的一半或三分之一作為最大字符個數(shù)。 雖然PL/SQL字符變量的長度相對來說比較長，但CHAR類型在數(shù)據(jù)庫的字段中最大存儲長度為2000個字節(jié)，所以，我們不能往數(shù)據(jù)庫CHAR類型字段中插入超過2000個字節(jié)的字符。但是，我們可以把任意CHAR(n)插入LONG類型的字段中，因為LONG的最大長度是2*31字節(jié)或是2G(gigabyte)。如果我們不使用CHAR或BYTE來對字符類型長度進(jìn)行限制的話，初始化參數(shù)NLS_LENGTH_SEMANTICS會決定默認(rèn)長度大小的。CHAR的子類型CHARACTE

14、R和CHAR有著相同的取值范圍。也就是說，CHARACTER只是CHAR的一個別名而已。這個子類型能與ANSI/ISO和IBM類型相兼容。 LONG和LONG RAW 我們可以使用LONG類型來存儲變長的字符串。除了LONG類型的最大長度是32760字節(jié)之外，LONG類型和VARCHAR2很相像。我們還可以使用LONG RAW類型來存儲二進(jìn)制數(shù)據(jù)或二進(jìn)制字符串。LONG RAW和LONG類似，但是它不會被PL/SQL解析。LONG RAW的最大長度也是32760字節(jié)。從9i開始，LOB類型變量可以與LONG和LONG RAW類型交換使用。Oracle推薦將LONG和LONG RAW都對應(yīng)的轉(zhuǎn)換

15、成COLB和BLOB類型。 我們可以將LONG類型的值插入字段類型為LONG的數(shù)據(jù)庫中，因為在數(shù)據(jù)庫中LONG的長度是2*31字節(jié)；但是，不可以從LONG字段中檢索超過32760字節(jié)的字符放到LONG類型變量中去。同樣，對于LONG RAW類型來說，這條規(guī)則同樣適用，在數(shù)據(jù)庫中它的最大長度也是2*31字節(jié)，而變量的長度在32760字節(jié)以內(nèi)。LONG類型字段可以存儲文本、字符數(shù)組或短文等。我們可以對LONG字段進(jìn)行UPDATE、INSERT和SELECT操作，但不能在表達(dá)式、SQL函數(shù)調(diào)用、或某個SQL子句(如：WHERE、GROUP BY和CONNECT BY)中使用它。 注意：在SQL語句中

16、，PL/SQL會將LONG類型的值綁定成VARCHAR2類型，而不是LONG。但是，如果被綁定的VARCHAR2值超過4000個字節(jié)，Oracle會自動地將綁定類型轉(zhuǎn)成LONG，但LONG并不能應(yīng)用在SQL函數(shù)中，所以，這時我們就會得到一個錯誤消息。 RAW 我們可以使用RAW數(shù)據(jù)類型來存儲二進(jìn)制數(shù)據(jù)或字節(jié)串。例如，一個RAW類型的變量可以存儲一個數(shù)字化圖形。RAW類型數(shù)據(jù)和VARCHAR2類型數(shù)據(jù)類似，只是PL/SQL不對其進(jìn)行解析而已。同樣，在我們把RAW數(shù)據(jù)從一個系統(tǒng)傳到另一個系統(tǒng)時，Oracle Net也不會對它做字符集轉(zhuǎn)換。RAW類型包含了一個可以讓我們指定最大長度的可選參數(shù)，上限為

17、32767字節(jié)，語法如下：RAW(maximum_size) 我們不能使用常量或變量來指定這個參數(shù)；而且參數(shù)的范圍必須是在1到32767范圍內(nèi)。在數(shù)據(jù)庫中RAW類型字段的最大長度是2000個字節(jié)，所以，不可以把超過2000字節(jié)的內(nèi)容放到RAW類型字段中。我們可以把任何RAW類型插入到LONG RAW類型的數(shù)據(jù)庫字段中，因為LONG RAW在數(shù)據(jù)庫中是2*31，但是不能把超過32767字節(jié)的LONG RAW類型放入RAW變量中。 ROWID和UROWID 在Oracle內(nèi)部，每個數(shù)據(jù)表都有一個偽列ROWID，用于存放被稱為ROWID的二進(jìn)制值。每個ROWID代表了一行數(shù)據(jù)的存儲地址。物理ROWI

18、D能夠標(biāo)識普通數(shù)據(jù)表中的一行信息，而邏輯ROWID能夠標(biāo)識索引組織表(index-organized table)中的一行信息。其中ROWID類型只能存儲物理內(nèi)容，而UROWID(universal rowid)類型可以存儲物理，邏輯或外來(non-Oracle)ROWID。 建議：只有在舊的應(yīng)用程序中，為了兼容性我們才使用ROWID數(shù)據(jù)類型。對于新的應(yīng)用程序，應(yīng)該使用UROWID數(shù)據(jù)類型。 當(dāng)我們把查詢出來的ROWID放到ROWID變量時，可以使用內(nèi)置函數(shù)ROWIDTOCHAR，這個函數(shù)能把二進(jìn)制內(nèi)容轉(zhuǎn)換成18個字節(jié)的字符串；還有一個與之對應(yīng)的CHARTOROWID函數(shù)，可以對該過程進(jìn)行反操

19、作，如果轉(zhuǎn)換過程中發(fā)現(xiàn)字符串并不是一個有效的ROWID時，PL/SQL就會拋出預(yù)定義異常SYS_INVALID_ROWID。UROWID變量和字符串之間進(jìn)行轉(zhuǎn)換也可以直接使用賦值操作符。這時，系統(tǒng)會隱式地實現(xiàn)UROWID和字符類型之間的轉(zhuǎn)換。 物理ROWID(Physical Rowid)可以讓我們快速的訪問某些特定的行。只要行存在，它的物理ROWID就不會改變。高效穩(wěn)定的物理ROWID在查詢行集合、操作整個集合和更新子集是很有用的。例如，我們可以在UPDATE或DELETE語句的WHERE子句中比較UROWID變量和ROWID偽列來找出最近一次從游標(biāo)中取出的行數(shù)據(jù)。 物理ROWID有兩種形式

20、。10字節(jié)擴(kuò)展ROWID格式(10-byte extended rowid format)支持相對表空間塊地址并能辨識分區(qū)表和非分區(qū)表中的行記錄。6字節(jié)限定ROWID格式支持向后兼容。 擴(kuò)展ROWID使用檢索出來的每一行記錄的物理地址的base-64編碼。例如，在SQL*Plus(隱式地將ROWID轉(zhuǎn)換成字符串)中的查詢： SQL SELECT ROWID, enameSQL FROM empSQL WHERE empno = 7788;ROWID ENAME- -AAAAqcAABAAADFNAAH SCOTT OOOOOOFFFBBBBBBRRR這樣的形式有四部分組成: 000000代表數(shù)

21、據(jù)對象號(data object number)，如上例中的AAAAqc，能夠辨識數(shù)據(jù)庫段。同一段中的模式對象，都有著相同的數(shù)據(jù)對象號。 FFF代表文件號(file number)，如上例中的AAB，能辨識出包含行的數(shù)據(jù)文件。在數(shù)據(jù)庫中，文件號是唯一的。 BBBBBB代表塊號(block number)，如上例中的AAADFN，能辨識出包含行的數(shù)據(jù)塊。塊號是與它們所在的數(shù)據(jù)文件相關(guān)，而不是表空間。所以，兩個在同一表空間的行數(shù)據(jù)，如果它們處于不同的數(shù)據(jù)文件中，也可能有著相同的塊號。 RRR代表了行號(row number)，如上例中的AAH，可以辨識塊中的行數(shù)據(jù)。 邏輯ROWID為訪問特定行提供

22、了最快的訪問速度。Oracle在索引組織表基礎(chǔ)上使用它們構(gòu)建二級索引。邏輯ROWID沒有持久的物理地址，當(dāng)新數(shù)據(jù)被插入時，它的值就會在數(shù)據(jù)塊上偏移。但是，如果一個行數(shù)據(jù)的物理位置發(fā)生變化，它的邏輯ROWID就無效了。 VARCHAR2 我們可以使用VARCHAR2類型來存儲變長的字符數(shù)據(jù)。至于數(shù)據(jù)在數(shù)據(jù)庫中的內(nèi)部表現(xiàn)形式要取決于數(shù)據(jù)庫的字符集。VARCHAR2類型需要指明數(shù)據(jù)長度，這個參數(shù)的上限是32767字節(jié)。語法如下： VARCHAR2(maximum_size CHAR | BYTE) 我們不能使用常量或變量來指定maximum_size值，maximum_size值的有效范圍在1到32

23、767之間。對于長度不同的VARCHAR2類型數(shù)據(jù)，PL/SQL對它們的處理方式也是不同的。值小的PL/SQL會優(yōu)先考慮到它的性能問題，而對于值大的PL/SQL會優(yōu)先考慮到內(nèi)存的使用效率問題。截止點(cutoff point)為2000個字節(jié)。在2000字節(jié)以下，PL/SQL會一次性分配我們聲明時所指定大小的空間容納實際的值；2000字節(jié)或2000字節(jié)以上時，PL/SQL會動態(tài)的分配VARCHAR2的存儲容量。比如我們聲明兩個VARCHAR2類型的變量，一個長度是1999字節(jié)，一個是2000字節(jié)，如果我們把長度為500字節(jié)的值分別分配給這兩個變量，那么前者占用的空間就是1999字節(jié)而后者只需5

24、00字節(jié)。如果我們采用字節(jié)形式而非字符形式指定最大值時，VARCHAR2(n)變量就有可能太小而不能容納n個多字節(jié)字符。為了避免這個問題，就要使用VARCHAR2(n CHAR)形式進(jìn)行定義，這樣，即使字符中包含多字節(jié)字符也不會出問題。所以，對于雙字節(jié)或多字節(jié)字符集，我們可以指定單字節(jié)字符集中字符個數(shù)的1/2或1/3。雖然PL/SQL字符變量相對比較長，但VARCHAR2類型的數(shù)據(jù)庫字段最大長度為4000個字節(jié)。所以，不能把字節(jié)超過4000的VARCHAR2類型值插入VARCHAR2類型的數(shù)據(jù)庫字段中。我們可以把任何VARCHAR2(n)值插入一個LONG類型的數(shù)據(jù)庫字段，因為LONG字段最大

25、長度為2*31字節(jié)。但是，不能把LONG字段中檢索出來的長度超過32767字節(jié)的值放到VARCHAR2(n)變量中。如果聲明時不使用CHAR或BYTE限定修飾詞，初始化參數(shù)NLS_LENGTH_SEMANTICS會決定默認(rèn)的設(shè)置。當(dāng)PL/SQL過程編譯時，這個參數(shù)的設(shè)置就會被記錄下來，這樣，當(dāng)過程失效后被重新編譯時就會被重新使用。VARCHAR2的子類型 下面VARCHAR2的子類型的范圍與VARCHAR2完全相同，它們只是VARCHAR2的一個別名而已。 STRING VARCHAR 我們可以使用這些子類型來與ANSI/ISO和IBM類型兼容。注意：目前，VARCHAR和VARCHAR2有著

26、相同意義，但是在以后的PL/SQL版本中，為了符合SQL標(biāo)準(zhǔn)，VARCHAR有可能會作為一個單獨(dú)的類型出現(xiàn)。所以最好使用VARCHAR2，而不是VARCHAR。 本地字符型被廣泛使用的單字節(jié)ASCII和EBCDIC字符集很適合表現(xiàn)羅馬字符，但有些亞洲語言，如漢語、日語等包含了成千上萬個字符，這些語言中的一個字符就需要用兩個或三個字節(jié)來表示。為了處理這些語言，Oracle提供了全球化支持，允許我們處理單字節(jié)和多字節(jié)字符數(shù)據(jù)，并在字符集之間進(jìn)行數(shù)據(jù)轉(zhuǎn)換。Oracle還能讓我們的應(yīng)用程序運(yùn)行在不同的語言環(huán)境中。有了全球化支持，數(shù)字和日期格式會根據(jù)用戶會話中所指定的語言約定(language conv

27、ention)而自動進(jìn)行調(diào)節(jié)。因此，全世界的用戶可以使用他們母語來使用Oracle。 Oracle支持兩種數(shù)據(jù)庫字符集和一種國家特有字符集，前者用于標(biāo)識符和源代碼，后者用于國家特有語言數(shù)據(jù)。NCHAR和NVARCHAR2類型用于存儲本地字符集。注意，當(dāng)在數(shù)據(jù)庫的不同字符集之間轉(zhuǎn)換CHAR或VARCHAR2數(shù)據(jù)時，要確保數(shù)據(jù)保持良好的形式(well-formed)。比較UTF8和AL16UTF16編碼 國家特有字符集使用Unicode來表現(xiàn)數(shù)據(jù)，采用UTF8或AL16UTF16編碼。 每個使用AL16UTF16編碼的字符都占用2個字節(jié)。這將簡化字符串的長度計算，避免采用混合語言編程時發(fā)生截斷錯誤

28、，但是這會比ASCII字符所組成的字符串需要更多空間。 每個使用UTF8編碼的字符占用1、2或3個字節(jié)。這就能讓我們把更多的字符放到變量或數(shù)據(jù)表的字段中，但這只是在大多數(shù)字符用單字節(jié)形式表現(xiàn)的條件下才能做到。這種編碼在傳遞數(shù)據(jù)到字節(jié)緩沖器時可能會引起截斷錯誤。 Oracle公司推薦使用默認(rèn)的AL16UTF16編碼，這樣可以獲取最大的運(yùn)行時可靠性。如果想知道一個Unicode字符串占用多少字節(jié)，就要使用LENGTHB函數(shù)，而不是LENGTH。 NCHAR 我們用NCHAR類型來儲存定長國家特有字符數(shù)據(jù)。數(shù)據(jù)的內(nèi)部表現(xiàn)取決于數(shù)據(jù)庫創(chuàng)建時指定的國家特有字符集，字符集可能采用變長編碼(UTF8)或定長

29、編碼(AL16UTF16)。因為這種類型總是與多字節(jié)字符兼容，所以我們可以使用它支持任何Unicode字符數(shù)據(jù)。 NCHAR數(shù)據(jù)類型可接受一個可選參數(shù)來讓我們指定字符的最大長度。語法如下： NCHAR(maximum_size) 因為物理限制是32767個字節(jié)，所以在AL16UTF16編碼格式下最大長度為32767/2，UTF8編碼格式下是32767/3。我們不能使用常量或變量來指定最大值，只能使用整數(shù)文字。如果我們沒有指定最大長度，它默認(rèn)值就為1。這個值總是代表字符的個數(shù)，不像CHAR類型，既可以采用字符形式又可以采用字節(jié)形式。my_string NCHAR(100); - maximum

30、size is 100 characters NCHAR在數(shù)據(jù)庫字段中的最大寬度是2000字節(jié)。所以，我們不能向NCHAR字段中插入值超過2000字節(jié)的內(nèi)容。如果NCHAR的值比NCHAR字段定義的寬度要小，Oracle就會自動補(bǔ)上空格，填滿定義的寬度。我們可以在語句和表達(dá)式中交互使用CHAR和NCHAR值。從CHAR轉(zhuǎn)到NCHAR總是安全的，但在NCHAR值轉(zhuǎn)換到CHAR的過程中，如果CHAR類型不能完全表現(xiàn)NCHAR類型的值，就會引起數(shù)據(jù)丟失。這樣的數(shù)據(jù)丟失會導(dǎo)致字符看起來像問號(?)。NVARCHAR2 我們可以使用NVARCHAR2數(shù)據(jù)類型來存儲變長的Unicode字符數(shù)據(jù)。數(shù)據(jù)的內(nèi)部

31、表現(xiàn)取決于數(shù)據(jù)庫創(chuàng)建時所指定的國家特有字符集，它有可能采用變長編碼(UTF8)或是定長編碼(AL16UTF16)。因為這個類型總與多字節(jié)兼容，我們可以用它來支持Unicode字符數(shù)據(jù)。NVARCHAR2數(shù)據(jù)類型需要接受一個指定最大大小的參數(shù)。語法如下：NVARCHAR2(maximum_size) 因為物理限制是32767個字節(jié)，所以在AL16UTF16編碼格式下最大長度為32767/2，UTF8編碼格式下是32767/3。我們不能使用常量或變量來指定最大值，只能使用整數(shù)文字。最大值總是代表字符的個數(shù)，不像CHAR類型，既可以采用字符形式又可以采用字節(jié)形式。my_string NVARCHAR

32、2(200); - maximum size is 200 characters NVARCHAR2在數(shù)據(jù)庫字段中的最大寬度是4000字節(jié)。所以，我們不能向NVARCHAR2字段中插入長度超過4000字節(jié)的值。我們可以在語句和表達(dá)式中交互使用VARCHAR2和NVARCHAR2值。從VARCHAR2向NVARCHAR2轉(zhuǎn)換總是安全的，但在NVARCHAR2值轉(zhuǎn)換到VARCHAR2的過程中，如果VARCHAR2類型不能完全表現(xiàn)NVARCHAR2類型的值，就會引起數(shù)據(jù)丟失。這樣的數(shù)據(jù)丟失會導(dǎo)致字符看起來像問號(?)。LOB類型LOB(large object)數(shù)據(jù)類型BFILE、BLOB、CLOB

33、和NCLOB可以最大存儲4G的無結(jié)構(gòu)數(shù)據(jù)(例如：文本、圖形、視頻剪輯和音頻等)塊。并且，它們允許高效地隨機(jī)地分段訪問數(shù)據(jù)。LOB類型和LONG、LONG RAW類型相比有幾個不同的地方。比如，LOB(除了NCOLB)可以作為對象類型的一個屬性，但LONG類型不可以。LOB的最大值是4G，而LONG只有2G。LOB支持隨機(jī)訪問數(shù)據(jù)，但LONG只支持順序訪問。LOB類型中可以存儲了LOB定位器，它能夠指向存放于外部文件中的大對象，in-line (inside the row)或out-of-line (outside the row)的形式。BLOB、CLOB、NCLOB或BFILE類型的數(shù)據(jù)庫

34、字段存儲了定位器。其中BLOB、CLOB和NCLOB的數(shù)據(jù)存在數(shù)據(jù)庫中，in-line (inside the row)或out-of-line (outside the row)的形式，而BFILE數(shù)據(jù)存在數(shù)據(jù)庫之外的操作系統(tǒng)文件中。PL/SQL是通過定位器來操作LOB的。例如，當(dāng)我們查詢出一個BLOB值，只有定位器被返回。如果在事務(wù)中得到定位器，LOB定位器就會包含事務(wù)的ID號，這樣我們就不能在另外一個事務(wù)中更新LOB內(nèi)容了。同樣，我們也不能在一個會話中操作另外一個會話中的定位器。 從9i開始，我們也可以把CLOB類型轉(zhuǎn)成CHAR和VARCHAR2類型或是把BLOB轉(zhuǎn)成RAW，反之亦然，這

35、樣，我們就能在大多數(shù)SQL和PL/SQL語句和函數(shù)中使用LOB類型了。要讀、寫和分段的操作LOB，我們可以使用Oracle系統(tǒng)包DBMS_LOB。BFILE BFILE數(shù)據(jù)類型用于存儲二進(jìn)制對象，它將存儲的內(nèi)容放到操作系統(tǒng)的文件中，而不是數(shù)據(jù)庫內(nèi)。每個BFILE變量都存儲一個文件定位器，它指向服務(wù)器上的一個大的二進(jìn)制文件。定位器包含目錄別名，該別名給出了文件全路徑。BFILE類型是只讀的，而且它的大小要依賴于系統(tǒng)，不能超過4G。我們的DBA要確保給定的BFILE存在且Oracle有讀取它的權(quán)限。BFILE并不參與事務(wù)，是不可恢復(fù)，不能被復(fù)制。能夠被打開的BFILE最大數(shù)是由Oracle初始化參

36、數(shù)SESSION_MAX_OPEN_FILES決定的。 BLOB、CLOB和NCLOB BLOB數(shù)據(jù)類型可以在數(shù)據(jù)庫中存放不超過4G的大型二進(jìn)制對象；CLOB和NCLOB可以在數(shù)據(jù)庫中分別存儲大塊CHAR類型和NCHAR類型的字符數(shù)據(jù)，都支持定寬和變寬字符集。同BFILE一樣，這三個類型也都儲存定位器，指向各自類型的一個大數(shù)據(jù)塊。數(shù)據(jù)大小都不能超過4G。BLOB、CLOB和NCLOB都可以在事務(wù)中使用，能夠被恢復(fù)和復(fù)制。DBMS_LOB包可以對它們更改過的內(nèi)容進(jìn)行提交或回滾操作。BLOB、CLOB和NCLOB的定位器都可以跨事務(wù)使用，但不能跨會話使用。 布爾類型布爾類型能存儲邏輯值TRUE、F

37、ALSE和NULL(NULL代表缺失、未知或不可用的值)。只有邏輯操作符才允許應(yīng)用在布爾變量上。數(shù)據(jù)庫SQL類型并不支持布爾類型，只有PL/SQL才支持。所以就不能往數(shù)據(jù)庫中插入或從數(shù)據(jù)庫中檢索出布爾類型的值。 Datetime和Interval類型Datetime就是日期時間類型，而Interval指的是時間的間隔。Datetime和Interval類型都由幾個域組成，下表是對每個域及其它們對應(yīng)的有效值描述： 域名稱 有效日期時間值 有效間隔值 YEAR -4712 到 9999 (不包括0) 任意非零整數(shù) MONTH 01 到 12 0 到 11 DAY 01 到 31 (根據(jù)當(dāng)?shù)氐臍v法規(guī)

38、則，受MONTH和YEAR值的限制 任意非零整數(shù) HOUR 00 到 23 0 到 23 MINUTE 00 到 59 0 到 59 SECOND 00 到 59.9(n)，9(n)是秒小數(shù)部分的精度 0 to 59.9(n)，9(n)間隔秒的小數(shù)部分的精度 TIMEZONE_HOUR -12 到 14 (隨日光節(jié)約時間的變化而變化) 不可用 TIMEZONE_MINUTE 00 到 59 不可用 TIMEZONE_REGION 查看視圖V$TIMEZONE_NAMES 不可用 TIMEZONE_ABBR 查看視圖V$TIMEZONE_NAMES 不可用 除了TIMESTAMP WITH LO

39、CAL TIMEZONE以外，剩下的都是SQL92所支持的。 DATE DATE數(shù)據(jù)類型能夠存儲定長的日期時間。日期部分默認(rèn)為當(dāng)月的第一天；時間部分為午夜時間。函數(shù)SYSDATE能夠返回當(dāng)前的日期和時間。 提示：如果只進(jìn)行日期的等值比較，忽略時間部分，可以使用函數(shù)TRUNC(date_variable)。 有效的日期范圍是從公元前4721年1月1日到公元9999年12月31日。儒略日(Julian date)是自公元前4712年1月1日起經(jīng)過的天數(shù)。我們可以使用日期模式J配合函數(shù)TO_DATE和TO_CHAR來把日期值轉(zhuǎn)換成等值的儒略日。在日期表達(dá)式中PL/SQL會自動地將格式為默認(rèn)日期格式的

40、字符值轉(zhuǎn)成DATE類型值。默認(rèn)的日期格式由Oracle的初始化參數(shù)NLS_DATE_FORMAT決定的。例如，默認(rèn)格式可能是DD-MON-YY，它包含兩位數(shù)字表示一個月中的第幾日，月份名稱的縮寫和兩位記年用的數(shù)字。我們可以對日期進(jìn)行加減運(yùn)算。例如，下面的語句就能返回員工自被雇用日起，至今所經(jīng)過的天數(shù)：SELECT SYSDATE - hiredate INTO days_worked FROM emp WHERE empno = 7499; 在算術(shù)表達(dá)式中，PL/SQL會將整數(shù)文字當(dāng)作日來處理，如SYSDATE + 1就代表明天的時間。TIMESTAMP TIMESTAMP是對DATE的擴(kuò)展，

41、包含了年月日時分秒，語法如下： TIMESTAMP(precision) precision是可選參數(shù)，用于指定秒的小數(shù)部分?jǐn)?shù)字個數(shù)。參數(shù)precision不可以是常量或變量，其有效范圍是0到9，默認(rèn)值是6。默認(rèn)的時間戳(timestamp)格式是由Oracle初始化參數(shù)NLS_TIMESTAMP_FORMAT決定的。 在下面的例子中，我們聲明了一個TIMESTAMP類型的變量，然后為它賦值：DECLARE checkout TIMESTAMP ( 3 );BEGIN checkout := 1999-06-22 07:48:53.275; .END; 這個例子中，秒的小數(shù)部分是0.275。T

42、IMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE擴(kuò)展了TIMESTAMP，能夠表現(xiàn)時區(qū)位移(time-zone displacement)。時區(qū)位移在本地時間和通用協(xié)調(diào)時間(UTC)中是不同的。語法如下： TIMESTAMP(precision) WITH TIME ZONE precision的用法同TIMESTAMP語法中的precision。默認(rèn)格式由Oracle初始化參數(shù)NLS_TIMESTAMP_TZ_FORMAT決定。下例中，我們聲明一個TIMESTAMP WITH TIME ZONE類型的變量，然后為其賦值： DECLARE LOG

43、OFF TIMESTAMP ( 3 ) WITH TIME ZONE;BEGIN LOGOFF := 1999-10-31 09:42:37.114 +02:00; .END; 例子中時區(qū)位移是+02:00。我們還可以使用符號名稱(symbolic name)來指定時區(qū)位移，名稱可以是完整形式也可以是縮寫形式，如US/Pacific和PDT，或是組合的形式。例如，下面的文字全都表現(xiàn)同一時間。第三種形式最可靠，因為它指定了切換到日光節(jié)約時間時的規(guī)則。TIMESTAMP 1999-04-15 8:00:00 -8:00TIMESTAMP 1999-04-15 8:00:00 US/PacificT

44、IMESTAMP 1999-10-31 01:30:00 US/Pacific PDT 我們可以在數(shù)據(jù)詞典V$TIMEZONE_NAMES的TIMEZONE_REGION和TIMEZONE_ABBR字段中找到相應(yīng)的時區(qū)名稱和它的縮寫。如果兩個TIMESTAMP WITH TIME ZONE值在通用協(xié)調(diào)時間中的值一樣，那么系統(tǒng)就會認(rèn)為它們的值相同而忽略它們的時區(qū)位移。下例兩個值被認(rèn)為是相同的，因為在通用協(xié)調(diào)時間里，太平洋標(biāo)準(zhǔn)時間8:00 AM和(美國)東部時區(qū)11:00 AM是相同的：1999-08-29 08:00:00 -8:001999-08-29 11:00:00 -5:00 TIMES

45、TAMP WITH LOCAL TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE是對TIMESTAMP WITH TIME ZONE的擴(kuò)展，它的語法如下： TIMESTAMP(precision) WITH LOCAL TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE的用途和TIMESTAMP WITH TIME ZONE相似，它們不同之處在于，當(dāng)我們往數(shù)據(jù)庫中插入TIMESTAMP WITH LOCAL TIME ZONE類型數(shù)據(jù)的時候，數(shù)據(jù)會被轉(zhuǎn)成數(shù)據(jù)庫的時區(qū)，并且時區(qū)位移并不會存放在數(shù)據(jù)庫中。當(dāng)進(jìn)行數(shù)據(jù)檢索時，Oracle會

46、按我們本地會話的時區(qū)設(shè)置返回值。下面就是一個使用TIMESTAMP WITH LOCAL TIME ZONE的例子： DECLARE LOGOFF TIMESTAMP ( 3 ) WITH LOCAL TIME ZONE;BEGIN NULL; .END; 我們不可以用文字值為這種類型的變量賦值。INTERVAL YEAR TO MONTH 我們可以使用INTERVAL YEAR TO MONTH類型用來保存年月的間隔，語法如下： INTERVAL YEAR(precision) TO MONTH precision指定間隔的年數(shù)。參數(shù)precision不能是常量或變量，其范圍在1到4之間，默

47、認(rèn)值是2，下面的例子中聲明了一個INTERVAL YEAR TO MONTH類型的變量，并把間隔值101年3個月賦給它：DECLARE lifetime INTERVAL YEAR(3)TO MONTH;BEGIN lifetime := INTERVAL 101-3 YEAR TO MONTH; - interval literal lifetime := 101-3; - implicit conversion from character type lifetime := INTERVAL 101 YEAR; - Can specify just the years lifetime :

48、= INTERVAL 3 MONTH; - Can specify just the months .END; INTERVAL DAY TO SECOND 我們可以使用INTERVAL DAY TO SECOND數(shù)據(jù)類型存儲和操作天、小時、分鐘和秒，語法如下：INTERVAL DAY(leading_precision) TO SECOND(fractional_seconds_precision) leading_precision和fractional_seconds_precision分別指定了天數(shù)和秒數(shù)。這兩個值都不可以用常量或變量指定，且只能使用范圍在0到9之間的整數(shù)文字為其賦值。

49、它們的默認(rèn)值分別為2和6。下面的例子中，我們聲明了一個INTERVAL DAY TO SECOND類型的變量： DECLARE lag_time INTERVAL DAY(3)TO SECOND(3);BEGIN IF lag_time INTERVAL 6 DAY THEN . .END; 用戶自定義子類型暫時不考慮.基本函數(shù).1.字符函數(shù)ASCII返回與指定的字符對應(yīng)的十進(jìn)制數(shù);SQL select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;A A ZERO SPACE- - - -65 97 48 322.C

50、HR給出整數(shù),返回對應(yīng)的字符;SQL select chr(54740) zhao,chr(65) chr65 from dual; ZH C- -趙 A3.CONCAT連接兩個字符串;SQL select concat(010-,88888888)|轉(zhuǎn)23 高乾競電話 from dual;高乾競電話234.INITCAP返回字符串并將字符串的第一個字母變?yōu)榇髮?SQL select initcap(smith) upp from dual;UPP-Smith5.INSTR(C1,C2,I,J)在一個字符串中搜索指定的字符,返回發(fā)現(xiàn)指定的字符的位置;C1 被搜索的字

51、符串C2 希望搜索的字符串I 搜索的開始位置,默認(rèn)為1J 出現(xiàn)的位置,默認(rèn)為1SQL select instr(oracle traning,ra,1,2) instring from dual;INSTRING-96.LENGTH返回字符串的長度;SQL select name,length(name),addr,length(addr),sal,length(to_char(sal) from .nchar_tst;NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)- - - - - -高乾競 3 北京市海錠區(qū) 6 999

52、9.99 7 7.LOWER返回字符串,并將所有的字符小寫SQL select lower(AaBbCcDd)AaBbCcDd from dual;AABBCCDD-aabbccdd8.UPPER返回字符串,并將所有的字符大寫SQL select upper(AaBbCcDd) upper from dual;UPPER-AABBCCDD 9.RPAD和LPAD(粘貼字符)RPAD 在列的右邊粘貼字符LPAD 在列的左邊粘貼字符SQL select lpad(rpad(gao,10,*),17,*)from dual;LPAD(RPAD(GAO,1-*gao*不夠字符則用*來填滿10.LTRI

53、M和RTRIMLTRIM 刪除左邊出現(xiàn)的字符串RTRIM 刪除右邊出現(xiàn)的字符串SQL select ltrim(rtrim( gao qian jing , ), ) from dual;LTRIM(RTRIM(-gao qian jing11.SUBSTR(string,start,count)取子字符串,從start開始,取count個SUBSTR(-0888888812.REPLACE(string,s1,s2)string 希望被替換的字符或變量 s1 被替換的字符串s2 要替換的字符串SQL select replace(he love you,he,i) from dual;REP

54、LACE(HELOVEYOU,HE,I)-i love you13.SOUNDEX返回一個與給定的字符串讀音相同的字符串SQL create table table1(xm varchar(8);SQL insert into table1 values(weather);SQL insert into table1 values(wether);SQL insert into table1 values(gao);SQL select xm from table1 where soundex(xm)=soundex(weather);XM-weatherwether14.TRIM(s fro

55、m string)LEADING 剪掉前面的字符TRAILING 剪掉后面的字符如果不指定,默認(rèn)為空格符.2.日期型函數(shù)常用的時間格式掩碼如下：掩碼元素 含義YYYY 四位數(shù)年份 （如：2005） yearYY 二位數(shù)年份（如 05） Q 季度（1-4） MM 月份（01-12） monthWW 年的星期數(shù)（1-53），其中第一星期為年的第一天至第七天 W 月的星期數(shù)（1-5），其中第一星期為月的第一天至第七天 DDD 年的日（1-366） DD 月的日（1-31） D 周的日（1-7），其中周日為1，周六為7 dayHH24 24小時制（0-23） hourMI 分鐘（0-59） minut

56、eSS 秒（0-59） secondSSSSS 自午夜之后的秒（0-86399）日期時間函數(shù)：add_months(日期，number) 指定日期推遲number個月last_day(日期) 指定日期當(dāng)月的最后一天new_time(日期，時區(qū)簡寫) 調(diào)整時區(qū)next_day(日期，number) number表示周幾，星期日是1，指定number的日期（一周內(nèi)或一周后）months_between(日期1，日期2) 日期1和日期2之間有幾個月sysdate 系統(tǒng)當(dāng)期那日期和時間to_char.3. 其他函數(shù)NvlDecode.過程控制.1.分支語句：IF condition THENSeque

57、nce_of_statements;END IF;IF condition THENSequence_of_statement1;ELSESequence_of_statement2;END IF;IF condition1 THENSequence_of_statement1;ELSIF condition2 THENSequence_of_statement2;ELSIF condition3 THENSequence_of_statement3;END IF;.2.循環(huán)語句：LOOPSequence_of_statements;IF condition THENEXIT;END IF;E

58、ND LOOP; WHILE condition LOOPSequence_of_statements; I = i+1END LOOP;FOR counter IN lower_bound.higher_bound LOOPSequence_of_statements;END LOOP;.表連接.1.迪卡爾乘積連接條件被省略連接條件無效第一個表中的所有行與第二個表中的所有行連接.2.等值連接.3.非等值連接.4.外連接group by什么是分組函數(shù)分組函數(shù)可以對行集進(jìn)行操作，并且為每組給出一個結(jié)果分組函數(shù)基本語法 SELECT column, group_function(column),

59、. FROM table WHERE condition GROUP BY column HAVING . ORDER BY column;常用分組函數(shù)AVGCOUNTMAXMINSTDDEVSUMVARIANCE.DDL與DML、DCL及動態(tài)SQL本節(jié)重點掌握如下內(nèi)容DDLDMLDCL語言的功用以及區(qū)別什么時間使用動態(tài)SQL，動態(tài)SQL語法.1.DDL DML DCL關(guān)系數(shù)據(jù)庫語言分為三類DDL數(shù)據(jù)描述語言，包括create drop alter truncateTruncate table wangjie1;Drop table wangjie1create table test_wang

60、jie1 (v varchar2(1000),v1 varchar2(1000); alter table test_wangjie1 add (v3 varchar2(1000);alter table test_wangjie1 drop column v3;DML數(shù)據(jù)操作語言，包括select insert delete updateDCL數(shù)據(jù)控制語言，包括grant revoke.動態(tài)SQLOracle編譯PL/SQL程序塊分為兩種：其一為前期聯(lián)編（early binding），即SQL語句在程序編譯期間就已經(jīng)確定，大多數(shù)的編譯情況屬于這種類型；另外一種是后期聯(lián)編（late bindi