获得表的相关信息

--获得表的相关信息
select distinct(a.column_id),a.column_name,b.comments,a.DATA_TYPE,a.DATA_PRECISION,a.DATA_LENGTH,a.DATA_SCALE,a.NULLABLE 
from user_tab_columns a,user_col_comments  b          
where a.table_name =UPPER( 'dyjs01') and b.table_name=UPPER('dyjs01') and a.COLUMN_NAME = b.column_name           
order by a.column_id
--将获取的信息插入到指定的表
insert into testxml(szsid,szname,szcomments,sztype) select distinct(a.column_id),a.column_name,b.comments,a.DATA_TYPE
from user_tab_columns a,user_col_comments  b          
where a.table_name =UPPER( 'dyjs01') and b.table_name=UPPER('dyjs01') and a.COLUMN_NAME = b.column_name           
order by a.column_id;
--截取指定字段的字符串
    SELECT CASE
　　WHEN INSTR(SZCOMMENTS,'-', 1, 1) > 0
　　THEN SUBSTR(SZCOMMENTS, 1, INSTR(SZCOMMENTS, '-', 1, 1) - 1)
    when instr(szcomments,' ',1,1) > 0
    then substr(szcomments,1,instr(szcomments,' ',1,1)-1)
　　ELSE szcomments
　　END AS szcomments
    FROM testxml;
--获取指定的字符串
    SELECT CASE
　　WHEN INSTR(comments,'-', 1, 1) > 0
　　THEN SUBSTR(comments, 1, INSTR(comments, '-', 1, 1) - 1)
    when instr(comments,' ',1,1) > 0
    then substr(comments,1,instr(comments,' ',1,1)-1)
　　ELSE comments
　　END AS comments
　　from   user_col_comments
    where table_name=UPPER('dyjs01');