Oracle中SQL*PLUS使用的一些技巧

　　Sql*plus中蘊藏著好多技巧如果掌握這些技巧對於在oracle數據庫下進行快速開發與有效維護數據庫都是有益的
　　
　　．使用SQL*PLUS動態生成批量腳本
　　將spool與select命令結合起來使用可以生成一個腳本腳本中包含有可以批量執行某一任務的語句
　　例
　　生成一個腳本刪除SCOTT用戶下的所有的表
　　a 創建gen_drop_tablesql文件包含如下語句
　　SPOOL　c:\drop_tablesql
　　SELECT DROP TABLE || table_name ||; FROM user_tables;
　　SPOOL OFF
　　b 以SCOTT用戶登錄數據庫
　　SQLPLUS > @ …\gen_dorp_tablesql
　　c 在c盤根目錄下會生成文件drop_tablesql文件包含刪除所有表的語句如下所示:
　　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;
　　
　　 rows selected
　　
　　SQL>　　　SPOOL OFF
　　d 對生成的drop_tablesql文件進行編輯去掉不必要的部分只留下drop table …語句
　　e 在scott用戶下運行dorp_tablesql文件刪除scott用戶下所有的表
　　SQLPLUS > @ c:\dorp_tablesql
　　
　　在上面的操作中在生成的腳本文件中會有多余的字符如運行的sql語句標題或返回的行數需要我們編輯該腳本後再運行給實際的操作帶來諸多不便懶惰是人的本性這促使我們用更簡單的辦法來實現上面的任務
　　
　　a 創建gen_drop_tablesql文件包含如下語句
　　set echo off
　　set feedback off
　　set newpage none
　　set pagesize
　　set linesize
　　set verify off
　　set pagesize
　　set term off
　　set trims on
　　set linesize
　　set heading　off
　　set timing off
　　set verify off
　　set numwidth
　　SPOOL　c:\drop_tablesql
　　SELECT DROP TABLE || table_name ||; FROM user_tables;
　　SPOOL OFF
　　b 以SCOTT用戶登錄數據庫
　　SQLPLUS > @ …\gen_dorp_tablesql
　　c 在c盤根目錄下會生成文件drop_tablesql文件包含刪除所有表的語句如下所示:
　　DROP TABLE DEPT;
　　DROP TABLE EMP;
　　DROP TABLE PARENT;
　　DROP TABLE STAT_VENDER_TEMP;
　　DROP TABLE TABLE_FORUM;
　　d 在scott用戶下運行dorp_tablesql文件刪除scott用戶下所有的表
　　SQLPLUS > @ c:\dorp_tablesql
　　
　　
　　．將一個表中的數據導出生成一個文本文件列與列之間以隔開
　　set echo off
　　set feedback off
　　set newpage none
　　set pagesize
　　set linesize
　　set verify off
　　set pagesize
　　set term off
　　set trims on
　　set linesize
　　set heading　off
　　set timing off
　　set verify off
　　set numwidth
　　SPOOL　c:\drop_tablesql
　　select DEPTNO || || DNAME FROM DEPT;
　　SPOOL OFF
　　將上面的內容保存為一個文本文件後以scott登錄執行該文件後顯示結果
　　ACCOUNTING
　　RESEARCH
　　SALES
　　OPERATIONS
　　
　　通過上面的兩個例子我們可以將
　　set echo off
　　set feedback off
　　set newpage none
　　set pagesize
　　set linesize
　　set verify off
　　set pagesize
　　set term off
　　set trims on
　　set linesize
　　set heading　off
　　set timing off
　　set verify off
　　set numwidth
　　SPOOL　c:\具體的文件名
　　你要運行的sql語句
　　SPOOL OFF
　　作為一個模版只要將必要的語句假如這個模版就可以了
　　
　　在oracle的較新版本中還可以用set colsep命令來實現上面的功能
　　SQL> set colsep
　　SQL> select * from dept;
　　ACCOUNTING　　NEW YORK
　　RESEARCH　　　DALLAS
　　SALES　　　　 CHICAGO
　　OPERATIONS　　BOSTON
　　aa　　　　　　bb
　　
　　．動態生成spool命令所需的文件名
　　在我們上面的例子中spool命令所需要的文件名都是固定的有時我們需要每天spool一次並且每次spool的文件名都不相同如文件名包含當天的日期該如何實現呢？
　　column dat new_value filename;
　　select to_char(sysdateyyyymmddhhmi) dat from dual;
　　spool c:\&&filenametxt
　　select * from dept;
　　spool off;
　　
　　．如何從腳本文件中得到WINDOWS環境變量的值
　　在windos中
　　spool c:\temp\%ORACLE_SID%txt
　　select * from dept;
　　
　　spool off
　　
　　在上面的例子中通過%ORACLE_SID%的方式引用環境變量ORACLE_SID的值如果ORACLE_SID的值為orcl則生成的spool文件名為orcltxt
　　
　　在UNIX中
　　spool c:\temp\$ORACLE_SIDtxt
　　select * from dept;
　　
　　spool off
　　
　　在上面的例子中通過$ORACLE_SID的方式引用環境變量ORACLE_SID的值如果ORACLE_SID的值為orcl則生成的spool文件名為orcltxt
　　
　　．如何指定缺省的編輯腳本的目錄
　　在sql*plus中可以用save命令將上一條執行的sql語句保存到一個文件中但是如何設置該文件的缺省目錄呢？
　　通過SQL> set editfile c:\temp\filesql 命令可以設置其缺省目錄為c:\tmpe缺省文件名為filesql
　　
　　．如何除去表中相同的行
　　找到相同的行
　　SELECT * FROM dept a
　　WHERE ROWID <> (SELECT MAX(ROWID)
　　FROM dept b
　　WHERE adeptno = bdeptno
　　AND adname = bdname　 Make sure all columns are compared
　　AND aloc = bloc);
　　
　　注釋
　　如果只找deptno列相同的行上面的查詢可以改為
　　SELECT * FROM dept a
　　WHERE ROWID <> (SELECT MAX(ROWID)
　　FROM dept b
　　WHERE adeptno = bdeptno)
　　
　　刪除相同的行
　　DELETE FROM dept a
　　WHERE ROWID <> (SELECT MAX(ROWID
　　FROM dept b
　　WHERE adeptno = bdeptno
　　AND adname = bdname Make sure all columns are compared
　　AND aloc = bloc);
　　
　　注意上面並不刪除列值為null的行
　　
　　．如何向數據庫中插入兩個單引號()
　　Insert inot dept values(aabbab);
　　
　　在插入時用兩個表示一個
　　
　　．如何設置sql*plus的搜尋路徑這樣在用@命令時就不用輸入文件的全路徑
　　設置SQLPATH環境變量
　　如
　　SQLPATH = C:\ORANT\DBS;C:\APPS\SCRIPTS;C:\MYSCRIPTS
　　
　　．@與@@的區別是什麼？
　　@等於start命令用來運行一個sql腳本文件
　　@命令調用當前目錄下的或指定全路徑或可以通過SQLPATH環境變量搜尋到的腳本文件
　　@@用在腳本文件中用來指定用@@執行的文件與@@所在的文件在同一目錄而不用指定全路徑也不從SQLPATH環境變量指定的路徑中尋找文件該命令一般用在嵌套腳本文件中
　　
　　．&與&&的區別
　　&用來創建一個臨時變量每當遇到這個臨時變量時都會提示你輸入一個值
　　&&用來創建一個持久變量就像用用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;
　　
　　．引入copy的目的
　　Copy命令在兩個數據庫之間拷貝數據時特別有用特別是該命令可以在兩個數據庫之間傳遞long型字段的數據
　　缺點
　　在兩個數據庫之間傳遞數據時有可能丟失精度(lose precision)
　　
　　．問什麼在修改大量的行時我的腳本會變得很慢？
　　當通過PL/SQL塊修改一個表中的許多行時你會創建在表上創建一個cursor但是只有在你關閉cursor時才會釋放ROLLBACK SEGMENT這樣當cursor仍然打開時修改過程會變慢這是因為數據庫不得不搜尋大量的rollback segment以便於維護讀一致性為了避免這樣情況試著在表上加一個標志字段來描述該行是否已經被修改然後關閉該cursor然後再打開該cursor每次可以修改行
From:http://tw.wingwit.com/Article/program/Oracle/201311/17262.html