Oracle面試題

1、Oracle(66道)一、理論題(15道)1什么是視圖？視圖的作用是什么？如何創(chuàng)建視圖？（1）定義：視圖是一個(gè)虛擬表，其內(nèi)容由查詢定義。同真實(shí)的表一樣，視圖包含一系列帶有名稱的列和行數(shù)據(jù)。但是，視圖并不在數(shù)據(jù)庫中以存儲(chǔ)的數(shù)據(jù)值集形式存在。（2）作用：簡化查詢 保護(hù)我們的一些私有數(shù)據(jù)，通過視圖也可以用來更新數(shù)據(jù)，但是我們一般不這么用 缺點(diǎn)：要對(duì)視圖進(jìn)行維護(hù)。（3）創(chuàng)建：創(chuàng)建視圖需要CREAE VIEW系統(tǒng)權(quán)限，視圖的創(chuàng)建語法如下： CREATE OR REPLACE FORCE|NOFORCE VIEW 視圖名(別名1，別名2.) AS 子查詢 WITH CHECK OPTION CONSTRA

2、INT 約束名 WITH READ ONLY2什么是索引？為什么使用索引？如何使用索引？（1）索引用來快速地尋找那些具有特定值的記錄，所有MySQL索引都以B-樹的形式保存。如果沒有索引，執(zhí)行查詢時(shí)必須從第一個(gè)記錄開始掃描整個(gè)表的所有記錄，直至找到符合要求的記錄。表里面的記錄數(shù)量越多，這個(gè)操作的代價(jià)就越高。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，無需掃描任何記錄即可迅速得到目標(biāo)記錄所在的位置。如果表有1000個(gè)記錄，通過索引查找記錄至少要比順序掃描記錄快100倍。 （2）索引主要是為了加快查詢的速度。（3）使用：CREATE UNIUQE | BITMAP INDEX <schema>

3、;.<index_name>      ON <schema>.<table_name>           (<column_name> | <expression> ASC | DESC,            <column_name> | <expres

4、sion> ASC | DESC,.)     TABLESPACE <tablespace_name>     STORAGE <storage_settings>     LOGGING | NOLOGGING    COMPUTE STATISTICS     NOCOMPRESS | COMPRESS<nn>    

5、NOSORT | REVERSE     PARTITION | GLOBAL PARTITION<partition_setting>相關(guān)說明1）UNIQUE | BITMAP：指定UNIQUE為唯一值索引，BITMAP為位圖索引，省略為B-Tree索引。2）<column_name> | <expression> ASC | DESC：可以對(duì)多列進(jìn)行聯(lián)合索引，當(dāng)為expression時(shí)即“基于函數(shù)的索引”3）TABLESPACE：指定存放索引的表空間(索引和原表不在一個(gè)表空間時(shí)效率更高)4）STORAGE：可進(jìn)一步

6、設(shè)置表空間的存儲(chǔ)參數(shù)5）LOGGING | NOLOGGING：是否對(duì)索引產(chǎn)生重做日志(對(duì)大表盡量使用NOLOGGING來減少占用空間并提高效率)6）COMPUTE STATISTICS：創(chuàng)建新索引時(shí)收集統(tǒng)計(jì)信息7）NOCOMPRESS | COMPRESS<nn>：是否使用“鍵壓縮”(使用鍵壓縮可以刪除一個(gè)鍵列中出現(xiàn)的重復(fù)值)8）NOSORT | REVERSE：NOSORT表示與表中相同的順序創(chuàng)建索引，REVERSE表示相反順序存儲(chǔ)索引值9）PARTITION | NOPARTITION：可以在分區(qū)表和未分區(qū)表上對(duì)創(chuàng)建的索引進(jìn)行分區(qū)3.SQL語句索引優(yōu)化規(guī)則 不帶where條件

7、的SQL語句一定不能使用索引 在where條件中有索引的字段不能使用函數(shù) 例子：假設(shè)我們查詢?cè)?987年參加工作的所有員工SQL >select * from emp where to_char(hiredate,'yyyy')='1987'在where條件中有索引的字段不能參與運(yùn)算 例子：查詢?cè)?0000天以前參加工作的員工信息 SQL >select * from emp where sysdate-hiredate>10000; SQL >select * from emp where hiredate<sysdate-100

8、00; 盡量少用 not in , not exists , like '%' 等關(guān)鍵字 書寫查詢語句的時(shí)候，應(yīng)該首先考慮關(guān)聯(lián)查詢、其次考慮集合查詢，最后考慮子查詢 where條件中限制性強(qiáng)的條件應(yīng)該寫在where最后的一個(gè)條件 where條件從右向左經(jīng)行條件解析 多表查詢中，驅(qū)動(dòng)表應(yīng)該是返回?cái)?shù)據(jù)少的表 例子：查詢部門account 的所有員工信息 SQL > select e.*,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname='ACCOUNTING'4DDL語句包括哪些？Cre

9、ate table ,alter table,drop table等5DML語句包括哪些？Select,update,delete,insert 6Select的格式？Select 列名列表 from 表名1,表名2,表名n where 條件 group by列名1,列名2,列名n having 分組條件 order by 列名 asc|desc7. Oracle數(shù)據(jù)庫啟動(dòng)時(shí)必需開啟的后臺(tái)服務(wù) OracleOrahome90TNSListener 使第三方的軟件或語言訪問 OracleServiceETC Oracle的實(shí)例 CRUD 增刪改查 注意：中的ETC是你起的實(shí)例的名字8什么是事務(wù)？

10、.transacation 事務(wù)特點(diǎn)？如何使用事務(wù)？（1）事務(wù)(Transaction)是訪問并可能更新數(shù)據(jù)庫中各種數(shù)據(jù)項(xiàng)的一個(gè)程序執(zhí)行單元(unit)。事務(wù)通常由高級(jí)數(shù)據(jù)庫操縱語言或編程語言（如SQL，C+或Java）書寫的用戶程序的執(zhí)行所引起，并用形如begin transaction和end transaction語句（或函數(shù)調(diào)用）來界定。事務(wù)由事務(wù)開始(begin transaction)和事務(wù)結(jié)束(end transaction)之間執(zhí)行的全體操作組成。例如：在關(guān)系數(shù)據(jù)庫中，一個(gè)事務(wù)可以是一條SQL語句，一組SQL語句或整個(gè)程序。事務(wù)是恢復(fù)和并發(fā)控制的基本單位。（2）事務(wù)的四個(gè)特性原

11、子性 不可分割持久性 -> 當(dāng)數(shù)據(jù)寫入到數(shù)據(jù)庫物理文件中后，該數(shù)據(jù)被 持久化隔離性 -> 事務(wù)之間相互獨(dú)立互不干擾一致性 -> 數(shù)據(jù)安全(3)使用：整體提交(commit) 整體回滾(rollback)9. Oracle備份數(shù)據(jù)表數(shù)據(jù) create table 備份表名稱 as 查詢語句; Oracle備份表結(jié)構(gòu) create table emp_bak as select * from emp where 1=2;10. Oracle備份數(shù)據(jù)庫(1)冷備份 冷備份是Oracle最簡單的一種備份；執(zhí)行冷備份前必須關(guān)閉數(shù)據(jù)庫；然后使用操作系統(tǒng)實(shí)用工具或者第三方工具備份所有相關(guān)的

12、數(shù)據(jù)庫文件。 優(yōu)點(diǎn):能簡單快速地備份。能簡單快速地恢復(fù)。執(zhí)行簡單。 缺點(diǎn):必須關(guān)閉數(shù)據(jù)庫，不能進(jìn)行點(diǎn)恢復(fù)。 (2)熱備份 熱備份是當(dāng)數(shù)據(jù)庫正在運(yùn)行時(shí)進(jìn)行數(shù)據(jù)備份的過程。執(zhí)行熱備份的前提是：數(shù)據(jù)庫運(yùn)行在可歸檔日志模式。適用于24X7不間斷運(yùn)行的關(guān)鍵應(yīng)用系統(tǒng)。 優(yōu)點(diǎn):備份時(shí)數(shù)據(jù)庫可以是打開的。熱備份可以用來進(jìn)行點(diǎn)恢復(fù)。初始化參數(shù)文件、歸檔日志在數(shù)據(jù)庫正常運(yùn)行時(shí)是關(guān)閉的，可用操作系統(tǒng)命令拷貝。 缺點(diǎn):執(zhí)行過程復(fù)雜。由于數(shù)據(jù)庫不間斷運(yùn)行，測試比較困難。不能用操作系統(tǒng)實(shí)用工具拷貝打開的文件。必須使用Oracle提供的ocopy工具來拷貝打開的文件。熱備份可能造成CPU、I/O過載，應(yīng)在數(shù)據(jù)庫不太忙時(shí)進(jìn)行

13、。 (3)Export導(dǎo)出數(shù)據(jù)庫對(duì)象 冷備份和熱備份都備份物理數(shù)據(jù)庫文件，因而被稱為物理備份。而export備份的是數(shù)據(jù)庫對(duì)象，因此被稱為邏輯備份。 優(yōu)點(diǎn):能執(zhí)行對(duì)象或者行恢復(fù)。備份和恢復(fù)速度更快。能夠跨操作系統(tǒng)平臺(tái)遷移數(shù)據(jù)庫。數(shù)據(jù)庫可一直運(yùn)行。 缺點(diǎn):export并不是冷備份和熱備份的替代工具。冷、熱備份可保護(hù)介質(zhì)失效。export備份可保護(hù)用戶或應(yīng)用錯(cuò)誤。將數(shù)據(jù)庫TEST完全導(dǎo)出,用戶名system 密碼manager 導(dǎo)出到D:daochu.dmp中 exp system/managerTEST file=d:daochu.dmp full=y11.to_char和to_date函數(shù)應(yīng)用

14、 (1)to_char 語法：to_char(時(shí)間數(shù)據(jù),制定格式) SQL:> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss d ddd day') from dual; yyyy-mm-dd 年月日 hh24:mi:ss 時(shí)分秒(24) d 一周第幾天 ddd 一年的第幾天 day 星期幾 (2)to_date: 將文本類型數(shù)據(jù)按照制定格式轉(zhuǎn)換成時(shí)間類型數(shù)據(jù) 語法：to_date('具體的時(shí)間字符數(shù)據(jù)','時(shí)間字符串格式') to_date('2010-01-19','

15、yyyy-mm-dd') 注意：Oracle中的date字段類型及其特殊12. 存儲(chǔ)過程定義及優(yōu)勢(shì)？(1)定義：存儲(chǔ)過程（Stored Procedure ）是一組為了完成特定功能的SQL 語句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫中。用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行它。存儲(chǔ)過程是數(shù)據(jù)庫中的一個(gè)重要對(duì)象，任何一個(gè)設(shè)計(jì)良好的數(shù)據(jù)庫應(yīng)用程序都應(yīng)該用到存儲(chǔ)過程。 存儲(chǔ)過程是由流控制和SQL 語句書寫的過程，這個(gè)過程經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫服務(wù)器中，應(yīng)用程序使用時(shí)只要調(diào)用即可。在ORACLE 中，若干個(gè)有聯(lián)系的過程可以組合在一起構(gòu)成程序包。(2)優(yōu) 點(diǎn)：1). 存儲(chǔ)過程

16、只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲(chǔ)過程可提高數(shù)據(jù)庫執(zhí)行速度。 2).當(dāng)對(duì)數(shù)據(jù)庫進(jìn)行復(fù)雜操作時(shí)(如對(duì)多個(gè)表進(jìn)行Update、Insert、Query、Delete時(shí)），可將此復(fù)雜操作用存儲(chǔ)過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。 3).存儲(chǔ)過程可以重復(fù)使用,可減少數(shù)據(jù)庫開發(fā)人員的工作量。 4).安全性高,可設(shè)定只有某用戶才具有對(duì)指定存儲(chǔ)過程的使用權(quán)。13.如何調(diào)用存儲(chǔ)過程 兩種方式：1、指令方式 -> exec 存儲(chǔ)過程名稱; 2、使用PL/SQL塊調(diào)用 begin 存儲(chǔ)過程名稱; end;14帶參數(shù)的存儲(chǔ)過

17、程語法？ 兩大類：a) 帶輸入?yún)?shù) b) 帶輸出參數(shù) a) 帶輸入?yún)?shù)的存儲(chǔ)過程 語法：create or replace procedure 存儲(chǔ)過程名稱(參數(shù)名稱1 in 類型, . ,參數(shù)名稱n 類型) as PL/SQL塊b) 帶輸出參數(shù)的存儲(chǔ)過程 語法：create or replace procedure 存儲(chǔ)過程名稱(參數(shù)名稱1 out 類型, . ,參數(shù)名稱n out 類型) as PL/SQL塊 15. 觸發(fā)器的語法及案例？當(dāng)需要級(jí)聯(lián)操作的時(shí)候可能用到觸發(fā)器語法：create or replace trigger 觸發(fā)器名稱 before|after insert or up

18、date or delete of 字段名稱 on 表 begin PL/SQL語語句案例：當(dāng)對(duì)Emp進(jìn)行CUD操作時(shí)候做相應(yīng)的輸出信息。create or replace trigger tri_empafter insert or update or delete on empbegin if inserting then dbms_output.put_line('add is ok'); elsif updating then dbms_output.put_line('update is ok'); else dbms_output.put_line(

19、'delete is ok'); end if;end;二、基本SQL語句（39道）1.向DEPT表插入新的部門（50，MANAGEMENT，BEIJING）并顯示；INSERT INTO dept VALUES('50','management','beijing');SELECT * FROM dept WHERE deptno=50;2.將部門號(hào)為50的地址改為SHANGHAI，再執(zhí)行一次回滾，而后提交；UPDATE dept SET loc='shanghai' WHERE deptno=50; ROLLB

20、ACK; COMMIT;SELECT * FROM dept WHERE deptno=50; 3.統(tǒng)計(jì)各部門工資的平均值；SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;4.查詢SMITH上級(jí)領(lǐng)導(dǎo)的姓名；SELECT mgr FROM emp WHERE ename='smith'5.查詢工資高于JONES的所有員工的姓名和工資；SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='jones');6.按員工的工資劃分等級(jí)

21、，工資高于2500的為A等，在1500和2500間（包括1500及2500）為B等， 其余為C等（提示：增加新列DJ）；alter table emp add DJ varchar(1);Update emp set DJ=case when sal>2500 then 'A' when sal<1500 then 'C' else 'B' end ;7.查詢和ALLEN不在同一部門的員工姓名和所在部門名稱；SELECT emp.ename,dept.dname FROM emp,dept WHERE emp.deptno not i

22、n(SELECT emp.deptno FROM emp WHERE ename='allen') AND emp.deptno=dept.deptno;8.用UNION查詢工資高于1500并且在30號(hào)部門工作的員工號(hào)，員工名，工資；SELECT empno,ename,sal FROM emp WHERE sal>1500 and deptno=30;UNIONSELECT empno,ename,sal FROM emp WHERE sal>1500 and deptno=30;9.按照員工的部門號(hào)升序排列，同部門的再按員工工資降序排列；SELECT deptn

23、o,sal FROM emp ORDER BY deptno,sal desc;10.查工資不超過2000的員工所有信息。SELECT * FROM emp WHERE sal<=2000;11.使用group by進(jìn)行單列分組 ：顯示每個(gè)部門的平均工資和最高工資SELECT deptno,AVG(sal),MAX(sal) from emp GROUP BY deptno;12.使用group by進(jìn)行多列分組：顯示每個(gè)部門每種崗位的平均工資和最高工資SELECT deptno,job,AVG(sal),MAX(sal) FROM emp GROUP BY deptno,job;13.

24、使用having子句限制分組顯示結(jié)果 ：顯示平均工資低于2000的部門號(hào)、平均工資及最高工資SELECT deptno,AVG(sal),MAX(sal) FROM emp GROUP BY deptno HAVING AVG(sal)<2000;14.相等連接(emp和dept表，emp.empno,ename,dname字段)select emp.empno,ename,dname from emp,dept where emp.deptno=dept.deptno;15.內(nèi)連接(emp和dept表，emp.empno,ename,dname字段)select empno,ename

25、,dname from emp inner join dept on emp.deptno=dept.deptno;16.左外連接(emp和dept表，emp.empno,ename,dname字段)select empno,dept.deptno from emp left join dept on emp.deptno=dept.deptno;17.右外連接(emp和dept表，emp.empno,ename,dname字段)select empno,dept.deptno from emp right join dept on emp.deptno=dept.deptno;18. 求-1

26、2.01的絕對(duì)值。select abs(-12.01) from dual;19.求小于15.6的最大整數(shù)。select floor(15.6) from dual;20.11除以4的余數(shù)。select mod(11,4) from dual;21.求-2的3次方和2的-1次方；select power(-2,3),power(2,-1) from dual;22.12.666四舍五入到小數(shù)點(diǎn)后一位。select round(12.666,1) from dual;23. 查詢a和A的ASCII碼值。select ascii('a') "a",ascii(&

27、#39;A') "A" from dual;24.查ASCII碼值為56的字符。select chr(56) from dual;25連接Good和Morning兩個(gè)字符串。select concat('Good','Morning') from dual;26找h在hello中的位置。select instr('hello','h') from dual;27示正好為5個(gè)字符的員工的姓名. select ename from emp where length(ename)=5;28.缺省值為20

28、9;字符串中的'缺省'替換為'默認(rèn)'。select replace('缺省值為20','缺省','默認(rèn)') from dual;29.hello中取第一個(gè)字符到第三個(gè)字符的字符串。select substr('hello',1,3) from dual;30.求14個(gè)月前的時(shí)間。select add_months(sysdate,-14) from dual;31.查詢現(xiàn)在的日期時(shí)間。select current_date from dual;32.查詢當(dāng)前會(huì)話的日期時(shí)間。select curren

29、t_timestamp from dual;33.查詢1998年8月31號(hào)到現(xiàn)在有多少個(gè)月。select months_between(sysdate,'31-8月-1998') from dual;34.查詢現(xiàn)在時(shí)間。select sysdate from dual;35.查詢當(dāng)前系統(tǒng)日期時(shí)間及時(shí)區(qū)。select systimestamp from dual;36.將系統(tǒng)當(dāng)前時(shí)間轉(zhuǎn)換為字符型。select cast(sysdate as varchar2(20) from dual;37.將'中國'從'US7ASCII'轉(zhuǎn)化為'WE8I

30、SO8859P1'字符集。select convert('中國','US7ASCII','WE8ISO8859P1') from dual;38.將當(dāng)前時(shí)間轉(zhuǎn)換成字符型。select to_char(sysdate) from dual;39.講字符串'05-9月-11'轉(zhuǎn)化為日期型。select to_date('05-9月-11') from dual;三、視圖（5道）1建立一個(gè)最簡單的視圖create view dept_20 as (select * from scott.emp_dll where

31、 deptno=20)create view job_clerk as (select * from scott.emp_dll where job='CLERK')2使用視圖dept_20和job_clerk取得部門20或崗位為CLERK的所有雇員名、工資（不顯重復(fù)值）select ename,sal from dept_20unionselect ename,sal from job_clerk;3使用視圖dept_20和job_clerk私利部門20或崗位為CLERK的所有雇員名、工資（顯示重復(fù)值）select ename,sal from dept_20union al

32、lselect ename,sal from job_clerk; 4使用視圖dept_20和job_clerk取得 部門20并且崗位為CLERK的所有雇員名和工資select ename,sal from dept_20INTERSECTselect ename,sal from job_clerk;5使用視圖dept_20和job_clerk取得部門20但崗位不是CLERK的所有雇員名和工資select ename,sal from dept_20 where empno not in(select empno from job_clerk)四、存儲(chǔ)過程和函數(shù)（7道）1。編寫一個(gè)函數(shù)，計(jì)算

33、scott.emp表中工資總和（工資在1500以內(nèi)的漲200,1500-3000的漲300,3000以上的漲400);create or replace function total_sal return number asv_total number; a number; b number; c number;begin select count(*) into a from emp where sal<1500; select count(*) into b from emp where sal between 1500 and 3000; select count(*) into

34、c from emp where sal>3000; select sum(sal) into v_total from scott.emp; v_total:=a*200+b*300+c*400 +v_total; return v_total;end total_sal;select total_sal from dual; TOTAL_SAL- 327252。編寫一個(gè)存儲(chǔ)過程，循環(huán)向表中插入1000條記錄；create or replace procedure my_insert as cnt number;begin for cnt in 1.1000 loop insert i

35、nto emp values(cnt,'dede','dede',cnt+1,'16-9月-11',3000,21111,20); end loop;end my_insert;call my_insert();select * from emp;3。編寫一個(gè)函數(shù)，計(jì)算工資應(yīng)交稅。(工資2000起交稅，為工資10%;3000-4000,為工資15%;4000以上，為工資20%)create or replace function shui(v_sal in number) return number asv_shui number;beginif

36、 v_sal>2000 thenv_shui:=v_sal*0.1;elsif v_sal>3000 thenv_shui:=v_sal*0.15;elsif v_sal>4000 then v_shui:=v_sal*0.2;end if; return v_shui;end shui;SQL> select shui(2500) from dual;SHUI(2500)- 250232頁 3.查找員工編號(hào)為1111的員工是否存在。存在則返回TURE，不存在則返回FALSE;create or replace function valid_cust(v_no in n

37、umber) return varchar asv_boolean varchar(6); a number;begin select count(*) into a from emp where empno=v_no;if a=0 thenv_boolean:='FALSE'elsev_boolean:='TRUE'end if;return v_boolean;end valid_cust;select valid_cust(1111) from dual;VALID_CUST(1111)-FALSE4.創(chuàng)建存儲(chǔ)過程ADD_ORD，輸入員工號(hào),上司編號(hào)，錄用

38、日期，檢查上司編號(hào)是否存在 ，檢查員工號(hào)是否存在 ，檢查錄用日期是否在1979年1月1號(hào)之后create or replace procedure ADD_ORD(v_empno in number,v_mgr in number,v_date in date) as cnt number;begin if VALID_CUST(v_empno)='TRUE' thendbms_output.put_line('ORA-20001:檢查并輸入正確員工號(hào)');elsif VALID_CUST(v_mgr)='FALSE' thendbms_outp

39、ut.put_line('ORA-20003:檢查并輸入正確上司編號(hào)');elsif v_date<'01-1月-79' thendbms_output.put_line('ORA-20003:檢查并輸入正確日期');else insert into emp values(v_empno,'dede','dede',v_mgr,v_date,3000,21111,20);end if;end ADD_ORD;call ADD_ORD(1111,1111,'03-12月-81');ORA-200

40、03:檢查并輸入正確上司編號(hào)call ADD_ORD(1111,7902,'03-12月-81');select * from emp where empno=1111; EMPNO ENAME JOB MGR HIREDATE SAL COMM- - - - - - - DEPTNO- 1111 dede dede 7902 03-12月-81 3000 21111 20call ADD_ORD(1222,7902,'03-12月-78');ORA-20003:檢查并輸入正確日期call ADD_ORD(1111,7902,'03-12月-78'

41、;)ORA-20001:檢查并輸入正確員工號(hào)5.建立存儲(chǔ)過程UPD_EMP，輸入員工編號(hào)和錄用日期，更新員工表中錄用日期，檢查員工編號(hào)是否存在，不存在則報(bào)ORA-20001:檢查并輸入正確員工號(hào)；檢查錄用日期是否在2011年9月16日之前，不成立則ORA-20003:檢查并輸入正確日期。create or replace procedure UPD_EMP(v_empno in number,v_date in date) as cnt number;begin if VALID_CUST(v_empno)='FALSE' thendbms_output.put_line('ORA-20001:檢查并輸入正確員工號(hào)');e