Oracle 存储过程使用 Pipelined Table 管道化表函数 Pipe row() 返回结果集集合表类型提高性能 Oracle的pipelined函数实现高性能大数据处理

深藏阁楼爱情的钟 2022-04-01 05:48 815阅读 0赞

[https://docs.oracle.com/cd/E18283\_01/appdev.112/e17126/pipe\_row\_statement.htm][https_docs.oracle.com_cd_E18283_01_appdev.112_e17126_pipe_row_statement.htm]

# PIPE ROW Statement #

The `PIPE` `ROW` statement, which can appear only in the body of a pipelined table function, returns a table row (but not control) to the invoker of the function. A pipelined table function is declared with the option ["PIPELINED"][PIPELINED].

[https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/PIPE-ROW-statement.html\#GUID-AD2713A9-062A-42DD-B49E-804C6120378B][https_docs.oracle.com_en_database_oracle_oracle-database_12.2_lnpls_PIPE-ROW-statement.html_GUID-AD2713A9-062A-42DD-B49E-804C6120378B]

# Database PL/SQL Language Reference #

## 13.46 PIPE ROW Statement ##

### [oracle管道函数的用法][oracle] ###

[oracle][oracle 1]管道函数是一类特殊的函数，oracle管道函数返回值类型必须为集合，下面将介绍oracle管道函数的语法。

在普通的函数中，使用dbms\_output输出的信息，需要在服务器执行完整个函数后一次性的返回给客户端。如果需要在客户端实时的输出函数执行过程中的一些信息，在oracle9i以后可以使用管道函数(pipeline function)。

关键字PIPELINED表明这是一个oracle管道函数，oracle管道函数的返回值类型必须为集合，在函数中，PIPE ROW语句被用来返回该集合的单个元素，函数以一个空的RETURN 语句结束，以表明它已经完成。

![复制代码][2018122915210494]

1 create or replace type MsgType as table of varchar2(4000);
     2 /
     3 
     4 create or replace function f_pipeline_test return MsgType
     5   PIPELINED as
     6 begin
     7   for i in 1 .. 10 loop
     8     pipe row('Iteration ' || i || ' at ' || systimestamp);
     9     sys.dbms_lock.sleep(1);
    10   end loop;
    11   pipe row('All done!');
    12   return;
    13 end;
    14 /

![复制代码][2018122915210494]

若以上function执行过程中提示错误：“Error: PLS-00201: 必须说明标识符 'DBMS\_LOCK'”，则是由于授权的原因，可通过如下方案解决：

conn system/bitservice@ttonline as sysdba;
    grant execute on dbms_lock to tt;

为了在sql\*plus演示管道函数的执行效果，我们需要将arraysize为1，否则服务器会按照默认的15来向客户端返回信息，这会影响我们的测试效果。

set arraysize 1
    select * from table( f_pipeline_test );

如果要在pipeline中执行DML操作，则必须使用自治事务，否则会报ORA-14551错误。

在oracle9205及其之后的版本中，在pipeline function中使用自治事务，则必须在pipe row之前提交或者回滚事务，否则会报ORA-06519错误。

分类: [Oracle][]

好文要顶 关注我 收藏该文 ![20181229152104184][] ![20181229152104226][]

[![20181229152104275][]][20181229152104275 1]

[马侃][20181229152104275 1]  
[关注 - 10][- 10]  
[粉丝 - 86][- 86]

\+加关注

[« ][Link 1]上一篇：[PowerDesigner教程系列][Link 1]  
[» ][Link 2]下一篇：[MySQL计划任务(事件调度器)(Event Scheduler)][Link 2]

posted on 2014-05-15 16:25 [马侃][Link 3] 阅读(11150) 评论(1) [编辑][Link 4] [收藏][Link 5]

[https://www.cnblogs.com/advocate/p/3730285.html][oracle]

### [Oracle的pipelined函数实现高性能大数据处理][Oracle_pipelined] ###

*  **博客分类：** 
 *  [Oracle][Oracle 1]

[oracle][oracle 2][pipelined 函数][pipelined][表函数][Link 6]

在plsql开发中，会涉及到一些大数据量表的数据处理，如将某记录数超亿的表的记录经过处理转换插入到另外一张或几张表。

常规的操作方法固然可以实现，但时间、磁盘IO、redo日志等等都非常大。Oracle 提供了一种高级函数，可以将这种数据处理的性能提升到极限。这种函数称为管道函数。

在实际项目中，管道函数会和表函数、数据流函数（即表函数和CURSOR结合）、数据集合、并行度一起使用，达到大数据处理的性能顶峰。

(miki西游 @mikixiyou 原文链接: [http://mikixiyou.iteye.com/blog/1673672][Oracle_pipelined] )

下面是一个例子，将表t\_ss\_normal的记录插入到表t\_target中，插入过程中有部分转换操作。

我分成四个方法来实现这个数据处理操作。

第一个方法，也是最常规的方法，代码如下：

Sql代码

![收藏代码][20181229152854337]

1.  create table T\_SS\_NORMAL  
2.  (  
3.    owner          VARCHAR2(30),  
4.    object\_name    VARCHAR2(128),  
5.    subobject\_name VARCHAR2(30),  
6.    object\_id      NUMBER,  
7.    data\_object\_id NUMBER,  
8.    object\_type    VARCHAR2(19),  
9.    created        DATE,  
10.   last\_ddl\_time  DATE,  
11.   timestamp      VARCHAR2(19),  
12.   status         VARCHAR2(7),  
13.   temporary      VARCHAR2(1),  
14.   generated      VARCHAR2(1),  
15.   secondary      VARCHAR2(1)  
16. );  
17. /  
18.   
19. create table T\_TARGET  
20. (  
21.   owner       VARCHAR2(30),  
22.   object\_name VARCHAR2(128),  
23.   comm        VARCHAR2(10)  
24. );

这是源表和目标表的表结构。现在源表有200W条，其数据来自dba\_objects视图。

Sql代码

![收藏代码][20181229152854337]

1.  create or replace package pkg\_test is  
2.    procedure load\_target\_normal;  
3.  end pkg\_test;  
4.    
5.  create or replace package body pkg\_test is  
6.    procedure load\_target\_normal is  
7.    begin    
8.      insert into t\_target (owner, object\_name, comm)  
9.        select owner, object\_name, 'xxx' from t\_ss\_normal;    
10.     commit;    
11.   end;  
12. begin  
13.   null;  
14. end pkg\_test;

一个insert into select语句搞定这个数据处理，简单。

第二方法，采用管道函数实现这个数据处理。

Sql代码

![收藏代码][20181229152854337]

1.  create type obj\_target as object(  
2.  owner VARCHAR2(30), object\_name VARCHAR2(128), comm varchar2(10)  
3.  );  
4.  /  
5.  create or replace type typ\_array\_target as table of obj\_target;  
6.  /  
7.    
8.  create or replace package pkg\_test is  
9.    
10.   function pipe\_target(p\_source\_data in sys\_refcursor) return typ\_array\_target  
11.     pipelined;  
12.   
13.   procedure load\_target;  
14. end pkg\_test;

首先创建两个自定义的类型。obj\_target的定义和t\_target的表结构一致，用于存储每一条目标表记录。typ\_array\_target用于管道函数的返回值。

接着定义一个管道函数。

普通函数的结尾加一个pipelined关键字，就是管道函数。这个函数的返回参数类型为集合，这是为了使其能作为表函数使用。表函数就是在from子句中以table(v\_resultset)调用的，v\_resultset就是一个集合类型的参数。

最后定义一个调用存储过程。

在包体中定义该管道函数和调用存储过程。管道函数pipe\_target的传入参数一个sys\_refcursor类型。这是一个游标，可以理解为使用select \* from table才能得到的结果集。

你也可以不用这个传入的游标，取而代之，在函数中定义一个游标，也一样使用。

Sql代码

![收藏代码][20181229152854337]

1.    function pipe\_target(p\_source\_data in sys\_refcursor) return typ\_array\_target  
2.      pipelined is  
3.      r\_target\_data obj\_target := obj\_target(null, null, null);  
4.      r\_source\_data t\_ss%rowtype;  
5.    
6.   begin  
7.      
8.      loop  
9.        fetch p\_source\_data  
10.         into r\_source\_data;  
11.       exit when p\_source\_data%notfound;      
12.         
13.       r\_target\_data.owner       := r\_source\_data.owner;  
14.       r\_target\_data.object\_name := r\_source\_data.object\_name;  
15.       r\_target\_data.comm        := 'xxx';      
16.       pipe row(r\_target\_data);  
17.       
18.     end loop;  
19.     
20.     close p\_source\_data;  
21.     return;  
22.     
23.   end;  
24.   
25.   procedure load\_target is  
26.   begin    
27.     insert into t\_target  
28.       (owner, object\_name, comm)  
29.       select owner, object\_name, comm  
30.         from table(pipe\_target(cursor(select \* from t\_ss\_normal)));    
31.     commit;    
32.   end;  
33.

关键字 pipe row 的作用是将obj\_target插入到typ\_array\_target类型的数组中，管道函数自动返回这些数据。

因为源表的数据量会非常大，所以在fetch取值时会使用bulk collect ，实现批量取值。这样做可以减少plsql引擎和sql引擎的控制转换次数。这种转换称为上下文切换。

Sql代码

![收藏代码][20181229152854337]

1.  function pipe\_target\_array(p\_source\_data in sys\_refcursor,  
2.                             p\_limit\_size  in pls\_integer default c\_default\_limit)  
3.    return typ\_array\_target  
4.    pipelined is    
5.    r\_target\_data obj\_target := obj\_target(null, null, null);   
6.       
7.    type typ\_source\_data is table of t\_ss%rowtype index by pls\_integer;  
8.    aa\_source\_data typ\_source\_data;  
9.    
10. begin  
11.   
12.   loop  
13.     fetch p\_source\_data bulk collect  
14.       into aa\_source\_data;  
15.     exit when aa\_source\_data.count = 0;  
16.     
17.     for i in 1 .. aa\_source\_data.count loop  
18.       
19.       r\_target\_data.owner       := aa\_source\_data(i).owner;  
20.       r\_target\_data.object\_name := aa\_source\_data(i).object\_name;  
21.       r\_target\_data.comm        := 'xxx';  
22.       
23.       pipe row(r\_target\_data);  
24.       
25.     end loop;  
26.     
27.   end loop;  
28.   
29.   close p\_source\_data;  
30.   return;  
31.   
32. end;  
33.   
34.   
35. procedure load\_target\_array is  
36. begin  
37.   insert into t\_target  
38.     (owner, object\_name, comm)  
39.     select owner, object\_name, comm  
40.       from table(pipe\_target\_array(cursor (select \* from t\_ss\_normal),  
41.                                    100));    
42.   commit;    
43. end;

还可以使用并行度，使得管道函数可以多进程同时执行。并行度还有一个好处，就是将数据插入方式从常规路径转换为直接路径。直接路径可以大量减少redo日志的生成量。

Sql代码

![收藏代码][20181229152854337]

1.  function pipe\_target\_parallel(p\_source\_data in sys\_refcursor,  
2.                                p\_limit\_size  in pls\_integer default c\_default\_limit)  
3.    return typ\_array\_target  
4.    pipelined  
5.    parallel\_enable(partition p\_source\_data by any) is  
6.    
7.    r\_target\_data obj\_target := obj\_target(null, null, null);  
8.    
9.    type typ\_source\_data is table of t\_ss%rowtype index by pls\_integer;    
10.   aa\_source\_data typ\_source\_data;  
11.   
12. begin    
13.   loop  
14.     fetch p\_source\_data bulk collect  
15.       into aa\_source\_data;  
16.     exit when aa\_source\_data.count = 0;      
17.     for i in 1 .. aa\_source\_data.count loop        
18.       r\_target\_data.owner       := aa\_source\_data(i).owner;  
19.       r\_target\_data.object\_name := aa\_source\_data(i).object\_name;  
20.       r\_target\_data.comm        := 'xxx';        
21.       pipe row(r\_target\_data);        
22.     end loop;      
23.   end loop;    
24.   close p\_source\_data;  
25.   return;  
26.   
27. end;  
28.   
29.   
30. procedure load\_target\_parallel is  
31. begin  
32.   execute immediate 'alter session enable parallel dml';    
33.   insert /\*+parallel(t,4)\*/  
34.   into t\_target t  
35.     (owner, object\_name, comm)  
36.     select owner, object\_name, comm  
37.       from table(pipe\_target\_array(cursor (select /\*+parallel(s,4)\*/  
38.                                      \*  
39.                                       from t\_ss\_normal s),  
40.                                    100));    
41.   commit;  
42. end;

在测试过程中，我测试200W记录的操作，时间从24秒降到到8秒，重做日志也降低更多。

在此数据处理操作中，涉及到集合(collection)、表函数、管道函数、流函数、bulk collect、游标等知识点。

### [PLSQL集合类型的使用总结][PLSQL]   ###

### [使用bulk collect insert实现大数据快速迁移][bulk collect insert] ###

### [Oracle的pipelined函数提升数据输出性能][Oracle_pipelined 1] ###

### 以前写的这三篇文章，都详细介绍了这些知识点，更多可以去参考oracle官方文档。 ###

[https://mikixiyou.iteye.com/blog/1673672][Oracle_pipelined]

原

# Oracle管道函数(Pipelined Table Function)介绍 #

2014年05月29日 23:05:26 [indexman][] 阅读数：21067 标签： [管道函数][Link 7][pipelined][pipelined 1] 更多

个人分类： [PL/SQL开发][PL_SQL]

所属专栏： [PL/SQL开发][PL_SQL 1]

## 一 概述： ##

1、管道函数即是可以返回行集合（可以使嵌套表nested table 或数组 varray）的函数，我们可以像查询物理表一样查询它或者将其

赋值给集合变量。

2、管道函数为并行执行，在普通的函数中使用dbms\_output输出的信息，需要在服务器执行完整个函数后一次性的返回给客户端。如果需要在客户端

实时的输出函数执行过程中的一些信息，在oracle9i以后可以使用管道函数(pipeline function)。

3、关键字PIPELINED表明这是一个oracle管道函数，oracle管道函数的返回值类型必须为集合，在函数中，PIPE ROW语句被用来返回该集合的单个元

素，函数以一个空的RETURN 语句结束，以表明它已经完成。

4、由于管道函数的并发多管道流式设计以及实时返回查询结果而去除了中间环节因此可以带来可观的性能提升。

## 二、如何编写管道函数： ##

例1：

1.  `CREATE OR REPLACE PACKAGE pkg1 AS`
2.  `TYPE numset_t IS TABLE NUMBER;`
3.  `FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;`
4.  `END pkg1;`

1.  `CREATE OR REPLACE PACKAGE BODY pkg1 AS`
2.  `FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS`
3.  `BEGIN`
4.  `FOR i IN 1..x LOOP`
5.  `PIPE ROW(i);`
6.  `END LOOP;`
7.  `RETURN;`
8.  `END;`
9.  `END pkg1;`

SELECT * FROM TABLE(pkg1.f1(5));

COLUMN\_VALUE  
\------------------------  
           1  
           2  
           3  
           4  
           5

## 三 管道函数用于数据转换： ##

例2：管道函数可以和常规函数一样接收任何参数，下面的管道函数中参数为ref cursor。

1.  `CREATE OR REPLACE PACKAGE refcur_pkg IS`
2.  `TYPE refcur_t IS REF CURSOR RETURN emp%ROWTYPE;`
3.  `TYPE outrec_typ IS RECORD (`
4.  `var_num NUMBER(6),`
5.  `var_char1 VARCHAR2(30),`
6.  `var_char2 VARCHAR2(30));`
7.  `TYPE outrecset IS TABLE OF outrec_typ;`
8.  `FUNCTION f_trans(p refcur_t)`
9.  `RETURN outrecset PIPELINED;`
10. `END refcur_pkg;`

1.  `CREATE OR REPLACE PACKAGE BODY refcur_pkg IS`
2.  `FUNCTION f_trans(p refcur_t)`
3.  `RETURN outrecset PIPELINED IS`
4.  `out_rec outrec_typ;`
5.  `in_rec p%ROWTYPE;`
6.  `BEGIN`
7.  `LOOP`
8.  `FETCH p INTO in_rec;`
9.  `EXIT WHEN p%NOTFOUND;`
10. `-- first row`
11. `out_rec.var_num := in_rec.empno;`
12. `out_rec.var_char1 := in_rec.ename;`
13. `out_rec.var_char2 := in_rec.mgr;`
14. `PIPE ROW(out_rec);`
15. `-- second row`
16. `out_rec.var_num := in_rec.deptno;`
17. `out_rec.var_char1 := in_rec.deptno;`
18. `out_rec.var_char2 := in_rec.job;`
19. `PIPE ROW(out_rec);`
20. `END LOOP;`
21. `CLOSE p;`
22. `RETURN;`
23. `END;`
24. `END refcur_pkg;`

1.  `SELECT * FROM TABLE(`
2.  `refcur_pkg.f_trans(CURSOR(SELECT * FROM emp WHERE empno=7782)));`

VAR\_NUM       VAR\_CHAR1           VAR\_CHAR2  
\---------- ------------------------------ ------------------------------  
      7782       CLARK                          7839  
        10         10                             MANAGER

## 四 用法扩展： ##

1、表函数间传递数据：

SELECT \* FROM TABLE(f(CURSOR(SELECT \* FROM TABLE(g()))));

2、使用游标变量接收管道函数返回的结果：

OPEN c FOR SELECT \* FROM TABLE(f(...));

3、使用多个游标变量入参：

例3：

-- Define the ref cursor types
    CREATE PACKAGE refcur_pkg IS
      TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;
      TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;  
      TYPE outrec_typ IS RECORD ( 
        var_num    NUMBER(6),
        var_char1  VARCHAR2(30),
        var_char2  VARCHAR2(30));
      TYPE outrecset IS TABLE OF outrec_typ;
      FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2) 
        RETURN outrecset PIPELINED;
    END refcur_pkg;
    /
    
    CREATE PACKAGE BODY refcur_pkg IS
    FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2) 
        RETURN outrecset PIPELINED IS
        out_rec outrec_typ;
        in_rec1 p1%ROWTYPE;
        in_rec2 p2%ROWTYPE;
    BEGIN
      LOOP
        FETCH p2 INTO in_rec2;
        EXIT WHEN p2%NOTFOUND;
      END LOOP;
      CLOSE p2;
      LOOP
        FETCH p1 INTO in_rec1;
        EXIT WHEN p1%NOTFOUND;
        -- first row
        out_rec.var_num := in_rec1.employee_id;
        out_rec.var_char1 := in_rec1.first_name;
        out_rec.var_char2 := in_rec1.last_name;
        PIPE ROW(out_rec);
        -- second row
        out_rec.var_num := in_rec2.department_id;
        out_rec.var_char1 := in_rec2.department_name;
        out_rec.var_char2 := TO_CHAR(in_rec2.location_id);
        PIPE ROW(out_rec);
      END LOOP;
      CLOSE p1;
      RETURN;
    END;
    END refcur_pkg;
    /
    
    -- SELECT query using the g_trans table function
    SELECT * FROM TABLE(refcur_pkg.g_trans(
      CURSOR(SELECT * FROM employees WHERE department_id = 60),
      CURSOR(SELECT * FROM departments WHERE department_id = 60)));

4、管道函数作为聚合函数使用：

例4：

CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30),
                              weight NUMBER, grade NUMBER);
    INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);
    INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3);
    INSERT INTO gradereport VALUES('Mark','Maths', 3, 3);
    INSERT INTO gradereport VALUES('Mark','Economics', 3, 4);
    
    CREATE PACKAGE pkg_gpa IS
      TYPE gpa IS TABLE OF NUMBER;
      FUNCTION weighted_average(input_values SYS_REFCURSOR)
        RETURN gpa PIPELINED;
    END pkg_gpa;
    /
    CREATE PACKAGE BODY pkg_gpa IS
    FUNCTION weighted_average(input_values SYS_REFCURSOR)
      RETURN gpa PIPELINED IS
      grade NUMBER;
      total NUMBER := 0;
      total_weight NUMBER := 0;
      weight NUMBER := 0;
    BEGIN
    -- The function accepts a ref cursor and loops through all the input rows
      LOOP
         FETCH input_values INTO weight, grade;
         EXIT WHEN input_values%NOTFOUND;
    -- Accumulate the weighted average
         total_weight := total_weight + weight;
         total := total + grade*weight;
      END LOOP;
      PIPE ROW (total / total_weight);
      RETURN; -- the function returns a single result
    END;
    END pkg_gpa;
    /
    -- the query result comes back as a nested table with a single row
    -- COLUMN_VALUE is a keyword that returns the contents of a nested table
    SELECT w.column_value "weighted result" FROM TABLE(
           pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;

5、在管道函数中进行DML操作,我们使用自治事务使管道函数作为独立事务处理：

`CREATE FUNCTION f(p SYS_REFCURSOR)`  
`  RETURN CollType PIPELINED IS`  
`    PRAGMA AUTONOMOUS_TRANSACTION;`  
`BEGIN `

NULL;

`END;`  
`/`

`6、对管道函数进行DML操作：`

`实际上我们无法直接对管道函数进行DML操作，例如以下语句都会失败：`

`UPDATE F(CURSOR(SELECT * FROM tab)) SET col = value;   INSERT INTO f(...) VALUES ('any', 'thing');`

官方给出的方案是创建一个基于管道函数的VIEW，然后在这个VIEW上创建相应的instead of 触发器。下面给出操作实例：

1.  `CREATE OR REPLACE VIEW V_F_TRANS AS`
2.  `SELECT x.var_num, x.var_char1, x.var_char2`
3.  `FROM TABLE(refcur_pkg.f_trans(CURSOR (SELECT *`
4.  `FROM emp))) x;`

1.  `CREATE OR REPLACE TRIGGER tri_f_trans`
2.  `INSTEAD OF INSERT ON v_f_trans`
3.  `FOR EACH ROW`
4.  `BEGIN`
5.  `dbms_output.put_line('Trigger of a pipelined funtion based view was on fire!');`
6.  `END;`

SCOTT@orcl> insert into v\_f\_trans values(102, 'abc','def');  
Trigger of a pipelined funtion based view was on fire!

\---------------------------------------

By    Dylan.

[https://blog.csdn.net/indexman/article/details/27580517][https_blog.csdn.net_indexman_article_details_27580517]

[在Oracle的函数中,返回表类型][Oracle 2]

在SQL Server中有表变量,可以在function中方便地返回,习惯SQL Server或者需要把脚本从SQL Server转到Oracle中的朋友可以都会碰到这个问题.

Oracle的function中怎么返回表变量?

太晚了,过多的理论知识就不说了,下面简单地说实现吧!..

**1、创建表对象类型。**

在Oracle中想要返回表对象，必须自定义一个表类型，如下所示：

create or replace type t\_table is table of number;

上面的类型定义好后，在function使用可用返回一列的表，如果需要多列的话，需要先定义一个对象类型。然后把对象类型替换上面语句中的number；

定义对象类型：

create or replace type obj\_table as object  
(  
  id int,  
  name varchar2(50)  
)

修改表对象类型的定义语句如下：

create or replace type t\_table is table of obj\_table;

**2、 创建演示函数**

在函数的定义中,可以使用管道化表函数和普通的方式,下面提供两种使用方式的代码:

1)、管道化表函数方式：

![复制代码][20181229152104314]

create or replace function f\_pipe(s number)  
return t\_table pipelined  
as  
    v\_obj\_table obj\_table;     
begin      
for i in 1..s loop   
    v\_obj\_table :=  obj\_table(i,to\_char(i\*i));  
    pipe   row(v\_obj\_table);     
end loop;  
return;  
end f\_pipe;

![复制代码][20181229152104314]

注意:管道的方式必须使用空的return表示结束.

调用函数的方式如下：

select \* from table(f\_pipe(5));

2)、 普通的方式:

![复制代码][20181229152104314]

create or replace function f\_normal(s number)  
return t\_table  
as  
    rs t\_table:= t\_table();  
begin  
    for i in 1..s loop  
        rs.extend;  
        rs(rs.count) := obj\_table(rs.count,'name'||to\_char(rs.count));  
        --rs(rs.count).name := rs(rs.count).name || 'xxxx';  
    end loop;  
return rs;  
end f\_normal;

![复制代码][20181229152104314]

初始化值后还可以想注视行那样进行修改.

调用方式如下:

select \* from table(f\_normal(5));

OK, The End...

分类: [DataBase][]

好文要顶 关注我 收藏该文 ![20181229152104471][] ![20181229152854583][]

[![20181229152104521][]][20181229152104521 1]

[逐风者][20181229152104521 1]  
[关注 - 0][- 0]  
[粉丝 - 10][- 10 1]

\+加关注

[« ][Link 8]上一篇：[在Word 中编辑文档的时候 有时鼠标点击无响应][Link 8]  
[» ][Link 9]下一篇：[使用自定义控件的方式实现一个分页控件][Link 9]

posted on 2009-05-26 00:42 [逐风者][Link 10] 阅读(18873) 评论(10) [编辑][Link 11] [收藏][Link 12]

[http://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html][http_www.cnblogs.com_liguiqing_archive_2009_05_26_1489243.html]

# Oracle函数返回Table集合 #

2013年01月11日 10:35:53 [FighterLiu][] 阅读数：512

在实际的应用中，为了让PL/SQL 函数返回数据的多个行，必须通过返回一个 REF CURSOR 或一个数据集合来完成。REF CURSOR 的这种情况局限于可以从查询中选择的数据，而整个集合在可以返回前，必须进行具体化。 9i 通过引入的管道化表函数纠正了后一种情况。表函数是返回整个行的集（通常作为一个集合）的函数，可以直接从 SQL 语句中进行查询，就好像它是一个真正的数据库表一样。管道化表函数与之相似，但是它像在构建时一样返回数据，而不是一次全部返回。管道化表函数更加有效，因为数据可以尽可能快地返回。

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

1.使用自定义类型

1.  `/* Formatted on 2010/02/26 08:42 (Formatter Plus v4.8.8) */`
2.  `CREATE OR REPLACE TYPE objemp AS OBJECT (`
3.  `maxsal NUMBER,`
4.  `minsal NUMBER`
5.  `);`
6.   
7.   
8.  `/* Formatted on 2010/02/26 08:43 (Formatter Plus v4.8.8) */`
9.  `CREATE OR REPLACE TYPE tabemp AS TABLE OF objemp;`

2.使用Pipeline管道函数和Pipe row()

1.  `CREATEORREPLACEFUNCTIONgetmaxminsalary(department NUMBER)`
2.   
3.  `RETURN tabemp PIPELINED`
4.   
5.  `AS`
6.   
7.  `maximum_salary NUMBER;`
8.   
9.  `minimum_salary NUMBER;`
10.  
11. `v_errorcode NUMBER;`
12.  
13. `v_errortext VARCHAR2(200);`
14.  
15. `v objemp;`
16.  
17. `BEGIN`
18.  
19. `FOR myrow IN(SELECTMAX(sal) m_sal,MIN(sal) min_sal`
20.  
21. `FROMemp`
22.  
23. `WHERE deptno = departmnet)`
24.  
25. `LOOP`
26.  
27. `v :=(myrow.m_sal, myrow.min_sal);`
28.  
29. `PIPE ROW(v);`
30.  
31. `ENDLOOP;`
32.  
33.  
34.  
35. `RETURN;`
36.  
37. `EXCEPTION`
38.  
39. `WHENOTHERS`
40.  
41. `THEN`
42.  
43. `v_errorcode :=SQLCODE;`
44.  
45. `v_errortext :=SUBSTR(SQLERRM,1,200);`
46.  
47.  
48.  
49. `INSERTINTOlog_table`
50.  
51. `(code, MESSAGE, info`
52.  
53. `)`
54.  
55. `VALUES(v_errorcode, v_errortext,'getMaxMinSalary'`
56.  
57. `);`
58.  
59. `END;`
60.

3.使用Table操作符

1.  `SELECT * FROM TABLE(getMaxMinSalary(10));`
2.

[https://blog.csdn.net/xinghui\_liu/article/details/8492565][https_blog.csdn.net_xinghui_liu_article_details_8492565]

[博客园][Link 13]    [首页][Link 10]    [新随笔][Link 14]    [联系][Link 15]    [订阅][Link 16][![订阅][20181226143618155]][Link 16]    [管理][Link 17]

posts - 23,  comments - 40,  trackbacks - 0

[在Oracle的函数中,返回表类型][Oracle 2]

在SQL Server中有表变量,可以在function中方便地返回,习惯SQL Server或者需要把脚本从SQL Server转到Oracle中的朋友可以都会碰到这个问题.

Oracle的function中怎么返回表变量?

太晚了,过多的理论知识就不说了,下面简单地说实现吧!..

**1、创建表对象类型。**

在Oracle中想要返回表对象，必须自定义一个表类型，如下所示：

create or replace type t\_table is table of number;

上面的类型定义好后，在function使用可用返回一列的表，如果需要多列的话，需要先定义一个对象类型。然后把对象类型替换上面语句中的number；

定义对象类型：

create or replace type obj\_table as object  
(  
  id int,  
  name varchar2(50)  
)

修改表对象类型的定义语句如下：

create or replace type t\_table is table of obj\_table;

**2、 创建演示函数**

在函数的定义中,可以使用管道化表函数和普通的方式,下面提供两种使用方式的代码:

1)、管道化表函数方式：

![复制代码][20181226143618253]

注意:管道的方式必须使用空的return表示结束.

调用函数的方式如下：

select \* from table(f\_pipe(5));

2)、 普通的方式:

![复制代码][20181226143618253]

初始化值后还可以想注视行那样进行修改.

调用方式如下:

select \* from table(f\_normal(5));

OK, The End...

分类: [DataBase][]

好文要顶 关注我 收藏该文 ![20181226143618307][] ![20181226143618325][]

[![20181226143618340][]][20181229152104521 1]

[逐风者][20181229152104521 1]  
[关注 - 0][- 0]  
[粉丝 - 10][- 10 1]

\+加关注

[« ][Link 8]上一篇：[在Word 中编辑文档的时候 有时鼠标点击无响应][Link 8]  
[» ][Link 9]下一篇：[使用自定义控件的方式实现一个分页控件][Link 9]

posted on 2009-05-26 00:42 [逐风者][Link 10] 阅读(18857) 评论(10) [编辑][Link 11] [收藏][Link 12]

[http://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html][http_www.cnblogs.com_liguiqing_archive_2009_05_26_1489243.html]

参考：[https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11\_QUESTION\_ID:331217279811][https_asktom.oracle.com_pls_asktom_f_p_100_11_P11_QUESTION_ID_331217279811]

### To: Anup Toshniwal re: returning Incorrect type code ###

February 12, 2008 - 2:36 am UTC

Reviewer: Stew Ashton from Paris, France

I'm not at work so I can't test this, but you might try using orai18n.jar in addition to ojdbc14.jar. It provides NLS support.  
  
[http://www.oracle.com/technology/software/tech/java/sqlj\_jdbc/htdocs/jdbc\_10201.html][http_www.oracle.com_technology_software_tech_java_sqlj_jdbc_htdocs_jdbc_10201.html]

###  ###

February 13, 2008 - 7:41 am UTC

Reviewer: haaseg from Germany

We also started using the JDBC Array interface mapped by Oracle to object relational data.
    
    This is a good solution to provide data into procedures.
    But we changed to use the PL/SQL PIPE ROW machanism and sql table operator to read data from stored procedures.
    See
    http://www.psoug.org/reference/pipelined.html
    for an example.
    There are many advantages using PIPE ROW:
    
    1. you can read the data via the standard JDBC/resultset interface without using oracle specific extensions.
    
    2. You don't have to overload the procedure when attributes are added to the result and you want to be backward compatible.
    
    3. it needs less resources for large arrays because it is piped.
    
    4. Personal performance tests show that the time need is comparable to ref cursors (of cause the fastet solution) and the arr

参考：[https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11\_QUESTION\_ID:331217279811][https_asktom.oracle.com_pls_asktom_f_p_100_11_P11_QUESTION_ID_331217279811]

[https_docs.oracle.com_cd_E18283_01_appdev.112_e17126_pipe_row_statement.htm]: https://docs.oracle.com/cd/E18283_01/appdev.112/e17126/pipe_row_statement.htm
[PIPELINED]: https://docs.oracle.com/cd/E18283_01/appdev.112/e17126/function.htm#CJABACGF
[https_docs.oracle.com_en_database_oracle_oracle-database_12.2_lnpls_PIPE-ROW-statement.html_GUID-AD2713A9-062A-42DD-B49E-804C6120378B]: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/PIPE-ROW-statement.html#GUID-AD2713A9-062A-42DD-B49E-804C6120378B
[oracle]: https://www.cnblogs.com/advocate/p/3730285.html
[oracle 1]: http://database.51cto.com/art/201009/228094.htm
[2018122915210494]: /images/20220401/fbfcea3da4984a389f7e841dba851d6e.png
[Oracle]: https://www.cnblogs.com/advocate/category/244720.html
[20181229152104184]: https://img-blog.csdnimg.cn/20181229152104184
[20181229152104226]: /images/20220401/8b5543f7aed041ed84f475d58d42e8f5.png
[20181229152104275]: /images/20220401/1854bea15eab4da382b5458ea1a63331.png
[20181229152104275 1]: http://home.cnblogs.com/u/advocate/
[- 10]: http://home.cnblogs.com/u/advocate/followees
[- 86]: http://home.cnblogs.com/u/advocate/followers
[Link 1]: https://www.cnblogs.com/advocate/p/3730027.html
[Link 2]: https://www.cnblogs.com/advocate/p/3755650.html
[Link 3]: https://www.cnblogs.com/advocate/
[Link 4]: /images/20220401/45ddcde70c7e4aa38f163faa419b6940.pngEditPosts.aspx?postid=3730285
[Link 5]: https://www.cnblogs.com/advocate/p/3730285.html#
[Oracle_pipelined]: https://mikixiyou.iteye.com/blog/1673672
[Oracle 1]: https://mikixiyou.iteye.com/category/217570
[oracle 2]: http://www.iteye.com/blogs/tag/oracle
[pipelined]: http://www.iteye.com/blogs/tag/pipelined%20%E5%87%BD%E6%95%B0
[Link 6]: http://www.iteye.com/blogs/tag/%E8%A1%A8%E5%87%BD%E6%95%B0
[20181229152854337]: /images/20220401/17077a08633c472f86ae088ed46616e0.png
[PLSQL]: https://mikixiyou.iteye.com/blog/1607889
[bulk collect insert]: https://mikixiyou.iteye.com/blog/1626906
[Oracle_pipelined 1]: https://mikixiyou.iteye.com/blog/1628397
[indexman]: https://me.csdn.net/IndexMan
[Link 7]: http://so.csdn.net/so/search/s.do?q=%E7%AE%A1%E9%81%93%E5%87%BD%E6%95%B0&t=blog
[pipelined 1]: http://so.csdn.net/so/search/s.do?q=pipelined&t=blog
[PL_SQL]: https://blog.csdn.net/IndexMan/article/category/1490285
[PL_SQL 1]: https://blog.csdn.net/column/details/18552.html
[https_blog.csdn.net_indexman_article_details_27580517]: https://blog.csdn.net/indexman/article/details/27580517
[Oracle 2]: https://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html
[20181229152104314]: /images/20220401/2de1dfa3f48e494c82316d56a90216b5.png
[DataBase]: https://www.cnblogs.com/liguiqing/category/105614.html
[20181229152104471]: https://img-blog.csdnimg.cn/20181229152104471
[20181229152854583]: /images/20220401/54f3728ffbd34455b5329251c70b656e.png
[20181229152104521]: /images/20220401/1b0a0ef21efa4fe3a10def3df097e578.png
[20181229152104521 1]: http://home.cnblogs.com/u/liguiqing/
[- 0]: http://home.cnblogs.com/u/liguiqing/followees
[- 10 1]: http://home.cnblogs.com/u/liguiqing/followers
[Link 8]: https://www.cnblogs.com/liguiqing/archive/2008/06/17/1223906.html
[Link 9]: https://www.cnblogs.com/liguiqing/archive/2009/05/30/1492380.html
[Link 10]: https://www.cnblogs.com/liguiqing/
[Link 11]: /images/20220401/45ddcde70c7e4aa38f163faa419b6940.pngEditPosts.aspx?postid=1489243
[Link 12]: http://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html#
[http_www.cnblogs.com_liguiqing_archive_2009_05_26_1489243.html]: http://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html
[FighterLiu]: https://me.csdn.net/xinghui_liu
[https_blog.csdn.net_xinghui_liu_article_details_8492565]: https://blog.csdn.net/xinghui_liu/article/details/8492565
[Link 13]: https://www.cnblogs.com/
[Link 14]: /images/20220401/45ddcde70c7e4aa38f163faa419b6940.pngEditPosts.aspx?opt=1
[Link 15]: https://msg.cnblogs.com/send/%E9%80%90%E9%A3%8E%E8%80%85
[Link 16]: https://www.cnblogs.com/liguiqing/rss
[20181226143618155]: https://img-blog.csdnimg.cn/20181226143618155
[Link 17]: /images/20220401/45ddcde70c7e4aa38f163faa419b6940.png
[20181226143618253]: /images/20220401/947e773472a245b896e71a66870ee99f.png
[20181226143618307]: https://img-blog.csdnimg.cn/20181226143618307
[20181226143618325]: /images/20220401/3315345bc584444b84a22b1eb30d3d61.png
[20181226143618340]: /images/20220401/62413e17e7b6434f8526bf573d698158.png
[https_asktom.oracle.com_pls_asktom_f_p_100_11_P11_QUESTION_ID_331217279811]: https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:331217279811
[http_www.oracle.com_technology_software_tech_java_sqlj_jdbc_htdocs_jdbc_10201.html]: http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html