PLSQL集合笔记

  1. 云栖社区>
  2. 博客>
  3. 正文

PLSQL集合笔记

科技小能手 2017-11-12 22:31:00 浏览518
展开阅读全文

    针对多行单列的数据处理,用之前的标量变量肯定不行,这里引入一个新的数据类型复合数据类型。

这个复合类型类似于语言的数组struct[i],包括索引表,嵌套表,边长数组三种类型

记住记录是管理单行数据的结构体,而集合时管理多行数据的结果体

集合就是列表,可能有序也可能无序,有序列表的索引是唯一性的数字下标,而无序列表的索引是唯一性的标识符,标识符可以是数字,哈希值,或者字符串名。

目前三种集合类型区别:

集合名 说明
可用下标
大小
初始化
是否可以为表列
索引表(index-by,或者叫做联合数组都行) 物理大小位置,不存于表,运行时变化
唯一性的数字或者字符串
动态分配
不需要
不可以
嵌套表
运行时变化大小未知,可以存于表中
数字序列
动态分配
需要
可以
varray表(密集数组)
物理大小静态的,可以存于表中
数字序列
固定
需要
可以

一、索引表(index-by,或者叫做联合数组都行)称为PLSQL的表,下标与数组比较可以为负数,下标个数没有限制。下标可以是唯一的数字序号或者字符。

TYPE type_name IS TABLE OF element_type

[not null] index by key_type;

identifier type_name;

以上为语法

element_type 为元素的数据类型

key_type 为索引表元素下标的数据类型(binary_integer,pls_integer,varchar2);

set serveroutput on;

declare

type ename_table_type is table of emp.ename%TYPE

index by binary_integer;

ename_table ename_table_type;

begin

select ename into ename_table(-1) from emp where empno =&no;--此处为什么会用-1 ,随便用的一个下标,下标定义个是数字,所以任何一个唯一的数字都可以用在此处,

dbms_output.put_line('ename_table(-1));

end;

用varchar2类型作为下标类型,此处应该有疑问,下标是字符类型

set serveroutput on;

declare

type area_table_type is table of number

index by varchar2(10);定义的下标是字符,那用下标的时候就需要用具有唯一性的字符去当下标,不能用数字去当下标,同样遍历的时候也不存在,字符无法用for loop遍历。

area_table area_table_type;

begin

area_table('北京'):=1;

area_table('天津'):=2;

area_table('天上人间'):=3;

dbms_output.put_line(area_table.first);

dbms_output.put_line(area_table('天津'))

dbms_output.put_line(area_table.last);

二、嵌套表与数组比较下标从1开始,元素没有限制,这个比较浪费数组,他的元素值不是顺序的

语法

type type_name is table of element_type:相比于index-of只是少了一个index by

identifier type_name;

declare

type ename_table_type is table of emp.ename%TYPE;

ename_table ename_table_type:=ename_table_type('a','a');


下面展示嵌套表的使用方法

啊)

declare

    type ENAME_TABLE_TYPE is table of emp.ename%TYPE;

ename_table ename_table_type:=ename_table_type('a','a','a','a','a');

begin

select ename into ename_table(2) from emp  #使用的时需要用构造方法ename_table_type()初始化,必要时可以用集合的extend()方法初始化

where empno=&no;

dbms_output.put_line(ename_table(2));

end;

吧)作为类型的时候需要用create type先创建类型,然后必须指定存储的表 

create type phone_type is table of varchar2(20);

create table employee(

id number(4),name varchar2(20),sal number(6,2),

phone phone_type)nested table phone store as phone_table; 指定存储的表


insert into employee values(1,'scott',800,phone_type('0473-343434','128381298'));


的)取数据这个案例提示了一个集合类型所拥有的方法

set serveroutput on;

declare

phone_table phone_type;

begin

select phone into phone_table from employee where id=1;

for i in 1..phone_table.count loop

dbms_output.put_line(phone_table(i));

end loop;

end;


取数据还有一个column_value的方法

select column_value from the 的使用


如果定义100个元素,那么可以使用extend()方法进行初始化

集合的方法,count first last等输出与输入不一致顺序,是由于数据库存储的时候是按下表(如果是字符,那么下标就是用的镜像的哈希值)



三、变长数组 VARRAY 可以作为表类型使用,下标1开始最大为7,关于下标有的書上根本没有提及这个数值,使用构造房初始化,如果使用变长数组时需要注意一般处理大型的事物,IO的代价比较高,

TYPE TYPE_NAME IS VARRAY(SIZE_LIMIT) OF TABLE ELEMENT_TYPE [NOT NULL];

IDENTIFIER TYPE_NAME;

SIZE_LIMIT用来指定元素的最大个数,个人理解是元素的个数,不是指下标。

declare

TYPE ENAME_TABLE_TYPE IS VARRAY(30) OF EMP.ENAME%TYPE;

ENAME_TABLE ENAME_TABLE_TYPE:=ENAME_TABLE_TYPE('A','A','A');


关于varray表需要说明一点,就是如果直接select ename into emp_table(1) from emp where empno=&no 这个时候就需要初始化emp_table 

如果使用bulk collect  into 则不需要初始化,

select ename bulk collect into emp_table from emp where empno=&no;

输出可以用dbms_output.put_line(emp_table(1));

输出就需要用for i in 1..emp_table.count loop

以上输出方式


四、记录表是记录跟表的结合 PLSQL变量处理单行单列 PLSQL记录用于处理单行多列,PLSQL集合用户处理多行单列,记录表是结合了PLSQL记录跟PLSQL表的有点组成的

,这个地方有待学习??如何进行多行多列处理,有一个拌饭就是用bulk collect 目前需要掌握的

select * bulk collect into emp_table from emp;

for i in 1..emp_table.count loop

....

end loop;


declare

type name_array_type is varray(20) of varchar2(30);

type city_array_type is varray(20) of varchar2(30);

name_array name_array_type;

city_array city_array_type;

begin

select name,city bulk collect into name_array,city_array from customer;

for i in 1..city_array.count loop

dbms_output.put_line(city_array(i)||name_array(i));

end loop;

end;



declare 输入编号,输出所有的信息

type item_table_type is table of item%ROWTYPE

index by binary_integer;

item_table item_table_type;

begin

select * bulk collect into item_table from item where ord_id=&id;

for i in 1..item_table.count loop

dbms_output.put_line('....');

end loop;

end;



五、多级集合类型,类似于多维数组,嵌套了集合类型的集合类型,此时的元素类型不能是集合类型,可以是标量,plsql记录以及对象类型这个对象类型好像没见过,


六、集合的方法,

exists判断集合元素是否存在,在就是true,不在就是false 

if ename_table.exists(1) then

ename_table(1):='scott';

else

dbms_output.put_line('a');

end if;

count返回当前集合变量中的元素个数,

for i in 1..ename_table.count loop

dbms_output.put_line(ename_table(i).ename);

end loop;

limit 用于返回集合元素的最大个数,这个一直没有明白,就像varray表可以定义的时候用varray(20)来定义元素的个数,但是下标却只能到7,有可能是这么理解,一个下标对应的元素个数之和最大不能超过20,这个还是有疑问。

declare
type ename_table_type is varray(20) of emp.ename%type;
ename_table1 ename_table_type:=ename_table_type('a');
ename_table2 ename_table_type:=ename_table_type('aaaa');
ename_table3 ename_table_type:=ename_table_type('aaaaaaaaaa');
ename_table4 ename_table_type:=ename_table_type('aaaa','3333333333');
begin
dbms_output.put_line(ename_table1.limit);
dbms_output.put_line(ename_table2.limit);
dbms_output.put_line(ename_table3.limit);
dbms_output.put_line(ename_table4.limit);
end;

四个值输出均为20,也就是他计算的时候是你定义的值,


first跟last,返回集合变量第一个元素的下标,跟最后一个元素的下标

dbms_output.put_line(ename_table.first);

dbms_output.put_line(ename_table.last);这个在遍历循环的时候用的上,for i in 1..ename_table.last loop

prior返回当前元素下标的前一个下标值,next返回当前元素下标的后面一个下标值,

extend扩展集合变量的大小,只是用与嵌套表跟varray表,这里还是有疑问,

trim从集合尾部删除元素,trim(n)如果不写n就是从尾部删除一个,如果写就是从尾部删除n个

delete用于删除指定的第n个元素,三种调用格式,delete(m,n)你懂得


集合赋值 两个集合进行赋值,不知道有什么意义(:=)或者sql语句将源集合中的数据赋值给目标集合时会自动清除原有的数据,给集合赋空值,集合不初始化即可

另外一个赋值为集合操作符,set multiset union,multiset intersect,multiset except

ename_table1 :=set(ename_table2);

multiset union取两个集合的并集

result=nt1 multiset union nt2;

multiset union distinct 取两个集合的并集并去除重复的值

multiset intersect 用于取得嵌套表的交集

multiset except 用户取得两个嵌套表的差集

针对以上的这些个操作,还有一些嵌套表所固有的东西,比如集合是否为null if ename_table is null

是否为空,等等,目前生产当中没有用到过这些东西。



本文转自 aklaus 51CTO博客,原文链接:http://blog.51cto.com/aklaus/1953493

网友评论

登录后评论
0/500
评论
科技小能手
+ 关注