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/password@connstr
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%sqlplusadmin目錄下有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/&1@&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文件的內容。
?
使用function定義構造函數)
