sql*plus使用的一些技巧

- 中國WEB開發者網絡 (http://www.webasp.net)
-- 技術教程 (http://www.webasp.net/article/)
--- sql*plus使用的一些技巧 (http://www.webasp.net/article/22/21226.htm)
-- 作者:未知
-- 發佈日期: 2005-04-29

sql*plus使用的一些技巧Sql*plus中蘊藏著好多技巧,如果掌握這些技巧,對於在oracle數據庫下進行快速開發與有效維護數據庫都是有益的。        下面就接受一二,        1.使用SQL*PLUS動態生成批量腳本將spool與select命令結合起來使用,可以生成一個腳本,腳本中包含有可以批量執行某一任務的語句。例1:生成一個腳本,刪除SCOTT用戶下的所有的表:a. 創建gen_drop_table.sql文件,包含如下語句:SPOOL  c:\drop_table.sql      SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;      SPOOL OFF b. 以SCOTT用戶登錄數據庫SQLPLUS > @ …..\gen_dorp_table.sqlc. 在c盤根目錄下會生成文件drop_table.sql文件,包含刪除所有表的語句,如下所示:SQL>      SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;                                                                                               'DROPTABLE'||TABLE_NAME||';'                           -------------------------------------------------------------------------------- DROP TABLE DEPT;                                         DROP TABLE EMP;                                         DROP TABLE PARENT;                                      DROP TABLE STAT_VENDER_TEMP;                      DROP TABLE TABLE_FORUM;                                                                                                               5 rows selected.                               SQL>      SPOOL OFF  d. 對生成的drop_table.sql文件進行編輯去掉不必要的部分,只留下drop table …..語句e. 在scott用戶下運行dorp_table.sql文件,刪除scott用戶下所有的表。SQLPLUS > @ c:\dorp_table.sql在上面的操作中,在生成的腳本文件中會有多餘的字符,如運行的sql語句,標題,或返回的行數,需要我們編輯該腳本後再運行,給實際的操作帶來諸多不便。懶惰是人的本性,這促使我們用更簡單的辦法來實現上面的任務。a. 創建gen_drop_table.sql文件,包含如下語句:        set echo offset feedback offset newpage noneset pagesize 5000set linesize 500set verify offset pagesize 0set term offset trims onset linesize 600set heading  off set timing offset verify offset numwidth 38SPOOL  c:\drop_table.sql      SELECT 'DROP TABLE '|| table_name ||';' FROM user_tables;      SPOOL OFF b. 以SCOTT用戶登錄數據庫SQLPLUS > @ …..\gen_dorp_table.sqlc. 在c盤根目錄下會生成文件drop_table.sql文件,包含刪除所有表的語句,如下所示:DROP TABLE DEPT;                                         DROP TABLE EMP;                                         DROP TABLE PARENT;                                      DROP TABLE STAT_VENDER_TEMP;                      DROP TABLE TABLE_FORUM;                              d. 在scott用戶下運行dorp_table.sql文件,刪除scott用戶下所有的表。SQLPLUS > @ c:\dorp_table.sql2.將一個表中的數據導出生成一個文本文件,列與列之間以”,”隔開set echo offset feedback offset newpage noneset pagesize 5000set linesize 500set verify offset pagesize 0set term offset trims onset linesize 600set heading  off set timing offset verify offset numwidth 38SPOOL  c:\drop_table.sql      select DEPTNO || ',' || DNAME FROM DEPT;     SPOOL OFF 將上面的內容保存為一個文本文件後,以scott登錄,執行該文件後顯示結果:10,ACCOUNTING  20,RESEARCH  30,SALES  40,OPERATIONS  通過上面的兩個例子,我們可以將:set echo offset feedback offset newpage noneset pagesize 5000set linesize 500set verify offset pagesize 0set term offset trims onset linesize 600set heading  off set timing offset verify offset numwidth 38SPOOL  c:\具體的文件名      你要運行的sql語句     SPOOL OFF作為一個模版,只要將必要的語句假如這個模版就可以了。在oracle的較新版本中,還可以用set colsep命令來實現上面的功能:SQL> set colsep ,SQL> select * from dept;        10,ACCOUNTING    ,NEW YORK        20,RESEARCH      ,DALLAS        30,SALES         ,CHICAGO        40,OPERATIONS    ,BOSTON        35,aa            ,bb3.動態生成spool命令所需的文件名在我們上面的例子中,spool命令所需要的文件名都是固定的。有時我們需要每天spool一次,並且每次spool的文件名都不相同,如文件名包含當天的日期,該如何實現呢?column dat1 new_value filename;select to_char(sysdate,'yyyymmddhh24mi') dat1 from dual;  spool c:\&&filename..txt  select * from dept;spool off;4.如何從腳本文件中得到WINDOWS環境變量的值:在windos中:spool c:\temp\%ORACLE_SID%.txt   select * from dept;   ...   spool off在上面的例子中,通過%ORACLE_SID%的方式引用環境變量ORACLE_SID的值,如果ORACLE_SID的值為orcl,則生成的spool文件名為:orcl.txt在UNIX中:spool c:\temp\$ORACLE_SID.txt   select * from dept;   ...   spool off在上面的例子中,通過$ORACLE_SID的方式引用環境變量ORACLE_SID的值,如果ORACLE_SID的值為orcl,則生成的spool文件名為:orcl.txt5.如何指定缺省的編輯腳本的目錄        在sql*plus中,可以用save命令,將上一條執行的sql語句保存到一個文件中,但是如何設置該文件的缺省目錄呢?通過SQL> set editfile c:\temp\file.sql 命令,可以設置其缺省目錄為c:\tmpe,缺省文件名為file.sql。6.如何除去表中相同的行找到相同的行:SELECT * FROM dept a   WHERE ROWID <> (SELECT MAX(ROWID)                   FROM dept b                   WHERE a.deptno = b.deptno                   AND a.dname = b.dname  -- Make sure all columns are compared                   AND a.loc = b.loc);註釋:如果只找deptno列相同的行,上面的查詢可以改為:SELECT * FROM dept a   WHERE ROWID <> (SELECT MAX(ROWID)                   FROM dept b                   WHERE a.deptno = b.deptno)刪除相同的行:DELETE FROM dept aWHERE ROWID <> (SELECT MAX(ROWID                                FROM dept b                                WHERE a.deptno = b.deptno                                AND a.dname = b.dname -- Make sure all columns are compared                                AND a.loc = b.loc);注意:上面並不刪除列值為null的行。7.如何向數據庫中插入兩個單引號(’’)Insert inot dept values(35,’aa’’’’bb’,’a’’b’);在插入時,用兩個’表示一個’。8.如何設置sql*plus的搜尋路徑,這樣在用@命令時,就不用輸入文件的全路徑。設置SQLPATH環境變量。如:SQLPATH = C:\ORANT\DBS;C:\APPS CRIPTS;C:\MYSCRIPTS9.@與@@的區別是什麼?@等於start命令,用來運行一個sql腳本文件。@命令調用當前目錄下的,或指定全路徑,或可以通過SQLPATH環境變量搜尋到的腳本文件。@@用在腳本文件中,用來指定用@@執行的文件與@@所在的文件在同一目錄,而不用指定全路徑,也不從SQLPATH環境變量指定的路徑中尋找文件,該命令一般用在嵌套腳本文件中。10.&與&&的區別&用來創建一個臨時變量,每當遇到這個臨時變量時,都會提示你輸入一個值。&&用來創建一個持久變量,就像用用define命令或帶new_vlaue字句的column命令創建的持久變量一樣。當用&&命令引用這個變量時,不會每次遇到該變量就提示用戶鍵入值,而只是在第一次遇到時提示一次。如,將下面三行語句存為一個腳本文件,運行該腳本文件,會提示三次,讓輸入deptnoval的值:select count(*) from emp where deptno = &deptnoval;select count(*) from emp where deptno = &deptnoval;select count(*) from emp where deptno = &deptnoval;將下面三行語句存為一個腳本文件,運行該腳本文件,則只會提示一次,讓輸入deptnoval的值:select count(*) from emp where deptno = &deptnoval;select count(*) from emp where deptno = &deptnoval;select count(*) from emp where deptno = &deptnoval;11.引入copy的目的Copy命令在兩個數據庫之間拷貝數據時特別有用,特別是該命令可以在兩個數據庫之間傳遞long型字段的數據。缺點:在兩個數據庫之間傳遞數據時,有可能丟失精度(lose precision)。12.問什麼在修改大量的行時,我的腳本會變得很慢?當通過PL/SQL塊修改一個表中的許多行時,你會創建在表上創建一個cursor,但是只有在你關閉cursor時,才會釋放ROLLBACK SEGMENT,這樣,當cursor仍然打開時,修改過程會變慢,這是因為數據庫不得不搜尋大量的rollback segment以便於維護讀一致性。為了避免這樣情況,試著在表上加一個標誌字段來描述該行是否已經被修改,然後關閉該cursor,然後再打開該cursor。每次可以修改5000行.

轉此:http://www.cnoug.org/viewthread.php?tid=31451

webasp.net