--获得表的相关信息
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');