SQL*PLUS用户指南和参考
一、 SQL*PLUS的安装
1. sqlplus :oracle公司提供用户操作oracle数据库的工具。
安装所需的包:
①oracle 客户端 ②sqlplus工具
官方下载地址 :http://www.oracle.com/technetwork/topics/winx64soft-089540.html
注:选择相对应的版本 且版本号要一致
2. 解压两个包将两个包的所有文件放在同一目录下
3. 配置sqlplus 的配置文件
在步骤2的目录下新建 network/admin目录并且新建文件 tnsnames.ora
4. tnsnames.ora的格式如下
abc = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 99.6.136.49)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ygzdb) ) ) |
5. 可以设置将当前目录加入path 环境变量中以方便操作系统查找 sqlplus.exe 的运行程序。
SQL*Plus命令行工具无需执行exe安装,所以只需将下载回来的两个文件解压到同一个目录即可,解压后文件名应该为instantclient_12_1,在运行工具之前我们需要在windows中配置以下环境变量,先右键计算机->属性->高级系统设置->环境变量,在系统变量中找到Path并在后面加上刚才解压后instantclient_12_1的目录与sdk子目录 D:\java\instantclient_12_2\; D:\java\instantclient_12_2\sdk; 再新增两个变量到系统环境中 TNS_ADMIN= D:\java\instantclient_12_2 NLS_LANG=AMERICAN_AMERICA.UTF8 |
6. 连接数据库详细语法
sqlplus username/password #如:普通用户登录 sqlplus scott/tiger
sqlplus username/password@net_service_name #如: sqlplus scott/tiger@orcl
sqlplus username/password as sysdba #如: sqlplus sys/admin as sysdba sqlplus username/password@//host:port/sid
|
注意:sys和system需要以sysdba登录
7. 连接可能遇到的错误
a) ORA-12514 TNS:listener does not currently know of servicerequested in connect descriptor.
原因为环境变量中没有配置TNS_ADMIN,加上配置后错误就解决了。
b) 在sql*plus中,如果select语句查询的某一字段很宽,可以使用哪个命令将该行的显示宽度设置为一个。
三种调节方式:
i. SQL>column字段名 format a10;
ii. –查看目前的linesize,默认是80
SQL>show linesize;
–设置成200或者更宽都可
SQL>set linesize 200;
iii. 首先查看目前的pagesize,默认是14。
SQL>show pagesize;
SQL>set pagesize 100; –将pagesize设置好100,则可以一次显示够多行记录了。
c) 使用SQL*Plus登录到数据库上之后,输入下面的SELECT语句,这条语句会返回数据库中的日期:
SELECT SYSDATE FROM dual;
注意:直接输入SQL*Plus中的SQL语句以分号(;)结束。
二、 SQL*PLUS常用指令
a) 编辑SQL语句
命令 | 说明 |
append text | 将text附加到当前行之后。 |
change /old/new | 将当前行中的old替换为new |
clear buffer | 清除缓冲区中的所有行 |
del | 删除当前行 |
del x | 删除第x行(行号从1开始) |
list | 列出缓冲区中所有行 |
list x | 列出第x行 |
run或/ | 运行缓存区中保存的语句 |
b) 保存、检索并运行文件
命令 | 说明 |
save filename | 将SQL*Plus缓冲区的内容保存到由filename所指定的文件中。 |
save filename [replace|append] | 选项说明将缓冲区的的内容追加到一个现有的文件之后;REPLACE选项说明覆盖一个现有的文件。 |
get filename | 将filename所指定的文件的内容读入SQL*Plus缓冲区中。 |
start filename | 将由filename所指定的文件的内容读入SQL*Plus缓冲区中,然后试图运行缓冲区中的内容。 |
@ filename | 与start 命令相同 |
edit | 将SQL*Plus缓冲区的内容复制到一个名为afiedt.buf的文件中,然后重启操作系统默认编辑器。在退出编辑器时,所编辑的文件将被复制到SQL*Plus缓冲区中。 |
edit filename | 与edit命令相同,但是可以指定要进行编辑的文件,它的文件名可以使用filename参数指定。 |
spool filename | 将SQL*Plus中的输出结果复制到filename所指定的文件中。 |
spool off | 将SQL*Plus中的输出结果复制到filename所指定的文件中,并关闭该文件。 |
c) 格式化列
COLUMN命令用于格式化列标题和列数据的显示格式
COLUMN { column |alias} [options]
l column 指定列名
l alias 指定要格式化的列的别名。
l options 指定用于格式化列或别名的一个或多个选项。
命令 | 说明 |
format | 将列或别名的显示格式设置为由format字符串指定的格式。 |
heading | 将列或别名的标题中的文本设置为由heading字符串指定的格式。 |
justify [left|right|center] | 将列输出设置为左对齐、居中或右对齐。 |
wrapped | 在输出结果中将一个字符串的末尾换行显示。该选项可能导致单个单词跨越多行。 |
clear | 清除列的任何格式化(将格式设置回默认值)。 |
d) 设置页面大小
set pagesize 100 页面大小设置为100行。
注意:页面大小最大为50000。
e) 设置行大小
set linesize50 一行不能多于50个字符。
注意:行大小最大为32767。
f) 使用变量
- 临时变量
在SQL语句中,可以使用字符&定义临时变量
例子:
SQL>SELECT XFSH,XFMC FROM BAS_XFXX WHERE XFSH =&XFSH_VAR;
SQL>输入 XFSH_VAR 的值:‘11’
命令 | 说明 |
set verify off | 禁止显示原行和新行。 |
set define | 指定一个除字符&之外的字符,用来定义变量。 |
- 已定义变量
命令 | 说明 |
define | 可以用来定义一个新变量,也可以用来查看当前已经定义的变量。 |
set define | 指定一个除字符&之外的字符,用来定义变量。 |
accept | 等待用户为变量输入一个值 |
ACCEPT命令简化语法:
ACCEPT variable_name [type] [format] [prompt] [hide]
- variable_name 指定了为变量分配的名称。
- type 指定了变量的类型,可以使用的类型为CHAR、NUMBER和DATE。默认为CHAR。DATE类型的变量实际上以CHAR变量存储。
- format 指定变量的格式(A15 15个字符、9999 一个4位数字、DD-MON-YYYY日期)。
- prompt 指定了SQL*Plus所显示的提示文本,提示用户输入变量的值。
- hide 说明隐藏变量输入值,隐藏敏感信息。
g) 创建简单报表
- 在脚本中使用临时变量
例子:
report1.sql文件,使用了一个名为xfsh_var的变量:
set echo off set verify off select xfsh,xfmc from bas_xfxx where xfsh = & xfsh_var; |
set echo off命令禁止SQL*Plus显示脚本中的SQL语句和命令。set verify off命令禁止显示验证信息。输入这两个命令,运行脚本时SQL*Plus所显示的多余行数可以减到最少。
- 在脚本中使用已定义变量
例子:
set echo off set verify off
accept xfsh_var char prompt 'Enter xfsh:' select xfsh,xfmc from bas_xfxx where xfsh = &xfsh_var; undefine xfsh_var |
在脚本末尾将xfsh_var删除,这样使得脚本非常感激。
- 向脚本中的变量传递值
set echo off set verify off
select xfsh,xfmc from bas_xfxx where xfsh = '&1'; |
我们可以添加任意个参数,命令行中指定的每个值都会对应于文件中的一个匹配数字。第一个参数对应&1,第二个参数对应&2。
三、 SQL*PLUS命令详解
命令 | 说明 | 举例 |
sqlplus username/pwd@xxx | 登陆sqlplus |
|
exit | 退出 |
|
CTRL+C | 强制中断会话 |
|
help index | 调出sqlplus帮助索引界面 |
|
@ <scriptname> | 运行指定脚本 |
|
@@ <scriptname> | 同@,运行nest script时使用 |
|
/ | 执行最近脚本 |
|
accept | 接受用户输入行存储到替代变量中 | SQL>ACCEPT pswd CHAR PROMPT 'Password: ' HIDE |
append | 向当前SQL行追加文本 |
|
achive log | 查看和管理归档信息 |
|
attribute | 设置object类型column属性 | SQL>ATTRIBUTE EMPLOYEE_TYPE.LAST_NAME FORMAT A20 |
break | 分开重复列 |
|
COLUMN | 改变列格式 | COLUMN SALARY FORMAT $99,999 --设置数字显示格式 COLUMN LAST_NAME FORMAT A10 --设置LAST_NAME列每行显示10个字符 COLUMN COMMISSION_PCT LIKE SALARY HEADING BONUS --LIKE通知SQLPLUS COPY之前定义好的列SALARY属性 COLUMN column_name CLEAR --清楚已设置列属性 CLEAR COLUMNS --重设所有列属性 |
compute |
|
|
change err/correct | 修改错误代码 |
|
connect | conn | 会话中切换连接命令,如conn hr/hr@orcl |
|
disconnect | 断开连接 |
|
clear buffer | 清空sql buffer |
|
clear screen | 清空screen |
|
copy |
|
|
define <name> | 定义变量( variables) |
|
undefine | 取消替代变量 |
|
describle/desc <table|view |function|synonym|procedure> | 描述表、视图的列以及函数、程序的说明。 |
|
del <line n> | 删除SQL BUFFER指定行(不知道n 则倒序删除行) |
|
edit | ed | 创建或编辑sql 脚本 |
|
execute <statement> | 执行语句 |
|
get <sqlfilename> | 将sql脚本从文件load into sql buffer |
|
save <filename> | 将sql buffer内容保存至文件 |
|
input | 追加新行enter a new line |
|
list | L <n> | SQL BUFFER中sql语句 |
|
password | 修改current user 密码 |
|
|
| |
prompt | 向screen发送消息 |
|
quit | 用法同exit |
|
recover | 恢复数据库等操作 |
|
repheader | 放置report头(见ttitle) |
|
repfooter | 放置report角 |
|
remark | rem | 单行注释 |
|
show <variable> | 显示变量或参数值 |
|
shutdown | 关闭数据库(DBA用户执行) |
|
startup | 启动数据库(DBA用户执行) |
|
set autocommit on | 设置事务自动提交 |
|
set verify off | 关闭替代变量校验 |
|
set timing on | 设置显示“已用时间:XXXX” |
|
set autotrace on | 设置允许对执行的sql进行分析。 |
|
set trimout on | 去除标准输出每行的拖尾空格,缺省为off。 |
|
set trimspool on | 去除重定向(spool)输出每行的拖尾空格,缺省为off。 |
|
set echo on | 设置运行命令是是否显示语句。 |
|
set feedback on | 设置显示“已选择XX行”。 |
|
set echo on | 设置运行命令是是否显示语句。 |
|
set echo off | 显示start启动的脚本中的每个sql命令,缺省为on。 |
|
set feedback on | 设置显示“已选择XX行”。 |
|
set feedback off | 回显本次sql命令处理的记录条数,缺省为on |
|
set colsep' ' | 输出分隔符。 |
|
set heading off | 输出域标题,缺省为on |
|
set pagesize 0
| 输出每页行数,缺省为24,为了避免分页,可设定为0。 |
|
set linesize 80 | 输出一行字符个数,缺省为80。 |
|
set numwidth 12 | 输出number类型域长度,缺省为10 |
|
set termout off | 显示脚本中的命令的执行结果,缺省为on。 |
|
set serveroutput on | 设置允许显示输出类似dbms_output。 |
|
set verify off | 可以关闭和打开提示确认信息old 1和new 1的显示。 |
|
spool | 设置输出文件。 | SQL>spool d:\tempfile\oracle\output.txt;
|
spool off | 把内容从缓存里面输出到文件吧。 | |
set termout off | on | 关闭打开sqlplus输出显示。 | |
show all | 列出所有参数。 | |
ttitle | 设置report抬头。 | |
btitle | 设置report尾。 | |
timing | 计时器。 | SQL> timing start sql_timer SQL> timing show SQL> timing stop SQL> sql_timer |
variable <name> | 声明绑定变量供PLSQL块引用。 | |
whenever oserror exit | 执行命令中遇到系统异常自动退出 | |
whenever sqlerror exit sql.sqlcode | 执行命令中遇到SQL异常自动退出 | |
XQUERY | 运行xquery语句 |