Oracle PL/SQL 类型(Type)：索引表、嵌套表、变长数组、pipelined 管道

1、Oracle 新建员工表和部门表.sql。

集合类型

1、Oracle 集合是相同类型元素的组合，在集合中，使用唯一的下标来标识其中的每个元素，与 Java 的 List 很像。

2、常用集合方式：

类型	语法	下标	元素个数	初始值	.extend	能否存在DB中
索引表	TYPE type_name IS TABLE OF element_type   INDEX BY index_type; table_name type_name := type_name();	整数或字符 可以为负数	无限制	不用	不用	只能用在PLSQL中
嵌套表	TYPE type_name IS TABLE OF element_type; table_name type_name := type_name();	只能为整数	无限制	用同名构造函数赋值	用	可以使用create type 创建, 存在数据库中
变长数组	TYPE type_name IS VARRAY(n) OF element_type; varray_name type_name := type_name();		有限制

索引表：不需要初始化或者extend，且无个数限制，迄今为止最高效的集合类型，优先选择。唯一不足的一点是它只能用于PL/SQL而不能直接用于数据库。
嵌套表：如果需要使用10g，11g中的那些集合操作，则可以选择Nested table（嵌套表）;
变长数组：需要限制集合元素个数时可以使用。

3、集合常用方法：集合方法是集合对象的内置函数，可以使用"."标记来调用。

方法	描述
exists(n)	索引处的元素是否存在, 返回TRUE|FALSE
count	当前集合中的元素总个数
limit	集合元素索引的最大值  (索引表和嵌套表是不限个数的，所以返回null，变长数组返回定义时的最大索引 )
first / last	返回集合第一个/最后一个元素的下标
prior / next	当前元素的前一个 / 后一个元素
extend	增加元素,扩展集合的容量,不能用于索引表     x.extend    增加一个null元素       x.extend(n)    增加n个null元素     x.extend(n,i)    增加n个元素，元素值与第i个元素相同
trim	从尾部删除, 被删元素不保留占位符，不能用于索引表。     x.trim    删除一个元素     x.trim(n)    删除n个元素
delete	按索引删除集合元素, 被删元素保留占位符     delete    删除所有     delete(n)    删除第n个     delete(a,b)    删除a--b之间的所有元素

索引表

语法	TYPE type_name IS TABLE OF element_type  INDEX BY index_type; table_name type_name;
描述	type_name：类型名称，自定义即可。
	element_type：集合中存放的元素类型，如 number、char、varchar2，也可以是整行记录
	index_type：只能是整型或者字符串，可选值有：pls_integer, binary_integer or char
	table_name：索引表/集合变量名称，自定义即可。

示例一

declare--查询员工姓名cursor ename_cusor isselect ename from emp where rownum <= 100;--定义[索引表]（元素类型为员工姓名）TYPE ename_type IS TABLE OF emp.ename%TYPE INDEX BY PLS_INTEGER;--定义索引表变量v_ename_index_table ename_type;--索引表定义为 index by pls_integer 时，需要一个整数变量做计数器下标v_idx number := 0;
begin--循环游标，将元素添加到索引表集合中for ename_row in ename_cusor loopv_idx := v_idx + 1;v_ename_index_table(v_idx) := ename_row.ename;end loop;--索引表集合for循环--引用不存在的元素会报错：ORA-01403: 未找到任何数据for i in v_ename_index_table.first .. v_ename_index_table.last LOOPdbms_output.put_line(i || ' ' || v_ename_index_table(i));end loop;
end;

示例二

declare--查询员工信息cursor emp_cusor isselect t.* from emp t where rownum <= 100;--定义[索引表]（元素类型为员工表整行记录）TYPE emp_type IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;--定义集合变量v_emp_index_table emp_type;--索引表定义为 index by pls_integer 时，需要一个整数变量做计数器下标v_idx number := 0;
begin--循环游标，将元素添加到索引表集合中for emp_row in emp_cusor loopv_idx := v_idx + 1;v_emp_index_table(v_idx) := emp_row;end loop;--索引表集合for循环--引用不存在的元素会报错：ORA-01403: 未找到任何数据for i in v_emp_index_table.first .. v_emp_index_table.last LOOPdbms_output.put_line('empno=' || v_emp_index_table(i).empno ||' ename=' || v_emp_index_table(i).ename ||' comm=' || v_emp_index_table(i).comm);end loop;
end;

嵌套表

语法	TYPE type_name IS TABLE OF element_type; table_name type_name := type_name();
描述	type_name：类型名称，自定义即可。
	element_type：集合中存放的元素类型，如 number、char、varchar2，也可以是整行记录
	table_name：索引表/集合变量名称，自定义即可。

1、和索引表区别:

    嵌套表没有 index of，其下标固定为整型；
    必须使用和其同名的构造器对其初始化；
    添加元素前，必须先使用.extend分配存储空间;

2、嵌套表的构造器函数() 与嵌套表类型完全同名，可以带参数也可以无参：

带参数初始化：table_name type_name := type_name(a,b,c,...);
不带参数初始化：table_name type_name := type_name();

示例一

declare--查询员工信息cursor emp_cusor isselect t.empno, t.ename, t.comm, t.sal from emp t where rownum <= 100;--定义[嵌套表]（元素类型为员工表整行记录）TYPE emp_type IS TABLE OF emp%ROWTYPE;--定义集合变量, 并初始化嵌套表，使用和其同名的构造器对其初始化v_emp_index_table emp_type := emp_type();--集合元素下标(嵌套表下标只能是整数)v_idx number := 0;
begin--循环游标，将元素添加到索引表集合中for emp_row in emp_cusor loop--集合添加元素前，必须先调用它的extend方法给集合添加存储空间v_emp_index_table.extend;v_idx := v_idx + 1;v_emp_index_table(v_idx).empno := emp_row.empno;v_emp_index_table(v_idx).ename := emp_row.ename;v_emp_index_table(v_idx).comm := emp_row.comm;end loop;--索引表集合for循环for i in 1 .. v_emp_index_table.count LOOPdbms_output.put_line('empno=' || v_emp_index_table(i).empno ||' ename=' || v_emp_index_table(i).ename ||' comm=' || v_emp_index_table(i).comm);end loop;
end;

自定义 split 函数

-- =============创建类型(Type)——>split_table_type=============
create or replace type split_table_type as table of varchar2(32676);
/
-- =============创建函数——> split=============
create or replace function split(p_str clob, p_sep varchar2 := ',')
-- 根据匹配给定的分隔符来拆分字符串，没有匹配时，返回空对象。--参数 p_str: 待分割的字符串。--参数 p_sep: 分隔符，不明确指定时，默认为逗号。--返回字符串(p_str)被指定字符(p_sep)分割后的嵌套表类型/集合。return split_table_type is--定义集合变量, 并初始化嵌套表，使用和其同名的构造器对其初始化v_split_table split_table_type := split_table_type();--集合元素下标(嵌套表下标只能是整数)v_idx           number := 0;--分隔符找到的上一位置的索引v_sep_pre_idx number := 0;--分隔符找到的当前位置的索引v_sep_cur_idx number;
beginloopv_sep_cur_idx := instr(p_str, p_sep, v_sep_pre_idx + 1);if v_sep_cur_idx > 0 then--集合添加元素前，必须先调用它的extend方法给嵌套表(集合)添加存储空间v_split_table.extend;v_idx := v_idx + 1;--切割需要的目标元素(需要去掉分隔符本身)，示例：split('7369=#=7521=#=7900=#=9000','=#=')if v_sep_pre_idx <= 0 then v_split_table(v_idx) := substr(p_str, v_sep_pre_idx, v_sep_cur_idx - 1);else v_split_table(v_idx) := substr(p_str, v_sep_pre_idx + length(p_sep), v_sep_cur_idx - (v_sep_pre_idx + length(p_sep)));end if;--将上一位置切换到当前位置v_sep_pre_idx := v_sep_cur_idx;elsif v_sep_pre_idx > 0 and v_sep_pre_idx + length(p_sep) <= length(p_str) then--处理最后一个分隔符到内容结尾的部分(比如 1##2##3,1##2##3##)v_split_table.extend;v_idx := v_idx + 1;v_split_table(v_idx) := substr(p_str, v_sep_pre_idx + length(p_sep), length(p_str));exit;elseexit;end if;end loop;return v_split_table;
end;
/
--==============调用示例--==============
--这样查询时，返回的原生的集合，可视化效果不明显
SELECT split('1,223,32,56565') FROM dual T; 
--table 函数用于专门读取集合内容，列名默认为 column_value
select * from table(split('7369,7521,7900'));
select * from table(split(',7369,7521,,7900,'));
select * from table(split('7369,7521,7900','#'));
select * from table(split('','#'));--空结果
select * from table(split('#','#'));--返回一个null
select column_value as empno from table(split('7369=#=7521=#=7900=#=9000','=#='));
select column_value as empno from table(split('=#=7369=#=7521=#=7900=#=9000=#=','=#='));
select * from emp t where t.empno in (select * from table(split('7369,7521,7900')));
select * from emp t where t.empno in (select column_value as empno from table(split('7369,7521,7900')));

存储函数返回多行多列结果集

1、存储过程，存储函数返回单个结果时比较容易处理，返回多行多列时，则需要借助其它一些知识点，比如引用游标，以及本文的 Type 类型。

-- =============创建类型(Type)对象，指定每一行的多列信息，相当于列。
create or replace type emp_qury_col_type is object
(empno    NUMBER(4),ename    VARCHAR2(10),job      VARCHAR2(9),mgr      NUMBER(4),hiredate TIMESTAMP(6),sal      NUMBER(7, 2),comm     NUMBER(7, 2),deptno   NUMBER(2)
);
/
-- =============创建类型(Type)嵌套表，元素为上面的对象，相当于行。
create or replace type emp_qury_row_type is table of emp_qury_col_type;
/
create or replace function emp_qury_fun(pageNo in number := 1,pageSize in number := 20)--分页查询员工信息--参数 pageNo：查询的页码，从1开始，默认为1--参数 pageSize：每页的条数，默认为20--返回查询的集合——多行多列return emp_qury_row_type is--定义集合变量, 并初始化嵌套表，使用和其同名的构造器对其初始化v_emp_qury_row emp_qury_row_type := emp_qury_row_type();--集合元素下标(嵌套表下标只能是整数)v_idx           number := 0;
begin--将查询的结果放入到嵌套表/集合中for row in (select t.empno, t.ename, t.job, t.mgr, t.hiredate, t.sal, t.comm, t.deptnofrom (select rownum r, t1.* from emp t1) t where t.r between ((pageNo - 1) * pageSize + 1) and pageNo * pageSize) loop--嵌套表集合添加元素前，必须先调用它的extend方法给嵌套表(集合)添加存储空间v_emp_qury_row.extend;v_idx := v_idx + 1;v_emp_qury_row(v_idx) := emp_qury_col_type(row.empno, row.ename, row.job, row.mgr, row.hiredate, row.sal, row.comm, row.deptno);end loop;return v_emp_qury_row;
end;
/
--==============调用示例（使用table函数将函数返回的嵌套表转换成查询可以使用的目的表）--==============
select T.* from table(emp_qury_fun(1,50)) T;
select T.* from table(emp_qury_fun(1,5)) T;
select T.* from table(emp_qury_fun(2,10)) T;
SELECT T.* FROM table(emp_qury_fun()) T; 

变长数组

语法	TYPE type_name IS VARRAY(size) OF element_type; varray_name type_name := type_name();
描述	type_name：类型名称，自定义即可。
	size：表示数组的最大长度，一旦超过则报错：ORA-06532：下标超出限制
	element_type：集合中存放的元素类型，如 number、char、varchar2，也可以是整行记录
	varray_name：变长数组/集合变量名称，自定义即可。

示例一：

DECLARE--查询员工姓名CURSOR ename_cusor ISSELECT ename FROM emp WHERE ROWNUM <= 100;--定义[变长数组]（元素类型为员工姓名）TYPE ename_type IS VARRAY(100) OF emp.ename%TYPE;---定义集合变量, 并使用和其同名的构造器对其初始化v_ename_array ename_type := ename_type();--集合元素下标(变长数组下标只能是整数)v_idx NUMBER := 0;
BEGIN--循环游标，将元素添加到索引表集合中FOR ename_row IN ename_cusor LOOPv_idx := v_idx + 1;--集合添加元素前，必须先调用它的extend方法给集合添加存储空间v_ename_array.EXTEND;v_ename_array(v_idx) := ename_row.ename;END LOOP;--索引表集合for循环--引用不存在的元素会报错：ORA-01403: 未找到任何数据FOR i IN v_ename_array.FIRST .. v_ename_array.LAST LOOPDBMS_OUTPUT.PUT_LINE(i || ' ' || v_ename_array(i));END LOOP;
END;

二维数组

1、Oracle 9i 开始，可以创建多层集合，相当于 Java 的二维数组。

declaretype varray_in is varray(6) of pls_integer; --内层集合，元素是整型，相当于列type varray_out is varray(4) of varray_in; --外层集合，元素是变成数组，相当于行，--定义变量，通过构造器函数初始化变长数组。v_varray_in varray_in := varray_in(1, 2, 3, 4, 5, 6);--外层集合赋值为内层集合v_varray_out varray_out := varray_out(v_varray_in);
begin--继续使用构造器赋值数据v_varray_out.extend;v_varray_out(2) := varray_in(5, 6, 7, 8, null, null);v_varray_out.extend;v_varray_out(3) := varray_in(9, 10, 11, 12, 13, null);--遍历二维数组for i in v_varray_out.first .. v_varray_out.last loopfor j in v_varray_in.first .. v_varray_in.last loopdbms_output.put_line('[' || i || '][' || j || ']= ' ||v_varray_out(i) (j));end loop;if i < v_varray_out.count thendbms_output.new_line();end if;end loop;
end;

pipelined 管道

1、plsql 中使用 dbms_output 输出的信息，需要等服务器执行完成后，才能一次性返回给客户端。如果需要在客户端 实时 输出函数执行过程中的一些信息，在 Oracle9i 以后，可以使用管道函数。

2、管道函数为 并行执行。用关键字 pipelined 声明这是一个管道函数，返回值类型必须为集合，函数中 pipe row 语法被用来返回该集合的单个元素，可以以一个空的 return 语句提前结束。

示例1：

--自定义类型(嵌套表/集合)，元素类型为字符串，最长38位
create or replace type uuid_table_type is table of varchar2(38);
/
--自定义函数
create or replace function uuid_fun(p_size number :=20)return uuid_table_type--生成指定个数的uuid值,小写。--参数 p_size：每次生成的个数，默认为 20个。--返回随机的uuid字符串，多行单列。pipelined is
begin
for i in 1 .. p_size loop--uuid转为小写，然后通过管道输出返回.pipe row(lower(sys_guid()));
end loop;
--经过测试，return 写不写效果都一样。
return;
end;
/
--==============调用示例--==============
select t.* from table(uuid_fun()) t; 
select t.* from table(uuid_fun(100)) t; 

示例2：dbms_output 输出日志不仅慢，而且手动缓冲区的大小限制，而采用管道则不仅快，而已不受大小限制。

--自定义类型(嵌套表/集合)，元素类型为字符串
create or replace type emp_init_data_log_type is table of varchar2(32767);
/
--自定义函数
create or replace function emp_init_data_fun(p_empno_start number :=1,p_empno_end number :=5000)return emp_init_data_log_type--生成初始化员工表的 insert SQL语句--参数 p_empno_start：员工表主键开始的数值--参数 p_empno_end：员工表主键结束的数值pipelined isv_sql varchar2(2048);pragma autonomous_transaction;
begin
for i in p_empno_start .. p_empno_end loopv_sql := 'INSERT INTO EMP VALUES (' || i || ', ''SMITH' || i || ''', ''CLERK'', 7902,TO_DATE(''17-08-1980'', ''DD-MM-YYYY''), 100 + abs(mod(dbms_random.random,9999)) , NULL, 20);';--如果想在这里直接执行Insert语句，而在 SELECT 语句中调用该函数，则会报错：ORA-14551:无法在查询中执行DML操作--因为 SELECT 语句无法更改数据库的状态，一般来说，此时推荐创建为存储过程而不是存储函数。pipe row(v_sql);
end loop;
--经过测试，return 写不写效果都一样。
return;
end;
/
--==============调用示例--==============
select t.* from table(emp_init_data_fun(1，10)) t; 
select t.* from table(emp_init_data_fun()) t; 

自定义 split 函数

示例1：管道+Type(类型)实现其它语言中的 split 分割函数。

-- 创建类型(Type)——>split_table_type
create or replace type split_table_type as table of varchar2(32676);
/
-- 创建函数——> split
create or replace function split(p_list clob, p_sep varchar2 := ',' )
-- 根据匹配给定的分隔符来拆分字符串，没有匹配时，返回空对象。
--参数 p_list: 待分割的字符串。
--参数 p_sep: 分隔符，不明确指定时，默认为逗号。
--返回字符串(p_list)被指定字符(p_sep)分割后的表类型/集合。return split_table_typepipelined isv_idx  pls_integer;--直接使用 number 类型也可以 v_list varchar2(32676) := p_list;
beginloop--获取分隔符所在的位置v_idx := instr(v_list, p_sep);if v_idx > 0 then--通过管道输出/返回截取的目标元素pipe row(substr(v_list, 1, v_idx - 1));--去掉截取过的内容，保留未截取的内容，用于下次循环继续截取v_list := substr(v_list, v_idx + length(p_sep));elsif v_list != p_list then--处理最后一个分隔符到内容结尾的部分(比如 1##2##3,1##2##3##)pipe row(v_list);exit;elseexit;end if;end loop;
end;
/
--==============调用示例--==============
--这样查询时，返回的原生的集合，可视化效果不明显
SELECT split('1,223,32,56565') FROM dual T; 
--table 函数用于专门读取集合内容，列名默认为 column_value
select * from table(split('7369,7521,7900'));
select * from table(split(',7369,7521,,7900,'));
select * from table(split('7369,7521,7900','#'));
select * from table(split('','#'));--空结果
select * from table(split('#','#'));--返回一个null
select column_value as empno from table(split('7369=#=7521=#=7900=#=9000','=#='));
select column_value as empno from table(split('=#=7369=#=7521=#=7900=#=9000=#=','=#='));
select * from emp t where t.empno in (select * from table(split('7369,7521,7900')));
select * from emp t where t.empno in (select column_value as empno from table(split('7369,7521,7900')));