Oracle Sqlplus 设置数据显示的行数、行宽等
文章目录
在使用 Oracle Sqlplus 对数据库进行操作时,通过设置数据显示的行数、行宽以及某些列的显示宽度,可以使数据的显示更加美观。
一、设置页面显示的行数(pagesize)
1、查看 pagesize 的当前值
SQL> show pagesize
pagesize 100
2、设置页面显示的行数并查看效果
SQL> set pagesize 8;
SQL> select empno,ename,job,sal from emp;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
14 rows selected.
SQL> set pagesize 80;
SQL> select empno,ename,job,sal from emp;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
14 rows selected.
二、设置行宽(linesize)
1、查看 linesize 的当前值
SQL> show linesize;
linesize 50
2、设置行宽并查看效果
SQL> set linesize 80;
SQL> select * from emp where sal>4000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10
--由于行宽太小,导致一行数据分为两行显示。
SQL> set linesize 150;
SQL> select * from emp where sal>2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
6 rows selected.
三、设置某一列显示的宽度
命令格式如下:
col 列名 format a列宽(数值);
--注意:列宽前面加字母a
举例:
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> col tname format a20;
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
-------------------- ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL> col tname format a10;
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
---------- ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE