(一)程序包
(1)程序包是对相关过程、函数、变量、游标和异常等对象的封装
(2)程序包由规范和主体两部分组成
(2)程序包由规范和主体两部分组成
(二)创建程序包
(1)程序包规范
CREATE [OR REPLACE]
PACKAGE
package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
PACKAGE
package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
(2)程序包主体
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
BEGIN
Initialization]
END [package_name];
[Private item declarations]
[Subprogram bodies]
BEGIN
Initialization]
END [package_name];
(三)自己写的一个例子:
--程序包的声明
create or replace package pkg_test as
type type_pkg_person is record(
pname varchar2(20),
age number
);
v_pkg_var varchar2(50);
cursor cur_pkg_test return type_pkg_person;
procedure pro_pkg_add(x in integer,y in integer,z out integer);
function fn_pkg_test (x integer, y integer) return integer;
end;
--创建程序包的主体
create or replace package body pkg_test as
cursor cur_pkg_test return type_pkg_person is select name,age from test;
procedure pro_pkg_add(x in integer,y in integer,z out integer)
as
total integer;
begin
total :=x+y;
z :=total;
end;
function fn_pkg_test(x integer,y integer) return integer
as
begin
return x*y;
end;
end;
create or replace package pkg_test as
type type_pkg_person is record(
pname varchar2(20),
age number
);
v_pkg_var varchar2(50);
cursor cur_pkg_test return type_pkg_person;
procedure pro_pkg_add(x in integer,y in integer,z out integer);
function fn_pkg_test (x integer, y integer) return integer;
end;
--创建程序包的主体
create or replace package body pkg_test as
cursor cur_pkg_test return type_pkg_person is select name,age from test;
procedure pro_pkg_add(x in integer,y in integer,z out integer)
as
total integer;
begin
total :=x+y;
z :=total;
end;
function fn_pkg_test(x integer,y integer) return integer
as
begin
return x*y;
end;
end;
--使用程序包
declare
z integer;
begin
pkg_test.pro_pkg_add(2,3,z);
dbms_output.put_line('2加3等于:'||z);
z :=pkg_test.fn_pkg_test(2,4);
dbms_output.put_line('2*3等于:'||z);
end;
declare
z integer;
begin
pkg_test.pro_pkg_add(2,3,z);
dbms_output.put_line('2加3等于:'||z);
z :=pkg_test.fn_pkg_test(2,4);
dbms_output.put_line('2*3等于:'||z);
end;
(四)一个更多功能的例子参考:
--包(包声明、包体)
--包声明
create or replace package pkg_test as
type type_pkg_test is record
(
name varchar2(20),
age integer
);
v_pkg_test varchar2(20);
cursor cur_pkg_test return type_pkg_test;
procedure p_pkg_test (sid integer, sname varchar2);
procedure p_pkg_test (sid integer); --包中存储过程的重载
function fn_pkg_test (x integer, y integer) return integer;
ex_pkg_test exception;
end;
--包声明
create or replace package pkg_test as
type type_pkg_test is record
(
name varchar2(20),
age integer
);
v_pkg_test varchar2(20);
cursor cur_pkg_test return type_pkg_test;
procedure p_pkg_test (sid integer, sname varchar2);
procedure p_pkg_test (sid integer); --包中存储过程的重载
function fn_pkg_test (x integer, y integer) return integer;
ex_pkg_test exception;
end;
--包体的定义
create or replace package body pkg_test as
cursor cur_pkg_test return type_pkg_test is select name,age from student;
procedure p_pkg_test (sid integer, sname varchar2)
as
begin
update student set name=sname where id=sid;
end p_pkg_test;
procedure p_pkg_test (sid integer)
as
begin
update student set name='package' where id=sid;
end;
procedure p_pkg_cur --注意,此存储过程没有在包声明中被定义,属于包的私有存储过程
as
v_type_pkg_test type_pkg_test;
begin
open cur_pkg_test;
fetch cur_pkg_test into v_type_pkg_test;
while cur_pkg_test%found loop
dbms_output.put_line(v_type_pkg_test.name||'|'||v_type_pkg_test.age);
fetch cur_pkg_test into v_type_pkg_test;
end loop;
close cur_pkg_test;
commit;
end p_pkg_cur;
function fn_pkg_test (x integer, y integer) return integer
as
begin
return x*y;
end;
end;
create or replace package body pkg_test as
cursor cur_pkg_test return type_pkg_test is select name,age from student;
procedure p_pkg_test (sid integer, sname varchar2)
as
begin
update student set name=sname where id=sid;
end p_pkg_test;
procedure p_pkg_test (sid integer)
as
begin
update student set name='package' where id=sid;
end;
procedure p_pkg_cur --注意,此存储过程没有在包声明中被定义,属于包的私有存储过程
as
v_type_pkg_test type_pkg_test;
begin
open cur_pkg_test;
fetch cur_pkg_test into v_type_pkg_test;
while cur_pkg_test%found loop
dbms_output.put_line(v_type_pkg_test.name||'|'||v_type_pkg_test.age);
fetch cur_pkg_test into v_type_pkg_test;
end loop;
close cur_pkg_test;
commit;
end p_pkg_cur;
function fn_pkg_test (x integer, y integer) return integer
as
begin
return x*y;
end;
end;
--包的使用
declare
v_var integer;
aaa pkg_test.type_pkg_test;
begin
pkg_test.p_pkg_test(95,'package123');
pkg_test.p_pkg_test(98); --包中存储过程重载的调用
aaa.name:='saff';
aaa.age:=45;
v_var:= pkg_test.fn_pkg_test(2,6);
dbms_output.put_line(v_var);
commit;
end;
declare
v_var integer;
aaa pkg_test.type_pkg_test;
begin
pkg_test.p_pkg_test(95,'package123');
pkg_test.p_pkg_test(98); --包中存储过程重载的调用
aaa.name:='saff';
aaa.age:=45;
v_var:= pkg_test.fn_pkg_test(2,6);
dbms_output.put_line(v_var);
commit;
end;
(五)程序包的优点:模块化、更轻松的应用程序设计、信息隐藏、新增功能、性能更佳
(六)程序包中的游标
游标的定义分为游标规范和游标主体两部分
在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型
RETURN子句指定的数据类型可以是:
用 %ROWTYPE 属性引用表定义的记录类型
程序员定义的记录类型
在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型
RETURN子句指定的数据类型可以是:
用 %ROWTYPE 属性引用表定义的记录类型
程序员定义的记录类型