SQL*PLUS命令的使用大全

 

     Oracle的sql*plus是與oracle進行互動的客戶端工具。在sql*plus中，可以執行 sql*plus命令與sql*plus語句。  

   我們通常所說的DML、DDL、DCL語句都是sql*plus語句，它們執行完後，都可以儲存在一個被稱為sql buffer的記憶體區域中，並且只能儲存一條 近執行的sql語句，我們可以對儲存在sql buffer中的sql 語句進行修改，然後再次執行，sql*plus一般都與資料庫打交道。  

   除了sql*plus語句，在sql*plus中執行的其它語句我們稱之為sql*plus命令。它們執行完後，不儲存在sql buffer的記憶體區域中，它們一般用來對輸出的結果進行格式化顯示，以便於製作報表。  

   下面就介紹一下一些常用的sql*plus命令：  

1. 執行一個SQL指令碼檔案  

SQL>start file_name

SQL>@ file_name  

我們可以將多條 sql 語句儲存在一個文字檔案中，這樣當要執行這個檔案中的所有的 sql 語句時，用上面的任一命令即可，這類似於 dos 中的批處理。  

 

@與@@的區別是什麼？ 

@等於 start 命令，用來執行一個 sql 指令碼檔案。 

@命令呼叫當前目錄下的，或指定全路徑，或可以透過 SQLPATH 環境變數搜尋到的指令碼檔案。該命令使用是一般要指定要執行的檔案的全路徑，否則從預設路徑(可用 SQLPATH 變數指定)下讀取指定的檔案。 

@@用在 sql 指令碼檔案中，用來說明用@@執行的 sql 指令碼檔案與@@所在的檔案在同一目錄下，而不用指定要執行 sql 指令碼檔案的全路徑，也不是從 SQLPATH 環境變數指定的路徑中尋找 sql 指令碼檔案，該命令一般用在指令碼檔案中。 

如：在 c:\temp 目錄下有檔案 start.sql 和 nest_start.sql，start.sql 指令碼檔案的內容為： 

@@nest_start.sql     - - 相當於@ c:\temp\nest_start.sql 則我們在 sql*plus 中，這樣執行： 

SQL> @ c:\temp\start.sql  

2. 對當前的輸入進行編輯 

SQL>edit  

   

3. 重新執行上一次執行的sql語句 

SQL>/  

   

4. 將顯示的內容輸出到指定檔案  

SQL> SPOOL file_name  

   在螢幕上的所有內容都包含在該檔案中，包括你輸入的 sql 語句。  

   

5. 關閉spool輸出  

SQL> SPOOL OFF  

   只有關閉 spool 輸出，才會在輸出檔案中看到輸出的內容。  

   

6．顯示一個表的結構  

SQL> desc table_name     

7. 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]  

   

1). 改變預設的列標題  

COLUMN column_name HEADING column_heading  For example:  

Sql>select * from dept;  

     DEPTNO DNAME                        LOC  

---------- ---------------------------- ---------  

        10 ACCOUNTING                   NEW YORK  sql>col  LOC heading location  

sql>select * from dept;  

    DEPTNO DNAME                        location  

--------- ---------------------------- -----------  

        10 ACCOUNTING                   NEW YORK  

   

2). 將列名ENAME改為新列名EMPLOYEE NAME並將新列名放在兩行上：  

Sql>select * from emp  

Department  name           Salary  

---------- ---------- ----------  

         10 aaa                11          

SQL> COLUMN ENAME HEADING ’Employee|Name’  

Sql>select * from emp  

           Employee  

Department  Name           Salary  

---------- ---------- ----------   

         10 aaa                11  

note: the col heading turn into two lines from one line.     

3). 改變列的顯示長度：  

FOR[MAT] format  

Sql>select empno,ename,job from emp;  

      EMPNO ENAME      JOB         

---------- ----------     ---------  

       7369 SMITH      CLERK       

       7499 ALLEN      SALESMAN    

7521 WARD       SALESMAN    

Sql> col ename format a40  

      EMPNO ENAME                                    JOB  

----------   ----------------------------------------         ---------  

       7369 SMITH                                    CLERK  

       7499 ALLEN                                    SALESMAN  

       7521 WARD                                    SALESMAN  

   

4). 設定列標題的對齊方式  

JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}  

SQL> col ename justify center  

SQL> /  

      EMPNO           ENAME                   JOB  

----------   ----------------------------------------       ---------  

       7369 SMITH                                    CLERK  

       7499 ALLEN                                    SALESMAN  

7521 WARD                                     SALESMAN  

對於 NUMBER 型的列，列標題預設在右邊，其它型別的列標題預設在左邊  

   

5). 不讓一個列顯示在螢幕上  

NOPRI[NT]|PRI[NT]  

SQL> col job noprint  

SQL> /  

      EMPNO           ENAME  

----------     ----------------------------------------  

       7369 SMITH  

       7499 ALLEN  

7521 WARD  

   

6). 格式化NUMBER型別列的顯示：  

SQL> COLUMN SAL FORMAT $99,990  

SQL> /  

Employee  

Department Name        Salary    Commission  

---------- ---------- --------- ----------  

30          ALLEN        $1,600    300     

7). 顯示列值時，如果列值為NULL值，用text值代替NULL值  

COMM NUL[L] text  

SQL>COL COMM NUL[L] text  

   

8). 設定一個列的迴繞方式  

WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]  

        COL1  

--------------------  

HOW ARE YOU?  

   

SQL>COL COL1 FORMAT A5  

SQL>COL COL1 WRAPPED  

COL1  

-----  

HOW A  RE YO  U?  

   

SQL> COL COL1 WORD_WRAPPED  

COL1  

-----  

HOW  

ARE  YOU?  

   

SQL> COL COL1 TRUNCATED  

COL1  

-----  

HOW A  

   

9). 顯示列的當前的顯示屬性值  

SQL> COLUMN column_name  

   

10). 將所有列的顯示屬性設為預設值  

SQL> CLEAR COLUMNS  

   

8. 遮蔽掉一個列中顯示的相同的值  

BREAK ON break_column  

SQL> BREAK ON DEPTNO  

SQL> SELECT DEPTNO, ENAME, SAL  

FROM EMP  

  WHERE SAL < 2500  

  ORDER BY DEPTNO;  

DEPTNO      ENAME         SAL  

---------- ----------- ---------  

10           CLARK        2450  

MILLER      1300  

20            SMITH       800  

ADAMS       1100  

   

9. 在上面遮蔽掉一個列中顯示的相同的值的顯示中，每當列值變化時在值變化之前插入n 個空行。  

BREAK ON break_column SKIP n  

   

SQL> BREAK ON DEPTNO SKIP 1  

SQL> /  

DEPTNO ENAME SAL  

---------- ----------- ---------  

10 CLARK 2450  

MILLER 1300  

   

20 SMITH 800  

ADAMS 1100  

   

10. 顯示對BREAK的設定  

SQL> BREAK  

   

11. 刪除 8、9 的設定  

SQL> CLEAR BREAKS  

   

12. Set 命令：  

該命令包含許多子命令：  

SET system_variable value  

system_variable value 可以是如下的子句之一：  

APPI[NFO]{ON|OFF|text}  

ARRAY[SIZE] {15|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]{V7|V8|NATIVE}  

CON[CAT] {.|c|ON|OFF}  

COPYC[OMMIT] {0|n}  

COPYTYPECHECK {ON|OFF}  

DEF[INE] {&|c|ON|OFF}  

DESCRIBE [DEPTH {1|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] {6|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] {80|n}  

LOBOF[FSET] {n|1}  

LOGSOURCE [pathname]  

LONG {80|n}  

LONGC[HUNKSIZE] {80|n}  

MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL  

{ON|OFF}] [PRE[FORMAT] {ON|OFF}]  

NEWP[AGE] {1|n|NONE}  

NULL text  

NUMF[ORMAT] format  

NUM[WIDTH] {10|n}  

PAGES[IZE] {24|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}  

   

1).設定當前session是否對修改的資料進行自動提交  

SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}  

   

2).在用start命令執行一個sql指令碼時，是否顯示指令碼中正在執行的SQL語句  

SQL> SET ECHO {ON|OFF}  

   

3).是否顯示當前sql語句查詢或修改的行數  

SQL> SET FEED[BACK] {6|n|ON|OFF}  

   預設只有結果大於 6 行時才顯示結果的行數。如果 set feedback 1 ，則不管查詢到多少行都返回。當為 off 時，一律不顯示查詢的行數  

   

4).是否顯示列標題  

SQL> SET HEA[DING] {ON|OFF}  

當 set heading off 時，在每頁的上面不顯示列標題，而是以空白行代替  

   

5).設定一行可以容納的字元數  

SQL> SET LIN[ESIZE] {80|n}  

   如果一行的輸出內容大於設定的一行可容納的字元數，則折行顯示。  

   

6).設定頁與頁之間的分隔  

SQL> SET NEWP[AGE] {1|n|NONE}  

當 set newpage 0 時，會在每頁的開頭有一個小的黑方框。  當 set newpage n 時，會在頁和頁之間隔著 n 個空行。  當 set newpage none 時，會在頁和頁之間沒有任何間隔。  

   

7).顯示時，用text值代替NULL值  

SQL> SET NULL text     

8).設定一頁有多少行數  

SQL> SET PAGES[IZE] {24|n}  

如果設為 0，則所有的輸出內容為一頁並且不顯示列標題  

   

9).是否顯示用DBMS_OUTPUT.PUT_LINE包進行輸出的資訊。  

SQL> SET SERVEROUT[PUT] {ON|OFF}   

在編寫儲存過程時，我們有時會用 dbms_output.put_line 將必要的資訊輸出，以便對儲存過程進行除錯，只有將 serveroutput 變數設為 on 後，資訊才能顯示在螢幕上。  

   

10).當SQL語句的長度大於LINESIZE時，是否在顯示時擷取SQL語句。  

SQL> SET WRA[P] {ON|OFF}  

   當輸出的行的長度大於設定的行的長度時（用 set linesize n 命令設定），當 set wrap on 時，輸出行的多於的字元會另起一行顯示，否則，會將輸出行的多於字元切除，不予顯示。  

   

11).是否在螢幕上顯示輸出的內容，主要用與SPOOL結合使用。  

SQL> SET TERM[OUT] {ON|OFF}  

   在用 spool 命令將一個大表中的內容輸出到一個檔案中時，將內容輸出在螢幕上會耗費大量的時間，設定 set termout off 後，則輸出的內容只會儲存在輸出檔案中，不會顯示在螢幕上，極大的提高了 spool 的速度。  

   

12).將SPOOL輸出中每行後面多餘的空格去掉  

SQL> SET TRIMS[POOL] {ON|OFF}        

13)顯示每個sql語句花費的執行時間  

set TIMING  {ON|OFF}  

 

14). 遇到空行時不認為語句已經結束，從後續行接著讀入。

SET SQLBLANKLINES ON

Sql*plus 中, 不允許 sql 語句中間有空行, 這在從其它地方複製指令碼到 sql*plus 中執行時很麻煩. 比如下面的指令碼:

select deptno, empno, ename

from emp

 

where empno = '7788';

如果複製到 sql*plus 中執行, 就會出現錯誤。這個命令可以解決該問題 

 

15).設定DBMS_OUTPUT的輸出

SET SERVEROUTPUT ON BUFFER 20000

用 dbms_output.put_line('strin_content');可以在儲存過程中輸出資訊，對儲存過程進行除錯如果想讓 dbms_output.put_line('     abc');的輸出顯示為： 

SQL>     abc，而不是 SQL>abc，則在 SET SERVEROUTPUT ON 後加 format wrapped 引數。 

 

16). 輸出的資料為html格式

set markup html

在 8.1.7 版本(也許是 816? 不太確定)以後, sql*plus 中有一個 set markup html 的命令, 可以將 sql*plus 的輸出以 html 格式展現.

注意其中的spool on, 當在螢幕上輸出的時候, 我們看不出與不加spool on有什麼區別, 但是當我們使用 spool filename 輸出到檔案的時候, 會看到 spool 檔案中出現了等 tag.

 

 

14．修改sql buffer中的當前行中，第一個出現的字串  

C[HANGE] /old_value/new_value  

SQL> l  

   1* select * from dept  SQL> c/dept/emp  

   1* select * from emp     

15．編輯sql buffer中的sql語句  

EDI[T]     

16．顯示sql buffer中的sql語句，list n顯示sql buffer中的第n行，並使第n行成為當前行  

L[IST] [n]  

   

17．在sql buffer的當前行下面加一行或多行  

I[NPUT]     

18．將指定的文字加到sql buffer的當前行後面  

A[PPEND]  

SQL> select deptno,  

2 dname  

3 from dept;  

     DEPTNO DNAME  

---------- --------------  

         10 ACCOUNTING  

         20 RESEARCH  

         30 SALES  

         40 OPERATIONS