Sql*plus技巧之SPOOL應用

2024-07-21 02:32:56

字體：大中小

來源：轉載

供稿：網友

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.sql　　c. 在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 off

set feedback off

set newpage none

set pagesize 5000

set linesize 500

set verify off

set pagesize 0

set term off

set trims on

set linesize 600

set heading off

set timing off

set verify off

set numwidth 38

SPOOL c:/drop_table.sql

SELECT 'DROP TABLE ' table_name ';' FROM user_tables;

SPOOL OFF
　　b. 以SCOTT用戶登錄數據庫

SQLPLUS > @ …..gen_dorp_table.sql 　　c. 在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.sql　　2．將一個表中的數據導出生成一個文本文件，列與列之間以”,”隔開

set echo off

set feedback off

set newpage none

set pagesize 5000

set linesize 500

set verify off

set pagesize 0

set term off

set trims on

set linesize 600

set heading off

set timing off

set verify off

set numwidth 38

SPOOL c:/drop_table.sql

select DEPTNO ',' DNAME FROM DEPT;

SPOOL OFF
　　將上面的內容保存為一個文本文件后，的XM}`S!;網(J以scott登錄，執行該文件后顯示結果：

10,ACCOUNTING

20,RESEARCH

30,SALES

40,OperaTIONS　　通過上面的兩個例子，我們可以將：

set echo off

set feedback off

set newpage none

set pagesize 5000

set linesize 500

set verify off

set pagesize 0

set term off

set trims on

set linesize 600

set heading off

set timing off

set verify off

set numwidth 38

SPOOL 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 ,bb　　3．動態生成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.txt 　　5．如何指定缺省的編輯腳本的目錄 　　在sql*plus中，可以用save命令，將上一條執行的sql語句保存到一個文件中，但是如何設置該文件的缺省目錄呢？　　通過SQL> set editfile c:tempfile.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 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);　　注重：上面并不刪除列值為null的行。　　7．如何向數據庫中插入兩個單引號(’’)

Insert inot dept values(35,’aa’’’’bb’,’a’’b’);　　在插入時，用兩個’表示一個’。　　8．如何設置sql*plus的搜尋路徑，這樣在用@命令時，就不用輸入文件的全路徑。 　　設置SQLPATH環境變量。　　如：

SQLPATH = C:/ORANT/DBS;C:/APPS/SCRipTS;C:/MYSCRIPTS 　　9．@與@@的區別是什么？ 　　@等于start命令，用來運行一個sql腳本文件。　　@命令調用當前目錄下的，或指定全路徑，或可以通過SQLPATH環境變量搜尋到的腳本文件。　　@@用在腳本文件中，用來指定用@@執行的文件與@@所在的文件在同一目錄，而不用指定全路徑，也不從SQLPATH環境變量指定的路徑中尋找文件，該命令一般用在嵌套腳本文件中。　　10．&與&&的區別 　　&用來創建一個臨時變量，每當碰到這個臨時變量時，都會提示你輸入一個值。　　&&用來創建一個持久變量，就像用用define命令或帶new_vlaue字句的column命令創建的持久變量一樣。當用&&命令引用這個變量時，不會每次碰到該變量就提示用戶鍵入值，而只是在第一次碰到時提示一次。
　　如，將下面三行語句存為一個腳本文件，運行該腳本文件，會提示三次，讓輸入deptnoval的值：

select count(*) from emp where deptno = &deptnoval;

select count(*) from emp where deptno = &deptnoval;　　將下面三行語句存為一個腳本文件，:RY7供0管k網[運行該腳本文件，則只會提示一次，讓輸入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行。

上一篇：哈希分區表使用時注意事項

下一篇：重建控制文件會導致密碼文件發生變化