Oracle sqlplus使用总结

文章目录

  • 0、参考资料
  • 1、sqlplus使用技巧案例
    • 1.1、设置个securecrt按钮,防止误操作库
    • 1.2、Thomas Kyte大神的SQL*PLUS设置例子
    • 1.3、解决小案例汇总
  • 2、sqlplus如何优雅的输出结果为html文档
  • 3、通过SQL*PLUS向shell传递变量
  • 4、sqlplus常用设置

0、参考资料

参考官方文档:SQL*Plus® User’s Guide and Reference
https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_three.htm#SQPUG013
文章以oracle 11g r2为例,11g+版本会有新功能的增强,不过变化不大。

1、sqlplus使用技巧案例

1.1、设置个securecrt按钮,防止误操作库

主要是把数据库实例名提示出来:

set sqlprompt _user'@'_connect_identifier> 
set serveroutput on 
set long 5000 
set linesize 200 
set pagesize 9999 
col member for a60 

1.2、Thomas Kyte大神的SQL*PLUS设置例子

sqlplus在启动时会自动运行两个脚本:glogin.sql、login.sql这两个文件。

执行顺序:

先判断是否设置SQLPATH环境变量,如果设置了该变量则在对应路径下查找并执行

1.默认在在$ORACLE_HOME/sqlplus/admin路径下查找glogin.sql文件执行

2.默认在sqlplus当前路径下$ORACLE_HOME/bin查找login.sql文件执行

Thomas Kyte大神的glogin.sql或者login.sql脚本如下:

set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
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 termout on

说明:

(1)set serveroutput on size 1000000:
这会默认地打开DBMS_OUTPUT(这样就不必每次再键入这个命令了)。另外也将默认缓冲区大小设置得尽可能大。

(2)set trimspool

on:假脱机输出文本时,会去除文本行两端的空格,而且行宽不定。如果设置为OFF(默认设 置),假脱机输出的文本行宽度则等于所设置的LINESIZE。

(3)set long 5000:设置选择LONG 和CLOB 列时显示的默认字节数

(4)set linesize 100:设置SQL*Plus 显示的文本行宽为100 个字符。

(5)set pagesize 9999:
PAGESIZE 可以控制SQL*Plus多久打印一次标题,这里将PAGESIZE设置为一个很大的数(所以每页只有一组标题)。

(6)column plan_plus_exp format a80:
设置由AUTOTRACE 得到的解释计划输出(explain planoutput)的默认宽度。a80 通常足以放下整个计划。

(7)column global_name new_value gname
指令告诉SQL*Plus 取得GLOBAL_NAME列中的最后一个值,并将这个值赋给替换变量GNAME。

(8)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 );

从数据库中查出global_name(global_name=db_name.do_main)截取db_name与登录的用户名连接。得到sqlplus提示符为:sys@ORCL> 这样就知道用户是谁,登录的那个数据库

1.3、解决小案例汇总

  • sqlplus中执行存储过程的DBMS_OUTPUT并没有结果
set serveroutput on
  • sqlplus中贴一些带有空行的sql报语法错误

有空行导致sql语句加载到机器内存中截断了,导致一堆报错,说是无法识别到命令啥的,解决:

set sqlblanklines on
  • 解决数字的显示变成科学计数法,或者其它不识别格式

在查询oracle scn是由于数字太长,显示的结果变成了科学计数法,看着很不爽
用下面三种方法可以解决:

1、Set numw 20 轻松解决,默认值 show numw 是10 最大值是50

2、SQL> col colname format 99999.99

3、SQL>set numformat 999999999.999999999

附加:
PL/SQL中每行长度问题

set colwidth nnn

2、sqlplus如何优雅的输出结果为html文档

参考资料:Data Collection - Script to Gather Oracle Text Index Information (Doc ID 1902197.1)

How to Create HTML with SQL*Plus (Doc ID 136207.1)

Example 6 in Note 136207.1 for HTML Output Generates > < instead of ‘<’ ‘>’ (Doc ID 239667.1)

Markup HTML Causes &, ", >, and < To Be Displayed As & " > and < (Doc ID 239424.1)

案例:

set markup html on entmap off spool on preformat off  
set pages 400
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
spool out.html
select t.logtime ,
      id,
      case scount - dcount
        when 0 then
        to_char(scount - dcount)
        else
          '' || to_char((scount - dcount)) || ''
      end diff,
      sowner ,
      stable ,
      scount ,
      downer ,
      dtable ,
      dcount 
  from sys.t_check_log t
where logtime > trunc(sysdate)
order by id;

3、通过SQL*PLUS向shell传递变量

参考资料:http://www.eygle.com/archives/2005/04/eciosqlplusieos.html

案例参考一:

[oracle@jumper oracle]$ cat a.sh

sqlplus -S "/ as sysdba" << !
set heading off
col today noprint
column today new_val dat
select to_char( sysdate, 'yyyy-mm-dd') today from dual;
host echo 'today is ' &dat
exit;
exit;
!

执行结果:

[oracle@jumper oracle]$ ./a.sh
 
today is  2005-04-11

案例参考二:
itpub上的Toms_zhang朋友提供了另外一种方法:

[oracle@jumper oracle]$ more a.sh

#!/bin/ksh
VALUE=`sqlplus -silent "/ as sysdba" < < END
set pagesize 0 feedback off verify off heading off echo off
select max(sequence#) from v\\\$log_history;
exit;
END`
 
if [ -z "$VALUE" ]; then
echo "No rows returned from database"
exit 0
else
echo "Max Sequence Number: $VALUE"
fi

执行结果:

[oracle@jumper oracle]$ ./a.sh
Max Sequence Number:        17

4、sqlplus常用设置

  • a.执行一个SQL脚本文件
 SQL>start file_name 
 SQL>@ file_name
  • b.将显示的内容输出到指定文件
 SQL> SPOOL file_name
  • c.关闭spool输出
SQL>SPOOL OFF
 只有关闭spool输出,才会在输出文件中看到输出的内容。
  • d.改变列的显示长度.
 字符型改变:
 SQL>col colname format a40
 数值型改变:
 SQL> col colname format 99999.99
  • e.是否显示脚本中正在执行的SQL语句.
SQL>SET ECHO {ON|OFF}
  • f.是否显示当前sql语句查询或修改的行数.
SQL> SET FEED[BACK] {6|n|ON|OFF} 
 默认只有结果大于6行时才显示结果的行数。如果set feedback 1,则不管查询到多少行都返回。当为off 时,一律不显示查询的行数
  • g.是否显示列标题
SQL> SET HEA[DING] {ON|OFF} 
 当set heading off 时,在每页的上面不显示列标题,而是以空白行代替
  • h.设置一行可以容纳的字符数
SQL> SET LIN[ESIZE] {80|n} 
  如果一行的输出内容大于设置的一行可容纳的字符数,则折行显示。
  • i.设置页与页之间的分隔
 SQL> SET NEWP[AGE] {1|n|NONE} 
 当set newpage 0 时,会在每页的开头有一个小的黑方框。 
 当set newpage n 时,会在页和页之间隔着n个空行。 
 当set newpage none 时,会在页和页之间没有任何间隔。
  • j.设置一页有多少行数
SQL> SET PAGES[IZE] {24|n} 
 如果设为0,则所有的输出内容为一页并且不显示列标题
  • k.是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息
SQL> SET SERVEROUT[PUT] {ON|OFF}
 这个在shell脚本中经常使用,默认buffer是256个字节。
 当输出的大于256时,可以使用SET SERVEROUT on size 1000000;
 最大buffer的缓冲区为1000000(9i and 8i)。
  • l.当SQL语句的长度大于LINESIZE时,是否在显示时截取SQL语句
SQL> SET WRA[P] {ON|OFF} 
 当输出的行的长度大于设置的行的长度时(用set linesize n命令设置),
 当set wrap on时,输出行的多于的字符会另起一行显示,否则,
 会将输出行的多于字符切除,不予显示。
  • m.是否在屏幕上显示输出的内容,主要用与SPOOL结合使用
SQL> SET TERM[OUT] {ON|OFF} 
 在用spool命令将一个大表中的内容输出到一个文件中时,
 将内容输出在屏幕上会耗费大量的时间,设置set termspool off后,
 则输出的内容只会保存在输出文件中,不会显示在屏幕上,
 极大的提高了spool的速度。
  • n.将SPOOL输出中每行后面多余的空格去掉
SQL> SET TRIMS[OUT] {ON|OFF}
  • o.显示每个sql语句花费的执行时间
SQL>set TIMING  {ON|OFF}

你可能感兴趣的