oracle 返回业务表的列

create or replace procedure sql_create( i_tbname varchar2, -- 默认大写(可以写成小写，但我会将其转换成大小去比较，这里可以按照你的需求去更改) i_except_columns varchar2, -- 要排除的字段名，多个字段用逗号(,)隔开(因为Oracle里面的字段默认均为大写，所以输入时要注意，当然可以用upper()函数，那是后话) o_sql out varchar2)is v_sqls varchar2(4000); i_except_columns2 varchar2(4000); v_sql varchar2(4000); v_cnt number(18,0);begin v_sql := ''; i_except_columns2 :=''''||replace(upper(i_except_columns),',',''',''')||''''; execute immediate 'select count(1) from user_tables where table_name=upper(:i_tbname)' into v_cnt using i_tbname; -- 看是否存在大写名字的表 if v_cnt = 1 then begin v_sqls := 'select trim(regexp_replace(wm_concat((case when m.column_name<>upper(m.column_name) then '' "''||m.column_name||''"'' else '' ''||m.column_name end)),'','','',''||chr(10))) as columnNames from user_tab_columns m where m.table_name=upper(:i_tbname) and m.column_name not in ('||i_except_columns2||') order by column_id asc'; execute immediate v_sqls into v_sql using i_tbname; o_sql := 'SELECT '||v_sql||chr(10)||' FROM '||i_tbname||';'; end; else begin execute immediate 'select count(1) from user_tables where table_name=:i_tbname' into v_cnt using i_tbname; -- 看是否存在小写名字的表 if v_cnt = 1 then begin v_sqls := 'select trim(regexp_replace(wm_concat((case when m.column_name<>upper(m.column_name) then '' "''||m.column_name||''"'' else '' ''||m.column_name end)),'','','',''||chr(10))) as columnNames from user_tab_columns m where m.table_name=:i_tbname and m.column_name not in ('||i_except_columns2||') order by column_id asc'; execute immediate v_sqls into v_sql using i_tbname; o_sql := 'SELECT '||v_sql||chr(10)||' FROM '||'"'||i_tbname||'"'; end; else o_sql := '对不起，表名为 '||i_tbname||'、'||upper(i_tbname)||' 在当前用户 '||user||' 下均不存在! '; end if; end; end if;end;

 

返回业务表的所有列。。。