Oracle面試題及答案有哪些

今天小編給大家分享一下Oracle面試題及答案有哪些的相關(guān)知識(shí)點(diǎn)，內(nèi)容詳細(xì)，邏輯清晰，相信大部分人都還太了解這方面的知識(shí)，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來(lái)了解一下吧。

1.MySQL和Oracle的區(qū)別：

1. MySQL是開(kāi)源的項(xiàng)目，免費(fèi)。中小企業(yè)首選MySQL，Oracle是Oracle公司推出的一款成熟的數(shù)據(jù)庫(kù)產(chǎn)品是閉源同時(shí)也是收費(fèi)的，但是Oracle官網(wǎng)是不限制下載的（只要你有賬號(hào)）一般的開(kāi)發(fā)測(cè)試是沒(méi)問(wèn)題。

2. SQL語(yǔ)法的不同，MySQL較為靈活，Oracle較為嚴(yán)格（具體就是單引號(hào)，分頁(yè)的處理，主鍵的自動(dòng)增長(zhǎng)）

3. 對(duì)于事務(wù)的支持。MySQL對(duì)于事務(wù)默認(rèn)是不支持的，只有存儲(chǔ)引擎innodb支持。而Oracle對(duì)于事務(wù)是完全支持。

4. 并發(fā)性。MySql默認(rèn)表級(jí)鎖，Oracle行級(jí)鎖，所以oracle并發(fā)行高很多。

2.Oracle中function和procedure的區(qū)別？ 

1). 函數(shù)function是存儲(chǔ)過(guò)程的一種 

2). 函數(shù)可以沒(méi)有參數(shù),但是一定需要一個(gè)返回值，存儲(chǔ)過(guò)程可以沒(méi)有參數(shù),不需要返回值 

3). 函數(shù)return返回值沒(méi)有返回參數(shù)模式，存儲(chǔ)過(guò)程通過(guò)out參數(shù)返回值, 如果需要返   回多個(gè)參數(shù)則建議使用存儲(chǔ)過(guò)程 

4). 在sql數(shù)據(jù)操縱語(yǔ)句中只能調(diào)用函數(shù)而不能調(diào)用存儲(chǔ)過(guò)程

3. 比較truncate和delete命令 ？ 

1). Truncate 和delete都可以將數(shù)據(jù)實(shí)體刪掉，truncate 的操作并不記錄到 rollback日志，所以操作速度較快，但同時(shí)這個(gè)數(shù)據(jù)不能恢復(fù) 

2). Delete操作不騰出表空間的空間 

3). Truncate 不能對(duì)視圖等進(jìn)行刪除 

4). Truncate是數(shù)據(jù)定義語(yǔ)言（DDL），而delete是數(shù)據(jù)操縱語(yǔ)言(DML) 

4.oralce中 rowid, rownum的定義 

1). rowid和rownum都是偽列 

2). rowid是物理地址，用于定位oracle中具體數(shù)據(jù)的物理存儲(chǔ)位置 

3). rownum則是sql的輸出結(jié)果排序

5. 事務(wù)的特性（ACID）是指什么

事務(wù)是作為一個(gè)邏輯單元執(zhí)行的一系列操作，一個(gè)邏輯工作單元必須有四個(gè)屬性，稱為 ACID(原子性、一致性、隔離性和持久性)屬性。

1）原子性（Atomic）：事務(wù)中的各項(xiàng)操作，要么全做要么全不做，任何一項(xiàng)操作的失敗都會(huì)導(dǎo)致整個(gè)事務(wù)的失敗

2）一致性（Consistent）：事務(wù)結(jié)束后系統(tǒng)狀態(tài)是一樣的

3）隔離性（Isolated）：并發(fā)執(zhí)行的事務(wù)彼此無(wú)法看到對(duì)方的中間狀態(tài)

4）持久性（Durable）：事務(wù)完成后，即使發(fā)生災(zāi)難性的故障，通過(guò)日志和同步備份可以在故障發(fā)生后重建數(shù)據(jù)

6. 列舉幾種表連接方式

等值連接（內(nèi)連接）、非等連接、自連接、外連接（左、右、全）

內(nèi)連接又稱等值連接，篩選完全匹配的行

左連接以左表為主，篩選完全匹配的行之外還會(huì)把左表中沒(méi)有匹配的行也篩選，找不到匹配的數(shù)據(jù)用null填充

右連接以右表為主，篩選完全匹配的行之外還會(huì)把有表中沒(méi)有匹配的行也帥選，找不到匹配的數(shù)據(jù)用null填充

7. 描述oracle中tablespace和datafile之間的關(guān)系

一個(gè)表空間可包含一個(gè)或多個(gè)數(shù)據(jù)文件。表空間利用增加或擴(kuò)展數(shù)據(jù)文件擴(kuò)大表空間，表空間的大小為組成該表空間的數(shù)據(jù)文件大小的和。一個(gè)datafile只能屬于一個(gè)表空間;一個(gè)tablespace可以有一個(gè)或多個(gè)datafile,每個(gè)datafile只能在一個(gè)tablespace內(nèi), table中的數(shù)據(jù),通過(guò)hash算法分布在tablespace中的各個(gè)datafile中,tablespace是邏輯上的概念,datafile則在物理上儲(chǔ)存了數(shù)據(jù)庫(kù)的種種對(duì)象。

8. 什么是邏輯備份

1) 邏輯備份(exp/imp)用于實(shí)現(xiàn)數(shù)據(jù)庫(kù)對(duì)象的恢復(fù)。但不是基于時(shí)間點(diǎn)可完全恢復(fù)的備份策略。只能作為聯(lián)機(jī)備份和脫機(jī)備份的一種補(bǔ)充。

2) 完全邏輯備份

完全邏輯備份是將整個(gè)數(shù)據(jù)庫(kù)導(dǎo)出到一個(gè)數(shù)據(jù)庫(kù)的格式文件中，該文件可以在不同的數(shù)據(jù)庫(kù)版本、操作系統(tǒng)和硬件平臺(tái)之間進(jìn)行移植。

3) 指定表的邏輯備份

通過(guò)備份工具，可以將指定的數(shù)據(jù)庫(kù)表備份出來(lái)，這可以避免完全邏輯備份所帶來(lái)的時(shí)間和財(cái)力上的浪費(fèi)。

9. 什么是物理備份

物理備份是最主要的備份方式。用于保證數(shù)據(jù)庫(kù)在最小的數(shù)據(jù)庫(kù)丟失或沒(méi)有數(shù)據(jù)丟失的情況下得到恢復(fù).物理備份分為熱備份和冷備份

  1) 熱備份：主要是指?jìng)浞葸^(guò)程在數(shù)據(jù)庫(kù)打開(kāi)并且用戶可以使用的情況下進(jìn)行。需要執(zhí)行熱物理備份的情況有：由于數(shù)據(jù)庫(kù)性質(zhì)要求不間斷工作，因而此時(shí)只能采用熱物理備份。

2) 冷備份：冷物理備份提供了最簡(jiǎn)單和最直接的方法保護(hù)數(shù)據(jù)庫(kù)因物理?yè)p壞丟失。

對(duì)一個(gè)已經(jīng)存在大最數(shù)據(jù)量的數(shù)據(jù)庫(kù)，在晚間數(shù)據(jù)庫(kù)可以關(guān)閉，此時(shí)應(yīng)用冷物理。

當(dāng)對(duì)數(shù)據(jù)庫(kù)服務(wù)器進(jìn)行升級(jí)，（如更換硬盤(pán)），此時(shí)需要備份數(shù)據(jù)庫(kù)信息需要采用冷備。

10. NVL與NVL2兩個(gè)函數(shù)的使用方法和差別？

1）NVL (expr1, expr2)：expr1為NULL，返回expr2；不為NULL，返回expr1。注意兩者的類型要一致

2） NVL2 (expr1, expr2, expr3) ：expr1不為NULL。返回expr2；為NULL，返回expr3。expr2和expr3類型不同的話。expr3會(huì)轉(zhuǎn)換為expr2的類型 

11.union和union all的區(qū)別

1) UNION：由每一個(gè)查詢 選擇的 全部不反復(fù)的行組成。并集不包括反復(fù)值， 默認(rèn)按第 1 個(gè)查詢的第 1 列升序排列。

2) UNION ALL： 由每一個(gè)查詢 選擇的 全部的行。全然并集包括反復(fù)值。 不排序。

12. minus和intersect區(qū)別

1）MINUS: 在第一個(gè)查詢中但不在后面查詢中的行，不包括重復(fù)行。 按第1 個(gè)查詢的第 1 列升序排列。

2）INTERSECT:取每一個(gè)查詢結(jié)果的交集。 不包括重復(fù)行。 按第1 個(gè)查詢的第 1 列升序排列。

13.什么是存儲(chǔ)過(guò)程，優(yōu)點(diǎn)是什么？

存儲(chǔ)過(guò)程就像我們編程語(yǔ)言中的函數(shù)一樣，封裝了我們的代碼(PLSQL、T-SQL)。存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：

能夠?qū)⒋a封裝起來(lái)保存在數(shù)據(jù)庫(kù)之中讓編程語(yǔ)言進(jìn)行調(diào)用存儲(chǔ)過(guò)程是一個(gè)預(yù)編譯的代碼塊，執(zhí)行效率比較高一個(gè)存儲(chǔ)過(guò)程替代大量T_SQL語(yǔ)句 ，可以降低網(wǎng)絡(luò)通信量，提高通信速率

14.什么是三范式

第一范式：字段是最小的的單元不可再分

第二范式：滿足第一范式,表中的字段必須完全依賴于全部主鍵而非部分主鍵。

第三范式：滿足第二范式，非主鍵外的所有字段必須互不依賴既不存在傳遞依賴

15.什么是視圖？以及視圖的使用場(chǎng)景有哪些？

1. 視圖是一種基于數(shù)據(jù)表的一種虛表

2. 視圖建立在已有表的基礎(chǔ)上, 視圖賴以建立的這些表稱為基表

向視圖提供數(shù)據(jù)內(nèi)容的語(yǔ)句為 SELECT 語(yǔ)句,可以將視圖理解為存儲(chǔ)起來(lái)的 SELECT 語(yǔ)句

視圖向用戶提供基表數(shù)據(jù)的另一種表現(xiàn)形式

視圖沒(méi)有存儲(chǔ)真正的數(shù)據(jù)，真正的數(shù)據(jù)還是存儲(chǔ)在基表中

程序員雖然操作的是視圖，但最終視圖還會(huì)轉(zhuǎn)成操作基表

一個(gè)基表可以有0個(gè)或多個(gè)視圖

16.索引是什么？有什么作用以及優(yōu)缺點(diǎn)？

（1）是一種快速查詢表中內(nèi)容的機(jī)制，類似于新華字典的目錄

（2）運(yùn)用在表中某個(gè)些字段上，但存儲(chǔ)時(shí)，獨(dú)立于表之外

優(yōu)缺點(diǎn)：

1. 索引加快數(shù)據(jù)庫(kù)的檢索速度

2. 索引降低了插入、刪除、修改等維護(hù)任務(wù)的速度(雖然索引可以提高查詢速度，但是它們也會(huì)導(dǎo)致數(shù)據(jù)庫(kù)系統(tǒng)更新數(shù)據(jù)的性能下降，因?yàn)榇蟛糠謹(jǐn)?shù)據(jù)更新需要同時(shí)更新索引)

3. 唯一索引可以確保每一行數(shù)據(jù)的唯一性，通過(guò)使用索引，可以在查詢的過(guò)程中使用優(yōu)化隱藏器，提高系統(tǒng)的性能

4. 索引需要占物理和數(shù)據(jù)空間

17.數(shù)據(jù)庫(kù)的樂(lè)觀鎖和悲觀鎖是什么？

確保在多個(gè)事務(wù)同時(shí)存取數(shù)據(jù)庫(kù)中同一數(shù)據(jù)時(shí)不破壞事務(wù)的隔離性和統(tǒng)一性以及數(shù)據(jù)庫(kù)的統(tǒng)一性，樂(lè)觀鎖和悲觀鎖是并發(fā)控制主要采用的技術(shù)手段。

悲觀鎖：假定會(huì)發(fā)生并發(fā)沖突，屏蔽一切可能違反數(shù)據(jù)完整性的操作.在查詢數(shù)據(jù)的時(shí)候就把事務(wù)鎖起來(lái)，直到提交事務(wù).實(shí)現(xiàn)方式：使用數(shù)據(jù)庫(kù)中的鎖機(jī)制。

 執(zhí)行select xxx for update操作時(shí)，數(shù)據(jù)會(huì)被鎖定，只有執(zhí)行commit或rollback才會(huì)釋放.執(zhí)行select xxx for update nowait操作時(shí)，數(shù)據(jù)也會(huì)被鎖定，其他人訪問(wèn)時(shí)或返回ORA-00054錯(cuò)誤，內(nèi)容是資源正忙，需要采取相應(yīng)的業(yè)務(wù)措施進(jìn)行處理。

樂(lè)觀鎖：假設(shè)不會(huì)發(fā)生并發(fā)沖突，只在提交操作時(shí)檢查是否違反數(shù)據(jù)完整性。在修改數(shù)據(jù)的時(shí)候把事務(wù)鎖起來(lái)，通過(guò)version的方式來(lái)進(jìn)行鎖定.實(shí)現(xiàn)方式：使用version版本或者時(shí)間戳.

1）：在數(shù)據(jù)取得的時(shí)候把整個(gè)數(shù)據(jù)都copy到應(yīng)用中，在進(jìn)行提交的時(shí)候比對(duì)當(dāng)前數(shù)據(jù)庫(kù)中的數(shù)據(jù)和開(kāi)始的時(shí)候更新前取得的數(shù)據(jù)。當(dāng)發(fā)現(xiàn)兩個(gè)數(shù)據(jù)一模一樣以后，就表示沒(méi)有沖突可以提交，否則就是并發(fā)沖突，需要去用業(yè)務(wù)邏輯進(jìn)行解決。

2)：新增一個(gè)Table的Column，這個(gè)column是采用timestamp型，存儲(chǔ)數(shù)據(jù)最后更新的時(shí)間。這種Timestamp的數(shù)據(jù)精度在Oracle的時(shí)間類型中是最高的，精確到微秒，是在更新提交的時(shí)候檢查當(dāng)前數(shù)據(jù)庫(kù)中數(shù)據(jù)的時(shí)間戳和自己更新前取到的時(shí)間戳進(jìn)行對(duì)比，如果一致則OK，否則就是版本沖突。

18.數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化

1)范式優(yōu)化：

1)比如消除冗余（節(jié)省空間）

2)反范式優(yōu)化：比如適當(dāng)加冗余等（減少join）

3)拆分表： 垂直拆分和水平拆分

19.解釋oralce中的游標(biāo)

游標(biāo)就是指向一個(gè)結(jié)果集的記錄定位器（指示器），用于定位記錄。游標(biāo)的使用方式：聲明游標(biāo)，打開(kāi)游標(biāo)，提取游標(biāo)數(shù)據(jù)，關(guān)閉游標(biāo)

Declare
 cursor cur is select * from category;
    record category%rowtype;
  begin
    open cur;
    loop
      fetch cur into record;
      if cur%notfound then
        exit;
      end if;
      dbms_output.put_line(to_char(record.id) || ',' || record.name);
    end loop;
    close cur;
 end;

20.描述oracle中的觸發(fā)器

觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程由用戶顯式調(diào)用，而觸發(fā)器是當(dāng)用戶對(duì)表

進(jìn)行update,delete,insert是自動(dòng)調(diào)用。觸發(fā)器類型通常包括（插入，刪除，修改）

CREATE OR REPLACE TRIGGER SAL_EMP
 BEFORE UPDATE ON scott.EMP
 FOR EACH ROW
BEGIN
 IF :OLD.SAL > :NEW.SAL THEN
  DBMS_OUTPUT.PUT_LINE('工資減少');
 ELSIF :OLD.SAL < :NEW.SAL THEN
  DBMS_OUTPUT.PUT_LINE('工資增加');
 ELSE
 
  DBMS_OUTPUT.PUT_LINE('工資未作任何變動(dòng)');
 END IF;
 
 DBMS_OUTPUT.PUT_LINE('更新前工資 ：' || :OLD.SAL);
 DBMS_OUTPUT.PUT_LINE('更新后工資 ：' || :NEW.SAL);
END;

測(cè)試代碼：

UPDATE scott.emp SET sal = 3000 WHERE empno = '7788';

21.  使用oracle 偽列刪除表中重復(fù)記錄： 

delete  table  t  where t.rowid not in
(select  max(t1.rowid)  from  table1 t1 where  t1.name=t.name)

根據(jù)表結(jié)構(gòu)scott.emp如下圖:

22.列出所有員工的姓名及其直接上級(jí)的姓名。

分析：表自映射，為表起別名，進(jìn)行關(guān)聯(lián)  t1 表模擬員工表 t2 表保存直接上級(jí)信息

select t1.ename 員工姓名, t2.ename 直接上級(jí) from emp t1,emp t2 where t1.MGR = t2.empno;

23.列出受雇日期早于其直接上級(jí)的所有員工。

select t1.*,t2.hiredate from emp t1,emp t2 where t1.MGR = t2.empno and t1.hiredate < t2.hiredate;

24.列出最低薪金大于1500的各種工作。

分析：工作的最低薪金 —- 按工作分組，求最低薪金

select min(sal) from emp group by job;

大于1500 是一個(gè)分組條件 — having 

select job,min(sal) from emp group by job having min(sal) > 1500;

25.列出所有員工的年工資,按年薪從低到高排序。 

select ename, sal*12 from emp order by sal*12 asc;

26.列出與“SCOTT”從事相同工作的所有員工。

分析：先用子查詢查出SCOTT的工作 :

select job from emp where ename ='SCOTT';
   select * from emp where ename <> ‘SCOTT' and job = (select job from emp where     ename ='SCOTT');

27.刪除10號(hào)部門(mén)薪水最高的員工。

delete from emp where deptno=10 and sal >= all(select sal from emp where deptno=10 ); // MYSQL 不支持

Mysql 規(guī)范，修改或者刪除 表中記錄，不允許在子查詢中 查詢相同表

mysql解決方案：臨時(shí)表

delete from emp where deptno=10 and sal >= all(select t.sal from (select sal from emp where deptno=10) t );

28.將薪水最高的員工的薪水降30%。

oracle：update emp set sal = sal*0.7 where sal = (select max(sal) from emp);
mysql:  update emp set sal = sal*0.7 where sal = (select t.maxsal  from (select max(sal) maxsal from emp) t);

29.示出薪水最高人的職位。

select job from emp where sal = (select max(sal) from emp);
select job from emp where sal >= all(select sal from emp);

30.查詢員工姓名，工資和 工資級(jí)別(工資>=3000 為3級(jí)，工資>2000 為2級(jí)，工資<=2000 為1級(jí))

分析：

select ename,sal, case when sal>=3000 then ‘3級(jí)' when sal>2000 then ‘2級(jí)' else ‘1級(jí)' end 級(jí)別 from emp;

31.語(yǔ)法：case &hellip; when &hellip; then &hellip; when &hellip; then &hellip; else &hellip; end

行列互換

姓名 課程 分?jǐn)?shù)

張三 語(yǔ)文 74

張三 數(shù)學(xué) 83

張三 物理 93

李四 語(yǔ)文 74

李四 數(shù)學(xué) 84

李四 物理 94

變成(得到如下結(jié)果)：

姓名 語(yǔ)文 數(shù)學(xué) 物理

&mdash;- &mdash;- &mdash;- &mdash;-

李四 74   84   94

張三 74   83   93

&mdash;&mdash;&mdash;&mdash;&mdash;&mdash;-

select name,max(case when cource ='語(yǔ)文' then score else 0 end) from scores group by name;
 
select name,max(case when cource ='語(yǔ)文' then score else 0 end)  語(yǔ)文,max(case when cource ='數(shù)學(xué)' then score else 0 end) 數(shù)學(xué),
max(case when cource ='英語(yǔ)' then score else 0 end) 英語(yǔ)  from scores group by name;

32. Oracle中的分頁(yè)

使用rownum函數(shù)

SELECT *
FROM (SELECT ROWNUM AS rowno,r.*
           FROM(SELECT * FROM tmp t  ORDER BY id desc
                   ) r
           where ROWNUM <= page*size
          ) a
WHERE a.rowno > (page-1)*size

使用row_number()over()函數(shù)

  Select * from (
  Select * ,row_number() over(order by id desc) num
  ) a where num between (page-1)*size and page*size

33.一個(gè)叫department的表，里面只有一個(gè)字段name,一共有4條紀(jì)錄，分別是a,b,c,d,對(duì)應(yīng)四個(gè)球?qū)?，現(xiàn)在四個(gè)球?qū)M(jìn)行比賽，用一條sql語(yǔ)句顯示所有可能的比賽組合.

select a.name, b.name
from team a, team b
where a.name < b.name

以上就是“Oracle面試題及答案有哪些”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會(huì)為大家更新不同的知識(shí)，如果還想學(xué)習(xí)更多的知識(shí)，請(qǐng)關(guān)注億速云行業(yè)資訊頻道。