Oracle面试题

1. 简述Oracle数据库中实例和数据库的概念以及它们之间的关系。

实例是一个非固定的，基于内存的基本进程与内存结构。当服务器关闭后，实例也就不存在了，数据库指的是固定的，基于磁盘的数据文件和控制文件及日志文件、参数文件、归档日志文件等。一般情况下按装的oracle数据库都是一个数据库中 包含一个实例。当然也可以在一个数据库创建多个实例

2. 描述你所了解的数据库对象。

Table（表）用于存放需要化持久的记录，function（函数）用pl/sql语言编写的能完成特定功能的程序块，并能把处理结果返回。Procedure（存储过程）与函数类似，只是不能有返回值。View（视图），tigger（触发器）。

3. 写出你所知道的所有Oracle中的函数。并描述其参数含义。

Sum(col) col为列的名字，substr（col，l1，l2）col列的名字，l1截取字符串的开始位置，

L2截取字符串的结束位置。。

4. 如果Oracle数据库安装在c:\oracle目录下，请写出创建表空间bluedot的命令，并将bluedot表空间所对应的数据文件指定到oracle下默认的数据文件所在目录中。数据文件的名字为bluedot.dbf

create tablespace bluedot datafile ‘c:\oralce\oradata\XE\bluedot.dbf’ size 5m autoextend on next 3m;

5. 现要完成一个学生选课系统，学生可以选择多门课程，每门课程可以被多个学生选择。请根据这个需求画出概念模型，描述实体之间的关系。

6. 请根据上面的概念模型，创建关系模型（数据库表），写出创建表的SQL语句（学生表的名字为S、课程表的名字为C、选课表的名字为SC）。假如，学生具有学号和名字两个属性。课程具有课程号、课程名和学分三个属性。请合理的选择表内的主键和外键，以及是否为空的属性。要求创建一个用户自定义的完整性，就是课程的成绩必须在0到100之间。

create table S(s_id number(10),s_name char(10),entrance_time date, primary

key(s_id));

 create table C(c_id number(10),c_name varchar2(40) not null , primary key(c_id));

create table SC(s_id number(10),c_id number(10),result number(4,1) check(result<=100 and result>=0),

primary key(s_id,c_id));

alter table SC
  add constraint sfk foreign key (S_ID)
  references s (S_ID) on delete cascade;

alter table SC add constraint cfk foreign key(c_id)

references c(c_id) on delete cascade;

7. 根据以上的表结构完成如下的SQL语句：

a) 列出每个学生的姓名、所选的课程和成绩。

select s.s_name,c.c_name ,sc.result from s,sc,c where s.s_id=sc.s_id and sc.c_id=c.c_id;

b) 列出每个学生的学号以及所选课程的总成绩。

select s_id ,sum(result) from sc group by s_id;

c) 查询所有选修了Java课程的学生姓名和成绩，并按成绩降序排列结果。

SQL> select s_name,c_name,result from s,sc,c where s.s_id=sc.s_id and sc.c_id=c.c_id and c.c_name='java';

d) 请写出学生表S和成绩表SC表的左连接、右连接和全链接的SQL语句。并写出查询出来的数据示例数据。

select * from s left join sc on s.s_id=sc.s_id;

select * from s right join sc on s.s_id=sc.s_id;

select * from s full join sc on s.s_id=sc.s_id;

8. 如果在学生表中添加一个学生所在系的字段，请创建一个视图用来查询计算机学生的学号、姓名、所选课程以及成绩。

alter table S add department varchar2(30) default 'computer';

select s.s_id, s.s_name,c.c_name,result from s,sc,c where s.s_id=sc.s_id
 and sc.c_id=c.c_id and s.department='computer';

9. 如果在学生表中含有一个Date类型的字段表示学生的入学时间，请编写一个函数GET_STU_DATE，入参为学生的学号，返回该学生入学后3个月后的第一个星期一的日期，并以字符型返回，要求返回的字符型日期格式为1998-12-21 18:56:32 AM。

核心代码：

TO_CHAR(TO_DATE(next_day(trunc(add_months(ENTRANCE_TIME,3),'month'),2)

),'YYYY-MM-DD HH24:MI:SS AM') 

10. 请编写一个存储过程完成学生转学的功能。要求存储过程的入参为学生的学号和out类型的出参。out类型的出参为400表示转学成功，为500表示存储过程执行有异常，如果为300表示给定学号的学生不存在。

create or replace procedure changeschool(id in number  ,rs  out number)
is 
cursor sc is select * from s where s.s_id=id;

srecord s%rowtype;
begin
  open sc;
  fetch sc into srecord;
  if (sc%found) then
   insert into ss(s_id,s_name,entrance_time,department)
    values(srecord.s_id,srecord.s_name,srecord.entrance_time,srecord.department);
   delete from s where s.s_id=id;
   close sc;
   commit;
   rs:=400;
  else 
    rs:=300;
  end if;
 exception when others then
rollback;
 rs:=500;
end changeschool;

11. 请创建一个序列用来生成学生的学号。学号要求在400-999999之间，序列不需要缓存不需要循环利用。

create sequence S_SEQ
minvalue 400
maxvalue 99999
start with 400
increment by 1
nocache;

12. 为了增加安全性，当有数据库用户修改学生成绩的时候，需要在日志表SC_LOG中记录用户的名字，修改时间以及所修改的学生的学号以及修改之前的学生的成绩。请为这个需求编写一个触发器完成这个功能。

Create table sc_log (id number(20),s_id number(10),oldresult number(4,1),createtime 

Date default sysdate);

create sequence sclog_seq start with 1;

create or replace trigger stigger
  before update on sc  
  for each row
declare
  
begin
  if updating then
  insert into sc_log(id,s_id,oldresult) values(sclog_seq.nextval,:old.s_id,:old.result);
  end if;
end stigger;