Oracle面试题

1、Oracle(66道)一、理论题(15道)1什么是视图？视图的作用是什么？如何创建视图？（1）定义：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。（2）作用：简化查询 保护我们的一些私有数据，通过视图也可以用来更新数据，但是我们一般不这么用 缺点：要对视图进行维护。（3）创建：创建视图需要CREAE VIEW系统权限，视图的创建语法如下： CREATE OR REPLACE FORCE|NOFORCE VIEW 视图名(别名1，别名2.) AS 子查询 WITH CHECK OPTION CONSTRA

2、INT 约束名 WITH READ ONLY2什么是索引？为什么使用索引？如何使用索引？（1）索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。 （2）索引主要是为了加快查询的速度。（3）使用：CREATE UNIUQE | BITMAP INDEX . ON . ( | AS

3、C | DESC, | ASC | DESC,.) TABLESPACE STORAGE LOGGING | NOLOGGING COMPUTE STATISTICS NOCOMPRESS | COMPRESS NOSORT | REVERSE PARTITION | GLOBAL PARTITION相关说明1）UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。2） | ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时

4、效率更高)4）STORAGE：可进一步设置表空间的存储参数5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)6）COMPUTE STATISTICS：创建新索引时收集统计信息7）NOCOMPRESS | COMPRESS：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区3.SQL语句索引优化规则

5、不带where条件的SQL语句一定不能使用索引 在where条件中有索引的字段不能使用函数 例子：假设我们查询在1987年参加工作的所有员工SQL select * from emp where to_char(hiredate,yyyy)=1987;在where条件中有索引的字段不能参与运算 例子：查询在10000天以前参加工作的员工信息 SQL select * from emp where sysdate-hiredate10000; SQL select * from emp where hiredate select e.*,d.dname from emp e,dept d whe

6、re e.deptno=d.deptno and d.dname=ACCOUNTING;4DDL语句包括哪些？Create 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数据库启动时必需开启的后台服务 OracleOrahome90TNSListener 使第三方的软件或语言访问 Ora

7、cleServiceETC Oracle的实例 CRUD 增删改查 注意：中的ETC是你起的实例的名字8什么是事务？.transacation 事务特点？如何使用事务？（1）事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C+或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。例如：在关系数据

8、库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。事务是恢复和并发控制的基本单位。（2）事务的四个特性原子性 不可分割持久性 - 当数据写入到数据库物理文件中后，该数据被 持久化隔离性 - 事务之间相互独立互不干扰一致性 - 数据安全(3)使用：整体提交(commit) 整体回滚(rollback)9. Oracle备份数据表数据 create table 备份表名称 as 查询语句; Oracle备份表结构 create table emp_bak as select * from emp where 1=2;10. Oracle备份数据库(1)冷备份 冷备份是Oracle最简单

9、的一种备份；执行冷备份前必须关闭数据库；然后使用操作系统实用工具或者第三方工具备份所有相关的数据库文件。 优点:能简单快速地备份。能简单快速地恢复。执行简单。 缺点:必须关闭数据库，不能进行点恢复。 (2)热备份 热备份是当数据库正在运行时进行数据备份的过程。执行热备份的前提是：数据库运行在可归档日志模式。适用于24X7不间断运行的关键应用系统。 优点:备份时数据库可以是打开的。热备份可以用来进行点恢复。初始化参数文件、归档日志在数据库正常运行时是关闭的，可用操作系统命令拷贝。 缺点:执行过程复杂。由于数据库不间断运行，测试比较困难。不能用操作系统实用工具拷贝打开的文件。必须使用Oracle提

10、供的ocopy工具来拷贝打开的文件。热备份可能造成CPU、I/O过载，应在数据库不太忙时进行。 (3)Export导出数据库对象 冷备份和热备份都备份物理数据库文件，因而被称为物理备份。而export备份的是数据库对象，因此被称为逻辑备份。 优点:能执行对象或者行恢复。备份和恢复速度更快。能够跨操作系统平台迁移数据库。数据库可一直运行。 缺点:export并不是冷备份和热备份的替代工具。冷、热备份可保护介质失效。export备份可保护用户或应用错误。将数据库TEST完全导出,用户名system 密码manager 导出到D:daochu.dmp中 exp system/managerTEST

11、file=d:daochu.dmp full=y11.to_char和to_date函数应用 (1)to_char 语法：to_char(时间数据,制定格式) SQL: select to_char(sysdate,yyyy-mm-dd hh24:mi:ss d ddd day) from dual; yyyy-mm-dd 年月日 hh24:mi:ss 时分秒(24) d 一周第几天 ddd 一年的第几天 day 星期几 (2)to_date: 将文本类型数据按照制定格式转换成时间类型数据 语法：to_date(具体的时间字符数据,时间字符串格式) to_date(2010-01-19,yyy

12、y-mm-dd) 注意：Oracle中的date字段类型及其特殊12. 存储过程定义及优势？(1)定义：存储过程（Stored Procedure ）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。 存储过程是由流控制和SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。在ORACLE 中，若干个有联系的过程可以组合在一起构成程序包。(2)优 点：1). 存储过程只在创造时进行编

13、译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2).当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3).存储过程可以重复使用,可减少数据库开发人员的工作量。 4).安全性高,可设定只有某用户才具有对指定存储过程的使用权。13.如何调用存储过程 两种方式：1、指令方式 - exec 存储过程名称; 2、使用PL/SQL块调用 begin 存储过程名称; end;14带参数的存储过程语法？ 两大类：a)

14、带输入参数 b) 带输出参数 a) 带输入参数的存储过程 语法：create or replace procedure 存储过程名称(参数名称1 in 类型, . ,参数名称n 类型) as PL/SQL块b) 带输出参数的存储过程 语法：create or replace procedure 存储过程名称(参数名称1 out 类型, . ,参数名称n out 类型) as PL/SQL块 15. 触发器的语法及案例？当需要级联操作的时候可能用到触发器语法：create or replace trigger 触发器名称 before|after insert or update or dele

15、te of 字段名称 on 表 begin PL/SQL语语句案例：当对Emp进行CUD操作时候做相应的输出信息。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(delete is ok); end if;end;二、基本SQ

16、L语句（39道）1.向DEPT表插入新的部门（50，MANAGEMENT，BEIJING）并显示；INSERT INTO dept VALUES(50,management,beijing);SELECT * FROM dept WHERE deptno=50;2.将部门号为50的地址改为SHANGHAI，再执行一次回滚，而后提交；UPDATE dept SET loc=shanghai WHERE deptno=50; ROLLBACK; COMMIT;SELECT * FROM dept WHERE deptno=50; 3.统计各部门工资的平均值；SELECT deptno,AVG(sa

17、l) FROM emp GROUP BY deptno;4.查询SMITH上级领导的姓名；SELECT mgr FROM emp WHERE ename=smith;5.查询工资高于JONES的所有员工的姓名和工资；SELECT ename,sal FROM emp WHERE sal(SELECT sal FROM emp WHERE ename=jones);6.按员工的工资划分等级，工资高于2500的为A等，在1500和2500间（包括1500及2500）为B等， 其余为C等（提示：增加新列DJ）；alter table emp add DJ varchar(1);Update emp

18、set DJ=case when sal2500 then A when sal1500 and deptno=30;UNIONSELECT empno,ename,sal FROM emp WHERE sal1500 and deptno=30;9.按照员工的部门号升序排列，同部门的再按员工工资降序排列；SELECT deptno,sal FROM emp ORDER BY deptno,sal desc;10.查工资不超过2000的员工所有信息。SELECT * FROM emp WHERE sal=2000;11.使用group by进行单列分组 ：显示每个部门的平均工资和最高工资SEL

19、ECT deptno,AVG(sal),MAX(sal) from emp GROUP BY deptno;12.使用group by进行多列分组：显示每个部门每种岗位的平均工资和最高工资SELECT deptno,job,AVG(sal),MAX(sal) FROM emp GROUP BY deptno,job;13.使用having子句限制分组显示结果 ：显示平均工资低于2000的部门号、平均工资及最高工资SELECT deptno,AVG(sal),MAX(sal) FROM emp GROUP BY deptno HAVING AVG(sal)2000;14.相等连接(emp和dep

20、t表，emp.empno,ename,dname字段)select emp.empno,ename,dname from emp,dept where emp.deptno=dept.deptno;15.内连接(emp和dept表，emp.empno,ename,dname字段)select empno,ename,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 joi

21、n 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. 求-12.01的绝对值。select abs(-12.01) from dual;19.求小于15.6的最大整数。select floor(15.6) from dual;20.11除以4的余数。select mod(11,4) from dual;21.求-2的3次方和2的-1次方；se

22、lect power(-2,3),power(2,-1) from dual;22.12.666四舍五入到小数点后一位。select round(12.666,1) from dual;23. 查询a和A的ASCII码值。select ascii(a) a,ascii(A) A from dual;24.查ASCII码值为56的字符。select chr(56) from dual;25连接Good和Morning两个字符串。select concat(Good,Morning) from dual;26找h在hello中的位置。select instr(hello,h) from dual;

23、27示正好为5个字符的员工的姓名. select ename from emp where length(ename)=5;28.缺省值为20字符串中的缺省替换为默认。select replace(缺省值为20,缺省,默认) from dual;29.hello中取第一个字符到第三个字符的字符串。select substr(hello,1,3) from dual;30.求14个月前的时间。select add_months(sysdate,-14) from dual;31.查询现在的日期时间。select current_date from dual;32.查询当前会话的日期时间。sele

24、ct current_timestamp from dual;33.查询1998年8月31号到现在有多少个月。select months_between(sysdate,31-8月-1998) from dual;34.查询现在时间。select sysdate from dual;35.查询当前系统日期时间及时区。select systimestamp from dual;36.将系统当前时间转换为字符型。select cast(sysdate as varchar2(20) from dual;37.将中国从US7ASCII转化为WE8ISO8859P1字符集。select convert

25、(中国,US7ASCII,WE8ISO8859P1) from dual;38.将当前时间转换成字符型。select to_char(sysdate) from dual;39.讲字符串05-9月-11转化为日期型。select to_date(05-9月-11) from dual;三、视图（5道）1建立一个最简单的视图create view dept_20 as (select * from scott.emp_dll where deptno=20)create view job_clerk as (select * from scott.emp_dll where job=CLERK)

26、2使用视图dept_20和job_clerk取得部门20或岗位为CLERK的所有雇员名、工资（不显重复值）select ename,sal from dept_20unionselect ename,sal from job_clerk;3使用视图dept_20和job_clerk私利部门20或岗位为CLERK的所有雇员名、工资（显示重复值）select ename,sal from dept_20union allselect ename,sal from job_clerk; 4使用视图dept_20和job_clerk取得 部门20并且岗位为CLERK的所有雇员名和工资select en

27、ame,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)四、存储过程和函数（7道）1。编写一个函数，计算scott.emp表中工资总和（工资在1500以内的涨200,1500-3000的涨300,3000以上的涨400);create or replace function total

28、_sal return number asv_total number; a number; b number; c number;begin select count(*) into a from emp where sal3000; 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。编写一个存储过程，循环向表中插入1000条记录；

29、create or replace procedure my_insert as cnt number;begin for cnt in 1.1000 loop insert into 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。编写一个函数，计算工资应交税。(工资2000起交税，为工资10%;3000-4000,为工资15%;4000以上，为工资20%)create or replace function

30、shui(v_sal in number) return number asv_shui number;beginif v_sal2000 thenv_shui:=v_sal*0.1;elsif v_sal3000 thenv_shui:=v_sal*0.15;elsif v_sal4000 then v_shui:=v_sal*0.2;end if; return v_shui;end shui;SQL select shui(2500) from dual;SHUI(2500)- 250232页 3.查找员工编号为1111的员工是否存在。存在则返回TURE，不存在则返回FALSE;crea

31、te or replace function valid_cust(v_no in number) 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.创建

32、存储过程ADD_ORD，输入员工号,上司编号，录用日期，检查上司编号是否存在 ，检查员工号是否存在 ，检查录用日期是否在1979年1月1号之后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:检查并输入正确员工号);elsif VALID_CUST(v_mgr)=FALSE thendbms_output.put_line(ORA-20003:检查并输入正确上司编号);elsif v_date16-9月-11 thendbms_output.put_line(ORA-20003:检查并输入正确日期);else update emp set HIREDATE=v_date where empno = v_empno;end if;end UPD_EMP;call UPD_EMP(1111,17-9月-11);ORA-20003:检查并输入正确日期call