oracle常见面试题及答案

需求: 写一个邮件系统, 采用oracle+jsp+servlet来完成.

1． 创建一个表空间。

2． 创建一个用户,将用户赋到表空间上.

3． 给用户赋权限.

4． 以新建用户登录,创建一个程序包.

5． 在表空间上建表，两个表

用户表

Id int

Uname varchar

Upass varchar

邮件表

eId int

Title varchar

Contents varchar

Uid int 外键

附件表

Id int

Filepath varchar

Eid int 外键

6． 作增，删，改，查的操作,全部封装到存储过程中

7． 写一个java程序来调用.

1．    解释FUNCTION，PROCEDURE和PACKAGE区别

答：function 和procedure是PL/SQL代码的集合，通常为了完成一个任务。procedure 不需要返回任何值, 而function将返回一个值. 在另一方面，Package是为了完成一个商业功能的一组function和procedure的集合。

2．    取某个序列的当前值的PL/SQL语句怎么写？

答：SELECT 序列名.CURRVAL  FROM  DUAL；

Create sequence 名字 start with x increment by y maxvalue z nocycle

3．    说明ORACLE数据库实例与ORACLE用户的关系？

答：实例可以包含多个用户，一个用户只能在一个实例下

4．    创建用户时，需要赋予新用户什么权限才能使它连上数据库？

答：grant CONNECT [on 表名] to 用户名

5．比较truncate和delete命令？

答：两者都可以用来删除表中所有的记录。区别在于：truncate是DDL（data defining language数据定义语言），它移动HWK，不需要rollback segment（处理事务回滚操作）而Delete是DML（data manufacturing language数据操作语言）操作，需要rollback segment（处理事务回滚操作）且花费较长时间

6．给出数据的相关约束类型？

答：主键约束，外键约束，非空约束，唯一约束，检查约束。

7．说明索引的类型与作用？

答：索引类型上分为聚集索引，非聚集索引. 其作用是加快查询速度。

8．获取某个字符字段的最后3个字符的函数是什么？

答：select substr (字段,(length(字段)-3)) from 表

9．取当前系统时间点日期（不包括年月）的SQL写法是怎样的？

答：Select substr (to_char(sysdate,’YYYYMMDDh24hh:MM:SS’),5) from dual;

to_char(sysdate,’YYYYMMDDh24hh:MM:SS’)

10．返回大于等于N的最小整数值？

答：select ceil(1.5) from dual;

floor(1.5)

11．将根据字符查询转换后结果，规则为：’A’转换为’男’，’B’转换为’女’，其他字符转换为’未知’，请用一个SQL语句写出。

答：select decode(字符,’A’,’男’,’B’,’女’,’未知’) from dual;

12．如何搜索出前N条记录？

答：select * from 表 where  Rownum <= N;

13．如何搜索出第N～M条记录？

答：select * from 表 where Rownum <= 100 Minus select * from 表 where Rownum <= 90;

Select * from ( Select rownum rn, t.* from (Select * from 表名) t where rownum<=大值 ) where rn>=小值

14．有一个数据表（TEST），字段如下：

ID      number

PARENT_ID      number

NAME      Varchar(20)

请使用PL/SQL来按父子层次关系查询出该表的所有数据

答：Select * from test a, test b Where a.parent_id = b.id;

15．怎样用SQL语句实现查找一列中的第N大值？

答：select * from (select * from 表 order by 列名 Desc) where Rownum <= N Minus select * from (select * from 表 order by 列名 Desc) where Rownum <= N-1;

select * from 表 order by 列名 Desc

1 2 3 4 5 6 7 8 9

9 8 7

8 9

请按要求写出下列题目的答案:

表结构

∙ Empno—-雇员编号

∙ Ename—-雇员名

∙ Job—-工作类型

∙ Mgr—-雇员的经理

∙ Hiredate—-雇用日期

∙ Sal—-每月工资

∙ Comm—-销售奖金

∙ Deptno—-部门编号

∙ Dname—-部门名

∙ Loc—-地址

17.下面哪两个查询可以用来确定员工Blake所在部门的名称? B C E

a. SELECT dname FROM dept WHERE ename =(‘BLAKE’)；

b. SELECT d. dname FROM dept d NATURAL JOIN emp e

WHERE e.ename =‘BLAKE’； (主健和外键的名称和类型必须完全相等)

c. SELECT dname FROM dept d,emp e

WHERE e.ename =（‘BLAKE’）

AND d.deptno = e.deptno；

d. SELECT dname FROM dept JOIN emp USING (dname)

WHERE ename =‘BLAKE’；

e. SELECT dname FROM dept JOIN emp

ON dept.deptno = emp.deptno

WHERE ename =‘BLAKE’；

18. 如果Sal包含各个员工的月薪,那么下面哪一个查询将显示EMP表中的各个员工的年薪? ( d )

a. SELECT sal * 12 ‘Annual Salary’FROM emp;

b. SELECT salary * 12 ‘annual FROM emp;

c. SELECT annual sal * 12 FROM emp;

d. SELECT sal * 12 FROM emp;

19.下面哪一个查询将显示存储在EMP表中的所有数据? A

a. SELECT * FROM emp;

b. SELECT % FROM emp;

c. SELECT ^ FROM emp;

d. SELECT _ FROM emp;

20.在执行下面的查询时,下面哪能一种说法反映了将会发生的情况? B

SELECT ename fROM emp e,emp m WHERE e.mgr = m.empno;

a. 这个查询将产生一个自我联接,显示各个员工的经理的姓名

b. 将显示一个多义性错误,并且不会执行这个语句

c. 这个查询将执行一个完全外部联接,并显示没有为其指定经理的员工的姓名

d. 这个查询将执行一个右外部联接,显示不是经理的员工的姓名

21.下面哪些关键字可以用来创建一个不等连接?选出所有正确的选项. E

a. NATURAL JOIN

b. JOIN… USING

c. OUTER JOIN

d. JOIN… ON

e. 以上都不可以—-使用任何JOIN关键字都不能创建不等连接

22.下面哪一个查询只返回包含DEPT表中而没有在EMP表中列出的部门号? C

a. SELECT deptno FROM dept NATURAL JOIN emp WHERE deptno NOT IN emp;

b. SELECT deptno FROM dept MINUS deptno FROM emp;

c. SELECT deptno FROM dept MINUS SELECT deptno FROM emp;

d. SELECT deptno FROM dept JOIN emp ON dept.deptno<>emp.deptno;

e. SELECT deptno FROM emp MINUS SELECT deptno FROM dept;

23.下面哪一个查询将显示名为King的员工的工号? A

a. SELECT empno FROM emp WHERE ename = ‘% KING’;

b. SELECT empno FROM emp WHERE ename = ‘_ ING’;

c. SELECT empno FROM emp WHERE ename LIKE KING;

d. SELECT empno FROM emp WHERE ename = KING;

e. 以上查询都不对

24.下面哪一个查询将显示销售部在1981年聘用的所有员工? B

a. SELECT * FROM emp WHERE dname =’SALES’AND hiredate LIKE’%81′;

b. SELECT * FROM emp NATURAL JOIN dept WHERE dname =’SALES’AND hiredate LIKE’%81′;

c. SELECT * FROM emp WHERE dname =’SALES’OR hiredate LIKE’%81′;

d. SELECT * FROM emp NATURAL JOIN dept WHERE dname =’SALES’AND hiredate LIKE’%1981′;

25.下面哪一个查询将显示存储在EMP表中的各个员工的姓名和职务?选择所有正确的查询. C

a. SELECT ename,job AS”Job Title”FROM emp;

b. SELECT ename,job “Job Title”FROM emp;

c. SELECT ename,job FROM emp;

d. SELECT ename,job ‘Job TITLE’FROM emp;

e. SELECT ename,job ‘Job Title’FROM emp;

26.下面哪一种说法最准确地说明了下面的SELECT语句的结果? A

SELECT DISTINCT job,ename FROM emp;

a. 结果中返回的每一行都是惟一的

b. 各个职务在结果中只显示一次

c. 各个职务将只显示一次,并随之显示具有该职务的各个员工的姓名

d. 结果将按照员工姓名排序

27.下面哪一个子句用来选择表中的某些列? A

a. SELECT

b. FROM

c. WHERE

d. ORDER BY

28.下面哪一个查询将显示年薪至少为10000美元的所有员工的姓名? D

a. SELECT ename FROM emp WHERE sal*12>10,000;

b. SELECT ename FROM emp WHERE sal*12>’10,000′;

c. SELECT ename FROM emp WHERE sal*12=>10000;

d. SELECT ename FROM emp WHERE sal*12>=10000.00;

e. 上面的语句都不正确

29.下面哪些查询将按照员工姓名的顺序显示各个员工的号码?选出所有正确的查询. 无

a. SELECT ename,ename FROM emp ORDER BY empno;

b. SELECT ename,ename FROM emp ORDER BY ename;

c. SELECT ename,ename FROM emp ORDER BY 1;

d. SELECT ename,ename FROM emp ORDER BY 2;

e. SELECT ename,ename ORDER BY ename;

30.下面哪一个查询将显示月薪至少为1200美元但是少于2000美元的各个员工的姓名? D

a. SELECT ename FROM emp WHERE sal BETWEEN(1200,2000);

b. SELECT ename FROM emp WHERE sal BETWEEN 1200 and 2000;

c. SELECT ename FROM emp WHERE sal>=1200 AND <2000;

d. SELECT ename FROM emp WHERE sal>=1200 AND sal <2000;

e. SELECT ename FROM emp WHERE sal>1200 AND sal <2000;

31.下面哪一个子句用来限制查询返回的行? C

a. SELECT

b. FROM

c. WHERE

d. ORDER BY

32.下面哪一个运算符用来执行模式搜索? D

a. IN

b. BETWEEN

c. IS NULL

d. LIKE

33.下面哪些查询在结果中不包括部门30中的任何员工?选出所有正确的答案. B

a. SELECT * FROM emp WHERE deptno! =30;

b. SELECT * FROM emp WHERE deptno<>30;

c. SELECT * FROM emp WHERE deptno^30;

d. SELECT * FROM emp WHERE deptno =30;

34.下面哪一个查询将显示没有佣金的所有员工? B

a. SELECT ename FROM emp WHERE comm = NULL;

b. SELECT ename FROM emp WHERE comm IS NULL;

c. SELECT ename FROM emp WHERE comm LIKE NULL;

d. SELECT ename FROM emp WHERE comm LIKE ‘NULL’;

35.下面哪一个查询将返回在销售部或会计部工作并且月薪至少为2000美元的所有员工的姓名?选出所有正确的答案. A B C D

a. SELECT ename FROM emp NATURAL JOIN dept WHERE dname IN(’SALES’,'ACCOUNTING’) AND sal>=2000;

b. SELECT ename FROM emp  JOIN dept ON emp.deptno = dept.deptno WHERE sal >= 2000 AND dname = ‘SALES’ OR dname =’ACCOUNTING’;

c. SELECT ename FROM emp  JOIN dept USING(deptno) WHERE sal >= 2000 AND (dname = ‘SALES’OR dname =‘ACCOUNTING’);

d. SELECT ename FROM emp NATURAL JOIN dept WHERE sal >= 2000 AND (dname = ‘SALES’OR dname =‘ACCOUNTING’);

36.下面哪一个子句用来按照某种顺序显示查询的结果? D

a. SELECT

b. FROM

c. WHERE

d. ORDER BY

37.在PL/SQL块中,下面哪一部分是必需的? B

a. 声明

b. 可执行

c. 异常处理

d. 结束

38.下面哪一个参数”不”是过程接受的参数? A

a. INPUT

b. OUT

c. INOUT

d. IN

39.如果在PL/SQL块中使用NUMBER数据类型声明了一个变量,但是在创建时没有初始化它,那么将为这个变量指定什么值? C

a. 0

b. 1

c. NULL

d. 999

40.下面哪一种循环不需要用户执行OPEN命令来打开显式游标? D

a. 基本循环

b. WHILE循环

c. IF循环

d. 游标FOR循环

43.哪一个程序包可以用来显示PL/SQL块中的一个变量的内容? B

a. PUT_LINK

b .DBMS_OUTPUT.put_line

c. SERVEROUTPUT

d. DISPLAY_OUT

44.下面哪一个属性可以用来指定游标处理的行数? A

a. % ROWCOUNT

b. % ISOPEN

c. % FOUND

d. % NOTFOUND

45.下面哪一个属性可以用来为记录指定一行的结构? D

a. % FOUND

b. % TYPE

c. % STRUCTURE

d. % ROWTYPE

46.下面哪些属性可以用来控制循环的执行?选出所有正确的答案. C D

a. % ROWCOUNT

b. % ISOPEN

c. % FOOUND

d. %NOTFOUND

47.下面哪些项目可以用来在PL/SQL块中初始化一个变量?选出所有正确的答案. C

a. DEFAULT

b. =

c. : =

d. <>

50.下面哪一个符号可以用来在Oracle9i中执行一个脚本文件? C

a. %

b. $

c. @

d. !

完成下列操作，写出相应的SQL语句

51. 创建表空间neuspace，数据文件命名为neudata.dbf，存放在d:\data目录下，文件大小为200MB，设为自动增长，增量5MB，文件最大为500MB。(8分)

答：create tablespace neuspace datafile ‘d:\data\neudata.dbf’ size 200m autoextend on next 5m maxsize 500m;

52. 假设表空间neuspace已用尽500MB空间，现要求增加一个数据文件，存放在e:\appdata目录下，文件名为appneudata，大小为500MB，不自动增长。（5分）

答：alter tablespace neuspace add datafile ‘e:\appdata\appneudata.dbf’ size 500m;

53. 以系统管理员身份登录，创建账号tom，设置tom的默认表空间为neuspace。为tom分配connect和resource系统角色，获取基本的系统权限。然后为tom分配对用户scott的表emp的select权限和对SAL, MGR属性的update权限。(8分)

答：create user tom identified by jack default tablespace neuspace;

Grant connect, resource to tom;

Grant select, update(salary, mgr) on scott.emp to tom;

54. 按如下要求创建表class和student。（15分）

答：create table class

(classno number(2) constraint class_classno_pk primary key,

cname varchar2(10) not null);

create table student

(stuno number(8) constraint student_stuno_pk primary key,

sname varchar2(12) not null,

sex char(2) default ‘男’,

birthday date,

email varchar2(20) constraint student_email_uk uni

表结构说明：

create table myemp(

id number(10) not null,

salary number(10,2) default 0 not null,

name varchar2(24) not null

);

1.创建序列seq_employee，该序列每次取的时候它会自动增加，从1开始计数，不设最大值，并且一直累加，不循环。（10分）

Create sequence seq_employee increment by 1 start with 1 nomaxvalue nocycle

2.写一个PL/SQL块，插入表user.employee中100条数据。插入该表中字段id用序列seq_employee实现，薪水和姓名字段可以任意填写。（15分）

declare i number;

begin

for i in 1 .. 100

loop

insert into employee

values(seq_employee.nextval,1950+i,’王明’||to_char(i));

commit;

end loop;

end;

/

6.写一个语句块，在语句块中定义一个显式游标，按id升序排列，打印表employee中前十条数据。（15分）

declare

cursor c is select id,salary,name from(select * from employee order by id) where rownum<11;

v_record c%rowtype;

begin

open c;

loop

fetch c into v_record;

exit when c%notfound;

dbms_output.put_line(to_char(v_record.id)||’,'||to_char(v_record.salary)||’,'||v_record.name);

end loop;

close c;

end;

/

7.创建存储过程p_employee，输入员工薪水范围，返回员工工号、姓名、薪水结果集，结果集按员工薪水升序排列。（15分）

create or replace procedure p_employee

(iminsalary in number,

imaxsalary in number)

is

begin

for x in(select id,salary,name from(select * from employee where salary between iminsalary and imaxsalary) order by salary)

loop

dbms_output.put_line(to_char(x.id)||to_char(x.salary)||x.name);

end loop;

end;

/

8.创建函数f_employee实现更新员工薪水的功能，将薪水低于2000且姓wang的员工薪水加5%，其他不变，更新成功则返回0，否则返回1。（15分）

答: create or replace function f_employee

Return number

is

begin

update employee set salary=salary+salary*0.05 where salary<2000 and name like ‘王%’;

commit;

if SQL%rowcount=0 then

return 1;

else

return 0;

end if;

end;

/

问答题:

1、写分页有哪些方法，你一般用什么方法？用SQL语句写一个分页？

如何用存储过程写分页？

select * from (select n.*,row_number() over(order by columnname) num from tablename n) where num>=10 and num <=20;

使用过程时，只要将分页的范围用两个参数就可以实现。在ORACLE中，要将过程封装在包里，还要用动态游标变量才能实现数据集的返回。

2、什么时候会用到触发器

答: A安全管理、B日志管理、C复杂业务逻辑实现

3、如何在数据库中显示树控制？

答: 用父ID与子ID来实现

4、如何实现数据库的优化？

答: A、调整数据结构的设计。这一部分在开发信息系统之前完成，程序员需要考虑是否使用ORACLE数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。

B、调整应用程序结构设计。这一部分也是在开发信息系统之前完成，程序员在这一步需要考虑应用程序使用什么样的体系结构，是使用传统的Client/Server两层体系结构，还是使用Browser/Web/Database的三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。

C、调整数据库SQL语句。应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了ORACLE数据库的性能。ORACLE公司推荐使用ORACLE语句优化器（Oracle Optimizer）和行锁管理器（row-level manager）来调整优化SQL语句。

D、调整服务器内存分配。内存分配是在信息系统运行过程中优化配置的，数据库管理员可以根据数据库运行状况调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小；还可以调整程序全局区（PGA区）的大小。需要注意的是，SGA区不是越大越好，SGA区过大会占用操作系统使用的内存而引起虚拟内存的页面交换，这样反而会降低系统。

E、调整硬盘I/O，这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。 SCISC

F、调整操作系统参数，例如：运行在UNIX操作系统上的ORACLE数据库，可以调整UNIX数据缓冲池的大小，每个进程所能使用的内存大小等参数。

本文来源：https://www.2haoxitong.net/k/doc/30a9fd38580216fc700afd2e.html