欢迎光临
我们一直在努力

源码-PL/SQL从入门到精通-第十四章-包-Part 1

调试经验:

1. 编译包时,如果出现编译错误(PL/SQL developer中包名出现红叉),可通过以下语句查看:

SELECT   name,line, POSITION, text
    FROM user_errors
   WHERE NAME = ‘INITTEST’
ORDER BY SEQUENCE;

2. 上述语句中,引号中的包名(如,INITTEST)一定要大写,否则查不到

另外,PL/SQL中的包分为包规范(包的定义)和包体(包的实现)两部分,是PL/SQL编程的基本单元。

虽然名称为包,但包中内容无非是一些类型定义(常量、集合、记录、游标、函数、过程等变量)及实现方法,这更像是Java中的一个类(字段+方法),而比Java的包(package) 又低一个层次。

--第14章开始
--代码14.1 包规范定义示例
--定义包规范,包规范将被用于应用程序的接口部分,供外部调用
CREATE OR REPLACE PACKAGE emp_pkg AS
   --定义集合类型
   TYPE emp_tab IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
   --在包规范中定义一个记录类型
   TYPE emprectyp IS RECORD(
      emp_no NUMBER,
      sal  NUMBER
   );
   --定义一个游标变量
   CURSOR desc_salary RETURN emprectyp;
   --定义雇佣员工的过程
   PROCEDURE hire_employee(p_empno NUMBER,p_ename VARCHAR2,p_job VARCHAR2,p_mgr NUMBER,p_sal NUMBER,
                        p_comm NUMBER,p_deptno NUMBER,p_hiredate DATE);
   --定义解雇员工的过程                        
   PROCEDURE fire_employee(p_emp_id NUMBER );
END emp_pkg;

--代码14.2 包体定义示例
--定义包体
CREATE OR REPLACE PACKAGE BODY emp_pkg
AS
   --定义游标变量的具体类型
   CURSOR desc_salary RETURN emprectyp IS
      SELECT empno, sal FROM emp ORDER BY sal DESC;
   --定义雇佣员工的具体实现
   PROCEDURE hire_employee(p_empno NUMBER,p_ename VARCHAR2,
                           p_job VARCHAR2,p_mgr NUMBER,p_sal NUMBER,
                           p_comm NUMBER,p_deptno NUMBER,p_hiredate DATE) IS
   BEGIN
      --向emp表中插入一条员工信息
      INSERT INTO emp VALUES(p_empno,p_ename,p_job,p_mgr,p_hiredate,p_sal,p_comm,p_deptno); 
   END;       
   --定义解雇员工的具体实现                 
   PROCEDURE fire_employee(p_emp_id NUMBER ) IS
   BEGIN
      --从emp表中删除员工信息
      DELETE FROM emp WHERE empno=p_emp_id;
   END;          
END emp_pkg;



--代码14.3 包规范定义示例
--定义包规范,包规范将被用于应用程序的接口部分,供外部调用
CREATE OR REPLACE PACKAGE dept_pkg AS
   dept_count NUMBER:=1;
   --定义集合类型
   TYPE dept_tab IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
   --在包规范中定义一个记录类型
   TYPE deptrectyp IS RECORD(
      dept_no NUMBER,
      dname  VARCHAR2(30),
      loc VARCHAR2(30)
   );
   CURSOR deptcur RETURN deptrectyp;       --定义一个游标变量
   e_nodept_assign EXCEPTION;              --定义一个异常
END dept_pkg;


--代码14.4 调用包规范中定义的元素
DECLARE
   mydept dept_pkg.dept_tab;                           --定义包中的集合类型的变量     
BEGIN
   FOR deptrow IN (SELECT * FROM dept) LOOP            --使用游标FOR循环提取dept数据
     mydept(dept_pkg.dept_count):=deptrow;             --为集合类型赋值
     dept_pkg.dept_count:=dept_pkg.dept_count+1;       --递增包中的变量的值
   END LOOP;    
   FOR i IN 1..mydept.count LOOP                       --循环显示集合中的部门的部门名称
      DBMS_OUTPUT.put_line(mydept(i).dname);
   END LOOP;
   dept_pkg.dept_count:=1;                             --重置dept_pkg.dept_count的值。
EXCEPTION
   WHEN  dept_pkg.e_nodept_assign THEN
      DBMS_OUTPUT.put_line('没有找到员工记录');     --捕捉异常,如果有触发的话
END;   



--代码14.5 包体实现示例
--定义包规范
CREATE OR REPLACE PACKAGE emp_action_pkg IS
   v_deptno NUMBER(3):=20;              --包公开的变量
   --定义一个增加新员工的过程
   PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE,     --部门名称
       p_loc      dept.loc%TYPE        --位置
    );   
    --定义一个获取员工加薪数量的函数
    FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
       RETURN NUMBER;    
END emp_action_pkg;

--定义包体
CREATE OR REPLACE PACKAGE BODY emp_action_pkg IS
  --公开,实现包规范中定义的newdept过程
  PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE,     --部门名称
       p_loc      dept.loc%TYPE        --位置
    )
    AS
       v_deptcount   NUMBER;           --保存是否存在员工编号
       BEGIN
       SELECT COUNT (*) INTO v_deptcount FROM dept
        WHERE deptno = p_deptno;       --查询在dept表中是否存在部门编号
       IF v_deptcount > 0              --如果存在相同的员工记录
       THEN                            --抛出异常
          raise_application_error (-20002, '出现了相同的部门记录');
       END IF;
       INSERT INTO dept(deptno, dname, loc)  
            VALUES (p_deptno, p_dname, p_loc);--插入记录
    END newdept;
    --公开,实现包规范中定义的getraisedsalary函数
    FUNCTION getraisedsalary (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 '职员' THEN
             v_salaryratio := 1.09;
          WHEN '销售人员' THEN
             v_salaryratio := 1.11;
          WHEN '经理' THEN
             v_salaryratio := 1.18;
          ELSE
             v_salaryratio := 1.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;                             --如果没找到原工记录,返回0
     END getraisedsalary;
    --私有,该函数在包规范中并不存在,只能在包体内被引用
    FUNCTION checkdeptno(p_deptno dept.deptno%TYPE) RETURN NUMBER
    AS
      v_counter NUMBER(2);
    BEGIN
       SELECT COUNT(*) INTO v_counter FROM dept WHERE deptno=p_deptno;
       RETURN v_counter; 
    END;           
END emp_action_pkg;       

--代码14.6 调用包组件示例
--在该块中为v_deptno赋值为30,调用getraisedsalary函数
BEGIN
   emp_action_pkg.v_deptno:=30;                                  --为包规范变量赋值
   DBMS_OUTPUT.put_line(emp_action_pkg.getraisedsalary(7369));--调用包中的函数
END;

select * from dept;
--在该块中为v_deptno赋值为50,并调用newdept过程
BEGIN
   emp_action_pkg.v_deptno:=50;
   emp_action_pkg.newdept(45,'采纳部','佛山');
END;
--在该块中输出v_deptno的值

BEGIN
   DBMS_OUTPUT.put_line(emp_action_pkg.v_deptno);
END;

DELETE FROM dept WHERE deptno=45;

--代码14.7 使用SERIALLY_RESUABLE编译提示
--定义包规范
CREATE OR REPLACE PACKAGE emp_action_pkg IS
   PRAGMA SERIALLY_REUSABLE;            --指定编译提示
   v_deptno NUMBER(3):=20;              --包公开的变量
   --定义一个增加新员工的过程
   PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE,     --部门名称
       p_loc      dept.loc%TYPE        --位置
    );   
    --定义一个获取员工加薪数量的函数
    FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
       RETURN NUMBER;    
END emp_action_pkg;

--定义包体
CREATE OR REPLACE PACKAGE BODY emp_action_pkg IS
   PRAGMA SERIALLY_REUSABLE;            --指定编译提示
  --公开,实现包规范中定义的newdept过程
  PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE,     --部门名称
       p_loc      dept.loc%TYPE        --位置
    )
    AS
       v_deptcount   NUMBER;           --保存是否存在员工编号
       BEGIN
       SELECT COUNT (*) INTO v_deptcount FROM dept
        WHERE deptno = p_deptno;       --查询在dept表中是否存在部门编号
       IF v_deptcount > 0              --如果存在相同的员工记录
       THEN                            --抛出异常
          raise_application_error (-20002, '出现了相同的员工记录');
       END IF;
       INSERT INTO dept(deptno, dname, loc)  
            VALUES (p_deptno, p_dname, p_loc);--插入记录
    END newdept;
    --公开,实现包规范中定义的getraisedsalary函数
    FUNCTION getraisedsalary (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 '职员' THEN
             v_salaryratio := 1.09;
          WHEN '销售人员' THEN
             v_salaryratio := 1.11;
          WHEN '经理' THEN
             v_salaryratio := 1.18;
          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;                             --如果没找到原工记录,返回0
     END getraisedsalary;
    --私有,该函数在包规范中并不存在,只能在包体内被引用
    FUNCTION checkdeptno(p_deptno dept.deptno%TYPE) RETURN NUMBER
    AS
      v_counter NUMBER(2);
    BEGIN
       SELECT COUNT(*) INTO v_counter FROM dept WHERE deptno=p_deptno;
       RETURN v_counter; 
    END;           
END emp_action_pkg;   



--包的重新编译
ALTER PACKAGE emp_action_pkg COMPILE BODY;           --编译包体
ALTER PACKAGE emp_action_pkg COMPILE PACKAGE;        --编译包规范和包体
ALTER PACKAGE emp_action_pkg COMPILE SPECIFICATION;  --编译包规范

--查看包对象和包体对象
 SELECT object_type 对象类型, object_name 对象名称, status 状态
  FROM user_objects
  WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
  ORDER BY object_type, status, object_name;
  
--查看包规范的源代码  
SELECT   line, text
    FROM user_source
   WHERE NAME = 'EMP_ACTION_PKG' AND TYPE = 'PACKAGE'
ORDER BY line;

--查看包体的源代码  
SELECT   line, text
    FROM user_source
   WHERE NAME = 'EMP_ACTION_PKG' AND TYPE = 'PACKAGE BODY'
ORDER BY line;

--代码14.8 在包中重载子程序
--定义包规范
CREATE OR REPLACE PACKAGE emp_action_pkg_overload IS
   --定义一个增加新员工的过程
   PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE,     --部门名称
       p_loc      dept.loc%TYPE        --位置
    );   
   --定义一个增加新员工的过程,重载过程
   PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE     --部门名称
    );       
    --定义一个获取员工加薪数量的函数
    FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
       RETURN NUMBER;
       
    --定义一个获取员工加薪数量的函数,重载函数
    FUNCTION getraisedsalary (p_ename emp.ename%TYPE)
       RETURN NUMBER;                  
END emp_action_pkg_overload;

--代码14.9 包含重载子程序的包体实现
--定义包体
CREATE OR REPLACE PACKAGE BODY emp_action_pkg_overload IS
  --公开,实现包规范中定义的newdept过程
  PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE,     --部门名称
       p_loc      dept.loc%TYPE        --位置
    )
    AS
       v_deptcount   NUMBER;           --保存是否存在员工编号
       BEGIN
       SELECT COUNT (*) INTO v_deptcount FROM dept
        WHERE deptno = p_deptno;       --查询在dept表中是否存在部门编号
       IF v_deptcount > 0              --如果存在相同的员工记录
       THEN                            --抛出异常
          raise_application_error (-20002, '出现了相同的员工记录');
       END IF;
       INSERT INTO dept(deptno, dname, loc)  
            VALUES (p_deptno, p_dname, p_loc);--插入记录
    END newdept;
    
  PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE     --部门名称
    )
    AS
       v_deptcount   NUMBER;           --保存是否存在员工编号
       BEGIN
       SELECT COUNT (*) INTO v_deptcount FROM dept
        WHERE deptno = p_deptno;       --查询在dept表中是否存在部门编号
       IF v_deptcount > 0              --如果存在相同的员工记录
       THEN                            --抛出异常
          raise_application_error (-20002, '出现了相同的员工记录');
       END IF;
       INSERT INTO dept(deptno, dname, loc)  
            VALUES (p_deptno, p_dname, '中国');--插入记录
    END newdept;    
    
    --公开,实现包规范中定义的getraisedsalary函数
    FUNCTION getraisedsalary (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 '职员' THEN
             v_salaryratio := 1.09;
          WHEN '销售人员' THEN
             v_salaryratio := 1.11;
          WHEN '经理' THEN
             v_salaryratio := 1.18;
          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;                             --如果没找到原工记录,返回0
     END getraisedsalary;
     
    --重载函数的实现
    FUNCTION getraisedsalary (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 '职员' THEN
             v_salaryratio := 1.09;
          WHEN '销售人员' THEN
             v_salaryratio := 1.11;
          WHEN '经理' THEN
             v_salaryratio := 1.18;
          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;                             --如果没找到原工记录,返回0
     END getraisedsalary;     
     
    --私有,该函数在包规范中并不存在,只能在包体内被引用
    FUNCTION checkdeptno(p_deptno dept.deptno%TYPE) RETURN NUMBER
    AS
      v_counter NUMBER(2);
    BEGIN
       SELECT COUNT(*) INTO v_counter FROM dept WHERE deptno=p_deptno;
       RETURN v_counter; 
    END;           
END emp_action_pkg_overload;   


--重载过程调用示例
DECLARE
   v_sal NUMBER(10,2);
BEGIN
   emp_action_pkg_overload.newdept(43,'样品部','东京');          --重载过程使用示例
   emp_action_pkg_overload.newdept(44,'纸品部');
   v_sal:=emp_action_pkg_overload.getraisedsalary(7369);         --重载函数使用示例
   v_sal:=emp_action_pkg_overload.getraisedsalary('史密斯');
END;


--代码14.10 包体初始化单元示例
--定义包头,在包头中定义要公开的成员
CREATE OR REPLACE PACKAGE InitTest IS
   TYPE emp_typ IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
   CURSOR emp_cur RETURN emp%ROWTYPE;      --定义游标
   curr_time NUMBER;                       --当前秒数
   emp_tab emp_typ;                        --定义集合类型的变量
   --定义一个增加新员工的过程
   PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE,     --部门名称
       p_loc      dept.loc%TYPE        --位置
    );   
    --定义一个获取员工加薪数量的函数
    FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
       RETURN NUMBER;         
END InitTest;
--定义包体,在包体的初始化区域对包进行初始化
CREATE OR REPLACE PACKAGE BODY InitTest IS
   row_counter NUMBER:=1;
   CURSOR emp_cur RETURN emp%ROWTYPE IS
      SELECT * FROM emp ORDER BY sal DESC; --定义游标体         
    --定义一个增加新员工的过程
   PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE,     --部门名称
       p_loc      dept.loc%TYPE        --位置
    ) AS
    BEGIN
       NULL;
    END newdept;
    --定义一个获取员工加薪数量的函数
    FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
       RETURN NUMBER IS
    BEGIN
       NULL;
    END getraisedsalary;
BEGIN    
    --包初始化部分,定义包的代码
    SELECT TO_NUMBER(TO_CHAR(SYSDATE,'SSSSS')) INTO curr_time FROM dual;
     FOR emp_row IN emp_cur LOOP
       emp_tab(row_counter):=emp_row;          --为集合赋值
       row_counter:=row_counter+1;
    END LOOP;   
EXCEPTION 
    WHEN OTHERS THEN 
       DBMS_OUTPUT.put_line('出现了异常');               
END InitTest;

SELECT   name,line, POSITION, text
    FROM user_errors
   WHERE NAME = 'INITTEST'
ORDER BY SEQUENCE;


DECLARE
   v_time NUMBER;
BEGIN
   v_time:=InitTest.curr_time;              --获取当前的时间秒数
   --输出索引表中的员工名称,以及当前的秒数(应该是初始化时的秒数)。
  DBMS_OUTPUT.put_line(InitTest.emp_tab(1).ename||' '||v_time);
END;



该文章由WP-AutoPost插件自动采集发布

原文地址:http://blog.csdn.net/hpdlzu80100/article/details/51997140

未经允许不得转载:SRE空间 » 源码-PL/SQL从入门到精通-第十四章-包-Part 1

分享到:更多 ()

评论 抢沙发

评论前必须登录!

 

oracle

联系我们联系我们