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;