学习笔记：oracle学习二：oracle11g数据库sql*plus命令之数据库交互、设置运行环境

---

---

1、SQL*PLUS与数据库的交互

sql plus是与Oracle进行交互的客户端工具，基于C/S两层结构的客户端工具，包括客户层（即命令行接口）和服务器层（即数据库实例）。主要用来进行数据查询和数据处理。利用sql plus可将sql和oracle专有的pl/sql结合起来进行数据查询和处理。

sql*plus具有以下功能

• 定义变量，编写sql语句
• 插入、修改、删除、查询，以及执行命令和pl/sql语句，比如：执行show parameter
• 格式化查询结构、运算处理、保存、打印机输出等
• 显示任何一个表的字段定义，并实现与用户进行互动
• 完成数据库的几乎多有管理工作，比如维护表空间和数据表
• 运行存储在数据库中的子程序或者包
• 以sysdba身份登录数据库实例，可以实现启动/停止数据库实例

2、设置sql*plus运行环境

set命令可以为sqlplus交互建立一个特殊的环境。例如，设置屏幕每一行能够显示的最多字符数、设置每页打印的行数、设置列的宽度

2.1 set命令简介

用法： set system——variable value

• system-variable 变量
• value 值

set命令常用变量名、可选值及其说明

变量名	变量可选值	说明
ARRAY[SIZE]	20(默认值)、N	设置一批的行数，即以此从数据库获取的行数，有效值为1-5000
AUTO[COMMIT]	OFF(默认值) ON IMM	控制Oracle对数据库的修改的提交，设置为ON时，执行每个sql命令或sql块后自动提交，OFF手工提交修改，IMME功能同ON
BLO[CKTERMINATOR]	.(默认值)、C	设置非字母数字字符，用于结束sql块。要执行块时，必须发出RUN命令或者/命令
CMDS[EP]	; C OFF(命令) ON	设置非字母数字字符，用于分隔在一行中输入的多个sql命令，ON或OFF控制在一行中是否能输入多个命令。ON时将自动将命令分隔符设为；，其中C表示所致字符
ECHO	OFF ON	控制start命令是否列出命令文件中的每个命令，如设置为ON，则领出命令；若设置为OFF，则制止列清单
FLU[SH]	OFF ON(默认值)	控制输出至用户的显示设备。设置为OFF时，运行操作系统做缓冲区输出；设置为ON时，不容许缓冲
HEA[DING]	OFF ON（默认值）	控制报表中列标题的打印。设置为ON时，在报表中打印列标题；设置为OFF时，禁止打印列标题
LIN[ESIZE]	80(默认值) N	设置一行中显示的最多字符总数，范围1-最大值
NEWP[AGE]	1(默认值) N	设置一页空行中的数量，默认值为1
NULL	TEXT	设置表示空值null的文本，如果null没有文本，则显示空格（空格）
PAGES[IZE]	14(默认值) N	设置从顶部标题至页结束之间的行数
PAU[SE]	OFF ON TEXT	设置sqlplus结果是否滚动显示。ON时表示数据结果的每一页都暂停，用户按ENTER后继续显示。设置为ON后，再设置字符串，则暂停后都显示该字符串
RECSEP	WRAPPED EA[CH] OFF	指示显示或者打印记录分行符的条件。一个记录分行分行符是由RECSEPCHAR指定的字符组成的单行，空格为默认字符
SERVEROUT[PUT]	OFF ON[SIZE N]	控制在sqlplus中的存储过程中的存储过程是否显示输出。OFF禁止，ON显示输出，size设置缓冲区输出的字节数，默认值为2000，值不能大于100万
SHOW[MODE]	OFF(默认值) ON	控制sqlplus在执行set命令时是否列出其新老值new或old的设置
SPA[CE]	1(默认值) N	设置输出列之间空格的数目，最大值为10
SQLCO[NTINUE]	>; 文本	在一附加行上继续某个sqlplus命令时，以该设置的字符序列进行提示
SQLN[UMBER]	OFF ON	为SQL命令和PLSQL块的第二行和后继行设置提示。ON提示行号，OFF提示为SQLPROMPT值
TI[ME]	OFF(默认值) ON	控制当前日期的显示。ON则在每条命令前显示当前时间。OFF禁止时间显示
TIM[ING]	OFF(默认值) ON	控制时间统计的显示。ON显示每一个运行的sql命令或者块的时间统计。OFF禁止每个命令的时间统计
UND[ERLINE]	-(默认值) C OFF ON(默认值)	设置用在sqlplus报表中下划线列标题的字符。ON或者OFF将下划线设置为开或者关状态
VER(IFY)	OFF ON(默认值)	控制sqlplus用值替换前后是否列出命令的文本。ON显示、OFF禁止列清单
WRA[P]	OFF ON(默认值)	控制sqlplus是否截断数据项的显示。若为OFF，则截断数据项。若为ON，则容许数据项下一行继续显示

注意： SET命令设置的环境变量是临时的，不是永久的。当用户退出sqlplus后，用户设置的环境参数会丢失

例子：使用set time on设置提示当前系统时间

set time on

2.2 使用set命令设置运行环境

常用变量选项详解

2.2.1 pagesize变量

用于设置从顶部到页结束之间的行数

语法格式：

set pagesize value

value值默认为14，根据实际情况需要，可以修改value值

可以使用show pagesize命令查看当前pagesize参数值

例：使用set pagesize命令修改一页的行数为20

set pagesize 20;

select user_id,username,account_status from dba_users;

说明：一业内的数据行包括两个列标题之间的数据行、上面的一个列标题、分割线和空行。

2.2.2 NEWPAGE变量

该变量用来设置一页中空行的数量，其语法格式如下：

set newpage value

value默认值为1，根据实际情况修改。

例：显示当前newpage值，并修改

show newpage;

set newpage 3;

select user_id,username,account_status from dba_users;

2.2.3 linesize变量

用来设置在sqlplus环境中一行所显示的最多字符数，语法格式：

set linesize value

value默认值为80，根据实际情况修改。如果数据行的宽度大于linesize变量值，输出字符时，数据就会发生折行显示的情况，

例：是哦那个show line 显示linesize值，并用set linesize 修改

show line；

set linesize 100；

2.2.4 PAUSE变量

该变量用来设置sqlplus输出结果是否滚动显示，语法格式如下：

set pause value

value变量值有以下3中情况：

• OFF：默认值，返回结果一次性输出完毕，中间的每一页不会暂停；
• ON：表述输出结果的每一页都暂停，用户按enter后继续显示；
• TEXT：设置ON后，再设置TEXT，则每次暂停都会显示文本内容；

例：使用set pause命令设置显示结果暂停，并在暂停后显示“按enter键继续”字符

set pause on;

set pause '按<enter>健继续';

select user_id,username,account_status from dba_users;

2.2.5 NUMFORMAT变量

该变量用来设置显示数值的格式

set numformat format

format为数值掩码，常用掩码说明如下：

掩码	说明	举例
9	查询结果中数字替换格式的掩码	999
0	格式中的掩码皮笔调查询结果中的数字	999.00
$	在查询结果中的数字前添加美元前缀	$999
S	为数字显示符号类型，通常用于查询结果中的正负数字	S999
，	在字符","位置上放置逗号	999,99

select ename,job,sal from scott.emp;

例子 使用$999，999，999.00格式显示scott.emp表中的雇员工资，代码如下：

set numformat $999,999,999.0;

select ename,job,sal from scott.emp;