shell - 用sh列表显示oracle数据库单条查询结果

用sh列表显示oracle数据库单条查询结果
2004-04-23 15:18 pm
来自：Linux文档
现载：Www.8s8s.coM
地址：无名

经常在UNIX下使用oracle数据库,用的是sqlplus,大体已经习惯了.但是有一点受不了,就是用select查询一个大表(字段比较多的表)时,虽然查的结果只有一行,但要是set head on,往往要显示几屏,每一屏都要显示一遍所有字段,根本看不清整个表的内容,如果set head off,又不知道每个值对应的是哪个字段,一个一个数太麻烦了.总想,要是这条数据纵向列表显示,左边列名,右边是值,看着该多方便啊,于是花了点写了一个shell程序,程序名就叫select.
#!/bin/sh

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 ",$1,$2}'
rm -f on$$.sql sel$$.sql

哈哈,使用的时候跟在sqlplus状态下一样的语句,瞧,这是我在sh状态下查询一个有26个字段的表,要是在sqlplus状态下用set head on状态,得显示个几十屏的内容.当我在UNIX sh状态下用这个sql语句时,旁边的同事都呆住了,没搞清怎么回事.(为隐密起见,字段名用了X和乱符表示)
$ select * from testtable;
ColumnName value
----------- ------------
XXXX_ID 2
XXXXON_ID 3
XXXXXXXX_SUBS 2
XXXXTE_TIME 20020320173232
XXXXTOR 0
XXXXTOR_GROUP 0
XXXXNAME
XXXXXXXX_BIRTHDAY_TIME
XXXXXXXX_MODIFY_TIME 20020320173232
XXXXXXXX_OLDNO
XXXXFIER 0
XXXXFIER_NAME Admin
XXXXFIER_LOGIN super
XXXXOPERTYPE 0
XXXXXXXX_CHARGE_NO
XXXXXXXX_BANK
XXXXXXXX_BANK_NO
XXXXXXXX_NET_USE 0
XXXXXXXX_ATTRIBUTE 1
XXXXUP1 0
XXXXUP2 Net
XXXXXXXX_BUSINESS 99
XXXXXXXX_SEX 0
XXXXXXXX_CREDIT_THRESHOLD 0
XXXXXXXX_CREDIT_FLOOR 0
XXXXXXXX_ID 11
XXXXXXXX_NAME USER
XXXXXXXX_PASSWORD xxxc6xxx2ab461b4
XXXXXXXX_IDENTITY
XXXXXXXX_IDTYPE 0
XXXXXXXX_VOCATION
XXXXXXXX_CITY
XXXXXXXX_PROVINCE 13900000005
XXXXXXXX_COUNTRY
XXXXXXXX_ZIP_CODE
XXXXXXXX_PHONE 01062501658
XXXXXXXX_FAX
XXXXXXXX_EMAIL [email protected]
XXXXXXXX_STATUS 0
XXXXXXXX_STATUS_TIME 20020320173231
XXXXXXXX_STATUS_COMMENT
XXXXXXXX_XXXOUNT_CXXLE 14
XXXXXXXX_PAY_TYPE 1
XXXXXXXX_NO AN20000013
XXXXXXXX_ACCOUNT_NO 11
XXXXXXXX_XXXXING_CYCLE
XXXXXXXX_XXXXING_AMOUNT
XXXXXXXX_XXXXING_EAMOUNT
XXXXXXXX_PAY_TIME
XXXXXXXX_OWE_XXXXE 0
XXXXXXXX_PRE_XXXXK_DATE
XXXXENCY_TYPE 0
XXXXXXXX_PARENT 0
XXXXXXXX_BANKNAME
XXXXXXXX_BANKNUM
XXXXXXXX_LINKMAN 99098808
XXXXICE_DELIVER_METHOD 1
XXXXXXXX_TYPE 0
XXXXXXXX_NUMBER
CDT_XXXX_HOLDER
CDT_XXXX_EXPIRY 20020320000000
XXXXXNT_TYPE 0
XXXXXXXX_GRADE 5
XXXXRTISE_FLAG 0
XXXXODE_ID 0
XXXXS_ID 0
XXXXLLER_ID 0
XXXX_NAME Admin
XXXX_LOGIN_NAME super
ACYSEW_DATE 20041214000000

又是精华之作。