基于scott表的练习，多表查询，子查询，PL/SQL，存储过程。

// 1、查询emp表中的所有信息
select * from emp;

// 2、显示emp表的员工姓名和工资。
select ename, sal from emp;

// 3、查询emp表中部门编号为20的并且sal（工资）大于3000的所有员工信息
select * from emp where deptno=20 and sal > 3000;

// 4、查询emp表中部门编号为20的或者sal（工资）大于3000的所有员工信息
select * from emp where deptno=20 or sal > 3000;

// 5、使用between and 查询工资在2000和4000之间的员工（用and 重新实现）
select * from emp where sal between 2000 and 4000;
select * from emp where sal >= 2000 and sal <= 4000;

// 6、使用in 查询 部门编号10，20的所有员工
select * from emp where deptno in (10, 20);

// 7、使用like查询所有名字中包括 W的员工信息
select * from emp where ename like '%W%';

// 8、使用like查询所有员工名字中第二子字母为W的员工信息
select * from emp where ename like '_W%';

// 9、查询所有员工信息并按照部门编号和工资进行排序
select * from emp order by deptno, sal;
select deptno, sal from emp order by 1, 2;

// 10、显示员工工资上浮20%的结果。
select sal*1.2 from emp;

// 11、显示EMP表的员工姓名以及工资和奖金的和。
select ename, sal+NVL(comm, 0) from emp;  // 空值函数

// 12、显示DEPT表的内容，使用别名将表头转换成中文显示。 
select deptno 部门编号, dname 部门名称, loc 所在地 from dept;    

// 13、查询员工姓名和工资，并按工资从小到大排序。
select ename, sal from emp order by sal;

// 14、查询员工姓名和雇佣日期，并按雇佣日期排序，后雇佣的先显示。
select ename, to_char(hiredate, 'yyyy-mm-dd') from emp order by hiredate desc;
select ename, hiredate from emp order by hiredate desc;

// 15、查询员工信息，先按部门编号从小到大排序，再按雇佣时间的先后排序。
select * from emp order by deptno, hiredate;

// 16、按工资和入职月份的乘积排序（倒序）。
select * from emp order by extract(month from hiredate) * sal desc;

// 17、显示职务为“SALESMAN”的员工的姓名、职务和工资。
select ename, job, sal from emp where job='SALESMAN';

// 18、显示工资大于等于3000的员工姓名、职务和工资。
select ename, job, sal from emp where sal >= 3000;

// 19、显示1982年以后雇佣的员工姓名和雇佣时间。
select ename, to_char(hiredate, 'yyyy-mm-dd') from emp where extract(year from hiredate) > 1982;

// 20、显示部门编号为10的员工姓名和雇佣时间。
select ename, to_char(hiredate, 'yyyy-mm-dd') from emp where deptno = '10';

// 21、显示工资在1000～2000之间(不包括1000和2000)的员工信息。
select * from emp where sal > 1000 and sal < 2000;

// 22、显示部门10中工资大于1500的员工。
select * from emp where deptno=10 and sal > 1500;

// 23、显示职务为CLERK或MANAGER的员工信息。
select * from emp where job='CLERK' or job='MANAGER';

// 24、显示部门10以外的其他部门的员工。
select * from emp where deptno != 10;

25、显示部门10和部门20中工资小于1500的员工。
select * from emp where (deptno=10 or deptno=20) and sal < 1500;

PPT上已学查询：

1、查询姓名首字母为“A”或第二个字符为“A”的所有员工信息
select * from emp where ename like 'A%' or ename like '_A%';

2、查询部门20和30中的、岗位不是“CLERK”或“SALESMAN”的所有员工信息。
select * from emp where (deptno=20 or deptno=30) and job != 'CLERK' and job !='SALESMAN';

3、查询出工资在2500-3500之间，1981年入职的，没有奖金的所有员工信息。
select * from emp where sal between 2500 and 3500 and extract(year from hiredate)=1981 and comm is null;

4、查询比平均员工工资高的员工信息。
select * from emp where sal > (select avg(sal) from emp);
 
5、查询平均工资高于2000的部门信息。
select * from dept where deptno in (select deptno from emp group by deptno having avg(sal) > 2000);

6、查询出WARD的工作所在地。
select loc from dept where deptno = (select deptno from emp where ename='WARD');

7、查询出工资比ADAMS高的所有人姓名、部门、所在地。
select e.ename, d.dname, d.loc from emp e, dept d where e.sal >(select sal from emp where ename='ADAMS') and e.deptno=d.deptno;
select e.ename, d.dname, d.loc from emp e JOIN dept d ON e.deptno=d.deptno where e.sal > (select sal from emp where ename='ADAMS');

8、查询工资排名第7的员工信息。
select * from emp where sal = (select min(sal) from (select sal from (select * from emp order by sal desc) where rownum <=7 order by sal)); s
select * from (select * from emp order by sal desc) where rownum <=7
minus
select * from (select * from emp order by sal desc) e where rownum <=6;
select * from emp where sal = (select max(sal) from emp);

31、假定当前的系统日期是2013年11月13日，显示部门10员工的雇佣天数。
select ename, to_date('2013-11-13', 'yyyy-mm-dd')-hiredate 雇用天数 from emp where deptno=10;

32、显示员工姓名和雇佣的星期数
select ename, floor((sysdate-hiredate)/7) 雇佣星期数 from emp;

33、显示从本年1月1日开始到现在经过的天数。
select floor(sysdate-to_date('2019-01-01', 'yyyy-mm-dd')) from dual;

34、查询所有员工的奖金，如果奖金没有，用0来替代
select NVL(comm, 0) from emp;

select * from emp where empno between 7499 and 7902;
select * from emp where rownum <= 5;
// 查询员工姓名中第一个大写字母为 A 或者第二个大写字母为 A 的员工信息
select * from emp where ename like 'A%' or ename like '_A%'; 
select * from emp where substr(ename, 1, 1)='A' or substr(ename, 2, 1)='A';
select * from emp where instr(ename, 'A')='1' or instr(ename, 'A')='2';

// 查找部门 20 或者 30 岗位不是 clerk 或者 salesman 的员工
select * from emp where deptno in(20, 30) and job != 'CLERK' and job != 'SALESMAN';
select * from emp where deptno in(20, 30) and job not in ('CLERK', 'SALESMAN');

// 查询工资在 2500-3500 之间的，1981年入职的，没有奖金的员工信息
select * from emp where sal between 2500 and 3500 and to_char(hiredate, 'yyyy-mm-dd') like '1981%' and  comm is null;
select *
from (select * from emp where sal between 2500 and 3500 and comm is null)
where extract(year from hiredate) ='1981';

// 查询工资比平均工资高的员工信息
select * from emp where sal > (select avg(sal) from emp);

// 查询平均工资高于2000的部门信息
select dept.* from dept where deptno in (select deptno from emp group by deptno having avg(sal) > 2000);

// 查询出 WARD 所在部门的地址
select e.ename, d.loc from emp e, dept d where d.deptno = (select deptno from emp where ename='WARD') and ename='WARD';
select e.ename, d.loc from emp e JOIN dept d on e.deptno=d.deptno where e.ename='WARD';
select e.ename, d.loc from emp e, dept d where d.deptno = e.deptno and e.ename='WARD';

// 查询出工资比 ADAMS 高的所有人的姓名，部门，所在地
select e.ename, d.dname, d.loc from emp e, dept d where d.deptno=e.deptno and e.sal > (select sal from emp where ename='ADAMS') order by e.ename;
select e.ename, d.dname, d.loc from emp e join dept d on e.deptno = d.deptno where e.sal > (select sal from emp where ename='ADAMS') order by e.ename;

// 查询员工工资排名第一的员工信息
select * from emp where sal = (select max(sal) from emp);
select * from (select * from emp order by sal desc) where rownum=1;

// 查询当前日期
select sysdate, add_months(sysdate, 1) from dual; 

// 查询与部门20岗位不同的员工姓名和工资
select e.ename, e.job, e.sal from emp e where job not in(select e.job from emp e where deptno='20');

// 查询与 SMITH 岗位部门完全相同的员工姓名，工作，工资
select ename, job, sal from emp 
where job = (select job from emp where ename='SMITH') and deptno = (select deptno from emp where ename='SMITH') AND ename !='SMITH';

// 列出在部门sales工作的员工姓名
select ename from emp e, dept d where e.deptno=d.deptno and d.dname='SALES';

// 列出所有员工的姓名，部门和工资
select ename, e.deptno, sal from emp e, dept d where e.deptno =d.deptno;

// 列出所有部门详细信息和部门人数
select d.*, count(ename) from dept d, emp e where d.deptno=e.deptno(+) group by d.deptno, d.dname, d.loc;

// 列出各个部门为 MANAGER 的最低工资
select d.deptno, min(e.sal) from dept d, emp e where d.deptno=e.deptno(+) and e.job='MANAGER' group by d.deptno;

// 查询部门人数只少为1的部门信息
select d.*, count(e.ename) num from dept d, emp e where e.deptno(+)=d.deptno group by d.deptno, d.dname, d.loc having count(e.ename) >=1 order by num;

// 列出工资比 SMITH 多个员工
select e.ename, e.sal from emp e where e.sal > (select sal from emp where ename='SMITH');

// 列出所有员工对应领导的姓名
select e.ename 员工姓名, e1.ename BOSS, e1.sal from emp e, emp e1 where e.mgr = e1.empno(+);

// 列出某个员工的领导，并且领导的工资要大于等于3000
select e.ename, e1.ename BOSS, e1.sal from emp e, emp e1 where e.mgr=e1.empno(+) and e1.sal >= 3000;

// 列出部门名称和部门的员工信息
select d.dname, e.* from emp e, dept d where e.deptno(+)=d.deptno;

// 列出职位为 CLERK 的员工名称及部门名称，部门人数
select e.job, e.ename, d.deptno, d.dname, d.num from emp e, (select d.deptno, d.dname, count(e.ename) num from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname) d
where d.deptno(+) = e.deptno and e.job='CLERK';
select d.deptno, d.dname, count(e.ename) from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname;

// 列出工资高于公司平均工资的所有员工，所在部门，上级领导，公司的工资等级。
select d.dname, d.员工, s.grade, d.领导 from salgrade s, 
  (select d.dname, d.ename 员工, e.ename 领导, d.sal from emp e, 
          (select d.dname, s.* from dept d, (select * from emp where sal > (select avg(sal) from emp)) s where d.deptno=s.deptno) d 
  where d.mgr=e.empno(+)) d
where d.sal between s.losal and s.hisal;
 
select e.ename 员工, e1.ename BOSS, d.dname, s.grade
from emp e, emp e1, dept d, salgrade s
where e.sal > (select avg(sal) from emp) 
      and e.mgr = e1.empno(+)
      and e.deptno = d.deptno(+) 
      and e.sal between s.losal and s.hisal;

// 列出与scott从事相同工作的所有员工及部门名称
select e.ename, e.job, d.dname
from emp e, dept d
where e.deptno=d.deptno 
and e.job=(select job from emp where ename='SCOTT');

// 列出工资大于30号部门任意员工工资的所有员工的姓名和工资
select e.ename, e.sal 
from emp e
where e.sal > any(select sal from emp where deptno=30);

// 列出工资大于30号部门所有员工的所有员工信息和部门名称
select e.ename, e.sal, d.dname 
from emp e, dept d 
where e.sal > all(select sal from emp where deptno=30) 
and e.deptno=d.deptno;

// 列出每个部门的员工数量和平均工资
select d.dname, NVL(avg(e.sal), 0) 平均工资, count(e.ename) 员工数量 
from emp e, dept d 
where e.deptno(+)=d.deptno 
group by d.deptno, d.dname;

// 列出每个部门的员工数量，平均工资和平均服务期限
select d.dname, NVL(avg(e.sal), 0) 平均工资, count(e.ename) 员工数量, floor(avg(months_between(sysdate, e.hiredate))) 平均期限
from emp e, dept d 
where e.deptno(+)=d.deptno 
group by d.deptno, d.dname;

// 列出每个工作的最低工资和从事最低工资工作的雇员名称
select e.sal, e1.ename, e.job
from emp e1, (select min(e.sal) sal, e.job from emp e group by e.job) e
where e1.sal=e.sal and e1.job=e.job;

// 求出部门名称带'S'的部门名称，部门员工，工资合计和部门人数。
select e.ename, d.dname, d.sal, d.num 
from emp e, (select d.deptno, d.dname, sum(e.sal) sal, count(e.ename) num
    from emp e, dept d 
    where d.dname like '%S%' 
          and e.deptno(+)=d.deptno 
    group by d.deptno, d.dname) d
where e.deptno(+) = d.deptno;

// 求出部门平均工资以及等级
select d.deptno, d.dname, d.sal, NVL(s.grade, 0) 
from salgrade s, (select d.deptno, d.dname, NVL(avg(e.sal), 0) sal from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname) d 
where d.sal between s.losal and s.hisal;

// 求平均工资等级最低的部门名称
select d.dname, d.grade from
(select d.deptno, d.dname, d.sal, NVL(s.grade, 0) grade
from salgrade s, (select d.deptno, d.dname, NVL(avg(e.sal), 0) sal from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname) d 
where d.sal between s.losal and s.hisal) d,
      (select min(grade) grade
      from ((select d.deptno, d.dname, d.sal, NVL(s.grade, 0) grade
            from salgrade s, (select d.deptno, d.dname, NVL(avg(e.sal), 0) sal from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname) d 
            where d.sal between s.losal and s.hisal))) d1
where d.grade=d1.grade;

// 部门经理中平均工资最低的部门名称
select d.dname
from dept d,
      (select d.deptno
      from (select deptno, avg(sal) sal from emp where job='MANAGER' group by deptno) d,
           (select min(sal) sal from (select deptno, avg(sal) sal from emp where job='MANAGER' group by deptno)) d1 
      where d.sal=d1.sal) d1
where d.deptno=d1.deptno;
 

 

1,列出在部门sales工作的员工的姓名
select e.ename from emp e, dept d where e.deptno(+)=d.deptno and d.dname='SALES';

2,列出所有员工的姓名，部门名称和工资
select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno;

3,列出所有部门的详细信息和部门人数
select d.*, NVL(count(e.ename), 0) from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, d.dname, d.loc;

4,列出各个部门职位为manager的最低薪金
select e.deptno, min(e.sal) from emp e where e.job='MANAGER' group by e.deptno;

5,查询出部门人数至少是1的部门名字
select d.dname, d1.num from dept d, (select d.deptno, count(e.ename) num from dept d, emp e where d.deptno=e.deptno(+) group by d.deptno) d1 where d.deptno=d1.deptno and d1.num >= 1;

6,列出工资比smith多得员工
select e.ename from emp e where e.sal > (select sal from emp where ename='SMITH');

7,列出所有员工的对应领导的姓名
select e.ename, e1.ename from emp e, emp e1 where e.mgr=e1.empno(+);

8,求出某个员工的领导，并要求这些领导的薪水高于或等于3000
select e.ename, e.sal from emp e, (select distinct e1.empno as empno from emp e, emp e1 where e.mgr=e1.empno(+)) e1 where e.empno=e1.empno and e.sal >= 3000;

9.列出部门名称，和这些部门的员工信息
select d.dname, e.* from emp e, dept d where e.deptno(+)=d.deptno;

10.列出所有职位为clerk的员工姓名及其部门名称，部门的人数
select ename, deptno from emp where job='CLERK';
select d.dname, count(e.ename), e.ename from emp e, dept d where e.deptno(+)=d.deptno and e.job='CLERK' group by e.ename, d.dname;

11.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
select avg(sal) sal from emp;
select e.ename, e.sal, d.dname, e1.ename, s.grade from emp e, emp e1, dept d, salgrade s 
where e.sal > (select avg(sal) sal from emp) 
      and e.deptno=d.deptno 
      and e.mgr=e1.empno 
      and e.sal between s.losal and s.hisal;

12.列出与scott从事相同工作的所有员工及部门名称
select job from emp where ename='SCOTT';
select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno(+) and e.job=(select job from emp where ename='SCOTT');

13.列出薪金大于部门30中的任意员工的薪金的所有员工的姓名和薪金
select ename, sal from emp where sal > ANY(select sal from emp where deptno=30);

14.列出薪金大于部门30中的全部员工的薪金的所有员工的姓名和薪金，部门名称
select ename, sal, dname from emp e, dept d where e.deptno=d.deptno(+) and sal > ALL(select sal from emp where deptno=30);

15.列出每个部门的员工数量，平均工资
select dname, NVL(count(e.ename), 0), NVL(avg(e.sal), 0) from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, dname;

---------------------------------------------

1.查询每个部门的员工数量和部门编号
select NVL(count(e.ename), 0), d.deptno from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno;

2.查询每个部门的员工数量、平均工资和平均入职时间
select NVL(count(e.ename), 0), NVL(avg(e.sal), 0), NVL(avg(extract(year from sysdate) - extract(year from hiredate)), 0) 
from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno;

3.查询每个部门的员工数量以及该部门名称,将员工数大于2人的部门筛选出来
select d.dname, d.deptno, d.num from (select dname, d.deptno, NVL(count(ename), 0) num from emp e, dept d where e.deptno(+)=d.deptno group by dname, d.deptno) d where d.num > 2;

4.查询出没有员工的部门信息（部门编号、名称、所在地）
select d.* from (select d.*, NVL(count(e.ename), 0) num from emp e, dept d where e.deptno(+)=d.deptno group by d.dname, d.deptno, d.loc) d where d.num=0;

5.查询出SMITH所在部门的部门编号，部门名称，部门人数
select d.deptno, d.dname, NVL(count(e.ename), 0) from emp e, dept d where e.deptno=d.deptno and e.deptno=(select deptno from emp where ename='SMITH') group by d.deptno, d.dname;

6.查询出平均工资最高的两个岗位
select e.job, e.sal from (select job, NVL(avg(sal), 0) sal from emp group by job  order by sal desc) e where rownum <=2;

7.查询出工资总数最少部门的部门信息（部门编号、名称、所在地）
select d.* from (select d.*, NVL(sum(sal), 0) sal from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno, dname, loc order by sal) d where rownum<= 1;

8.查询出工资排名第七的员工姓名、工资、部门编号、所在地
(select d.* from (select e.ename, e.sal, d.deptno, d.loc from emp e, dept d where e.deptno=d.deptno order by sal desc) d where rownum <= 7)
minus
(select d.* from (select e.ename, e.sal, d.deptno, d.loc from emp e, dept d where e.deptno=d.deptno order by sal desc) d where rownum <= 6);

9.查询出工资最高的员工姓名、工资、部门名称、所在地
select empno from emp e, (select max(sal) sal from emp) e1 where e.sal=e1.sal;
select e.ename, e.sal, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno and e.empno=(select empno from emp e, (select max(sal) sal from emp) e1 where e.sal=e1.sal)

10.求出部门名称带字符‘S’的部门员工，工资合计，部门人数
select e.ename, d.sal, d.num from
      (select e.*, d.dname from emp e, dept d where e.deptno(+)=d.deptno and d.dname like '%S%') e,
      (select d.deptno, NVL(count(ename), 0) num, NVL(sum(sal), 0) sal from emp e, dept d where e.deptno(+)=d.deptno and d.dname like '%S%' group by d.deptno) d
where e.deptno=d.deptno;

11.不使用函数查询工资最高人的信息
select e.* from (select e.* from emp e order by sal desc) e where rownum<=1;

12.求平均工资的等级最低的部门名称
select d.dname from (select d.dname, s.grade from salgrade s, (select d.deptno, dname, NVL(avg(sal), 0) sal from emp e, dept d where d.deptno=e.deptno(+) group by d.deptno, dname) d
where d.sal between s.losal and s.hisal order by s.grade) d where rownum<=1;

13.部门经理人（职位为MANAGER）中平均工资最低的部门名称
select dname from dept d, (select deptno from (select deptno, avg(sal) sal from emp where job='MANAGER' group by deptno order by sal) where rownum <=1) d1 where d.deptno=d1.deptno;

14.求出部门平均工资以及等级
select d.sal, s.grade from salgrade s, (select d.deptno, NVL(avg(sal), 0) sal from emp e, dept d where e.deptno(+)=d.deptno group by d.deptno) d where d.sal between s.losal and s.hisal;

--------------------------------

请写出一段PL/SQL代码，判断输入的数字是否可以被3整除
declare
  num number(8) := &num;

begin
  if mod(num, 3) = 0 then
    dbms_output.put_line('可以被三整除');
  else
    dbms_output.put_line('不可以被三整除');
  end if;

end;

输入一个员工工号，修改该员工的工资，如果该员工在10号部门，工资增加100；如果该员工在20号部门，工资增加150；如果该员工在30号部门，工资增加200；否则增加10000。
要求：显示员工姓名、部门编号、现工资、涨后的工资
declare
  empno number := &empno;
  deptno number;
  mysal number;
  rowinfo scott.emp%rowtype;
begin
  select * into rowinfo from emp where emp.empno=&empno;
  if rowinfo.deptno = 10 then
     mysal := rowinfo.sal + 100;
  elsif rowinfo.deptno = 20 then
     mysal := rowinfo.sal + 150;
  elsif rowinfo.deptno = 30 then
     mysal := rowinfo.sal + 200;
  else
     mysal := rowinfo.sal + 10000;
  end if;
  dbms_output.put_line(' 员工姓名：' || rowinfo.ename || ' 部门编号：' || rowinfo.deptno || ' 现工资：' || rowinfo.sal || ' 涨后的工资：' || mysal);
  update emp set emp.sal=mysal where empno=&empno;  -- 插入时不可和字段同名
  commit;
end;
     
     

输入一个员工工号，修改该员工的工资，如果该员工工资低于1000，工资增加100；如果该员工工资在1000-2000，工资增加50；如果该员工工资在2000-3000，工资增加20；否则不增加。
要求：显示员工姓名、现工资、涨后的工资

declare
  empno number := &empno;
  deptno number;
  mysal number;
  rowinfo scott.emp%rowtype;
begin
  select * into rowinfo from emp where emp.empno=&empno;
  if rowinfo.sal < 1000 then
     mysal := rowinfo.sal + 100;
  elsif rowinfo.sal >= 1000 and rowinfo.sal <= 2000 then
     mysal := rowinfo.sal + 50;
  elsif rowinfo.sal >= 2000 and rowinfo.sal <= 3000 then
     mysal := rowinfo.sal + 20;
  else
     mysal := rowinfo.sal;
  end if;
  dbms_output.put_line(' 员工姓名：' || rowinfo.ename  || ' 现工资：' || rowinfo.sal || ' 涨后的工资：' || mysal);
  update emp set emp.sal=mysal where empno=&empno;  -- 插入时不可和字段同名
  commit;
end;
  

利用循环控制，向emp表中插入50条数据，empno从1000开始
declare
  empno number := 1000;
  rowinfo scott.emp%rowtype;
begin
  for i in 50..99 loop
      rowinfo.empno := empno + i;
      rowinfo.ename := i;
      rowinfo.job := i;
      rowinfo.mgr := i;
      rowinfo.hiredate := sysdate;
      rowinfo.sal := i;
      rowinfo.comm := i;
      rowinfo.deptno := 20;
      insert into emp values rowinfo;
      commit;
   end loop;
end;

declare
  empn number := 1000;
  rowinfo scott.emp%rowtype;
begin
  for i in 0..99 loop
      delete from emp where empno=empn+i;
      commit;
   end loop;
end;

自定义输入任意员工编号，输出该员工编号、姓名、工资、部门名称、所在地
declare
   empn number := &empno;
   rowinfo scott.emp%rowtype;
   rowinfo1 scott.dept%rowtype;
begin
   select * into rowinfo from emp where empno=empn;
   select * into rowinfo1 from dept where deptno=rowinfo.deptno;
   dbms_output.put_line('员工编号：' || rowinfo.empno || ' 姓名：' || rowinfo.ename || ' 工资：' || rowinfo.sal || ' 部门名称：' || rowinfo1.dname || ' 所在地：' || rowinfo1.loc);
end;

自定义输入任意员工编号，如果该员工入职时间大于10年，则奖金加1w，如果该员工入职时间大于5年，奖金加5000，否则
奖金不加。。
最终输出员工编号、姓名、入职时间、原奖金、现奖金
declare
   empn number := &empn;
   rowinfo scott.emp%rowtype;
   comm number;
   now number;
begin
   select * into rowinfo from emp where empno=empn;
   select extract(year from sysdate)-extract(year from hiredate) into now from emp where empno=empn;
   if now > 10 then
     comm := rowinfo.comm + 10000;
   elsif now > 5 then
     comm := rowinfo.comm + 5000;
   else
     comm := rowinfo.comm;
   end if;
   dbms_output.put_line('员工编号：' || rowinfo.empno || ' 姓名：' || rowinfo.ename || ' 入职时间：' || rowinfo.hiredate || ' 原奖金：' || rowinfo.comm || ' 现奖金：' || comm);
end;

自定义输入部门编号，查询出该部门编号下所有员工信息(姓名、工资、部门编号),并显示信息条数
declare
   rowinfo scott.emp%rowtype;
   cursor cur is select * from emp where deptno=&deptno;
begin
   open cur;
   fetch cur into rowinfo;
   if cur%isopen then
      while cur%found loop
            dbms_output.put_line('姓名：' || rowinfo.ename || ' 工资：' || rowinfo.sal || ' 部门编号：' || rowinfo.deptno);
            fetch cur into rowinfo;
      end loop;
      dbms_output.put_line('信息条数：' || cur%rowcount);
   end if;
end;

对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号
declare 
  i integer;
  cursor cur is select * from emp;
  empinfo emp%rowtype;
  avgsal number;
  nowsal number;
begin
  open cur;
  fetch cur into empinfo;
  if cur%isopen then
     while cur%found loop
           select avg(sal) into avgsal from emp where deptno=empinfo.deptno;
           if empinfo.sal > avgsal then
              nowsal := empinfo.sal - 50;
           else
              nowsal := empinfo.sal;
           end if;
           dbms_output.put_line('员工姓名：' || empinfo.ename || ' 所在部门编号：' || empinfo.deptno || ' 原薪水：' || empinfo.sal || ' 现薪水：' || nowsal);
           fetch cur into empinfo;
     end loop;
   end if; 
   dbms_output.put_line('信息数量：' || cur%rowcount);
   close cur;
end;

创建一个存储过程，通过输入员工的账号来进行查看员工的姓名，工资，奖金。
   输入的工号不存在则进行异常处理
   员工的工资大于4000进行异常提示处理
   奖金没有或为0，进行异常提示处理
   
create or replace procedure proc(num in out number)
as
begin
       if num = 0 then
          raise_application_error('-20000', '员工不存在');
       end if;
end;

create or replace procedure proc1(sal in out number, comm in out number)
as
begin
       if sal > 4000 then
          raise_application_error('-20001', '工资大于4000');
       elsif comm is null or comm = 0 then
          raise_application_error('-20002', '无奖金');
       end if;
end;

declare
   empinfo scott.emp%rowtype;
   num number:=0;
   empn number := &empn;
begin
   select count(empno) into num from emp where empno=empn;
   proc(num);
   select * into empinfo from emp where empno=empn;
   proc1(empinfo.sal, empinfo.comm);
/*   if empinfo.comm is null then
      empinfo.comm := 0;
   end if; */
   dbms_output.put_line('员工姓名：' || empinfo.ename || ' 员工工资：' || empinfo.sal || ' 员工奖金：' || empinfo.comm);
end;

select * from emp order by sal desc;

create or replace procedure procemp(empn in number)
is
   num number := 0;
   empinfo scott.emp%rowtype;
begin
   select count(empno) into num from emp where empno=empn;
   select * into empinfo from emp where empno=empn;
       
   if num = 0 then
      dbms_output.put_line('-20000, 员工不存在');
   end if;
   if empinfo.sal > 4000 then
      dbms_output.put_line('-20001, 工资大于4000');
   end if;
   if empinfo.comm is null or empinfo.comm = 0 then
      dbms_output.put_line('-20002, 无奖金');
   end if;
   
   if empinfo.comm is null then
      empinfo.comm := 0;
   end if; 
   dbms_output.put_line('员工姓名：' || empinfo.ename || ' 员工工资：' || empinfo.sal || ' 员工奖金：' || empinfo.comm);
end procemp;