sqlplus 使用之一 初步了解

os: centos 7.4
db: oracle 18c(18.3)

虽然 sqlplus 不太好用，但经常是必须用。

使用 sqlplus 登录数据库

$ sqlplus / as sysdba;

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Nov 23 11:06:57 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> 

查看帮助

SQL> help

 HELP
 ----

 Accesses this command line help system. Enter HELP INDEX or ? INDEX
 for a list of topics.

 You can view SQL*Plus resources at
     http://www.oracle.com/technology/documentation/

 ******************************************************************************
 **  Top 12.2 features:                                                      **
 **    - Fast retrieval of data as CSV for use in applications like          **
 **      SQL*Loader. Use SQLPLUS -M "CSV ON" or SET MARKUP CSV ON            **
 **    - Improved defaults and optimizations for reports. Use SQLPLUS -F     **
 **    - Command recall. Use SET HISTORY ON and HISTORY to list previous     **
 **      commands.                                                           **
 ******************************************************************************

 HELP|? [topic]


SQL> help index

Enter Help [topic] for help.

 @             COPY         PASSWORD                 SHOW
 @@            DEFINE       PAUSE                    SHUTDOWN
 /             DEL          PRINT                    SPOOL
 ACCEPT        DESCRIBE     PROMPT                   SQLPLUS
 APPEND        DISCONNECT   QUIT                     START
 ARCHIVE LOG   EDIT         RECOVER                  STARTUP
 ATTRIBUTE     EXECUTE      REMARK                   STORE
 BREAK         EXIT         REPFOOTER                TIMING
 BTITLE        GET          REPHEADER                TTITLE
 CHANGE        HELP         RESERVED WORDS (SQL)     UNDEFINE
 CLEAR         HISTORY      RESERVED WORDS (PL/SQL)  VARIABLE
 COLUMN        HOST         RUN                      WHENEVER OSERROR
 COMPUTE       INPUT        SAVE                     WHENEVER SQLERROR
 CONNECT       LIST         SET                      XQUERY

column 控制列的显示宽度

在sqlplus下查询的输出丑到爆表，怎么简单输出看起来漂亮些。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
	 0

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
    CON_ID
----------


Elapsed: 00:00:00.01
SQL> 
SQL> desc v$version;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 BANNER 					    VARCHAR2(80)
 BANNER_FULL					    VARCHAR2(160)
 BANNER_LEGACY					    VARCHAR2(80)
 CON_ID 					    NUMBER

控制显示宽度是最常用的格式化方式了

SQL> set linesize 200;
SQL> set pagesize 200;

SQL> col BANNER format a40;
col BANNER_FULL format a40;
col BANNER_LEGACY format a40;
col CON_ID format 999999;

SQL> select * from v$version;

BANNER					 BANNER_FULL				  BANNER_LEGACY 			    CON_ID
---------------------------------------- ---------------------------------------- ---------------------------------------- -------
Oracle Database 18c Enterprise Edition R Oracle Database 18c Enterprise Edition R Oracle Database 18c Enterprise Edition R	 0
elease 18.0.0.0.0 - Production		 elease 18.0.0.0.0 - Production 	  elease 18.0.0.0.0 - Production
					 Version 18.3.0.0.0

是不是好很多了。

清除列的设置

SQL> col BANNER clear 
SQL> clear columns

参考：
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqpug/index.html
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqprn/index.html#SQPRN101
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqpqr/index.html#SQPQR101