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