Oracle sqlplus 常用設定

標籤：

Oracle sqlplus 常用設定§2.5 SQLPLUS常用設定

使用過程中大都需要進行必要的環境設定才能完成我們所需要的輸出。

所有環境的設定由SET命令加相應的環境變數來完成。下面是常用的環境設定：

§2.5.1   ARRAYSIZE（取回的行數）
SET ARRAY[SIZE]{integer}

一次可以提取（Fetch）的行的數目，1->5000，當有較長欄位時應設小些。


§2.5.2   AUTOCOMMIT(自動認可)
SET AUTO [COMMIT] { [ OFF | ON | IMM | n] }
用於在操作中是自動認可或是部分提交或是不自動認可。
1） 或IMM使得在完成每條SQL語句時將未提交的改變立刻提交給資料庫系統。ON
2） 允許在使用者發出COMMIT後，可以執行命令的數量（將n條SQL語句所做的改變進行提交）。N
3） 停止自動認可，使用者必須用COMMIT命令才能被提交。OFF

§2.5.3   LINESIZE(行顯示寬度)
可以設定LINESIZE環境變數來控制行的顯示寬度，預設是80個字元。
SET    Lin[esize]{80|integer}
Integer =設定行寬度(字元個數)，最大值999，如：
SQL>set linesize 160

§2.5.4   LONG(長類型顯示位元組數)
在預設的SQL> 狀態下，SQL>緩衝區用於顯示LONG 的位元組數只有80個字元。如果我們需要查詢的列中含有 LONG類型的欄位的話，就需要將LONG緩衝區設定大些。
SET LONG{80|integer}
Integer是 顯示或拷貝long值的最大寬度， n=1->32767（但必須小於Maxdata值）
SQL>show Maxdata （最大行寬）
SQL>set long 2000

§2.5.5   PAGESIZE(頁行數)
在預設的SQL> 狀態下，SQL>緩衝區顯示頁的行數是24行，其中22行顯示資料，2行顯示標題和橫線。我們將pagesize 設定大些以減少提示標題和橫線。
SET pag[esize]   {24|integer}
SQL>SET pagesize 66

§2.5.6   PAUSE(暫停)
可以設定 PAUSE 為ON 或OFF來控制螢幕顯示。當設定為ON 時，在select 語句發出後需要按Enter鍵才能顯示一屏。
SET PAUSE [ ON | OFF ]
SQL> setpause on

提示：在發出select 語句並按Enter鍵後還要再按Enter鍵才能顯示結果.

§2.5.7   SPACE(列間空格)
可用 set space 來設定各列間的空格數，文法為：
SET   SPA[CE]    {1|n}
N為設定輸出行列間的空格數，最大為10。
SQL>set space 2

建議：在一般情況下，不用設定space參數。

§2.5.8   Termout (啟/停螢幕顯示)
TERMOUT用於設定在螢幕上顯示或不顯示所輸出的資訊。
SET TERMOUT { ON | OFF }

set    termout off
set    termout on

set   termout off  常用SPOOL XXX時，即關閉報表在螢幕上的顯示（節省時間）
set   termout on   常用SPOOL   off之後，即恢複報表在螢幕上的顯示

§2.5.9   ECHO (啟/停命令顯示)
可以用ECHO命令來顯示或不顯示所執行的SQL命令。文法如：
SET   ECHO{OFF|ON}
顯示執行當中的各命令（ 即用start 時）
set   echo   受到   set   termout 的影響

set pagesize 100
set echo on
select table_name from dict where rownum<20;
select * from cat where rownum<30;
set echo off
--下面只顯示結果不顯示命令：
select table_name from dict where rownum<20;
select * from cat where rownum<30;

§2.5.10 TRANSACTION (啟動事務)
一個很重要的事務環境設定是TRANSACTION。它包括兩個部分的內容：
SET TRANSACTION { READ ONLY | USE ROLLBACK SEGMENT segment_name }
READ ONLY是用於保證讀的一致性。即其他使用者的修改不影響當前查詢結果。
USE ROLLBACK SEGMENT segment_name是為當前所處理的事務指定專門的復原段。這主要是在進行大量的Insert或Delete或Update時，需要一個大的復原段以保證事務正常完成。詳細見資料庫管理員。

§2.5.11 SHOW ALL(列出所有參數)
可以用SHOW ALL來顯示當前的所有參數情況。它的用法很簡單。比如：

SQL> show all
appinfo為ON並且已設定為"SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and為下一條SELECT語句的前幾個字元
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK為ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
markup HTML OFF SPOOL OFF ENTMAP ON PREFORMAT OFF
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE為OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 801070000
repfooter OFF and為NULL
repheader OFF and為NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF and為下一條SELECT語句的前幾個字元
underline "-" (hex 2d)
USER為"SYS"
verify ON
wrap :行將為已換行
SQL>
你可以從上面的參數中看到其當前值，也可以修改某些參數的值。
格式化輸出
§2.6.1  一般資料的格式化輸出

在Oracle的SQL>下，經常用COLUMN命令來對所輸出的列進行格式化，即按照一定的格式進行顯示。COLMUN命令文法如下：

COL[UMN] [{ column | expr } [ option_1 ... option_n ] ]

column：列名
expr：有效SQL運算式
option_1...option_n：可以是下列之一：
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE {expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]

下面給出常用的關鍵字的解釋：
Alias    給出列的別名，BREAK和COUMN可以引用所定義的別名。
CLEAR 取消列的定義。
FORMAT列顯示格式，format為：
9999990     9或0的個數決定最多顯示多少位
9,999,999.99按照逗號和小數點來顯示資料，若是0以空格顯示
099999     顯示前面補0
$999,999.99 數字前加美圓號
B99999     若為0，則結果為空白
99999Mi    若數字為負，則負號放在數字後（右邊），預設放在左邊
99999PR     負號將以括弧括起
9.999EEEE  以科學記號標記法表示（必須有4個E）
999V99     數字乘以10n ,如1234變為123400
DATE      採用日期數字格式（MM/DD/YY）


Heading重新標記列的顯示標題，如:

SQL> col ename heading 姓名format a10
SQL> select ename,sal from emp;


例子：
SQL
COLUMN  SALARY  FOR  $9,999,999.99
COLUMN  LAST_NAME  FOR  A35.

§2.6.2  日期的格式化輸出
Oracle系統提供了一個NLS_DATE_FORMAT的環境變數來設定日期的顯示格式。用它可以完成按照不同格式要求的顯示，比如按照中國的習慣為yyyy年mm月dd日等。

1.系統日期 sysdate 的顯示
用sysdate 可以顯示ORACLE RDBMS 所在機器的日期及時間，如：
SQL> alter session set nls_date_format =‘"公元"yyyy"年"mm"月"dd"日"‘;

會話已更改。

SQL> select sysdate from dual;

SYSDATE
------------------
公元2001年05月30日


2.日期類型的顯示

select sysdate,to_char(sysdate,’yyyy.mm.dd hh24:mi;ss’) from dual;

SQL> connect scott/tiger
已串連。
SQL> alter session set nls_date_format =‘yyyy"年"mm"月"dd"日生"‘;

會話已更改。
SQL> col HIREDATE heading生日
SQL> col sal heading工資
SQL> col sal ename姓名
SQL> select ename,sal,hiredate from emp;

姓名            工資            生日
---------- ---------- -----------------
SMITH             800 1980年12月17日生
ALLEN            1600 1981年02月20日生
WARD             1250 1981年02月22日生
JONES            2975 1981年04月02日生
MARTIN           1250 1981年09月28日生
BLAKE            2850 1981年05月01日生
CLARK            2450 1981年06月09日生
SCOTT            3000 1987年04月19日生
KING             5000 1981年11月17日生
TURNER           1500 1981年09月08日生
ADAMS            1100 1987年05月23日生
JAMES             950 1981年12月03日生
FORD             3000 1981年12月03日生
MILLER           1300 1982年01月23日生

已選擇14行。
§2.7 加標題
有時在輸出一些結果時，可能需要加一些標題，如表上面的頂標題，落款等。這樣的要求可由Ttitle和Btitle來完成。
ttitle和btitle

ttitle   [center|left|right]string   頂標題
btitle   [center|left|right]string   底標題
ttitle   center   ‘XX公司人員情況表‘
btitle   left     ‘製表人：xxxx‘   right    ‘日期：xxxx年xx月‘
Clear    ttitle

§2.8 建立簡單報告
我們可以用TTITLE、BTITLE、COLUMN、BREAK ON、COMPUTE SUM及SET LINESIZE、SET PAGESIZE、SET NEWPAGE來設定查詢結果的顯示格式；在用SPOOL命令將顯示結果輸出到一個作業系統檔案中去，一般輸出檔案的類型為.LST。

建立簡單報告主要使用下面命令來實現：

1． SPOOL命令

SPOOL   filename     將緩衝區的內容寫到檔案中
SPOOL   off          終止寫命令

2． COLUMN  命令
column    col_name[，heading]    format     format_spe
把欄位的結果指定為一種輸出格式
COL   name   heading   ‘姓名‘   for    a10
COL    sal      heading   ‘工資‘    for    9,999.99

3.ttitle、btitle

ttitle   [center|left|right]string   頂標題
btitle   [center|left|right]string   底標題
ttitle   center   ‘XX公司人員情況表‘
btitle   left     ‘製表人：趙元傑‘   right    ‘日期：1998.11月‘
Clear    ttitle

4.break、compute

clear    breaks,clear    computes
break   on   column    在該列上中斷
break   on   row       在每一行上中斷
break   on    Page
break   on    report
skip     n              跳過n行
skip     page           跳過未用完的頁
compute       avg
compute      count
compute       max
compute       min
compute       std
compute       sum
compute       var
compute       num    計算所有行
compute       sum   of   sal   on   deptno

5.set  在報表中的設定
l      set    termout    off、set   termout   on命令
l      set   termout    off  常用SPOOL    XXX前，即關閉報表在螢幕上的顯示（節省時間）
l      set   termout    on   常用SPOOL   off之後，即恢複報表在螢幕上的顯示
l      set   ECHO{OFF|ON} 顯示執行當中的各命令（即用start   時），set   echo   受到   set   termout   的影響
l      set    Lin[esize]{80|integer}   設定行寬度，最大值999
l      set pag[esize]   {24|integer}  設定頁的大小

例子：
SQL>COL ename heading ‘姓名’ for a12
SQL>COL sal heading ‘工資’ for a999,999.99
SQL>COL hiredate heading ‘出生’
SQL>SET LINESIZE 200
SQL>SET PAGESIZE 60
SQL>SPOOL c:\all_emp
SQL>select ename,sal,deptno,hiredate from emp order by deptno;
SQL>SPOOL OFF

§2.9 輸入變數
Oracle提供一種在處理SQL語句時可以將參數作為變數來對待的技術，即在條件句中可以是變數而不是具體的值，這樣的處理就是輸入變數。這樣做的目的就是可以重複使用同樣的語句，每次只要輸入相應的值即可。要實現將參數寫成為變數，只要在變數前加一個&號即可。看下面語句：

Select sid, serial#,username, command from v$session
Where   USERNAME = upper(‘&usr’);

這樣的語句在運行中，系統會自動提示你回答變數的具體值，上面語句運行時提示和回答時顯示的資訊如下：

SQL> Select sid, serial#,username, command from v$session
2 Where   USERNAME = upper(‘&usr‘);
輸入 usr 的值: sys
原值    2: Where   USERNAME = upper(‘&usr‘)
新值    2: Where   USERNAME = upper(‘sys‘)

   SID SERIAL# USERNAME      COMMAND
---------- ---------- ------------------------------ ----------
         7      26 SYS                       3


在變數說明中，可以使用多個變數，比如：

Alter system kill session ‘&sid,&ser’;
或
Alter system kill session ‘&會話號,&序號’;

它的運行情況如下：

SQL> Select sid, serial#,username, command from v$session;

   SID SERIAL# USERNAME       COMMAND
---------- ---------- ------------------------------ ----------
         1          1                 0
         2          1                 0
         3          1                 0
         4          1                 0
         5          1                 0
         6          1                 0
         7         26 SYS             3
         8         16 ZHAO           0

已選擇8行。

SQL> Alter system kill session ‘&sid,&ser‘;
輸入sid的值: 8
輸入ser的值: 16
原值    1: Alter system kill session ‘&sid,&ser‘
新值    1: Alter system kill session ‘8,16‘

系統已更改。

一般系統預設下是使用 “&” 符號來定義變數，你也可以使用另外的符號來代替，比如不喜歡用 & 而要用 ？，則有：

SQL> set define ?
SQL> select sid,serial#,username from v$session where username=‘?usr‘;
輸入 usr 的值: SYS
原值    1: select sid,serial#,username from v$session where username=‘?usr‘
新值    1: select sid,serial#,username from v$session where username=‘SYS‘

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
7                               26 SYS

Oracle sqlplus 常用設定