sqlplus学习笔记


1、sqlplus是什么？
    sqlplus是一个交互式的，可以进行批处理查询的工具。
每一个oracle数据库安装时都会安装sqlplus。sqlplus是以命令
行方式工作的，SQL*Plus有它自己的命令集和环境。sqlplus为
用户提供一种访问数据库的方式。
    sqlplus能够执行SQL、PL/SQL、SQL*Plus和操作系统命
令来提供以下功能：
    --格式化、执行运算命令、存储、以及输出查询的结果
    --检查数据库里表和对象的定义
    --开发和运行批处理脚本
    --执行数据库管理
    --将数据库查询的结果输出到文本文件、屏幕或者HTML
    文件里
    最后总结一下：sqlplus就是一个普通的oracle数据库
客户端。
 
2、sqlplus相关的环境变量
    这些环境变量指定了sqlplus所使用的一些命令的路径，
或者影响sqlplus的一些运行行为。
 
ORACLE_HOME：指定oracle软件安装在那个目录下
 
ORACLE_SID：指定数据库实例，SQL*Plus可选
 
PATH：指定可执行程序搜索路径，windows环境还用来指定DLLs
的路径。一般包含ORACLE_HOME/bin
 
TNS_ADMIN：连接远端oracle时，指定tnsnames.ora的位置
LD_LIBRARY_PATH：在Unix和Linux系统下指定动态库的搜索路
径。windows环境不需要。
 
SQLPATH：指定要执行的SQL脚本搜索路径
 
3、怎样运行SQL*Plus？
    sqlplus一般安装在$ORACLE_HOME/bin路径下，设置好
PATH后可以在命令行直接执行sqlplus:
 
以下命令的符号约定：
"$"表示系统命令提示符
"[]"表示可选项
"|"表示左右两边的选项并列可选
"{}"里面的可选项只能出现一个，不能共存
 
$ sqlplus [ [options] [logon] [start] ]
 
其中options有以下语法：
-H[ELP] | -V[ERSION] | ... | [-S[ILENT]]
显示帮助、版本等信息
 
其中logon有以下语法：
{username[/password][@connect_indentifier] | /}
[AS {SYSOPER | SYSDBA | SYSASM}] | /nolog
指定登录数据库的用户名、密码以及系统特权，或者(/nolog)
不连接数据库只启动sqlplus，或者只跟(/)使用操作系统认证。
小技巧：最好不要再sqlplus后面直接跟上密码，因为在任务管
理器或者用ps -ef|grep oracle会看到
 
其中start有以下语法：
@{url | file_name[.ext]} [arg ...]
指定进入sqlplus后立即执行的一些脚本
 
4、sqlplus可以执行的命令类型
    有三种类型的命令sqlplus可以执行：
SQL命令：通过数据库工作
PL/SQL块：通过数据库工作
SQL*Plus命令：格式话查询结果、设置可选项、编辑存储SQL命令
和PL/SQL块
 
5、sqlplus的SQL缓冲区
    SQL缓冲区存储最近一次执行的SQL命令或者PL/SQL块。
可以：
执行LIST命令查看缓冲区中的命令。或者简写L
执行RUN或/命令，执行该缓冲区中的命令。
执行SAVE命令将内容保存在文件中。
 
注意：缓冲区不保存SQL*Plus命令！
 
6、安装命令行帮助
    命令行帮助一般在oracle数据库安装时会被安装。如果
没有安装，DBA可以执行一些脚本来安装。
第一步：以SYSTEM用户登录进SQL*Plus
第二步：执行hlpbld.sql和helpus.sql
SQL> @$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql 
卸载命令行帮助：
SQL> @$ORACLE_HOME/sqlplus/admin/help/helpdrop.sql
 
7、退出SQL*Plus命令
    输入EXIT或QUIT，或者Unix下Ctrl+D，或者windows下
Ctrl+Z。
 
8、查看一个表的结构
SQL> describe 表_或_视图名
SQL> desc 表_或_视图名
SQL> describe PL/SQL的函数_或_过程名
 
9、怎样结束一个SQL命令？
    有三种方式结束SQL命令：
使用";"。执行
使用"/",必须另起一行开头处。执行
使用空白行。不执行
 
10、在sqlplus中执行操作系统命令
使用host命令
SQL> host ls -l /tmp
也可以使用"!"(Linux)
SQL> ! ls -l /tmp
也可以使用"$"(windows)
SQL> $ dir c:
或者直接执行"!"，从新开启shell程序
SQL> !
$ ls
$ exit
SQL>
 
11、暂停(pause)查询结果显示，方便查看结果
    语法如下：
SET PAU[SE] {ON | OFF | text}
如果设置set pause on，则可以设置PAESIZE来决定一次暂停显示
多少行数据
set pause text指定每屏之间的间隔字符串
SQL> set pause '^^^^^^^^^^^^^^^^^^^^'
SQL> show pause
 
12、sqlplus的自动提交(autocommit)
    DML命令(insert,update,delete)需要执行COMMIT命令或
执行DCL(GRANT或REVOK)命令或DDL命令(create table)后才会被记
录到数据库中。
    通过设置AUTOCOMMIT使sqlplus自动提交。
SQL> set autocommit on
SQL> set autocommit immediate
SQL> set autocommit 10 --执行10条DML语句会自动提交
SQL> set autocommit off
SQL> show autocommit
 
13、在sqlplus中编辑脚本
    使用START命令、@、或@@可以执行脚本。也可以在sqlplus
中编辑脚本。
SQL> edit myscripts
其中edit可以指定：
SQL> define _editor = vi
其中myscript会保存在当前目录，并自动加上.sql后缀
 
14、如何在脚本中加入注释？
    有三种方式在脚本中加入注释：
使用：REMARK命令：remark 此处是备注。也可简写为REM
使用：/*此处是备注*/
使用：--此处是备注
 
15、使用SPOOL保存查询结果
    使用SPOOL不但可以使查询结果在屏幕上输出，还同时将
结果保存在指定的文件中。
语法：
SPO[OL] [file_name[.ext] [CRE[ATE] |　REP[LACE]] \
    | APP[END]] | OFF | OUT]
 
16、查询用户有哪些表空间
SQL> select object_name, object_type from user_objects;
 
17、使用变量替换
SQL> select * from m_table where id=&myid;
Enter value for myid:
 
18、格式调整有以下参数
 
set echo on/off       是否显示脚本中的需要执行的命令
set feedback on/off   是否显示select结果之后返回多少行的提示
set linesize n        设置一行最多显示多少字符
set termout on/off    在执行脚本时是否在屏幕上输出结果
set heading on/off    是否显示查询结果的列名
set pagesize n        设置每页的行数
set trimspool on/off  在spool到文件时是否去除输出结果中行末尾的空白字
set trimout on/off    是否去掉屏幕上输出结果行末尾的空白字符                      
set space on/off      输出列间空格数设置
 
如果需要sqlplus下次启动的时候自动调整这些格式，可以将上面的设置保存到$ORACLE_HOME/sqlplus/admin/glogin.sql文件
 
19、列格式
 
col column_name format a50   设置column_name为50个字符宽
col column_name              显示列格式
col column_name noprint      隐藏列
CLEAR COLUMNS                将所有列的显示