Oracle 10g的使用【SQLPLUS操作大全】

                                          SQLPLUS 操作大全

Sql*plus 中使用綁定變數：
sql> variable x number;
sql> exec := 7788;
sql> SELECT empno,ename from scott.emp where empno=:x;
SQL*PLUS 是Oracle提供的一個工具程式，它不僅可以用於測試，運行SQL語句和PL/SQL塊，而且還可以用於管理Oracle資料庫，
1.啟動sql*plus
為了使用sql*plus，必須首先要啟動sql*plus。Oracle不僅提供了命令列和圖形介面的sql*plus，而且還可以在web瀏覽器中
運行.
(1)在命令運行sql*plus
在命令列運行sql*plus是使用sqlplus命令來完成的，該命令適用於任何作業系統平台，
文法如下:
   sqlplus [username]/[password][@server]
如上所示:username用於指定資料庫使用者名稱，password用於指定使用者口令，server則用於指定主機字串(網路服務名).
當串連到本機資料時，不需要提供網路服務名,如果要串連到遠端資料庫，則必須要使用網路服務名.
(2)在windows環境中運行sql*plus
如果在windows環境中安裝了oralce資料庫產品，那麼可以在視窗環境中運行sql*plus
具體方法: "開始->程式->oracle-oradb10g_home1->application development->sql*plus"
2.串連命令
(1)conn[ect]
   該命令用於串連到資料庫。注意，使用該命令建立新會話時，會自動斷開先前會話，樣本如下:
   sql>conn scott/yhai1981@demo
(2)disc[onnect]
   該命令用於斷開已經存在的資料庫連接。註：該命令只是中斷連線會話，而不會退出sql*plus，樣本如下:
   sql>disc
(3)passw[ord]
   該命令用於修改使用者的口令。注，任何使用者都可以使用該命令修改其自身口令，但如果要修改其他使用者的口令時，
則必須以DBA身份(sys和system)登入，在sql*plus中，當修改使用者口令時，可以使用該命令取代sql命令alter user,
   樣本如下:
sql>passw
更改scott的口令
舊口令:******
新口令:******
重新鍵入新口令:******
口令已更改
sql>
(4)exit
該命令用於退出 sql*plus,另外你也可以使用quit命令退出sql*plus.使用該命令不僅會中斷連線，而且也會退出sql*plus
注:預設情況下，當執行該命令時會自動認可事務。
3，編輯命令
(1)l[ist]
   該命令用於列出sql緩衝區的內容，使用該命令可以列出sql緩衝某行，某幾行或所有行的內容。在顯示結果中,資料字為具體
的行號，而"*"則表示當前行。
樣本一:列出sql緩衝區所有內容
     sql>l
樣本二:列出sql緩衝區首行內容:
     sql>l1
(2)a[ppend]
   該命令用於在sql緩衝區的當前行尾部新增內容。注:該命令將內容追加到標記為"*"的行的尾部，樣本如下:
   sql>l
     1 select empno,ename,sal,hiredate,comm,deptno
     2 from emp
     3* where deptno=10
   sql>a and job='CLERK'
   sql>l
   SQL> list
    1 select empno,ename,sal,hiredate,comm,deptno
    2 from emp
    3* where deptno=10 and job='CLERK'
(3)c[hange]
   該命令用於修改sql緩衝區的內容。如果在編寫sql語句時寫錯了某個詞，那麼使用該命令可以進行修改，
    sql>select ename from temp where deptno=10;
    SQL> c /temp/emp
      1* select ename from emp where deptno=10
(4)del
   該命令用於刪除sql緩衝區中內容，使用它可以刪除某行，某幾行或所有行，在預設情況下，當直接執行
del時，只刪除當前行的內容，樣本如下:
   SQL> l
   1 select ename
   2 from emp
   3* where deptno=20
   sql>del
   SQL> l
   1 select ename
   2* from emp
   如果一次要刪除多行，則指定起始行號和終止行號，例如"del 3 5"
(5)i[nput]
   該命令用於在sql緩衝區的當前行後新增加一行。樣本如下:
   SQL> l
    1 select ename
    2* from emp
   sql>i where deptno=30
   如果要在首行前增加內容，則使用"0文本"
   sql>0 create table temp as
   SQL> l
1 create table temp as
2 select ename
3 from emp
4* where deptno=30
(6) n
該數值用於定位sql緩衝區的當前行，樣本如下:
(7)edi[t]
   該命令用於編輯sql緩衝區的內容。當運行該命令時，在windows平台中會自動啟動"記事本",以編輯sql緩衝區
(8)run和/
   run的/命令都可以用於運行sql緩衝區中的sql語句。注:當使用run命令時，還會列出sql緩衝區內容,eg：
   SQL> run
   1* select ename from emp where deptno=20
   4.檔案操縱命令
   (1)save
   該命令用於將當前sql緩衝區的內容儲存到sql指令碼中。當執行該命令時，預設選項為create，即建立新檔案。
   eg:
   SQL> save c:\a.sql create
   已建立 file c:\a.sql
    當執行命令之後，就會建立新指令檔a.sql，並將sql緩衝區內容存放到該檔案中。如果sql已經存在，使用
   replace選項可以替撚已存在的sql指令碼，如果要給已存在的sql指令碼追加內容，可以使用append選項。
   (2)get
    該命令與save命令作用恰好相反，用於將sql指令碼中的所有內容裝載到sql緩衝區中。
    eg:
    SQL> get c:\a.sql
    1* select ename from emp where deptno=20
   (3)start和@
    start和@命令用於運行sql指令檔。注:當運行sql指令檔時，應該指定檔案路徑.eg:
    SQL> @c:\a.sql
    ENAME
    ----------
    SMITH
    JONES
    SCOTT
    ADAMS
    FORD
   (4)@@
    該命令與@命令類似，也可以運行指令檔，但主要作用是在指令檔中嵌套調用其它的指令檔。當使用該命令
    嵌套指令檔時，可在調用檔案所在目錄下尋找相應檔案名稱。
   (5)ed[it]
    該命令不僅可用於編輯sql緩衝區內容，也可以用於編輯sql指令檔。當運行該命令時，會啟動預設的系統編輯
    器來編輯sql指令碼。運行方法為:
    sql>edit c:/a.sql
   (6)spool
    該命令用於將sql*plus螢幕內容存放到文字檔中。執行該命令時，應首先建立假離線檔案，並將隨後sql*plus
    螢幕的所有內容全部存放到該檔案中，最後使用spool off命令關閉假離線檔案。eg:
    sql>spool c:\a.sql
    5.格式命令
     sql*plus不僅可以用於執行sql語句、pl/sql塊，而且還可以根據select結果產生報表。使用sql*plus的格式命令
    可以控制報表的顯示格式，例如使用column命令可以控制列的顯示格式，使用ttitle命令可以指定網頁標題;使用
    btitle命令可以指定頁尾注。
    (1)col[umn]
     該命令用於控制列的顯示格式。column命令包含有四個選項，其中clear選項用於清除已定義列的顯示格式:
     heading選項用於指定列的顯示標題;justify選項用於指定欄位標題的對齊格式(left,center,right);format選項用於
     指定列的顯示格式，其中格式模型包含以下一些元素。
     An:設定char,varchar2類型列的顯示寬度;
     9: 在number類型列上禁止顯示前置0;
     0: 在number類型列上強制顯示前置0;
     $: 在number類型列前顯示貨幣符號;
     L: 在number類型列前顯示本地貨幣符號;
     .: 指定number類型列的小數點位置;
     ,: 指定number類型列的千分隔字元;
    eg1:使用column設定列顯示格式
     sql>col ename heading 'name' format a10
     sql>col sal heading 'sal' format L99999.99
     sql>select ename,sal,hiredate from emp
     sql>where empno=7788;
     name                       sal HIREDATE
     ---------- ------------------- -------------------
     SCOTT                ￥3000.00 04/19/1987 00:00:00
     sql>col ename clear
     sql>col sal clear
     sql>select ename,sal,hiredate from emp
     sql>where empno=7788;
     (2)title
     該命令用於指定網頁標題，網頁標題會自動顯示在頁的中央。如果網頁標題由多個片語成，則用單引號引住。如果要將頁
    標題分布在多行顯示，則用"|"分開不同單詞。如果不希望顯示網頁標題，則使用"ttitle off"命令，禁止顯示,eg:
SQL> set linesize 40
SQL> ttitle 'employee report'
SQL> select ename,sal,hiredate from emp where empno=7788;

星期二 5月 20                第    1
            employee report

ENAME             SAL
---------- ----------
HIREDATE
-------------------
SCOTT            3000
04/19/1987 00:00:00
   (3)btitle
該命令用於指定頁尾注，頁尾注會自動顯示在頁的中央。如果頁尾注由多個片語成，則用單引號引注。如果要將頁尾注
分布在多行顯示，則用"|"分開不同單詞。如果不希望顯示頁尾注，則使用"btitle off"命令，禁止顯示。eg:
   SQL> btitle 'page end'
   SQL> select ename,sal,hiredate from emp where empno=7788
ENAME             SAL
---------- ----------
HIREDATE
-------------------
SCOTT            3000
04/19/1987 00:00:00
         page end
(4)break
該命令用于禁止顯示重複行，並將顯示結果分隔為幾個部分，以表現更友好的顯示結果，通常應該在order by 的排序列上
使用該命令。eg:
SQL> set pagesize 40
SQL> break on deptno skip 1
SQL> select deptno,ename,sal from emp order by deptno
2 ;

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
           KING             5000
           MILLER           1300

        20 JONES            2975
           FORD             3000
           ADAMS            1100
           SMITH             800
           SCOTT            3000

        30 WARD             1250
           TURNER           1500
           ALLEN            1600
           JAMES             950
           BLAKE            2850
           MARTIN           1250
   6.互動式命令
    如果經常要執行某些sql語句和sql*plus命令，可以將這些語句和命令存放到sql指令碼中。通過使用sql指令碼，
   一方面可以降低命令輸入量，另一方面可以避免使用者的輸入錯誤。為了使得sql指令碼可以根據不同輸入獲得
   不同結果，需要在sql指令碼中包含互動式命令。通過使用互動式命令，可以在sql*plus中定義變數，並且在運行
   sql指令碼時可以為這些變數動態輸入資料。下面介紹sql*plus的互動命令，以及引用變數所使用的標號。
(1)&
    引用替代變數(substitution variable)時，必須要帶有該標號。如果替代變數已經定義，則會直接使用其資料，
    如果替代變數沒有定義，則會臨時定義替代變數(該替代變數只在當前語句中起作用),並需要為其輸入資料。
    注:如果替代變數為數字列則提供資料，則可以直接引用;如果替代變數為字元類型列或日期類型列提供資料，
    則必須要用單引號引注。eg:
SQL> select ename,sal from emp where deptno=&no and job='&job';
輸入 no 的值: 20
輸入 job 的值: CLERK
原值    1: select ename,sal from emp where deptno=&no and job='&job'
新值    1: select ename,sal from emp where deptno=20 and job='CLERK'
   (2)&&
   該標號類似於單個&標號。但需要注意，&標號所定義的替代變數只在當前語句中起作用;而&&標號所定義的變數
會在當前sql*plus環境中一直生效。eg:
SQL> select ename,sal from emp where deptno=&&no and job='&&job' --定義了no變數
輸入 no 的值: 20
輸入 job 的值: CLERK
原值    1: select ename,sal from emp where deptno=&&no and job='&&job'
新值    1: select ename,sal from emp where deptno=20 and job='CLERK'
SQL> select ename,sal from emp where deptno=&no;           
原值    1: select ename,sal from emp where deptno=&no             --直接引用no變數
新值    1: select ename,sal from emp where deptno=20
ENAME             SAL
---------- ----------
SMITH             800
JONES            2975
SCOTT            3000
ADAMS            1100
FORD             3000
如例所示，當第一次引用no變數時，使用&&標號需要為其輸入資料;當第二次引用no變數時，
使用&標號直接引用其原有值，而不需要輸入資料。
(3)define
該命令用於定義類型為char的替代變數，而且該命令的定義的替代變數只在當前sql*plus環境中起作用。
當使用該命令定義變數時，如果變數值包含空格或區分大小寫，則用引號引注。另外，使用"define變數名"可以檢查變數
是否已經定義。eg:
sql>set verify off
sql>define title=CLERK
sql>select ename,sal from where job='&title';
(4)accept
該命令可以用於定義char,number和date類型的替代變數。與define命令相比，accept命令更加靈活。當使用該命令定義替代
變數時，還可以指定變數輸入提示、變數輸入格式、隱藏輸入內容。
eg1:指定變數輸入提示
SQL> accept title prompt '請輸入崗位:'
請輸入崗位:CLERK
SQL> select ename,sal from emp where job='&title';
原值    1: select ename,sal from emp where job='&title'
新值    1: select ename,sal from emp where job='CLERK'

ENAME             SAL
---------- ----------
SMITH             800
ADAMS            1100
JAMES             950
MILLER           1300
eg2:隱藏使用者輸入
sql>accept pwd hide
(5)undefine
該命令用於清除替代變數的定義。eg:
sql>undefine pwd
SQL> disc
從 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 斷開
SQL> conn scott/&pwd
輸入 pwd 的值: yhai1981
已串連
(6)prompt的pause
prompt命令用於輸出提示資訊，而pause命令則用於暫停指令碼執行。在sql指令碼中結合使用這兩條命令，可以控制sql指令碼
的暫停執行。假定在a.sql指令碼中包含以下命令：
prompt '按<Return>鍵繼續'
pause
當運行該sql指令碼時，會暫停執行，eg:
sql>@c:\a.sql
'按<Return>鍵繼續'
(7)variable
該命令用於在sql*plus中定義綁定變數。當在sql語句或pl/sql塊中引用綁定變數時，必須要在綁定變數前加冒號(:);
當直接給綁定變數賦值時，需要使用execute命令(類似於調用預存程序).樣本如下:
sql>var no number
sql>exec :no:=7788
sql>select ename from emp where empno=:no;
ename
------------------
scott
(8)print
該命令用於輸出綁定變數結果,eg:
SQL> print no

        NO
----------
      7788
7.顯示和設定環境變數
使用sql*plus的環境變數可以控制其運行環境，例如設定行顯示寬度，設定每頁顯示的行數、
設定自動認可標記、設定自動跟蹤等等。使用show命令可以顯示當前sql*plus的環境變數設定
:使用set命令可以修改當前sql*plus的環境變數設定。下面介紹常用的sql*plus環境變數。
(1)顯示所有環境變數
為了顯示sql*plus的所有環境變數，必須要使用show all命令。樣本如下:
SQL> show all
appinfo 為 OFF 並且已設定為 "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF 為下一條 SELECT 語句的前幾個字元
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK 為 ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
用於 6 或更多行的 FEEDBACK ON
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 4
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE 為 OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 1002000100
repfooter OFF 為 NULL
repheader OFF 為 NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool ON
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlpluscompatibility 10.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF 為下一條 SELECT 語句的前幾個字元
underline "-" (hex 2d)
USER 為 "SCOTT"
verify ON
wrap : 將換至下一行
SQL> spool off

(2)arraysize
該環境變數用於指定數組提取尺寸，其預設值為15.該值越大，網路開銷將會越低，但佔用記憶體會增加。假定使用預設值，
如果查詢返回行數為50行，則需要通過網路傳送4將資料;如果設定為25，則網路傳送次數只有兩次。eg:

SQL> show arraysize
arraysize 15
SQL> set arraysize 25
(3)autocommit
該環境變數用於設定是否自動認可dml語句，其預設值為off(表示禁止自動認可)。當設定為ON時，每次執行DML
語句都會自動認可。eg:
SQL> show autocommit
autocommit OFF
SQL> set autocommit on
SQL> show autocommit
autocommit IMMEDIATE
(4)colsep
該環境變數用於設定列之間的分隔字元，預設分隔符號為空白格。如果要使用其它分隔字元，則使用set命令進行設定。eg:
sql>set colsep |  
SQL> select ename,sal from emp where empno=7788

ENAME     |       SAL
----------|----------
SCOTT     |      3000
(5)feedback
該環境變數用於指定顯示反饋行數資訊的最低行數，其預設值為6。如果要禁止顯示行數反饋資訊，則將feedback
設定為off。假設只要有查詢結果就返回行數，那麼可以將該環境變數設定為1.eg:
sql>set feedback 1
sql>select ename,sal from emp where empno=7788;

ENAME     |       SAL
----------|----------
SCOTT     |      3000
已選擇 1 行。
(6)heading
該環境變數用於設定是否顯示標題，其預設值為on。如果不顯示欄位標題，則設定為off。eg:
sql>set heading off
sql>select ename,sal from emp where empno=7788
SCOTT     |      3000
(7)linesize
該環境變數用於設定行寬度，預設值為80。在預設情況下，如果資料長度超過80個字元，那麼在sql*plus中會折
行顯示資料結果。要在一行中顯示全部資料，應該設定更大的值。eg:
(8)pagesize
該環境變數用於設定每頁所顯示的行數,預設值為14
set pagesize 0;　　　//輸出每頁行數，預設為24,為了避免分頁，可設定為0。
(9)long
該環境變數用於設定long和lob類型列的顯示長度。預設值為80,也就是說當查詢long或lob列時，只會顯示該列的前80個字元，
應該設定更大的值。eg:
sql>show long
long 80
sql>set long 300
(10)serveroutput
該環境變數用於控制伺服器輸出，其預設值為off，表示禁止伺服器輸出。在預設情況下，當調用dbms_output包時，
不會在sql*plus螢幕上顯示輸出結果。在調用dbms_output包時，為了在螢幕上輸出結果，必須要將serveroutput設定
為on。eg:
sql>set serveroutput on
sql>exec dbms_output.put_line('hello')
(11)termout
該環境變數用於控制sql指令碼的輸出，其預設值為ON。當使用預設值時，如果sql指令碼有輸出結果，則會在螢幕上輸出
顯示結果，如果設定為OFF，則不會在螢幕上輸出sql指令碼。eg:
SQL> set termout off
SQL> @c:\a
(12)time
該環境變數用於設定在sql提示符前是否顯示系統時間，預設值為off,表示禁止顯示系統時間。如果設定為on,
則在sql提示符前會顯示系統時間.eg:
SQL> set time on
12:09:59 SQL>
(13)timing
該環境變數用於設定是否要顯示sql語句執行時間,預設值為off，表示不會顯示sql語句執行時間。如果設定為
ON，則會顯示sql語句執行時間。eg:
sql>set timing on
SQL> select count(*) from emp;

COUNT(*)
----------
        14

已選擇 1 行。

經過時間: 00: 00: 00.03
(14)trimspool
set trimout on;　　　//去除標準輸出每行的拖尾空格，預設為off
set trimspool on;　　//去除重新導向（spool）輸出每行的拖尾空格，預設為off

如果trimspool設定為on，將移除spool檔案中的尾部空格 ，trimout同trimspool功能相似，只不過對象是控制台。
If trimspool is set to on, it will remove trailing blanks in spooled files.
See also trimout which does the same thing to the output to the console (terminal).
eg:
set trimspool off
spool c:\temp\trimspool.txt
declare
v_name varchar2(30);
begin
SELECT table_name into v_name
FROM all_tables
WHERE rownum =1;
dbms_output.put_line(v_name);
end;
/
set trimspool on
declare
v_name varchar2(30);
begin
SELECT table_name into v_name
FROM all_tables
WHERE rownum =1;
dbms_output.put_line(v_name);
end;
/
spool off

-- from ITPUB

  【參考網站資料：http://www.itpub.net/247463.html 】

   http://www.itpub.net/memcp_index.php