Oracle Class10. 集合和成员函数(pl/sql表和记录,嵌套表和可变数组,成员函数和过程)
------------------------2013-5-21------------------------
定义表的类型
type tablename is table of col_def index by binary_integer;
声明表的类型
Tablename tablename
pl/sql表
临时使用，像数组一样的对象。
包含一列和一个主键
不能对列和主键进行命名
列可以是任何标量数据类型
主键必须是binary_integer类型
大小没有限制
引用：tablename(key_value)
赋值：tablename(key_value) := expression;
记录 %rowtype来声明记录。
定义记录类型
type typename is record(列定义 ...);
声明记录类型
recordtype typename;
引用记录
recordtype.columnname
赋值
recordtype.columnname := expression;
pl/sql的运行
oracle使用两个引擎来运行pl/sql块和sql语句
pl/sql引擎运行过程语句。
sql引擎运行sql语句。
声明嵌套表
type tablename IS TABLE OF tabletype;
嵌套表与索引表差异
嵌套表 索引表
使用sql来操纵并存储在数据库中 不可能
下标范围：1到2147483647 下标范围：-2147483647到2147483647
可用is null操作符来验证 不可验证
可用extend和trim方法 不可用
声明可变数组
TYPE typename IS VARRAY(Maximum_size) OF element_type(not null);
-typename是可变数组名
-Maximum_size设置了元素的数目
-element_type不能为boolean,ref游标,表或者另一种可变数组类型。
-----------------------------------------------
exists(n) 第n个元素存在，返回true
count 总数
limit 最大数目
first 第一个元素 （下标值）
last 最后一个元素 （下标值）
prior(x) 第x个元素之前的元素 （下标值）
next(x) 第x个元素之后的元素 （下标值）
extend(x,y) 追加x个第y个元素的副本
trim(x) 从集合的末尾处截断x个元素
delete 删除部分或全部元素
-----------------------------------------------
##forall应用举例##
-- 采用ForAll对变量进行批量邦定，然后一次发送
declare
type tt is table of int index by binary_integer;
lv_tt tt;
t1 char(5);
t2 char(5);
t3 char(5);
procedure get_time(t out number)
is
begin
--取得当前时间
select to_char(sysdate, 'sssss') into t from dual;
end;
begin
for i in 1 .. 100000
loop
lv_tt(i) := i;
end loop;
get_time(t1);
for i in 1 .. 100000
loop
insert into ttt2 values (lv_tt(i));
end loop;
get_time(t2);
forall i in 1 .. 100000
insert into ttt2 values (lv_tt(i));
get_time(t3);
dbms_output.put_line('未批量邦定耗时：');
dbms_output.put_line(t2 - t1);
dbms_output.put_line('批量邦定耗时：');
dbms_output.put_line(t3 - t2);
end;
未批量邦定耗时：
10
批量邦定耗时：
0
##bulk collect##
declare
-- PL/SQL表（索引表）
type ttt is table of int index by binary_integer;
lv_tt ttt;
begin
select a BULK Collect into lv_tt from tt; --BULK Collect
for i in 1 .. 3
loop
dbms_output.put_line(lv_tt(i));
end loop;
end;
create table tt
(
a int, -- 自动增长/在触发器中实现 ???
b int
)
create or replace trigger triInsertTT
before insert
on tt
for each row
begin
:New.a := 1;
end;
set serveroutput on;
--动态sql
declare
i int;
begin
--execute immediate 'create table aqq(i int)';
--execute immediate 'insert into aqq values(1) ';
i := 13;
execute immediate 'insert into aqq values('|| i ||') '; --这样写才正确
--execute immediate 'insert into aqq values(i) '; --ORA-00984: 列在此处不允许
--'insert into a values (i)'; --PLS-00490: 非法语句
end;
select * from aqq;
declare
type tt is table of int index by binary_integer;
lv_t tt;
begin
for i in 5 .. 10 --从哪个值开始都可以。
loop
lv_t(i) := i;
end loop;
for i in 5 .. 10 --从哪个值开始都可以。
loop
dbms_output.put_line(lv_t(i));
end loop;
end;
declare
type tr is record
(
a int,
b int
);
type tt is table of tr index by binary_integer;
lv_t tt;
begin
for i in 2 .. 10 --从哪个值开始都可以。
loop
lv_t(i).a := i * 2;
lv_t(i).b := i * 3;
end loop;
for i in 2 .. 10 --从哪个值开始都可以。
loop
dbms_output.put(lv_t(i).a || ' --- '); --输出不换行。
dbms_output.put_line(lv_t(i).b);
end loop;
end;
#type嵌套的情况#
declare
type tr1 is record
(
b int
);
type tr is record
(
a tr1
);
type tt is table of tr index by binary_integer;
lv_t tt;
begin
for i in 4 .. 10 --从哪个值开始都可以。
loop
lv_t(i).a.b := i;
end loop;
for i in 4 .. 10 --从哪个值开始都可以。 如果索引值不对，报ORA-01403: 未找到数据。
loop
dbms_output.put_line(lv_t(i).a.b);
end loop;
end;
##存在问题??? ##
declare
type t is record
(
a int
--b varchar2(10)
);
type tt is table of t index by binary_integer;
lv_t2 tt;
begin
select a into lv_t2 from c6; --bulk collect --PLS-00597: INTO 列表中的表达式 'LV_T2' 类型错误
for i in 1 .. lv_t2.count
loop
dbms_output.put(lv_t2(i).a || ' --- ');
--dbms_output.put_line(lv_t2(i).b);
end loop;
end;
##存在问题??? ##
declare
type t is record
(
a int,
b varchar2(10)
);
type tt is table of t index by binary_integer;
lv_tt tt;
begin
select a,b bulk collect into lv_tt from c6; --bulk collect --PLS-00597: INTO 列表中的表达式 'LV_T2' 类型错误
dbms_output.put_line(lv_tt.count || '~~~~~~' );
for i in 1 .. lv_tt.count
loop
dbms_output.put(lv_tt(i).a || ' --- ');
dbms_output.put_line(lv_tt(i).b);
end loop;
end;
declare
type tt is table of varchar2(24) index by binary_integer;
lv_tt tt;
begin
select b bulk collect into lv_tt from c6; --bulk collect可以--
dbms_output.put_line(lv_tt.count || '~~~~~~' );
for i in 1 .. lv_tt.count
loop
dbms_output.put(lv_tt(i) || ' --- ');
end loop;
end;
--集合 记录 对象（抽象数据类型）
declare
type address_rec is record --自定义类型
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);
lv_address_rec address_rec;
begin
lv_address_rec.state := 'HN';
lv_address_rec.city := 'ZZ';
dbms_output.put_line(lv_address_rec.state);
dbms_output.put_line(lv_address_rec.city);
end;
--建立程序包，仅包含一个自定义数据类型。
create or replace package pkg_school
is
type address_rec is record --自定义类型
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);
end pkg_school;
--简化以上的代码--
declare
lv_address_rec pkg_school.address_rec; --使用程序包来取代
begin
lv_address_rec.state := 'HN';
lv_address_rec.city := 'ZZ';
dbms_output.put_line(lv_address_rec.state);
dbms_output.put_line(lv_address_rec.city);
end;
--创建学生表，使用自定义类型。
create table student
(
no char(4),
name varchar2(8),
address pkg_school.address_rec --报错：ORA-00902: 无效数据类型
);
create table student10
(
no char(4),
name varchar2(8),
address address_rec5 --要使用永久性对象，自定义类型才可以。
);
--添加值
insert into STUDENT10 values ('0001','ANiu',address_rec5('HN','ZZ','TL','0001'));
select no,name,address.state from STUDENT10; --这样写会报错。address.state ORA-00904: 无效列名
select * from STUDENT10;
select no,name,address from STUDENT10;
#这样写才可以，通过别名的方式。#
select no,name,s.address.state from STUDENT10 s;
--添加，通过pl/sql方式。
declare
lv_address address_rec5;
begin
lv_address := address_rec5('GD','ZZ','TL','0001');
insert into STUDENT10 values ('0001','ANiu',lv_address); --通过变量的方式，lv_address
end;
--通过点分法修改address_rec5
update student10 s set s.address.state = 'HK' where s.no='0001';
--删除
delete student10 s where s.address.state = 'HK';
--定义索引，在自定义抽象数据类型的字段上，优化查询性能。
create index street_name_idx on student10(address.street_name);
--查看用户定义的索引
select index_name,index_type,table_name,table_type from user_indexes where table_name = 'STUDENT10';
--#这样创建表会报错??#
declare
type address_rec is record --自定义类型
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);
begin
create table students
(
no char(4),
name varchar2(8),
address address_rec
);
end;
--创建类型
create type address_rec4 as record --会报错 as object
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);
declare
type address_rec4 is record --暂态对象
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);
begin
dbms_output.put_line('aaa');
end;
create type address_rec4 as object --需要写成object,永久性对象。
(
state varchar2(12),
city varchar2(12),
street_name varchar2(12),
street_no varchar2(4)
);
--删除自定义抽象数据类型
drop type address_rec4;
--强制删除
drop type address_rec4 force;
--使用自定义类型
declare
lv_address address_rec5;
begin
lv_address := address_rec5('HN','ZZ','TL','0001');
dbms_output.put_line(lv_address.state);
dbms_output.put_line(lv_address.city);
dbms_output.put_line(lv_address.street_name);
dbms_output.put_line(lv_address.street_no);
end;
select column_name,data_type from user_tab_columns where table_name = 'student10'; --查询不了结果。
select column_name,data_type from user_tab_columns where table_name = 'STUDENT10'; --表名要大写才可以。
--查询类型属性。
select attr_name,length,attr_type_name from user_type_attrs where type_name = 'ADDRESS_REC5';
--查看对象依赖性
desc user_dependencies;
select name,type from user_dependencies where referenced_name = 'ADDRESS_REC5';
--##对象表的概念##
create table Address of address_rec5;
-- 查看
desc Address;
-- 添加
insert into Address values(address_rec5('HN','ZZ','TL','0001'));
insert into Address values('SH','PJ','XH','0005'); --也可以这样添加值。
-- 查询
select * from address;
--通过value函数可以将查询结果作为一个对象的集合返回
select value(a) from address a; --有要求的，不是任何一个表都可以，而是要基于对象表创建的表才可以。
--调用
declare
s1 address_rec5; --类型，而不是Address，否则会报错。
begin
select value(s) into s1 from address s where s.state = 'SH';
dbms_output.put_line(s1.state);
dbms_output.put_line(s1.city);
dbms_output.put_line(s1.street_name);
dbms_output.put_line(s1.street_no);
end;
insert into Address values(address_rec5('GD','GZ','XA','0001'));
insert into Address values(address_rec5('XX','XA','BJ','0004'));
--oid
--oracle自动分配给对象表中每一条对象记录的一个唯一标示号
--通过ref来进行调用
select ref(a) from address a; --有几条记录就返回几条a地址值.如
REF(A)
--------------------------------------------------------------------------------
00002802096D5EE0A11F2C4C828CE4DB0E4B9DBA51592BB9591600436887DFDE706F178DBC004086
E60000
00002802094F82C9267C2C4BE6B21E9851001E74B3592BB9591600436887DFDE706F178DBC004086
E60001
0000280209C955BEBAA7D04674995B5996900A6330592BB9591600436887DFDE706F178DBC004086
E60002
#OID应用# deref()函数 Dangling
##可变数组varray##
--可变数组
declare
-- 定义可变数组 varing array =>varray
type authors_ary is varray(5) of varchar2(12);
-- 声明数组变量，通过构造函数初始化
lv_authors authors_ary := authors_ary('A','B','C');
begin
--通过索引下标来调用
dbms_output.put_line(lv_authors(1));
dbms_output.put_line(lv_authors(2));
dbms_output.put_line(lv_authors(3));
end;
-- 创建可变类型
create or replace type authors_ary is varray(5) of varchar2(12);
create table bs(
bid varchar2(4),
authors authors_ary
)
insert into bs values('001',authors_ary('A','B','C'));
insert into bs values('002',authors_ary('D','E','F'));
insert into bs values('003',authors_ary('G','H','I'));
insert into bs values('004',authors_ary('J','K','L'));
-- select * from bs;
select * from bs where bid = '001';
select b.bid,b.authors_ary(1) from bs b where bid = '001'; --这样子是取不了的??
--构造一个隐式的游标
begin
for lv_book_rec in (select * from bs)
loop
--dbms_output.put( ' ---- ');
dbms_output.put_line (lv_book_rec.bid || '========');
for lv_index in 1 .. lv_book_rec.authors.count
loop
dbms_output.put(lv_book_rec.authors(lv_index)); --这样子是可以取的。
end loop;
dbms_output.put_line ( '========');
end loop;
end;
-- 可变数组的函数
declare
lv_authors authors_ary := authors_ary('A','B','C');
begin
dbms_output.put_line('Limit:' || lv_authors.limit); --定义的数组大小
dbms_output.put_line('Count:' || lv_authors.count);
if lv_authors.exists(1) then
dbms_output.put_line(lv_authors(1));
end if;
dbms_output.put_line(lv_authors(2));
dbms_output.put_line(lv_authors(3));
if lv_authors.exists(4) then
dbms_output.put_line(lv_authors(4));
else
dbms_output.put_line('没有第4项');
end if;
end;
Limit:5
Count:3
A
B
C
没有第4项
PL/SQL 过程已成功完成。
#完善以上代码，加了大小超出报错异常及通过extend添加新项#
declare
lv_authors authors_ary := authors_ary('A','B','C');
begin
dbms_output.put_line('Limit:' || lv_authors.limit); --定义的数组大小
dbms_output.put_line('Count:' || lv_authors.count);
if lv_authors.exists(1) then
dbms_output.put_line(lv_authors(1));
end if;
dbms_output.put_line(lv_authors(2));
dbms_output.put_line(lv_authors(3));
lv_authors.extend; --扩充，延伸。
lv_authors(4) := 'DDD';
--通过extend添加新项。
lv_authors.extend; --lv_authors.extend(1); 与 lv_authors.extend; 等效。
--lv_authors.extend(1,1); 第一个参数表示在4的基础上新加一个，第二个参数表示引用原来数组里面的第一个值。整体作为第5个元素。
lv_authors(5) := 'e';
--lv_authors.extend;
--lv_authors(6) := 'f';
--lv_authors(6) := 'f'; --ORA-06533: 下标超出数量
if lv_authors.exists(4) then
dbms_output.put_line(lv_authors(4));
else
dbms_output.put_line('没有第4项');
end if;
if lv_authors.exists(5) then
dbms_output.put_line(lv_authors(5));
else
dbms_output.put_line('没有第5项');
end if;
end;
--循环可变数组的函数
declare
lv_authors authors_ary := authors_ary('A','B','C');
begin
for lv_index in 1 .. lv_authors.count
loop
dbms_output.put_line(lv_index);
end loop;
end;
##隐式游标##
begin
for lv_Title_type in (select cid, cname from class6)
loop
dbms_output.put_line ('========');
--dbms_output.put_line (lv_Title_type.cid);
--dbms_output.put_line (lv_Title_type.cname);
end loop;
-- 关闭游标
end;
---------ORACLE6.1.txt
Oracle集合分为两类
-- 可变数组大小 type type_name is varray(limit) of datatype not null
特点：固定大小，数据连续存储。
-- 嵌套表 type table_name is table of data_type
特点：可变大小，数据松散存储。
declare
type typ_authors is varray(3) of varchar2(12); --容量大小是varray后面的值，而不是数据类型varchar2(12)
-- 调用构造函数初始化
au_ary typ_authors := typ_authors();
begin
if au_ary is null then
dbms_output.put_line('au_ary is null!'); --打印
else
dbms_output.put_line('au_ary is not null!');
end if;
dbms_output.put_line('Limit: ' || au_ary.limit); --3
dbms_output.put_line('Count: ' || au_ary.count); --0
au_ary.extend;
au_ary(1) := 'A';
au_ary.extend;
au_ary(2) := 'B';
dbms_output.put_line(au_ary(1)); --A
dbms_output.put_line(au_ary(2)); --B
-- 通过Extend添加新项
au_ary.extend;
au_ary(3) := 'C';
dbms_output.put_line(au_ary(3)); --C
dbms_output.put_line('Limit: ' || au_ary.limit); --3
dbms_output.put_line('Count: ' || au_ary.count); --3
au_ary.trim(1); --截掉最后一个元素
dbms_output.put_line('Limit: ' || au_ary.limit); --3
dbms_output.put_line('Count: ' || au_ary.count); --2
if au_ary.exists(3) then
dbms_output.put_line('au_ary(3) 存在！');
else
dbms_output.put_line('au_ary(3) 不存在！'); --打印
end if;
dbms_output.put_line('1 -- > Next: ' || au_ary.Next(1)); --B 输出为2？
dbms_output.put_line('Last: ' || au_ary.Last); --B 输出为2？
end;
#通过以下实例发现：lv_authors.Next(1),lv_authors.Last返回的都是下标值。#
declare
lv_authors authors_ary := authors_ary('A','B','C');
begin
dbms_output.put_line('Limit:' || lv_authors.limit); --定义的数组大小
dbms_output.put_line('Count:' || lv_authors.count);
if lv_authors.exists(1) then
dbms_output.put_line(lv_authors(1));
end if;
dbms_output.put_line(lv_authors(2));
dbms_output.put_line(lv_authors(3));
dbms_output.put_line('First: ' || lv_authors.First);
dbms_output.put_line('First: ' || lv_authors(lv_authors.First));
--dbms_output.put_line('1 -- > Prior: ' || lv_authors.prior(1)); --ORA-06502: PL/SQL: 数字或值错误 : NULL 索引表键值
--dbms_output.put_line('1 -- > Prior: ' || lv_authors(lv_authors.prior(1)));
dbms_output.put_line('1 -- > Prior: ' || lv_authors.prior(2));
dbms_output.put_line('1 -- > Prior: ' || lv_authors(lv_authors.prior(2)));
dbms_output.put_line('1 -- > Next: ' || lv_authors.Next(1));
dbms_output.put_line('1 -- > Next: ' || lv_authors(lv_authors.Next(1)));
dbms_output.put_line('Last: ' || lv_authors.Last);
dbms_output.put_line('Last: ' || lv_authors(lv_authors.Last));
end;
#嵌套表#
declare
type student_typ_tbl is table of varchar2(12);
stu_tbl student_typ_tbl;
j number;
begin
if stu_tbl is null then
dbms_output.put_line('stu_tbl is null!'); --打印
stu_tbl := student_typ_tbl(); --初始化
else
dbms_output.put_line('stu_tbl is not null!');
end if;
dbms_output.put_line('Limit: ' || stu_tbl.limit); --没有值
dbms_output.put_line('Count: ' || stu_tbl.count); --0
for i in 1 .. 10
loop
stu_tbl.extend; --附加值
stu_tbl(i) := i;
end loop;
stu_tbl.delete(1); --删除元素1
stu_tbl.delete(4); --删除元素4
--for i in 1 .. 10
--for i in 1 .. stu_tbl.count
--for i in 1 .. stu_tbl.last
j := stu_tbl.first;
for i in 1 .. stu_tbl.count
loop
--if stu_tbl.exists(i) then
-- dbms_output.put_line('stu_tbl(' || i || '): ' || stu_tbl(i));
--end if;
dbms_output.put_line('stu_tbl(' || j || '): ' || stu_tbl(j));
j := stu_tbl.next(j);
end loop;
dbms_output.put_line('Limit: ' || stu_tbl.limit); --没有值
dbms_output.put_line('Count: ' || stu_tbl.count); --8
end;
--索引表
declare
type teachers_type_tbl is table of varchar2(12) index by binary_integer;
teacher_tbl teachers_type_tbl;
begin
teacher_tbl(1) := 'A';
teacher_tbl(9) := 'B';
dbms_output.put_line(teacher_tbl(1));
dbms_output.put_line(teacher_tbl(9));
end;
##对象命名规范和对象体##
create or replace type rectangle is object
(
width number(5,2),
height number(5,2),
map member function area return number --需要定义成map或者是order（编译报错?），对象间才可以比较大小。
);
create or replace type body rectangle is
map member function area
return number
is
begin
return (width * height);
end area;
end;
--调用
declare
lv_rect rectangle := rectangle(10,10);
begin
DBMS_OUTPUT.Put_Line( 'lv_rect.area' );
DBMS_OUTPUT.Put_Line( lv_rect.area );
end;
--添加了map之后，才可以比较大小，否则会报错。
declare
lv_rect1 rectangle := rectangle(10, 10);
lv_rect2 rectangle := rectangle(20, 20);
begin
if lv_rect1 > lv_rect2 then
DBMS_OUTPUT.Put_Line( '1 > 2' );
else
DBMS_OUTPUT.Put_Line( '1 < 2' );
end if;
end;
##order方式## 注意function区别
--类型规范
create or replace type rectangle is object
(
width number(5,2),
height number(5,2),
order member function area(r rectangle) return int
);
--类型体
create or replace type body rectangle is
order member function area(r rectangle)
return int
is
begin
case
when (width * height) < (r.width * r.height) then return -1;
when (width * height) > (r.width * r.height) then return 1;
when (width * height) = (r.width * r.height) then return 0;
end case;
end area;
end;
-- 实例 对象规范和对象体。???
-- 堆栈(stack)
