Oracle面試題及答案有哪些

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

1.MySQL和Oracle的區別：

1. MySQL是開源的項目，免費。中小企業首選MySQL，Oracle是Oracle公司推出的一款成熟的數據庫產品是閉源同時也是收費的，但是Oracle官網是不限制下載的（只要你有賬號）一般的開發測試是沒問題。

2. SQL語法的不同，MySQL較為靈活，Oracle較為嚴格（具體就是單引號，分頁的處理，主鍵的自動增長）

3. 對于事務的支持。MySQL對于事務默認是不支持的，只有存儲引擎innodb支持。而Oracle對于事務是完全支持。

4. 并發性。MySql默認表級鎖，Oracle行級鎖，所以oracle并發行高很多。

2.Oracle中function和procedure的區別？ 

1). 函數function是存儲過程的一種 

2). 函數可以沒有參數,但是一定需要一個返回值，存儲過程可以沒有參數,不需要返回值 

3). 函數return返回值沒有返回參數模式，存儲過程通過out參數返回值, 如果需要返   回多個參數則建議使用存儲過程 

4). 在sql數據操縱語句中只能調用函數而不能調用存儲過程

3. 比較truncate和delete命令 ？ 

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

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

3). Truncate 不能對視圖等進行刪除 

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

4.oralce中 rowid, rownum的定義 

1). rowid和rownum都是偽列 

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

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

5. 事務的特性（ACID）是指什么

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

1）原子性（Atomic）：事務中的各項操作，要么全做要么全不做，任何一項操作的失敗都會導致整個事務的失敗

2）一致性（Consistent）：事務結束后系統狀態是一樣的

3）隔離性（Isolated）：并發執行的事務彼此無法看到對方的中間狀態

4）持久性（Durable）：事務完成后，即使發生災難性的故障，通過日志和同步備份可以在故障發生后重建數據

6. 列舉幾種表連接方式

等值連接（內連接）、非等連接、自連接、外連接（左、右、全）

內連接又稱等值連接，篩選完全匹配的行

左連接以左表為主，篩選完全匹配的行之外還會把左表中沒有匹配的行也篩選，找不到匹配的數據用null填充

右連接以右表為主，篩選完全匹配的行之外還會把有表中沒有匹配的行也帥選，找不到匹配的數據用null填充

7. 描述oracle中tablespace和datafile之間的關系

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

8. 什么是邏輯備份

1) 邏輯備份(exp/imp)用于實現數據庫對象的恢復。但不是基于時間點可完全恢復的備份策略。只能作為聯機備份和脫機備份的一種補充。

2) 完全邏輯備份

完全邏輯備份是將整個數據庫導出到一個數據庫的格式文件中，該文件可以在不同的數據庫版本、操作系統和硬件平臺之間進行移植。

3) 指定表的邏輯備份

通過備份工具，可以將指定的數據庫表備份出來，這可以避免完全邏輯備份所帶來的時間和財力上的浪費。

9. 什么是物理備份

物理備份是最主要的備份方式。用于保證數據庫在最小的數據庫丟失或沒有數據丟失的情況下得到恢復.物理備份分為熱備份和冷備份

  1) 熱備份：主要是指備份過程在數據庫打開并且用戶可以使用的情況下進行。需要執行熱物理備份的情況有：由于數據庫性質要求不間斷工作，因而此時只能采用熱物理備份。

2) 冷備份：冷物理備份提供了最簡單和最直接的方法保護數據庫因物理損壞丟失。

對一個已經存在大最數據量的數據庫，在晚間數據庫可以關閉，此時應用冷物理。

當對數據庫服務器進行升級，（如更換硬盤），此時需要備份數據庫信息需要采用冷備。

10. NVL與NVL2兩個函數的使用方法和差別？

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

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

11.union和union all的區別

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

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

12. minus和intersect區別

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

2）INTERSECT:取每一個查詢結果的交集。 不包括重復行。 按第1 個查詢的第 1 列升序排列。

13.什么是存儲過程，優點是什么？

存儲過程就像我們編程語言中的函數一樣，封裝了我們的代碼(PLSQL、T-SQL)。存儲過程的優點：

能夠將代碼封裝起來保存在數據庫之中讓編程語言進行調用存儲過程是一個預編譯的代碼塊，執行效率比較高一個存儲過程替代大量T_SQL語句 ，可以降低網絡通信量，提高通信速率

14.什么是三范式

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

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

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

15.什么是視圖？以及視圖的使用場景有哪些？

1. 視圖是一種基于數據表的一種虛表

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

向視圖提供數據內容的語句為 SELECT 語句,可以將視圖理解為存儲起來的 SELECT 語句

視圖向用戶提供基表數據的另一種表現形式

視圖沒有存儲真正的數據，真正的數據還是存儲在基表中

程序員雖然操作的是視圖，但最終視圖還會轉成操作基表

一個基表可以有0個或多個視圖

16.索引是什么？有什么作用以及優缺點？

（1）是一種快速查詢表中內容的機制，類似于新華字典的目錄

（2）運用在表中某個些字段上，但存儲時，獨立于表之外

優缺點：

1. 索引加快數據庫的檢索速度

2. 索引降低了插入、刪除、修改等維護任務的速度(雖然索引可以提高查詢速度，但是它們也會導致數據庫系統更新數據的性能下降，因為大部分數據更新需要同時更新索引)

3. 唯一索引可以確保每一行數據的唯一性，通過使用索引，可以在查詢的過程中使用優化隱藏器，提高系統的性能

4. 索引需要占物理和數據空間

17.數據庫的樂觀鎖和悲觀鎖是什么？

確保在多個事務同時存取數據庫中同一數據時不破壞事務的隔離性和統一性以及數據庫的統一性，樂觀鎖和悲觀鎖是并發控制主要采用的技術手段。

悲觀鎖：假定會發生并發沖突，屏蔽一切可能違反數據完整性的操作.在查詢數據的時候就把事務鎖起來，直到提交事務.實現方式：使用數據庫中的鎖機制。

 執行select xxx for update操作時，數據會被鎖定，只有執行commit或rollback才會釋放.執行select xxx for update nowait操作時，數據也會被鎖定，其他人訪問時或返回ORA-00054錯誤，內容是資源正忙，需要采取相應的業務措施進行處理。

樂觀鎖：假設不會發生并發沖突，只在提交操作時檢查是否違反數據完整性。在修改數據的時候把事務鎖起來，通過version的方式來進行鎖定.實現方式：使用version版本或者時間戳.

1）：在數據取得的時候把整個數據都copy到應用中，在進行提交的時候比對當前數據庫中的數據和開始的時候更新前取得的數據。當發現兩個數據一模一樣以后，就表示沒有沖突可以提交，否則就是并發沖突，需要去用業務邏輯進行解決。

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

18.數據庫結構優化

1)范式優化：

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

2)反范式優化：比如適當加冗余等（減少join）

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

19.解釋oralce中的游標

游標就是指向一個結果集的記錄定位器（指示器），用于定位記錄。游標的使用方式：聲明游標，打開游標，提取游標數據，關閉游標

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中的觸發器

觸發器是一種特殊的存儲過程，存儲過程由用戶顯式調用，而觸發器是當用戶對表

進行update,delete,insert是自動調用。觸發器類型通常包括（插入，刪除，修改）

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('工資未作任何變動');
 END IF;
 
 DBMS_OUTPUT.PUT_LINE('更新前工資 ：' || :OLD.SAL);
 DBMS_OUTPUT.PUT_LINE('更新后工資 ：' || :NEW.SAL);
END;

測試代碼：

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

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

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

根據表結構scott.emp如下圖:

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

分析：表自映射，為表起別名，進行關聯  t1 表模擬員工表 t2 表保存直接上級信息

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

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

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 是一個分組條件 — 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號部門薪水最高的員工。

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

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

mysql解決方案：臨時表

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.查詢員工姓名，工資和 工資級別(工資>=3000 為3級，工資>2000 為2級，工資<=2000 為1級)

分析：

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

31.語法：case &hellip; when &hellip; then &hellip; when &hellip; then &hellip; else &hellip; end

行列互換

姓名 課程 分數

張三 語文 74

張三 數學 83

張三 物理 93

李四 語文 74

李四 數學 84

李四 物理 94

變成(得到如下結果)：

姓名 語文 數學 物理

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

李四 74   84   94

張三 74   83   93

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

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

32. Oracle中的分頁

使用rownum函數

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()函數

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

33.一個叫department的表，里面只有一個字段name,一共有4條紀錄，分別是a,b,c,d,對應四個球對，現在四個球對進行比賽，用一條sql語句顯示所有可能的比賽組合.

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

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