SQLPLUS 操作大全

附錄B SQL*PLUS
Sql*plus 中使用繫結變數：
sql> variable x number;
sql> exec :x := 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 '按鍵繼續'
pause
當執行該sql指令碼時，會暫停執行，eg:
sql>@c:a.sql
'按鍵繼續'
(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 "" 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:temptrimspool.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