SQL*Plus使用技巧

sqlplus连接

sqlplus / as sysdba
sqlplus test/test123@db1:1521/orclpdb

set设置sqlplus运行环境变量

set system_variable value

#常用变量
set arraysize 20	 -- 默认20,有效值1-5000,一次从数据库获取的行数
set	autocommit off	 -- 自动提交,默认off
set heading off      -- 不显示每行的列名
set pages 0          -- 不进行分页显示
set feed off         -- 不显示sql语句的运行结果(selected N rows)
set echo off         -- 是否显示脚本中正在执行的SQL语句 on 显示 off 不显示
set serveroutput on  -- 使用dbms_output.put_line时,是否在屏幕上显示信息 on 显示 off 不显示
set feedback off     -- 是否显示当前sql语句查询或修改的行数 off 不显示查询或修改的行数    
set trimspool on     -- 是否去除重定向(spool)输出每行的拖尾空格,on 去除 off 不去除
set line 10000       -- 设置一行显示的字符总数
set verify off       -- 是否显示替代变量被替代前后的语句 on 显示 off 不显示
set head off         -- 是否显示列标题 on 显示 off 不显示

一些常用的变量示例

pagesize

pagesize变量用来设置从顶部标题至页结束之间的行数,默认的value行数为14

一页的行数包括两个标题之间数据行、上面一个列标题、分隔线和空行。

查看当前sqlplus一页有多少行
SQL> show pagesize
pagesize 14

修改pagesize

--默认的14行
SQL> select OBJECT_NAME from t1 where rownum<20;

OBJECT_NAME
--------------------------------------------------------------------------------
TS$
ICOL$
C_FILE#_BLOCK#
I_OBJ2
USER$
I_TAB1
I_OBJ5
CDEF$
I_IND1
I_OBJ#
C_TS#

OBJECT_NAME
--------------------------------------------------------------------------------
。。。。。

--修改为5
SQL> set pagesize 5
SQL> select OBJECT_NAME from t1 where rownum<20;

OBJECT_NAME
--------------------------------------------------------------------------------
TS$
ICOL$

OBJECT_NAME
--------------------------------------------------------------------------------

newpage

newpage变量用来设置一页中空行的数量

linesize

一行所显示的总字符数,默认80

linesize值小于数据行宽度时,行就会折叠,示例:

SQL> show linesize
linesize 80
SQL> select username,account_status from dba_users where rownum<5;

USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
SYS
OPEN

AUDSYS
LOCKED

SYSTEM
OPEN


USERNAME
--------------------------------------------------------------------------------
ACCOUNT_STATUS
--------------------------------
SYSBACKUP
LOCKED


--调整列宽和linesize值
SQL> col username for a50;
SQL> set linesize 200;
SQL> select username,account_status from dba_users where rownum<5;

USERNAME                                           ACCOUNT_STATUS
-------------------------------------------------- --------------------------------
SYS                                                OPEN
AUDSYS                                             LOCKED
SYSTEM                                             OPEN
SYSBACKUP                                          LOCKED

pause

设置sqlplus结果是否滚动显示

set pause value

value的三个值:
off:默认值,一次性输出完毕
on:没输出一页都暂停,enter键继续
text:提示文本,在设置on之后,在设置text值,如set pause aaa,则每次暂停时,先显示这段文本;设置为off时,无效

numformat

numformat用来显示数值的默认格式,该格式是数值格式

set numformat format
掩码 说明 例子
9 查询结果中数字替换格式中的掩码 999
0 格式中的掩码屏蔽掉查询结果中的数字 999.00
$ 在查询结果的数字前添加$符号 $999
S 为数字显示符号类型,通常由于显示查询结果中的正负数字 S999
, 放置逗号,便于位数统计 99,999

sqlplus中默认10个字符宽度和常规格式来显示数字

SQL> set numformat $999,999,999,00
SQL> select bytes from dba_free_space;

           BYTES
----------------
      $83,886,08
     $273,940,48
         $655,36
      $26,869,76
      $41,943,04
      $10,485,76
      $43,253,76
         $655,36
         $655,36
       $1,966,08
     $233,963,52
......

long

LONG (长类型显示字节数)
在缺省的 SQL> 状态下,SQL > 缓冲区用于显示 LONG 的字节数只有 80 个字符。如果我们需要查询的列中含有 LONG 类型的字段的话,就需要将 LONG 缓冲区设置大些。
SET LONG{80|integer}
Integer 是 显示或拷贝 long 值的最大宽度, n=1->32767(但必须小于 Maxdata 值)
SQL>show Maxdata (最大行宽)
SQL>set long 999

常用命令

help

使用help来查看帮助

语法

help|? [topic]
SQL> helpSQL> ?

例子:
SQL> help start

[topic]选项帮助
SQL> help index 或者SQL> help ind

host

使用操作系统命令

SQL> help host

 HOST
 ----

 Executes an operating system command without leaving SQL*Plus.
 Enter HOST without command to display an operating system prompt.
 You can then enter multiple operating system commands.

 HO[ST] [command]

SQL> host pwd
/home/oracle

describe

可以查看对象的结构

desc object_name

describe不仅可以查询表、视图的结构,而且还可以查询过程、函数和程序包等PL/SQL对象的规范

spool

spool命令可以把查询的结果输出到指定的文件中,可以保存查询结果并方便打印。

SQL> help spool

 SPOOL
 -----

 Stores query results in a file, or optionally sends the file to a printer.

 SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

file_name:指定文件名
CRE[ATE]:创建一个新的脱机文件(默认)
REP[LACE]:替代已经存在的脱机文件
APP[END]:把脱机内容附加到一个已经存在的脱机文件中
OFF | OUT:关闭spool输出

例子:

--保存查询内容
SQL> spool D:\tmp\tmp.txt
Started spooling to D:\tmp\tmp.txt

SQL> select username,user_id from all_users;
......

SQL> spool off
Stopped spooling to D:\tmp\tmp.txt

--从spool开始到spool off结束之间的所有内容都被写入文件中。

只有使用spool off或者spool out关闭输出,才会在输出文件中看到内容

其他命令

define

定义一个用户变量并且可以分配给它一个char值

SQL> help define

 DEFINE
 ------

 Specifies a substitution variable and assigns a CHAR value to it, or
 lists the value and variable type of a single variable or all variables.

 DEF[INE] [variable] | [variable = text]

variable:定义的变量名
text:变量的char值

例子:

--使用define命令定义vtest变量,并分配给它一个char值test123
SQL> define vtest='test123';
SQL> define vtest
DEFINE VTEST           = "test123" (CHAR)

show

SHO[W] option,显示SQL/PLUS系统变量和环境变量的值

SQL> help show

 SHOW
 ----

 Shows the value of a SQL*Plus system variable, or the current
 SQL*Plus environment. SHOW SGA requires a DBA privileged login.

 SHO[W] option

 where option represents one of the following terms or clauses:
     system_variable
     ALL
     BTI[TLE]
     CON_ID
     CON_NAME
     EDITION
     ERR[ORS] [{ANALYTIC VIEW | ATTRIBUTE DIMENSION | HIERARCHY | FUNCTION
        | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE
        | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
     LNO
     PARAMETERS [parameter_name]
     PDBS
     PNO
     RECYC[LEBIN] [original_name]
     REL[EASE]
     REPF[OOTER]
     REPH[EADER]
     SGA
     SPOO[L]
     SPPARAMETERS [parameter_name]
     SQLCODE
     TTI[TLE]
     USER

例子:

--显示当前Container的名称
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

--显示当前用户
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"

--显示系统变量
SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

edit

edit用来编辑SQL缓冲区或指定磁盘文件中的SQL语句或者PL/SQL块

SQL语句在PL/SQL块执行完毕之后,可以将其存储在sql缓冲区中,用户可以从中重新调用、编辑或运行最近输入的SQL语句.若要编辑SQL缓冲区中的最近一条SQL语句或PL/SQL块,既可以在SQLPLUS中直接编辑,也可以使用edit命令在记事本中编辑

SQL> help edit

 EDIT
 ----

 Invokes an operating system text editor on the contents of the
 specified file or on the contents of the SQL buffer. The buffer
 has no command history list and does not record SQL*Plus commands.

 ED[IT] [file_name[.ext]]

如果edit不带文件名,则默认编辑SQL缓冲区中最近一条SQL语句或PL/SQL块

可以运行“/”来执行SQL缓冲区中最近一条SQL

save命令

save命令将SQL缓冲区中最近一条SQL语句或者PL/SQL块保存到一个文件中

SQL> help save

 SAVE
 ----

 Saves the contents of the SQL buffer in a script. The
 buffer has no command history list and does not record SQL*Plus commands.

 SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]

file_name如果不指定,则默认在oracle安装的主目录中;如果不保存为指定的的扩展名,则默认扩展名为SQL

clear

清空相关信息

SQL> help clear

 CLEAR
 -----

 Resets or erases the current value or setting for the specified option.

 CL[EAR] option ...

 where option represents one of the following clauses:
     BRE[AKS]
     BUFF[ER]
     COL[UMNS]
     COMP[UTES]
     SCR[EEN]
     SQL
     TIMI[NG]

例子

--清空当前会话的sql缓冲区
SQL> cl buffer
buffer cleared
SQL> /
SP2-0103: Nothing in SQL buffer to run.

--清屏,类似bash的ctrl+l
SQL> cl scr

get

get命令将一个SQL脚本文件的内容放进SQL缓冲区

SQL> help get

 GET
 ---

 Loads a SQL statement or PL/SQL block from a script into the SQL buffer.
 The buffer has no command history list and does not record SQL*Plus commands.

 GET [FILE] file_name[.ext] [LIST | NOLIST]

file_name:提供的文件名,同样若省略扩展名,则默认为SQL
LIST:将文件的内容加载到缓冲区时显示内容
NOLIST:将文件的内容加载到缓冲区时不显示内容

例子:

--加载到缓冲区,使用/运行
SQL> get D:\tmp\tmp.txt
--某些工具get之后进入编辑模式

--再次运行
SQL> /

start和@、@@

start和@都可以用来执行一个SQL脚本文件

语法:

--start语法
SQL> help start
 START
 -----

 Runs the SQL*Plus statements in the specified script. The script can be
 called from the local file system or a web server.

 STA[RT] {url|file_name[.ext]} [arg ...]

 where url supports HTTP and FTP protocols in the form:

    http://host.domain/script.sql


--@和@@
SQL> help @

 @ ("at" sign)
 -------------
 Runs the SQL*Plus statements in the specified script. The script can be
 called from the local file system or a web server.

 @ {url|file_name[.ext]} [arg ...]

 where url supports HTTP and FTP protocols in the form:

    http://host.domain/script.sql


 @@ (double "at" sign)
 ---------------------

 Runs the specified script. This command is almost identical to
 the @ command. It is useful for running nested scripts because it
 has the additional functionality of looking for the nested script
 in the same url or path as the calling script.

 @@ {url|file_name[.ext]} [arg ...]

例如:

--/tmp/tmp.txt中包含得是select语句
SQL> start /tmp/tmp.txt

--同样
SQL> @ /tmp/tmp.txt
SQL> @@ /tmp/tmp.txt

区别在于@@用于嵌套,可以调用和当前脚本同路径下SQL脚本文件

格式化查询结果

column

colimn可以用于实现格式化查询结果、设置列宽、重设标题等

语法:

SQL> help col

 COLUMN
 ------

 Specifies display attributes for a given column, such as:
     - text for the column heading
     - alignment for the column heading
     - format for NUMBER data
     - wrapping of column data
 Also lists the current display attributes for a single column
 or all columns.

 COL[UMN] [{column | expr} [option ...] ]

 where option represents one of the following clauses:
     ALI[AS] alias
     CLE[AR]
     ENTMAP {ON|OFF}
     FOLD_A[FTER]
     FOLD_B[EFORE]
     FOR[MAT] format
     HEA[DING] text
     JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
     LIKE {expr | alias}
     NEWL[INE]
     NEW_V[ALUE] variable
     NOPRI[NT] | PRI[NT]
     NUL[L] text
     OLD_V[ALUE] variable
     ON|OFF
     WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

option含义

选项 含义
CLAER 清除指定列所设置的显示属性,恢复默认属性
FORMAT 格式化指定的列
HEADING 定义列标题
JUSTIFY 调整列标题的对齐方式,默认数值类型的列为右对齐,其他为左对齐
NULL 指定一个字符串,如果列值为null,则由该字符串代替
PRINT/NOPRINT 显示列标题或隐藏,默认PRINT
ON/OFF 控制定义的显示属性的状态,OFF表示定义的所有显示属性都无效,默认ON
WRAPPED 当字符串长度超过显示宽度时,将字符串超出的部分折叠到下一行
WORD_WARPPED 表示从一个完整的字符出折叠
TRUNCATED 截断字符串尾部

如果column后面不跟选项,则默认显示SQLPLUS中所有的当前属性

format值

格式化指定的列

SQL> col owner format a50;
SQL> select owner,object_id from all_objects where rownum<5;
format可以缩写for

格式化字符
col 列名 format $999,999.00

heading值

用于定义列标题

SQL> col owner heading 属主;
SQL> col object_id heading 对象ID;
SQL> select owner,object_id from all_objects where rownum<5;

属主                                                   对象ID
-------------------------------------------------- ----------
SYS                                                        16
SYS                                                        20
SYS                                                         8
SYS                                                        37

null值

在Null值后面指定一个字符串,如果列值为Null值,则用该字符串代替

SQL> select owner,SUBOBJECT_NAME from all_objects where SUBOBJECT_NAME is null and rownum<=1;

OWNER                                              SUBOBJECT_NAME
-------------------------------------------------- --------------------------------------------------
SYS


SQL> col SUBOBJECT_NAME null "空值"
SQL> select owner,SUBOBJECT_NAME from all_objects where SUBOBJECT_NAME is null and rownum<=1;

OWNER                                              SUBOBJECT_NAME
-------------------------------------------------- --------------------------------------------------
SYS                                                空值

on/off值

on定义的属性剩生效,off关闭,使用默认

col 列名 off

WARPPED/WORD_WARPPED值

都用于折行,WARPPED按指定长度折行,WORD_WARPPED值按照完整字符串折行

SQL> select col1 from t1;

COL1
----------
HOW ARE YOU?

SQL> col col1 for a5
SQL> col col1 wrapped
SQL> select col1 from t1;

COL1
-----
HOW A
RE YO
U?


#WORD_WARPPED折行
SQL> col col1 word_wrapped;
SQL> select col1 from t1;

COL1
-----
HOW
ARE
YOU?

TTITLE和BTITLE

TTITLE和BTITLE分别用来打印顶部和底部标题

语法:

SQL> help TTI

 TTITLE
 ------

 Places and formats a title at the top of each report page.
 Enter TTITLE with no clause to list its current definition.

 The old form of TTITLE is used if only a single word or
 a string in quotes follows the TTITLE command.

 TTI[TLE] [printspec [text|variable] ...] | [OFF|ON]

 where printspec represents one or more of the following clauses:

     COL n          LE[FT]        BOLD
     S[KIP] [n]     CE[NTER]      FORMAT text
     TAB n          R[IGHT]

printspec:头部标题的修饰性选项,一些选项值含义
	col指定在当前行的第几列打印头部标题
	skip跳到从下一行开始的第几行,默认1
	left在当前行中左对齐打印
	center当前行居中打印
	right当前行右对齐打印
	bold黑体打印
text:设置输出结果的头标题(报表头文字)
variable:用于在头标题中输出相应变量值
on:允许打印头标题
off:禁止打印头标题

BTITLE语法与之相似,help查看

例子

SQL> select * from t1;

COL1
------------------------------
THANK YOU


SQL> ttitle left "标题头测试"
SQL> btitle left '打印人:XXX'
SQL> select * from t1;

标题头测试
COL1
------------------------------
THANK YOU




打印人:XXX


#可以定义define定义一个变量,然后ttitle left 变量名
posted @   EverEternity  阅读(439)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版
点击右上角即可分享
微信分享提示