重载(overload)是指多个具有相同名称的子程序。定义包时,使用重载特性,可以使用户在调用同名组件时使用不同参数传递数据,从而方便用户使用。
1、建立包规范
1、建立包规范
CREATE OR REPLACE PACKAGE pkg_overload
IS
FUNCTION get_sal(eno NUMBER) RETURN NUMBER;
FUNCTION get_sal(NAME VARCHAR2) RETURN NUMBER;
PROCEDURE fire_employee(eno NUMBER);
PROCEDURE fire_employee(NAME VARCHAR2);
END pkg_overload;
/
2、建立包体
CREATE OR REPLACE PACKAGE BODY pkg_overload
IS
FUNCTION get_sal(eno NUMBER) RETURN NUMBER
IS
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = eno;
RETURN v_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,'雇员不存在');
END;
FUNCTION get_sal(NAME VARCHAR2) RETURN NUMBER
IS
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE UPPER(ename) = UPPER(NAME);
RETURN v_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,'雇员不存在');
END;
PROCEDURE fire_employee(eno NUMBER)
IS
BEGIN
DELETE FROM emp WHERE empno = eno;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20000,'雇员不存在');
END IF;
END;
PROCEDURE fire_employee(NAME VARCHAR2)
IS
BEGIN
DELETE FROM emp WHERE UPPER(ename) = UPPER(NAME);
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20000,'雇员不存在');
END IF;
END;
END pkg_overload;
3、调用重载过程和重载函数
SQL> VAR sal1 NUMBER;
SQL> VAR sal2 NUMBER;
SQL> EXEC :sal1 := pkg_overload.get_sal('SCOTT');
SQL> EXEC :sal2 := pkg_overload.get_sal(7788);
SQL> print sal1,sal2;
SQL> EXEC pkg_overload.fire_employee(1112);
SQL> EXEC PKG_OVERLOAD.fire_employee('张三');