官术网_书友最值得收藏!

第8章 SQL*Plus工具介紹

本章介紹SQL*Plus系列產品的使用。SQL*Plus是一個被系統管理員(DBA)和開發人員廣泛使用的功能強大而且很直接的Oracle工具。SQL*Plus可以運行在Oracle運行的任何平臺上,在客戶端可以通過安裝Oracle客戶端軟件時安裝,在服務器端通過安裝Oracle Server軟件時安裝。SQL*Plus可以執行輸入的SQL語句和包含SQL語句的文件,及PL/SQL語句。通過SQL*Plus,可以與數據庫進行“對話”。

iSQL*Plus是在SQL*Plus基礎上開發的基于Web訪問的具有三層結構的新工具,與傳統的命令行模式的SQL*Plus相比,它提供了更為友善的界面,不需要安裝Oracle客戶端就可以用瀏覽器訪問,直接操作數據庫,大大方便了用戶的使用。

本章的主要內容如下:

◎ 如何使用SQL*Plus與數據庫交互

◎ 如何使用SQL*Plus的編輯功能

◎ 如何使用SQL*Plus格式化查詢結果

◎ 如何使用iSQL*Plus

8.1 用SQL*Plus與數據庫交互

為了使用SQL語句及其過程語言PL/SQL(Oracle對標準SQL的擴展)與數據庫進行交互,Oracle提供了SQL*Plus和iSQL*Plus兩個工具,它們都是與Oracle數據庫一起安裝的。通過它們,就可以使用SQL及PL/SQL語句創建并管理數據庫的所有對象。

其中,SQL*Plus是一個基于傳統的C/S兩層結構的SQL開發工具,包括客戶層和服務器層,這兩層既可以在一臺主機上,也可以在不同主機上。SQL*Plus也是要給大家重點介紹的一個工具,本書中絕大多數的SQL和PL/SQL都將在SQL*Plus環境下進行演示。

而iSQL*Plus是基于目前流行的三層模型的B/S結構,不需要單獨安裝,它通過瀏覽器訪問數據庫。

SQL及PL/SQL的使用環境,除了Oracle提供的以外,還有許多第三方廠家的Oracle的開發工具,如TOAD(Tools of Oracle Application Developer)和PL/SQL Developer等,它們均具有與SQL*Plus同樣的功能,甚至還具備了SQL*Plus不具備的許多功能。

SQL工具與數據庫服務器之間的結構和連接如圖8-1所示。

圖8-1 SQL工具與數據庫服務器之間的結構和連接

8.1.1 SQL*Plus的主要功能

SQL*Plus工具主要用來進行數據查詢和數據處理。利用SQL*Plus可將SQL和Oracle專有的PL/SQL結合起來進行數據查詢和處理。SQL*Plus工具具備以下功能:

◎ 插入、修改、刪除、查詢,以及執行SQL、PL/SQL塊。

◎ 查詢結果的格式化、運算處理、保存、打印及輸出Web格式。

◎ 顯示任何一個表的字段定義,并與終端用戶交互。

◎ 連接數據庫,定義變量。

◎ 完成數據庫管理。

◎ 運行存儲在數據庫中的子程序或包。

◎ 啟動/停止數據庫實例,要完成該功能,必須以sysdba身份登錄數據庫。

8.1.2 啟動SQL*Plus連接數據庫

如前所述,SQL*Plus是Oracle系統為用戶提供的使用SQL和PL/SQL進行創建、管理和使用數據庫對象,并與Oracle服務器進行交互的前端工具。可以把它想象成一個編輯器,SQL*Plus為用戶輸入、調試SQL和PL/SQL語句并在其中獲得結果提供了一個環境。

下面分幾種情況介紹在不同環境下啟動SQL*Plus的兩種方法。

1.Oracle數據庫安裝在Windows操作系統下,從命令窗口直接啟動SQL*Plus

單擊“開始”菜單中的“運行”命令,打開“運行”對話框。

輸入sqlplus,如圖8-2所示。(當然,也可以輸入cmd,打開DOS命令行窗口,然后輸入sqlplus命令,結果是一樣的。)

圖8-2 “運行”對話框

如果指定某個具體用戶登錄,則可以輸入sqlplus “scott\tiger”,如圖8-3所示。

如果是以sys dba的身份登錄數據庫,則需要明確指出,可以輸入sqlplus “/as sysdba”,如圖8-4所示。

圖8-3 指定確定的用戶登錄數據庫圖

圖8-4 該方式直接以dba的身份登錄本機的數據庫

如果是以無連接的方式進行輸入,則可以輸入sqlplus /nolog,如圖8-5所示,進入sqlplus后再決定以哪種身份連接數據庫(此時需要使用connect命令)。

圖8-6為其中的一張結果圖片,其他情況就不再一一列舉了。

圖8-5 以無連接方式進入sqlplus環境

圖8-6 連接到Oracle數據庫中

輸入SQL命令,在命令的結尾處輸入“;”號,然后按回車鍵執行,如圖8-7所示。如果SQL語句的結尾沒有“;”號,那么,sqlplus會認為SQL語句還沒有結束,而自動往下續行。

如果要退出sqlplus,直接輸入exit即可。

如果不想退出sqlplus,但是想運行Windows操作系統的命令,則可以在SQL>提示符下直接輸入host,然后輸入DOS命令操作,此時輸入exit,即可退回到sqlplus環境。如圖8-8所示。

圖8-7 執行SQL語句

圖8-8 運行操作系統命令

2.從菜單命令中啟動窗口程序形式的SQL*Plus

可執行如下操作:

依次選擇“開始 → 程序 → Oracle-OraDb11g_home1 → 應用程序開發>SQL Plus”菜單命令,彈出輸入用戶名和口令的窗口,如圖8-9所示。其余操作與前面的講述相同。

圖8-9 從開始菜單執行

在Linux操作系統下,啟動SQL*Plus與Windows操作系統類似,以Oracle用戶登錄系統,直接在命令行狀態下執行sqlplus即可,此處不再贅述。

8.2 使用SQL*Plus的編輯功能

作為一個調試和運行SQL語句和PL/SQL的軟件工具,SQL*Plus的功能遠不止在其中輸入命令行,然后執行得到結果這么簡單。使用SQL*Plus可以十分方便地編輯和管理編程的過程。本節主要介紹SQL*Plus的管理功能,主要包括:

◎ 編輯命令;

◎ 保存命令;

◎ 加入注釋;

◎ 運行命令;

◎ 編寫交互命令;

◎ 使用綁定變量;

◎ 跟蹤語句。

8.2.1 SQL語句、PL/SQL塊與SQL*Plus命令的區別

在正式介紹SQL*Plus之前,我們需要明確SQL語句、PL/SQL塊和SQL*Plus命令這幾個概念之間的區別。

(1)SQL語句是以數據庫為操作對象的語言,主要包括數據定義語言DDL、數據操縱語言DML和數據控制語言DCL及數據存儲語言DSL。當輸入SQL語句后,SQL*Plus將其保存在內部緩沖區中。當SQL命令輸入完畢時,有3種方法可以結束SQL命令:在命令行的末尾輸入分號“;”并按回車鍵;在單獨一行上用斜杠(/);或用空行表示。

(2)PL/SQL塊同樣是以數據庫中的數據為操作對象。但由于SQL不具備過程控制功能,所以,為了能夠與其他語言一樣具備面向過程的處理功能,在SQL中加入了諸如循環、選擇等面向過程的處理功能,由此形成了PL/SQL。所有PL/SQL語句的解釋均由PL/SQL引擎來完成,使用PL/SQL塊可編寫過程、觸發器和包等數據庫永久對象。

(3)SQL*Plus命令主要用來格式化查詢結果、設置選擇、編輯及存儲SQL命令、以設置查詢結果的顯示格式,并且可以設置環境選項。

8.2.2 編輯命令

下面介紹使用SQL*Plus的編輯命令時應注意的幾點。

(1)當運行SQL*Plus時,Oracle會在緩沖區中保留最后執行的命令,因而方便了命令的修改和執行。要訪問緩沖區,只要輸入斜杠“/”并按“Enter”鍵即可,該操作將使最后輸入的SQL查詢語句再次被運行,如圖8-10所示。

圖8-10 輸入“/”訪問緩沖區

(2)在很多情況下,在SQL*Plus環境下輸入SQL命令時,往往難以避免出錯。此時,使用SQL*Plus的行編輯功能比重新輸入整行更方便快捷得多。盡管SQL*Plus的行編輯功能相對簡單,但十分有用,可以很快地修改SQL緩沖區中的SQL語句并再次執行。表8-1顯示了SQL*Plus主要的行編輯命令。

表8-1 SQL*Plus行編輯命令

因為SQL*Plus具備的是行編輯功能,所以使用編輯器的關鍵是要明白什么是“當前行”,即允許修改的行。參看圖8-11中的代碼。

圖8-11 代碼1

此時,如果輸入List命令,則會列出在緩沖區中SQL語句的所有行,但要注意的是,前面帶有 *的行是“當前行”,也就是SQL*Plus目前在編輯的行,如圖8-12所示。

“*”表示當前行。如果要將第1行變為當前行,則可以使用命令LIST(縮寫為L)后面直接跟行號1,簡寫為L1,即可以將第1行變為當前行,如圖8-13所示。

圖8-12 代碼2

圖8-13 代碼3

這表示,現在當前行為第1行。

(3)上面介紹的命令行編輯器只允許編輯SQL查詢語句本身。許多情況下,為了使簡單的SQL查詢成為實用的報表,需要許多格式和設置,這項工作最好由全屏編輯器來完成。在SQL*Plus中有一個命令,允許定義直接在SQL*Plus中使用的編輯器,其命令格式為“define_editor=editor_name”,其中的“editor_name”是用戶選擇的編輯器的名稱。在UNIX中,該編輯器名可以是“vi”;在VMS中,該名稱可以是“edt”;在Windows下,編輯器是“記事本”。

用戶可以設置自選的任何編輯器。為了使用以上述格式定義的編輯器,輸入命令“edit”或者用縮寫“ed”,Oracle將使用用戶在“define_editor”命令中定義的編輯器。例如,在Windows xp操作系統下,啟動SQL*Plus后執行“ed”命令,將打開“記事本”程序,緩沖區中的SQL語句自動出現在“記事本”中,如圖8-14所示。

圖8-14 打開“記事本”編輯SQL語句

在“記事本”程序中,用戶可以像編輯普通文本那樣編輯SQL語句,然后保存修改,關閉“記事本”。這時再在SQL*Plus中輸入斜杠“/”,就可以執行更新后的SQL語句了。

8.2.3 保存命令

通過SQL*Plus,可以將命令存儲在命令文件中,當創建了一個命令文件后,可以重新提取、編輯和運行它。使用命令文件保存命令,可以使該命令能夠重復使用,特別是對于復雜的SQL命令和PL/SQL塊。

在SQL*Plus中,可以將一個或者多個SQL命令、PL/SQL塊和SQL*Plus命令存儲在命令文件中,可以使用3種方式在SQL*Plus創建命令文件。

◎ 通過使用SAVE命令,可以直接保存緩沖區中的SQL語句到指定的文件;

◎ 通過使用INPUT輸入命令,可以連同SQL*Plus命令與SQL語句一起保存在緩沖區,然后使用save命令保存在指定的文件中;

◎ 可以直接使用EDIT命令創建文件。

(1)輸入SAVE命令,保存緩沖區中的SQL命令或者PL/SQL塊。

格式為:

SAVE file_name

默認保存在當前路徑下,也可以是絕對路徑,如SAVE D:\test,則會保存在D盤中,文件全名為test.sql 。

SQL*Plus為文件名添加.SQL擴展名,表示它是一個SQL查詢文件。下面的示例顯示了如何保存當前的SQL語句(首先顯示一下緩沖區中的SQL語句),如圖8-15所示。

圖8-15 保存當前的SQL語句

(2)SQL*Plus命令(注意不是SQL語句和PL/SQL塊)不會自動保存到緩沖區中。可以使用INPUT命令,將SQL*Plus命令輸入到緩沖區中,然后使用SAVE命令保存包含SQL*Plus命令在內的查詢語句到指定的文件。

下面的示例顯示如何結合使用IINPUT和SAVE保存命令。

使用INPUT編寫和保存查詢,清除緩沖區;然后,使用IINPUT命令輸入SQL*Plus命令和SQL語句,用SAVE保存。(注意,此處以空行回車),如圖8-16所示。

圖8-16 使用IINPUT和SAVE保存命令

注意

在上面的例子中,SAVE命令后面的REPLACE關鍵字將覆蓋同名的文件。如果不使用這個關鍵字而又恰恰存在該文件名的文件,將不能寫入。

使用INPUT命令將SQL*Plus命令加到緩沖區中,因此,不能在緩沖區中直接執行該命令,否則會報錯。

如果要執行剛剛保存的SQL*Plus命令和SQL命令,需要使用SQL>@filename,如圖8-17所示。

圖8-17 使用IINPUT和SAVE保存命令

8.2.4 加入注釋

在代碼中加入注釋能夠使用戶的編程更具有可讀性。這里我們再系統地討論一下加入注釋的方法及應該注意的一些問題。

可以使用3種方式在命令文件中輸入注釋。

◎ 使用SQL*Plus REMARK命令輸入單行注釋;

◎ 使用SQL注釋定界符/*... */輸入多行注釋的單行注釋;

◎ 使用ANSI/ISO注釋--,用于輸入單行注釋。

1.使用REMARK命令

使用REMARK命令在命令文件的一行上加注釋。

REMARK Commission Report;
REMARK to be run monthly.;
COLUMN LAST_NAME HEADING 'LAST_NAME';
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999;
COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90;
REMARK Includes only salesmen;
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN'
2.使用/*…*/

輸入SQL注釋分隔符/*…*/,例如:

/* Commission Report to be run monthly. */
COLUMN LAST_NAME HEADING 'LAST_NAME';
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999;
COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90;
REMARK Includes only salesmen;
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM EMP_DETAILS_VIEW
/* Include only salesmen.*/
WHERE JOB_ID='SA_MAN'
3.使用--

可以使用ANSI/ISO樣式注釋“--”,例如:

-- Commissions report to be run monthly
DECLARE --block for reporting monthly sales

對于SQL*Plus命令,如果本身在一行,可以只包含“--”,例如,下面的注釋是合法的:

-- set maximum width for LONG to 777
SET LONG 777

如果輸入下面的SQL*Plus命令,SQL*Plus將解釋它為注釋,而不會執行這條命令。

-- SET LONG 777

SQL*Plus不會編譯和執行作為注釋的語句,也沒有SQL或者PL/SQL命令編譯器。它掃描每個新的語句的前面幾個關鍵字,確定命令類型。下面的一些規則,可以幫助讀者更好地使用SQL*Plus。

不要將注釋放在語句前面幾個關鍵字中,例如:

SQL> CREATE OR REPLACE
  2 /* HELLO */
  3 PROCEDURE HELLO AS
  4 BEGIN
  5 DBMS_OUTPUT.PUT_LINE('HELLO');
警告: 創建的過程帶有編譯錯誤。

不要將注釋語句放在語句終止符后面,例如,如果輸入:

SQL> SELECT * FROM SCOTT.EMP;--TESTING
  2

系統認為SQL語句還沒有終止。應將終止符放到注釋語句的后面,例如:

SQL> SELECT * FROM SCOTT.DEPT --GET DEPARTMENT INFORMATION;
DEPTNO DNAME    LOC
------------- ------------------------  -------------
     10 ACCOUNTING                                      NEW YORK
     20 RESEARCH                                        DALLAS
     30 SALES                                           CHICAGO
     40 OPERATIONS                              BOSTON

8.2.5 運行命令

本節我們系統討論運行命令的方法。在前面的介紹中,我們都是采用在SQL命令行的后面加分號(;)來運行命令的。實際上,運行SQL命令和PL/SQL塊有3種方式。

◎ 命令行方式;

◎ SQL緩沖區方式;

◎ 命令文件方式。

1.命令行方式

命令行方式就是前面介紹過的在命令后面加分號(;)作為終止符來運行SQL命令的方式。

2.SQL緩沖區方式

為了以緩沖區方式執行SQL命令或PL/SQL塊,SQL*Plus提供了RUN命令和/(斜杠)命令。RUN命令的語法是:

R[UN]

RUN命令列出并執行當前存儲在緩沖區中的SQL命令或PL/SQL塊。

RUN命令顯示緩沖區中的命令并返回查詢的結果。另外,RUN命令使得緩沖區中的最后一行成為當前行。

/命令類似于RUN命令,它執行存儲在緩沖區中的SQL命令或PL/SQL塊,但不顯示緩沖區的內容。此外,/命令不會使緩沖區中的最后一行成為當前行。

3.命令文件方式

要以命令文件方式運行SQL命令、SQL*Plus命令或PL/SQL塊,有兩種命令:

START
@(讀作“at”)

START命令的語法如下:

START file_name[.sql] [arg1 arg2]

參數file_name[.ext]代表用戶想運行的命令文件,如果省略擴展名,那么,SQL*Plus將使用默認的命令文件擴展名(通常為.sql)。

SQL*Plus在當前目錄下查找具有在START命令中指定的文件名和擴展名的文件。如果沒有找到符合條件的文件,SQL*Plus將在SQLPATH環境變量定義的目錄中查找該文件。在參數文件中也可以包括文件的全路徑名,例如:C:\MYSQL\TEST.SQL。

參數部分([arg1 arg2])代表用戶希望傳遞給命令文件中的參數的值,命令文件中的參數必須使用如下格式聲明:&1、&2(或&&1、&&2)。如果輸入一個或多個參數,SQL*Plus使用這些值替換命令文件中的參數。第一個參數替代每個&1,第二個參數替代每個&2,依此類推。

@命令的功能與START命令非常類似,唯一的區別就是@命令既可以在SQL*Plus會話內部運行,又可以在啟動SQL*Plus時的命令行級別運行,而START命令只能在SQL*Plus會話內部運行。

此外,使用EXECUTE命令能夠直接在SQL*Plus提示符狀態下執行單條PL/SQL語句,而不需要從緩沖區或命令文件中執行。EXECUTE的主要用途是運行涉及函數或存儲過程的PL/SQL語句。

8.2.6 編寫交互命令

使用SQL*Plus可以編寫交互命令,使得最終用戶能夠:

◎ 定義用戶變量;

◎ 在命令中替代值;

◎ 使用START命令提供值;

◎ 提示值。

1.定義用戶變量

用戶可以定義用戶變量,稱為User variables,可以在命令文件中重復使用。注意:用戶還可以在標題中定義用戶變量。下列的示例顯示如何定義用戶變量。

定義用戶變量L_NAME,賦值為“SMITH”,輸入下面的命令:

DEFINE MYFRIEND=SMITH

如果需要列出所有的變量定義,可以在命令提示符處輸入DEFINE。

SQL> DEFINE
DEFINE _SQLPLUS_RELEASE = "900010001" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production" (CHAR)
DEFINE _O_RELEASE = "900010101" (CHAR)
DEFINE MYFRIEND = "SMITH" (CHAR)

注意

任何用戶都必須使用DEFINE顯示定義。如果需要刪除一個用戶變量,可以使用SQL*Plus命令UNDEFINE加上變量名。

2.在命令中替代值

如果希望編寫一個查詢,列出具有某種工作的雇員信息,那么用戶很容易想到使用WHERE子句,設置關于工作列名的條件,例如:

WHERE JOB=’SALES’

可是,如果需要列出不同工作的雇員信息,且工作部門由最終的用戶選擇而不是SQL語句的輸入者,那么,就需要替代變量編寫交互SQL命令。

替代變量是在用戶變量名前加入一個或者兩個&變量。當SQL*Plus遇到替代變量時,SQL*Plus執行命令,就如同包含替代變量的值。例如,如果變量SORTCOL包含值JOB_ID,變量MYABLE包含值EMP_DETAILS_VIEW,SQL*Plus執行命令:

SELECT &SORTCOL, SALARY
FROM &MYTABLE
WHERE SALARY>12000;

等價于執行下面的SQL語句:

SELECT JOB_ID, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000;

但是,要明確如何使用替代變量,及何時使用替代變量。除了在語句的第一個關鍵詞之外,用戶可以在SQL和SQL*Plus命令的任何位置使用替代變量(例如,不能用替代變量替代查詢關鍵詞SELECT)。

下面創建一個包含替代變量的交互SQL命令。

SQL> CLEAR BUFFER
buffer已清除
SQL> INPUT
  1 SELECT ENAME,JOB,SAL
  2 FROM SCOTT.EMP E,SCOTT.DEPT D
  3 WHERE E.DEPTNO=D.DEPTNO
  4 AND DNAME=&DNAME
  5
SQL> SAVE TEST
已創建文件 TEST.sql

上面的例子用替代變量DNAME來代替具體的部門名稱,由最終執行該命令的用戶來指定而不是由SQL命令的編寫者來指定。從這個角度來說,該命令對于命令的開發者和使用者是交互的。

下面,我們運行此命令:

SQL> @TEST
輸入 dname的值: 'SALES'
原值 4: AND DNAME=&DNAME
新值 4: AND DNAME='SALES'
ENAME JOB SAL
-------------- ------------ -------------
ALLEN    SALESMAN                1600
WARD     SALESMAN                1250
MARTIN   SALESMAN                1250
BLAKE    MANAGER                 2850
TURNER   SALESMAN                1500
JAMES    CLERK           950
已選擇6行。

如果希望在替代變量后添加字符,可以使用“.”將變量和字符隔開。此外,還可以在變量上加引號,這樣對于字符串變量,執行命令時,用戶就無須使用引號了。例如,我們對上面保存得到的命令TEST.sql進行一些修改,如下所示。

SQL> GET TEST
  1 SELECT ENAME,JOB,SAL
  2 FROM SCOTT.EMP E,SCOTT.DEPT D
  3 WHERE E.DEPTNO=D.DEPTNO
  4* AND DNAME=&DNAME
SQL> C/&DNAME/'&DNAME.S'
  4* AND DNAME='&DNAME.S'
SQL> SAVE TEST REPLACE
已寫入文件 TEST.sql
SQL> @TEST
輸入 dname的值: SALE
原值 4: AND DNAME='&DNAME.S'
新值 4: AND DNAME='SALES'
ENAME JOB SAL
-------------- ------------ -------------
ALLEN    SALESMAN        1600
WARD     SALESMAN        1250
MARTIN   SALESMAN        1250
BLAKE    MANAGER         2850
TURNER   SALESMAN        1500
JAMES    CLERK   950
已選擇6行。

在什么情況下使用兩個&表示替代變量呢?請看下面這個例子。

SQL> SELECT ENAME,&COL FROM SCOTT.EMP
  2 ORDER BY &COL;
    輸入 col的值: SAL
    原值 1: SELECT ENAME,&COL FROM SCOTT.EMP
    新值 1: SELECT ENAME,SAL FROM SCOTT.EMP
    輸入 col的值: SAL
    原值 2: ORDER BY &COL
    新值 2: ORDER BY SAL
ENAME SAL
----------------- -------------
SMITH    800
JAMES    950
ADAMS    1100
WARD     1250
MARTIN   1250
MILLER   1300
TURNER   1500
ALLEN    1600
CLARK    2450
BLAKE    2850
JONES    2975
ENAME SAL
----------------- -------------
SCOTT    3000
FORD     3000
KING     5000
已選擇14行。

在上面的例子中,兩次用到了變量COL,而實際上,編寫者的本意是按照執行命令的用戶指定的列進行查詢,并根據該列進行排序查詢結果。因此,這里可以使用兩個&符號來標識替代變量,這樣在運行時就只需輸入一次變量的值,如下所示。

SQL> SELECT ENAME,&&COL2 FROM SCOTT.EMP
  2 ORDER BY &&COL2;
    輸入 col2的值: SAL
    原值 1: SELECT ENAME,&&COL2 FROM SCOTT.EMP
    新值 1: SELECT ENAME,SAL FROM SCOTT.EMP
    原值 2: ORDER BY &&COL2
    新值 2: ORDER BY SAL
ENAME SAL
----------------- -------------
SMITH    800
JAMES    950
ADAMS    1100
WARD     1250
MARTIN   1250
MILLER   1300
TURNER   1500
ALLEN    1600
CLARK    2450
BLAKE    2850
JONES    2975
ENAME SAL
----------------- -------------
SCOTT    3000
FORD     3000
KING     5000
已選擇14行。

在上面的例子中,不再使用變量COL是由于已經執行過交互命令,變量COL已經被賦值;而使用兩個&標識變量,如果系統發現該變量已經賦值,則不再要求用戶輸入,而直接使用原來的賦值。例如,執行如下命令,就會發現系統不再需要用戶輸入變量COL的值了。

SQL> SELECT ENAME,&&COL FROM SCOTT.EMP
  2 ORDER BY &&COL;
    原值 1: SELECT ENAME,&&COL FROM SCOTT.EMP
    新值 1: SELECT ENAME,SAL FROM SCOTT.EMP
    原值 2: ORDER BY &&COL
    新值 2: ORDER BY SAL
ENAME SAL
----------------- -------------
SMITH    800
JAMES    950
ADAMS    1100
WARD     1250
MARTIN   1250
MILLER   1300
TURNER   1500
ALLEN    1600
CLARK    2450
BLAKE    2850
JONES    2975
ENAME SAL
----------------- -------------
SCOTT    3000
FORD     3000
KING     5000
已選擇14行。

用戶只需使用DEFINE命令就會發現:一經賦值,這個變量的值就保留在SQL*Plus中,如果再在命令中用到這個變量,系統直接將該值賦給變量。

SQL> DEFINE
DEFINE _SQLPLUS_RELEASE = "900010001" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production" (CHAR)
DEFINE _O_RELEASE = "900010101" (CHAR)
DEFINE MYFRIEND = "SMITH" (CHAR)
DEFINE DNAME = "SALE" (CHAR)
DEFINE COL = "SAL" (CHAR)
DEFINE COL2 = "SAL" (CHAR)
3.使用START命令提供值

在編寫SQL*Plus命令時,也可以使用START命令將命令文件的參數值傳給替代變量。為此,僅需將符號(&)置于命令文件數字前,替換替代變量。當每次運行此命令文件時,STRAT使用第一個值替換每個&1,使用第二個值替換&2。

例如,將下面的命令包含在命令文件MYFILE中。

SELECT * FROM EMP_DETAILS_VIEW
WHERE JOB_ID='&1'
AND SALARY='&2';

在下面的STRAT命令中,SQL*Plus將使用CLEARK替換&1,使用3100替換&2:

START MYFILE PU_CLERK 3100
4.與用戶通信

在SQL*Plus中,可以使用3個命令——PROMPT、ACCEPT和PAUSE與最終用戶進行通信。這些命令可以用來發送消息到屏幕,接受最終用戶的輸入。用戶可以使用PROMPT和ACCEPT自定義值的提示,SQL*Plus自動生成替代變量。

PROMPT在屏幕上顯示定義的消息,使用此消息引導用戶進行操作。ACCEPT提示用戶輸入值,將輸入的值存儲在定義的變量中。

例如,下面的代碼將提供報告標題,并將其存儲在變量MYTITLE中。

SQL> CLEAR BUFFER
buffer已清除
SQL> INPUT
  1 PROMPT Please input a title
  2 ACCEPT MYTITLE PROMPT 'Title:'
  3 TTITLE LEFT MYTITLE SKIP2
  4 SELECT ENAME,JOB
  5 FROM SCOTT.EMP E,SCOTT.DEPT D
  6 WHERE E.DEPTNO=D.DEPTNO
  7 AND DNAME='SALES'
  8
SQL> SAVE TEST1
已創建文件 TEST1.sql

在上面命令文件的第三行中,TTITLE命令是用來為報告設置標題的。運行該命令文件,如下所示。

SQL> @TEST1
Please input a title
Title:Employee in Sales
Employee in Sales
ENAME JOB
--------------- ----------------
ALLEN    SALESMAN
WARD     SALESMAN
MARTIN   SALESMAN
BLAKE    MANAGER
TURNER   SALESMAN
JAMES    CLERK
已選擇6行。

在繼續操作以前,關閉TTITLE命令。

TTITLE OFF

對于上面介紹的替代變量,如果在執行命令時突然要求用戶給一個變量賦值,用戶往往會不知所措。此時,利用PROMPT命令創建提示信息,就可以避免這種尷尬。并且,使用ACCEPT命令還可以制定接受賦值的數據類型。例如,希望用戶輸入合適的部門ID(數字型),可是用戶偏偏要輸入部門名稱(字符串),這樣導致的錯誤往往不能引起用戶的注意。而且,用戶往往會以為是命令編寫時的錯誤而不是自己輸入變量值的時候發生了錯誤。

下面的例子給出了一個范本。

SQL> CLEAR BUFFER
buffer已清除
SQL> INPUT
  1 PROMPT Enter a valid department ID
  2 PROMPT For example 10, 20, 30 or 40
  3 ACCEPT DEPTNO NUMBER PROMPT 'Department ID: '
  4 SELECT * FROM SCOTT.DEPT
  5 WHERE DEPTNO=&DEPTNO
  6
SQL> SAVE TEST2
已創建文件 TEST2.sql

執行命令文件:

SQL> TTITLE OFF
SQL> @TEST2
Enter a valid department ID
For example 10, 20, 30 or 40
Department ID: 20
原值 2: WHERE DEPTNO=&DEPTNO
新值 2: WHERE DEPTNO= 20
    DEPTNO  DNAME LOC
------------------ ----------------- -------------
       20 RESEARCH DALLAS

如果用戶不輸入數字,則會顯示錯誤,并要求用戶再次輸入變量的值。

SQL> @TEST2
Enter a valid department ID
For example 10, 20, 30 or 40
Department ID: RESEARCH
SP4-0425: "RESEARCH"是無效的數字
Department ID:

如果希望在用戶的屏幕上顯示消息,然后當用戶讀取消息后再讓用戶輸入,那么,可以使用SQL*Plus的PAUSE命令。例如,將上面的命令文件TEST2更改為如下所示。

PROMPT Enter a valid department ID
PROMPT For example 10, 20, 30 or 40
PAUSE Press ENTER to continue
ACCEPT DEPTNO PROMPT 'Department ID: '
SELECT * FROM SCOTT.DEPT
WHERE DEPTNO=&DEPTNO

運行命令文件,結果如下:

SQL> @TEST2
Enter a valid department ID
For example 10, 20, 30 or 40
Press ENTER to continue

首先要求用戶按ENTER鍵表示已經閱讀完提示內容,用戶按ENTER鍵后命令才繼續運行。

Department ID: 20
原值 2: WHERE DEPTNO=&DEPTNO
新值 2: WHERE DEPTNO=20
    DEPTNO DNAME LOC
------------------ ----------------- -------------
        20 RESEARCH DALLAS

如果希望在顯示報告之前首先清除屏幕,則可以使用CLEAR命令加上SCREEN。將命令文件TEST2更改為如下所示。

PROMPT Enter a valid department ID
PROMPT For example 10, 20, 30 or 40
PAUSE Press ENTER to continue
ACCEPT DEPTNO PROMPT 'Department ID: '
CLEAR SCREEN
SELECT * FROM SCOTT.DEPT
WHERE DEPTNO=&DEPTNO

8.2.7 使用綁定變量

假設希望顯示SQL*Plus中的PL/SQL子程序使用的變量,或者在多個子程序中使用相同的變量。如果在PL/SQL子程序中聲明變量,則不能在SQL*Plus中顯示,可以在PL/SQL中使用綁定變量訪問來自SQL*Plus的變量。

綁定變量是在SQL*Plus中創建的變量,然后在PL/SQL和SQL中引用,就像在PL/SQL子程序中聲明的變量一樣。我們可以使用綁定變量存儲返回的代碼,調試PL/SQL子程序。

可以使用VARIABLE命令在SQL*Plus中創建綁定變量,例如:

VARIABLE ret_val NUMBER

該命令創建了一個綁定變量,稱為ret_val,數據類型是NUMBER類型。

在PL/SQL中通過鍵入冒號(:)引用綁定變量,例如:

:ret_val := 1;

當需要在SQL*Plus中改變綁定變量的值的時候,必須進入PL/SQL塊中,例如:

SQL> VARIABLE ret_val NUMBER
SQL> BEGIN
  2 :ret_val:=4;
  3 END;
  4 /
PL/SQL過程已成功完成。

該命令將值賦予綁定變量ret_val。

如果需要在SQL*Plus中顯示綁定變量,則可以使用SQL*Plus命令PRINT,例如:

SQL> PRINT RET_VAL
  RET_VAL
----------------
        4

SQL*Plus提供了REFCURSOR綁定變量,使得SQL*Plus能夠提取和格式化PL/SQL塊中包含的SELECT語句返回的結果。REFCURSOR綁定變量能夠用于引用存儲過程中的PL/SQL塊的游標變量,使得用戶能夠將SELECT語句存儲在數據庫中,被SQL*Plus引用。

下面的示例顯示如何創建、引用和顯示REFCURSOR綁定變量,首先,聲明REFCURSOR數據類型的本地綁定變量。

VARIABLE employee_info REFCURSOR

然后,進入在OPEN…FOR SELECT語句的綁定變量,該語句打開一個游標,執行查詢,在本例中,我們將SQL*Plus employee_info變量綁定給游標變量。

BEGIN
OPEN :employee_info FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW WHERE
JOB_ID=’SA_MAN’ ;
END;
/
PL/SQL 過程已成功完成。

SELECT語句的結果現在顯示在SQL*Plus中。

PRINT employee_info
EMPLOYEE_ID SALARY
--------------------------- ------------------------
145 14000
146 13500
147 12000
148 11000
149 10500

PRINT命令同樣關閉游標,如果需要重新打印結果,就需要重新執行PL/SQL塊。

下面演示如何在過程中使用REFCURSOR綁定變量。REFCURSOR綁定變量作為參數傳給過程,參數包含REFCURSOR類型。首先,定義類型。

CREATE OR REPLACE PACKAGE cv_types AS
TYPE EmpInfoTyp is REF CURSOR RETURN emp%ROWTYPE;
END cv_types;
/
包已創建。

然后,創建存儲過程,包含OPEN…FOR SELECT語句。

CREATE OR REPLACE PROCEDURE EmpInfo_rpt
(emp_cv IN OUT cv_types.EmpInfoTyp) AS
BEGIN
OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW -
WHERE JOB_ID=’SA_MAN’ ;
END;
/
過程已創建。

執行帶有SQL*Plus綁定變量的過程。

VARIABLE odcv REFCURSOR
EXECUTE EmpInfo_rpt(:odcv)
PL/SQL 過程已成功完成。

打印綁定變量:

PRINT odcv
EMPLOYEE_ID SALARY
--------------------------- ------------------------
145 14000
146 13500
147 12000
148 11000
149 10500

這個過程可以使用相同或者不同的REFCURSOR綁定變量執行多次。

VARIABLE pcv REFCURSOR
EXECUTE EmpInfo_rpt(:pcv)

其結果為:

PL/SQL 過程已成功完成。

輸入下面的命令:

PRINT pcv

得到的結果如下:

EMPLOYEE_ID SALARY
--------------------------- ------------------------
145 14000
146 13500
147 12000
148 11000
149 10500

同樣,可以在存儲的函數中使用REFCURSOR變量,首先創建一個包含OPEN…FOR SELECT語句的存儲函數。

CREATE OR REPLACE FUNCTION EmpInfo_fn RETURN -
cv_types.EmpInfo IS
resultset cv_types.EmpInfoTyp;
BEGIN
OPEN resultset FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW -
WHERE JOB_ID=’SA_MAN’ ;
RETURN(resultset);
END;
/
函數已創建。

執行函數:

VARIABLE rc REFCURSOR
EXECUTE :rc := EmpInfo_fn

返回結果如下:

PL/SQL 過程已成功完成。

打印綁定變量:

PRINT rc

得到的結果如下:

EMPLOYEE_ID SALARY
--------------------------- ------------------------
145 14000
146 13500
147 12000
148 11000
149 10500

函數可以使用相同的,也可以是不同的綁定變量執行多次。

EXECUTE :rc := EmpInfo_fn

顯示結果如下:

PL/SQL 過程已成功完成。

打印綁定變量:

PRINT rc

輸出結果如下:

EMPLOYEE_ID SALARY
--------------------------- ------------------------
145 14000
146 13500
147 12000
148 11000
149 10500

8.2.8 跟蹤語句

用戶通過SQL優化器和語句執行統計自動獲得執行路徑的報告,該報告在成功執行SQL DML以后生成,對于監視和調整這些語句的性能是非常重要的。

1.控制報告

我們可以設置AUTOTRACE系統變量控制報告。

◎ SET AUTOTRACE OFF:不會生成AUTOTRACE報告,這是默認情況;

◎ SET AUTOTRACE ON EXPLAIN:AUTOTRACE報告只顯示優化器執行路徑的報告;

◎ SET AUTOTRACE ON STATISTICS:AUTOTRACE顯示SQL語句執行的統計;

◎ SET AUTOTRACE ON:AUTOTRACE報告報告優化器執行路徑和SQL語句執行統計;

◎ SET AUTOTRACE TRACEONLY:與SET AUTOTRACE ON 類似,只是不顯示查詢輸出。

要使用這些特性,就必須在方案中創建PLAN_TABLE表,然后,將PLUSTRACE角色賦予你。為此,需要DBA權限進行授權。

在SQL*Plus會話中執行下面的命令創建PLAN_TABLE。

CONNECT HR/HR
@$ORACLE_HOME\RDBMS\ADMIN\UTLXPLAN.SQL

例如:C:\ORACLE\RDBMS\ADMIN\UTLXPLAN.SQL

顯示結果如下:

表已創建。

可以在SQL*Plus會話中使用下面的命令創建PLUSTRACE角色,將該角色授予DBA。

CONNECT PLUSTRACE/PLUSTRACE AS SYSDBA
@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL

顯示結果如下:

SQL> drop role plustrace;
角色已丟棄。
SQL> create role plustrace;
角色已創建。
SQL>
SQL> grant select on v_$sesstat to plustrace;
授權成功。
SQL> grant select on v_$statname to plustrace;
授權成功。
SQL> grant select on v_$session to plustrace;
授權成功。
SQL> grant plustrace to dba with admin option;
授權成功。

創建角色以后,進行授權,執行下面的命令,將PLUSTRACE角色授權給HR用戶。

CONNECT/AS SYSDBA
GRANT PLUSTRACE TO HR;

顯示結果如下:

授權成功。
2.執行計劃

執行計劃顯示了SQL優化器執行查詢的路徑。執行計劃的每行都包含一個序列號,SQL*Plus顯示了父操作的序列號。

執行計劃包含4列,如表8-2所示。

表8-2 執行計劃

列的格式可以使用COLUMN命令進行修改,例如,為了停止PARENT_ID_PLUS_EXP列的顯示,可以輸入下面的命令:

COLUMN PARENT_ID_PLUS_EXP NOPRINT

可以使用EXPLAIN PLAN命令生成執行計劃輸出。

當語句執行時,請求服務器資源,服務器就會生成統計信息,在統計中的客戶就是SQL*Plus。Oracle Net指的是SQL*Plus與服務器之間的進程通信,用戶不能改變統計報告的格式。下面演示如何跟蹤性能統計和查詢執行路徑的語句。首先假定SQL緩沖區中包含下面的語句。

SELECT E.LAST_NAME, E.SALARY, J.JOB_TITLE
FROM HR.EMPLOYEES E, HR.JOBS J
WHERE E.JOB_ID=J.JOB_ID AND E.SALARY>12000

當語句執行時,可以自動執行:

SET AUTOTRACE ON
    /

返回結果如下:

LAST_NAME SALARY JOB_TITLE
------------------------- ------------------ -----------------------------------
King     24000   President
Kochhar  17000   AdministrationVice President
De Haan  17000   Administration Vice President
Russell  14000   Sales Manager
Partners         13500   Sales Manager
Hartstein        13000   Marketing Manager
已選擇6行。
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=59 Bytes=2832)
  1 0 HASH JOIN (Cost=3 Card=59 Bytes=2832)
  2 1 TABLE ACCESS (FULL) OF 'JOBS' (Cost=1 Card=19 Bytes=513)
  3 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=59 Bytes=1239)
Statistics
----------------------------------------------------------
        0 recursive calls
        0 db block gets
        0 consistent gets
        0 physical reads
        0 redo size
        0 bytes sent via SQL*Net to client
        0 bytes received via SQL*Net from client
        0 SQL*Net roundtrips to/from client
        0 sorts (memory)
        0 sorts (disk)
        6 rows processed

也可以跟蹤語句,不用顯示查詢結果,輸入下面的代碼:

SET AUTOTRACE TRACEONLY
    /

顯示結果如下:

已選擇6行。
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=59 Bytes=2832)
  1 0 HASH JOIN (Cost=3 Card=59 Bytes=2832)
  2 1 TABLE ACCESS (FULL) OF 'JOBS' (Cost=1 Card=19 Bytes=513)
  3 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=59 Bytes=1239)
Statistics
----------------------------------------------------------
        0 recursive calls
        0 db block gets
        0 consistent gets
        0 physical reads
        0 redo size
        0 bytes sent via SQL*Net to client
        0 bytes received via SQL*Net from client
        0 SQL*Net roundtrips to/from client
        0 sorts (memory)
        0 sorts (disk)
        6 rows processed

該選項對調試大型查詢時非常有用。

下面演示如何使用數據庫連接跟蹤語句,輸入下面的語句:

SET AUTOTRACE TRACEONLY EXPLAIN
SELECT * FROM HR.EMPLOYEES@SID;

例如:SELECT * FROM HR.EMPLOYEES@luyao

顯示結果如下:

Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=107 Bytes=7276)
  1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=107 Bytes=7276)

有時,我們需要跟蹤并行和分布式查詢,當跟蹤并行和分布式查詢的時候,執行計劃顯示了優化器估計的成本。例如,我們使用并行查詢選項跟蹤語句,輸入下面的代碼:

CREATE TABLE D2_T1 (UNIQUE1 NUMBER) PARALLEL -(DEGREE 6);

顯示結果如下:

表已創建。

再輸入下面的代碼:

CREATE TABLE D2_T2 (UNIQUE1 NUMBER) PARALLEL -
(degree 6);

顯示結果如下:

表已創建。

輸入下面的代碼:

CREATE UNIQUE INDEX D2_I_UNIQUE1 ON D2_T1(UNIQUE1);

顯示結果如下:

索引已創建。

為了創建執行計劃,輸入下面的代碼:

SET LONG 500 LONGCHUNKSIZE 500
SET AUTOTRACE ON EXPLAIN
SELECT /*+ INDEX(B,D2_I_UNIQUE1) USE_NL(B) ORDERED -
*/ COUNT (A.UNIQUE1)
FROM D2_T2 A, D2_T1 B
WHERE A.UNIQUE1 = B.UNIQUE1;

顯示結果如下:

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q2000
3 2 NESTED LOOPS* (Cost=1 Card=41 Bytes=1066) :Q2000
4 3 TABLE ACCESS* (FULL) OF ’D2_T2’ (Cost=1 Card=41 Byte :Q2000
s=533)
5 3 INDEX* (UNIQUE SCAN) OF ’D2_I_UNIQUE1’ (UNIQUE) :Q2000
2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(A1.C0
)) FROM (SELECT /*+ ORDERED NO_EXPAND USE_NL
(A3) INDEX(A3 "D2_I_UNIQUE1") */ A2.C0 C0,A3
.ROWID C1,A3."UNIQUE1" C2 FROM (SELECT /*+ N
O_EXPAND ROWID(A4) */ A4."UNIQUE1" C0 FROM "
D2_T2" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)
A4) A2,"D2_T1" A3 WHERE A2.C0=A3."UNIQUE1")
A1
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT

8.3 使用SQL*Plus格式化查詢結果

本節主要介紹如何格式化查詢結果,生成良好的報告。主要包括以下幾點:

◎ 格式化列;

◎ 使用空間和概述行闡明報告;

◎ 定義頁與報告的標題和維;

◎ 存儲和打印結果;

◎ 創建Web報告。

8.3.1 格式化列

通過SQL*Plus的COLUMN命令,可以改變列的標頭,重新格式化查詢中的列的數據。

1.修改列標頭

當顯示列標題時,可以使用默認的標頭,也可以使用COLUMN命令修改列標頭。下面,我們將介紹如何使用COLUMN命令進行修改。

當顯示查詢結果時,SQL*Plus使用列或者表達式名稱作為列的標題。如果需要改變默認標題,可以使用COLUMN命令,具體格式如下:

COLUMN column_name HEADING column_heading

為了生成一個來自EMP_DETAILS_VIEW的報告,帶有新的標題:LAST_NAME,SALARY,和COMMISSION_PCT,輸入下面的命令:

COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY'
COLUMN COMMISSION_PCT HEADING COMMISSION
SELECT LAST_NAME, SALARY, COMMISSION_PCT
FROM HR.EMP_DETAILS_VIEW
WHERE JOB_ID=’SA_MAN’

顯示結果如下:

LAST NAME MONTHLY SALARY COMMISSION
------------------------- -------------------------- -----------------------
Russell   14000  .4
Partners          13500  .3
Errazuriz         12000  .3
Cambrault         11000  .3
Zlotkey   10500  .2

也可以拆分列標題,例如,輸入下面的命令:

COLUMN SALARY HEADING 'MONTHLY|SALARY'
COLUMN LAST_NAME HEADING 'LAST|NAME'
/

得到的結果如下:

LAST MONTHLY
NAME SALARY COMMISSION
------------------------- -------------------------- -----------------------
Russell   14000  .4
Partners          13500  .3
Errazuriz         12000  .3
Cambrault         11000  .3
Zlotkey   10500  .2

同樣,我們可以設置下畫線字符,為此,輸入下面的命令:

SET UNDERLINE = /

得到的結果如下:

LAST MONTHLY
NAME SALARY COMMISSION
=========== ================== ============
Russell  14000   .4
Partners         13500   .3
Errazuriz        12000   .3
Cambrault        11000   .3
Zlotkey  10500   .2
2.格式化NUMBER列

當顯示NUMBER列時,可以接受SQL*Plus的默認顯示格式,也可以使用COLUMN命令進行修改,下面介紹如何使用COLUMN命令修改默認設置。

NUMBER列的寬度等于標題的寬度或者是FORMAT的寬度加上一個空格。如果沒有使用FORMAT,則該列的寬度至少是SET NUMWIDTH的值。

一般情況下,SQL*Plus盡可能顯示數字,直到SET命令設置的NUMWIDTH變量的值。如果數字多于SET NUMWIDTH的值,則SQL*Plus將截斷該值,使得它到達所允許的最大數字數。

用戶可以在COLUMN命令中使用格式化模型,選擇任何NUMBER列的不同格式,格式化模型是用來顯示數字列的描述方式,使用9s表示數字。

下面介紹如何修改默認顯示。COLUMN命令標識了希望格式化的列和使用的模型,具體如下:

COLUMN column_name FORMAT model

使用格式化模型添加逗號、美元符號等,可以將值限定為給定的十進制數。下面的示例顯示帶有美元符的SALARY,命令如下:

COLUMN SALARY FORMAT $99,990
/

得到的結果如下:

LAST MONTHLY
NAME SALARY COMMISSION
------------------------- -------------------------- -----------------------
Russell  $14,000         .4
Partners         $13,500         .3
Errazuriz        $12,000         .3
Cambrault        $11,000         .3
Zlotkey  $10,500         .2
3.格式化數據類型

當顯示數據類型時,可以接受SQL*Plus的默認顯示格式,也可以使用COLUMN命令進行修改,主要有以下數據類型:

◎ CHAR

◎ NCHAR

◎ VARCHAR2 (VARCHAR)

◎ NVARCHAR2 (NCHAR VARYING)

◎ DATE

◎ LONG

◎ CLOB

◎ NCLOB

當需要改變數據類型的顯示寬度或DATE時,可以使用COLUMN命令,帶上格式化模型,具體格式如下:

COLUMN column_name FORMAT model

如果定義列的寬度小于列的標題,則SQL*Plus截斷標題;如果為LONG,CLOB和NCLOB定義寬度,那么,SQL*Plus使用LONGCHUNKSIZE或者定義的寬度。

例如,設置LAST_NAME列的寬度為4個字符,返回查詢結果,輸入下面的命令:

COLUMN LAST_NAME FORMAT A4
/

得到的結果如下:

LAST MONTHLY
NAME SALARY COMMISSION
------------------------- -------------------------- -----------------------
Russ     $14,000         .4
ell
Part     $13,500         .3
ners
Erra     $12,000         .3
zuriz
Camb     $11,000         .3
rault
Zlot     $10,500         .2
key
4.復制列顯示屬性

當希望給定多個列相同的顯示屬性時,可以在COLUMN命令中使用LIKE子句減少輸入命令的長度。LIKE子句告訴SQL*Plus拷貝前面定義的列顯示屬性給新的列。例如,輸入下面的命令:

COLUMN COMMISSION_PCT LIKE SALARY HEADING BONUS

返回的查詢結果如下:

LAST MONTHLY
NAME SALARY COMMISSION
------------------------- -------------------------- -----------------------
Russell  $14,000         $0
Partners         $13,500         $0
Errazuriz        $12,000         $0
Cambrault        $11,000         $0
Zlotkey  $10,500         $0
5.列出和重新設置列顯示屬性

為了列出給定列的當前顯示屬性,可以使用COLUMN命令,格式如下:

COLUMN column_name

為了顯示所有列的顯示屬性,可以輸入下面的命令:

COLUMN

重新設置列的顯示屬性為默認情況,使用下面的命令格式:

COLUMN column_name CLEAR
6.在外層列值后面打印一行字符

默認情況下,當顯示的值不適合列的寬度時,SQL*Plus將列的值包裝為附加行。如果希望插入一個記錄分隔符,則可以使用SET命令的RECSEP和RECSEPCHAR變量。RECSEP決定何時打印字符行,將RECSEP設置為EACH,即表示在每行后面進行打印;設置為WRAPPED,就表示在包裝行后面打印,設置為OFF表示壓縮打印。RECSEP的默認值為WRAPPED。

首先,在緩沖區中輸入下面的查詢(以空格行終止,僅保存在緩沖區而不執行)。

SELECT LAST_NAME, JOB_TITLE, CITY
FROM HR.EMP_DETAILS_VIEW
WHERE SALARY>12000;

RECSEPCHAR設置在每行中打印的字符,可以將RECSEPCHAR設置為任何字符。例如:

SET RECSEP WRAPPED
SET RECSEPCHAR "-"

顯示JOB_TITLE列的寬度:

COLUMN JOB_TITLE FORMAT A20 WORD_WRAPPED

運行查詢,得到的查詢結果如下:

/
LAST
NAME JOB_TITLE CITY
------------------------- -------------------- ------------------------------
King     President       Seattle
Kochhar  Administration Vice     Seattle
        President
--------------------------------------------------------------------------------------
De Haan  Administration Vice     Seattle
        President
--------------------------------------------------------------------------------------
Hartstein        Marketing Manager       Toronto
Russell  Sales Manager   Oxford
Partners         Sales Manager   Oxford
已選擇6行。
7.使用空格和概述行闡明報告

當在SQL SELECT命令中使用ORDER BY子句時,將在輸出結果中對行數據進行排序。使用SQL*Plus的BREAK和COMPUTE命令可以創建記錄的子集合,添加空格和概述行。

例如,對于下面的查詢,沒有BREAK和COMPUTE命令。

SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM HR.EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID;

得到的結果如下:

        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
20       Hartstein        13000
80       Russell          14000
80       Partners         13500
90       King     24000
90       Kochhar          17000
90       De Haan          17000
已選擇6行。

用戶可以使用BREAK命令,制定某一列作為斷行,對于重復的數據只顯示一次,如下所示:

BREAK ON DEPARTMENT_ID;

執行上面的查詢,得到的結果如下:

        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
20       Hartstein        13000
80       Russell          14000
         Partners         13500
90       King     24000
         Kochhar          17000
         De Haan          17000
已選擇6行。

斷開列時,可以插入空白行,也可以開始一個新頁,插入n個空白行,使用BREAK命令,格式如下:

BREAK ON break_column SKIP n

使用下面的命令跳過頁:

BREAK ON break_column SKIP PAGE

例如,下面的示例表示在一個斷開列的值改變時,插入空白行。

BREAK ON DEPARTMENT_ID SKIP 1

執行查詢,得到的結果如下:

        LAST    MONTHLY
DEPARTMENT_ID     NAME   SALARY
---------------------------- -------------------------- ---------------------
20        Hartstein      13000
80        Russell        14000
         Partners        13500
90        King   24000
         Kochhar         17000
         De Haan         17000
已選擇6行。

還可以使用多空格技巧。假設在ORDER BY子句中有多行,希望在每列值修改時插入空白行,為此,首先清除緩沖區。

CLEAR BUFFER

然后,在緩沖區中輸入下面的命令(以空格行的方式終止):

SELECT DEPARTMENT_ID, JOB_ID, LAST_NAME, SALARY
FROM HR.EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID,JOB_ID

當DEPARTTMENT_ID值修改同時JOB_ID值被修改時,跳過一行,輸入下面的命令:

BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID SKIP 1

為了顯示SKIP PAGE的影響,創建帶有頁碼的TTITLE:

TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO

運行新的查詢,結果如下:

Page: 1
        LAST    MONTHLY
DEPARTMENT_ID    NAME    SALARY
------------------------- -------------------------- ---------------------
20       Hartstein       13000
Page: 2
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
80       Russell          14000
         Partners         13500
Page: 3
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
90       King     24000
         Kochhar          17000
         De Haan          17000
已選擇6行。

如果已經使用BREAK命令將報告的行分為子集,那么,可以對每個子集執行很多計算。為此,可以使用SQL*Plus的COMPUTE函數,語法如下:

BREAK ON break_column
COMPUTE function LABEL label_name OF column column column
... ON break_column

用戶可以包含多個斷列和操作,使用COMPUTE時,包含多個斷列和操作,可以使用下面的命令格式:

BREAK ON break_column_1 SKIP PAGE ON break_column_2 SKIP 1
COMPUTE function LABEL label_name OF column column column
... ON break_column_2

COMPUTE命令如果沒有對應的BREAK命令,將不會產生任何影響,下面列出了計算函數及其功能,如表8-3所示。

表8-3 計算函數

這些函數可以應用于在OF后、ON前的列,計算的值將在不同行顯示。

例如,下面的代碼按照部門計算SALARY的總和,首先列出當前的BREAK定義。

BREAK

顯示結果如下:

break on DEPARTMENT_ID page nodup
on JOB_ID skip 1 nodup

輸入下面的COMPUTE命令,運行當前查詢:

COMPUTE SUM OF SALARY ON DEPARTMENT_ID
/

得到的結果如下:

Page: 1
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
20       Hartstein        13000
*****************               ---------------------
sum             13000
Page: 2
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
80       Russell          14000
        Partners        13500
*****************               ---------------------
sum             27500
Page: 3
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
90       King     24000
         Kochhar          17000
         De Haan          17000
*****************               ---------------------
sum             58000
已選擇6行。

為了計算部門10和部門20的薪水總和,不要打印計算標簽,輸入下面的命令:

COLUMN DUMMY NOPRINT;
COMPUTE SUM OF SALARY ON DUMMY;
BREAK ON DUMMY SKIP 1;
SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY
FROM HR.EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;

得到的結果如下:

Page: 1
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
20       Hartstein        13000
                ---------------------
                13000
Page: 2
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
80       Russell          14000
80       Partners         13500
                ---------------------
                27500
Page: 3
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
90       King     24000
90       Kochhar          17000
90       De Haan          17000
                ---------------------
                58000
已選擇6行。

在報告后面計算薪水:

COLUMN DUMMY NOPRINT;
COMPUTE SUM OF SALARY ON DUMMY;
BREAK ON DUMMY;
SELECT NULL DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY
FROM HR.EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;

得到的結果如下:

Page: 1
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
20       Hartstein        13000
80       Russell          14000
80       Partners         13500
90       King     24000
90       Kochhar          17000
90       De Haan          17000
                ---------------------
                98500
已選擇6行。

如果需要在報告后面計算的總結行,可以使用下面的命令格式。

BREAK ON REPORT
COMPUTE function LABEL label_name OF column column column
... ON REPORT

例如,計算并打印所有銷售的薪水總和,并修改計算標簽。首先,輸入下面的BREAK和COMPUTE命令。

BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF SALARY ON REPORT

然后,輸入并運行新的查詢。

SELECT LAST_NAME, SALARY
FROM HR.EMP_DETAILS_VIEW
WHERE JOB_ID=’SA_MAN’;

得到的結果如下:

Page: 1
LAST MONTHLY
NAME  SALARY
------------------------------------- ------------------
Russell   14000
Partners          13500
Errazuriz         12000
Cambrault         11000
Zlotkey   10500
                ------------------
TOTAL   61000

要打印全部查詢的總和,可以使用下面的命令格式。

BREAK ON break_column ON REPORT
COMPUTE function LABEL label_name OF column ON break_column
COMPUTE function LABEL label_name OF column ON REPORT

同時,用戶可以計算多個總結值和行,可以輸入下面的命令。

COMPUTE SUM OF SALARY COMMISSION_PCT ON REPORT

改變所需查詢的第一行,包含COMMISSION_PCT。

SQL> L1
  1* SELECT LAST_NAME, SALARY
SQL> APPEND , COMMISSION_PCT
  1* SELECT LAST_NAME, SALARY, COMMISSION_PCT

最后,運行修改的查詢,查看結果。

Page: 1
LAST MONTHLY
NAME  SALARY  COMMISSION
------------------------------------- ------------------ -------------------
Russell   14000  .4
Partners          13500  .3
Errazuriz         12000  .3
Cambrault         11000  .3
Zlotkey   10500  .2
        ------------------      -------------------
sum     61000   1.5

用戶還可以在相同的斷列上打印多個總結行,可以將每個總結行的函數包含在COMPUTE命令中。

COMPUTE function LABEL label_name function
LABEL label_name function LABEL label_name ...
OF column ON break_column

如果在OF與ON之間包含了多個列,COMPUTE將計算并打印每個列的值。

例如,計算銷售部門薪水的平均值和總和,首先輸入BREAK和COMPUTE命令。

BREAK ON DEPARTMENT_ID
COMPUTE AVG SUM OF SALARY ON DEPARTMENT_ID

然后,輸入并運行下面的命令。

SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM HR.EMP_DETAILS_VIEW
WHERE DEPARTMENT_ID = 30
ORDER BY DEPARTMENT_ID, SALARY;

得到的結果如下:

Page: 1
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
30       Colmenares       2500
         Himuro   2600
         Tobias   2800
         Baida    2900
         Khoo     3100
         Raphaely         11000
*****************               ---------------------
avg             4150
sum             24900
已選擇6行。

8.3.2 定義頁與報告的標題和維

“頁”是指全屏幕的信息,用戶可以在每頁添加上下標題、設置每頁的行數,及確定每行的寬度。

“報告”是指一個查詢的完整結果,可以在每個報告上加上標題和腳注,就像頁的上下標題一樣。

1.設置上下標題、題頭和腳注

在上面的介紹中,我們已經簡單介紹過使用TTITLE命令設置報告的上標題的方法。同樣,也可以設置每頁的下標題。TTITLE命令定義上標題,BTITTLE命令定義下標題。還可以為每個報告設置題頭,REPHEADER命令定義報告題頭,REPFOOTER命令定義報告腳注。

TTITLE,BTITLE,REPHEADER和REPFOOTER命令包含了子句,用于定義其格式、位置。用戶可以包含多個子句。

TTITLE position_clause(s) char_value position_clause(s) char_value ...
BTITLE position_clause(s) char_value position_clause(s) char_value ...
REPHEADER position_clause(s) char_value position_clause(s) char_value ...
REPFOOTER position_clause(s) char_value position_clause(s) char_value ...

若要在報告的每頁上面添加標題,可以輸入下面的命令。

TTITLE CENTER -
"ACME SALES DEPARTMENT PERSONNEL REPORT"
BTITLE CENTER "COMPANY CONFIDENTIAL"

運行查詢,得到的結果如下:

ACME SALES DEPARTMENT PERSONNEL REPORT  LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
        30       Colmenares       2500
         Himuro   2600
         Tobias   2800
         Baida    2900
         Khoo     3100
         Raphaely         11000
*****************               ---------------------
avg             4150
                COMPANY CONFIDENTIAL
ACME SALES DEPARTMENT PERSONNEL REPORT
        LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
sum             24900
                COMPANY CONFIDENTIAL
已選擇6行。

若要將題頭置于一個報告的上面,可以輸入下面的命令。

REPHEADER PAGE CENTER 'PERFECT WIDGETS'

執行當前查詢:

/

輸出結果如下:

ACME SALES DEPARTMENT PERSONNEL REPORT PERFECT WIDGETS
COMPANY CONFIDENTIAL
ACME SALES DEPARTMENT PERSONNEL REPORT
LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
30       Colmenares       2500
Himuro    2600
Tobias    2800
Baida     2900
Khoo      3100
Raphaely          11000
*****************               ---------------------
avg             4150
COMPANY CONFIDENTIAL
ACME SALES DEPARTMENT PERSONNEL REPORT
LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
sum             24900
COMPANY CONFIDENTIAL
已選擇6行。

同樣,可以定位標題元素,輸入下面的命令。

TTITLE CENTER 'A C M E W I D G E T' SKIP 1 -
CENTER ================== SKIP 1 LEFT 'PERSONNEL REPORT' -
RIGHT 'SALES DEPARTMENT' SKIP 2
SET LINESIZE 60
/

得到的結果如下:

A C M E W I D G E T
==================
PERSONNEL REPORT SALES DEPARTMENT
PERFECT WIDGETS
COMPANY CONFIDENTIAL
A C M E W I D G E T
==================
PERSONNEL REPORT SALES DEPARTMENT
LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
30       Colmenares       2500
Himuro    2600
Tobias    2800
Baida     2900
Khoo      3100
COMPANY CONFIDENTIAL
A C M E W I D G E T
==================
PERSONNEL REPORT SALES DEPARTMENT
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
30       Raphaely         11000
*****************               ---------------------
avg             4150
sum             24900
COMPANY CONFIDENTIAL
已選擇6行。

在TTITLE或BTITTLE命令中使用COL子句,可以縮進標題元素。輸入下面的命令:

TTITLE LEFT 'ACME WIDGET' SKIP 1 -
COL 6 'SALES DEPARTMENT PERSONNEL REPORT' SKIP 2

執行當前查詢,返回結果如下:

ACME WIDGET
SALES DEPARTMENT PERSONNEL REPORT
PERFECT WIDGETS
COMPANY CONFIDENTIAL
ACME WIDGET
SALES DEPARTMENT PERSONNEL REPORT
LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
30       Colmenares       2500
Himuro    2600
Tobias    2800
Baida     2900
Khoo      3100
COMPANY CONFIDENTIAL
ACME WIDGET
SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
30       Raphaely         11000
*****************               ---------------------
avg             4150
sum             24900
COMPANY CONFIDENTIAL
已選擇6行。

當需要輸入一個大于500個的字符擦黑標題時,可以使用SQL*Plus的DEFINE命令:

DEFINE LINE1 = 'This is the first line...'
DEFINE LINE2 = 'This is the second line...'
DEFINE LINE3 = 'This is the third line...'

然后,在TTILTLE或BTITTLE命令中引用變量。

TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1 –
CENTER LINE3
2.顯示頁序號和系統維護值

如果需要顯示當前頁的序號和標題中系統維護值,那么,可以使用下面的命令:

TTITLE LEFT system-maintained_value_name

例如,要在每頁上顯示頁序號,可以輸入下面的命令:

TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' SQL.PNO SKIP 2

執行當前查詢,得到的結果如下:

ACME WIDGET PAGE: 1
PERFECT WIDGETS
ACME WIDGET PAGE: 2
LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
30       Colmenares       2500
Himuro    2600
Tobias    2800
Baida     2900
Khoo      3100
30      Raphaely        11000
*****************               ---------------------
COMPANY CONFIDENTIAL
ACME WIDGET PAGE: 3
LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
avg             4150
sum             24900
COMPANY CONFIDENTIAL
已選擇6行。

還可以格式化標題中系統維護值,命令格式如下:

TTITLE LEFT 'ACME WIDGET' RIGHT 'PAGE:' FORMAT 999 -
SQL.PNO SKIP 2
3.列出、壓縮和恢復頁標題的定義

若要列出標題定義,可以輸入下面的命令:

TTITLE
BTITLE

若要壓縮標題定義,可以輸入下面的命令:

TTITLE OFF
BTITLE OFF

如果需要恢復當前定義,可以輸入下面的命令:

TTITLE ON
BTITLE ON
4.顯示標題的列值

如果希望創建詳細報告,用來在頁的上面顯示改變的主列的值。也可以在上標題引用一個列值,通過將值存儲在變量中,在TTITLE命令中引用變量,使用下面的命令格式定義變量。

COLUMN column_name NEW_VALUE variable_name

下面的示例顯示如何創建詳細報告。詳細報告用于顯示兩個不同經理的雇員號,每個雇員號都在不同的頁上。首先,創建變量MGRVAR,保存當前經理的雇員號。

COLUMN MANAGER_ID NEW_VALUE MGRVAR NOPRINT

由于只需要在標題中顯示經理的雇員號,因而不希望作為細節部分打印。

然后,將標簽和值包含在頁標題中,輸入相應的BREAK命令,壓縮標題。

TTITLE LEFT 'Manager: ' MGRVAR SKIP 2
BREAK ON MANAGER_ID SKIP PAGE
BTITLE OFF

最后,輸入并運行下面的查詢。

SELECT MANAGER_ID, DEPARTMENT_ID, LAST_NAME, SALARY
FROM HR.EMP_DETAILS_VIEW
WHERE MANAGER_ID IN (101, 201)
ORDER BY MANAGER_ID, DEPARTMENT_ID;

得到的結果如下:

Manager: 101
PERFECT WIDGETS
Manager: 101
LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
10       Whalen   4400
40       Mavris   6500
70       Baer     10000
100      Greenberg        12000
110      Higgins          12000
Manager: 201
LAST    MONTHLY
DEPARTMENT_ID    NAME     SALARY
------------------------- -------------------------- ---------------------
20      Fay     6000
已選擇6行。

如果希望在頁的下面打印列的值,可以使用COLUMN命令,格式如下:

COLUMN column_name OLD_VALUE variable_name
5.在標題中顯示當前日期

當然,可以通過在標題中簡單輸入一個值,在報告中加入日期。若要創建變量,可以在SQL*Plus的LOGIN文件中添加命令:

SET TERMOUT OFF
BREAK ON TODAY
COLUMN TODAY NEW_VALUE _DATE
SELECT TO_CHAR(SYSDATE, ’fmMonth DD, YYYY’) TODAY
FROM DUAL;
CLEAR BREAKS
SET TERMOUT ON

啟動SQL*Plus時,這些命令將SYSDATE值放入變量中。若要顯示當前日期,可以在標題中引用_DATE。

6.設置頁維

一般情況下,報告的每頁都包含一定數量的空行。當一個報告太長、SQL*Plus不能在單頁中顯示時,將分為幾頁顯示報告,每個都帶有自己的標題和列標題。SQL*Plus在每頁上顯示的數據的數量依賴于當前頁的維。

SQL*Plus使用的默認頁維如下。

在上標題前面的行的數量:1;

每個頁的行數,從上標題到下標題:24;

每行的字符數:80。

若要設置每頁的開始和上標題之間的行數,可以使用SET命令的NEWPAGE變量。

SET NEWPAGE number_of_lines

若要設置每頁的行數,可以在SET命令中使用PAGESIZE變量。

SET PAGESIZE number_of_lines

例如,將頁設置為66行,清除屏幕,設置行大小為70,可以輸入下面的命令:

SET PAGESIZE 66
SET NEWPAGE 0
SET LINESIZE 70

重新設置PAGESIZE、NEWPAGE和LINESIZE為默認值。

SET PAGESIZE 24
SET NEWPAGE 1
SET LINESIZE 80

使用SHOW命令列出這些變量的當前值。

SHOW PAGESIZE
SHOW NEWPAGE
SHOW LINESIZE

8.3.3 存儲和打印結果

如果用戶希望使用字處理軟件編輯查詢結果,那么,可以使用SPOOL命令將查詢結果發送到文件中,并同時在屏幕上顯示。

SPOOL file_name

當在不同軟件產品之間移動數據時,很有必要使用“flat”文件。使用SQL*Plus創建一個“flat”文件,必須首先輸入SET命令。

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF

將結果發送給文件,使用下面的格式。

SPOOL file_name

將結果發送給打印機,輸入下面的命令。

SPOOL OUT

例如,若要生成一個最終的報告,然后打印結果,應進行如下操作。

首先,使用操作系統文本編輯器的EDIT命令創建命令文件。

EDIT EMPRPT

然后,在文件中輸入下面的命令。

SPOOL TEMP
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN DEPARTMENT_ID HEADING DEPARTMENT
COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999
BREAK ON DEPARTMENT_ID SKIP 1 ON REPORT
COMPUTE SUM OF SALARY ON DEPARTMENT_ID
COMPUTE SUM OF SALARY ON REPORT
SET PAGESIZE 24
SET NEWPAGE 0
SET LINESIZE 70
TTITLE CENTER 'A C M E W I D G E T' SKIP 2 -
LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' -
FORMAT 999 SQL.PNO SKIP 2
BTITLE CENTER 'COMPANY CONFIDENTIAL'
SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM HR.EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;
SPOOL OFF

如果不希望在屏幕上看到輸出,可以添加SET TERMOUT OFF到文件開始處,將SET TERMOUT ON置于文件后端。

8.4 本章小結

本章重點介紹了SQL*Plus工具的使用,特別適用于對數據庫的簡單管理和數據的簡單操作。

主站蜘蛛池模板: 宾川县| 唐河县| 麻城市| 云安县| 浏阳市| 靖宇县| 兰州市| 湘阴县| 房产| 定南县| 措勤县| 宿州市| 汝城县| 张掖市| 固原市| 伽师县| 汽车| 盐山县| 辛集市| 伊吾县| 大姚县| 安岳县| 七台河市| 东山县| 密山市| 虹口区| 礼泉县| 武功县| 临沭县| 牙克石市| 怀柔区| 饶平县| 调兵山市| 梅河口市| 社会| 项城市| 唐海县| 南和县| 昌都县| 招远市| 叶城县|