流水线的功能 [英] Functions Pipelined

问题描述

您能向我解释一下吗:我在软件包说明中具有以下内容，而不是使用Oracle 10g的正文.

Could you please explain this to me: I have the following in a Package Specification and not the body using Oracle 10g.

TYPE t_fraud_ext IS TABLE OF FI_RPT.FI_Fraud_OBJ;


FUNCTION fraud_ext_Sql
(   schema_name  IN VARCHAR2
, select_beginning_business_date     IN DATE     – Start Date
, select_thru_business_date  IN DATE     – End Date
, select_beginning_business_time     IN VARCHAR2     – Start Time
, select_thru_business_time  IN VARCHAR2     – End Time
) RETURN VARCHAR2;



FUNCTION fraud_ext
(   schema_name  IN VARCHAR2
, select_beginning_business_date     IN DATE     – Start Date
, select_thru_business_date  IN DATE     – End Date
, select_beginning_business_time     IN VARCHAR2     – Start Time
, select_thru_business_time  IN VARCHAR2     – End Time
) RETURN t_fraud_ext PIPELINED;

这些有什么关系?从未使用过Pipelined函数.

How these are related ? never worked with Pipelined function.

我也喜欢一个非常详细的示例，说明如何在Package Specification/Body中使用管道功能.

I would also love a very detailed example on how to use pipeline function in a Package Specification / Body.

谢谢

推荐答案

鉴于您只有规范，我们无法确定fraud_ext是否调用了fraud_ext_Sql，但这与流水线函数无关.

give that you only have the spec, we cannot tell if fraud_ext_Sql is called by fraud_ext, but it's irrelevant in the context of a pipelined function.

要使用流水线函数，请从基本数组/嵌套表类型开始.例如:

to use a pipelined function you start with a base array / nested table type. eg:

SQL> create type test_typ as object (id number, txt varchar2(20));
  2  /

Type created.

SQL> create type test_tab as table of test_typ;
  2  /

Type created.

因此test_tab将用于流水线输出中.我们也可以有一个标量数组/嵌套表.例如这也可以与流水线函数一起使用:

so test_tab will be used in the pipelined output. we could have a scalar array/nested table too. e.g. this would also be valid to use with a pipelined function:

SQL> create type test_tab as table of varchar2(20);
  2  /

Type created.

一旦有了基本类型，就可以将函数定义为流水线.在下面的示例中，我将函数封装在一个包中，但这不是必须的；也可以使用独立功能.

once you have your base type, you define the function as pipelined. In the following example, i've encapsulated the function in a package, but this is not a requirement; stand-alone functions can be used too.

SQL> create package test_pkg
  2  as
  3    function get_data(p_id number)
  4    return test_tab pipelined;
  5  end;
  6  /

Package created.

pipelined关键字是关键.包体如下:

the pipelined keyword is the key. the package body is like the below:

SQL> create package body test_pkg
  2  as
  3
  4    function get_data(p_id number)
  5    return test_tab pipelined
  6    is
  7    begin
  8      for idx in 1..p_id
  9      loop
 10        pipe row(test_typ(idx, dbms_random.string('x', 2)));
 11      end loop;
 12    end get_data;
 13
 14  end;
 15  /

Package body created.

PIPE ROW命令将行抽回客户端.这个(与一般函式不同)会立即将资料列传回给客户(也就是，它不会等待整个集变现再寄回资料列之前)，您会看到这些资料列会依客户的arraysize设定控制成批地返回.这样做的好处是，在将数据发送回客户端之前，Oracle不需要将整个阵列保留在内存中.

the PIPE ROW command pumps a row back to the client. This (unlike regular functions) returns the row to the client immediately (i.e. it wont wait for the whole set to materialize before sending the rows back) you'll see rows coming back in batches controlled by your arraysize setting in your client. This has the advantage that Oracle doesn't need to hold the whole array in memory before sending the data back to the client.

要调用流水线函数，请使用table函数，如下所示:

so to call a pipelined function you use the table function like so:

SQL> select *
  2    from table(test_pkg.get_data(10));

        ID TXT
---------- --------------------
         1 3B
         2 AM
         3 1J
         4 36
         5 8I
         6 BM
         7 LS
         8 ON
         9 5Z
        10 D7

10 rows selected.

如果您有一个标量数组(如我之前提到的那样)，则pipe row命令只是直接具有该值，而没有任何类型名称:

if you had a scalar array, like I assuded to before, the pipe row command just has the value directly without any type name in it:

SQL> create type test_tab as table of varchar2(20);
  2  /

Type created.

SQL>
SQL> create package test_pkg
  2  as
  3    function get_data(p_id number)
  4    return test_tab pipelined;
  5  end;
  6  /

Package created.

SQL> create package body test_pkg
  2  as
  3
  4    function get_data(p_id number)
  5    return test_tab pipelined
  6    is
  7    begin
  8      for idx in 1..p_id
  9      loop
 10        pipe row(dbms_random.string('x', 2));
 11      end loop;
 12    end get_data;
 13
 14  end;
 15  /

Package body created.

SQL> select *
  2    from table(test_pkg.get_data(10));

COLUMN_VALUE
--------------------
GS
MJ
PF
D6
NG
WO
22
MV
96
8J

这篇关于流水线的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！