创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。

CREATE OR REPLACE PROCEDURE return_deptinfo( p_deptno emp.deptno%TYPE, p_avgsal OUT emp.sal%TYPE, p_count OUT emp.sal%TYPE)

AS

BEGIN

SELECT avg(sal),count(*) INTO p_avgsal,p_count FROM emp WHERE deptno=p_deptno;

EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('The department don’’t exists!');

END r

eturn_deptinfo;

 

 

DECLARE

v_avgsal emp.sal%TYPE;

v_count NUMBER;

BEGIN

show_emp(20);

return_deptinfo(10,v_avgsal,v_count);

DBMS_OUTPUT.PUT_LINE(v_avgsal||' '||v_count);

END;

 

创建一个以部门号为参数，返回该部门最高工资的函数。

CREATE OR REPLACE FUNCTION return_maxsal

(p_deptno emp.deptno%TYPE)

RETURN emp.sal%TYPE

AS

v_maxsal emp.sal%TYPE;

BEGIN

SELECT max(sal) INTO v_maxsal FROM emp

WHERE deptno=p_deptno;

RETURN v_maxsal;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('The deptno is invalid!');

END return_maxsal;

 

DECLARE

v_sal emp.sal%TYPE;

BEGIN

FOR v_dept IN (SELECT DISTINCT deptno FROM emp)

LOOP

v_sal:=return_maxsal(v_dept.deptno);

DBMS_OUTPUT.PUT_LINE(v_dept.deptno||' '||v_sal);

END LOOP;

END;

 

包由包规范和包体两部分组成，在数据库中独立存储

创建一个软件包，包括2个变量、2个过程和1个异常。

CREATE OR REPLACE PACKAGE pkg_emp

AS

minsal NUMBER;

maxsal NUMBER;

e_beyondbound EXCEPTION;

PROCEDURE update_sal(

p_empno NUMBER, p_sal NUMBER);

PROCEDURE add_employee(

p_empno NUMBER,p_sal NUMBER);

END pkg_emp;

CREATE OR REPLACE PACKAGE BODY pkg_emp

AS

PROCEDURE update_sal(p_empno NUMBER, p_sal NUMBER)

AS

BEGIN

SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;

IF p_sal BETWEEN minsal AND maxsal THEN

UPDATE emp SET sal=p_sal WHERE empno=p_empno;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20000,'The employee doesn''t exist');

END IF;

ELSE

RAISE e_beyondbound;

END IF;

EXCEPTION

WHEN e_beyondbound THEN

DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');

END update_sal;

PROCEDURE add_employee(p_empno NUMBER,p_sal NUMBER)

AS

BEGIN

SELECT min(sal), max(sal) INTO minsal,maxsal FROM emp;

IF p_sal BETWEEN minsal AND maxsal THEN

INSERT INTO emp(empno,sal) VALUES(p_empno,p_sal);

ELSE

RAISE e_beyondbound;

END IF;

EXCEPTION

WHEN e_beyondbound THEN

DBMS_OUTPUT.PUT_LINE('The salary is beyond bound!');

END add_employee;

END pkg_emp;

 

在一个包中重载两个过程，分别以部门号和部门名称为参数，查询相应部门员工名、员工号信息

CREATE OR REPLACE PACKAGE pkg_overload

AS

PROCEDURE show_emp(p_deptno NUMBER);

PROCEDURE show_emp(p_dname VARCHAR2);

END pkg_overload;

CREATE OR REPLACE PACKAGE BODY pkg_overload

AS

PROCEDURE show_emp(p_deptno NUMBER)

AS

BEGIN

FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno) LOOP

DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||

v_emp.ename);

END LOOP;

END show_emp;

PROCEDURE show_emp(p_dname VARCHAR2)

AS

v_deptno NUMBER;

BEGIN

SELECT deptno INTO v_deptno FROM dept

WHERE dname=p_dname;

FOR v_emp IN (SELECT * FROM emp WHERE deptno=v_deptno) LOOP

DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||

v_emp.ename);

END LOOP;

END show_emp;

END pkg_overload;

创建一个触发器，禁止在休息日改变雇员信息，

create or replace trigger tr_sec_emp

before insert or update or delete on emp

begin

if to_char(sysdate,'DY') in ('星期六','星期日') then

raise_application_error(-20001,'不能在休息日修改员工信息');

end if;

end;