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