Oracle操作实例-Oracle面试常见问题-oracle游标，异常，存储过程用法

当前位置: 数据库>oracle

本页文章导读:

▪Oracle操作实例：所有部门。select dept.deptno,dept.dnamefrom dept,empwhere dept.deptno=emp.deptno2．列出薪金比“SMITH”多的所有员工。select * from empwhere sal >(select sal from emp where ename=’SMITH’)3．列出所有员.........

▪Oracle面试常见问题什么是数据库监听?答：数据库监听是数据库启动后台进程的一部分，是数据库正常工作的必要条件，类似人的五脏六腑；数据库的启动顺序?答：创建数据库、启动数据库实例、装载数据库数.........

▪oracle游标，异常，存储过程用法在项目里写了个小的存数过程，功能是给一些特定人员类型补录对应档案流水号的。总结下：　　开头：create or replace procedure insert_serial as　　as后边接申请的变量：　　　.........

[1]Oracle操作实例

来源: 发布时间: 2013-10-29

：所有部门。
select dept.deptno,dept.dname
from dept,emp
where dept.deptno=emp.deptno
2．列出薪金比“SMITH”多的所有员工。
select * from emp
where sal >(select sal from emp where ename=’SMITH’)
3．列出所有员工的姓名及其直接上级的姓名。
select yg.ename,sj.ename
from emp yg,emp sj
where yg.mgr=sj.empno

4．列出受雇日期早于其直接上级的所有员工。
select yg.ename,sj.ename
from emp yg join emp sj on yg.mgr=sj.empno
where yg.hiredate
5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select dept.deptno,dept.dname,emp.empno,emp.ename
from dept left join emp
on dept.deptno=emp.deptno
6．列出所有“CLERK”（办事员）的姓名及其部门名称。
select * from dept,emp
where dept.deptno=emp.deptno
and job=’CLERK’
7．列出最低薪金大于1500的各种工作。
select job from emp
group by job
having min(sal)>1500

8．列出在部门名称为“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
方法一：
select emp.ename
from dept,emp
where dept.deptno=emp.deptno
and dname=’SALES’
方法二：
select * from emp
where deptno=(select deptno from dept where dname=’SALES’)
9．列出薪金高于公司平均薪金的所有员工。
select * from emp
where sal> (select avg(sal) from emp)
对比：列出薪金高于本部门平均薪金的所有员工。
方法一：
select * from emp a
where a.sal>(select avg(sal) from emp b where b.deptno=a.deptno)
缺点：相关子查询，效率低。
方法二：
select empno,ename,sal from
emp a,(select avg(sal) avg_sal from emp b where b.deptno=a.deptno) b
where a.deptno=b.deptno
and a.sal>b.avg_sal
10．列出与“SCOTT”从事相同工作的所有员工。
select * from emp
where job=(select job from emp where ename=’SCOTT’)
11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename,sal from emp
where sal=any(select sal from emp where deptno=30 )

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
方法一：
select ename,sal from emp
where sal>all(select sal from emp where deptno=30 )
方法二：
select ename,sal from emp
where sal>(select max(sal) from emp where deptno=30 )
13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
select deptno,avg(trunc((sysdate-hiredate)/365)) as year
from emp
group by deptno
–参考：截断，取整函数
select trunc(99.9) from dual;
返回
99
14．列出所有员工的姓名、部门名称和工资。
select d.dname,e.ename,e.sal
from dept d,emp e
where d.deptno=e.deptno
说明：每个字段都加表前缀，效率要高些
15．列出所有部门的详细信息和部门人数。
select dept.deptno,dept.dname,count(*)
from dept,emp
where dept.deptno=emp.deptno
group by dept.deptno,dept.dname

16．列出各种工作的最低工资。
select job,min(sal) from emp group by job
17．列出各个部门的MANAGER（经理）的最低薪金。
select deptno,min(sal) from emp where job=’MANAGER’ group by deptno

18．列出所有员工的年工资,按年薪从低到高排序。
select empno,ename,sal*12 as 年薪 from emp
order by sal
19. 求各种工作工资最低的员工。
方法一：
select * from emp a
where sal=(select min(sal) from emp b where b.job=a.job)
方法二：
select emp.*
from emp a,( select job,min(sal) min_sal from emp group by job) b
where a.job=b.job and a.sal=b.min_sal
20. 列出各种工作工资前3名的员工
select * from (
select empno,ename,sal,job,
dense_rank() over(partition by job order by sal desc) as 名次
from emp ) a
where a.名次<=2
order by job;
说明：用到了Oracle强大的“分区排名技术”，其中“dense_rank()”是Oracle的解析函数。

本文链接

[2]Oracle面试常见问题

来源: 发布时间: 2013-10-29

什么是数据库监听?

答：数据库监听是数据库启动后台进程的一部分，是数据库正常工作的必要条件，类似人的五脏六腑；

数据库的启动顺序?

答：创建数据库、启动数据库实例、装载数据库

数据库服务器端如何配置?

答：在数据库的安装目录（E:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN）下的tnsnames.ora文件修改HOST（服务器端的IP地址）与PORT（对应端口）部分

两表不能更新也不能修改，如何定位?

答：1、重启数据库，2、表与表可能被锁定，要解锁；3、重启数据库各个服务

什么是端口?

答：端口指的是网络与网络之间通信并开放的唯一口子

数据库启动时后台进程有哪些?

答：service服务进程、监听进程

什么是数据库实例?有哪些部分组成?

答：数据库实例就是后台进程和数据库文件的集合

你曾经用过的是数据库哪个版本?

答：oracle11G版本的

数据库启动不了，有哪些原因导致?

答：网络原因，安装原因

数据库客户端与服务器端连接不上，为什么?说明三点原因

答：1、服务端文件配置错误；2、监听服务没有启动；3、用户名与密码错误

http://www.jobui.com/mianshiti/it/oracle/?n=3

本文链接

[3]oracle游标，异常，存储过程用法

来源: 发布时间: 2013-10-29

在项目里写了个小的存数过程，功能是给一些特定人员类型补录对应档案流水号的。总结下：

　　开头：create or replace procedure insert_serial as

　　as后边接申请的变量：　　

　　perNum_str person_info.person_no%type; 表示声明的变量 perNum_str 的类型和 person_info.person_no一样

　　cursor cur_perInfo is               　　　　　　　　表示定义一个游标cursor
    　　select person_no, person_type,hj_area_id   表示游标对应这个查询结果集
      　　from person_info;

create or replace procedure insert_serial as
  perNum_str   person_info.person_no%type;
  perType_str  person_info.person_type%type;
  perOrgan_str person_info.hj_area_id%type;
  licNum_num licence_serial_no.cur_num%type;
  licType_str licence_serial_no.licence_type%type;
  cursor cur_perInfo is
    select person_no, person_type,hj_area_id
      from person_info;

　　开始遍历结果集合：

　　首先要声明打开游标，读数据要用fetch语句完成,依次把每行结果集放入到已经声明的变量里。

　　如果显式的打开游标，一定要加上 %found 属性判断，否则当游标到最后一行时候，就会无限循环取出最后一行结果集，不会自动推出loop循环

　　最后end loop 循环，提交事务commit，关闭游标

　　也可以用 for..in..:

　　　　用for....in... 语句遍历结果集则不需要声明打开游标。（变量v后边直接接字段名，就可以取出结果）

  /*begin
  for v in cur_perInfo 
  loop
      dbms_output.put_line(v.person_no);
  end loop;*/

　对游标取出的结果集合进行处理：

　　如果游标没有有效数据了，跳转到 exit 推出循环。

　　select .... into ... from 隐式游标，表示把查询出来的结果放入到对应的变量里。这种写法每次只能查出一行数据，如果结果有多行(too_many_rows)或者没有数据查出（no_data_found），程序会抛出异常，中断循环。

　　所以添加了异常处理 begin....exception (when then end;):

　　使用了 goto 跳转，当出现异常了，跳到<<ponit1>>,然后继续循环。

　　在oracle中 “ :=“ 表示赋值，“=”表示判断等价。 “||” 表示字符串拼接

使用to_char（）对数字格式化的时候，会自动把转换格式后的字符最高位，来保存数字符号，所以当正数格式化的时候，最高位会多出个空格。可以“fm”来消除空格

 if cur_perInfo%found then
      begin
        select nvl(cur_num, -1), licence_type
          into licNum_num, licType_str
          from licence_serial_no
         where area_id = perType_str
           and cur_area_id = perOrgan_str;
      EXCEPTION
        WHEN NO_DATA_FOUND then
          goto point1;
        WHEN TOO_MANY_ROWS then
          goto point1;  
      end;
      if licNum_num > -1 then
        licNum_num := licNum_num + 1;
        update licence_serial_no
           set cur_num = licNum_num
         where area_id = perType_str
           and cur_area_id = perOrgan_str;
        update person_info
           set DOCUMENT_NO =(licType_str||to_char(licNum_num,'fm0999'))
         where person_no = perNum_str;
       
      end if;
      <<point1>>
      null;
    else
      exit;
    end if;

完整代码：

create or replace procedure insert_serial as
  perNum_str   person_info.person_no%type;
  perType_str  person_info.person_type%type;
  perOrgan_str person_info.hj_area_id%type;
  licNum_num licence_serial_no.cur_num%type;
  licType_str licence_serial_no.licence_type%type;
  cursor cur_perInfo is
    select person_no, person_type,hj_area_id
      from person_info;
  /*begin
  for v in cur_perInfo loop
      dbms_output.put_line(v.person_no);
  end loop;*/
begin
  open cur_perInfo;
  loop
    fetch cur_perInfo
      into