ORcale 存储过程实例

1、建立一个存储过程用来接收一个员工号，返回他的工资和他所在部门的平均工资并作为传出参数传出。
CREATE OR REPLACE PROCEDURE p_get_emp
(i_empno emp.empno%TYPE,o_sal OUT emp.sal%TYPE,o_avg_a OUT emp.sal%TYPE)
AS
BEGIN
        SELECT sal
          INTO  o_sal
          FROM emp
          WHERE empno=i_empno;
        dbms_output.put_line(o_sal); 
 SELECT AVG(sal) AS aa
 INTO o_avg_a
 FROM emp WHERE deptno =(SELECT deptno FROM emp WHERE empno=i_empno);
         dbms_output.put_line(o_avg_a); 
END p_get_emp;

DECLARE
      i_no emp.empno%TYPE;
      v_sal emp.sal%TYPE;
      avg_sal emp.sal%TYPE;
   BEGIN
        i_no:=&请输入;
        p_get_emp(i_no,v_sal,avg_sal);
        dbms_output.put_line(to_char(i_no) || v_sal || avg_sal);
   END;

 

2、建立一个存储过程用来接收一个部门号，找出其中的两位最老的员工的员工号，并打印。
CREATE OR REPLACE PROCEDURE proc_deptno(i_deptno emp.deptno%TYPE)
IS
       CURSOR emp_cur
       IS
       SELECT empno,hiredate
       FROM emp
       WHERE deptno=i_deptno
       AND ROWNUM<=2 ORDER BY hiredate;
       o_emp_col emp_cur%ROWTYPE;
BEGIN
       OPEN emp_cur;
       FETCH emp_cur INTO o_emp_col;
       WHILE emp_cur%FOUND
       LOOP
       dbms_output.put_line(o_emp_col.empno ||' '||o_emp_col.hiredate);
       FETCH emp_cur INTO o_emp_col;
       END LOOP;
       CLOSE emp_cur;
END proc_deptno;

DECLARE
        i_empno emp.empno%TYPE;
        i_no emp.deptno%TYPE;
  BEGIN
       i_no:=&请输入;
        proc_deptno(i_no);
    END;

3、编写一个过程用来传入一个员工号，在emp表中删除一个员工，当该员工是该部门的最后一个员工时
就在dept表中删除该员工所在的部门。
CREATE OR REPLACE PROCEDURE p_del_empno(i_empno emp.empno%TYPE)
IS
    i NUMBER(2,0);
    o_deptno emp.deptno%TYPE;
BEGIN
        SELECT deptno
          INTO o_deptno
          FROM emp
          WHERE empno=i_empno;
         
        SELECT COUNT(deptno)
          INTO i
          FROM emp WHERE deptno=o_deptno;
         
        DELETE
          FROM emp
         WHERE empno=i_empno;
            
          IF i<=1 THEN
        DELETE FROM dept
        WHERE deptno=o_deptno;
       END IF;
END;

DECLARE
        i_empno emp.empno%TYPE;
        i_no emp.empno%TYPE;
  BEGIN
       i_no:=&请输入;
        p_del_empno(i_no);
        dbms_output.put_line(i_no);
   END;