create or replace function col_to_comment(v_table_name varchar2) return varchar2 is v_sql varchar2(1000); begin execute immediate ' select listagg(COLUMN_NAME || '' AS '' || COMMENTS, '','') within group(order by rownum) from user_col_comments where TABLE_NAME = :1' into v_sql using upper(v_table_name); if v_sql is null then return null; else v_sql := 'select ' || v_sql || ' from '|| v_table_name; end if; return v_sql; end; / select col_to_comment('EMP') from dual ; / 查询视图,然后生成一条sql
select col as alias from t;
return varchar2
is
v_sql varchar2(1000);
begin
execute immediate ' select listagg(COLUMN_NAME || '' AS '' || COMMENTS, '','') within group(order by rownum)
from user_col_comments
where TABLE_NAME = :1' into v_sql using upper(v_table_name);
if v_sql is null
then return null;
else
v_sql := 'select ' || v_sql || ' from '|| v_table_name;
end if;
return v_sql;
end;
/
select col_to_comment('EMP') from dual ;
/
查询视图,然后生成一条sql