Oracle面试题

20210713:编写一个 SQL 查询，查找所有至少连续出现三次的数字。

Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+

Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 是唯一连续出现至少三次的数字。

解答思路：巧用递增的ID-连续出现数字的row_number的差值是固定的这一特性

select distinct num as "ConsecutiveNums"
from (select num,id - row_number() over(partition by num order by id) as rn
from logs)
group by num,rn
having count(rn) >= 3;

 

1.rowid、rownum的定义

答：rowid是物理地址，是用于定位数据表中数据存储的位置，唯一的，不会改变

rownum是SQL输出结果的排序，同一条记录不同查询条件对应的rownum也会不同

 

2.Oracle中function何procedure的区别？

答：function是函数，procedure是存储过程，函数有1个返回值，存储过程有多个或者没有；函数可以在查询语句中调用，而存储过程只能单独调用；

 

3.Oracle的导入导出有几种方式，有何区别？

1）dmp文件方式
描述：dmp文件是二进制的，可以跨平台，并且包含权限，支持大字段数据，是用的最广泛的一种。

导出语法：exp 用户名/密码@监听器路径/数据库实例名称 file=e:数据库文件.dmp full=y ignore=y ;其中full = y ,表示整个数据库操作; ignore=y,忽略错误，继续操作；
重点内容
导出举例：exp jojo/jojo@localhost/my_database file=e:my_database.dmp full=y ignore=y

导入语法：imp 用户名/密码@监听器路径/数据库实例名称 file=e:数据库文件.dmp full=y ignore=y ;

导入举例：imp jojo/jojo@localhost/my_database file=e:my_database.dmp full=y ignore=y

2）sql文件方式
SQL文件可用文本编辑器查看，有利于可读性，但效率不如dmp文件，适合小数据量导入导出。尤其注意的是表中不能有大字段（blob,clob,long），如果有，会提示不能导出(提示如下： table contains one or more LONG columns cannot export in sql format,user Pl/sql developer format instead)

3）pde文件
第三种是导出为pde格式，pde格式是PL/SQL 自带的文件格式，且且适用于PL/SQL工具，编辑器无法查看，一般不常用。

 

4.Oracle数据库sql查询把数据为空的项显示为0，用sql查询表示？

答：select decode(name,NULL,0,name) from table;

 

5.truncate和delete命令的异同？

答：truncate是DDL，隐形提交，不能回滚，delete是DML，每次删除都有记录；

执行速度上truncate>delete；

truncate是通过释放数据页来达到删除效果，所以日志文件只有一条删除记录，delete每删除一条就有一条记录；

 

6.数据库和数据仓库有什么区别？

答：数据库是面向事务，而数据仓库是面向主题的；数据库的设计是尽量避免冗余，因此符合范式，数据仓库反过来是尽可能引入冗余，利用反范式来设计；另外数据库面向的都是一些在线数据，数据仓库都是历史数据。还有之所以说数据仓库是数据库的升级优化版，是因为数据库是用来存放捕获数据，数据仓库在次基础上对数据进行分析。

 

7.Oracle中in与exists的区别？

答：exists返回的是一个布尔值，ture或false，in返回的是一个数据组吧。从查询效率角度分析，当外循环（主循环）返回的结果集大于子循环返回的结果值时，用in，反之用exists。

 

8.char()和varchar()有什么区别？

答：1.char的长度是不可变的，而varchar的长度是可变的；

       2.定义一个char[10]和varchar[10],如果存进去的是‘abcd’,那么char所占的长度依然为10，除了字符‘abcd’外，后面跟六个空格，而varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的

       3.char的存取数度还是要比varchar要快得多

       4.char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。

 

选择适当的数据结构建表（省市县三级关系）

create table china
(
c_id varchar(7) NOT NULL comment '地区编号'，
c_name varchar(50) NOT NULL comment '地区名称',
c_fid varchar(7) default -1 comment '父级id',
c_jibie char(5) NOT NULL comment '省=1，市=2，县=3',
primary key (c_id)
)

 

 

查询成绩表score中每科成绩前三名

select a.* from score a left join score b on a.c_id=b.c_id 
and a.s_score<b.s_score
group by a.s_id,a.c_id 
having count(b.s_id)<3 
order by a.c_id,a.s_score desc;

 

写一个存储过程，实现输入数字n，输出1到n的和

create procedure p(in n int,out result int)
begin
declace i int default 1;
declace sum int default 0;
while i<n
set sum=sum+i;
set i=i+1;     //如果奇数相加则i=i+2
end while
set result=sum;
end

 

用PL/SQL块实现在一个表中插入1000条数据,要求每100条提交一次

delimiter ||
create procedure p(in n int)
begin 
declare i int default 0; 
set i=0; 
lp:loop insert into kkk values(i+1); 
set i=i+1; 
if mod(i,10)=0 then 
commit; 
end if; 
if i=n then 
leave lp; 
end if; 
end loop; 
end||

 

有3个表 A（userid（用户ID），time（时间），fee（话费）），B（userid（用户ID），time（时间）），  C（userid（用户ID），fee（话费））各有1000万的数据， 3个表的userid是相同的，要求用B表的time字段，C表的fee字段更新A表的相应字段，用存储过程实现。

create procedure p1
cursor abc_cur is select b.userid,b.time,c.fee 
from b join c on b.userid=c.userid;
begin
for i in abc_cur loop
update a set a.time=i.time,a.fee=c.fee 
where a.userid=i.userid;
end loop;
end ||

 

有上百万数据用户表，删除其中的重复数据，保留最小id号

delete from kxf  k
where k.name in(select name from kxf group by name having count(*) >1) and
k.id not in (select min(id) from kxf froup by name);

 

用户资料表：serv( serv_id number(10), pro_id number(10), user_type varchar2(30), terminal_name varchar2(30) )

其中，serv_id 为用户标识，是serv表的主键，prod_id为产品标识；user_type为用户类型；terminal_name为终端类型

终端类型临时表：terminal(serv_id number(10),terminal_name varchar2(30))

初始化的情况下，serv表的serv_id,prod_id,user_type字段是已知的，terminal_name字段是空的

要求：根据prod_id,user_type字段的值来更新terminal_name字段

更新条件为：

当条件满足“prod_id = 1 and user_type='A'“时，terminal_name更新为‘固话’

当条件满足”prod_id = 1 and user_type='B'“时，terminal_name更新为‘小灵通’

当条件满足”prod_id = 2“时，terminal_name更新为‘宽带’

当条件满足”user_type=‘C’“时，terminal_name更新为‘CDMA’

以上条件均不满足时，terminal_name更新为 -1；

create procedure p 
is 
begin
update serv s
set s.terminal_name = case when prod_id=1 and user_type='A' then 固话
                                           when prod_id=1 and user_type='B' then 小灵通
                                           when prod_id=2 then 宽带
                                           when user_type='C' then CDMA
                                    else '-1' 
                                    end;
update terminal a
set a.terminal_name = (select t.terminal_name 
                              from serv t
                             where t.serv_id = a.serv_id)
where exists (select *
                   from serv t
                   where t.serv_id = s.serv_id);
end;