oracle语句面试题_oracle基础面试题

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说oracle语句面试题_oracle基础面试题,希望您对编程的造诣更进一步.

开篇介绍

大家好，我是Java最全面试题库的提裤姐，今天这篇是数据库面试题系列的第三篇，主要总结了Oracle相关的面试题；在后续，会沿着第一篇开篇的知识线路一直总结下去，做到日更！如果我能做到百日百更，希望你也可以跟着百日百刷，一百天养成一个好习惯。

什么是存储过程，使用存储过程的好处？

存储过程（Stored Procedure ）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。优点：

允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次。
允许更快执行，如果某操作需要执行大量 SQL 语句或重复执行，存储过程比 SQL 语句执行的要快。
减少网络流量，例如一个需要数百行的 SQL 代码的操作有一条执行语句完成，不需要在网络中发送数百行代码。
好的安全机制，对于没有权限执行存储过程的用户，也可授权他们执行存储过程。

Oracle 存储过程怎么创建？

存储过程创建语法：

create or replace procedure 存储过程名（param1 in type，param2 out type）
as
变量 1 类型(值范围);
变量 2 类型(值范围);
Begin
 Select count(*) into 变量 1 from 表 A where 列名=param1；
 If (判断条件) then
   Select 列名 into 变量 2 from 表 A where 列名=param1；
   Dbms_output。Put_line(‘打印信息’);
 Elsif (判断条件) then
   Dbms_output。Put_line(‘打印信息’);
 Else
   Raise 异常名(NO_DATA_FOUND);
 End if;
Exception
 When others then
   Rollback;
End;

注意事项：

存储过程参数不带取值范围，in 表示传入，out 表示输出
变量带取值范围，后面接分号
在判断语句前最好先用 count（*）函数判断是否存在该条操作记录
用 select ...into...给变量赋值
在代码中抛异常用 raise+异常名

存储过程和存储函数的特点和区别

特点：

一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
对于存储过程来说可以返回参数，而函数只能返回值或者表对象。
存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于 FROM 关键字的后面。

区别：

函数必须有返回值；而过程没有
函数可以单独执行；而过程必须通过 execute 执行
函数可以嵌入到 SQL 语句中执行；而过程不行

我们可以将比较复杂的查询写成函数，然后到存储过程中去调用这些函数

如何使用 Oracle 的游标？

Oracle 中的游标分为显示游标和隐式游标 显示游标： 显示游标是用 cursor...is 命令定义的游标，它可以对查询语句(select)返回的多条记录进行处理；显式游标的操作：打开游标、操作游标、关闭游标；

隐式游标： 隐式游标是在执行插入 (insert)、删除(delete)、修改(update) 和返回单条记录的查询(select)语句时由 PL/SQL 自动定义的。 PL/SQL 隐式地打开 SQL 游标，并在它内部处理 SQL语句，然后关闭它。

Oracle 中字符串用什么连接？

使用 || 符号连接字符串，如 ‘abc’ || ‘d’ 的结果是 abcd。

Oracle 中是如何进行分页查询的？

Oracle 中使用 rownum 来进行分页，这个是效率最好的分页方法，hibernate 也是使用 rownum 来进行 Oralce分页的

select * from 
  (select rownum r,a from tableName where rownum <= 20)
where r>10

Oracle中function和procedure的区别?

1、可以理解函数是存储过程的一种 2、函数可以没有参数，但是一定需要一个返回值；存储过程可以没有参数，不需要返回值 3、函数return返回值没有返回参数模式，存储过程通过out参数返回值，如果需要返回多个参数则建议使用存储过程 4、在sql数据操纵语句中只能调用函数而不能调用存储过程

触发器的作用有哪些？

触发器可通过数据库中的相关表实现级联更改；通过级联引用完整性约束可以更有效地执行这些更改。
触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。
触发器还可以强制执行业务规则
触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

Oralce怎样存储文件，能够存储哪些文件？

Oracle 能存储 clob、nclob、 blob、 bfile

Clob 可变长度的字符型数据，也就是其他数据库中提到的文本型数据类型
Nclob 可变字符类型的数据，不过其存储的是Unicode字符集的字符数据
Blob 可变长度的二进制数据
Bfile 数据库外面存储的可变二进制数据

Oracle分区是怎样优化数据库的?

Oracle的分区可以分为：列表分区、范围分区、散列分区、复合分区。 1、增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍可以使用； 2、减少关闭时间：如果系统故障只影响表的一部份分区，那么只有这部份分区需要修复，可能比整个大表修复花的时间更少； 3、维护轻松：如果需要得建表，独产管理每个公区比管理单个大表要轻松得多； 4、均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能； 5、改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快 6、分区对用户透明，最终用户感觉不到分区的存在。

在千万级的数据库查询中，如何提高效率？

数据库设计方面：

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询： select id from t where num=0
3.并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，查询可能不会去利用索引，如一表中有字段 sex，male、female 几乎各一半，那么即使在 sex 上建了索引也对查询效率起不了作用。
4.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
5.应尽可能的避免更新索引数据列，因为索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新索引数据列，那么需要考虑是否应将该索引建为索引。
6.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
7.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
8.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
9.避免频繁创建和删除临时表，以减少系统表资源的消耗。
10.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
11.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先 create table，然后 insert。
1. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

SQL语句方面：

1.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20
3.in 和 not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3
4.下面的查询也将导致全表扫描： select id from t where name like ‘%abc%’
5.如果在 where 子句中使用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where num=@num 可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num
6.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100 应改为: select id from t where num=100*2
7.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)= ‘ abc ’ – name 以 abc 开头的 id select id from t where datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’生成的 id 应改为: select id from t where name like ‘abc%’， select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
8.不要写一些没有意义的查询，如需要生成一个空表结构： select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table #t(…)
9.很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)
10.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
11.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过 1 万行，那么就应该考虑改写。
12.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
13.尽量避免大事务操作，提高系统并发能力。