pl/sql之package

程序包规范:

CREATE [OR REPLACE] PACKAGE package_name IS|AS
	[Public item declarations]
	[Subprogram specification]
END [package_name];

程序包主体:

CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
	[Private item declarations]
	[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];

---

程序包中的游标:

游标的定义分为游标规范和游标主体两部分;
在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型;
RETURN子句指定的数据类型可以是：
    1, 用 %ROWTYPE 属性引用表定义的记录类型
    2, 程序员定义的记录类型，例如 TYPE EMPRECTYP IS RECORD(emp_id INTEGER,salary REAL)来定义的。
    3, 不可以是number, varchar2， %TYPE等类型。

例：

SQL> CREATE OR REPLACE PACKAGE BODY cur_pack AS
		 CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE IS 
			SELECT * FROM order_master WHERE VENCODE=vcode;
		 PROCEDURE ord_pro(vcode VARCHAR2) IS or_rec order_master%ROWTYPE;
		 BEGIN
			 OPEN ord_cur(vcode); 
			 LOOP
				 FETCH ord_cur INTO or_rec;
				 EXIT WHEN ord_cur%NOTFOUND;
				 DBMS_OUTPUT.PUT_LIne(’返回的值为' || or_rec.orderno);
			 END LOOP;
		 END ord_pro;
	 END cur_pack;
	 /

USER_OBJECTS ：视图包含用户创建的子程序和程序包的信息

SELECT object_name, object_type
FROM USER_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');

USER_SOURCE ：视图存储子程序和程序包的源代码

SELECT line, text 
FROM USER_SOURCE
WHERE NAME='TEST'; --注意大写

---

ORACLE内置程序包：

程序包名称	说明
STANDARD和DBMS_STANDARD	定义和扩展PL/SQL语言环境
DBMS_LOB	提供对 LOB数据类型进行操作的功能
DBMS_OUTPUT	处理PL/SQL块和子程序输出调试信息
DBMS_RANDOM	提供随机数生成器
DBMS_SQL	允许用户使用动态 SQL
DBMS_XMLDOM	用DOM模型读写XML类型的数据
DBMS_XMLPARSER	XML解析，处理XML文档内容和结构
DBMS_XMLQUERY	提供将数据转换为 XML 类型的功能
DBMS_XSLPROCESSOR	提供XSLT功能，转换XML文档
UTL_FILE	用 PL/SQL 程序来读写操作系统文本文件

例：

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
		 DBMS_OUTPUT.PUT_LINE('打印三角形');
		 FOR i IN 1..9 LOOP
			 FOR j IN 1..i LOOP
				DBMS_OUTPUT.PUT('*');
			 END LOOP for_j;
			 DBMS_OUTPUT.NEW_LINE;
		 END LOOP for_i;
	 END;
	 /

例：chr(10) 换行符，

select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置
select instr('helloworld','lo') from dual; --返回结果：4    即：在“lo”中，“l”开始出现的位置
select instr('helloworld','wo') from dual; --返回结果：6    即“w”开始出现的位置

格式一：instr( string1, string2 )    /   instr(源字符串, 目标字符串)
格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] )   /   instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

---

SQL> DECLARE
		 result CLOB;
		 xmlstr VARCHAR2(32767);
		 line   VARCHAR2(2000);
		 line_no INTEGER := 1;
	 BEGIN
		 result := DBMS_XMLQUERY.GETXML('SELECT empno, ename FROM employee');
		 xmlstr := DBMS_LOB.SUBSTR(result,32767);
		 LOOP
			 EXIT WHEN xmlstr IS NULL;
			 line := SUBSTR( xmlstr, 1 , INSTR( xmlstr, CHR(10)) - 1 );
			 DBMS_OUTPUT.PUT_LINE(line_no || ':' || line);
			 xmlstr := SUBSTR( xmlstr, INSTR( xmlstr, CHR(10)) + 1);
			 line_no := line_no + 1;
		 END LOOP;
	 END;
	 /

例: DBMS_RANDOM 包可用来生成随机整数

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
		 v_random  NUMBER;
		 counter   NUMBER;
	 BEGIN
		 counter := 1;
		 WHILE counter <= 10 LOOP
			 v_random := DBMS_RANDOM.RANDOM;
			 DBMS_OUTPUT.PUT_LINE(v_random);
			 counter := counter + 1;
		 END LOOP;
	 END;
	 /

---

`UTL_FILE` 包用于读写操作系统文本文件，操作文件的一般过程是打开、读或写、关闭，UTL_FILE 包指定文件路径依赖于 DIRECTORY 对象

SQL> CREATE DIRECTORY TEST_DIR AS 'C:\DEVELOP';
SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
		 input_file   UTL_FILE.FILE_TYPE;
		 input_buffer VARCHAR2(4000);
	 BEGIN
		 input_file := UTL_FILE.FOPEN('TEST_DIR', 'employees.xml', 'r');
		 LOOP
			 UTL_FILE.GET_LINE(input_file,input_buffer);
			 DBMS_OUTPUT.PUT_LINE(input_buffer);
		 END LOOP;
		 UTL_FILE.FCLOSE(input_file);
		 
		 EXCEPTION
			 WHEN
				NO_DATA_FOUND 
			 THEN
				DBMS_OUTPUT.PUT_LINE('------------------');
	 END;
	 /

---

DBMS_JOB包的用法

包含以下子过程：
BROKEN()过程更新一个已提交的工作的状态，典型地是用来把一个已破工作标记为未破工作。这个过程有三个参数：job 、broken与next_date。
CHANGE()过程用来改变指定工作的设置。这个过程有四个参数：job、what 、next_date与interval。
INTERVAL()过程用来显式地设置重执行一个工作之间的时间间隔数。
ISUBMIT()过程用来用特定的工作号提交一个工作.
NEXT_DATE()过程用来显式地设定一个工作的执行时间。这个过程接收两个参数：job与next_date。
REMOVE()过程来删除一个已计划运行的工作。这个过程接收一个参数。
RUN()过程用来立即执行一个指定的工作。这个过程只接收一个参数。
SUBMIT()过程，工作被正常地计划好。
USER_EXPORT()过程返回一个命令，此命令用来安排一个存在的工作以便此工作能重新提交。
WHAT()过程应许在工作执行时重新设置此正在运行的命令。

创建测试表

create table test_job(insert_time date);

创建一个自定义过程

create or replace procedure test_pro as
begin
	insert into test_job values(sysdate);
end;
/

创建JOB

variable job1 number;
begin
	dbms_job.submit(:job1,'test_pro;',sysdate,'sysdate+1/1440'); --每天1440分钟，即一分钟运行test过程一次
end;

运行JOB

begin
	dbms_job.run(:job1);
end;

检查结果

select to_char(insert_time,'yyyy/mm/dd hh24:mi:ss') 时间 from test_job; 

删除JOB

begin
	dbms_job.remove(:job1);
end;
/

–查看job下次执行时间以及间隔时间
select * from dba_jobs where job = '774';

–启动job
exec dbms_job.run(774);

–停用job
exec dbms_job.broken(job#,true);
exec dbms_job.broken(774, true);

–查看当前用户下面有多少job执行
select job,what,failures,broken from user_jobs where job = '774' ;

–查看正在运行的job
select * from dba_jobs_running where job = '774';

---

产生一个9位的随机整数:
SELECT DBMS_RANDOM.RANDOM FROM DUAL;

产生一个100以内的随机整数:
SELECT ABS(MOD(DBMS_RANDOM.RANDOM,100)) FROM DUAL;

VALUE函数会返回一个大于等于0但是小于1的数:
SELECT DBMS_RANDOM.VALUE FROM DUAL;

对于指定范围内的整数，要加入参数low_value和high_value，并从结果中截取小数(最大值不能被作为可能的值),产生一个0到99之间的小数:
SELECT DBMS_RANDOM.VALUE(0, 100) FROM DUAL;

产生0到99之间的整数:
SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM DUAL;

STRING函数生成随机文本字符串，可以指定字符串的类型和所希望的长度：
SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;

下面是一些类型的代码：
'U'用来生成大写字符，
'L'用来生成小写字符，
'A'用来生成大小写混合的字符，
'P'表示字符串由任意可打印字符构成，
'X'表示字符串由大写字符和数字构成。

返回某年内的随机日期，分两步：

1) 
 SELECT TO_CHAR(TO_DATE('01/01/03','mm/dd/yy'),'J') 
 FROM DUAL; 
2) 
 SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2452641,2452641+364)),'J') 
 FROM DUAL;