oracle 内置程序包，建立程序包的方法，程序包中使用游标，常用程序包

1. 程序包

1.1 程序包定义

  程序包是对相关过程、函数、游标、异常等对象的封装；程序包由规范和主体2部分组成：

  规范：
声明程序包中公共对象，包括类型、变量、异常、游标规范和子程序规范；

  主体：
声明程序包私有对象和实现在包规范中声明的子程序和游标;

  项目中都是建立程序包，之后再建立过程、函数

---

1.2 程序包创建

规范：

CREATE [OR REPLACE] PACKAGE package_name
  [AUTHID {CURRENT_USER | DEFINER}]
  {IS | AS}
  [公有数据类型定义[公有数据类型定义]…]
  [公有游标声明[公有游标声明]…]
  [公有变量、常量声明[公有变量、常量声明]…]
  [公有函数声明[公有函数声明]…]
  [公有过程声明[公有过程声明]…]
END [package_name];

主体

CREATE [OR REPLACE] PACKAGE BODY package_name
  {IS | AS}
  [私有数据类型定义[私有数据类型定义]…]
  [私有变量、常量声明[私有变量、常量声明]…]
  [私有异常错误声明[私有异常错误声明]…]
  [私有函数声明和定义[私有函数声明和定义]…]
  [私有函过程声明和定义[私有函过程声明和定义]…]
  [公有游标定义[公有游标定义]…]
  [公有函数定义[公有函数定义]…]
  [公有过程定义[公有过程定义]…]
BEGIN
  执行部分(初始化部分)
END package_name;

如：
(1) 插入更新scott中emp表

create or replace package pack1
	is 
	a int:=9;
	procedure insert_emp(a1 in scott.emp%rowtype);
	procedure update_emp(a2 in scott.emp%rowtype);
	end pack1;
	/

create or replace package body pack1
	is 
	b int:=5;
	 
	procedure insert_emp(a1 in scott.emp%rowtype)
	is 
	begin 
	 insert into scott.emp values (a1.EMPNO,'fd','fd',7902,null,8000,null,20); 
	 commit;
	 dbms_output.put_line('pack1中a变量'||pack1.a);
	 end insert_emp;

	procedure update_emp(a2 in scott.emp%rowtype)
	is 
	begin 
	 update scott.emp set ENAME=a2.ENAME where EMPNO=a2.EMPNO;
	 commit;
	 end update_emp;

	end pack1;
	/

公有类型变量在包外可访问，私有类型变量在包外不能访问

declare
	a  scott.emp%rowtype;
	begin
	a.EMPNO:=6666;
	pack1.insert_emp(a);
	end;
	/

declare
	a  scott.emp%rowtype;
	begin
	a.EMPNO:=6666;
	a.ENAME:='jack';
	pack1.update_emp(a);
	end;
	/

---

1.3 程序包中的游标

  游标的定义分为游标规范和游标主体2部分；在包规范中声明游标规范必须使用RETURN子句指定游标的返回类型；

RETURN子句指定的数据类型可以是：
  用%rowtype属性引用表定义的记录类型；
  程序员自定义的记录类型，如 type emprectyp is record(emp_id integer,salary real)来定义的；
  不可是number，varchar2,%type类型

如
（1）在程序包内使用显示游标

create or replace package pack2
	is
	cursor mycursor return scott.emp%rowtype;
	procedure mycursor_use;
	end pack2;
	/

create or replace package body pack2
	is
	
	cursor mycursor return scott.emp%rowtype is select * from scott.emp;
	
	procedure mycursor_use 
	is emp_rec  scott.emp%rowtype;
	begin
	open mycursor;
	fetch mycursor into emp_rec  ;
	while mycursor%found loop
	 dbms_output.put_line('姓名为'||emp_rec.ENAME );
	 fetch mycursor into emp_rec  ;
	end loop;
	end mycursor_use ;
	end pack2;
	/

（2）在程序包内使用REF游标

create or replace package pack3
	is
	 type refcur is ref cursor ;
	procedure mycursor_use;
	end pack3;
	/

create or replace package body pack3
	is
	
	procedure mycursor_use 
	is
	mycursor refcur;
	 emp_rec  scott.emp%rowtype;
	begin
	open mycursor for select * from scott.emp;
	fetch mycursor into emp_rec  ;
	while mycursor%found loop
	 dbms_output.put_line('姓名为'||emp_rec.ENAME );
	 fetch mycursor into emp_rec  ;
	end loop;
	end mycursor_use ;
	
	end pack3;
	/

---

1.4 有关程序包的信息

user_objects 视图中包含用户创建的程序包信息

select * from user_objects where object_type in('PACKAGE','PACKAGE BODY');

user_source视图存储源代码

select * from user_source where name='TEST';

2. 内置程序包

用户sys拥有所有程序包，是公有同义词；

程序包名称	说明
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的功能
UTL_FILE	用PL/SQL程序来读写操作系统文本文件

3. DBMS_JOB包

用于安排和管理作业队列,通过使用作业,可以使ORACLE数据库定期执行特定的任务。
DBMS_JOB说明

如
（1）每分钟调用一次过程

创建表和过程：

create table a3(a date);

create or replace procedure test1
	as
	begin
	insert into a3 values(sysdate);
	commit;
	end;
	/

定义变量接收job编号

variable job1 number;

提交任务:

	begin
	dbms_job.submit(:job1,'test1;',sysdate,'sysdate+1/1440');
	end;

–系统会自动分配一个任务号jobno。
删除job: dbms_job.remove(jobno);
修改要执行的操作: job:dbms_job.what(jobno, what);
修改下次执行时间：dbms_job.next_date(jobno, next_date);
修改间隔时间：dbms_job.interval(jobno, interval);
启动job: dbms_job.run(jobno);
停止job: dbms.broken(jobno, broken, nextdate); –broken为boolean值

启动任务

begin
	dbms_job.run(:job1);
	end;

删除任务

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

4. UTL_FILE包

用于读写操作系统的文件，前提是创建路径(Directory)并授权。ORACLE目录的作用就是让ORACLE数据库和操作系统之前进行文件的交互。为了创建目录，必须具有DBA角色或者赋予了CREATE ANY DIRECTORY权限。如果普通用户被赋予了CREATE ANY DIRECTORY权限，那么用户就自动具备目录的READ和WRITE权限
utl_file说明

点击链接，查看到UTL_FILE包的异常说明：

如
（1）
sys用户创建目录

create or replace directory TEST_DIR as 'D:\c'; 

查看目录

 select * from all_directories;

授权scott用户

--路径授权，添加对路径读、写权限
grant read,write on directory TEST_DIR to scott;
 
--utl_file包授权，添加执行权限
GRANT EXECUTE ON utl_file TO scott;

读取文件

create or replace procedure read_file (path in varchar2,name varchar2)
	as
	l_output UTL_FILE.file_type;
	str     varchar2(2000);
	begin 
	l_output :=UTL_FILE.fopen(path ,name,'r',2000);
	loop 
	UTL_FILE.get_line(l_output ,str);
	dbms_output.put_line(str );
	end loop;
	UTL_FILE.fclose(l_output );
	exception when NO_DATA_FOUND 
	then UTL_FILE.fclose(l_output );
	end;