Sql*plus的使用
Sql*plus介紹
Sql*plus是oracle提供的一個工具程式，既可以在oracle伺服器使用，也可以在oracle用戶端使用。在windows下分兩種，sqlplus.exe是指令行程式，sqlplusw.exe是窗體程式，通常我們在開始菜單中啟動的是後者，兩者的功能是一緻的。
Sql*plus是一個最常用的工具，具有很強的功能，主要有：
1. 資料庫的維護，如啟動，關閉等，這一般在伺服器上操作。
2. 執行sql語句執行pl/sql。
3. 執行sql腳本。
4. 資料的導出，報表。
5. 應用程式開發、測試sql/plsql。
6. 生成新的sql腳本。
7. 供應用程式調用，如安裝程式中進行腳本的安裝。
2 dual表
dual是一張系統表，同時也被定義成了public同義詞。它隻有一個字段和一條記錄。該表本身的結構和資料沒有什麼意義，主要是借助該表進行其它操作。如：
select sysdate from dual; --擷取函數值
select 21+15*3 from dual; --計算表達式的值
說明：不要對dual表進行ddl與dml操作，隻進行查詢操作。
3 sql*plus使用
3.1 啟動sql*plus
1. 不帶參數啟動
啟動sqlplusw.exe程式，會彈出登陸框，讓輸入使用者名、密碼和連接配接字元串，在使用者名中輸入“/nolog”，表示先進入sql>提示符，先不連接配接資料庫，下面可以利用connect指令連接配接資料庫。啟動sqlplus.exe程式，會提示輸入使用者名與密碼。如果使用者名輸入
2. 帶參數啟動
下面列舉一些最常見的方式，還有很多可選參數。
1) 不連接配接資料庫
sqlplus /nolog
2) 連接配接資料庫
sqlplus username/password
3) 使用net8連接配接字元串連接配接資料庫
sqlplus username/[email protected]
4) 連接配接後執行filename指定的sql腳本,sql腳本中是sql指令和sql*plus的設定指令
sqlplus username/password[@connstr] @filename
3.2 sql*plus的指令
3.2.1 幫助指令
1. help指令
格式：help 指令名 ，用于知道某個具體指令的幫助資訊。
如：help connect
2. describe指令
使用者檢視表的結構；擷取函數，存儲過程和包的描述。這是非常有用和常用的一個指令。
如：desc user_tables
說明：在sql*plus中，所有指令都可以用前面的四個字母作為整個指令。
3.2.2 編輯指令
sql*plus會将上一次執行過的sql指令（包括sql語句和pl/sql語句，包括一行或多行）儲存到緩存區中，可以對緩存區中資訊進行編輯。
編輯後可以通過 “/ ”指令執行修改後的緩存區中的指令，如果不休改，則是執行原有指令。
1. list [n] 指令
顯示上一條指令中的第n行，如果不指定n，則顯示上一指令的所有行，這樣目前行就是最後一行。其它操作會對目前行進行操作，是以其它操作需要先執行list指令。
如：
begin
insert into test values(1);
end;
/
list
2. change指令
編輯目前行的内容，先用list指令指定目前行。文法為：
change /被修改字串/修改後的串
如：
list 2
change /(1)/(20)
3. 增加新行
在第一行插入一行，方法為：輸入0，在0後輸入文本。如：
0 insert into test values(2);
在目前行後插入一行，方法為：輸入input（或i），回車，輸入新行，再回車，會提示再輸入新行，如不想輸入，輸入點号，回車。如：
4. 删除行
del --删除緩存區目前行，執行前先用list指令指定目前行
del n 删除緩存區指定的行
5. 使用作業系統編輯器編輯指令
在 sql*plus中輸入edit指令，會自動打開系統的預設的文本編輯器（windows下為notepad），緩存區中内容被裝到文本編輯器中，這時可以對其中的内容進行編輯（這時sql*plus處于等待狀态），修改完畢後，儲存檔案後。被修改的内容就會被寫入緩存區。這對于修改錯誤指令很友善。
6. save指令
格式：save 檔案名 [replace | append]
save指令的作用是将緩存區中内容儲存到指定檔案中。如果指定的檔案不存在，将會建立，但如果檔案目錄不存在，将會失敗。如果指定檔案名的檔案已存在，不指定replace或append參數将會失敗。指定replace表示将覆寫原檔案内容，指定append表示将緩存區内容加到檔案後。
7. get指令
格式：get 檔案名
get指令的作用是将指定檔案的内容加載到緩存區中，以供編輯或執行。
3.2.3 spool指令
sql> spool 檔案名
執行該指令後，如果指定的檔案不存在，則會按指定的檔案名建立一個空文本檔案，如果目錄不存在，會失敗。如果指定的檔案已存在，則檔案内容将會被清空。
執行上述指令後，此指令後的所有輸出（包括指令、輸出提示資訊、錯誤資訊等）都會被寫入指定的檔案。需要說明的時，并不是每輸出一行資訊，就會立即寫入檔案，有個緩存過程。
Sql>spool off
上述指令就是停止存儲，将前面所有輸出立即寫入檔案。
Sql>spool out
除完成spool off指令的功能外，還列印輸出的資訊。
說明：spool off/out指令必須與spool指令一一對應。
Spool指令的用途主要有如下：
1） 導出資料
2） 記錄腳本的執行日志
3） 生成新的sql腳本
3.2.4 start/@ 指令
格式：start/@ 腳本檔案名
這樣可以将相關的sql/plsql語句，sql*plus的指令寫在腳本中，進而執行。最常用的就是安裝腳本，更新腳本。
如：
sql> start e:/test.sql
sql> @e:/test.sql
3.2.5 設定sql*plus環境（set指令）
可以通過設定參數來改變sql*plus的一些屬性，如顯示等。如果運作的是sqlplusw.exe程式，通過菜單“選項|環境”可以通過界面改變這些參數的預設值。也可在sql>提示符下輸入set指令來改變參數的值。
檢視參數的目前設定值的指令是：show 參數名 。 常見的設定參數指令有：
1. set pagesize [n]
用于設定每頁的行數，範圍為1~ 50000，如果為0，則表示不分頁，不帶n，表示為0。否則，預設情況下，當查詢結果的行數超過一頁的行數時，就會分頁顯示，每頁的開頭會顯示列标題資訊。
2. set newpage [n]
該指令與pagesize結合使用，用來設定每一頁的頂行的空行數，範圍為0~ 999，不帶n，表示為0。預設值為1。
3. set linesize n
設定每行能容納的字元數，範圍為1~32767 。在查詢時，通常一條記錄會顯示一行，如果一行顯示不下，則會自動換行。使用者輸入資料時，當一行輸入的值超過一行的最大值時，也會自動換行。
4. set heading off|on
設定打開(on)或關閉(off)查詢結果頁的頭資訊，如列标題。比如，想輸出sql語句存儲到檔案中時，就需要把這關閉。如：
select 'insert into test1 values('||id||');' from test;
5. set feedback off|on
設定為on，當執行insert,update，pl/sql等操作時，會提示執行的結果。如果設定為off，則不顯示。
6. set termout off|on
設定為off，執行的資訊就不會在螢幕上顯示。需要說明的是，該選項隻有在腳本中設定，執行腳本時才有效。
7. set trimspool off|on
設定為on，查詢結果輸入到檔案中時，對于查詢結果的後面的空格，将被截掉。
8. set serveroutput off|on
設定on，在pl/sql中使用dbms_output包輸出調試資訊時sql*plus中可以顯示出來，否則不顯示。預設為off。關于dbms_output包的詳細資訊在以後介紹。需要說明的是，該設定隻是在目前會話有效，一旦重新連接配接後，又恢複為預設值。可以在未連接配接資料庫的情況下設定，設定後連接配接後有效，但一旦重新連接配接就恢複預設值了。 如：
begin
dbms_output.put_line('hello');
end;
9. set timing off|on
設定為on，每執行一sql或pl/sql，都會顯示該執行所需要的時間，通過這可以檢視sql語句的執行效率。
10. set autocommit on|off|n
在sql*plus中，執行dml語句後，需要commit後或者執行了dcl或ddl語句後才會被送出。本指令可以設定讓sql*plus自動送出。
其中on表示每執行一sql/plsql，都自動送出一下。而off隻是當sql*plus退出時才自動送出一下。n表示執行n條sql/plsql語句後就自動送出一下。
11. set echo on|off
設定為on，sql*plus執行腳本時，都會将每一條執行的sql語句輸出來，這樣如果執行出錯，便于定位。預設為off 。
3.2.6 show指令
通過show 參數名 ，可以看到目前sql*plus的一些環境參數的設定。還可以檢視其它資訊，如：
show user
檢視目前登陸的使用者
show error
檢視sql執行出錯的詳細資訊，因為建立pl/sql對象時即使出錯，sql*plus不會報error，隻會報warning，而且無法看到詳細錯務資訊，通過show error就可以看到。
3.2.7 column(col)指令
該指令可用于設定列的顯示屬性，常見格式如：
1. col 列名 format an [truncate]
上面指令用于設定列的顯示寬度，n為寬度。Truncate表示如果列值寬度超過n時，就截去超長的部分。不加Truncate，如果列值寬度超過n時，換行顯示。
如：col s format a20
比如一個字段定義了varchar2類型，長度很大，但實際字段值寬度很少，如果不加設定，在sql*plus查詢時，會占用定義的寬度，這樣看起來不方面。
2. 設定數字的顯示寬度
create table test(id number);
insert into test values(888888812345678);
SQL> select * from test;
ID
----------
8.8889E+14
SQL> select to_char(id) from test;
TO_CHAR(ID)
----------------------------------------
888888812345678
col id format 999999999999999999999999 --設定數字的顯示寬度
SQL> select * from test;
ID
-------------------------
888888812345678
3. 清除列的設定
col 列名 clear --清除指定列的格式設定
clear columns --清除所有列的格式設定
3.2.8 host指令
通過host指令，可以在sql*plus中執行作業系統指令。如：
sql>host mkdir e:/temp
3.2.9 退出sql*plus
文法：exit [n]
說明：通過exit指令退出sql*plus，退出可以帶一個錯務碼。主要是供父程序使用。
3.3 執行失敗的處理
當一個腳本中存在大量的sql/plsql語句時，執行該腳本時，如果其中有一條sql語句執行失敗，可能很難發現。如果能讓碰到錯誤時，讓sql*plus退出，再結合spool指令檢視日志，就很容易知道腳本的執行情況。這可以通過whenever指令來實作。
格式一：whenever sqlerror exit [success | failure | warning | n] [commit | rollback | none]
說明：一旦腳本中該語句之後的某sql語句執行出錯，就會停止出錯，sql*plus就會自動退出。其中success | failure | warning | n 為設定sql*plus出錯後退出帶出的錯務碼，success | failure | warning為固定的常量，n為指定任意數值。如果加了commit選項，則出錯後，sql*plus在退出前，會自動執行commit一下；如果加了rollback選項，則出錯後，sql*plus在退出前，會自動執行rollback一下；如果加了none選項或什麼都不加，則退出不會做任何事，但實際上預設設定下sql*plus在退出前會commit一下，是以這種情況與加了commit選項效果一樣。
例：whenever sqlerror exit sql.sqlcode --保證了出現錯誤，立即退出，并傳回錯誤碼
例：***test.sql***
spool e:/test.txt
whenever sqlerror exit
select * from test1233;
select * from dual;
spool off
格式二：whenever sqlerror continue [commit | rollback | none]
說明：一旦腳本中該語句之後的某sql語句執行出錯，會繼續往下執行，sql*plus不會自動退出，預設就是這樣的設定。如果加了commit選項，則出錯後，執行下一條語句前，sql*plus會自動執行commit一下；如果加了rollback選項，則出錯後，執行下一條語句前，sql*plus會自動執行rollback一下；如果什麼都沒加或加了none選項，則出錯後，對前面執行過的語句不會自動添加執行任何commit或rollback語句，是否commit或rollback，看後面的語句。
例：***test.sql***
spool e:/test.txt
whenever sqlerror continue
select * from test12;
select * from dual;
spool off
說明：當建立一個pl/sql對象時，如存儲過程，函數，觸發器，包等。即使代碼寫的有問題，在sql*plus執行時不會報error，隻會報warning。這樣通過whenever sqlerror是無法擷取到的。而且sql*plus不直接給出出錯的詳細資訊，這可以通過show error指令看到出錯的詳細資訊。
3.4 固化對sql*plus的環境設定
通過上面的介紹我們可以知道，可以通過指令設定sql*plus運作環境參數。但sql*plus一旦關閉重新打開，這些參數又恢複成預設值，又需要重新設定，這非常不友善。好的是，oracle提供一種方法可以使這些設定固定下來。
如果%ORACLE_HOME%/sqlplus/admin目錄下有glogin.sql檔案（不同的系統目錄和檔案名可能不确定），則啟動sql*plus後（如果有初始連接配接，則連接配接後），sql*plus會自動執行該腳本，這樣就可以在該腳本對sql*plus的參數進行設定，還可以加上特定的sql/plsql語句。
可以看出，每次啟動sql*plus，不管是誰啟動，都會執行glogin.sql腳本。Oracle同時提供一種機制讓不同的作業系統使用者啟動sql*plus執行自己特定的腳本。方法是，在目前目錄下（即運作sql*plus程式時的目前目錄）編寫一個檔案名為login.sql的腳本，将自己特定的設定寫入檔案，這樣啟動sql*plus時首先會執行login.sql，然後再執行公共的glogin.sql。這在windows系統下顯的不是很方面，因為隻有在指令行下啟動sql*plus時才有效，且必須先設定目前目錄。而在unix下就非常有用，因為unix下每個登陸使用者都有自己的主目錄，而登陸後目前目錄就是主目錄，不同的使用者可以将login.sql檔案放在自己的主目錄下。
4 日期類型的顯示
在sql*plus中執行：select sysdate from dual; 發現查詢結果是以字元串顯示的，但格式不好看。原因是，oracle對日期類型它會自動的按照預設格式隐式的轉換為字元串類型。
可以通過t_char函數和to_date函數來實作日期與字元串之間的顯示轉轉。如：
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;
create table test(s date);
insert into test values(to_date(‘2005-01-25 10:20:22’,’ yyyy-mm-dd hh24:mi:ss’));
也可以通過執行sql指令，來改變目前會話的日期格式。如：
alter session set nls_date_format=’ yyyy-mm-dd hh24:mi:ss’;
select sysdate from dual;
insert into test values(‘2005-01-25 10:20:22’);
可以直接将字元串插入到日期類型中，原因是oracle對資料類型會做自動隐式的轉換。
說明：該設定隻在目前會話有效，重新登陸後，又恢複為預設值。
5 腳本中變量
5.1 替換變量
變量前加一個&符号，sql*plus在指令中遇到替換變量時，用真實值去代替，相當于c語言中的宏定義。真實值來源于三個地方：
1） 腳本參數帶入
2） 腳本中直接定義
3） 使用者動态輸入
如：
select &num from dual;
sql*plus中有幾個環境參數将影響替換變量，為：
set define off | c
off表示關閉替換變量功能。c為定義替換字元（預設為&），同時啟用替換變量功能。
set escape off | c
定義轉義字元。即&前面如果有指定的c字元，則作為普通字元處理。預設是off，即沒有定義轉義字元。如：
set escape /
select ‘/&hello’ from dual;
set ver off|on
如果為on，在替換前後會列出指令檔案的每一行，預設為on。
set concat c
設定替換變量與其後的其它字元的分隔符，預設為句點（.）。如：
select ‘&hello.good’ from dual; -- 句點不作為輸出的一部分
等價于：select ‘&hello’||’good’ from dual;
select ‘&hello good’ from dual; --空格也起到分隔作用，單空格作為輸出的一部分
select ‘&hello’||’.good’ from dual;
5.1.1 腳本帶參數
腳本可以帶參數，在腳本中通過&n來引用參數，n為1表示為第一個參數，2表示第二個參數，依次類推。如：
set ver off;
connect omc/&1
insert into test values(&2);
commit;
執行該腳本的方法是：sql>@e:/test.sql omc self 45
如果參數是字元串，且字元串有空格，應該用雙引号或單引号擴起，如果字元串中有雙引号，則隻能用單引号擴起，如果字元串中要輸入單引号，則隻能用雙引号擴起，且輸入兩個單引号才代表一個單引号。如：
set ver off;
connect omc/&[email protected]&2
create table test(s varchar2(20));
insert into test values('&3');
commit;
執行：
sql>@e:/test.sql omc self hello
sql>@e:/test.sql omc self “hello world”
sql>@e:/test.sql omc self hello world’
sql>@e:/test.sql omc self “hello ‘’zte’’world”
sql>@e:/test.sql omc self ‘hello “zte” world’
正常情況下，slq*plus執行時，碰到&符号，就會作為參數來處理，如果&符号後跟的是數字，就會從指令行中取相應的值替換&n，如果找不到，如n為3，但執行時隻帶了2個參數，sql*plus就會在螢幕上提示輸入參數。如果&後跟的不是數字，而是其它字元，則sql*plus會把&及其後的字元串（截止到空格為止）當作一個參數提示輸入。
如：
select ‘hello&good china’ from dual;
那麼如何将&作為普通字元處理呢？除了前面介紹的定義escape環境參數外，另一方法是，&符号後緊跟單引号（之間可以跟空格）。如：
select ‘hello&’||’good china’ from dual;
select ‘hello& ’||’good china’ from dual;
是以，一般情況下在sql*plus下，執行sql語句時或腳本時，對&字元需要特殊處理一下，因為它是sql*plus中的特殊字元。
5.1.2 腳本中定義
格式：define 辨別符 = 值
如：
define n=12
define s=’hello’ --是否加引号沒有關系
通過加&引用,如：
select &n from dual;
select ‘&s’ from dual; --注意必須要加引号
begin
dbms_output.put_line('&s');
end;
取消定義的方法是：undefine辨別符
一旦取消定義後，如果在通過&引用，則sql*plus會提示輸入。
5.1.3 接收使用者互動式輸入
很多時候，在執行腳本時，我們希望有些資訊根據腳本的提示，讓使用者動态輸入，進而在下面的sql語句中使用。
文法為：accept var_name typename prompt ‘hint’ [hide]
說明：hide表示以密文方式讓使用者輸入。typename隻能是char,number,date三種。
如：
accept user_name char prompt ‘please input username:’
accept passwd char prompt ‘please input password:’ hide
通過變量前加&引用：connect &user_name/&passwd
5.2 捆綁變量
一般情況下，我們都是在pl/slq中定義變量，如：
delclare
s varchar2(10);
begin
s:=’hello’;
insert into test values(s);
end;
上面的程式段中，定義了變量s，但它隻在該程式段有效。那如何定義在整個sql*plus中都有效的變量（這裡稱為捆綁變量）呢？方法如：
var g_str varchar2(10)
begin --指派隻能在pl/sql中進行
:g_str:='hello'; --注意前面要加冒号
end;
select :g_str from dual; --引用捆綁變量，注意前面要加冒号
print g_str --顯示捆綁變量，注意前面不要加冒号
declare --在pl/sql中引用捆綁變量
s varchar2(10);
begin
dbms_output.put_line(:g_str);
s:=:g_str;
dbms_output.put_line(s);
insert into test values(:g_str);
end;
6 腳本例子
6.1 生成表的備份腳本
編寫存儲過程，該存儲過程生成一個select語句，執行結果為輸出指定表的所有記錄的insert語句。
create or replace function BuildSelSql(tablename in varchar2) return varchar2 is
cursor cur(tabname varchar2) is
select column_name,data_type from user_tab_columns where table_name=upper(tabname) order by COLUMN_ID;
tmp varchar2(4000);
msg varchar2(4000);
len number;
num number;
begin
tmp:='select ''insert into '||tablename||' values(''';
for re in cur(tablename) loop
if re.data_type='CHAR' or re.data_type='DATE' or re.data_type='VARCHAR2'
or re.data_type='RAW' then
tmp:=tmp||'|'||'|''''''''';
tmp:=tmp||'|'||'|'||re.column_name||'|'||'|'||''''''',''';
else
--tmp:=tmp||'|'||'|'||'decode('||re.column_name||',null,''null'','||re.column_name||')'||'|'||'|'||''',''';
tmp:=tmp||'|'||'|'||'nvl('||re.column_name||',0)'||'|'||'|'||''',''';
end if;
end loop;
len:=length(tmp);
msg:=substr(tmp,1,len-3);
msg:=msg||''');'' from '||tablename ||';';
return msg;
end;
執行函數例子：
select BuildSelSql('test') from dual; --test為表名
set trimout on
set trimspool on
set heading off
set feedback off
set termout off
set ver off
set linesize 4000
set pagesize 0
whenever sqlerror exit
set serveroutput on
spool e:/test1.sql
select BuildSelSql('test') from dual;
spool off
set pagesize 6
btitle left 'commit;' --分批送出
spool e:/test.txt
start e:/test1.sql
spool off
執行：
sql>@e:/test.sql
執行後檢視e:/test.txt檔案的内容。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)