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

 

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".

 

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管道函数的语法。

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

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

复制代码

 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 /

复制代码

若以上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

好文要顶 关注我 收藏该文  

马侃
关注 - 10
粉丝 - 86

+加关注

« 上一篇:PowerDesigner教程系列
» 下一篇:MySQL计划任务(事件调度器)(Event Scheduler)

posted on 2014-05-15 16:25 马侃 阅读(11150) 评论(1) 编辑 收藏

https://www.cnblogs.com/advocate/p/3730285.html

 

 

 

 

 

 

 

Oracle的pipelined函数实现高性能大数据处理

oraclepipelined 函数表函数 

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

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

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

 

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

 

下面是一个例子,将表t_ss_normal的记录插入到表t_target中,插入过程中有部分转换操作。

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

 

第一个方法,也是最常规的方法,代码如下:

 

Sql代码 

 收藏代码

  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代码 

 收藏代码

  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代码 

 收藏代码

  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代码 

 收藏代码

  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代码 

 收藏代码

  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代码 

 收藏代码

  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集合类型的使用总结  

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

Oracle的pipelined函数提升数据输出性能

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

https://mikixiyou.iteye.com/blog/1673672

 

 

 

 

 

 

 

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

2014年05月29日 23:05:26 indexman 阅读数:21067 标签: 管道函数pipelined 更多

个人分类: PL/SQL开发

所属专栏: PL/SQL开发

版权声明:凭栏处,潇潇雨歇。 https://blog.csdn.net/IndexMan/article/details/27580517

一 概述:

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

 

 

 

 

 

 

 

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

在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)、管道化表函数方式:

复制代码

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;

复制代码

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

调用函数的方式如下:

select * from table(f_pipe(5));

 

2)、 普通的方式:

复制代码

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;

复制代码

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

调用方式如下:

select * from table(f_normal(5));

 

OK, The End...

分类: DataBase

好文要顶 关注我 收藏该文  

逐风者
关注 - 0
粉丝 - 10

+加关注

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

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

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

 

 

 

 

 

 

博客园    首页    新随笔    联系    订阅订阅    管理

posts - 23,  comments - 40,  trackbacks - 0

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

在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)、管道化表函数方式:

复制代码

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;

复制代码

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

调用函数的方式如下:

select * from table(f_pipe(5));

 

2)、 普通的方式:

复制代码

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;

复制代码

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

调用方式如下:

select * from table(f_normal(5));

 

OK, The End...

分类: DataBase

好文要顶 关注我 收藏该文  

逐风者
关注 - 0
粉丝 - 10

+加关注

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

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

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

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 

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

 

相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页