Oracle 练习P297 131026 PL/SQL块程序

--1、编写一个PL/SQL块，输出所有员工的员工姓名，员工号、工资和部门号。

begin

  for v_emp in (select * from emp) loop

    dbms_output.put('员工姓名：' || v_emp.ename);

    dbms_output.put('，员工号：' || v_emp.empno);

    dbms_output.put('，工资：' || v_emp.sal);

    dbms_output.put_line('，部门号：' || v_emp.deptno);

  end loop;

end;

--2、编写一个PL/SQL块，输出所有比本部门平均工资高的员工信息。

begin

  for v_emp in (select e.*

                  from emp e

                  join (select deptno, avg(sal) avg_sal

                         from emp

                        group by deptno) t on e.deptno = t.deptno

                 where e.sal > t.avg_sal

                 order by e.deptno) loop

    dbms_output.put('员工姓名：' || v_emp.ename);

    dbms_output.put('，员工号：' || v_emp.empno);

    dbms_output.put('，工资：' || v_emp.sal);

    dbms_output.put_line('，部门号：' || v_emp.deptno);

  end loop;

end;

--3、编写一个PL/SQL块，输出所有员工及其部门领导的姓名、员工号及部门号。

begin

  for v_emp in (select e1.empno a,

                       e1.ename b,

                       e1.deptno c,

                       e2.ename d,

                       e2.empno e,

                       e2.deptno f

                  from emp e1

                  join emp e2 on e1.mgr = e2.empno) loop

    dbms_output.put('员工姓名：' || v_emp.a);

    dbms_output.put('员工员工号：' || v_emp.b);

    dbms_output.put('员工部门号：' || v_emp.c);

    dbms_output.put('，领导姓名：' || v_emp.d);

    dbms_output.put('，领导员工号：' || v_emp.e);

    dbms_output.put_line('，领导部门号：' || v_emp.f);           

  end loop;

end;

--4、查询姓为“SMITH”的员工信息，并输出其员工号、姓名、工资、部门号。

--如果该员工不存在，则插入一条新记录，员工号为2012，员工姓为“SMITH”，工资为7500元，

--入职日期为“2002年3月5日”，部门号为50，如果存在多个名为“SMITH”的员工，

--则输出所有名为“SMITH”的员工号、姓名、工资、入职日期、部门号L。

--delete from emp2 where ename = 'SMITH';

declare

  v_exist number(1);

begin

  select count(*) into v_exist from emp2 where ename = 'SMITH';

  --dbms_output.put_line(v_exist);

  if v_exist = 0 then

    insert into emp2(empno,ename,sal,hiredate,deptno) values('2012','SMITH',7500,to_date('20020305','yyyymmdd'),50);

    commit;

  elsif v_exist >= 1 then

    for v_emp in (select * from emp2 where ename='SMITH') loop

      dbms_output.put_line('员工号：' || v_emp.empno);

      dbms_output.put_line('姓名：' || v_emp.ename);

      dbms_output.put_line('工资：' || v_emp.sal);

      dbms_output.put_line('入职日期：' || v_emp.hiredate);

      dbms_output.put_line('部门号：' || v_emp.deptno);

    end loop;

  else null;

  end if;

end;