SQL_PLUS 常用格式 & 用法

 用法

sqlplus需要设置环境变量

oracle_home 环境变量

oracle_sid( 只是针对安装的server需要此环境变量，客户端登录sqlplus时不需要此环境变量)

path 特别是 oracle_home/bin 执行目录，只有设置了这个环境变量才能在任何位置执行sqlplus

tns_admin(tnsnames.ora) 我们自己系统设置此内容，只有设置此内容才准许远程的sqlplus登录服务器

ld_library_path 只是针对 linux/unix 这里边有有一些库文件 windows的库文件都放在oracle_home中了(oracle需要的)

sql_path ( 当运行脚本时，如果你输入的时全路径，没有问题，否则sqlplus会首先寻找本目录，如果没有就会寻找sql_path这个目录下是否有该脚本文件)

sqlplus登录

sqlplus [options] [logon] [start]

options ：表示登录时的一些参数，-h( help ) 帮助信息，-v (version)版本信息，-s (silent 不显示输出情况,一般在批处理脚本中使用)

logon ：表示连接信息,语法如下

username[/password] [@connection identifier] / 或者 如下 

/as  { sysoper | sysdba | sysasm }   /no log

注意，上面的username/password一般时远程客户端登录使用,但是不要用户名密码一起输入,因为再进程中会发现用户名和密码，所以先输入用户名，当遇到提示说让输入密码时再输入密码，最后的 / 是表示当sqlplus于server是用一个机器时使用,表明使用操作系统权限登录。第一行的@connection identifier也是远程登录时使用，这个名字跟tnsname.ora这个文件中的名字是同一个。第二行表明使用哪些角色登录，直接连接了数据库。第二行的 /no log 表明我只是先登录 sqlplus但是并不连结数据库(nolog)，进入sqlplus以后再conn 连结数据库。没连结前可以使用基本的sqlplus命令。

补充 : 常用的连接方式 :

    a. 远程 : 1. sqlplus /nolog -> conn hanaro@htnshz -> 再输入密码 -> OK , 2. sqlplushanaro@htnshz -> 再输入密码 -> OK

    b.本地(同一台机器) : 1.使用本地管理员权限 sqlplus /nolog -> conn /as sysdba ( 注意没有输入密码,因为使用了管理员权限 ) 2. 不使用本地管理员权限, 使用passwordfile, sqlplus /nolog -> connect sys/password as sysdba -> OK . 

list命令; 查看sql buffer中的命令( 最近上一条命令) 是sqlplus的命令, list只是查看sql buffer中的命令，如何执行sql buffer中的命令呢，使用 run或者 / 执行sql buffer中的命令

run 于 / 的区别，run是先显示sql buffer中的命令再输出结果，而 / 是直接将结果输出。

start ：是指连结数据库时直接执行的脚本, 语法为 @ { url | filename} [argument] , 脚本名称和执行参数。

save 命令 保存 sql buffer中信息到操作系统 save /tmp/asdf

运行sqlplus 帮助文档 @?/sqlplus/admin/help/hlpbld.sql helpus.sql ( 其中?代表的是oracle的家目录,即 $oracle_home，helpus.sql是参数) .. 要以 system登陆

删除刚刚安装的脚本 @?/sqlplus/admin/help/helpdrop.sql

查看help信息 help index ( 当没有安装脚本时，输入help没有翻译，安装完后。输入help会看到帮助信息)

退出 exit / quit

输入错误时, 想重新输入 两下回车就可以了。

sqlplus 命令不需要 ; 分号

pl/sql 使用 . 表示输入完成,但是只是输入完成，内容保存再sqlbuffer中，如果想执行内容，使用／,也可以直接使用／执行pl/sql命令

sqlplus中如果指令太长可以使用 －连字符，连接下一行，作为一行输入命令

ctrl + c 可以强行中断在屏幕上输出的内容。

使用 host 或者 ! 可以从sqlplus中切换出来，切换到操作系统的bash中。再使用 exit 切换回 sqlplus环境 , 在bash中,此时 ps -ef 可观察内存，其中pid为子线程, ppid 为父线程，从内存的情况可以看出从sqlplus中切换出来是创建了新的bash，并非回到原来的bash，此bash的父进程是sqlplus进程，是嵌套关系。

常用格式

remark  --备注   使用以下格式调整,有利于打印和观看(使用SQL/PLUS时)

set headsep! --设置分隔符 , 利用 ! 分隔 ( 在哪里把超出一行的页标题或者列标题分开)  分开成两行

ttitle  --头标题   要用 '' 括起来 ttitle 'asdf'
btitle  --尾标题  要用 '' 括起来 btitle 'ssss'

column  --设置列 标题,格式,处理指令 等等
 column Name format a20  --Name列宽度为20 word_wrapped(接着) / truncates(连续)
 column Price format 999.99 --定义价格列的格式,其中前边要多出一位,如果位数少了,负号就被冲掉了
 column DayOut heading 'Days|out'-- 如上所示的分行 分出之后的效果 Days   注意，中间只是一个| 数棍 ( || 或操作符的一个)
                                                                                                                               out
 
 break on Name ( 默认为相同的省略,如果加 duplicate 则重复出现 )skip 1 on report --跳行,其中根据 Name列值不同时跳行, 要和order by Name 命令连用,要排序,正常
 break on Name duplicate skip 1 on report
 
 compute avg /sum / count / max  of Daysout on Name     --这两条命令看成一个整体, compute 命令要和 break on 命令一起使用 , 并且两个 on 后边接相同列
 compute avg /sum / count / max  of Daysout on report   -- 每个 break on 必须有个相关的 order by , 每个 compute avg / sum / count /max 必须有个 break on .
 
 set linesize 120  --设置一行最多字符个数为 80 个
 
 set pagesize 14       --设置每页的行数, 这个例子设置的数值跟容易打印,(每次换新页时会另起一页,如果页的长度设置得当,则最好)
 set newpage 0  --设置页与页之间的行间数
 
 set feedback off / 14--设置不需要知道查询到多少行,或者最少为25行才显示 feedback, 可以用 show feedback 查看
 set numwidth 5  --设置所有的数字列宽度为 5 , 并非单指某列

 set serveroutput on 使客户端dbms.output可以使用
 
 spool abc.lst --SQL从SQLPLUS中提取所有的输出并将其写入到名为 abc.lst的文件中   .lst文件为假脱机文件
 spool off  --结束接收 一般情况下, 在SQL语句前放 spool abc.sql 并在SQL语句后立刻放 spool off ,这样就只接收SQL语句返回的有用的内容

 spool 这个命令相当重要, 可以将内容保存成脚本后,再根据脚本执行,例如想删除一个用户下的所有table,可以先使用data dictionary查到这个用户的所有表,并且查询语法为select 'drop' || table_name || ';' 此时保存这个脚本时，这个脚本就可以直接执行，删除所有的table.
 
 list   --当SQL语句出现错误时, list会把之前放在BUFFER中的SQL语句掉出,没有必要重新输入所有SQL语句
 list 1  --比如第一行错了, 这个命令会返回第一行做为当前行 , 错误语句行前有 * , 修改完后 / ,告诉SQLPLUS在缓冲区中执行 , 将第一行变成当前行

clear screen 是在sqlplus中清屏，类似操作系统的clear命令
    
 change  --修改当前行
 del  --删除当前行
 del 3  --删除第3行
 clear buffer --清空整个 buffer

 A[PPEND] text                      -- Adds text to the end of the current line 当增加的前面需要有空格时，命令要有2个空格 a  and name='asdf' 注意 a 后有2个空格

当想增加一整行而不时象 append是在当前行增加内容时，以第2行为例子，如果想在第2行之前插入，则输入 2 要增加内容，如果要在第2行之后插入，则首先输入2，然后回车，再输入要增加的内容。

 C[HANGE] / old / new         -- Change old text to new in the current line

 C[HANGE] / text /                 -- Deletes text from the sql buffer

 CL[EAR] BUFF[ER]             -- Deletes all ines from the SQL buffer

 DEL                                       -- Deletes current line

 DEL n                                    -- Deletes line n

 DEL n *                                  － 从 n 行到 当前行

 DEL n last                              － 从 n  行到 最后行

 DEL m n                                -- Deletes lines m to n inclusive

 分号 或者 / 结束 SQL/Plus 输入 .

 如果一行没有书写完毕 , 使用 - 连结符 接着下一行输入 .

 I [ NPUT ]                              -- Inserts an indefinite number of lines ( 当前应该输入的行, 比如之前输入 select * from employees , 当输入 I 时, 就会出现2

                                                    表示该输入的为第二行, 这样你可以在原来的基础上添加语句, 例如输入 I, 然后写 where last_name = 'a'

I [NPUT]    text                        -- Inserts a line consisting of text ( 感觉是另起一行, 将 text 加上 )

L[ IST ]                                     -- 显示全部行 ( SQL buffer )

L[ IST ] n                                 -- 显示一行 ( n 那个行 )

L[ IST ] m n                            -- 显示行 ( 从 m 到 n )

R[ UN ]                                   -- 显示并运行  SQL buffer 中的内容

n                                              -- 输入的行号变成了 current line

0       text                                 -- 再第一行前加入 text ( 这个是数字 0 , 不是 字母 o )

SAVE filename   ( 将 sql buffer 中的内容，保存在某个文件中，只保存 sql 语句 )

GET filename     ( 将文件内的 sql 语句，读取到 sql buffer 中 )

START filename                  相同意思

@ filename                          @ /tmp/asdf.sql

EDIT filename                      调用编辑软件 ( 需要设置成 vi )

SPOOL filename                 存储查询结果 ( 全部显示，包括，结果，系统提示，语句等等，适合备份 )

EXIT

注释 单行 －－    多行 /* */      remark 也是单行注释, 这种只能在sqlplus中运行    共 3 种

注释注意 编辑的前几行内容不能注释，别把 注释放到 ；. / 这些符号后边，本来这些符号代表着编辑结束，如果把注释放到后边就表示没有结束

               另外不能在注释中出现 ; & 等符号

echo on / off  表示 当我们写完一个脚本以后，如果是 echo on 执行脚本时会将脚本输出, echo off 就只输出脚本结果。不输出脚本内容。

set pause on / off / 'More...' --设置停顿 ,关看查询结果时,如果页多,一下子就过去了,所以要用More查看,类似 Linux中的 More，进入后要按回车进行，如果想停止使用ctrl+c回车停止

set autocommit 10 , set autocommit on / off 设置自动commit 或者是 10 条DML操作后commit .

设置 edit 为 vi 的方法( 永久保存 ) 修改$ORACLE_HOME/sqlplus/admin/glogin.sql配置文件，增加define _editor=vi即可，sqlplus启动的时候会读取glogin.sql脚本。

define _editor 设置默认编辑器,使用不多, 不如直接切换到操作系统下是用vi

define _editor=vi

 save a.sql ( a.sql rep )  --保存SQL脚本, 如果 a.sql已经存在 , 则需要使用 a.sql rep 覆盖
 
 start a.sql --逐行执行 SQL 脚本

 show linesize , show pagesize , show newpage , ttitle off , btitle off , clear columns , clear breaks , clear computes  -- 清空,查看,等等  

替换变量 sqlplus 的功能

  例如 select * from m where id=12; 此时可以使用替换变量，不用每次都输入sql语句，select * from m where id=&myid ; 此时输入回车时，sqlplus会提示你输入myid的值，输入12时跟上面的内容一样。