Oracle程序員面試分類模擬7簡答題1.

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

動態(tài)SQL是使用EXECUTEIMMEDIATE語句來實(shí)現(xiàn)的。

2.

如何使用批量動態(tài)SQL(FORALL及BULK子句的使用)?正確答案：批量動態(tài)SQL即在動態(tài)SQL中使用BULK子句，或使用游標(biāo)變量時在FETCH中使用BULK，或在FORALL子句中使用BULK子句來實(shí)現(xiàn)。

如果一個循環(huán)內(nèi)執(zhí)行了INSERT、DELETE或UPDATE等語句引用了集合元素，那么可以將其移動到一個FORALL子句中。如果SELECTINTO、FETCHINTO或RETURNINGINTO子句引用了一個集合，那么應(yīng)該使用BULKCOLLECT子句進(jìn)行合并，從而來提高程序的性能。

(1)動態(tài)SQL中使用BULK子句的語法

使用BULKCOLLECTINTO子句處理動態(tài)SQL中的多行查詢可以加快處理速度，從而提高應(yīng)用程序的性能。當(dāng)使用BULK子句時，集合類型可以是PL/SQL所支持的索引表、嵌套表和VARRY，但集合元素必須使用SQL數(shù)據(jù)類型。常用的三種語句支持BULK子句，分別為EXECUTEIMMEDIATE，F(xiàn)ETCH和FORALL。

(2)使用EXECUTEIMMEDIATE結(jié)合BULK子句處理DML語句返回子句下面的例子，首先定義了兩個索引表類型以及其變量，接下來使用動態(tài)SQL語句來更新T_20170104_LHR的薪水，使用EXECUTEIMMEDIATE配合BULKCOLLECTINTO來處理結(jié)果集。

運(yùn)行以上程序輸出結(jié)果如下：

(3)使用EXECUTEIMMEDIATE結(jié)合BULK子句處理多行查詢下面示例中，與前一個示例相同，只不過其動態(tài)SQL由查詢語句組成，且返回多個結(jié)果集，同樣使用了BULKCOLLECTINTO來傳遞結(jié)果。

運(yùn)行以上程序輸出結(jié)果如下：

(4)使用FETCH子句結(jié)合BULK子句處理多行結(jié)果集

下面的示例中首先定義了游標(biāo)類型、游標(biāo)變量以及復(fù)合類型和復(fù)合變量，接下來從動態(tài)SQL中OPEN游標(biāo)，然后使用FETCH將結(jié)果存放到復(fù)合變量中。即使用OPEN，F(xiàn)ETCH代替了EXECUTEIMMEDIATE來完成動態(tài)SQL的執(zhí)行。

運(yùn)行以上程序輸出結(jié)果如下：

(5)在FORALL子句中使用BULK子句下面是FORALL子句的語法：

FORALL子句允許為動態(tài)SQL輸入變量，但FORALL子句僅支持DML(INSERT、DELETE、UPDATE)語句，不支持動態(tài)的SELECT語句。

在下面的示例中，首先聲明了兩個復(fù)合類型以及復(fù)合變量，接下來為復(fù)合變量ENAME_TABLE賦值，以形成動態(tài)SQL語句。緊接著使用FORALL子句結(jié)合EXECUTEIMMEDIATE來提取結(jié)果集。

運(yùn)行以上程序輸出結(jié)果如下：

3.

存儲過程或函數(shù)如何返回集合類型?正確答案：TABLE()函數(shù)可接受查詢語句或游標(biāo)作為輸入?yún)?shù)，并可輸出多行數(shù)據(jù)，稱為表函數(shù)。所以，存儲過程或函數(shù)返回集合類型主要采用的是表函數(shù)和PIPELINED函數(shù)(管道化表函數(shù))及數(shù)組結(jié)合的方式。當(dāng)然，也可以采用存儲過程返回系統(tǒng)游標(biāo)SYS_REFCURSOR或自定義游標(biāo)的方式。

有關(guān)存儲過程或函數(shù)返回集合類型的寫法有多種，作者把這多種方式寫成了一個包，己發(fā)布到博客和公眾號上，讀者可白行下載閱讀。

4.

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

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

(1)

(2)

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

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

5.

行列互換有哪些方法?正確答案：行列轉(zhuǎn)換包括以下6種情況：①列轉(zhuǎn)行。②行轉(zhuǎn)列。③多列轉(zhuǎn)換成字符串。④多行轉(zhuǎn)換成字符串。⑤字符串轉(zhuǎn)換成多列。⑥字符串轉(zhuǎn)換成多行。其中，重點(diǎn)是行轉(zhuǎn)列和字符串轉(zhuǎn)換成多行。下面將對這幾種情況做簡要說明，詳細(xì)轉(zhuǎn)換過程請參考隨書pdf文檔。

(1)列轉(zhuǎn)行

列轉(zhuǎn)行就是將原表中的列名作為轉(zhuǎn)換后的表的內(nèi)容。列轉(zhuǎn)行主要采用UNIONALL來完成。

(2)行轉(zhuǎn)列

行轉(zhuǎn)列就是將行數(shù)據(jù)內(nèi)容作為列名。主要采用MAX和DECODE函數(shù)來完成。

(3)多列轉(zhuǎn)換成字符串

使用||或CONCAT函數(shù)實(shí)現(xiàn)。

(4)多行轉(zhuǎn)換成字符串

可以采用SYS_CONNECT_BY_PATH來完成。

(5)字符串轉(zhuǎn)換成多列

實(shí)際上就是一個字符串拆分的問題。主要采用SUBSTR和INSTR來完成。

(6)字符串轉(zhuǎn)換成多行

主要采用UNIONALL、SUBSTR和INSTR來完成，對于其他類型的轉(zhuǎn)換請參考隨書pdf文檔。還有幾類特殊的轉(zhuǎn)換如下：

結(jié)果：

若是字符串類型，則如下：

結(jié)果：

6.

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

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

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

此題若使用臨時表的方式，如下：

7.

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

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

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

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

(2)刪除重復(fù)記錄的方法若想要刪除部分字段重復(fù)的數(shù)據(jù)，則使用下面語句進(jìn)行刪除，下面的語句是刪除表中字段1和字段2重復(fù)的數(shù)據(jù)：

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

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

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

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

8.

分區(qū)表有什么優(yōu)點(diǎn)?分區(qū)表有哪幾類?如何選擇用哪種類型的分區(qū)表?正確答案：當(dāng)表中的數(shù)據(jù)量不斷增大時，查詢數(shù)據(jù)的速度就會變慢，應(yīng)用程序的性能就會下降，這時就應(yīng)該考慮對表進(jìn)行分區(qū)。當(dāng)對表進(jìn)行分區(qū)后，在邏輯上，表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上可能存放到多個表空間或物理文件上。當(dāng)查詢數(shù)據(jù)時，不至于每次都掃描整張表。Oracle可以將大表或索引分成若干個更小、更方便管理的部分，每一部分稱為一個分區(qū)，這樣的表稱為分區(qū)表。SQL語句使用分區(qū)表比全表能提供更好的數(shù)據(jù)處理與訪問的性能。即使某些分區(qū)不可用，其他分區(qū)仍然可用，這稱為分區(qū)獨(dú)立性。

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

(1)何時考慮分區(qū)?對大表進(jìn)行分區(qū)，將有益于大表操作的性能和大表的數(shù)據(jù)的維護(hù)。官方文檔說通常當(dāng)表的大小超過2GB，或?qū)τ贠LTP系統(tǒng)，當(dāng)表的記錄超過1000萬條時，都應(yīng)考慮對表進(jìn)行分區(qū)。

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

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

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

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

4)均衡I/O：可以把表的不同分區(qū)分配到不同的磁盤來平衡I/O，改善性能。

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

6)分區(qū)對用戶透明，最終用戶感覺不到分區(qū)的存在。

(3)有哪些類型的分區(qū)?如何選擇用哪種類型的分區(qū)表?Oracle的分區(qū)可以分為：

1)范圍分區(qū)(RANGEPARTITION)。

2)哈希分區(qū)(HASHPARTITION)。

3)列表分區(qū)(LISTPARTITION)。

4)復(fù)合分區(qū)(組合分區(qū))。

5)INTERVAL分區(qū)(間隔分區(qū))。

6)系統(tǒng)分區(qū)。

INTERVAL分區(qū)和系統(tǒng)分區(qū)是Oracle11g的新特性，由于篇幅原因，本書中不講解這2個分區(qū)，讀者可自行查閱官方文檔進(jìn)行學(xué)習(xí)。尤其對于INTERVAL分區(qū)在生產(chǎn)環(huán)境中還是比較實(shí)用的。下面作者將對范圍、哈希、列表和復(fù)合分區(qū)分別進(jìn)行講解。

(1)RAN(詎(范圍)分區(qū)在如下幾種情況下會使用到范圍分區(qū)：

1)頻繁地被一個時間范圍謂詞掃描。

2)維護(hù)一個時間滾動的數(shù)據(jù)窗口(RollingWindowOfData)。

3)保存歷史數(shù)據(jù)的表。

(2)HASH(哈希)分區(qū)HASH分區(qū)有如下的優(yōu)點(diǎn)：

1)提高了大表的高可用性和可管理性。

2)可以避免數(shù)據(jù)傾斜，將數(shù)據(jù)均勻地分布在多個物理設(shè)備上，最大化I/O吞吐量。

3)分區(qū)修剪和分區(qū)智能連接。

4)要求分區(qū)鍵是高基數(shù)列。

5)分區(qū)修剪不支持基于分區(qū)字段的范圍查詢，只支持等值查詢或IN-LIST查詢。

HASH分區(qū)能夠很容易對數(shù)據(jù)進(jìn)行分區(qū)，因?yàn)檎Z法很簡單，很容易實(shí)現(xiàn)。在下面這幾種情況下，使用HASH分區(qū)比RANGE分區(qū)更好：

1)事先不知道需要將多少數(shù)據(jù)映射到給定范圍的時候。

2)分區(qū)的范圍大小很難確定，或者很難平衡的時候。

3)RANGE分區(qū)使數(shù)據(jù)得到不期望的聚集時。

4)性能特性，如并行DML、分區(qū)修剪和分區(qū)連接很重要的時候。

(3)LIST(列表)分區(qū)列表分區(qū)(LISTPARTITION)提供了一種按照字段的值來進(jìn)行分區(qū)的方法，這種方法非常適合于有高重復(fù)率字段值的表。通過這種方法，可以非常方便地控制將某些特定的數(shù)值存放到一個分區(qū)。

列表分區(qū)有如下特點(diǎn)：

1)列表值是離散的。

2)列表值是無序的，例如：PARTITIONPART_01VALUES('A'，'E'，'F')等。

3)分區(qū)鍵僅能包含一個列。

4)列表值是低基數(shù)的。

(4)復(fù)合分區(qū)如果某表按照某列分區(qū)之后，仍然較大，或者是有一些其他的需求，那么還可以通過在分區(qū)內(nèi)再建子分區(qū)的方式將分區(qū)冉分區(qū)，即復(fù)合分區(qū)的方式。

復(fù)合分區(qū)在Oracle11g之前有兩種：RANGE-HASH與RANGE-LIST。需要注意的是其順序，根分區(qū)只能是RANGE分區(qū)，子分區(qū)可以是HASH分區(qū)或LIST分區(qū)，而Oracle11g在復(fù)合分區(qū)功能這塊有所增強(qiáng)，又推出了RANGE-RANGE、LIST-RANGE、LIST-LIST和LIST-HASH，這就相當(dāng)于除HASH外3種分區(qū)方式的笛卡爾形式都有了，即目前一共有6種分區(qū)，但根分區(qū)只能是RANGE分區(qū)或LIST分區(qū)。

在某些時候按照業(yè)務(wù)要求，上面的幾種分區(qū)也可以按照一定的目的創(chuàng)建復(fù)合分區(qū)，或者稱為子分區(qū)。

對于海量數(shù)據(jù)的數(shù)據(jù)庫設(shè)計，分區(qū)的設(shè)計非常重要。例如，對于一個大表，應(yīng)該采用哪種類型的分區(qū)，對于以后數(shù)據(jù)庫的性能和管理至關(guān)重要。

其實(shí)，范圍分區(qū)、HASH分區(qū)和列表分區(qū)這3種分區(qū)的特點(diǎn)都非常明顯，如下：

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

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

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

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

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

9.

Oracle之INTERVAL分區(qū)的STOREIN屬性存儲在哪張表中?正確答案：存儲在SYS.INSERT_TSN_LIST$中。可以通過10046事件對SQL語句“altertableTB_NAMEsetstorein(TS1，TS2，TS3)；”進(jìn)行跟蹤即可找到該表。所以，其查詢SQL如下：

10.

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

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

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

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

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

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

11.

普通表轉(zhuǎn)換為分區(qū)表有哪些辦法?正確答案：將普通表轉(zhuǎn)換成分區(qū)表有以下4種方法：

1)導(dǎo)出/導(dǎo)入方法(Export/ImportMethod)。

2)子查詢插入方法(InsertWithaSubqueryMethod)。

3)分區(qū)交換方法(PartitionExchangeMethod)。

4)在線重定義方法(DBMS_REDEFINITIONMethod)。

下面介紹一下這幾種方法的主要過程：

(1)導(dǎo)出/導(dǎo)入方法(Export/ImportMethod)

采用邏輯導(dǎo)出/導(dǎo)入很簡單，首先在源庫建立分區(qū)表，然后