oracle嵌套表--补充（所用函数说明）

/*

PL/SQL表---table()函数用法：
利用table()函数，我们可以将PL/SQL返回的结果集代替table。

simple example：

1、table()结合数组：

*/

create or replace type t_test as object(
id integer,
rq date,
mc varchar2(60)
);

create or replace type t_test_table as table of t_test;

create or replace function f_test_array(n in number default null) return t_test_table
as
v_test t_test_table := t_test_table();
begin
        for i in 1 .. nvl(n,100) loop
                v_test.extend();        --   append   one   null   element
                                                  --   为增加的元素赋值，如果没用EXTEND，这里会出错 
                v_test(v_test.count) := t_test(i,sysdate,'mc'||i);
        end loop;
        return v_test;
end f_test_array;
/

select * from table(f_test_array(10));

/*

2、table()结合PIPELINED函数：

*/

create or replace function f_test_pipe(n in number default null) return t_test_table PIPELINED
as
v_test t_test_table := t_test_table();
begin
        for i in 1 .. nvl(n,100) loop
                pipe row(t_test(i,sysdate,'mc'||i));
        end loop;
return;
end f_test_pipe;
/

select * from table(f_test_pipe(20));

/*

3、table()结合系统包：

*/

create table test (id varchar2(20));
insert into test values('1');
commit;
explain plan for select * from test;
select * from table(dbms_xplan.display);

 extend()：        Oracle   在逻辑上是由各个表空间(tablespace)构成的，
                            tablespace由segments(段)构成
                            段是由extends构成   中文叫作区   或者数据区 
                           区是由一个一个的数据块构成   数据块的大小由操作系统决定。

                     Pipelined Table实现split函数的示例

   split作用: 比如说把一个字符串A,B,C,D,E   根据逗号分隔，转换成一个数组，数组中的每个元素是
                  A
                  B
                  C
                  D
                  E

               

在实际的应用中，为了让PL/SQL 函数返回数据的多个行，必须通过返回一个 REF CURSOR 或一个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据，而整个集合在可以返回前，必须进行具体化。

Oracle 9i 通过引入的管道化表函数纠正了后一种情况。表函数是返回整个行的集（通常作为一个集合）的函数，可以直接从 SQL 语句中进行查询，就好像它是一个真正的数据库表一样。管道化表函数与之相似，但是它像在构建时一样返回数据，而不是一次全部返回。管道化表函数更加有效，因为数据可以尽可能快地返回。

管道化表函数必须返回一个集合。在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成。一旦我们创建了上述函数，我们就可以使用 TABLE 操作符从 SQL 查询中调用它。

管道化表函数经常被用来把数据从一种类型转化成另一种类型。

下面是用Pipelined Table实现split函数的示例：

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN ty_str_split PIPELINED
IS
    j INT := 0;
    i INT := 1;
    len INT := 0;
    len1 INT := 0;
    str VARCHAR2 (4000);
BEGIN
    len := LENGTH (p_str);
    len1 := LENGTH (p_delimiter);

    WHILE j < len
    LOOP
        j := INSTR (p_str, p_delimiter, i);  -----instr(待匹配的字符串，匹配字符串，开始位置，匹配次数)

        IF j = 0
        THEN
            j := len;
            str := SUBSTR (p_str, i);
            PIPE ROW (str);

            IF i >= len
            THEN
                EXIT;
            END IF;
        ELSE
            str := SUBSTR (p_str, i, j - i); ----取得字符串中指定起始位置和长度的字符串   substr( string,          start_position, [ length ] )

            i := j + len1;
            PIPE ROW (str);
        END IF;
    END LOOP;

    RETURN;
END fn_split;
/

测试：

SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;'));

结果：
1
12
123
1234
12345

又一个简单的例子：

CREATE TYPE mytype AS OBJECT (
    field1 NUMBER,
    field2 VARCHAR2 (50)
);

CREATE TYPE mytypelist AS TABLE OF mytype;

CREATE OR REPLACE FUNCTION pipelineme
    RETURN mytypelist PIPELINED
IS
    v_mytype mytype;
BEGIN
    FOR v_count IN 1 .. 20
    LOOP
        v_mytype := mytype (v_count, 'Row ' || v_count);
        PIPE ROW (v_mytype);
    END LOOP;

    RETURN;
END pipelineme;

 

SELECT * FROM TABLE (pipelineme);

FIELD1 FIELD2
------ ------------------------
     1 Row 1
     2 Row 2
     3 Row 3
     4 Row 4
     5 Row 5
     6 Row 6
     7 Row 7
     8 Row 8
     9 Row 9
    10 Row 10
    11 Row 11
    12 Row 12
    13 Row 13
    14 Row 14
    15 Row 15
    16 Row 16
    17 Row 17
    18 Row 18
    19 Row 19
    20 Row 20

20 rows selected