用sh列表显示oracle数据库单条查询结果(转)

用sh列表显示oracle数据库单条查询结果(转)[@more@]

经常在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 user@263.net

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 2004121400000

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8225414/viewspace-944856/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8225414/viewspace-944856/