PL/SQL--Collection
官方文档；http://docs.oracle.com/cd/B10501_01/appdev.920/a96624/05_colls.htm#20425
主要有三种常用的集合：VARRAY,NEST_TABLE(嵌套表),联合数组(index_by表，类似于MAP中的键-值对应)，这里主要介绍varray，在最后给了一个联合数组的例子
集合中常用到的方法：
1、EXISTS(row) returns TRUE if the row specified exists.
2、COUNT returns the number of rows.
3、FIRST returns the row number of the first populated row.
4、LAST returns the row number of the last populated row.
5、PRIOR(row) returns the row number of the last row populated before the row specified.
6、NEXT(row) returns the row number of the next row populated after the row specified.
7、DELETE removes all rows.
8、DELETE(row) removes the specified row.
9、DELETE(start_row,end_row) removes all rows between and including the start_row and end_row.
10、TRIM removes the last row.
11、TRIM(n) removes the last n rows.
12、EXTEND adds one row.
13、EXTEND(n) adds n rows.
14、EXTEND(n,m) adds n copies of row m.
-----------------------------------------------------------------------------------------------------------
下面的例子使用到了以下几个方面的知识:
(1)定义VARRAY对象,并实例化变量
(2)赋初值
(3)数组的LIMIT方法，类似于JAVA中的LENGTH
(4)数组的COUNT方法，元素的实际个数
(5)EXTEND(N)，添加N行元素，添加一行使用EXTEND
(6)LAST方法，最后一个元素，FIRST。第一个元素,这两个参数一般用于指定FOR循环的临界值
(7)使用FOR循环访问每个元素
(8)EXISTS(N)判断第N行数据是否存在
(9)TRIM(N),删除末尾的n行数据
1 DECLARE
2 --定义一个VARRAY对象，它的元素的类型为VARCHAR2，假如需要设定内部的元素不能为NULL
3 --则只需在类型后面添加 NOT NULL即可
4 --命名规范：类型_VARRAY
5 TYPE VARCHAR2_VARRAY IS VARRAY(21) OF VARCHAR2(50);
6 --实例化该对象的变量，并赋予初值为两个元素
7 VARRAY_VARCHAR2 VARCHAR2_VARRAY := VARCHAR2_VARRAY('A', 'B');
8 BEGIN
9 --EXIST方法的使用
10 IF VARRAY_VARCHAR2.EXISTS(3) THEN
11 DBMS_OUTPUT.PUT_LINE('存在第三行');
12 ELSE
13 DBMS_OUTPUT.PUT_LINE('不存在第三行');
14 END IF;
15 DBMS_OUTPUT.PUT_LINE('数组的上限为：' || VARRAY_VARCHAR2.LIMIT);
16 DBMS_OUTPUT.PUT_LINE('数组的长度为：' || VARRAY_VARCHAR2.COUNT);
17 VARRAY_VARCHAR2.EXTEND; --追加一行数据
18 VARRAY_VARCHAR2(VARRAY_VARCHAR2.LAST) := '我是最后新增的';
19 --使用FOR循环进行循环打印
20 FOR IDX IN VARRAY_VARCHAR2.FIRST .. VARRAY_VARCHAR2.LAST LOOP
21 DBMS_OUTPUT.PUT('VARRAY_VARCHAR2[' || IDX || '] ');
22 DBMS_OUTPUT.PUT_LINE(VARRAY_VARCHAR2(IDX) || '');
23 END LOOP;
24 --TRIM(N)删除最后的N行数据
25 DBMS_OUTPUT.PUT_LINE('删除前数组的长度为：' || VARRAY_VARCHAR2.COUNT);
26 VARRAY_VARCHAR2.TRIM(1);
27 --通过EXTEND方面追加N行数据
28 DBMS_OUTPUT.PUT_LINE('删除后数组的长度为：' || VARRAY_VARCHAR2.COUNT);
29 VARRAY_VARCHAR2.EXTEND(100);
30 VARRAY_VARCHAR2.EXTEND;
31 --打印总记录数
32 DBMS_OUTPUT.PUT_LINE('数组的长度为：' || VARRAY_VARCHAR2.COUNT);
33 EXCEPTION
34 WHEN OTHERS THEN
35 DBMS_OUTPUT.PUT_LINE('下标超出定义的限制。大于了21');
36 END;
定义一个含有2个元素，元素的类型为VARCHAR2，且长度不超过15的VARRAY:
1 CREATE OR REPLACE TYPE ADDRESS IS VARRAY(2) OF VARCHAR2(15)
定义含有VARRAY类型的表:
1 CREATE TABLE USER_INFO(USER_NAME VARCHAR2(100),USER_ADDRESS ADDRESS)
向表中插入数据，需要使用VARRAY的构造器来初始化VARRAY的数据
1 INSERT INTO USER_INFO
2 (USER_NAME, USER_ADDRESS)
3 VALUES
4 ('小明', ADDRESS('西安', '高新区'));
更新语句
1 UPDATE user_info
2 SET user_name = '小红',
3 user_address = address('北京','公主坟')
4 WHERE user_name = '小红';
使用table方法查值
1 SELECT * FROM USER_INFO USER_INFO, TABLE(USER_INFO.USER_ADDRESS)
使用函数，显示引用类型的数值
1 CREATE OR REPLACE FUNCTION GET_ELEVALUES(VISIT USER_INFO.USER_ADDRESS%TYPE)
2 RETURN VARCHAR2 IS
3 V_RETURN VARCHAR2(4000);
4 BEGIN
5 --通过FOR循环取值
6 FOR INX IN 1 .. VISIT.COUNT LOOP
7 V_RETURN := V_RETURN || VISIT(INX) || ',';
8 DBMS_OUTPUT.PUT_LINE('===' || INX);
9 END LOOP;
10 RETURN SUBSTR(V_RETURN, 0, LENGTH(V_RETURN) - 1);
11 END;
使用函数查询结果:
1 SELECT U.USER_NAME, GET_ELEVALUES(U.USER_ADDRESS) FROM USER_INFO U
VARRAY的游标的使用
1 DECLARE
2 --定义一个游标
3 CURSOR EMP_CURSOR IS
4 SELECT * FROM EMP E ORDER BY E.EMPNO;
5 --定义一个VARRAY类型
6 TYPE EMP_VARRAY IS VARRAY(100) OF EMP%ROWTYPE;
7 EMPS EMP_VARRAY;
8 INX1 PLS_INTEGER;
9 INX2 PLS_INTEGER;
10 BEGIN
11 INX1 := 0;
12 --初始化元素个数，设置0个可用元素
13 EMPS := EMP_VARRAY();
14 --游标for循环赋值
15 FOR EMP IN EMP_CURSOR LOOP
16 INX1 := INX1 + 1;
17 EMPS.EXTEND();
18 EMPS(INX1).EMPNO := EMP.EMPNO;
19 EMPS(INX1).ENAME := EMP.ENAME;
20 EMPS(INX1).JOB := EMP.JOB;
21 EMPS(INX1).SAL := EMP.SAL;
22 EMPS(INX1).DEPTNO := EMP.DEPTNO;
23 END LOOP;
24 --for循环输出变量
25 FOR INX2 IN 1 .. EMPS.COUNT LOOP
26 DBMS_OUTPUT.PUT_LINE('EMONO:' || EMPS(INX2).EMPNO || ' ENAME:' || EMPS(INX2)
27 .ENAME);
28 END LOOP;
29 END;
有两种方法访问VRARRAY的元素的方式
函数和CAST和ROWNUM并列使用
--定义函数访问表中的VRARRAY的元素的方法
1 CREATE OR REPLACE FUNCTION VISIT_ELE(VISIT USER_INFO.USER_ADDRESS%TYPE,
2 SUBSCRIPT PLS_INTEGER) RETURN VARCHAR2 IS
3 BEGIN
4 --判断下标是否越界
5 IF SUBSCRIPT <= VISIT.LAST THEN
6 RETURN VISIT(SUBSCRIPT);
7 ELSE
8 RETURN NULL;
9 END IF;
10 END;
使用函数查询结果
1 SELECT VISIT_ELE(U.USER_ADDRESS, 1) || ',' || VISIT_ELE(U.USER_ADDRESS, 2)
2 FROM USER_INFO U
使用DECODE方法优化输入结果
1 SELECT DECODE(VISIT_ELE(U.USER_ADDRESS, 1),
2 NULL,
3 'NO MEMBERS',
4 VISIT_ELE(U.USER_ADDRESS, 1))
5 FROM USER_INFO U WHERE u.user_name = '小明'
使用case语句优化输出结果
1 SELECT CASE
2 WHEN VISIT_ELE(U.USER_ADDRESS, 3) IS NULL THEN
3 'NO MEMBERS'
4 ELSE
5 VISIT_ELE(U.USER_ADDRESS, 3)
6 END
7 FROM USER_INFO U
使用CAST语句来查询VARRAY的列值:
保留关键子COLUMN_VALUE是访问嵌套表中记录行的一种方法
(表中还有一个nested_table_id的隐藏列，他是这样的一个外键，他映射到附表的记录行
THE是SQL的一个保留关键字，利用它可以从嵌套表的一个查询中检索出COLUMN_VALUE列
方法CASt将显式的将VARRAY类型的USER_ADDRESS转换成嵌套表，该嵌套表被定义为数据库中一个集合类型
1 SELECT COLUMN_VALUE
2 FROM THE (SELECT CAST(C.USER_ADDRESS AS ADDRESS)
3 FROM USER_INFO C
4 WHERE C.USER_NAME = '小红')
带有ROWNUM的CAST查询
1 SELECT N, V_VALUES
2 FROM (SELECT ROWNUM N, COLUMN_VALUE V_VALUES
3 FROM THE (SELECT CAST(C.USER_ADDRESS AS ADDRESS)
4 FROM USER_INFO C
5 WHERE C.USER_NAME = '小红'))
6 WHERE V_VALUES IS NOT NULL
使用ROWNUM来实现类似之前的函数的功能，过滤某一行的数据，
1 SELECT N, V_VALUES
2 FROM (SELECT ROWNUM N, COLUMN_VALUE V_VALUES
3 FROM THE (SELECT CAST(C.USER_ADDRESS AS ADDRESS)
4 FROM USER_INFO C
5 WHERE C.USER_NAME = '小红'))
6 WHERE V_VALUES IS NOT NULL AND N = 2
使用the和value关键字查询数据
1 SELECT VALUE(V)
2 FROM THE (SELECT U.USER_ADDRESS
3 FROM USER_INFO U
4 WHERE U.USER_NAME = '小明') V
5 WHERE VALUE(V) IS NOT NULL
---------------------------------------------------------------------------------------------------------
联合数组:联合数组不需要进行初始化，也没有构造函数语法:联合数组也没有extend方法
测试构造方法:
1 DECLARE
2 TYPE emp_table IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
3 emp emp_table := emp_table('A','B');--使用构造函数，不知道该函数
4 BEGIN
5 NULL;
6 END;
在未知联合数组的元素的时候，也不能进行遍历
1 DECLARE
2 TYPE EMP_TABLE IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
3 EMPS EMP_TABLE;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(EMPS(1));
6 NULL;
7 END;
作为一个通用的原则，我们应该极力的避免它：下面是一个解决方案
1 DECLARE
2 TYPE EMP_TABLE IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
3 EMPS EMP_TABLE;
4 BEGIN
5 IF EMPS.COUNT <> 0 THEN
6 DBMS_OUTPUT.PUT_LINE(EMPS(1));
7 ELSE
8 DBMS_OUTPUT.PUT_LINE('THERE IS NO DATA');
9 END IF;
10 END;
一个简单的联合数组的使用:
1 DECLARE
2 --定义一个联合数组--年份，
3 TYPE YEAR_TABLE IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
4 --初始化联合数组的一个变量,元素个数为12，且不允许为空
5 YEARS YEAR_TABLE;
6 --定义一个VARRAY类型
7 TYPE MONTH_VARRAY IS VARRAY(12) OF VARCHAR2(100);
8 --定义一个VARRAY类型变量，并使用构造方法赋值
9 MONTHES MONTH_VARRAY := MONTH_VARRAY('1月',
10 '2月',
11 '3月',
12 '4月',
13 '5月',
14 '6月',
15 '7月',
16 '8月',
17 '9月',
18 '10月',
19 '11月',
20 '12月');
21 BEGIN
22 IF YEARS.COUNT = 0 THEN
23 DBMS_OUTPUT.PUT_LINE('联合数组初始化--START');
24
25 END IF;
26 FOR IDX IN MONTHES.FIRST .. MONTHES.LAST LOOP
27 YEARS(IDX) := '';
28 DBMS_OUTPUT.PUT_LINE('INDEX [' || IDX || '] IS ' || YEARS(IDX));
29 --赋值
30 YEARS(IDX) := MONTHES(IDX);
31 END LOOP;
32 DBMS_OUTPUT.PUT_LINE('联合数组初始化--end');
33 DBMS_OUTPUT.PUT_LINE('--------------------');
34 DBMS_OUTPUT.PUT_LINE('联合数组输出--start');
35 FOR IDX IN MONTHES.FIRST .. MONTHES.LAST LOOP
36 DBMS_OUTPUT.PUT_LINE('INDEX [' || IDX || '] IS ' || YEARS(IDX));
37 END LOOP;
38 DBMS_OUTPUT.PUT_LINE('联合数组输出--end');
39 END;
Oracle Table Function:参考网址:http://www.oracle-base.com/articles/misc/PipelinedTableFunctions.php
Table Functions（PL/SQL）
Table functions are used to return PL/SQL collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations.（由于潜在的瓶颈，一把不再联机事务处理系统中使用table函数） Regular table functions require named row and table types to be created as database objects.
-- Create the types to support the table function.
DROP TYPE t_tf_tab;
DROP TYPE t_tf_row;
CREATE TYPE t_tf_row AS OBJECT (
id NUMBER,
description VARCHAR2(50)
);
/
CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/
-- Build the table function itself.
CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS
l_tab t_tf_tab := t_tf_tab();
BEGIN
FOR i IN 1 .. p_rows LOOP
l_tab.extend;
l_tab(l_tab.last) := t_tf_row(i, 'Description for ' || i);
END LOOP;
RETURN l_tab;
END;
/
-- Test it.
SELECT *
FROM TABLE(get_tab_tf(10))
ORDER BY id DESC;
ID DESCRIPTION
---------- --------------------------------------------------
10 Description for 10
9 Description for 9
8 Description for 8
7 Description for 7
6 Description for 6
5 Description for 5
4 Description for 4
3 Description for 3
2 Description for 2
1 Description for 1
10 rows selected.
SQL>
Notice the above output is in reverse order because the query includes a descending order by clause.
Table Functions（TABLE）
1 CREATE OR REPLACE TYPE mem_type IS VARRAY(10) of VARCHAR2(15);
建立表：
1 CREATE TABLE club (Name VARCHAR2(10),
2 Address VARCHAR2(20),
3 City VARCHAR2(20),
4 Phone VARCHAR2(8),
5 Members mem_type)
1 INSERT INTO CLUB
2 VALUES
3 ('AL',
4 '111 First St.',
5 'Mobile',
6 '222-2222',
7 MEM_TYPE('Brenda', 'Richard'));
8
9 INSERT INTO CLUB
10 VALUES
11 ('FL',
12 '222 Second St.',
13 'Orlando',
14 '333-3333',
15 MEM_TYPE('Gen', 'John', 'Steph', 'JJ'));
查询:(使用TABLE函数)
SELECT C.NAME, C.ADDRESS, P.COLUMN_VALUE FROM CLUB C, TABLE(C.MEMBERS) P;
查询全部的数据:
1 SELECT E CFROM CLUB C, TABLE(C.MEMBERS) P;
结果:
在SQL语句中，使用table函数，用来在表中查询数据，必须关联主表:
Manipulating Individual Elements
So far, you have manipulated whole collections. Within SQL, to manipulate the individual elements of a collection, use the operator TABLE. The operand of TABLE is a subquery that returns a single column value for you to manipulate. That value is a nested table or varray.
In the following example, you add a row to the History Department nested table stored in column courses:
BEGIN INSERT INTO TABLE(SELECT courses FROM department WHERE name = 'History') VALUES(3340, 'Modern China', 4); END;
In the next example, you revise the number of credits for two courses offered by the Psychology Department:
DECLARE adjustment INTEGER DEFAULT 1; BEGIN UPDATE TABLE(SELECT courses FROM department WHERE name = 'Psychology') SET credits = credits + adjustment WHERE course_no IN (2200, 3540); END;
In the following example, you retrieve the number and title of a specific course offered by the History Department:
DECLARE
my_course_no NUMBER(4);
my_title VARCHAR2(35);
BEGIN
SELECT course_no, title INTO my_course_no, my_title
FROM TABLE(SELECT courses FROM department
WHERE name = 'History')
WHERE course_no = 3105;
...
END;
In the next example, you delete all 5-credit courses offered by the English Department:
BEGIN DELETE TABLE(SELECT courses FROM department WHERE name = 'English') WHERE credits = 5; END;
In the following example, you retrieve the title and cost of the Maintenance Department's fourth project from the varray column projects:
DECLARE my_cost NUMBER(7,2); my_title VARCHAR2(35); BEGIN SELECT cost, title INTO my_cost, my_title FROM TABLE(SELECT projects FROM department WHERE dept_id = 50) WHERE project_no = 4; ... END;
TABLE函数的使用：
也可以同样的使用the和value()关键字完成该功能：
1 --1、使用TABLE关键字
2 SELECT * FROM TABLE (SELECT T.MEMBERS FROM CLUB T WHERE T.NAME = 'AL');
3 /**
4 保留关键子COLUMN_VALUE是访问嵌套表中记录行的一种方法
5 (表中还有一个nested_table_id的隐藏列，他是这样的一个外键，他映射到附表的记录行
6 THE是SQL的一个保留关键字，利用它可以从嵌套表的一个查询中检索出COLUMN_VALUE列
7 方法CAST将显式的将VARRAY类型的USER_ADDRESS转换成嵌套表，该嵌套表被定义为数据库中一个集合类型
8 **/
9 SELECT COLUMN_VALUE
10 FROM THE (SELECT T.MEMBERS FROM CLUB T WHERE T.NAME = 'AL');
11 SELECT *
12 FROM THE (SELECT CAST(T.MEMBERS AS MEM_TYPE)
13 FROM CLUB T
14 WHERE T.NAME = 'AL');
15 SELECT *
16 FROM THE (SELECT CAST(T.MEMBERS AS MEM_TYPE)
17 FROM CLUB T
18 WHERE T.NAME = 'AL');
19 --使用VALUE()和THE关键字
20 SELECT VALUE(V)
21 FROM THE (SELECT T.MEMBERS FROM CLUB T WHERE T.NAME = 'AL') V;
-----
1 CREATE OR REPLACE TYPE MEM_TYPE1 IS OBJECT
2 (
3 SUBSTRING VARCHAR2(15)
4 )
--
1 CREATE OR REPLACE TYPE aaa AS TABLE OF mem_type1
--
1 DECLARE
2 V_A AAA;
3 V_MEM_TYPE1 MEM_TYPE1;
4 BEGIN
5 V_MEM_TYPE1 := MEM_TYPE1('1');
6 V_MEM_TYPE1 := MEM_TYPE1('2');
7 V_MEM_TYPE1 := MEM_TYPE1('3');
8 V_MEM_TYPE1 := MEM_TYPE1('4');
9 V_A := AAA(V_MEM_TYPE1,
10 MEM_TYPE1('2'),
11 MEM_TYPE1('2'),
12 MEM_TYPE1('2'));
13 FOR I IN (SELECT SUBSTRING FROM TABLE(V_A)) LOOP
14 NULL;
15 DBMS_OUTPUT.PUT_LINE(I.SUBSTRING);
16 END LOOP;
17 END;
--
