Col
COL col_name CLEAR
clear the column format and set it to the default setting
COL col_name FOR[MAT] format
format column, tipical usage have:
COL col_name FORMAT A99 --> VARCHAR2
COL col_name FORMAT $9999.99 --> $2345.23
COL col_name FORMAT 0999.99 --> 0023.23
COL col_name FORMAT 9999MI --> 9999-
COL col_name FORMAT 9,999PR --> <-9,999->
COL col_name DATE
COL col_name HEA[DING] text
define one heading for one column. refer to SET HEADING OFF.
COL col_name JUS[TIFY] {L[EFT]|C[ENTER]|R[IGHT]}
By default Numbers are right justified, text is left justified.
COL col_name NEW_VALUE new_var
Specify a variable to hold a column value.
15:26:50 lab@ORCL>column tname new_value v_tname
15:26:54 lab@ORCL>select tname from tab where rownum <2;
已选择 1 行。
已用时间: 00: 00: 00.01
15:26:57 lab@ORCL>define v_tname
DEFINE V_TNAME = "DEPT" (CHAR)
COL col_name NOPRI[NT]|PRI[NT]
Display the column. But when there is only column, this option has no effect.
COL col_name ON|OFF
Enable or disable column format attributes
COL col_name WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
15:07:02 lab@ORCL>col tn format a10 wrapped
15:08:48 lab@ORCL>select 'sdfljsdlfjsldfjsdlkfjsldfj' tn from dual;
TN
----------
sdfljsdlfj
sldfjsdlkf
jsldfj
15:10:22 lab@ORCL>col tn format a10 word_wrapped
15:10:41 lab@ORCL>select 'what are you doing here' tn from dual;
TN
----------
what are
you doing
here
15:10:44 lab@ORCL>col tn format a10 truncated
15:11:20 lab@ORCL>select 'what are you doing here' tn from dual;
TN
----------
what are y
Accept
User input, example:
16:26:51 lab@ORCL>select * from &tname;
原值 1: select * from &tname
新值 1: select * from tab
TABTYPE CLUSTERID
------- ----------
TABLE
TABLE
TABLE
TABLE
VIEW
VIEW
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
已选择13行。
BREAK
BREAK ON {column|expr|ROW|REPORT} action
Specify where and how formatting will change. It is always used with compute. tipical usage:
break
break on col1
break on col_1 on col_2
break on column skip n
Compute
you also use avg,count,max,min,number,std,sum,var function in this command set.
16:38:32 lab@ORCL>compute sum of sal on deptno
16:38:58 lab@ORCL>break on deptno skip 1
16:39:00 lab@ORCL>select deptno,ename,sal from emp where deptno in(10,20) order by 1,2;
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
KING 5000
MILLER 1300
********** ----------
sum 8750
20 ADAMS 1100
CLARK 2550
FORD 3000
JONES 2975
KING 5100
MILLER 1400
SCOTT 3000
SMITH 800
********** ----------
sum 19925
已选择11行。
已用时间: 00: 00: 00.04