这个语句是写在shell脚步中的,我都贴来大家帮帮忙看看啊connectstr='username/password@connectstr' table=`echo $*|sed 's/.*from *\([^ ]*\) *.*/\1/'|tr "[a-z]" "[A-Z]"` where=`echo $*|sed 's/.*from/from/'` cat > sel$$.sql << END set head off set feed off set headsep off set newp none set linesize 255 set sqlblanklines OFF set trimspool ON set termout off spool on$$.sql select 'select '''||column_name||'|'',ltrim(rtrim('||column_name||')) $where;' from user_tab_columns where table_name='$table'; spool off set termout on select '##SQL BEGIN##' from dual; select 'ColumnName| Value' from dual; select '-----------| ------------' from dual; @@on$$.sql select '##SQL END##' from dual; exit END sqlplus $connectstr @sel$$.sql|sed -n '/##SQL BEGIN##/,/##SQL END/{ /^$/d /##SQL/d p }'|awk -F'|' '{printf "%-30s%-s\n",$1,$2}' rm -f on$$.sql sel$$.sql
select 'select '''||column_name||'|'',ltrim(rtrim('||column_name||')) $where;' from user_tab_columns where table_name='$table'; 比如有table a,其中有列string得到的结果是: select 'string|',string $where(是个变量,表示某具体字符串);这个sql语句将显示某条件下a表的string列名,以及列中的数据。开始那个sql语句可以得到下面这个sql语句。
table=`echo $*|sed 's/.*from *\([^ ]*\) *.*/\1/'|tr "[a-z]" "[A-Z]"`
where=`echo $*|sed 's/.*from/from/'`
cat > sel$$.sql << END
set head off
set feed off
set headsep off
set newp none
set linesize 255
set sqlblanklines OFF
set trimspool ON
set termout off
spool on$$.sql
select 'select '''||column_name||'|'',ltrim(rtrim('||column_name||')) $where;' from user_tab_columns where table_name='$table';
spool off
set termout on
select '##SQL BEGIN##' from dual;
select 'ColumnName| Value' from dual;
select '-----------| ------------' from dual;
@@on$$.sql
select '##SQL END##' from dual;
exit
END
sqlplus $connectstr @sel$$.sql|sed -n '/##SQL BEGIN##/,/##SQL END/{ /^$/d /##SQL/d p }'|awk -F'|' '{printf "%-30s%-s\n",$1,$2}'
rm -f on$$.sql sel$$.sql
select 'string|',string $where(是个变量,表示某具体字符串);这个sql语句将显示某条件下a表的string列名,以及列中的数据。开始那个sql语句可以得到下面这个sql语句。