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> help
或
SQL> ?
例子:
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 变量名
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版