SQL> select * from student; ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
----------
1
Aaron
78 2
Bill
76 ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
---------- 3
Cindy
89 4
Damon ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
----------
90 5
Ella
73 6 ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
----------
Frado
61 7
Gill
99
ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
----------
8
Hellen
56 9
Ivan
93 ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
---------- 10
Jay
90
10 rows selected.SQL> col id format a10
SQL> col name format a20
SQL> col score format a10
SQL> select * from student; ID NAME SCORE
---------- -------------------- ----------
########## Aaron ##########
########## Bill ##########
########## Cindy ##########
########## Damon ##########
########## Ella ##########
########## Frado ##########
########## Gill ##########
########## Hellen ##########
########## Ivan ##########
########## Jay ##########10 rows selected.SQL> col id format a20
SQL> col name format a20
SQL> col score format a20
SQL> select * from student; ID NAME SCORE
---------- -------------------- ----------
########## Aaron ##########
########## Bill ##########
########## Cindy ##########
########## Damon ##########
########## Ella ##########
########## Frado ##########
########## Gill ##########
########## Hellen ##########
########## Ivan ##########
########## Jay ##########10 rows selected.SQL>
为什么第二次我对列长度格式修改的命令好像不起作用哦
col id format a20
col name format a20
col score format a20
----------
NAME
-------------------------------------------------------------------------------- SCORE
----------
1
Aaron
78 2
Bill
76 ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
---------- 3
Cindy
89 4
Damon ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
----------
90 5
Ella
73 6 ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
----------
Frado
61 7
Gill
99
ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
----------
8
Hellen
56 9
Ivan
93 ID
----------
NAME
-------------------------------------------------------------------------------- SCORE
---------- 10
Jay
90
10 rows selected.SQL> col id format a10
SQL> col name format a20
SQL> col score format a10
SQL> select * from student; ID NAME SCORE
---------- -------------------- ----------
########## Aaron ##########
########## Bill ##########
########## Cindy ##########
########## Damon ##########
########## Ella ##########
########## Frado ##########
########## Gill ##########
########## Hellen ##########
########## Ivan ##########
########## Jay ##########10 rows selected.SQL> col id format a20
SQL> col name format a20
SQL> col score format a20
SQL> select * from student; ID NAME SCORE
---------- -------------------- ----------
########## Aaron ##########
########## Bill ##########
########## Cindy ##########
########## Damon ##########
########## Ella ##########
########## Frado ##########
########## Gill ##########
########## Hellen ##########
########## Ivan ##########
########## Jay ##########10 rows selected.SQL>
为什么第二次我对列长度格式修改的命令好像不起作用哦
col id format a20
col name format a20
col score format a20
但在editor窗口下是生效的。原因不明,待高手告知。
col id format a20
col name format a20
col score format a20
select * from student;
因为col是全局设置,局部设置不起作用的!!!
SQL> desc employees
名稱 空值? 類型
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(100)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)SQL> select employee_id,last_name,salary from employees where salary>15000;EMPLOYEE_ID
-----------
LAST_NAME
--------------------------------------------------------------------------------
SALARY
----------
100
King
24000 101
Kochhar
17000EMPLOYEE_ID
-----------
LAST_NAME
--------------------------------------------------------------------------------
SALARY
---------- 102
De Haan
17000SQL> col last_name format a20
SQL> col employee_id format a10
SQL> col salary format a10
SQL> /EMPLOYEE_ID LAST_NAME SALARY
----------- -------------------- ----------
########## King ##########
########## Kochhar ##########
########## De Haan ##########SQL> col last_name format a30
SQL> col employee_id format a20
SQL> col salary format a20
SQL> select employee_id,last_name,salary from employees where salary>15000;EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------------ ----------
########## King ##########
########## Kochhar ##########
########## De Haan ##########SQL> col salary clear
SQL> /EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------------ ----------
########## King 24000
########## Kochhar 17000
########## De Haan 17000SQL> col salary format a20
SQL> /EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------------ ----------
########## King ##########
########## Kochhar ##########
########## De Haan ##########
可以看出col ... format 對number字段不起作用
應該是col ... format a?? 對number字段不起作用.
SQL> col salary format $999,999.00
SQL> /EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------------ ------------
########## King $24,000.00
########## Kochhar $17,000.00
########## De Haan $17,000.00
先set wrap off 一下, 这样查询结果就在一行显示了在设下字段的宽度。 就可以看到了 。。
select score,id,name
from student
where id > 2 and id < 7unionselect score,id,name
from student
where id < 4unionselect score,id,name
from student
where id > 8
order by id desc
SQL> select * from student;
rows will be truncated
ID NAME
---------- --------------------------------------------------------------------- 1 Aaron
2 Bill
3 Cindy
4 Damon
5 Ella
6 Frado
7 Gill
8 Hellen
9 Ivan
10 Jay10 rows selected.SQL> col name format a20
SQL> select * from student; ID NAME SCORE
---------- -------------------- ----------
1 Aaron 78
2 Bill 76
3 Cindy 89
4 Damon 90
5 Ella 73
6 Frado 61
7 Gill 99
8 Hellen 56
9 Ivan 93
10 Jay 9010 rows selected.SQL> col id format a10
SQL> select * from student; ID NAME SCORE
---------- -------------------- ----------
########## Aaron 78
########## Bill 76
########## Cindy 89
########## Damon 90
########## Ella 73
########## Frado 61
########## Gill 99
########## Hellen 56
########## Ivan 93
########## Jay 9010 rows selected.
恩 这样也行 不过col ... format a...好像确实对NUMBER类型没用的
本来好好的 我一设置col id format a10 ID字段的显示结果反而变成############了
你这里是数值型的字段要用col xxxx format 99999999这样的方式。