Oracle进阶（六）包（Package）和包体_oracle创建包和包体

1、包（Package）概述

包（Package）是用来存储相关程序结构的对象，它存储于数据字典中。包由两部分组成：包规范(PACKAGE)和包体(PACKAGE BODY)。

包规范是包的说明部分，是对外的操作接口（类似java接口），对应用是可见的。

包体是包的代码和实现部分（类似java实现类），对应用来说是不可见的。

1.1 包说明

公有元素(PUBLIC)    在包头中说明，在包体中具体定义。在包外可见并可以访问，对整个应用的全过程有效。
私有元素(PRIVATE)  在包体的说明部分说明。只能被包内部的其他部分访问。
局部变量(LOCAL)     在过程或函数的说明部分说明。只能在定义变量的过程或函数中使用。

1.2 系统包

2、基本语法

2.1 创建包和包体

1.创建包规范语法：
        CREATE [OR REPLACE] PACKAGE 包名 IS|AS
           --变量(VARIABLE)定义
           --常量(CONSTANT)定义
           --游标(CURSOR)定义
           --类型(TYPE)定义
           --函数定义
           FUNCTION 函数名 [(参数列表)] RETURN 返回类型;
           --存储过程定义
           PROCEDURE 存储过程名 [(参数列表)];
           异常(EXCEPTION)
        END [包名]; 
 
2.创建包体语法：
        CREATE [OR REPLACE] PACKAGE BODY 包名 IS|AS
           --...
           --函数实现
           FUNCTION 函数名 [(参数列表)] RETURN 返回类型 IS|AS
           --函数实现内容
           --存储过程实现
           PROCEDURE 存储过程名 [(参数列表)] IS|AS
           --存储过程实现内容
       END [包名];

2.2 其它语法

--1 删除包规范
DROP PACKAGE 包规范名
 
--2 删除包体
DROP PACKAGE BODY 包体名
 
--3 重新编译包头
ALTER PACKAGE 包名 COMPILE PACKAGE
 
--4 重新编译包体
ALTER PACKAGE 包名 COMPILE PACKAGE BODY

2.3 注意要点

1 包说明和包体必须有相同的名字
2 包的开始没有BEGIN语句，与存储过程和函数不同。
3 在包的说明部分定义函数和过程的名称和参数，具体实现在包体中定义。
4 在包内声明常量、变量、类型定义、异常、及游标时不使用DECLARE。
5 包内的过程和函数的定义不要CREATE OR REPLACE语句。
6 包定义和包体两者分离。
7 只有当包头编辑成功后才能编辑包体。
8 函数名与过程名须和包头中的函数过程一样。

3、包的应用

以下所涉及的表，请查看Oracle进阶（三）常用函数的附表。

3.1 包创建

CREATE OR REPLACE PACKAGE EMP_PK   
        --包定义部分   
          IS  
				--公有变量：员工人数
         V_EMP_COUNT NUMBER(5);
				--公有过程：初始化数据
         PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER);
				--公有过程:显示员工列表 
         PROCEDURE LIST_EMP;
				 --公有过程:新增员工
         PROCEDURE INSERT_EMP(P_EMPNO NUMBER,P_ENAME VARCHAR2,P_JOB VARCHAR2,P_SAL NUMBER);
				 --公有过程：删除员工
         PROCEDURE DELETE_EMP(P_EMPNO NUMBER);
				 --公有过程:修改员工工资
         PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER);
        END EMP_PK;  
        
          /
   
        CREATE OR REPLACE PACKAGE BODY EMP_PK
        --包体部分   
          IS  
         --私有变量：输出信息
         V_MESSAGE VARCHAR2(50);
         --私有变量:可修改工资上限 
         V_MAX_SAL NUMBER(7);
         --私有变量:可修改工资下限  
         V_MIN_SAL NUMBER(7);
         --私有函数:判断员工是否存在
         FUNCTION EXIST_EMP(P_EMPNO NUMBER)  RETURN  BOOLEAN;
         --私有过程:显示信息
         PROCEDURE SHOW_MESSAGE;
         --INIT
         PROCEDURE INIT(P_MAX NUMBER,P_MIN NUMBER)   
          IS   
         BEGIN  
          SELECT COUNT(*) INTO V_EMP_COUNT FROM EMP;  
          V_MAX_SAL:=P_MAX;  
          V_MIN_SAL:=P_MIN;  
          V_MESSAGE:='初始化数据已完成！';  
          SHOW_MESSAGE;   
         END INIT;  
         --LIST_EMP
         PROCEDURE LIST_EMP   
          IS   
         BEGIN  
          DBMS_OUTPUT.PUT_LINE('姓名       职务      工资');  
          FOR EMP_REC IN (SELECT ENAME,JOB,SAL FROM EMP)  
          LOOP  
           DBMS_OUTPUT.PUT_LINE(RPAD(EMP_REC.ENAME,10,' ')||RPAD(EMP_REC.JOB,10,' ')||TO_CHAR(EMP_REC.SAL));  
          END LOOP;  
          DBMS_OUTPUT.PUT_LINE('员工总人数'|| V_EMP_COUNT);  
         END LIST_EMP;  
        --INSERT_EMP 
         PROCEDURE INSERT_EMP(P_EMPNO NUMBER,P_ENAME VARCHAR2,P_JOB VARCHAR2,P_SAL NUMBER)  
          IS   
         BEGIN  
          IF NOT EXIST_EMP(P_EMPNO) THEN  
           INSERT INTO EMP(EMPNO,ENAME,JOB,SAL)VALUES(P_EMPNO,P_ENAME,P_JOB,P_SAL);  
           COMMIT;   
           V_EMP_COUNT:=V_EMP_COUNT+1;  
           V_MESSAGE:='员工'||P_EMPNO||'已新增!';  
          ELSE  
           V_MESSAGE:='员工'||P_EMPNO||'已存在,不能新增!';  
          END IF;  
           SHOW_MESSAGE;   
         EXCEPTION  
          WHEN OTHERS THEN  
          V_MESSAGE:='员工'||P_EMPNO||'新增失败!';  
          SHOW_MESSAGE;  
         END INSERT_EMP;  
        --DELETE_EMP
         PROCEDURE DELETE_EMP(P_EMPNO NUMBER)   
          IS   
         BEGIN   
          IF EXIST_EMP(P_EMPNO) THEN  
           DELETE FROM EMP WHERE EMPNO=P_EMPNO;  
           COMMIT;  
           V_EMP_COUNT:=V_EMP_COUNT-1;  
           V_MESSAGE:='员工'||P_EMPNO||'已删除!';  
          ELSE  
           V_MESSAGE:='员工'||P_EMPNO||'不存在，不能删除!';  
          END IF;  
          SHOW_MESSAGE;  
         EXCEPTION  
          WHEN OTHERS THEN  
          V_MESSAGE:='员工'||P_EMPNO||'删除失败!';  
          SHOW_MESSAGE;  
         END DELETE_EMP;  
         --CHANGE_EMP_SAL  
         PROCEDURE CHANGE_EMP_SAL(P_EMPNO NUMBER,P_SAL NUMBER)   
          IS   
         BEGIN   
          IF (P_SAL>V_MAX_SAL OR P_SAL<V_MIN_SAL) THEN  
           V_MESSAGE:='工资超出修改范围!';  
          ELSIF NOT EXIST_EMP(P_EMPNO) THEN  
           V_MESSAGE:='员工'||P_EMPNO||'不存在，不能修改工资!';  
          ELSE  
           UPDATE EMP SET SAL=P_SAL WHERE EMPNO=P_EMPNO;  
           COMMIT;  
           V_MESSAGE:='员工'||P_EMPNO||'工资已经修改!';  
          END IF;  
           SHOW_MESSAGE;  
         EXCEPTION  
          WHEN OTHERS THEN  
          V_MESSAGE:='员工'||P_EMPNO||'工资修改失败!';  
          SHOW_MESSAGE;  
         END CHANGE_EMP_SAL;  
        --SHOW_MESSAGE
         PROCEDURE SHOW_MESSAGE   
          IS   
         BEGIN  
          DBMS_OUTPUT.PUT_LINE('提示信息：'||V_MESSAGE);  
         END SHOW_MESSAGE;  
         --EXIST_EMP
         FUNCTION EXIST_EMP(P_EMPNO NUMBER)  
         RETURN BOOLEAN   
          IS
         --局部变量：员工数量
         V_NUM NUMBER;
         BEGIN  
          SELECT COUNT(*) INTO V_NUM FROM EMP WHERE EMPNO=P_EMPNO;  
          IF V_NUM=1 THEN   
            RETURN TRUE;  
          ELSE  
           RETURN FALSE;  
          END IF;   
         END EXIST_EMP;  
 
        END EMP_PK; 

3.2 包调用

--调用包
 
--1 初始化
BEGIN
  EMP_PK.INIT(P_MAX => 200,
              P_MIN => 100);
END;
 
--2 员工列表
CALL EMP_PK.LIST_EMP();
 
--3 新增员工
CALL EMP_PK.INSERT_EMP(2021,'一二山人','YESR',10000); 
 
--4 查看员工人数
BEGIN  
    DBMS_OUTPUT.PUT_LINE(EMP_PK.V_EMP_COUNT);  
END;
 
--5 删除员工
CALL EMP_PK.DELETE_EMP(2021); 
 
--6 修改工资
CALL EMP_PK.CHANGE_EMP_SAL(7900,150); 
 
--7 授权其他用户调用包
GRANT EXECUTE ON EMP_PK TO SYS;  
 
--8 其它用户SYS调用包
CALL SCOTT.EMP_PK.INIT(400,300); 

4、包的进阶

4.1 包重载创建

CREATE OR REPLACE PACKAGE EMP_OVERLOAD_PK IS
   --过程：新增部门
   PROCEDURE INSERT_DEPT (
       P_DEPTNO   DEPT.DEPTNO%TYPE,    --部门编号
       P_DNAME    DEPT.DNAME%TYPE,     --部门名称
       P_LOC      DEPT.LOC%TYPE        --位置
    );   
   --过程：新增部门（重载）
   PROCEDURE INSERT_DEPT (
       P_DEPTNO   DEPT.DEPTNO%TYPE,    --部门编号
       P_DNAME    DEPT.DNAME%TYPE     --部门名称
    );       
    --函数：获取员工加薪
    FUNCTION GET_ADDSALARY (P_EMPNO EMP.EMPNO%TYPE)
       RETURN NUMBER;
 
    --函数：获取员工加薪(重载)
    FUNCTION GET_ADDSALARY (P_ENAME EMP.ENAME%TYPE)
       RETURN NUMBER;                  
END EMP_OVERLOAD_PK;
 
/
 
CREATE OR REPLACE PACKAGE BODY EMP_OVERLOAD_PK
IS
    FUNCTION EXIST_DEPT(P_DEPTNO DEPT.DEPTNO%TYPE)  RETURN  NUMBER;
  --INSERT_DEPT
  PROCEDURE INSERT_DEPT (
       P_DEPTNO   DEPT.DEPTNO%TYPE,
       P_DNAME    DEPT.DNAME%TYPE,
       P_LOC      DEPT.LOC%TYPE
    )
    AS
       BEGIN
       IF EXIST_DEPT(P_DEPTNO) > 0  --如果部门存在
       THEN                            --抛出异常
          RAISE_APPLICATION_ERROR (-2021, '出现了相同的员工记录');
       END IF;
       INSERT INTO DEPT(DEPTNO, DNAME, LOC)
            VALUES (P_DEPTNO, P_DNAME, P_LOC);--新增
						COMMIT;
    END INSERT_DEPT;
--INSERT_DEPT（重载）
  PROCEDURE INSERT_DEPT (
       P_DEPTNO   DEPT.DEPTNO%TYPE,
       P_DNAME    DEPT.DNAME%TYPE
    )
    AS
       BEGIN
       IF EXIST_DEPT(P_DEPTNO) > 0 --如果部门存在
       THEN                            --抛出异常
          RAISE_APPLICATION_ERROR (-2022, '出现了相同的员工记录');
       END IF;
       INSERT INTO DEPT(DEPTNO, DNAME, LOC)
            VALUES (P_DEPTNO, P_DNAME, '广州天河区');--新增记录
						COMMIT;
    END INSERT_DEPT;
 
    --GET_ADDSALARY
    FUNCTION GET_ADDSALARY (P_EMPNO EMP.EMPNO%TYPE)
       RETURN NUMBER
    IS
       V_JOB           EMP.JOB%TYPE;
       V_SAL           EMP.SAL%TYPE;
       V_SALARYRATIO   NUMBER (10, 2);--调薪比率
    BEGIN
       --获取员工表中的薪资信息
       SELECT JOB, SAL INTO V_JOB, V_SAL FROM EMP WHERE EMPNO = P_EMPNO;
       CASE V_JOB
          WHEN 'CLERK' THEN
             V_SALARYRATIO := 1.05;
          WHEN 'SALESMAN' THEN
             V_SALARYRATIO := 1.1;
          WHEN 'MANAGER' THEN
             V_SALARYRATIO := 1.2;
          ELSE
             V_SALARYRATIO := 1;
       END CASE;
       IF V_SALARYRATIO <> 1
       THEN
          RETURN ROUND(V_SAL * V_SALARYRATIO,2);
       ELSE
          RETURN V_SAL;
       END IF;
     EXCEPTION
          WHEN NO_DATA_FOUND THEN
             RETURN 0;
     END GET_ADDSALARY;
 
    --GET_ADDSALARY（重载）
    FUNCTION GET_ADDSALARY (P_ENAME EMP.ENAME%TYPE)
       RETURN NUMBER
    IS
       V_JOB           EMP.JOB%TYPE;
       V_SAL           EMP.SAL%TYPE;
       V_SALARYRATIO   NUMBER (10, 2);--调薪比率
    BEGIN
       --获取员工表中的薪资信息
       SELECT JOB, SAL INTO V_JOB, V_SAL FROM EMP WHERE ENAME = P_ENAME;
       CASE V_JOB
          WHEN 'CLERK' THEN
             V_SALARYRATIO := 1.05;
          WHEN 'SALESMAN' THEN
             V_SALARYRATIO := 1.1;
          WHEN 'MANAGER' THEN
             V_SALARYRATIO := 1.2;
          ELSE
             V_SALARYRATIO := 1;
       END CASE;
       IF V_SALARYRATIO <> 1
       THEN
          RETURN ROUND(V_SAL * V_SALARYRATIO,2);
       ELSE
          RETURN V_SAL;
       END IF;
     EXCEPTION
          WHEN NO_DATA_FOUND THEN
             RETURN 0;
     END GET_ADDSALARY;
 
    --私有函数：部门是否存在
    FUNCTION EXIST_DEPT(P_DEPTNO DEPT.DEPTNO%TYPE) RETURN NUMBER
    AS
      V_COUNT NUMBER(2);
    BEGIN
       SELECT COUNT(*) INTO V_COUNT FROM DEPT WHERE DEPTNO=P_DEPTNO;
       RETURN V_COUNT;
    END;
END EMP_OVERLOAD_PK;

4.2 包重载调用

--调用
BEGIN
	 EMP_OVERLOAD_PK.INSERT_DEPT(50,,);
END
--重载过程
BEGIN
  EMP_OVERLOAD_PK.INSERT_DEPT(P_DEPTNO => 50,
                              P_DNAME =>'交付二部',
                              P_LOC => '海珠区');
END;
--过程
BEGIN
  EMP_OVERLOAD_PK.INSERT_DEPT(P_DEPTNO => 60,
                              P_DNAME =>'交付一部');
END;
----重载函数
DECLARE
   V_SAL NUMBER(10,2);
BEGIN
   V_SAL:=EMP_OVERLOAD_PK.GET_ADDSALARY(7369);
	 DBMS_OUTPUT.PUT_LINE('获取员工加薪'|| V_SAL); 
END;
--函数
DECLARE
   V_SAL NUMBER(10,2);
BEGIN
   V_SAL:=EMP_OVERLOAD_PK.GET_ADDSALARY('SMITH');
	 DBMS_OUTPUT.PUT_LINE('获取员工加薪'|| V_SAL); 
END;