源码-PL/SQL从入门到精通-第十四章-包-Part 2
来源：互联网 发布：视频剪辑软件免费下载 编辑：程序博客网 时间：2024/05/29 15:31
作为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_pkgAS --定义游标变量的具体类型 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_dependencyAS --定义雇佣员工的具体实现 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_outputset 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_pipedeclare 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会话中，发送管道消息beginSEND_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;beginDBMS_JOB.WHAT(23,'emp_pkg_dependency.fire_employee(7369)'); end;function date_to_run_emp_jobreturn dateis 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;/ 0 0
- 源码-PL/SQL从入门到精通-第十四章-包-Part 2
- 源码-PL/SQL从入门到精通-第十四章-包-Part 1
- 源码-PL/SQL从入门到精通-第二章-PL/SQL基本概念-Part 2
- 源码-PL/SQL从入门到精通-第十八章-PL/SQL性能优化建议-Part 2
- 源码-PL/SQL从入门到精通-第九章-SQL内置函数-Part 2
- 源码-PL/SQL从入门到精通-第十六章-动态SQL语句-Part 2
- 源码-PL/SQL从入门到精通-第三章-变量和类型-Part 2
- 源码-PL/SQL从入门到精通-第六章-查询数据表-Part 2
- 源码-PL/SQL从入门到精通-第八章-记录与集合-Part 2
- 源码-PL/SQL从入门到精通-第十章-使用游标-Part 2
- 源码-PL/SQL从入门到精通-第十二章-异常处理机制-Part 2
- 源码-PL/SQL从入门到精通-第十三章-子程序-Part 2
- 源码-PL/SQL从入门到精通-第十五章-触发器-Part 2
- 源码-PL/SQL从入门到精通-第十七章-面向对象编程-Part 2
- 源码-PL/SQL从入门到精通-第二章-PL/SQL基本概念-Part 1
- 源码-PL/SQL从入门到精通-第二章-PL/SQL基本概念-Part 3
- 源码-PL/SQL从入门到精通-第十八章-PL/SQL性能优化建议-Part 1
- 源码-PL/SQL从入门到精通-第九章-SQL内置函数-Part 1
- LeetCode#338 Counting Bits
- java mongo
- spring
- java设计模式之解释器模式
- 【IOS】UITableView固定headerView的方案
- 源码-PL/SQL从入门到精通-第十四章-包-Part 2
- JAVA面向对象-----包机制
- 数据库创建索引的注意事项
- java数组
- 一个简单的redis性能测试程序和性能测试结果
- yii2读写分离配置
- redis里能不能针对set数据的每个member设置过期时间？
- date命令
- C++ iostream 迭代器
