ORACLE 使用函数返回表数据

#1.建立一个表对象类型

create or replace type mcard.obj_table_field as object
(
  sectionid number(1),
  cardid     int,
  orderid    number(3),
  fieldlist  VARCHAR2(100)
)

#2.建立一个对应的类型

create or replace type mcard.t_table_field is table of obj_table_FIELD

#3.函数

CREATE OR REPLACE FUNCTION MCARD.fn_get_fieldgroup(p_userid       IN NUMBER,
                                                         p_cardid       IN NUMBER,
                                                         p_field IN VARCHAR2 DEFAULT '',
                                                         p_prerecoflg  IN NUMBER DEFAULT 0)

 RETURN mcard.t_table_field
  pipelined AS

  v_obj_table_field mcard.obj_table_field;

BEGIN

             ......

              v_obj_table_field := mcard.obj_table_field(v_sectionid,
                                                                     p_cardid,
                                                                     v_ORDER,
                                                                     v_fieldlist);
              pipe row(v_obj_table_field);

             ......

  return;

END ;

#4.调用

   INSERT INTO tbl_test
     select *
       FROM TABLE(fn_get_fieldgroup(v_userid, v_cardid, v_field, v_prerecoflg) a