Oracle面试笔试题集

• 本文初衷是为了学习归纳，若有错误，欢迎指出。

Oralce数据库

数据库基础题

1. 数据库的常见分类有哪些？

数据库可以按照存储模型，或者关系型/非关系型来进行分类。

• 如果是按照存储模型分类，则可以把数据库分为网状数据库，关系型数据库和层次数据库。其中使用最广泛的是关系型数据库，比如Oracle，MySQL，DB2，SQLServer。

  网状数据库是以记录类型为节点的网状数据模型的数据库，处理方法是将网状结构分解成若干棵二级或二级以上的树结构，它的代表有DBTG（数据库任务组系统）。

  层次数据库，也称为树状数据库，它将数据组织成有向有序的树结构，并用一对多的关系联结不同层次的数据库，典型代表是IBM的IMS数据库。

  关系型数据库RDBMS，它通过数据，关系，和对数据的约束三者组成的数据模型来存放和管理系统，现实中的各种实体以及实体之间的各种联系均可以用关系模型来表示，并用SQL结构化查询语言来检索和操作数据库。其特点有：（1）数据以表格形式存在（2）每一行存储着一条单独的记录（3）每个列作为一条记录的一个属性存在（4）许多的行和列组成一张表（5）若干表组成数据库。

• 如果是按照是否关系模型来分类，可以把数据库分为关系型数据库和非关系型数据库，其中关系型数据库又可以根据是否在内存分为内存数据库和非内存数据库，内存数据库顾名思义就是将数据存放在内存中操作，相对于磁盘的读写速度要快很多，典型代表是SQLite和TimesTen，非内存数据库就常见的那几种RDBMS。另外关系型数据库还有跟大小划分，小型的如Access，Foxbase，SQLite，中型的如MySQL，SQLServer，PostgreSQL，大型的如Oracle，DB2。对于非关系型数据库，也即是NoSQL，分为键值数据库，列存储数据库，文档型数据库，和图形数据库。键值数据库主要会用到一个哈希表，这个表中有一个特定的键和一个指向特定数据的指针，优势是简单，容易部署，典型的代表是Redis。列存储数据库通常是用来应对分布式存储的海量数据，键依然存在，但是他们的特点是键指向了多个列，典型代表有HBase，HyperTable。而文档型数据库和键值数据库类似，他的数据类型是版本化的文档，半结构化的文档以特定的格式存储，比如JSON，可以看作是键值数据库的升级版，查询效率也更高，典型代表是MongoDB。对于图形数据库，采用图形模型，能够扩展到多台服务器，代表是Giraph，Titan等。

2. 数据库将表存入存储系统的方式有哪两种

参考连接：https://www.jianshu.com/p/3d3950c9fb06

行存储和列存储。

绝大部分数据库是用行存储的，行存储法是将各行放入连续的物理位置，把行当作一个整体来存储，然后由数据库引擎根据每个查询提取需要的列，行存储适用于OLTP系统，插入更新等频繁的系统。从查询来说，行存储笔记适合随机查询，并且在整行数据读取上要由于列存储。

列存储是将数据按照列存储到数据库中，它的行数据中包含一个列或者多个列，使用与OLAP，数据仓库，数据挖掘等查询密集型应用，不适合在OLTP这种DML频繁的系统。优点是它的每个字段的数据聚集存储，在查询只需要少数几个字段时能大大减少读取的数据量，尤其在数据量大时查询速度很快。另一个优点是因为字段聚集存储，所以更容易压缩解压，因为同一列的数据中数据类型是相同的，天生适合压缩…。

简述下关系型数据库完整性规则？

关系型数据库的完整性规则分为三类：实体完整性，参照完整性和用户自定义完整性。

实体完整性是指惯性的主属性也就是主键，不能为空，如果为空即存在不可区分的实体，这和现实的环境相矛盾，说明这个实体一定不是完整的实体。

参照完整性指的是如果关系R1的外键和关系R2的主键相符，那么外键的每个值必须在关系R2的主键值中都可以找到或者是空值。

用户自定义完整性是根据实际应用环境需要，对某一具体应用所涉及的数据提出约束条件。比如使用CHECK约束，UNIQUE约束，Default约束，Identity约束，Not null等。

3. 数据库的约束由哪些？

数据库的约束可以分为五类：主键约束，唯一约束，非空约束，检查约束，外键约束。

• 主键约束：主键唯一标识一个实体，必须非空且唯一
• 唯一约束：除了主键外，其他列如果不希望出现重复值，也可以使用唯一约束。
• 非空约束：列值不能为空
• 检查约束：检查列值是否合法。
• 外键约束：外键值必须是参照表的主键或者空值。

对于外键和主键约束，主要是为了维护关系型数据库的完整性，主键是能唯一标识一条记录，外键是用于和另一张表关联，并具有级联更新或删除的特点，外键更新的值也必须是主键表中主键有的值。

4. 什么是第一，二，三，BC范式？

范式主要目的是为了消除重复数据减少数据冗余。

第一范式：数据库表中的每一列都不可再分。

第二范式：在第一范式的基础上，消除非主属性对主键的部分依赖，也就是1NF基础上，每一个非主属性必须完全依赖于主键。

第三范式：在第二范式基础上消除传递依赖，或者说在1NF基础上，每个非主属性都不部分依赖于主键也不存在传递依赖。

符合第三范式的数据表，可以消除数据容易，更新异常，插入异常和删除异常。

BC范式：在第三范式基础上消除对主键子集的依赖，相比于第三范式，多限制了主键中的主属性也不能有部分依赖或者传递依赖。BC范式算是第三范式的修正版，能使数据库冗余度更小。

一般来说，数据库只要满足第三范式就可以了。

5. 事务的概念和四种特性是什么？

事务是一个操作序列，这些操作要么都做，要么都不做，具有不可分割的关系。通常以Begin Transaction开始，以Commit或Rollback结束。

四个特性如下：

• 原子性：指事务在逻辑上是不可分割的操作单元，其所有语句要么都执行，要么都补执行。比如A给B转账100元，A账号减去100元并且B账号增加100元，这两个动作不可分割只完成一个。
• 一致性：事务完成一项工作，将数据库中的数据从一种一致性状态转换到另一种一致性状态，以体现现实世界中的状态变化。还是比如转账，A和B的总资产加起来是5000元，那么不管A给B转账还是B给A转账多少次，他们之间总和必须还是5000元。
• 隔离性：隔离是针对并发事务而言，所谓并发就是数据库服务器同时处理多个事务，隔离性就是隔离多个并发事务之间相互影响。
• 持久性：是指事务一旦提交成功，对数据的修改是永久性的，即使数据库重启，事务做的更改操作不会丢失。

6. 事务的四种隔离级别分别是什么？

首先要知道如果没有隔离性，事务会发生什么问题：

第一个是脏读，一个事务读取了另一个事务修改但没有提交的数据，比如A对B转账100元，A的数据显示已经转账给B但事务没有提交，B去查看时发现确实多了100元，但由于事务没有提交，后续可能被回滚，回滚的话实际上A没有转账给B100元，就属于脏读。

第二个是不可重复度，在同一个事务操作中，同一条查询语句，在T1时间读取和在T2时间读取的数据显示不同，这期间可能数据被DML了。比如事务在T读取某个数据，而事务T2立刻修改了这个数据并提交，那么T1事务重复读取时就得到一个不同的数据结果。

第三个是幻读，幻读和不可重复读有点类似，都是在同一事务中查询，但期间有其他事务对数据做了DML操作，不同的是不可重复读针对的是同一个数据项，而幻读针对的是一个数据整体，比如数据条数。

在SQL中定义了4种隔离级别，每一种级别都规定了事务所作的修改。较低级别的事务通常可以执行更高的并发，系统的开销也更低，但存在问题较危险。

• 第一种是Read Uncommitted 未提交读。事务中的改变，即便没有提交对其他事务而言也是可见，实际很少用到。
• 第二种是Read Committed 提交读。大多数数据库默认的级别，一个事务只能看见已经提交事务所作的改变，对应解决了脏读问题。
• 第三种是Repeatable Read 可重复读。确保多次重复读取同样的数据，能得到同样的结果，对应解决不可重复读问题。
• 第四种是Serializable 可串行化，序列化。它通过强制事务排序，强制事务串行执行，使得他们不可能相互冲突，从而解决幻读问题，是最高的隔离级别。实际应用较少，因为花费代价大，需要在每个读的数据行上加上共享锁，会导致大量的超时现象和锁竞争，除非非常需要确保数据一致性且接受没有并发情况下使用。

7. 简述存储过程，它有什么优点？能否写下存储过程的语法。

存储过程是用户定义的一系列SQL语句的集合，涉及特定表或其他对象的任务，用户可以调用存储过程。存储过程用于执行特定的操作，可以接受输入参数，输出参数，返回单个或者多个结果集。存储过程允许用户声明变量并且可包含程序流，逻辑以及对数据库的查询。

存储过程的优点如下：

• 存储过程增强了SQL语言的功能和灵活性，这点比如可以用结构化的控制语言编写，能完成复杂的判断和运算
• 存储过程可以保证数据的安全性，可以使没有权限的用户在权限控制下间接地存储数据库中的数据。
• 通过存储过程可以使相关的sql操作连在一起发生，从而维护数据库完整性
• 存储过程编译一次后，由于已经做了语法分析，并优化执行，那么下一次运行时就能极大地改善SQL语句的性能，能以更快的速度执行。
• 可以降低网络的通信量，存储过程的SQL是一次性发送到数据库服务器的。
• 企业易于维护，业务规则发生变化时，只需修改存储过程中的逻辑。

存储过程的语法如下：

CREATE [OR REPLACE] PROCEDURE Procedure_name [(
argment [ {
  IN |OUT | IN OUT}] TYPE,
argment [ {
  IN |OUT | IN OUT}] TYPE)]
{
  IS | AS}
<类型.变量的说明，后面加分号>
BEGIN
<执行部分>
ECXCEPTION
<可选的异常错误处理程序>
END	存储过程名;

8. 存储过程和函数的区别是什么？各自作用？

存储过程和函数都是存储在数据库中的程序，都可以接受输入和输出参数，可以由用户直接或间接的调用，都是一系列SQL的集合。

但不同的是（1）标识符不同，函数是FUNCTION，过程是PROCEDURE；（2）函数必须有返回值，并且只有一个返回值；（3）存储过程没有返回值类型，不能直接赋值给变量，函数有返回值类型，在调用函数时，除了在select语句中，其他情况下必须将返回值赋值给一个变量。（4）刚提到的，函数可以在SELECT语句中直接使用，而过程不行。

9. 触发器的作用？分类？优缺点有哪些？

触发器和存储过程的区别在于，触发器不能显式调用，而是由某个事件来触发运行，即触发器是在某个事件发生隐式地运行的，而存储过程时可以被用户或由应用程序显式调用执行的。

11. 什么是游标？如何知道游标已经执行到最后?

游标是一种机制，可以对select语句返回的结果集，每次处理一行或者一部分行。

12. 什么是视图？视图的作用是什么？

视图是从数据库的基本表中选取出来的数据组成的逻辑窗口，是一个虚拟表，存放的只是视图的定义而不存放实际的数据，而视图中的数据也会在引用视图时动态生成，用户只需集中关注想要的数据，而非全部数据，大大提高运行效率和用户满意度。视图看上去和基表类似，通过视图修改数据时，实际上是在修改基本表，相反基本表的数据更新也会反映在视图上。

视图的作用由以下几点：（1）隐藏了数据的复杂性（2）有利于控制用户对表的某些数据的访问权限，提高了安全性。（3）能够简化查询结构，执行复杂查询操作。（4）能让用户可以多角度，更灵活的共享同一数据。

13. 什么情况下可以对视图执行DM操作？

视图对DML操作应遵循以下内容：

• 简单视图可以DML操作
• 当视图包含group by，distinct时不能执行删除操作
• 当视图有以下情况时，不能通过视图来修改基本表或插入数据到基本表：
  • 有group by，distinct
  • 视图中包含了表达式的定义
  • 视图中有ROWNUM伪列（针对Oracle）
  • 基表中未在视图中选择的其他列定义为非空且无默认值的时候。

DROP VIEW view_name可以删除视图，删除后，基于被删除视图的其他视图或者程序会无效。

14. SQL语句有哪些常见的分类？

• DML：UPDATE ，INSERT ，DELETE ，MERGE ，SELECT…FOR UPDATE;
• DDL：CREATE TABLE/INDEX，ALTER TABLE/INDEX，DROP TABLE/INDEX，TRUNCATE TABLE；
• DCL：数据控制语言，GRANT，REVOKE；
• DQL：SELECT
• TCL：事务控制语言（Oracle），COMMIT，ROLLBACK，SAVEPOINT，SET TRANSATION，SET CONSTRAINT，ROLLBACK TO SAVEPOINT；
• SCS：会话控制语言（Oracle）,ALTER SESSION，ALTER SYSTEM，SET ROLE；
• ESS：嵌入式sql（Oracle），OPEN ，CLOSE， CONNECT ，DESCRIBE ，WHENEVER， PREPARE ，EXECUTE ，FETCH

15.笛卡尔积是什么？产生笛卡尔积的条件？

​ 笛卡尔积是把所有记录做乘积操作，生成大量结果，而通过结果中可用的值有限。

​ 笛卡尔积的原因：（1）连接条件缺失，没有写where条件（2）连接条件无效，比如where条件写的是A.ID = A.ID （3）统计信息不准确，假如表A有100W条记录，但统计信息中记录的是0行，那么这种情况下的连接就容易形成笛卡尔积。

16.什么是OLTP和OLAP？

​ 数据处理可以大致分为两大类：OLTP（联机事务处理）和OLAP（联机分析处理）。

​ OLTP是传统关系型数据库的主要应用，记录实时的DML操作，主要是执行基本的，日常的事务处理，比如银行交易。一般情况下，OLTP系统数据量少，DML操作频繁，并行事务处理多，OLTP系统强调数据库处理效率，要求实时。主要面对数据库。

​ OLAP是数据仓库系统最主要的应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。OLAP实时性要求不高，DML不频繁，但数据量很大，主要面对数据仓库。

​ 在大数据中，对于OLAP，列式存储或者NoSQL的模式比传统意义上的行存储更有优势。

17.数据类型有哪些？

18.如何定义序列？

数据库零散知识点

1. Delete，Drop和Truncate的三者之间的区别

相同点是Truncate和不带where条件的delete及drop都会删除表内所有的数据，而drop和Truncate都是DD语句，执行后会自动提交；这三者执行后，索引的大小都会自动进行维护。

不同的是

（1）drop table还会删除表结构和表上的约束，并且依赖于该表的存储过程和函数等都将变成无效状态；而Truncate和delete只是删除表的数据，不会影响表的定义，存储过程，触发器，约束等。

（2）刚刚提到的drop和Truncate语句都是DDL语句，隐式提交，不能回滚；delete是DML语句，事务提交后数据才生效，可以回滚。

（3）drop table后会将表所占用的空间全部释放，Truncate table后表的大小会变为初始化的大小，而delete table后表的大小不会发生改变，所以正常情况下，truncate后的表比delete之后的表查询速度要快。

（4）drop和delete都可以作用域视图，truncate不能作用于视图

（5）执行速度一般是drop > truncate > delete，drop和truncate因为底层修改了数字字典，所以无论大表还是小表都执行非常快，而delete是需要读取数据到undo，所以对大表进行delete全表操作将会非常慢。

（6）安全性方面，drop和truncate在无备份情况下需要谨慎。

​ 想删除部分数据行的话，用delete带上where条件，想删除表数据及结构就用drop，想保留表结构而将所有数据都删除就用truncate。

2. Null有哪些注意事项？

• 判断是否为null只能用 is null 或者 is not null
• 空值是无效的，未指定的，未知的或者不可预知的值，而不是0，也不能说是空格。
• 包含空值的数学运算结果都为空值null
• null在order by排序中默认为最大值，desc在最前，asc在最后，可以加上null last来限制null值的显示
• 大部分函数都不计算空值。count(*)和count(1)是统计表中的数据量，这时不管有没有null值，而假如count(某个具体列)，那它也是忽略null值的。

3. 如何判断一个存储过程是否正在运行？

select *
  from v$db_object_cache
 where type = 'PROCEDURE'
   and locks > 0
   and pins > 0;
   
select * from all_objects where object_type = 'PROCEDURE' and object_name = 'AW_TRUNC_PROC';

4. 如何将文本文件或Excel中的数据导入数据库？

• pl/sql中的文本导入器（tools - text import）
• SQL*Loader
• shell脚本
• excel工具批量生成插入语句
• MySQL的Navicat有功能直接导入。
• Oracle的exp和imp功能

开发类常考题

SQL操作题

1. 怎么将相邻记录合并到一条？

可以利用分析函数lag()函数和lead()函数，他们可以提取前N条记录和后N条记录

select owner,
       name,
       lag(name, 1, null) over(partition by owner order by name)  LAG_NAME
  from v$db_object_cache
 where type = 'PROCEDURE';
 
	OWNER				NAME						LAST_NAME
1	APEX_040200		WWV_FLOW_INIT_HTP_BUFFER	
2	SYS				AW_DROP_PROC	
3	SYS				AW_TRUNC_PROC					AW_DROP_PROC
4	SYS				LOGMNR_DDL_TRIGGER_PROC			AW_TRUNC_PROC
  

2. 如果查询的列中含有特殊字符，如通配符“%”和“_”，那么该如何查询这些特殊字符？

SELECT * FROM SCOTT.EMP WHERE NAME  LIKE 'A\%' ESCAPE '\';	--	ESCAPE代表将后面的字符用于转义的意思。

3. 如何随机抽取表SCOTT.EMP的前5条记录？

使用ROWID来查询：

SELECT * FROM (SELECT * FROM SCOTT.EMP ORDER BY SYS_GUID()) WHERE ROWID <= 5;

函数相关

1. Oracle常用日期处理函数有哪些？他们的作用是什么？

1. 首先要知道Oracle常用的日期数据类型是DATE和TIMESTAMP。DATE可以保存日期和时间，日期范围从公元前4712年1月1日至公元9999年12月31日，数据显示格式是“年 月 日 小时 分 秒”，并且在数据库中的存储固定为7个字节。而TIMESTAMP与DATE的区别是它不仅可以显示日期和时间，还可以保存小数秒，小数位数范围从0-9，默认为6位；数据显示格式是“年 月 日 时 分 秒 小数秒”；在数据库用7个或者11个字节存储，如果精度是0 那么用7个字节存储，如果精度不为0那么用11个字节存储。

   注意：如果TIMESTAMP日期类型和数值进行加减运算，那么结果会自动转换为DATE类型，小数秒会被去掉。

2. 运算注意：日期加减一个数字结果仍为日期；两个日期相减返回之间相差的天数；

   默认的日期格式是“DD-MON-RR”？？？？

3. 会用到的修改系统日期格式操作（在遇到日期查询错误时可能要修改日期格式）：

   • 查询当前数据库日期格式

     SELECT SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') FROM DUAL;
     

   • 修改当前会话的日期格式

 ALTER SESS