SQL*PLUS命令的使用大全

　　Oracle的sql*plus是與oracle進行交互的客戶端工具在sql*plus中可以運行sql*plus命令與sql*plus語句
   我們通常所說的DMLDDLDCL語句都是sql*plus語句它們執行完後都可以保存在一個被稱為sql buffer的內存區域中並且只能保存一條最近執行的sql語句我們可以對保存在sql buffer中的sql 語句進行修改然後再次執行sql*plus一般都與數據庫打交道
   除了sql*plus語句在sql*plus中執行的其它語句我們稱之為sql*plus命令它們執行完後不保存在sql buffer的內存區域中它們一般用來對輸出的結果進行格式化顯示以便於制作報表
   下面就介紹一下一些常用的sql*plus命令
 
執行一個SQL腳本文件
SQL>start file_name
SQL>@ file_name
我們可以將多條sql語句保存在一個文本文件中這樣當要執行這個文件中的所有的sql語句時用上面的任一命令即可這類似於dos中的批處理

　　@與@@的區別是什麼？
@等於start命令用來運行一個sql腳本文件
@命令調用當前目錄下的或指定全路徑或可以通過SQLPATH環境變量搜尋到的腳本文件該命令使用是一般要指定要執行的文件的全路徑否則從缺省路徑(可用SQLPATH變量指定)下讀取指定的文件
@@用在sql腳本文件中用來說明用@@執行的sql腳本文件與@@所在的文件在同一目錄下而不用指定要執行sql腳本文件的全路徑也不是從SQLPATH環境變量指定的路徑中尋找sql腳本文件該命令一般用在腳本文件中
如在c:\temp目錄下有文件startsql和nest_startsqlstartsql腳本文件的內容為
@@nest_startsql     相當於@ c:\temp\nest_startsql
則我們在sql*plus中這樣執行
SQL> @ c:\temp\startsql

　　 對當前的輸入進行編輯
SQL>edit
 
重新運行上一次運行的sql語句
SQL>/
 
將顯示的內容輸出到指定文件
SQL> SPOOL file_name
   在屏幕上的所有內容都包含在該文件中包括你輸入的sql語句
 
關閉spool輸出
SQL> SPOOL OFF
   只有關閉spool輸出才會在輸出文件中看到輸出的內容
 
．顯示一個表的結構
SQL> desc table_name
 
COL命令
主要格式化列的顯示形式
該命令有許多選項具體如下
COL[UMN] [{ column|expr} [ option ]]
Option選項可以是如下的子句:
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]
 
) 改變缺省的列標題
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
     DEPTNO DNAME                        LOC

         ACCOUNTING                   NEW YORK
sql>col  LOC heading location
sql>select * from dept;
    DEPTNO DNAME                        location

        ACCOUNTING                   NEW YORK
 
) 將列名ENAME改為新列名EMPLOYEE NAME並將新列名放在兩行上
Sql>select * from emp
Department  name           Salary

         aaa                        
SQL> COLUMN ENAME HEADING Employee|Name
Sql>select * from emp
            Employee
Department  name           Salary
 
         aaa               
note: the col heading turn into two lines from one line
 
) 改變列的顯示長度
FOR[MAT] format
Sql>select empnoenamejob from emp;
      EMPNO ENAME      JOB       
    
       SMITH      CLERK     
       ALLEN      SALESMAN  
WARD       SALESMAN  
Sql> col ename format a
      EMPNO ENAME                                    JOB
           
       SMITH                                    CLERK
       ALLEN                                    SALESMAN
       WARD                                    SALESMAN
 
) 設置列標題的對齊方式
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL> col ename justify center
SQL> /
      EMPNO           ENAME                   JOB
         
       SMITH                                    CLERK
       ALLEN                                    SALESMAN
WARD                                     SALESMAN
對於NUMBER型的列列標題缺省在右邊其它類型的列標題缺省在左邊
 
) 不讓一個列顯示在屏幕上
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
      EMPNO           ENAME
    
       SMITH
       ALLEN
WARD
 
) 格式化NUMBER類型列的顯示
SQL> COLUMN SAL FORMAT $
SQL> /
Employee
Department Name        Salary    Commission

          ALLEN        $   
 
) 顯示列值時如果列值為NULL值用text值代替NULL值
COMM NUL[L] text
SQL>COL COMM NUL[L] text
 
) 設置一個列的回繞方式
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
        COL

HOW ARE YOU?
 
SQL>COL COL FORMAT A
SQL>COL COL WRAPPED
COL

HOW A
RE YO
U?
 
SQL> COL COL WORD_WRAPPED
COL

HOW
ARE
YOU?
 
SQL> COL COL WORD_WRAPPED
COL

HOW A
 
) 顯示列的當前的顯示屬性值
SQL> COLUMN column_name
 
) 將所有列的顯示屬性設為缺省值
SQL> CLEAR COLUMNS
 
屏蔽掉一個列中顯示的相同的值
BREAK ON break_column
SQL> BREAK ON DEPTNO
SQL> SELECT DEPTNO ENAME SAL
FROM EMP
  WHERE SAL <
  ORDER BY DEPTNO;
DEPTNO      ENAME         SAL

           CLARK       
MILLER     
            SMITH      
ADAMS      
 
在上面屏蔽掉一個列中顯示的相同的值的顯示中每當列值變化時在值變化之前插入n個空行
BREAK ON break_column SKIP n
 
SQL> BREAK ON DEPTNO SKIP
SQL> /
DEPTNO ENAME SAL

CLARK
MILLER
 
SMITH
ADAMS
 
顯示對BREAK的設置
SQL> BREAK
 
刪除的設置
SQL> CLEAR BREAKS
 
Set 命令
該命令包含許多子命令
SET system_variable value
system_variable value 可以是如下的子句之一
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {|c}
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V|V|NATIVE}
CON[CAT] {|c|ON|OFF}
COPYC[OMMIT] {|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EDITF[ILE] file_name[ext]
EMB[EDDED] {ON|OFF}
ESC[APE] {\|c|ON|OFF}
FEED[BACK] {|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {|n}
LOBOF[FSET] {n|}
LOGSOURCE [pathname]
LONG {|n}
LONGC[HUNKSIZE] {|n}
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL
{ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {|n}
PAGES[IZE] {|n}
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIMI[NG] {ON|OFF}
TRIM[OUT] {ON|OFF}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}
 
) 設置當前session是否對修改的數據進行自動提交
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
 
)．在用start命令執行一個sql腳本時是否顯示腳本中正在執行的SQL語句
SQL> SET ECHO {ON|OFF}
 
)是否顯示當前sql語句查詢或修改的行數
SQL> SET FEED[BACK] {|n|ON|OFF}
   默認只有結果大於行時才顯示結果的行數如果set feedback 則不管查詢到多少行都返回當為off 時一律不顯示查詢的行數
 
)是否顯示列標題
SQL> SET HEA[DING] {ON|OFF}
當set heading off 時在每頁的上面不顯示列標題而是以空白行代替
 
)設置一行可以容納的字符數
SQL> SET LIN[ESIZE] {|n}
   如果一行的輸出內容大於設置的一行可容納的字符數則折行顯示
 
)設置頁與頁之間的分隔
SQL> SET NEWP[AGE] {|n|NONE}
當set newpage 時會在每頁的開頭有一個小的黑方框
當set newpage n 時會在頁和頁之間隔著n個空行
當set newpage none 時會在頁和頁之間沒有任何間隔
 
)顯示時用text值代替NULL值
SQL> SET NULL text
 
)設置一頁有多少行數
SQL> SET PAGES[IZE] {|n}
如果設為則所有的輸出內容為一頁並且不顯示列標題
 
)是否顯示用DBMS_OUTPUTPUT_LINE包進行輸出的信息
SQL> SET SERVEROUT[PUT] {ON|OFF} 
在編寫存儲過程時我們有時會用dbms_outputput_line將必要的信息輸出以便對存儲過程進行調試只有將serveroutput變量設為on後信息才能顯示在屏幕上
 
)當SQL語句的長度大於LINESIZE時是否在顯示時截取SQL語句
SQL> SET WRA[P] {ON|OFF}
   當輸出的行的長度大於設置的行的長度時（用set linesize n命令設置）當set wrap on時輸出行的多於的字符會另起一行顯示否則會將輸出行的多於字符切除不予顯示
 
)是否在屏幕上顯示輸出的內容主要用與SPOOL結合使用
SQL> SET TERM[OUT] {ON|OFF}
   在用spool命令將一個大表中的內容輸出到一個文件中時將內容輸出在屏幕上會耗費大量的時間設置set termspool off後則輸出的內容只會保存在輸出文件中不會顯示在屏幕上極大的提高了spool的速度
 
)將SPOOL輸出中每行後面多余的空格去掉
SQL> SET TRIMS[OUT] {ON|OFF} 
   
)顯示每個sql語句花費的執行時間
set TIMING  {ON|OFF}

　　) 遇到空行時不認為語句已經結束從後續行接著讀入
SET SQLBLANKLINES ON
Sql*plus中 不允許sql語句中間有空行 這在從其它地方拷貝腳本到sql*plus中執行時很麻煩 比如下面的腳本:
select deptno empno ename
from emp

　　where empno = ;
如果拷貝到sql*plus中執行 就會出現錯誤這個命令可以解決該問題

　　)設置DBMS_OUTPUT的輸出
SET SERVEROUTPUT ON BUFFER
用dbms_outputput_line(strin_content);可以在存儲過程中輸出信息對存儲過程進行調試
如果想讓dbms_outputput_line(     abc);的輸出顯示為
SQL>     abc而不是SQL>abc則在SET SERVEROUTPUT ON後加format wrapped參數

　　) 輸出的數據為html格式
set markup html
在版本(也許是? 不太確定)以後 sql*plus中有一個set markup html的命令 可以將sql*plus的輸出以html格式展現
注意其中的spool on 當在屏幕上輸出的時候 我們看不出與不加spool on有什麼區別 但是當我們使用spool filename 輸出到文件的時候 會看到spool文件中出現了等tag

　　．修改sql buffer中的當前行中第一個出現的字符串
C[HANGE] /old_value/new_value
SQL> l
   * select * from dept
SQL> c/dept/emp
   * select * from emp
 
．編輯sql buffer中的sql語句
EDI[T]
 
．顯示sql buffer中的sql語句list n顯示sql buffer中的第n行並使第n行成為當前行
L[IST] [n]
 
．在sql buffer的當前行下面加一行或多行
I[NPUT]
 
．將指定的文本加到sql buffer的當前行後面
A[PPEND]
SQL> select deptno
     dname
     from dept;
     DEPTNO DNAME

         ACCOUNTING
         RESEARCH
         SALES
         OPERATIONS
 
SQL> L
   * dname
SQL> a loc
   * dnameloc
SQL> L
     select deptno
     dnameloc
   * from dept
SQL> /
 
     DEPTNO DNAME          LOC

         ACCOUNTING     NEW YORK
         RESEARCH       DALLAS
         SALES          CHICAGO
         OPERATIONS     BOSTON
 
．將sql buffer中的sql語句保存到一個文件中
SAVE file_name
 
．將一個文件中的sql語句導入到sql buffer中
GET file_name
 
．再次執行剛才已經執行的sql語句
RUN
or
/
 
．執行一個存儲過程
EXECUTE procedure_name
 
．在sql*plus中連接到指定的數據庫
CONNECT user_name/passwd@db_alias
 
．設置每個報表的頂部標題
TTITLE
 
．設置每個報表的尾部標題
BTITLE
 
．寫一個注釋
REMARK [text]
 
．將指定的信息或一個空行輸出到屏幕上
PROMPT [text]
 
．將執行的過程暫停等待用戶響應後繼續執行
PAUSE [text]
 
Sql>PAUSE Adjust paper and press RETURN to continue
 
．將一個數據庫中的一些數據拷貝到另外一個數據庫（如將一個表的數據拷貝到另一個數據庫）
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column column column )] USING query
 
sql>COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST 
create emp_temp
USING SELECT * FROM EMP
 
．不退出sql*plus在sql*plus中執行一個操作系統命令
HOST
 
Sql> host hostname
該命令在windows下可能被支持
 
．在sql*plus中切換到操作系統命令提示符下運行操作系統命令後可以再次切換回sql*plus
!
 
sql>!
$hostname
$exit
sql>
 
該命令在windows下不被支持
 
．顯示sql*plus命令的幫助
HELP
如何安裝幫助文件
Sql>@ ?\sqlplus\admin\help\hlpbldsql ?\sqlplus\admin\help\helpussql
Sql>help index
 
．顯示sql*plus系統變量的值或sql*plus環境變量的值
Syntax
SHO[W] option
where option represents one of the following terms or clauses:
system_variable
ALL
BTI[TLE]
ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY|
TRIGGER|VIEW|TYPE|TYPE BODY} [schema]name]
LNO
PARAMETERS [parameter_name]
PNO
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SQLCODE
TTI[TLE]
USER
 
) 顯示當前環境變量的值
Show all
 
) 顯示當前在創建函數存儲過程觸發器包等對象的錯誤信息
Show error
當創建一個函數存儲過程等出錯時變可以用該命令查看在那個地方出錯及相應的出錯信息進行修改後再次進行編譯
 
) 顯示初始化參數的值
show PARAMETERS [parameter_name]
 
) 顯示數據庫的版本
show REL[EASE]
 
) 顯示SGA的大小
show SGA
 
) 顯示當前的用戶名
show user

　　查詢一個用戶下的對象
SQL>select * from tab;
SQL>select * from user_objects;

　　查詢一個用戶下的所有的表
SQL>select * from user_tables;

　　查詢一個用戶下的所有的索引
SQL>select * from user_indexes;

　　 定義一個用戶變量
方法有兩個
a define
b COL[UMN] [{column|expr} NEW_V[ALUE] variable [NOPRI[NT]|PRI[NT]]
                            OLD_V[ALUE] variable  [NOPRI[NT]|PRI[NT]]

　　下面對每種方式給予解釋
a Syntax
DEF[INE] [variable]|[variable = text]
定義一個用戶變量並且可以分配給它一個CHAR值

　　assign the value MANAGER to the variable POS type:
SQL> DEFINE POS = MANAGER

　　assign the CHAR value to the variable DEPTNO type:
SQL> DEFINE DEPTNO =

　　list the definition of DEPTNO enter
SQL> DEFINE DEPTNO
        ―――――――――――――――
DEFINE DEPTNO = (CHAR)

　　定義了用戶變量POS後就可以在sql*plus中用&POS或&&POS來引用該變量的值sql*plus不會再提示你給變量輸入值

　　b COL[UMN] [{column|expr} NEW_V[ALUE] variable [NOPRI[NT]|PRI[NT]]
NEW_V[ALUE] variable
指定一個變量容納查詢出的列值
例:column col_name new_value var_name noprint
   select col_name from table_name where ……
將下面查詢出的col_name列的值賦給var_name變量

　　一個綜合的例子:
得到一個列值的兩次查詢之差(此例為秒之內共提交了多少事務):
column redo_writes new_value commit_count

　　select sum(statvalue) redo_writes
from v$sesstat stat v$statname sn
where statstatistic# = snstatistic#
and snname = user commits;

　　 等待一會兒(此處為秒);
execute dbms_locksleep();

　　set veri off
select sum(statvalue) &commit_count commits_added
from v$sesstat stat v$statname sn
where statstatistic# = snstatistic#
and snname = user commits;

　　 定義一個綁定變量
VAR[IABLE] [variable [NUMBER|CHAR|CHAR (n)|NCHAR|NCHAR (n) |VARCHAR (n)|NVARCHAR (n)|CLOB|NCLOB|REFCURSOR]]
定義一個綁定變量該變量可以在pl/sql中引用
可以用print命令顯示該綁定變量的信息
如
column inst_num  heading Inst Num  new_value inst_num  format ;
column inst_name heading Instance  new_value inst_name format a;
column db_name   heading DB Name   new_value db_name   format a;
column dbid      heading DB Id     new_value dbid      format just c;

　　prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~

　　select ddbid            dbid
     dname            db_name
     iinstance_number inst_num
     iinstance_name   inst_name
  from v$database d
       v$instance i;

　　variable dbid       number;
variable inst_num   number;
begin
  :dbid      :=  &dbid;
  :inst_num  :=  &inst_num;
end;
/
說明
在sql*plus中該綁定變量可以作為一個存儲過程的參數也可以在匿名PL/SQL塊中直接引用為了顯示用VARIABLE命令創建的綁定變量的值可以用print命令

　　注意
綁定變量不同於變量
．        定義方法不同
．        引用方法不同
綁定變量:variable_name
        變量&variable_name or &&variable_name
在sql*plus中可以定義同名的綁定變量與用戶變量但是引用的方法不同

　　 &與&&的區別
&用來創建一個臨時變量每當遇到這個臨時變量時都會提示你輸入一個值
&&用來創建一個持久變量就像用用define命令或帶new_vlaue字句的column命令創建的持久變量一樣當用&&命令引用這個變量時不會每次遇到該變量就提示用戶鍵入值而只是在第一次遇到時提示一次

　　如將下面三行語句存為一個腳本文件運行該腳本文件會提示三次讓輸入deptnoval的值
select count(*) from emp where deptno = &deptnoval;
select count(*) from emp where deptno = &deptnoval;
select count(*) from emp where deptno = &deptnoval;

　　將下面三行語句存為一個腳本文件運行該腳本文件則只會提示一次讓輸入deptnoval的值
select count(*) from emp where deptno = &&deptnoval;
select count(*) from emp where deptno = &&deptnoval;
select count(*) from emp where deptno = &&deptnoval;

　　．在輸入sql語句的過程中臨時先運行一個sql*plus命令(摘自)
#
有沒有過這樣的經歷? 在sql*plus中敲了很長的命令後 突然發現想不起某個列的名字了 如果取消當前的命令待查詢後再重敲 那太痛苦了 當然你可以另開一個sql*plus窗口進行查詢 但這裡提供的方法更簡單

　　比如說 你想查工資大於的員工的信息 輸入了下面的語句:

　　SQL> select deptno empno ename
from emp
where
這時 你發現你想不起來工資的列名是什麼了

　　這種情況下 只要在下一行以#開頭 就可以執行一條sql*plus命令 執行完後 剛才的語句可以繼續輸入

　　SQL>> select deptno empno ename
from emp
where
#desc emp
Name Null? Type

EMPNO NOT NULL NUMBER()
ENAME VARCHAR()
JOB VARCHAR()
MGR NUMBER()
HIREDATE DATE
SAL NUMBER()
COMM NUMBER()
DEPTNO NUMBER()

　　 sal > ;

　　DEPTNO EMPNO ENAME

KING

　　 SQLPlus中的快速復制和粘貼技巧(摘自)
) 鼠標移至想要復制內容的開始
) 用右手食指按下鼠標左鍵
) 向想要復制內容的另一角拖動鼠標與Word中選取內容的方法一樣
) 內容選取完畢後（所選內容全部反顯）鼠標左鍵按住不動用右手中指按鼠標右鍵
) 這時所選內容會自動復制到SQL*Plus環境的最後一行

From:http://tw.wingwit.com/Article/program/Oracle/201311/18384.html