數(shù)據(jù)庫(kù)程序員面試分類真題8

數(shù)據(jù)庫(kù)程序員面試分類真題8簡(jiǎn)答題1.

在Oracle中，什么是異常?正確答案：異常(Exception)處理是用來(lái)處理正常執(zhí)行過(guò)程中未預(yù)料到的事件，包括程序塊的異常處理、預(yù)定義的錯(cuò)誤和自定義錯(cuò)誤。（江南博哥）如果PL/SQL程序塊一旦產(chǎn)生異常，而程序并沒(méi)有指出如何處理，那么程序就會(huì)自動(dòng)終止運(yùn)行。

異常處理部分一般放在PL/SQL程序體的后半部分，結(jié)構(gòu)如下。

EXCEPTION

WHENfirst_exception

THEN

＜codetohandlefirstexertion＞

WHENsecond_exception

THEN

＜codetohandlesecondexception＞

WHENOTHERSTHEN＜codetohandleOTHERSexception＞[考點(diǎn)]程序處理

2.

Oracle的異?？梢苑譃槟膸最?正確答案：Oracle將異常分為預(yù)定義異常、非預(yù)定義異常和自定義異常三種。

1.預(yù)定義異常(Predefined)

當(dāng)PL/SQL應(yīng)用程序違反了Oracle規(guī)定的限制時(shí)，就會(huì)隱含地觸發(fā)一個(gè)內(nèi)部異常，這就是預(yù)定義異常。預(yù)定義異常用于處理常見(jiàn)的Oracle錯(cuò)誤，對(duì)這種異常情況的處理，無(wú)須在程序中定義，由Oracle自動(dòng)將其觸發(fā)。它們?nèi)糠旁赑L/SQL自帶的標(biāo)準(zhǔn)包中，這樣程序員就無(wú)須再次定義了。預(yù)定義異常大約有20多個(gè)，下表是一些常見(jiàn)的預(yù)定義異常。錯(cuò)誤號(hào)異常錯(cuò)誤信息名稱說(shuō)明ORA-01403NO_DATA_FOUNDSELECTINTO沒(méi)有找到數(shù)據(jù)ORA-01422TOO_MANY_ROWSSELECTINTO返回多行ORA-06501PROGRAM_ERROR內(nèi)部錯(cuò)誤，需重新安裝數(shù)據(jù)字典視圖和PL/SQL包ORA-06511CURSOR_ALREADY_OPEN試圖打開(kāi)一個(gè)已存在的游標(biāo)ORA-06530ACCESS_INTO_NULL試圖為NULL對(duì)象的屬性賦值ORA-01012NOT_LOGGED_ON沒(méi)有連接到OracleORA-01001INVALID_CURSOR試圖使用一個(gè)無(wú)效的游標(biāo)ORA-00061TRANSACTION_BACKED_OUT由于發(fā)生死鎖事務(wù)被撤銷ORA-00051TIMEOUT_ON_RESOURCE在等待資源時(shí)發(fā)生超時(shí)ORA-00001DUP_VAL_ON_INDEX試圖破壞一個(gè)唯一性限制ORA-01017LOGIN_DENIED無(wú)效的用戶名/口令ORA-01476ZERO_DIVIDE試圖被零除ORA-01722INVALID_NUMBER轉(zhuǎn)換一個(gè)數(shù)字失敗ORA-06500STORAGE_ERROR內(nèi)存不夠或內(nèi)存被破壞觸發(fā)的內(nèi)部錯(cuò)誤ORA-06502VALUE_ERROR賦值操作，變量長(zhǎng)度不足，觸發(fā)該異常ORA-06504ROWTYPE_MISMATCH宿主游標(biāo)變量與PL/SQL變量有不兼容行類型ORA-06531COLLECTION_IS_NULL試圖給沒(méi)有初始化的嵌套表變量或者VARRY變量賦值ORA-06532SUBSCRIP_OUTSIDE_LIMIT對(duì)嵌套或VARRAY索引使用了負(fù)數(shù)ORA-06533SUBSCRIPT_BEYOND_COUNT對(duì)嵌套或VARRAY索引的引用大于集合中元素的個(gè)數(shù)

2.非預(yù)定義異常(NonPredefined)

非預(yù)定義異常用于處理預(yù)定義異常不能處理的異常，即其他標(biāo)準(zhǔn)的Oracle錯(cuò)誤。使用預(yù)定義異常只能處理系統(tǒng)預(yù)定義的20多個(gè)Oracle錯(cuò)誤，而當(dāng)使用PL/SQL開(kāi)發(fā)應(yīng)用程序時(shí)，可能會(huì)遇到其他的一些Oracle錯(cuò)誤。例如，在PL/SQL塊中執(zhí)行DML語(yǔ)句時(shí)違反了約束規(guī)定等。在這樣的情況下，就可以使用非預(yù)定義異常來(lái)處理。

Oracle提供了2個(gè)函數(shù)SQLCODE和SQLERRM用于返回錯(cuò)誤信息。

1)SQLCODE：返回錯(cuò)誤代碼。

2)SQLERRM：返回與錯(cuò)誤代碼關(guān)聯(lián)的消息。

這樣就可以在錯(cuò)誤日志表中記錄程序在執(zhí)行過(guò)程中發(fā)生的錯(cuò)誤信息了。

3.自定義異常(User_define)

自定義異常用于處理與Oracle錯(cuò)誤無(wú)關(guān)的其他情況。對(duì)這種異常情況的處理，需要用戶在程序中定義，然后顯式地在程序中將其觸發(fā)。

預(yù)定義異常和自定義異常都是與Oracle錯(cuò)誤相關(guān)的，并且出現(xiàn)的Oracle錯(cuò)誤會(huì)隱含地觸發(fā)相應(yīng)的異常；而自定義異常與Oracle錯(cuò)誤沒(méi)有任何關(guān)聯(lián)，它是由開(kāi)發(fā)人員為特定情況所定義的異常。當(dāng)與一個(gè)異常相關(guān)的錯(cuò)誤出現(xiàn)時(shí)，就會(huì)隱含觸發(fā)該異常。用戶定義異常是通過(guò)顯式使用RAISE語(yǔ)句來(lái)觸發(fā)。當(dāng)觸發(fā)一個(gè)異常時(shí)，控制程序就轉(zhuǎn)到異常塊部分，執(zhí)行錯(cuò)誤處理代碼。[考點(diǎn)]程序處理

3.

在Oracle中，拋出異常有哪幾種方式?正確答案：有三種方式可以拋出異常，分別是通過(guò)PL/SQL運(yùn)行引擎、使用RAISE語(yǔ)句和調(diào)用RAISE_APPLICATION_ERROR存儲(chǔ)過(guò)程。

當(dāng)數(shù)據(jù)庫(kù)或PL/SQL在運(yùn)行時(shí)發(fā)生了錯(cuò)誤，此時(shí)的異?？梢酝ㄟ^(guò)PL/SQL運(yùn)行時(shí)的引擎自動(dòng)拋出異常，這個(gè)無(wú)須過(guò)度關(guān)注。第二種是通過(guò)RAISE語(yǔ)句拋出異常。顯式拋出異常是程序員處理聲明異常的習(xí)慣用法，但RAISE不限于聲明了的異常，它可以拋出任何異常。比如下面一個(gè)訂單輸入的例子，若訂單大于庫(kù)存數(shù)量，則拋出異常，并且捕獲該異常、處理該異常。

第三種拋出異常的辦法是通過(guò)RAISE_APPLICATION_ERROR語(yǔ)句。RAISE_APPLICATION_ERROR是一個(gè)內(nèi)建函數(shù)，它用于拋出一個(gè)異常并給異常賦予一個(gè)錯(cuò)誤號(hào)以及錯(cuò)誤信息，它將應(yīng)用程序?qū)Ｓ械腻e(cuò)誤從服務(wù)器端轉(zhuǎn)達(dá)到客戶端應(yīng)用程序(其他機(jī)器上的SQL*Plus或者其他前臺(tái)開(kāi)發(fā)語(yǔ)言)。該函數(shù)可以在PL/SQL程序塊的執(zhí)行部分和異常部分被調(diào)用。

RAISE_APPLICATION_ERROR的聲明：

RAISE_APPLICATION_ERROR(ERROR_NUMBERINNUMBER,MESSAGEINVARCHAR2[,TRUE,FALSE]))

錯(cuò)誤號(hào)的范圍是-20000～-20999，這樣就不會(huì)與Oracle的任何錯(cuò)誤代碼發(fā)生沖突。錯(cuò)誤信息是文本字符串，最多為2048B。TRUE和FALSE表示是添加(TRUE)錯(cuò)誤堆棧(ERRORSTACK)還是覆蓋(OVERWRITE)錯(cuò)誤堆棧(FALSE)。默認(rèn)情況下是FALSE。

RAISE_APPLICATION_ERROR使用示例如下：

IFPRODUCT_NOT_FOUNDTHEN

RAISE_APPLICATION_ERROR(-20123,'INVALDPRODUCTCODE',TRUE);

ENDIF;

需要說(shuō)明的一點(diǎn)，當(dāng)該函數(shù)用于觸發(fā)器并且觸發(fā)事件在以下情況的時(shí)候并不能拋出錯(cuò)誤：

1)觸發(fā)事件為“AFTERSTARTUPONDATABASE”或“BEFORESHUTDOWNONDATABASE”。

2)觸發(fā)事件為“AFTERLOGONONDATABASE”且用戶擁有“ADMINISTERDATABASETRIGGER”權(quán)限。

3)觸發(fā)事件為“AFTERLOGONONSCHEMA”且用戶擁有“ALTERANYTRIGGER”權(quán)限。[考點(diǎn)]程序處理

4.

如何捕獲存儲(chǔ)過(guò)程中出現(xiàn)異常的行號(hào)?正確答案：使用函數(shù)DBMS_UTILITY.FORMAT_ERROR_BACKTRACE可以獲取到出現(xiàn)異常時(shí)候的程序的行號(hào)，而函數(shù)DBMS_UTILITY.FORMAT_ERROR_STACK可以獲取到出現(xiàn)異常時(shí)的錯(cuò)誤信息，作用和SQLERRM一樣。[考點(diǎn)]程序處理

5.

Oracle中的觸發(fā)器有哪些類型?正確答案：Oracle數(shù)據(jù)庫(kù)有4種觸發(fā)器，分別是DML觸發(fā)器、替代觸發(fā)器(INSTEADOF觸發(fā)器)、DDL觸發(fā)器和系統(tǒng)觸發(fā)器。一般的應(yīng)用系統(tǒng)中都使用到DML觸發(fā)器和替代觸發(fā)器，而DDL和系統(tǒng)觸發(fā)器是DBA管理數(shù)據(jù)庫(kù)用得比較多。下圖是Oracle觸發(fā)器的分類圖。

6.

Oracle的自治事務(wù)是什么?正確答案：Oracle的自治事務(wù)(AutonomousTransaction)是將一個(gè)主事務(wù)分割成幾個(gè)子事務(wù)，在執(zhí)行完子事務(wù)以后再繼續(xù)執(zhí)行主事務(wù)。這里的關(guān)鍵是，子事務(wù)是獨(dú)立于主事務(wù)的，子事務(wù)中的ROLLBACK和COMMIT操作只會(huì)影響子事務(wù)中的DML操作；同樣，主事務(wù)中的ROLLBACK和COMMIT操作只會(huì)影響主事務(wù)中的DML操作，而不會(huì)影響子事務(wù)中的操作。在子事務(wù)中已經(jīng)COMMIT的操作，不會(huì)被主事務(wù)中的ROLLBACK撤銷。

可以在程序開(kāi)頭使用如下命令實(shí)現(xiàn)自治事務(wù)：

PRAGMAAUTONOMOUS_TRANSACTION;

定義自治事務(wù)必須遵循以下規(guī)則：

1)如果要被定義為自治事務(wù)的程序是匿名的，那么它必須是一個(gè)最外層的程序塊。

2)如果要被定義為自治事務(wù)的程序不是匿名的，那么它必須是函數(shù)或者存儲(chǔ)過(guò)程。在一個(gè)包中，只有其中的函數(shù)或存儲(chǔ)過(guò)程能夠被定義成自治事務(wù)。整個(gè)包不能聲明為自治事務(wù)。

3)一個(gè)對(duì)象的方法可以聲明為自治事務(wù)。

4)觸發(fā)器可以聲明為自治事務(wù)。

5)內(nèi)嵌程序塊不能聲明為自治事務(wù)。

需要注意的是，對(duì)于一個(gè)匿名的自治事務(wù)程序塊來(lái)說(shuō)，只有這個(gè)塊的BEGIN和END之間的代碼被看作是自治事務(wù)。

自治事務(wù)可以用來(lái)解決“ORA-14551:無(wú)法在查詢中執(zhí)行DML操作”錯(cuò)誤。觸發(fā)器無(wú)法包含COMMIT語(yǔ)句，除非有PRAGMAAUTONOMOUS_TRANSACTION標(biāo)記。但是，只有觸發(fā)中的語(yǔ)句才能被提交，主事務(wù)則不行。[考點(diǎn)]程序處理

7.

Oracle常用日期處理函數(shù)有哪些?這些函數(shù)可以實(shí)現(xiàn)哪些功能?正確答案：有關(guān)日期函數(shù)需要了解以下幾點(diǎn)：

1)日期函數(shù)用于處理DATE類型的數(shù)據(jù)。

2)在日期上加上或減去一個(gè)數(shù)字結(jié)果仍為日期。

3)兩個(gè)日期相減返回日期之間相差的天數(shù)。

4)默認(rèn)情況下，日期格式為DD-MON-RR。

5)查詢當(dāng)前數(shù)據(jù)庫(kù)日期格式的命令：SELECTSYS_CONTEXT('USERENV','NLS_DATE_FORMAT')FROMDUAL;。

6)SYSDATE：該函數(shù)返回系統(tǒng)時(shí)間。

7)LAST_DAY(D)：返回指定日期所在月份的最后一天。

8)MONTHS_BETWEEN：表示兩個(gè)日期的月份之差，即在給定的兩個(gè)日期之間有多少個(gè)月。

9)ADD_MONTHS(D,N)：該函數(shù)將給定的日期增加N個(gè)月。當(dāng)N為正數(shù)時(shí)，該函數(shù)將給定的日期增加N個(gè)月，為負(fù)數(shù)時(shí)減去N個(gè)月。該函數(shù)很常用，可以用來(lái)表示上個(gè)月、下個(gè)月、去年和下一年等。

10)NEXT_DAY(D,N)：返回以時(shí)間點(diǎn)D為基準(zhǔn)(開(kāi)始)，下一個(gè)“目標(biāo)日N”的日期。

由于數(shù)據(jù)庫(kù)以數(shù)字方式存儲(chǔ)日期，因此，日期類型可以進(jìn)行算術(shù)運(yùn)算(加法或減法)。例如，可以

給日期增加或減去一個(gè)數(shù)字，得到的結(jié)果還是一個(gè)日期值，兩個(gè)日期相減，得到兩個(gè)日期之間的天數(shù)，

用小時(shí)除以24就可以得到天數(shù)。

另外，日期中常用到的一個(gè)修改日期語(yǔ)言的SQL：

修改為英文：ALTERSESSIONSETNLS_LANGUAGE='AMERICAN';

修改為中文：ALTERSESSIONSETNLS_LANGUAGE='SIMPLIFIEDCHINESE';

在英語(yǔ)的環(huán)境中，查詢星期時(shí)要用英文，示例代碼如下：

SYS@lhrdb21＞SELECTNEXT_DAY(SYSDATE,'星期二')FROMDUAL;

SELECTNEXT_DAY(SYSDATE,'星期二')FROMDUAL

*

ERRORatline1;

ORA-01846:notavaliddayoftheweek

SYS@lhrdb21＞ALTERSESSIONSETNLS_LANGUAGE='SIMPLIFIEDCHINESE';

會(huì)話已更改

SYS@lhrdb21＞SELECTNEXT_DAY(SYSDATE,'星期二')FROMDUAL;

NEXT_DAY(SYSDATE,'

-----------------

2016-09-1309:32:58[考點(diǎn)]函數(shù)相關(guān)

8.

如何判斷一個(gè)字符串是否含有中文漢字?正確答案：可以利用LENGTH、LENGTHB和TO_SINGLE_BYTE函數(shù)來(lái)解決這個(gè)問(wèn)題。其中，LENGTH返回以字符為單位的長(zhǎng)度，LENGTHB返回以字節(jié)為單位的長(zhǎng)度，TO_SINGLE_BYTE將字符串中的多字節(jié)字符轉(zhuǎn)化為單字節(jié)字符。此外，還可以使用ASCIISTR和CONVERT函數(shù)找出包含漢字的字符串。若利用LENGTH、LENGTHB和TO_SINGLE_BYTE函數(shù)來(lái)實(shí)現(xiàn)該需求，則類似的WHERE條件為“LENGTHB(COL)＜＞LENGTH(COL)ANDLENGTHB(TO_SINGLE_BYTE(COL))＜＞LENGTH(TO_SINGLE_BYTE(COL))”。[考點(diǎn)]函數(shù)相關(guān)

9.

如何計(jì)算一個(gè)字符在字符串中出現(xiàn)的次數(shù)?正確答案：可以利用REGEXP_COUNT、REGEXP_REPLACE或TRANSLATE系統(tǒng)函數(shù)，也可以自定義函數(shù)來(lái)計(jì)算一個(gè)字符在字符串中出現(xiàn)的次數(shù)。[考點(diǎn)]函數(shù)相關(guān)

10.

在Oracle中，如何判斷一個(gè)字符串是否為數(shù)字?正確答案：可以有2種辦法來(lái)判斷，第一種辦法為REPLACE加TRANSLATE函數(shù)，在程序中可以直接使用“TRANSLATE(REPLACE('入?yún)?,'.',"),'/1234567890','/')ISNULL”來(lái)判斷入?yún)閿?shù)字，缺點(diǎn)是，若入?yún)⒂?個(gè)小數(shù)點(diǎn)或含有加減號(hào)則不能判斷。第二種辦法為使用TO_NUMBER或REGEXP_LIKE來(lái)自定義函數(shù)進(jìn)行判斷。[考點(diǎn)]函數(shù)相關(guān)

11.

如何將一個(gè)IP地址分解為4個(gè)字段?正確答案：使用Oracle的正則表達(dá)式函數(shù)REGEXP_SUBSTR，如下：

SYS@lhrdb＞SELECTREGEXP_SUBSTR(V.IP,'[^.]+',1,1)A,

2

REGEXP_SUBSTR(V.IP,'[^.]+',1,2)B,

3

REGEXP_SUBSTR(V.IP,'[^.]+',1,3)C,

4

REGEXP_SUBSTR(V.IP,'[^.]+',1,4)D

5

FROM

(SELECT'192.168.59.130'

IPFROMDUAL)V;

A

B

C

D

192168

59

130[考點(diǎn)]函數(shù)相關(guān)

12.

動(dòng)態(tài)SQL是什么?正確答案：在PL/SQL開(kāi)發(fā)過(guò)程中，使用SQL或PL/SQL可以實(shí)現(xiàn)大部分的需求，但是，在某些特殊的情況下，在PL/SQL中使用標(biāo)準(zhǔn)的SQL語(yǔ)句或DML語(yǔ)句不能實(shí)現(xiàn)自己的需求，例如，需要?jiǎng)討B(tài)建表或執(zhí)行某個(gè)不確定的操作的時(shí)候，就需要?jiǎng)討B(tài)執(zhí)行。此外，DDL語(yǔ)句及系統(tǒng)控制語(yǔ)句也不能在PL/SQL中直接使用，這就需要使用動(dòng)態(tài)SQL來(lái)實(shí)現(xiàn)。因此，在Oracle數(shù)據(jù)庫(kù)開(kāi)發(fā)PL/SQL塊中，可以把SQL分為靜態(tài)SQL和動(dòng)態(tài)SQL。所謂靜態(tài)SQL指的是在PL/SQL塊中使用的SQL語(yǔ)句在編譯時(shí)是明確的，執(zhí)行的是確定對(duì)象。動(dòng)態(tài)SQL是指在PL/SQL塊編譯時(shí)SQL語(yǔ)句是不確定的，如根據(jù)用戶輸入的參數(shù)的不同而執(zhí)行不同的操作。編譯程序?qū)?dòng)態(tài)語(yǔ)句部分不進(jìn)行處理，只是在程序運(yùn)行時(shí)動(dòng)態(tài)地創(chuàng)建語(yǔ)句、對(duì)語(yǔ)句進(jìn)行語(yǔ)法分析并執(zhí)行該語(yǔ)句。動(dòng)態(tài)SQL允許在SQL客戶模塊或嵌入式宿主程序的執(zhí)行過(guò)程中執(zhí)行動(dòng)態(tài)生成的SQL語(yǔ)句，動(dòng)態(tài)SQL語(yǔ)句在程序編譯時(shí)尚未確定。其中，有些部分需要在程序執(zhí)行過(guò)程中臨時(shí)生成的SQL語(yǔ)句，SQL標(biāo)準(zhǔn)引入動(dòng)態(tài)SQL的原因是由于靜態(tài)SQL不能提供足夠的編程靈活性。

動(dòng)態(tài)SQL是使用EXECUTEIMMEDIATE語(yǔ)句來(lái)實(shí)現(xiàn)的。[考點(diǎn)]高級(jí)操作

13.

假定SERV表有A、B、C三個(gè)字段：SERV(ANUMBER(10),BNUMBER(10),CNUMBER(10))。表SERV的內(nèi)容如下：ABC110702305011080

以下兩段PL/SQL的功能是根據(jù)A列的值，查找出對(duì)應(yīng)B列的值賦予變量X，請(qǐng)分別判斷這兩段PL/SQL是否能正常執(zhí)行，若不能正常執(zhí)行，請(qǐng)指出錯(cuò)誤的原因并修改。

1)

DECLARE

XNUMBER;

BEGIN

SELECTBINTOXFROMSERVWHEREA=1;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(X));

END;

2)

DECLARE

XNUMBER;

BEGIN

SELECTBINTOXFROMSERVWHEREA=2;

DBMS_OUTPUT.PUT.LINE(TO_CHAR(X));

END:正確答案：這兩段程序除了WHERE語(yǔ)句后的值不同以外，其他均一樣。對(duì)于程序1)，當(dāng)A=1時(shí)，返回了2行記錄；對(duì)于程序2)，當(dāng)A=2時(shí)，返回了1行記錄。對(duì)于變量X而言，只能接受一個(gè)值，所以，程序2)執(zhí)行不報(bào)錯(cuò)，程序1)執(zhí)行報(bào)錯(cuò)：ORA-01422:exactfetchreturnsmorethanrequestednumberofrows。

對(duì)于程序1)有兩種修改方法，第一種就是將“SELECTBINTOXFROMSERVWHEREA=1;”修改為“SELECTDISTINCTBINTOXFROMSERVWHEREA=1;”。第二種方法就是返回集合類型，修改后的程序塊如下：

DECLARE

VARCURSORSYS_REFCURSOR;

X

SERV%ROWTYPE;

BEGIN

OPENVARCURSORFORSELECTT.*FROMSERVTWHEREA=1;

LOOP

FETCHVARCURSORINTOX;

EXITWHENVARCURSOR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(R.B);

ENDLOOP;

END;[考點(diǎn)]高級(jí)操作

14.

行列互換有哪些方法?正確答案：行列轉(zhuǎn)換包括以下六種情況：①列轉(zhuǎn)行；②行轉(zhuǎn)列；③多列轉(zhuǎn)換成字符串；④多行轉(zhuǎn)換成字符串；⑤字符串轉(zhuǎn)換成多列；⑥字符串轉(zhuǎn)換成多行。其中，重點(diǎn)是行轉(zhuǎn)列和字符串轉(zhuǎn)換成多行。[考點(diǎn)]高級(jí)操作

15.

數(shù)據(jù)庫(kù)中有一張如下所示的表，表名為SALES。年季度銷售量19911111991212199131319914141992121199222219923231992424要求：寫(xiě)一個(gè)SQL語(yǔ)句查詢出如下所示的結(jié)果。年一季度二季度三季度四季度199111121314199221222324正確答案：這是一道行轉(zhuǎn)列的題目，首先建立表SALES：

CREATETABLESALES(年

NUMBER,季度NUMBER,銷售量NUMBER);

INSERTINTOSALESVALUES(1991,1,11);

INSERTINTOSALESVALUES(1991,2,12);

INSERTINTOSALESVALUES(1991,3,13);

INSERTINTOSALESVALUES(1991,4,14);

INSERTINTOSALESVALUES(1992,1,21);

INSERTINTOSALESVALUES(1992,2,22);

INSERTINTOsALESVALUES(1992,3,23);

INSERTINTOSALESVALUES(1992,4,24);

SELECT*FROMSALES;

此題若使用聚合函數(shù)+DECODE或CASE來(lái)回答，則代碼如下：

SELECT年，

SUM(CASEWHEN季度=1THEN銷售量ELSE0END)AS一季度，

SUM(CASEWHEN季度=2THEN銷售量ELSE0END)AS二季度，

SUM(CASEWHEN季度=3THEN銷售量ELSE0END)AS三季度，

SUM(CASEWHEN季度=4THEN銷售量ELSE0END)AS四季度

FROMSALESGROUPBY年ORDERBYT.教師號(hào);

此題若使用PIVOT函數(shù)，則代碼如下：

SELECT*FROMSALESPIVOT(SUM(銷售量)FOR季度IN(1AS“一季度",2AS"二季度",3AS"三季度",4AS"四季度"))ORDERBY1;

此題若使用臨時(shí)表的方式，則代碼如下：

SELECTT.年,NVL(SUM(T1.一季度),0)AS"一季度",NVL(SUM(T2.二季度),0)AS"二季度",

NVL(SUM(T3.三季度),0)AS"三季度",

NVL(SUM(T4.四季度),0)AS"四季度"

FROM(SELECT年,銷售量AS"一季度"FROMSALESAWHEREA.季度='1')T1,

(SELECT年,銷售量AS"二季度"FROMSALESAWHEREA.季度='2')T2,

(SELECT年,銷售量AS"三季度"FROMSALESAWHEREA.季度='3')T3,

(SELECT年,銷售量AS"四季度"FROMSALESAWHEREA.季度='4')T4,

(SELECTDISTINCT年FROMSALES)T

WHERET.年=T1.年(+)ANDT.年=T2.年(+)ANDT.年=T3.年(+)ANDT.年=T4.年(+)

GROUPBYT.年ORDERBY1;[考點(diǎn)]高級(jí)操作

16.

如何刪除表中重復(fù)的記錄?正確答案：在平時(shí)工作中可能會(huì)遇到這種情況，當(dāng)試圖對(duì)表中的某一列或幾列創(chuàng)建唯一索引時(shí)，系統(tǒng)提示ORA-01452：不能創(chuàng)建唯一索引，發(fā)現(xiàn)重復(fù)記錄。這個(gè)時(shí)候只能創(chuàng)建普通索引或者刪除重復(fù)記錄后再創(chuàng)建唯一索引。

重復(fù)的數(shù)據(jù)可能有這樣兩種情況：第一種是表中只有某些字段一樣，第二種是兩行記錄完全一樣。刪除重復(fù)記錄后的結(jié)果也分為兩種，第一種是重復(fù)的記錄全部刪除，第二種是重復(fù)的記錄只保留最新的一條記錄。在一般業(yè)務(wù)中，第二種的情況較多。

1.刪除重復(fù)記錄的方法原理

在Oracle中，每一條記錄都有一個(gè)ROWID，ROWID在整個(gè)數(shù)據(jù)庫(kù)中是唯一的，ROWID確定了每條記錄是在Oracle中的哪一個(gè)數(shù)據(jù)文件、塊、行上。在重復(fù)的記錄中，可能所有列上的內(nèi)容都相同，但ROWID不會(huì)相同，所以，只要保留重復(fù)記錄中那些具有最大的ROWID的記錄，其余的全部刪除。

2.刪除重復(fù)記錄的方法

若想要?jiǎng)h除部分字段重復(fù)的數(shù)據(jù)，則使用下面語(yǔ)句進(jìn)行刪除，下面的語(yǔ)句是刪除表中字段1和字段2重復(fù)的數(shù)據(jù)：

DELETEFROM表名WHERE(字段1,字段2)IN(SELECT字段1,字段2FROM表名GROUPBY字段1,字段2HAVINGCOUNT(1)＞1);

也可以利用臨時(shí)表的方式，先將查詢到的重復(fù)的數(shù)據(jù)插入一個(gè)臨時(shí)表中，然后進(jìn)行刪除，這樣，執(zhí)行刪除的時(shí)候就不用再進(jìn)行一次查詢了。代碼如下：

CREATETABLE臨時(shí)表AS(SELECT字段1,字段2,COUNT(*)FROM表名GROUPBY字段1,字段2HAVINGCOUNT(*)＞1);

上面這句話的功能是建立臨時(shí)表，并將查詢到的數(shù)據(jù)插入其中。有了上面的執(zhí)行結(jié)果，下面就可以進(jìn)行刪除操作了：

DELETEFROM表名AWHERE(字段1,字段2)IN(SELECT字段1,字段2FROM臨時(shí)表);

假如想保留重復(fù)數(shù)據(jù)中最新的一條記錄，應(yīng)該怎么做呢?可以利用ROWID，保留重復(fù)數(shù)據(jù)中ROWID最大的一條記錄即可：

DELETEFROMTABLE_NAMEWHEREROWIDNOTIN(SELECTMAX(ROWID)FROMTABLE_NAMED

GROUPBYD.COL1,D.COL2);

重復(fù)數(shù)據(jù)刪除技術(shù)可以提供更大的備份容量，實(shí)現(xiàn)更長(zhǎng)時(shí)間的數(shù)據(jù)保留，還能實(shí)現(xiàn)備份數(shù)據(jù)的持續(xù)驗(yàn)證，提高數(shù)據(jù)恢復(fù)服務(wù)水平，方便實(shí)現(xiàn)數(shù)據(jù)容災(zāi)等。[考點(diǎn)]高級(jí)操作

17.

什么是分區(qū)表?正確答案：當(dāng)表中的數(shù)據(jù)量不斷增大時(shí)，查詢數(shù)據(jù)的速度會(huì)變慢，應(yīng)用程序的性能就會(huì)下降，這時(shí)應(yīng)該考慮對(duì)表進(jìn)行分區(qū)。對(duì)大表進(jìn)行分區(qū)，將有益于大表操作的性能和大表數(shù)據(jù)的維護(hù)。官方文檔說(shuō)通常當(dāng)表的大小超過(guò)2GB，或?qū)τ贠LTP系統(tǒng)，當(dāng)表的記錄超過(guò)1000萬(wàn)時(shí)，都應(yīng)考慮對(duì)表進(jìn)行分區(qū)。當(dāng)對(duì)表進(jìn)行分區(qū)后，在邏輯上，表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上可能存放到多個(gè)表空間或物理文件上。當(dāng)查詢數(shù)據(jù)時(shí)，不至于每次都掃描整張表。Oracle可以將大表或索引分成若干個(gè)更小、更方便管理的部分，每一部分稱為一個(gè)分區(qū)，這樣的表稱為分區(qū)表。SQL語(yǔ)句使用分區(qū)表比全表能提供更好的數(shù)據(jù)處理與訪問(wèn)的性能。即使某些分區(qū)不可用，其他分區(qū)仍然可用，這稱為分區(qū)獨(dú)立性。

分區(qū)表的一些限制條件：①簇表不能進(jìn)行分區(qū)；②不能分割含有LONG或LONGRAW列的表；③索引組織表不能進(jìn)行范圍分區(qū)。[考點(diǎn)]分區(qū)表

18.

分區(qū)表有什么優(yōu)點(diǎn)?正確答案：分區(qū)表有如下的優(yōu)點(diǎn)：

1)增強(qiáng)可用性。如果表的一個(gè)分區(qū)由于系統(tǒng)故障而不能使用，那么表的其余好的分區(qū)仍可以使用。

2)減少關(guān)閉時(shí)間。如果系統(tǒng)故障只影響表的一部分分區(qū)，那么只有這部分分區(qū)需要修復(fù)，可能比整個(gè)大表修復(fù)花的時(shí)間更少。

3)維護(hù)輕松。單獨(dú)管理每個(gè)分區(qū)比管理單個(gè)大表要輕松得多。

4)均衡I/O?？梢园驯淼牟煌謪^(qū)分配到不同的磁盤(pán)來(lái)平衡I/O，改善性能。

5)改善性能。對(duì)大表的查詢、增加、修改等操作可以分解到表的不同分區(qū)來(lái)并行執(zhí)行，可使運(yùn)行速度更快。

6)分區(qū)對(duì)用戶透明，最終用戶感覺(jué)不到分區(qū)的存在。[考點(diǎn)]分區(qū)表

19.

分區(qū)表有哪幾類?如何選擇用哪種類型的分區(qū)表?正確答案：Oracle的分區(qū)可以分為范圍分區(qū)(RANGEPARTITION)、哈希分區(qū)(HASHPARTITION)、列表分區(qū)(IISTPARTITION)、復(fù)合分區(qū)(組合分區(qū))、INTERVAL分區(qū)(間隔分區(qū))和系統(tǒng)分區(qū)。其中，INTERVAL分區(qū)和系統(tǒng)分區(qū)是Oracle11g的新特性，尤其INTERVAL分區(qū)在生產(chǎn)環(huán)境中還是比較實(shí)用的。對(duì)于海量數(shù)據(jù)的數(shù)據(jù)庫(kù)設(shè)計(jì)，分區(qū)的設(shè)計(jì)非常重要。例如，對(duì)于一個(gè)大表，應(yīng)該采用哪種類型的分區(qū)，對(duì)于以后數(shù)據(jù)庫(kù)的性能和管理至關(guān)重要。其實(shí)，范圍分區(qū)、哈希分區(qū)和列表分區(qū)這三種分區(qū)的特點(diǎn)都非常明顯：

1)如果需要進(jìn)行數(shù)據(jù)的過(guò)期化處理，那么范圍分區(qū)基本上是唯一的選擇。

2)如果需要數(shù)據(jù)的均勻分布，那么可以考慮使用哈希分區(qū)。

3)如果數(shù)據(jù)的值可以很好地對(duì)應(yīng)于某個(gè)分區(qū)，那么就可以考慮使用列表分區(qū)。

在上面的原則基礎(chǔ)上，再結(jié)合性能的影響因素，來(lái)最終確定使用哪種類型的分區(qū)。

如果選擇的分區(qū)不能確保各分區(qū)內(nèi)數(shù)據(jù)量的基本平均，那么這種分區(qū)方式有可能是不恰當(dāng)?shù)摹１热鐚?duì)于RANGE分區(qū)，假設(shè)分了10個(gè)分區(qū)，而其中一個(gè)分區(qū)中的記錄數(shù)占總記錄數(shù)的90%，其他9個(gè)分區(qū)只占總記錄數(shù)的10%，則這個(gè)分區(qū)方式就起不到數(shù)據(jù)平衡的作用。[考點(diǎn)]分區(qū)表

20.

如何判斷一張表是否是分區(qū)表?正確答案：

如果這個(gè)視圖里的PARTITIONED列的值為YES，那么說(shuō)明該表就是分區(qū)表。[考點(diǎn)]分區(qū)表

21.

分區(qū)表在查詢的時(shí)候如何避免全分區(qū)掃描?正確答案：Oracle的分區(qū)掃描方式可以分為單分區(qū)和多分區(qū)掃描。

1)如果要求單分區(qū)查詢，那么需要加上PARTITION關(guān)鍵字。

2)如果要求不能全分區(qū)查詢，那么在WHERE條件中應(yīng)該加上分區(qū)鍵的過(guò)濾條件。如下，CREATED列是分區(qū)鍵：

[考點(diǎn)]分區(qū)表

22.

在查詢分區(qū)表的時(shí)候需要注意哪些問(wèn)題?正確答案：在使用分區(qū)表的時(shí)候需要注意以下幾方面：

1)在查詢分區(qū)表時(shí)盡量帶上分區(qū)鍵過(guò)濾條件，否則可能引起全分區(qū)掃描。

2)在設(shè)計(jì)分區(qū)表時(shí)，避免數(shù)據(jù)都進(jìn)入默認(rèn)分區(qū)，從而導(dǎo)致出現(xiàn)默認(rèn)分區(qū)超大或各個(gè)分區(qū)大小嚴(yán)重不均衡的情況，失去分區(qū)表的意義。

3)需要特別注意分區(qū)表性能比普通表性能差的情況。這種情況的本質(zhì)原因是，雖然分區(qū)表的分區(qū)索引比全局索引要小很多，但是由于沒(méi)有掃描指定的分區(qū)，而是掃描了很多個(gè)小的索引，這些小索引的高度累計(jì)起來(lái)一般都比全局索引要高。索引的范圍檢索性能是由索引的高度(BLEVEL)決定的，而不是由索引的大小決定。所以，性能差異很明顯。[考點(diǎn)]分區(qū)表

23.

分區(qū)表常用數(shù)據(jù)字典視圖有哪些?正確答案：Oracle分區(qū)表相關(guān)數(shù)據(jù)字典視圖如下：

1)顯示數(shù)據(jù)庫(kù)所有分區(qū)表的信息：DBAPARTTABLES。

2)顯示表分區(qū)信息，顯示數(shù)據(jù)庫(kù)所有分區(qū)表的詳細(xì)分區(qū)信息：DBA_TAB_PARTITIONS。

3)顯示子分區(qū)信息，顯示數(shù)據(jù)庫(kù)所有復(fù)合分區(qū)表的子分區(qū)信息：DBA_TAB_SUBPARTITIONS。

4)顯示分區(qū)列，顯示數(shù)據(jù)庫(kù)所有分區(qū)表的分區(qū)列信息：DBA_PART_KEY_COLUMNS。

5)顯示子分區(qū)列，顯示數(shù)據(jù)庫(kù)所有分區(qū)表的子分區(qū)列信息：DBA_SUBPART_KE