Orcale实训报告

一、sql查询题

1.显示姓名字段的任何位置包含’A’的所有员工的姓名
2.显示所有员工姓名的前三个字符
3.显示员工‘FORD’的上级
4.找出在任意年份2月受聘的所有员工
5.列出员工表中每个部门的员工数(员工数必须大于3)，和部门名称
6.以职位分组，找出平均工资最高的两种职位
7.查找出不在部门 20，且比部门20中任何一个人工资都高的员工姓名﹑部门名称
8.得到平均工资大于2000的工作职种
9.查找出职位和’MARTIN’或者’SMITH’一样的员工的平均工资10.杳询出king所在部门的部门号\部门名称\部门人数

--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 * from emp where to_char(hiredate,'mm')=2;
--5.列出员工表中每个部门的员工数(员工数必须大于3)，和部门名称
select emp.deptno,dname,count(empno) from emp,dept where emp.deptno=dept.deptno group by emp.deptno,dname having count(empno)>3;
--6.以职位分组，找出平均工资最高的两种职位
select *from (select job,avg(sal) from emp group by job order by avg(sal) desc) where rownum<=2;
--7．查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名﹑部门名称
select ename,dname from emp,dept where emp.deptno=dept.deptno and sal>all(select sal from emp where deptno=20) and emp.deptno!=20;
--8.得到平均工资大于2000的工作职种
select job,avg(sal) 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='MARTIN' or ename='SMITH');
--10．查询出 king所在部门的部门号\部门名称\部门人数
select emp.deptno,dname,count(emp.empno) from emp,dept where emp.deptno=dept.deptno and emp.deptno=(select deptno from emp where ename='KING') group by emp.deptno,dname;

二、pl/sql编程

1.输入一个雇员编号，根据它所在的部门涨工资，规则:
10部门上涨10%
20部门上涨20%.30部门上涨30%
所有部门的上涨工资，最高不能超过5000，如果超过5000，则工资就为5000

declare
  v_empno emp.empno%type:=&no;
  v_deptno emp.deptno%type;
  v_sal emp.sal%type;
begin
  select deptno into v_deptno from emp where empno=v_empno;
  select sal into v_sal from emp where empno=v_empno;
  
  if(v_deptno=10) then
    update emp set sal=sal+sal*0.1;
  elsif(v_deptno=20) then
    update emp set sal=sal+sal*0.2;
  elsif(v_deptno=30) then
    update emp set sal=sal+sal*0.3;
    end if;
    
    if(v_sal>500) then update emp set sal=5000;
    end if;
    dbms_output.put_line(v_sal);
end;
/
set serveroutput on
/

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

declare
  v_deptno number:=50;
begin
  while v_deptno<100
  loop
    insert into dept2(deptno) values(v_deptno);
    v_deptno:=v_deptno+10;
  end loop;
end;
/

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

declare
  v_ename emp.ename%type:='&n';
  e emp%rowtype;
begin
  select * into e from emp where ename=v_ename;
    dbms_output.put_line('姓名'||e.ename||'编号'||e.empno||'工资'||e.sal||'职位'||e.job);
  exception when no_data_found then dbms_output.put_line('不存在该雇员');
end;
/

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

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

declare
  cursor aaa is select dname from dept;
  v_dname dept.dname%type;
begin
  open aaa;
  loop
    fetch aaa into v_dname;
    exit when aaa%notfound;
      dbms_output.put_line(v_dname);
  end loop;
  close aaa;
end;
/

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

create or replace function bbb (v_ename emp.ename%type) return number
is
  figure number;
  n number;
begin
  select count(*) into figure from emp where ename=v_ename;
  if figure<>0 then n:=1;
  elsif figure=0 then n:=0;
  end if;
  return n;
end;
 
declare 
  n number;
begin
  n:=bbb('king');
  dbms_output.put_line(n);
end;

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

create or replace procedure ccc(v_deptno dept.deptno%type,v_dname dept.dname%type)
is
begin
  insert into dept(deptno,dname) values(v_deptno,v_dname);
end;

execute ccc(99,'sss');