求一个公用function合并所有字段到一个字段中

Question

本帖最后由 wildcorn 于 2009-07-23 15:50:16 编辑

Answer 1

要考虑过滤，和order by吗，已经多表关联。加上这些就复杂叻。

Answer 2

如果不保护过滤和order by已经统计的话，只是对全表来计算的话，那还不很复杂，用这样的方式select * from table(func('tablename'));这样的方式来调用func的参数是一个字符串，你需要访问的表名。返回是一个数组对象。实行用动态sql，和游标，加上几个系统表User_xxx的表来得到column的信息，然后访问table得到所有的记录，如果这里对非空记录不处理的话，可以先把columns连起来，拼成串，然后用replace替代里面的变化的参数，但是你这里是非空不写的，所以要动态拼了，这里只是大致的思路，

Answer 3

SQL> drop table test;
表已删除。SQL> create table test
  2  (
  3     id number primary key,
  4     colA varchar2(300),
  5     colb varchar2(300),
  6     colc varchar2(300)
  7  );
表已创建。SQL> comment on column test.id is 'comments_id';
注释已创建。
SQL> comment on column test.cola is 'commentsA';
注释已创建。
SQL> comment on column test.colb is 'commentsB';
注释已创建。
SQL> comment on column test.colc is 'commentsC';
注释已创建。SQL> 
SQL> select * from ALL_COL_COMMENTS atc where atc.TABLE_NAME ='TEST';OWNER     TABLE_NAME     COLUMN_NAME  COMMENTS
--------- -------------- ------------ ------------------------------
DINYA     TEST           ID           comments_id
DINYA     TEST           COLA         commentsA
DINYA     TEST           COLB         commentsB
DINYA     TEST           COLC         commentsCSQL> 
SQL> delete from test;
已删除0行。
SQL> insert into test values(1,'10','11','12');
已创建 1 行。
SQL> insert into test values(2,'20','21','22');
已创建 1 行。
SQL> commit;
提交完成。SQL> 
SQL> create or replace function get_comments
  2  (
  3     p_table_name in varchar2,
  4     p_id in number
  5  ) return varchar2 as
  6      v_id number := p_id;
  7      v_table_name varchar2(300) := upper(p_table_name);
  8      v_return varchar2(32767);
  9  
 10      v_sql_comments varchar2(32767):= 'select T.COMMENTS 
 11                                        from all_col_comments t 
 12                                        where t.table_name = :TABLE_NAME 
 13                                        AND T.COLUMN_NAME = :COL_NAME';
 14      v_sql_column varchar2(32767);
 15  
 16      --假设主键只有一个字段,而非多个字段的联合主键
 17      cursor c(s_table_name in varchar2,s_column in varchar2) is
 18      select atc.TABLE_NAME, atc.COLUMN_NAME 
 19      from all_tab_columns atc
 20      where atc.TABLE_NAME = s_table_name
 21      and atc.COLUMN_NAME != s_column
 22      order by
 23          atc.COLUMN_ID;
 24      
 25      v_primary_key varchar2(300);
 26      v_comments all_col_comments.comments%type;
 27      v_column_value varchar2(4000);
 28      
 29  begin
 30      --判断是否有主键
 31      begin
 32          select acc.column_name
 33          into v_primary_key
 34          from
 35              all_cons_columns acc,
 36              all_constraints ac
 37          where acc.constraint_name = ac.constraint_name
 38          and ac.constraint_type = 'P'
 39          and ac.table_name = v_table_name;
 40      exception
 41          when others then
 42              v_primary_key := '';
 43      end ;
 44  
 45      if v_primary_key is null then
 46          v_return := 'ERROR, no primary key on table:'||v_table_name;
 47      else
 48          for v_c in c(v_table_name,v_primary_key) loop
 49              EXECUTE IMMEDIATE v_sql_comments into v_comments using v_table_name,v_c.column_name
;
 50              v_sql_column := 'select '||v_c.column_name||' from '||v_c.table_name||' WHERE '||v_
primary_key||'='||v_id;
 51              EXECUTE IMMEDIATE v_sql_column into v_column_value;
 52              v_return := v_return ||v_comments ||':'||v_column_value||' ';
 53          end loop;
 54      end if;
 55      return v_return;
 56  end ;
 57  /函数已创建。SQL> select t.id,get_comments('test',t.id) from test t;        ID  GET_COMMENTS('TEST',T.ID)
----------  ----------------------------------------------------------------------
         1  commentsA:10 commentsB:11 commentsC:12
         2  commentsA:20 commentsB:21 commentsC:22SQL> 

Answer 4

--如果某列的数据为null,就不应该要加入到col_total中. 那就修改一下上面函数,处理一下空值的情况;

Answer 5

你好,用你的test测试可以通过
但用我的表就显示不出数据:
select t.corp_id,get_comments('t_CORP_INFO',t.corp_id) from t_CORP_INFO t;
CORP_ID              get_comments('t_CORP_INFO',t.corp_id)
-------------------- --------------------------------------
我的 corp_id是varchar不是number,请问是什么原因导致这个function无效.

Answer 6

你的表t_CORP_INFO中有记录吗? 还是function没有编译通过? 如果function没有编译通过, 你看一下是不是有语法问题. 如果编译通过, 且表中有数据的话, 应该会有记录显示出来的. 如果函数返回的值不正确,则把根据变量调试一下动态的SQL部分. 

Answer 7

9i以上的版本支持管道表函数了，执行思路同table(cast(func('tablename'))); 利用函数把所有的数据存到一个集合中，再对该函数返回的数据进行相关处理