oracle 系统函数

1、查询表的列名和类型

SELECT t.column_name,t.data_type||'('||t.data_length||')'
      FROM user_tab_cols  t
    WHERE table_name ='表名';

2、其他股东及股权情况

select
          pro_cid,
          org_cid,
          ltrim(max(sys_connect_by_path(gqjg, '； ')), '； ') add_mc
     from
  (select pro_cid,
          org_cid,
          i.sha_name||': '||concat(i.sha_rate*100,'%')gqjg,
          row_number() over(partition by pro_cid,org_cid order by pro_cid) rn_by_id,
          row_number() over(order by pro_cid) + pro_cid rn
    from dc_sha_inf i
    Where i.invet_id=3)
  start with rn_by_id = 1
connect by rn - 1 = prior rn
  group by pro_cid, org_cid
  order by pro_cid

3、方便取列名和长度

SELECT t.column_name,t.data_type||'('||t.data_length||')'

 FROM user_tab_cols  t 

WHERE table_name ='大写表名;

4、wm_concat函数(解决股东和股权结构)

Select a.pro_cid,a.org_cid,wm_concat(a.sha_name||':'||a.sha_rate||'%')t
  From dc_sha_inf a
  Group By a.pro_cid,a.org_cid