Orace List部署

create or replace type list_agg  as object

(

   total varchar2(4000),

   static function

        ODCIAggregateInitialize(sctx IN OUT list_agg )

        return number,

   member function

        ODCIAggregateIterate(self IN OUT list_agg ,

                             value IN varchar2 )

        return number,

   member function

        ODCIAggregateTerminate(self IN list_agg,

                               returnValue OUT  varchar2,

                               flags IN number)

        return number,

   member function

        ODCIAggregateMerge(self IN OUT list_agg,

                           ctx2 IN list_agg)

        return number

);

/

create or replace type body list_agg

is

static function ODCIAggregateInitialize(sctx IN OUT list_agg)

return number

is

begin

    sctx := list_agg(null);

    return ODCIConst.Success;

end;

member function ODCIAggregateIterate(self IN OUT list_agg,

                                     value IN varchar2 )

return number

is

begin

    self.total := self.total||','||value;

    return ODCIConst.Success;

end;

 

member function ODCIAggregateTerminate(self IN list_agg,

                                       returnValue OUT varchar2,

                                       flags IN number)

return number

is

begin

    returnValue := substr(self.total,2);

    return ODCIConst.Success;

end;

member function ODCIAggregateMerge(self IN OUT list_agg,

                                   ctx2 IN list_agg)

return number

is

begin

    self.total := self.total||ctx2.total;

    return ODCIConst.Success;

end;

end;

/

CREATE OR REPLACE FUNCTION list(input varchar2 )

RETURN varchar2

PARALLEL_ENABLE AGGREGATE USING list_agg;

/

 

 

##上面LIST的创建是使用网络上的资料，下面操作是本人自己写的。主要是想说明LIST函数使用带来的好处之一。

 

 

#################################

 

select 'update ' ||a.table_name || ' set ' || replace(list(a.column_name||' =nvl(trim('||a.column_name ||'),''/%'')'),'/%',' ')

        from user_tab_cols a

      where a.table_name <> 'MSTM803_BRAND'

      and a.table_name <> 'VMSTM301_PMAMNGER'

      and a.table_name <> 'vmstm311_supplier'

      and a.table_name <> 'VMSTM315_DLVER'  

      and rowunm<20   

      group by a.table_name;     

      commit;

--通过上面查询的sql语句进行修改全库表为空格的数据，如果buffer不够自己添加buffer空间,或修改rownum的值

 ###版权-----found2008 ------邮箱:hiho1128@126.com