程序包
概述
程序包是对相关过程、函数、变量、游标和异常等对象的封装
程序包由规范(包头)和主体(包体)两部分组成
规范中可以声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等
其语法格式如下:
create [or replace] package 包名 is
[declare_variable]; -- 规范内声明变量
[declare_type]; -- 规范内声明类型
[declare_cursor]; -- 规范内声明游标
[declare_function]; -- 规范内声明函数 只能定义函数和返回类型不包括函数体
[declare_procedure];-- 规范内声明存储过程 只能定义参数不包括存储过程主体
end [包名];
主体中可以声明程序包私有对象和实现在包规范中声明的子程序和游标
与创建‘规范’不同的是，创建‘程序包主体’使用 create [or replace] package body 语句，这一点需要注意：
create [or replace] package body 包名 is
[inner_variable] -- 程序包内部的变量
[cursor_body] -- 游标主体
[function_title] -- 从规范中引入的函数头部的声明
{
begin
fun_plsql; -- plsql块
[exception] -- 异常部分
[dowith_sentences;] -- 异常处理语句
end [fun_name] -- 函数名称
}
[procedure_title] -- 从规范中引入的存储过程的名称
{
begin
pro_plsql;
[exception]
[dowith_sentences;]
end [pro_name]
....
}
end [包名];
程序包创建的语法
-- 包头的创建语法
CREATE [OR REPLACE] PACKAGE package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
package_name:包的名称
Public item declarations：公共声明部分
Subprogram specification：声明PL/SQL子程序
-- 程序包主体的常见语法
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];
package_name是包的名称
Private item declarations:私有声明部分
subprogram specificatons:子程序体
-- 举例创建一个简单的
-- 创建包头
create or replace package pack1
is
-- 声明公共部分
v_n number :=12;
-- 声明PL/SQL子程序（过程函数）(注意：再这里创建函数或者过程的时候不用写create or replace)
procedure pp;
function ff return varchar2;
end;
-- 创建一个包体(包体是对包头的实现，名字必须和包头名字一致)
create or replace package body pack1
is
--声明私有部分
v_n2 number:=23;
--声明实现部分（子程序体）
procedure pp
is
begin
dbms_output.put_line('Hello');
end;
function ff return varchar2
is
begin
return '你好';
end;
end;
-- 如和查询包头和包体
select * from user_objects;
-- 如何使用包？
1、可以通过 包名.对象名
begin
dbms_output.put_line(pack1.v_n);-- 只能访问公共的，不能使用私有的
pack1.pp();
dbms_output.put_line(pack1.ff());
end;
--创建包头
create or replace package pack2
is
v number :=50;
-- 向dept表中添加数据
procedure pp_insert(v_dept in dept%rowtype);
-- 求2个数的和
function ff_add(v1 number,v2 number) return number;
end;
-- 创建包体
create or replace package body pack2
is
-- 实现过程的代码
procedure pp_insert(v_dept in dept%rowtype)
is
begin
insert into dept values(v_dept.deptno,v_dept.dname,v_dept.loc);
end;
-- 实现函数的代码
function ff_add(v1 number,v2 number) return number
is
begin
return v1+v2;
end;
end;
-- 使用
declare
v_dept dept%rowtype;
begin
dbms_output.put_line(pack2.v);
v_dept.deptno :=100;
v_dept.dname :='研发部';
v_dept.loc :='青岛';
pack2.pp_insert(v_dept);
dbms_output.put_line(pack2.ff_add(5,6));
end;
-- 删除包
drop package pack2;
程序包中的游标
游标的定义分为游标规范和游标主体两部分
在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型 return 数据类型;
RETURN子句指定的数据类型可以是：
用 %ROWTYPE 属性引用表定义的记录类型
程序员定义的记录类型，例如 TYPE EMPRECTYP IS RECORD(emp_id INTEGER,salary REAL) 来定义的。
不可以是number, varchar2， %TYPE等类型。
如：
-- 创建包头
-- 显式游标
create or replace package pack3
is
cursor cur_dept return dept%rowtype;
procedure p;
end;
-- 创建包体
create or replace package body pack3
is
cursor cur_dept return dept%rowtype is select * from dept;
procedure p
v_dept dept%rowtype;
is
begin
open cur_dept;
loop
fetch cur_dept into v_dept;
dbms_output.put_line(v_dept.deptno||','||v_dept.dname||','||v_dept.loc);
exit when cur_dept%notfound;
end loop;
close cur_dept;
end;
end;
-- 使用
begin
pack3.p();
end;
-- ref 游标
-- 创建包头
create or replace package pack4
is
-- 声明ref游标类型
type cur_type is ref cursor;
procedure p;
end;
-- 创建包体
create or replace package body pack4
is
procedure p
is
-- 声明ref游标变量
cur_my cur_type;
v_dept dept%rowtype;
begin
open cur_my for select * from dept;
loop
fetch cur_my into v_dept;
exit when cur_my%notfound;
dbms_output.put_line(v_dept.deptno||','||v_dept.dname||','||v_dept.loc);
end loop;
close cur_my;
end;
end;
--使用
begin
pack4 p();
end;
内置程序包
可以扩展数据库的功能
为 PL/SQL 提供对 SQL 功能的访问
用户 SYS 拥有所有程序包
是公有同义词
可以由任何用户访问
就是Oracle提供的程序包
-- DBMS_RANDOM包的使用，来产生随机的数字、字符、日期
--产生随机的整数
select DBMS_RANDOM.random from dual;
begin
dbms_output.put_line(DBMS_RANDOM.random);
end;
-- 产生0-100（不包括100）的随机数
select abs(mod(DBMS_RANDOM.random,100))from dual;
-- value函数会返回一个大于等于0但是小于1的数
select dbms_random.value from dual;
/*
对于指定范围内的数，要加入参数low_value和
high_value
*/
select dbms_random.value(0,10) from dual;
select floor(dbms_randow.value(0,10)) from dual;
--0到100之间的小数
SELECT DBMS_RANDOM.VALUE(0, 100) FROM DUAL;
--产生0到100之间的整数
SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM DUAL;
/*
STRING函数生成随机文本字符串，可以指定字符串的类型和所希望的长度。
SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;
下面是一些类型的代码：
‘U’用来生成大写字符，upper
‘L’用来生成小写字符，lower
‘A’用来生成大小写混合的字符，
’P’ 表示 字符串由任意可打印字符构成， +-#$%^^
’X’表示字符串由大写字符和数字构成。
*/
select dbms_random.string('U',10) from dual;
select dbms_random.string('L',10) from dual;
select dbms_random.string('A',10) from dual;
select dbms_random.string('P',10) from dual;
select dbms_random.string('X',10) from dual;
/*
返回某年内的随机日期，分两步：
--日期对应的内部整数，用格式'J' -4712
1, SELECT TO_CHAR(TO_DATE('01/01/21','mm/dd/yy'),'J')
FROM DUAL;
2, SELECT
TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2459216,2459216+365) ),'J') FROM DUAL;
*/
select to_char(sysdate,'J') from dual; -- 2459307
select to_date(2459307,'J' from) dual;
-- 随机2021年的某一天
select to_char(to_date('2021-01-01','yyyy-MM-dd'),'J') from dual;
select to_date(trunc(dbms_random.value(2459307,2459306+365)),'J') from dual;
/*
dbms_job包的用法
可以执行调度任务，比如执行定时任务
*/
-- 任务:每搁一段时间间隔，自动调用存储过程
-- 创建测试表
create table mytable11(a date);
-- 创建一个自定义过程
create or replace procedure test11
is
begin
insert into mytable11 values(sysdate);
end;
/* submit()参数：
1 job OUT BINARY_INTEGER, 输出的no 编号
2 what IN VARCHAR2,'存储过程名;'
3 next_date IN DATE DEFAULT sysdate,执行任务的时间
4 interval IN VARCHAR2 DEFAULT 'null', 间隔时间 单位是1天
*/
declare
v binary_integer;
begin
----创建job
dbms_job.submit(v,'test11;',sysdate,'sysdate+1/1440');
-- 第一个参数是任务编号。
-- 第二个参数是要执行的存储过程名，注意加；号
-- 第三个参数是下次执行的时间
-- 第四个参数是时间间隔
dbms_output.put_line(v);
end;
-- 如何查看任务
select * from user_jobs;
-- 如何删除任务
begin
dbms_job.run(v);
end;
--运行JOB
begin
dbms_job.run(v);
end;
--停止任务
begin
dbms_job.broken(v,true);
end;
/*
UTL_FILE包的用法
可以操作系统的文本文件
准备工作：在D盘下创建一个文件夹sql
*/
/*
创建一个Oracle的目录
create or replace directory 目录名称 as '目录的路径';
*/
create or replace directory filepath as 'd:/sql';
/*
文件类型：
utl_file.file_type
打开文件：
utl_file.fopen('目录'，'文件名','文件的模式')
目录: 值是oracle的目录，需要加单引号
文件名：值是文件名，需要加单引号
文件的模式:w(可写)，r(读)，a(追加)
写入文件内容：utl_file.put_line(文件类型变量，要写的文件内容)
读取文件内容：utl_file.get_line(文件类型变量，要读的文件内容)
*/
-- 写
declare
--声名文件类型的变量
f utl_file.file_type;
begin
--1.打开文件utl_file.fopen(创建的directory,文件名,打开方式字符串)打开方式分三种 w:写，r：读，a:追加
f:=utl_file.fopen('FILEPATH','test.txt','w');
--2.读或写文件utl_file.put_line(文件类型变量，要写的文件内容)
utl_file.put_line(f,'a');
utl_file.put_line(f,'b');
utl_file.put_line(f,'c');
utl_file.put_line(f,'d');
--3.关闭文件utl_file.fclose(文件类型变量)
utl_file.fclose(f);
end;
-- 读
declare
--声名文件类型的变量
f utl_file.file_type;
--声名一个变量保存文件中的一行记录
str varchar2(200);
begin
--1.打开文件utl_file.fopen(创建的directory,文件名,打开方式字符串)打开方式分三种 w:写，r：读，a:追加
f:=utl_file.fopen('FILEPATH','test.txt','r');
--2.读或写文件utl_file.get_line(文件类型变量，读文件内容的变量)
utl_file.get_line(f,str);
dbms_output.put_line(str);
utl_file.get_line(f,str);
dbms_output.put_line(str);
utl_file.get_line(f,str);
dbms_output.put_line(str);
utl_file.get_line(f,str);
dbms_output.put_line(str);
/*
loop
utl_file.get_line(f.str);
dbms_output.put_line(str);
end loop;
exception
when no_data_found then
dbms_output.put_line('找不到了');
*/
--3.关闭文件utl_file.fclose(文件类型变量)
utl_file.fclose(f);
end;
更多推荐
程序包——PL/SQL教程（九）
发布评论