Oracle数据库开发技术——sql查询题+PLSQL编程+游标、函数和存储过程——试题+答案解析

一、sql查询题：

1. 显示姓名字段的任何位置包含’A’的所有员工的姓名

select ename from emp where ename like '%A%';

2. 显示所有员工姓名的前三个字符

select substr(ename,1,3) from emp;

3. 显示员工‘FORD’的上级

select ename from emp where empno=(select mgr from emp where ename='FORD');

4. 找出在任意年份2月受聘的所有员工

select ename from emp where extract(month from hiredate)=2;

select ename from emp where to_char(hiredate,'mm')=2;

5. 列出员工表中每个部门的员工数（员工数必须大于 3），和部门名称

select dname,count(*) from emp e join dept d on e.deptno=d.deptno group by e.deptno having count(*)>3; 

6. 以职位分组，找出平均工资最高的两种职位

select rownum,e.* from (select job,avg(sal) from emp group by job order by avg(sal) desc) e where rownum<=2;

7. 查找出不在部门 20，且比部门 20 中任何一个人工资都高的员工姓名、部门名称

select ename,dname from emp e join dept d on e.deptno=d.deptno and e.deptno<>20 and sal>all(select sal from emp where deptno=20); 

8. 得到平均工资大于 2000 的工作职种

select job from emp group by job having avg(sal)>2000;

9. 查找出职位和’MARTIN’ 或者’SMITH’一样的员工的平均工资

select avg(sal) from emp where job in (select job from emp where ename in ('MARTIN', 'SMITH'));

10. 查询出 king 所在部门的部门号\部门名称\部门人数

select deptno,dname,count(*) from emp e join dept d on e.deptno=d.deptno and e.deptno=(select deptno from emp where ename=’king’) group by e.deptno,dname;

二、PLSQL编程

1、输入一个雇员编号，根据它所在的部门涨工资，规则：

• 10 部门上涨 10%

• 20 部门上涨 20%

• 30 部门上涨 30%

所有部门的上涨工资，最高不能超过 5000，如果超过 5000，则工资就为 5000。

set serveroutput on
declare
	v_deptno emp.deptno%type; 
	v_sal emp.sal%type;
	v_empno emp.empno%type := &a;
begin
	select deptno into v_deptno from emp where empno = v_empno;
	case v_deptno
		when 10 then
		update emp set sal=sal*1.1 where deptno = v_deptno;
		when 20 then
		update emp set sal=sal*1.2 where deptno = v_deptno;
		when 30 then
		update emp set sal=sal*1.3 where deptno = v_deptno;
	end case;
	update emp set sal=5000 where deptno=v_deptno and sal>5000;
end;

2、用 loop 循环结构，为 dept 表增加 50-90 这些部门

declare
	i number(3) :=50;
begin
loop
	insert into dept(deptno) values(i);
	i = i + 10;
exit when i>90;
end loop;
end;

3. 接收一个雇员名，显示该雇员的所有内容， （用%rowtype 实现），当没有这个雇员时（no_data_found），用异常来显示错误提示

declare
	v_ename emp.ename%type := '&a';
	v_mess emp%rowtype;
begin 
	select * into v_mess from emp where ename = upper(v_ename);
	dbms_output.put_line('雇员姓名：' || v_mess.ename || '雇员编号：' || v_mess.empno);
	exception
	when no_data_found then
	dbms_output.put_line('没有这个员工');
end;

三、游标、函数和存储过程

1. 使用游标 和 loop 循环来显示所有部门的名称

declare 
cursor show is select dname from dept;
	v_name dept.dname%type;
begin
	open show;
loop
	fetch show into v_name;
	exit when show%not found;
	dbms_output.put_line(v_name);
end loop;
close show;
end;

2. 写一个函数 输入一个员工名字，判断该名字在员工表中是否存在。存在返回 1，不存在返回 0

create or replace function if_emp (name emp.ename%type)
return number
is
	num number;
begin
	select count(*) into num from emp where ename=upper(name);
if num=0 then
		return 0;
	else
		return 1;
	end if;
end;

set serveroutput on
v_name emp.ename%type := '&a';
begin
	dbms_output.put_line(if_emp(v_name));
end;

3. 编写一个过程，要求，可以传入部门的编号，部门的名称，之后调用此过程就可以完成部门的增加操作

create or replace procedure add(dno dept.deptno%type,dnam dept.dname%type)
is 
begin
	insert into dept(deptno,dname) values(dno,dnam);
end;

declare
	v_deptno dept.deptno%type;
	v_dname dept.dept.dname%type;
begin
	add(v_deptno,v_dname);
end;

select * from dept;