oracle 存储过程 各位朋友帮忙 初学者

[yanqiu520happines]

1.建立一个存储过程用来接收一个部门号，找出其中的两位最老的员工的员工号，并打印。

2.编写一个过程用来传入一个员工号，在emp表中删除一个员工，当该员工是该部门的最后一个员工时就在dept表中删除该员工所在的部门。

[liuyyuns]

你这是作业题？

[tangren]

--写一个吧，作为范例，第二题自己写
CREATE OR REPLACE PROCEDURE p_get_emp(i_deptno NUMBER) IS
BEGIN
  FOR c IN (SELECT empno
              FROM (SELECT empno
                      FROM emp t
                     WHERE t.deptno = i_deptno
                     ORDER BY hiredate)
             WHERE rownum < 3) LOOP
    dbms_output.put_line('empno=' || c.empno);
  END LOOP;
END;
/

[yanqiu520happines]

非常感谢大侠！！
create or replace procedure getEmpno(v_empno number)
as
v_name varchar2(20);
cursor v_e is select * from emp where empno=v_empno order by hiredate;
begin
open v_e;
fetch v_e into v_name;
dbms_output.put_line(v_name);
fetch v_e into v_name;
dbms_output.put_line(v_name);
close v_e;
end;
/

create or replace procedure getEmpno2(v_empno number)
as
v_count number;
v_deptno number;
begin
select deptno into v_deptno from emp where empno=v_empno;
select count(*) into v_count from emp where deptno=v_deptno;
if v_count=1 then
delete from emp where empno=v_empno;
delete from dept where deptno=v_deptno;
else
delete from emp where emppno=v_empno;
end if;
end;
/