在sqlplus执行sql时,经常碰到显示结果,跨行输出,致使可读性很差,看起来特别乱。可通过set linesize及column命令进行调整。
set linesize 设置sqlplus输出的最大行宽
set linesize 设置sqlplus输出的最大行宽
column 这个命令很好用,可使用此命修改显示字段的长度或名称,如:
column c1 format a20 --将列c1(字符型)显示最大宽度调整为20个字符
column c1 format 9999999 --将列c1(num型)显示最大宽度调整为7个字符
column c1 heading c2 --将c1的列名输出为c2
示例如下:
点击(此处)折叠或打开
- SQL> SELECT id,
- 2 c123,
- 3 substr(c123, 1, instr(c123 || ',', ',', 1, 1) - 1) c1,
- 4 substr(c123,
- 5 instr(c123 || ',', ',', 1, 1) + 1,
- 6 instr(c123 || ',', ',', 1, 2) - instr(c123 || ',', ',', 1, 1) - 1) c2,
- 7 substr(c123,
- 8 instr(c123 || ',', ',', 1, 2) + 1,
- 9 instr(c123 || ',', ',', 1, 3) - instr(c123 || ',', ',', 1, 2) - 1) c3
- 10 FROM t_str_col
- 11 ORDER BY 1;
-
- ID C123
- ---------- --------------------------------
-
- C1
- ----------------------------------------------------------------
-
- C2
- ----------------------------------------------------------------
-
- C3
- ----------------------------------------------------------------
-
- 1 v11,v21,v31
- v11
- v21
- v31
-
-
-
- 已选择1行。
-
- SQL>
- SQL> show linesize
- linesize 80
- SQL> set linesize 200
- SQL> /
-
- ID C123 C1 C2
- ---------- -------------------------------- --------------------------------------------------------
-
- C3
- ----------------------------------------------------------------
-
- 1 v11,v21,v31 v11 v21
- v31
-
-
- 已选择1行。
-
- SQL> column c123 format a30
- SQL> column c1 format a10
- SQL> column c2 format a10
- SQL> column c3 format a10
- SQL> SELECT id,
- 2 c123,
- 3 substr(c123, 1, instr(c123 || ',', ',', 1, 1) - 1) c1,
- 4 substr(c123,
- 5 instr(c123 || ',', ',', 1, 1) + 1,
- 6 instr(c123 || ',', ',', 1, 2) - instr(c123 || ',', ',', 1, 1) - 1) c2,
- 7 substr(c123,
- 8 instr(c123 || ',', ',', 1, 2) + 1,
- 9 instr(c123 || ',', ',', 1, 3) - instr(c123 || ',', ',', 1, 2) - 1) c3
- 10 FROM t_str_col
- 11 ORDER BY 1;
-
- ID C123 C1 C2 C3
- ---------- ------------------------------ ---------- ---------- ----------
-
- 1 v11,v21,v31 v11 v21 v31
-
- 已选择1行。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21251711/viewspace-1067550/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21251711/viewspace-1067550/