源码-PL/SQL从入门到精通-第十四章-包-Part 2-CSDN博客

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/52025112
作为PL/SQL的进阶内容，本章（包）的干货还是挺多的，如，纯度级别、管道、作业、报警等，基本都是初次接触，感觉Oracle的功能的确强大。
在调试管道相关的示例时，由于教材中的说明不够详细、代码中存在错误、疏漏，我只能边调试边在万能的网上查找一些自己解决不掉的问题，最终，耗时1个多小时后，调试成功。
代码如下：
--代码14.11 定义包中函数的纯度级别
CREATE OR REPLACE PACKAGE purityTest IS
   TYPE dept_typ IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
   dept_tab dept_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;    
    --设置纯度级别
    PRAGMA RESTRICT_REFERENCES(newdept,WNPS);                   --限制函数不能修改包变量，也不能给包变量赋值
    PRAGMA RESTRICT_REFERENCES(getraisedsalary,WNDS,WNPS,RNPS);           
END purityTest;

--代码14.13 违反包纯度级别的包体示例
--定义包体，在包体的初始化区域对包进行初始化
CREATE OR REPLACE PACKAGE BODY purityTest IS
    --定义一个增加新员工的过程
   PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE,     --部门名称
       p_loc      dept.loc%TYPE        --位置
    ) AS dept_row dept%rowtype;
    BEGIN
       dept_row.deptno :=p_deptno;  
       dept_row.dname :=p_dname; 
       dept_row.loc :=p_loc;   
       dept_tab(1) :=dept_row;
    END newdept;
    --定义一个获取员工加薪数量的函数
    FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
       RETURN NUMBER IS
       v_sal NUMBER;
    BEGIN      
      update emp set sal=sal*1.12 where empno=p_empno;
       SELECT sal INTO v_sal FROM emp WHERE empno=p_empno;
       RETURN v_sal*1.12;
    END getraisedsalary;               
END purityTest;


--代码14.13 符合包纯度级别的包体示例
--定义包体，在包体的初始化区域对包进行初始化
CREATE OR REPLACE PACKAGE BODY purityTest IS
    --定义一个增加新员工的过程
   PROCEDURE newdept (
       p_deptno   dept.deptno%TYPE,    --部门编号
       p_dname    dept.dname%TYPE,     --部门名称
       p_loc      dept.loc%TYPE        --位置
    ) AS
    BEGIN
       INSERT INTO dept VALUES(p_deptno,p_dname,p_loc);  
    END newdept;
    --定义一个获取员工加薪数量的函数
    FUNCTION getraisedsalary (p_empno emp.empno%TYPE)
       RETURN NUMBER IS
       v_sal NUMBER;
    BEGIN      
       SELECT sal INTO v_sal FROM emp WHERE empno=p_empno;
       RETURN v_sal*1.12;
    END getraisedsalary;               
END purityTest;



SELECT empno 员工编号, puritytest.getraisedsalary (empno) 调薪后, sal 调薪前
  FROM emp
 WHERE deptno = 20;
 
 
--14.2.4 包权限设置
GRANT EXECUTE ON scott.purityTest TO userb;     --为userb分配执行权限


--定义包规范
CREATE OR REPLACE PACKAGE emp_action_pkg 
   AUTHID CURRENT_USER 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;


--代码14.15 在包规范中声明游标
--定义包规范，包规范将被用于应用程序的接口部分，供外部调用
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;
   --定义一个游标，并具有游标体   
   CURSOR emp_cur(p_deptno IN dept.deptno%TYPE) IS
      SELECT * FROM emp WHERE deptno=p_deptno;   
   --定义雇佣员工的过程
   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.16 在包体中定义游标查询
--定义包体
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
      FOR emp_salrow IN desc_salary LOOP
        DBMS_OUTPUT.put_line(emp_salrow.emp_no||'： '||emp_salrow.sal); 
      END LOOP;
   END;       
   --定义解雇员工的具体实现                 
   PROCEDURE fire_employee(p_emp_id NUMBER ) IS
   BEGIN
      --从emp表中删除员工信息
      DELETE FROM emp WHERE empno=p_emp_id;
      FOR emp_row IN emp_cur(20) LOOP
        DBMS_OUTPUT.put_line(emp_row.empno||' '||emp_row.deptno);
      END LOOP;
   END;          
END emp_pkg;

--查看包对象
 SELECT object_type 对象类型, object_name 对象名称, status 状态
      FROM user_objects
      WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
      ORDER BY object_type, status, object_name;
          
      
--删除包规范和包体      
DROP PACKAGE purityTest;    
--删除包体
DROP PACKAGE body purityTest;    


--代码14.17 包依赖性示例
--定义包规范，包规范将被用于应用程序的接口部分，供外部调用
CREATE OR REPLACE PACKAGE emp_pkg_dependency AS
   --定义雇佣员工的过程
   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_dependency;


--定义包体
CREATE OR REPLACE PACKAGE BODY emp_pkg_dependency
AS
   --定义雇佣员工的具体实现
   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); 
    INSERT INTO emp_history 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_dependency;

--查询包规范、包体及表的有效性
SELECT object_name, object_type, status
  FROM user_objects
 WHERE object_name IN ('EMP_PKG_DEPENDENCY', 'EMP');
 
DROP TABLE emp_history;
 
 
CREATE TABLE emp_history AS SELECT * FROM emp;
 
 
 
 SELECT * from user_dependencies WHERE REFERENCED_NAME='EMP_PKG_DEPENDENCY'
 

SELECT NAME, TYPE, referenced_name, referenced_type
  FROM user_dependencies
 WHERE NAME = 'EMP_PKG_DEPENDENCY';
 
--启用DBMS_output
set serverouput on;
dbms_output.enable(buffzer_size in Integer default 20000); 

--代码14.18 使用PUT、PUT_LINE和GET_LINE的示例）
DECLARE
   v_line1 VARCHAR(200);                  
   v_line2 VARCHAR(200);    
   v_status NUMBER;
 BEGIN
    DBMS_OUTPUT.ENABLE;                                              --开启DBMS_OUTPUT
    DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT主要用于输出信息，它包含：');  --写入并换行
    DBMS_OUTPUT.PUT('PUT_LINE');                                     --写入文本不换行
    DBMS_OUTPUT.PUT(',PUT_LINE');
    DBMS_OUTPUT.PUT(',PUTE');
    DBMS_OUTPUT.PUT(',NEW_LINE');
    DBMS_OUTPUT.PUT(',GET_LINE');
    DBMS_OUTPUT.PUT(',GET_LINES等过程');
    DBMS_OUTPUT.NEW_LINE;                                           --在文本最后加上换行符（注掉之后几行也能输出相同内容）
    DBMS_OUTPUT.GET_LINE(v_line1,v_status);  
    DBMS_OUTPUT.GET_LINE(v_line2,v_status);                         --获取缓冲区中的数据行
    DBMS_OUTPUT.PUT_LINE(v_line1);                                  --输出变量的值到缓冲区
    DBMS_OUTPUT.PUT_LINE(v_line2);       
 END; 
 
--代码14.19 使用PUT、PUT_LINE和GET_LINES的示例 
DECLARE
   v_lines DBMS_OUTPUT.CHARARR;                                     --定义集合类型的变量
   v_status NUMBER;
 BEGIN
    DBMS_OUTPUT.ENABLE;                                             --开启DBMS_OUTPUT
    DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT主要用于输出信息，它包含：');  --写入并换行
    DBMS_OUTPUT.PUT('PUT_LINE');                                     --写入文本不换行
    DBMS_OUTPUT.PUT(',PUT_LINE');
    DBMS_OUTPUT.PUT(',PUTE');
    DBMS_OUTPUT.PUT(',NEW_LINE');
    DBMS_OUTPUT.PUT(',GET_LINE');
    DBMS_OUTPUT.PUT(',GET_LINES等过程');
    DBMS_OUTPUT.NEW_LINE;                                           --在文本最后加上换行符     
    DBMS_OUTPUT.GET_LINES(v_lines,v_status);                        --获取缓冲区中所有的行
    FOR i IN 1..v_status LOOP
       DBMS_OUTPUT.PUT_LINE(v_lines(i));                            --输出集合中所有的数据行
    END LOOP;   
 END; 

--14.4.2 使用DBMS_PIPE 
--创建管道
declare
   flag int;
begin
   flag :=dbms_pipe.create_pipe('public_pipe',8192,false);
   if flag=0 then
      dbms_output.put_line('建立公用管道成功');
   end if;
end;

--代码14.20 使用Pack_message缓冲信息（原代码中有错误，已修正）
DECLARE
   v_ename emp.ename%TYPE;
   v_sal emp.sal%TYPE;
   v_rowid ROWID;
   v_empno emp.empno%TYPE:=&empno;
BEGIN
   SELECT rowid,ename,sal,empno INTO v_rowid,v_ename,v_sal,v_empno FROM emp WHERE empno=v_empno;
   DBMS_PIPE.pack_message('员工编号：'||v_empno||' 员工名称：'||v_ename);
   DBMS_PIPE.pack_message('员工薪资：'||v_sal||' ROWID值：'||v_rowid);
END;

select * from emp;

--代码14.21 使用Send_message发送消息
DECLARE 
   v_sendflag INT;                                          --发送标识变量
BEGIN
   v_sendflag:=DBMS_PIPE.send_message('PUBLIC_PIPE');    --向管道发送消息，如果管道不存在则创建管道
   IF v_sendflag=0 THEN
      DBMS_OUTPUT.PUT_LINE('消息成功发送到管道');        --如果消息成功发送，则提示成功消息
   END IF;
END;


--代码14.22 使用Receive_message接受消息
DECLARE 
   v_receiveflag INT;                                              --接收标识变量
BEGIN
   v_receiveflag:=DBMS_PIPE.receive_message('PUBLIC_PIPE');    --从管道接收消息，如果管道不存在则创建管道
   IF v_receiveflag=0 THEN
      DBMS_OUTPUT.PUT_LINE('成功的从管道中获取消息');           --如果消息成功接收，则提示成功消息
   END IF;
END;

--代码14.23 使用unpack_message读取信息（此处代码有增强，使用循环输出管道中的所有信息）
DECLARE
   v_message VARCHAR2(100);
   v_count int :=0;
BEGIN
   loop  
   DBMS_PIPE.unpack_message(v_message); --将缓冲区的内容写入到变量
   DBMS_OUTPUT.PUT_LINE(v_message);         --显示缓冲区的内容
   v_count :=v_count+1;
   exit when v_count=2;
   end loop;
END;

--删除管道remove_pipe
declare
   flag int;
begin
   flag :=dbms_pipe.remove_pipe('public_pipe');
   if flag=0 then
      dbms_output.put_line('删除公用管道成功');
   end if;
end;

--清除管道内容purge
--复位管道缓冲区reset_buffer

--代码14.24 管道使用示例
--发送管道消息
CREATE OR REPLACE PROCEDURE send_pipe_message (pipename VARCHAR2,message VARCHAR2)
IS
   flag INT;
BEGIN     
 flag := dbms_pipe.create_pipe(pipename,8192,false);  --创建管道
   if flag=0 THEN                                      --如果管道创建成功
      DBMS_PIPE.pack_message(message);              --将消息写到本地缓冲区
      flag :=DBMS_PIPE.send_message(pipename);       --将本地缓冲区中的消息发送到管道
   END IF;
END;

--从管道中接收消息
CREATE OR REPLACE PROCEDURE receive_pipe_message(p_pipename VARCHAR2,p_message IN OUT VARCHAR2)
IS
   flag INT;
BEGIN
   flag :=DBMS_PIPE.receive_message(p_pipename);  --从管道中获取消息，保存到缓冲区
   IF flag=0 THEN
      DBMS_PIPE.unpack_message(p_message);       --从缓冲区读取消息
      flag :=DBMS_PIPE.remove_pipe(p_pipename);    --移除管道
   END IF;
END;

SELECT   name,line, POSITION, text
     FROM user_errors
    WHERE NAME = 'RECEIVE_PIPE_MESSAGE'
 ORDER BY SEQUENCE;
 
--在scott会话中，发送管道消息
begin
SEND_PIPE_MESSAGE('pipe_demo','第一次学习管道，挺好玩的，你也玩玩看？');
end;

--授权给hr(使用sysdba权限)
BEGIN
  grant EXECUTE ON scott.receive_pipe_message TO hr;
END;

--在hr会话中，接受管道消息
DECLARE
  v_message VARCHAR2(300);
BEGIN
  scott.receive_pipe_message('pipe_demo', v_message);
  dbms_output.put_line(v_message);
END;


--14.4.3 使用DBMS_ALERT包
--授权(原代码有错，已修正)
grant execute on dbms_alert to scott;


--代码14.25 等待报警示例
DECLARE
   v_alertname   VARCHAR2 (30)  := 'alert_demo';    --报警名称
   v_status      INTEGER;                           --等待状态
   v_msg         VARCHAR2 (200);                    --报警消息
BEGIN
    --注册报警，指定报警名为alert_demo
   DBMS_ALERT.REGISTER (v_alertname);
   --监听报警，等待报警发生
   DBMS_ALERT.waitone (v_alertname, v_msg, v_status);
   --如果不返回0，表示报警示败
   IF v_status != 0
   THEN
      DBMS_OUTPUT.put_line ('error');        --显示错误消息
   END IF;
   DBMS_OUTPUT.put_line (v_msg);             --显示报警消息
END;


--代码14.26 产生报警示例
DECLARE
   v_alertname   VARCHAR2 (30) := 'alert_demo';
BEGIN
    --向报警alert_demo发送报警信息
   DBMS_ALERT.signal (v_alertname, 'dbms_alert测试!');
   COMMIT;             --触发报警，如果是ROLLBACK，则不触发报警。
END;


--代码14.27 定义一个作业
DECLARE
   v_jobno   NUMBER;
BEGIN
   DBMS_JOB.submit
        (v_jobno,                             --作业编号
          --作业执行的过程
         'DBMS_DDL.analyze_object(''TABLE'',''SCOTT'',''EMP'',''COMPUTE'');',
         --下一次执行的日期         
         SYSDATE,
         --执行的时间间隔，表示24小时。
         'SYSDATE+1'
        );  
   DBMS_OUTPUT.put_line('获取的作业编号为：'||v_jobno);  --输出作业编号
   COMMIT;
END;

--查询数据字典查看作业信息
SELECT job, next_date, next_sec, INTERVAL, what
  FROM user_jobs
 WHERE job = 23
 
--以下语句未调试成功，教材中说明不够详细，且代码和教材内容不匹配，暂且留着
SELECT TO_DATE ('2011-10-15', 'YYYY-MM-DD')
  FROM DUAL;

select * from emp;

begin
DBMS_JOB.WHAT(23,'emp_pkg_dependency.fire_employee(7369)'); 
end;

function date_to_run_emp_job
return date
is
  mydate date;
begin
  if to_char(sysdate,'D') < 4 --Wednesday is the 4th day of the week in Oracle
  then
    mydate := trunc(sysdate,'W')+2+15/24 ; --Monday is the 1st day of week
  elsif to_char(sysdate,'D')=4 and sysdate < trunc(sysdate)+15/24 then
    --ie. it's Wednesday but it's before 3 pm
    mydate := trunc(sysdate,'W')+2+15/24 ;
  else
    mydate := trunc(sysdate,'W')+4+17/24 ; --Friday at 5 pm
  end if;  
  return mydate;
end;
/