3.1 SQL*PLUS与数据库的交互
3.2 设置SQL*PLUS的运行环境
3.2.1 set命令概述
set system_variable value
3.2.2 使用set命令设置运行环境
1、pagesize变量
设置页行数
SQL> show pagesize;
pagesize 50
SQL> set pagesize 20;
SQL> show pagesize;
pagesize 20
2、newpage变量
设置一页中空行的数量
SQL> show newpage;
newpage 1
SQL> set newpage 2;
SQL> show newpage;
newpage 2
3、linesize变量
设置一行显示的最多字符总数
SQL> show linesize;
linesize 100
SQL> set linesize 80;
SQL> show linesize;
linesize 80
4、pause变量
设置输出结果是否滚动显示
value变量值有以下三种情况：
off：默认值，一次性输出完毕
on：每一页都暂停
text：设置为on时，可设置text值作为提示信息
SQL> show pause;
PAUSE is OFF
SQL> set pause on;
SQL> set pause 'press <enter> continue';
SQL> show pause;
PAUSE is ON and set to "press <enter> continue"
5、numformat变量
设置显示数值的默认格式
set numformat format
format为数值的掩码，常用掩码及其说明如下表：
| 掩码 | 说明 | 举例 |
| 9 | 显示查询结果 | 999 |
| 0 | 屏蔽查询结果 | 999.00 |
| $ | 数字前添加美元前缀 | $999 |
| S | 显示查询结果中的正负数 | S999 |
| , | 逗号 | 999,99 |
SQL> select ename,job,sal from scott.emp;
press <enter> continue
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300
14 rows selected.
SQL> set numformat $999,999,999.00;
SQL> select ename,job,sal from scott.emp;
press <enter> continue
ENAME JOB SAL
---------- --------- ----------------
SMITH CLERK $800.00
ALLEN SALESMAN $1,600.00
WARD SALESMAN $1,250.00
JONES MANAGER $2,975.00
MARTIN SALESMAN $1,250.00
BLAKE MANAGER $2,850.00
CLARK MANAGER $2,450.00
SCOTT ANALYST $3,000.00
KING PRESIDENT $5,000.00
TURNER SALESMAN $1,500.00
ADAMS CLERK $1,100.00
JAMES CLERK $950.00
FORD ANALYST $3,000.00
MILLER CLERK $1,300.00
14 rows selected.
3.3 常用SQL*Plus命令
3.3.1 help命令
SQL> help startup
STARTUP
-------
Starts an Oracle instance with several options, including mounting,
and opening a database.
STARTUP options | upgrade_options
where options has the following syntax:
[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
[ OPEN [open_options] [dbname] ] |
NOMOUNT ]
where open_options has the following syntax:
READ {ONLY | WRITE [RECOVER]} | RECOVER
and where upgrade_options has the following syntax:
[PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]
SQL> help index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
3.3.2 describe命令
查询数据对象的组成结构
SQL> desc dba_tablespaces;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)
SQL> select empno,ename,
2 #desc scott.emp
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
2 sal from scott.emp where job='SALESMAN';
press <enter> continue
EMPNO ENAME SAL
---------------- ---------- ----------------
$7,499.00 ALLEN $1,600.00
$7,521.00 WARD $1,250.00
$7,654.00 MARTIN $1,250.00
$7,844.00 TURNER $1,500.00
3.3.3 spool命令
把查询结果输出到指定文件中
语法：
spo[ol] [file_name[.ext]] [cre[ate]|rep[lace]|app[end]] [off|out]
关键字含义：
cre:创建新的文件，默认状态
rep：替代已经存在的文件
app：向已经存在的文件添加
off|out：关闭spool输出
SQL> spool /home/oracle/emp.txt
SQL> select empno,ename,job,sal from scott.emp where job='SALESMAN';EMPNO ENAME JOB SAL
---------- ---------- --------- ---------- 7499 ALLEN SALESMAN 1600 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 7844 TURNER SALESMAN 1500SQL> spool off
[oracle@bogon ~]$ cat /home/oracle/emp.txt
SQL> select empno,ename,job,sal from scott.emp where job='SALESMAN';EMPNO ENAME JOB SAL
---------- ---------- --------- ---------- 7499 ALLEN SALESMAN 1600 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 7844 TURNER SALESMAN 1500SQL> spool off
3.3.4 其他常用命令
1、define命令
定义一个用户变量并且可以分配给它一个char值
语法格式：
def[ine] [variable]|[variable=text]
SQL> define vjob='SALESMAN' SQL> define DEFINE _DATE = "30-NOV-13" (CHAR) DEFINE _CONNECT_IDENTIFIER = "stone1" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR) DEFINE _EDITOR = "ed" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000100" (CHAR) DEFINE VJOB = "SALESMAN" (CHAR) 2、show命令
显示SQL*PLUS系统变量或者环境变量的值
语法格式：
sho[w] option
option表示要显示的系统选项，常用的有ALL,PARAMETERS,SGA,SPOOL,USER等
SQL> show parameters db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------db_block_size integer 8192 3、edit命令
编辑SQL缓冲区或者指定磁盘文件中的SQL语句或PL/SQL块
语法格式：
ed[it] [file_name[.ext]]
file_name表示磁盘上的文件，如果不指定file_name，表示标记SQL缓冲区中的最近一条SQL语句或PL/SQL块。
linux下输入q(不保存 )或者wq(保存)退出
4、save命令
将SQL缓冲区的最近一条SQL语句或PL/SQL块保存到一个文件中。
语法格式：
save file_name
默认扩展名是.sql
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL> save /home/oracle/dept.sql
Created file /home/oracle/dept.sql[oracle@bogon ~]$ cat dept.sql
select * from scott.dept/使用clear buffer命令清除SQL缓冲区的内容
5、get命令
把一个SQL脚本文件的内容放进SQL缓冲区
get [file] file_name[.ext] [list|nolist]
file_name：要检索的文件名，如果省略扩展名，默认为.sql
list：指定文件的内容加载到缓冲区时显示文件的内容
nolist：不显示文件的内容
SQL> get /home/oracle/dept.sql
1* select * from scott.deptSQL> /DEPTNO DNAME LOC
---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
6、start和@命令
执行SQL脚本文件
语法格式；
sta[rt] {url|file_name[.ext]} [arg...]
@ {url|file_name[.ext]} [arg...]
url:表示要执行的SQL脚本文件的路径
file_name：表示包含SQL脚本的文件名
arg：其他参数
[oracle@bogon ~]$ cat emp.txt
select count(*) from scott.emp;SQL> start /home/oracle/emp.txt
COUNT(*)
---------- 14SQL> @ /home/oracle/emp.txt
COUNT(*)
---------- 14 3.4 格式化查询结果
3.4.1 column命令
格式化查询结果，设置列宽度，重新设置列标题等
语法格式：
col[umn] [column_name|alias|option]
column_name：指定列的名称
alias：指定列的别名，通过它可以把英文列标题设置为汉字
option：指定某个列的显示格式,具体选项如下:
clear:清除指定列所设置的显示属性,恢复默认属性
format:格式化指定列
heading:定义列标题
justify:调整列标题的对齐方式,默认数值类型列右对齐,其他类型左对齐
null:指定代替列值为null的字符串
print|noprint:显示列标题或隐藏列标题,默认为print
on|off:设置显示属性是否起作用,默认为on
warpped:折行
word_wrapped:从完整的字符处折行
truncated:截断字符串尾部
如果未指定任何参数,则column命令将显示SQL*PLUS环境中的所有列的当前定义属性
如果指定列名,则显示指定列的当前定义属性
1、format
SQL> col sal format $999,999.00
SQL> select empno,ename,sal from scott.emp;EMPNO ENAME SAL
---------- ---------- ------------ 7369 SMITH $800.00 7499 ALLEN $1,600.00 7521 WARD $1,250.00 7566 JONES $2,975.00 7654 MARTIN $1,250.00 7698 BLAKE $2,850.00 7782 CLARK $2,450.00 7788 SCOTT $3,000.00 7839 KING $5,000.00 7844 TURNER $1,500.00 7876 ADAMS $1,100.00EMPNO ENAME SAL
---------- ---------- ------------ 7900 JAMES $950.00 7902 FORD $3,000.00 7934 MILLER $1,300.0014 rows selected.
2、heading
SQL> col empno heading 雇员编号
SQL> col ename heading 雇员姓名SQL> col sal heading 雇员工资SQL> select empno,ename,sal from scott.emp;雇员编号 雇员姓 雇员工资
------------ ---------- ------------ 7369 SMITH $800.00 7499 ALLEN $1,600.00 7521 WARD $1,250.00 7566 JONES $2,975.00 7654 MARTIN $1,250.00 7698 BLAKE $2,850.00 7782 CLARK $2,450.00 7788 SCOTT $3,000.00 7839 KING $5,000.00 7844 TURNER $1,500.00 7876 ADAMS $1,100.00雇员编号 雇员姓 雇员工资
------------ ---------- ------------ 7900 JAMES $950.00 7902 FORD $3,000.00 7934 MILLER $1,300.0014 rows selected.
3、null
SQL> col comm null '空值'
SQL> select empno,ename,comm from scott.emp where comm is null;雇员编号 雇员姓 COMM
------------ ---------- ---------- 7369 SMITH 空值 7566 JONES 空值 7698 BLAKE 空值 7782 CLARK 空值 7788 SCOTT 空值 7839 KING 空值 7876 ADAMS 空值 7900 JAMES 空值 7902 FORD 空值 7934 MILLER 空值10 rows selected.
4、on|off
SQL> col ename off
SQL> select empno,ename,sal from scott.emp;雇员编号 ENAME 雇员工资
------------ ---------- ------------ 7369 SMITH $800.00 7499 ALLEN $1,600.00 7521 WARD $1,250.00 7566 JONES $2,975.00 7654 MARTIN $1,250.00 7698 BLAKE $2,850.00 7782 CLARK $2,450.00 7788 SCOTT $3,000.00 7839 KING $5,000.00 7844 TURNER $1,500.00 7876 ADAMS $1,100.00雇员编号 ENAME 雇员工资
------------ ---------- ------------ 7900 JAMES $950.00 7902 FORD $3,000.00 7934 MILLER $1,300.0014 rows selected.
3.4.2 TTITLE和BTITLE命令
设置页顶部和底部标题
语法格式：
tti[tle] [printspec [text|variable]...]|[off|on]
printspec：用于作为头标题的修饰性选项，常用选项如下：
col：指定在当前行的第几列打印头部标题
skip:跳到从下一行开始的第几行，默认为1
left：当前行左对齐打印数据
center：当前行中间打印数据
right：当前行右对齐打印数据
bold：以黑体打印数据
text：设置输出结果的头标题
variable：在头标题中输出相应变量值
off|on：禁止或者运行打印头标题
btitle与ttitle语法一样
SQL> ttitle left '销售情况排行表'
SQL> btitle left '打印日期：2011年9月20日 打印人：东方'SQL> select * from scott.salgrade;销售情况排行表
GRADE LOSAL HISAL---------- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000打印日期：2011年9月20日 打印人：：东方销售情况排行表
GRADE LOSAL HISAL---------- ---------- ---------- 4 2001 3000 5 3001 9999打印日期：2011年9月20日 打印人：：东方
SQL> set pagesize 8
SQL> define vart='雇员信息表'SQL> define varb='操作员：东方'SQL> ttitle center vartSQL> btitle left varbSQL> select * from scott.salgrade;雇员信息表
GRADE LOSAL HISAL---------- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000操作员：东方雇员信息表
GRADE LOSAL HISAL---------- ---------- ---------- 4 2001 3000 5 3001 9999操作员：东方