用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