Oracle学习1213程序包

一、了解 oracle 11g 的内置程序包

1，程序包是对相关过程、函数、变量、游标和异常等对象的封装，程序包由规范和主体两部分组成

2，程序包规范：

CREATE [OR REPLACE] PACKAGE package_name

IS | AS

[Public item declarations]

[Subprogram specification]

END [package_name];

3，程序包主体：

CREATE [OR REPLACE] PACKAGE BODY package_name 

IS | AS

[Private item declarations]

[Subprogram bodies]

[BEGIN Initialization]

END [package_name];

4，程序包的优点：模块化，更轻松的应用程序设计，信息隐藏，新增功能，性能更佳

二、掌握建立 oracle 11g 的程序包的方法

create or replace package pack1  --创建包头

is

aa number :=5;

procedure insert_student(a1 in student%rowtype);

procedure update_student(a2 in student%rowtype);

end pack1;

create or replare package body pack1  --声明包体

is

bb number :=5;

procedure insert_student(a1 in student%rowtype)

is

begin

insert into student(sno,sname,sage) values(a1.sno, a1.sname, a1.sage);

commit;

dbms_output.put_line(pack1.bb);

end insert_student;

procedure update_student(a2 in student%rowtype)

is

begin 

update student set sname=a2.sname where sno=a2.sno;

commit;

end update_student;

end pack1;

 使用程序包；例1：

declare

a1 student%rowtype;

begin

a1.sno := 7;

a1.sname := 'AA';

a1.sage := 27;

pack1.insert_student(a1);

end;

 使用程序包；例2：

declare

a2 student%rowtype;

begin

a2.sno := 7;

a2.sname := 'BB';

a2.sage := 27;

pack1.update_student(a2);

end;

三、掌握在 oracle 11g 的程序包中使用游标的方法

1，游标：分为游标规范和游标主体两部份，在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型，

2，RETURN 子句指定的数据类型可以是：

2-1，用 %ROWTYPE 属性引用表定义的记录类型；

2-2，程序员定义的记录类型，例如 TYPE EMPRECTYP IS RECORD(emp_id INTEGER.salary REAL) 来定义的；

2-3，不可以是 number, varchar2, %TYPE 等类型。

3，在程序包内，使用显式游标；例：

create or replace package pack1 is

cursor mycursor return student%rowtype;  --不在程序包中是：is select * from student;

procedure mycursor_use;

end pack1;

create or replace package body pack1 is

cursor mycursor return student%rowtype is select * from student;

procedure mycursor_use

is

stu_rec student%rowtype;

begin

open mycursor;

fetch mycursor into stu_rec;

where mycursor%found loop

dbms_output.put_line('学号是：' || stu_rec.sno);

fetch mycursor into stu_rec;

end loop;

close mycursor;

end mycrusor_use;

end pack1;

使用：exec pack1.mycursor_use;

3，在程序包内，使用 REF 游标；例：

create or replace package pack2 is

type refcur is ref cursor;

procedure mycursor_use;

end pack2;

create or replace package body pack2 is

procedure mycursor _use

is

mycursor refcur;

stu_rec student%rowtype;

begin

open refcur for select * from student;

fetch mycursor into stu_rec;

dbms_output.put_line('学号是：' || stu_rec.sno || '，姓名：' || stu_rec.sname);

fetch mycursor into stu_rec;

end loop;

close mycursor;

end mycursor_use;

end pack2;

使用：exec pack2.mycursor_use;

四、了解 oracle 11g 中的常用的程序包的使用

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

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

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

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

3，ORACLE 内置程序包：扩展数据库的功能，为 PL/SQL 提供对 SQL 功能的访问，用户 SYS 拥有所有程序包，是公有同义词；

4，ORACLE 常用内置程序包：如下图

4-1，UTL_FILE 包指定文件路径依赖于 DIRECTORY 对象；例：

CREATE DIRECTORY TEST_DIR AS 'C:\DEVELOP';

GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;

5，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()：应许在工作执行时重新设置此正在运行的命令；

5-1，测试DBMS_Job 包；例：

create table a(a date);

create or replace procedure test as  --创建一个程序，向 a 表中插入当前时间的记录

begin

insert into a values(sysdate);

end;

variable job1 number;  --声明一个可变的量

begin

dbms_job.submit(:job1,'test;', sysdate, 'sysdate+1/1440'); --表示在现在时间的基础上，每隔一分钟执行一次 test 程序

end;

begin

dbms_job.run(:job1);  --开始执行

end;

begin

dbms_job.remove(:job1);  --删除任务

end;

5-2，测试UTL_FILE 包；例：

select * from all_directories; --查询当前用户下的所有目录

drop directory 目录名;

create directory test_dir as 'd:\dir1';  --创建目录必须由 system 及以上权限用户使用

grant read, write on directory test_dir to scott;  --赋权给 scott 用户

create or replace procedure read_txtfile(path in varchar2, name varchar2) as

1_output utl_file.file_type;

str varchar2(2000);

begin

1_output := utl_file.fopen(path, name, 'r', 2000);

loop

utl_file.get_line(1_output, str);

dbms_output.put_line(str);

end loop;

utl_file.fclose(1_output);

exception

when no_data_found then utl_file.fclose(1_output);

when utl_file.invalid_path then raise_application_error(-20001, 'INVALID_PATH!');

when utl_file.invalid_filehandle then raise_application_error(-20002,'INVALID_MODE');

......一堆异常

end;

使用：exec read_txtfile('TEST_DIR', '1.txt');

5-3，DBMS_RANDOM的用法：

例：产生一个9位随机整数

SELECT DBMS_RANDOM.RANDOM FROM DUAL;

例：产生一个100以内的随机整数

SELECT ABS(MOD(DBMS_RANDOM.RANDOM, 100)) FROM DUAL;

5-3-1，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;

5-3-2，STRING 函数生成随机文本字符串，可以指字字符串的类型的所希望的长度。例：

SELECT DBMS_RANDOM.STRING('P', 20) FROM DUAL;

另外：‘U’ 用来生成大写字符，'L' 用来生成小写字符，'A' 用来生成大小写混合的字符，’P' 表示字符串由任意可打印字符构成，‘X' 表示字符串由大写字符和数字构成；

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

A：SELECT TO_CHAR(TO_DATE('01/01/18', 'mm/dd/yy'), 'J') FROM DUAL;

B：SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(A步骤查到的数，A步骤查到的数+364)), 'J') FROM DUAL;

SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2458120，2458120+364)), 'J') FROM DUAL;