Oracle中SQL*PLUS使用的一些技巧
2024-08-29 13:40:12
供稿：網友
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
將上面的內容保存為一個文本文件后，以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:/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 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;
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行.
