首页 > 技术文章 > 学习笔记:oracle学习二:oracle11g数据库sql*plus命令之数据库交互、设置运行环境

yj411511 2019-11-20 07:42 原文


本系列是作为学习笔记,用于记录学习过程,加深学习印象,以及自己重新回顾学习内容只用,参考书籍为《oracle 11g从入门到精通(第二版)》,如果需要深入学习,请购买原书籍,谢谢!

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

sqlplus是与Oracle进行交互的客户端工具,基于C/S两层结构的客户端工具,包括客户层(即命令行接口)和服务器层(即数据库实例)。主要用来进行数据查询和数据处理。利用sqlplus可将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;

推荐阅读