如何个性化设置自己的sql*plus环境

sql*plus简单，强大和无处不在。用sql*plus主要做什么呢？

1）autotrace

2）编程

在sql*plus创建存储过程的“最佳实践”应该是这样的：

create or replace program-type
as
your code
end；
/
show err

sql*plus会自动运行glogin.sql，该脚本包含全局默认设置。我们可按需自定义自己的sql*plus环境。

1）、首先在系统的环境设置中定义一个环境变量SQLPATH，把你自己的环境设置脚本login.sql和connect.sql
分别放在SQLPATH目录下。

2）、定义一个login.sql脚本，此脚本是SQL*PLUS打开的时候自动执行的脚本。

3）、定义一个脚本connect.sql，此脚本是在身份切换时，调用的脚本。

在实现这login.sql这个脚本，我们需要先掌握些基本的sql*plus知识。

友情提醒：命令太多不必记住。
a]help index
b]help 命令
这两步合起来，友善的sql*plus就会告诉你了。

1）执行一个sql脚本
@
@@：执行嵌套脚本。说明嵌套脚本和宿主在同一个目录下。sql*plus只会在当前目录（sql*plus登入的目录就是当前目录）下找执行文件，两个@就是告诉他，一个@所在的目录就是当前目录。
练习：
在$ORACLE_HOME/dbs下有脚本think.sql和sub_think.sql。
think.sql的内容是：@@sub_think.sql
则我们可以这样执行：
@$ORACLE_HOME/dbs/think.sql

@@告诉sql*plus，$ORACLE_HOME/dbs为当前目录。
2)对当前的输入进行编辑
edit
通常我们说的DML,DDL,DCL等都是sql*plus语句，他们执行完会被保存在sql buffer的内存区域，并且只能保存一条最近执行的sql语句。
3）重新运行在sql buffer中的语句
/
4）将显示的内容输出到指定文件
spool filename
/*你在sql*plus中所做的任何事*/
spool off
然后你就可以到file所在路径查看。
5）col命令
a] 改变缺省的列标题
COL 默认列标题 HEADING 自定义列标题

SQL> select first_change# from v$log;

FIRST_CHANGE#
-------------
       680280
       638906
       659033

SQL> col first_change# heading think
SQL> /   

     think
----------
    680280
    638906
    659033

SQL> col first_change# heading "go into current's scn"
SQL> /

go into current's scn
---------------------
               680280
               638906
               659033
SQL> col first_change# heading "fisrt_change#|go into current"
SQL> /

  fisrt_change#
go into current
---------------
         680280
         638906
         659033

b] 改变列的显示长度
col 字段名 for a多少
这里有个问题，我们需要注意。在sql*plus中，列的显示总是从最左边开始。而且，字符值左对齐；数值右对齐。
下面的例子可以说明。

SQL> select ename,job,deptno from scott.emp where rownum=1;

ENAME      JOB           DEPTNO
---------- --------- ----------
SMITH      CLERK             20

SQL> col ename for a20
SQL> /

ENAME                JOB           DEPTNO
-------------------- --------- ----------
SMITH                CLERK             20

SQL> col deptno for a20
SQL> /

ENAME                JOB           DEPTNO
-------------------- --------- ----------
SMITH                CLERK     ##########

c] 设置列标题的对齐方式

col 字段名 justify [L|R|C]

SQL> /

ENAME      JOB           DEPTNO
---------- --------- ----------
SMITH      CLERK             20

SQL> col job justify c
SQL> /

ENAME         JOB        DEPTNO
---------- --------- ----------
SMITH      CLERK             20

d]设置一个列的回绕方式
col 字段名 for axx
按需选择下面一个
col 字段名 wrapped 或者
col 字段名 word_wrapped 或者

col 字段名 truncated

SQL> select * from t;

TEXT
--------------------------------------------------------------------------------
I must work hard for my parents

SQL> col text for a6
SQL> /

TEXT
------
I must
 work
hard f
or my
parent
s


SQL> col text word_wrapped
SQL> /

TEXT
------
I
must
work
hard
for
my
parent
s


SQL> col text truncated
SQL> /

TEXT
------
I must

e]显示列的当前属性值
col 字段名

SQL> col text
COLUMN   text ON
FORMAT   a6
truncate 

f]将所有列清为缺省值
clear columns

SQL> clear col
columns cleared
SQL> col text
SP2-0046: COLUMN 'text' not defined

6）set命令

a]设置当前session是否对修改的数据进行自动提交

SQL> set auto on
SQL> update t set text='think big for my future';

1 row updated.

Commit complete.
SQL> select * from t;

TEXT
--------------------------------------------------------------------------------
think big for my future

SQL> rollback;

Rollback complete.

SQL> select * from t;

TEXT
--------------------------------------------------------------------------------
think big for my future

SQL> set auto off

b]是否显示当前sql查询或修改的行数
SET FEED[BACK] {6|n|ON|OFF}
默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数。

SQL> select * from t;

TEXT
--------------------------------------------------------------------------------
think big for my future

SQL> set feed 1
SQL> /

TEXT
--------------------------------------------------------------------------------
think big for my future

1 row selected.

c]是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。
SET SERVEROUT[PUT] {ON|OFF}

SQL> exec dbms_output.put_line('think');

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> exec dbms_output.put_line('think');
think

PL/SQL procedure successfully completed.

d]显示每个sql语句花费的执行时间

set TIMING {ON|OFF}

e]遇到空行时不认为语句已经结束，从后续行接着读入。
SET SQLBLANKLINES ON

Sql*plus中, 不允许sql语句中间有空行, 这在从其它地方拷贝脚本到sql*plus中执行时很麻烦. 比如下面的脚本:
select deptno, empno, ename
from emp
/*我是空行*/
where empno = '7788';
如果拷贝到sql*plus中执行, 就会出现错误。这个命令可以解决该问题

7）对sql buffer内的数据的操作
a]修改
c/旧值/新值

QL> l
  1* select * from t
SQL> c/select/update
  1* update * from t
SQL> l
  1* update * from t
SQL> c/from/think
  1* update * think t
SQL> l
  1* update * think t
    b]编辑
    edit
SQL> edit
Wrote file afiedt.buf

  1* update * think t

c]显示
list n显示sql buffer中的第n行，并使第n行成为当前行

SQL> update t
  2  set text='think';

1 row updated.

SQL> l
  1  update t
  2* set text='think'
SQL> l 2
  2* set text='think'

d]将sql buffer中的语句保存到一个文件中

SQL> save /home/oracle/t.sql
Created file /home/oracle/t.sql

e]将一个文件中的sql语句导入到sql buffer中

get file_name

8)在输入sql语句的过程中临时先运行一个sql*plus命令
另起一行，#打头

SQL>> select deptno, empno, ename
2 from emp
3 where
6 #desc 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)
6 sal > 4000;
DEPTNO EMPNO ENAME
---------- ---------- ----------
10 7839 KING

基础篇到这边介绍完了，建议大家可以参阅oracle官网的sql*plus指南。下面我们用Tom的login.sql来介绍一下个性化配置sql*plus的思路

1、编辑login.sql文件：
REM 禁止任何输出,确保用户登录时不显示任何信息
set termout off
REM 定义默认编辑器为vim
define _editor=vi
REM 设定输出缓冲区的大小
set serveroutput on size 1000000 format wrapped
REM 设定默认的列宽度
column object_name format a30
column segment_name format a30
column file_name format a40
column name format a30
column file_name format a30
column what format a30 word_wrapped
column plan_plus_exp format a100
set trimspool on
REM 定义对long数据类型的数据显示的大小
set long 5000
REM 定义行的长度为多少字符
set linesize 131
REM 定义页大小
set pagesize 9999
REM 定义提示符
define gname=idle
column global_name new_value gname
select lower(user) || '@' ||
substr(global_name,1,decode(dot,
0,length(global_name),
dot-1)) global_name
from (select global_name,instr(global_name,'.') dot
from global_name);
set sqlprompt '&gname '
REM 设置显示系统时间
set time on
REM 再次显示输出
set termout on
2、编辑connect.sql文件
set termout off
connect &1
@login
========================login.sql=================================================
set termout off
define _editor=vi
set serveroutput on size 1000000 format wrapped
column object_name format a30
column segment_name format a30
column file_name format a40
column whar format a30 word_wrapped
column plan_plus_exp format a100
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
define gname=idle
column global_name new_value gname
select lower(user)||'@'||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name,instr(global_name,'.') dot from global_name);
set sqlprompt '&gname '
set time on
set termout on