使用SQL Plus工具

1、 2 第3章 使用SQL*Plus工具 4本章要點(diǎn)： 了解SQL*Plus工具的功能。 掌握SQL*Plus連接與斷開數(shù)據(jù)庫的多種 方式。 熟練掌握DESCRIBE命令的使用。 熟練掌握各種編輯命令。 掌握臨時(shí)變量和已定義變量的使用。 掌握格式化查詢結(jié)果的設(shè)置。 掌握簡單報(bào)表的創(chuàng)建。 3 3.1 SQL*Plus概述 4本節(jié)將對SQL*Plus工具的主要功 能進(jìn)行簡單的介紹，另外介紹如 何使用SQL*Plus連接和斷開數(shù)據(jù) 庫。 4 3.1.1 SQL*Plus的主要功能 4SQL*Plus工具主要用于數(shù)據(jù)查詢和數(shù)據(jù)處理。利用 SQL*Plus可以將SQL與Oracle專有的PL/SQL結(jié)合起

2、 來進(jìn)行數(shù)據(jù)查詢和處理。 4SQL*Plus工具具備以下功能： 對數(shù)據(jù)表可以執(zhí)行插入、修改、刪除、查詢操作，以及執(zhí)行 SQL、PL/SQL塊。 查詢結(jié)果的格式化、運(yùn)算處理、保存、打印以及輸出Web格式。 顯示任何一個(gè)表的字段定義，并與終端用戶交互。 連接數(shù)據(jù)庫，定義變量。 完成數(shù)據(jù)庫管理。 運(yùn)行存儲在數(shù)據(jù)庫中的子程序或包。 啟動(dòng)/停止數(shù)據(jù)庫實(shí)例。 5 3.1.2 SQL*Plus連接與斷開數(shù)據(jù)庫 41啟動(dòng)SQL*Plus，連接到默認(rèn)數(shù)據(jù)庫 (1)執(zhí)行“開始”“程序”“Oracle OraDb11g_home1”“應(yīng)用程序開發(fā)”“SQL Plus”命令， 打開SQL Plus窗口，顯示登錄界面。

3、 (2)在登錄界面中將提示輸入用戶名，根據(jù)提示輸入相應(yīng) 的用戶名和口令(例如system和admin)后按Enter鍵，SQL*Plus 將連接到默認(rèn)數(shù)據(jù)庫。 (3)連接到數(shù)據(jù)庫之后，顯示SQL提示符，可以輸入相應(yīng) 的SQL命令。例如執(zhí)行SELECT name FROM V$DATABASE 語句，查看當(dāng)前數(shù)據(jù)庫名稱。如圖3-1所示。 6 3.1.2 SQL*Plus連接與斷開數(shù)據(jù)庫 42從命令行連接數(shù)據(jù)庫 要從命令行啟動(dòng)SQL*Plus，可以使用sqlplus命令。 sqlplus命令的一般使用形式如下： |sqlplus user_name / password connect_ident

4、ifier |AS SYSOPER | SYSDBA | SYSASM | / NOLOG 下面以system用戶連接數(shù)據(jù)庫，在DOS窗口中輸入 sqlplus system/adminorcl命令，按Enter鍵后提示 連接到orcl數(shù)據(jù)庫，如圖3-2所示。 7 3.1.2 SQL*Plus連接與斷開數(shù)據(jù)庫 43使用SQL*Plus命令連接與斷開數(shù)據(jù)庫 在SQL*Plus中連接數(shù)據(jù)庫時(shí)，可以使用 CONNECT命令指定不同的登錄用戶，連接 數(shù)據(jù)庫后，SQL*Plus維持?jǐn)?shù)據(jù)庫會話。 CONNECT命令的一般語法形式如下： |CONNECT user_name / password conne

5、ct_identifier | AS SYSOPER | SYSDBA | SYSASM 8 3.2 使用SQL*Plus命令 4Oracle的SQL*Plus是與Oracle進(jìn)行交互的客戶端工具。 4在SQL*Plus中，可以運(yùn)行SQL*Plus命令和SQL*Plus 語句。 4執(zhí)行這些語句后，都可以保存在一個(gè)被稱為SQL BUFFER的內(nèi)存區(qū)域中，但是只能保存一條最近執(zhí)行 的SQL語句。 4除了SQL*Plus語句，在SQL*Plus中執(zhí)行的其他語句 稱之為SQL*Plus命令。 4SQL*Plus命令執(zhí)行后，不保存在SQL BUFFER內(nèi)存區(qū) 域中，一般用來對輸出的結(jié)果進(jìn)行格式化顯示，以

6、便 于制作報(bào)表。 9 3.2.1 使用DESCRIBE命令查看表結(jié)構(gòu) 4DESCRIBE命令可以返回?cái)?shù)據(jù)庫中所存儲的對象的描 述。對于表和視圖等對象來說，DESCRIBE命令可以 列出各個(gè)列以及各個(gè)列的屬性，除此之外，該命令還 可以輸出過程、函數(shù)和程序包的規(guī)范。DESCRIBE命 令的語法如下： DESCRIBE schema. object connect_identifier 4使用DESCRIBE命令查看表的結(jié)構(gòu)時(shí)，如果存在指定 的表，則顯示該表的結(jié)構(gòu)。在顯示表結(jié)構(gòu)時(shí)，將按照 “名稱”、“是否為空？”和“類型”這3列進(jìn)行顯示。 其中 名稱：表示列的名稱。 是否為空？：表示對應(yīng)列的值是否可

7、以為空。如果不可以為空， 則顯示NOT NULL；否則不顯示任何內(nèi)容。 類型：表示列的數(shù)據(jù)類型，并且顯示其精度。 10 3.2.2 使用SQL*Plus語句快速編輯SQL語句 4 Oracle數(shù)據(jù)庫提供了一些SQL*Plus語句，這些語句由相應(yīng)的命令來實(shí)現(xiàn)， 例如APPEND(可以簡寫為A)命令，如表3-3所示。 命 令說 明 APPEND text將text附加到當(dāng)前行之后 CHANGE /old/new將當(dāng)前行中的old替換為new CHANGE /text/刪除當(dāng)前行中指定的text文本 CLEAR BUFFER清除緩存區(qū)中的所有行 INPUT插入不定數(shù)量的命令行 INPUT text插

8、入指定的文本text DEL刪除當(dāng)前行 DEL n刪除第n行(行號從1開始) DEL m n刪除從第m行到第n行之間的命令行 LIST列出緩沖區(qū)中所有的行 LIST n列出第n行 RUN或 /顯示緩沖區(qū)中保存的語句，并運(yùn)行這些語句 n將第n行作為當(dāng)前行 n text使用text文本替代第n行信息 0 text在第一行之前插入text文本 11 3.2.3 使用SAVE命令將緩沖區(qū)內(nèi)容保存到文件 4使用SAVE命令可以將當(dāng)前緩沖區(qū)的內(nèi)容保存到文件 中，這樣，即使緩沖區(qū)中的內(nèi)容被覆蓋，也保留有前 面的執(zhí)行語句。SAVE命令的語法如下所示： SAVE FILE file_name CREATE |

9、REPLACE | APPEND 4語法說明如下。 file_name：表示將SQL*Plus緩沖區(qū)的內(nèi)容保存到由file_name 指定的文件中。 CREATE：表示創(chuàng)建一個(gè)file_name文件，并將緩沖區(qū)中的內(nèi) 容保存到該文件。該選項(xiàng)為默認(rèn)值。 APPEND：如果file_name文件已經(jīng)存在，則將緩沖區(qū)中的內(nèi) 容追加到file_name文件的內(nèi)容之后；如果該文件不存在，則創(chuàng) 建該文件。 REPLACE：如果file_name文件已經(jīng)存在，則覆蓋file_name文 件的內(nèi)容；如果該文件不存在，則創(chuàng)建該文件。 12 3.2.4 使用GET命令讀取文件內(nèi)容到緩沖區(qū) 4使用GET命令的語法如

10、下： GET FILE file_name LIST | NOLIST 4語法說明如下。 file_name：表示一個(gè)指定文件，將該文件的內(nèi)容讀入 SQL*Plus緩沖區(qū)中。 LIST：列出緩沖區(qū)中的語句。 NOLIST：不列出緩沖區(qū)中的語句。 4【例例3.5】將scott_emp_query.sql文件的內(nèi)容讀入到 緩沖區(qū)中，并且輸出和顯示這些內(nèi)容，如下： SQL GET scott_emp_query.sql LIST 1 SELECT empno , job , mgr , sal , deptno 2 FROM scott.emp 3* where empno 7800 4將文件的內(nèi)容

11、讀入到緩沖區(qū)后，就可以使用編輯命令 對這些內(nèi)容進(jìn)行操作了。 13 3.2.5 使用START命令讀取并運(yùn)行文件內(nèi)容 4START命令可以讀取文件中的內(nèi)容到緩沖區(qū)中， 然后在SQL*Plus中運(yùn)行這些內(nèi)容。START命令 的語法如下： START url | file_name 4語法說明如下。 url：用來指定一個(gè)URL地址，例如 http:/host.domain/script.sql。 file_name：指定一個(gè)文件。該命令將file_name文件 的內(nèi)容讀入SQL*Plus緩沖區(qū)中，然后運(yùn)行緩沖區(qū)中 的內(nèi)容。 14 3.2.5 使用START命令讀取并運(yùn)行文件內(nèi)容 4【例例3.6】使用

12、START命令讀取并運(yùn)行 scott_emp_query.sql文件，如下： 4SQL START scott_emp_query.sql EMPNOJOB MGR SAL DEPTNO - 7839PRESIDENT 5000 10 7844SALESMAN 7698 1500 30 7876CLERK 7788 1100 20 7900CLERK 7698 950 30 7902ANALYST 7566 3000 20 7934CLERK 7782 1300 10 已選擇6行。 4上述輸出結(jié)果表示執(zhí)行START命令后，運(yùn)行了保存在 scott_emp_query.sql文件之中的內(nèi)容。 1

13、5 3.2.6 使用EDIT命令編輯緩沖區(qū)內(nèi)容或文件內(nèi)容 4使用EDIT命令，可以將SQL*Plus緩沖區(qū)的內(nèi)容 復(fù)制到一個(gè)名為afiedt.buf的文件中，然后啟動(dòng) 操作系統(tǒng)中默認(rèn)的編輯器打開這個(gè)文件，并且 對于文件內(nèi)容能夠進(jìn)行編輯。在Windows操作 系統(tǒng)中，默認(rèn)的編輯器是Notepad(記事本)。語 法形式如下： EDIT file_name 4其中，file_name默認(rèn)為afiedt.buf，也可以指定 一個(gè)其他的文件。 16 3.2.6 使用EDIT命令編輯緩沖區(qū)內(nèi)容或文件內(nèi)容 4【例例3.7】在SQL*Plus中執(zhí)行EDIT命令，如下： SQL EDIT 已寫入 file af

14、iedt.buf 4這時(shí)，將打開一個(gè)記事本文件afiedt.buf，在該 文件中顯示緩沖區(qū)中的內(nèi)容，文件的內(nèi)容以斜 杠(/)結(jié)束，如圖3-4所示。 17 3.2.7 使用SPOOL命令復(fù)制輸出結(jié)果到文件 4使用SPOOL命令實(shí)現(xiàn)將SQL*Plus中的輸出結(jié)果復(fù)制 到一個(gè)指定的文件中，或者把查詢結(jié)果發(fā)送到打印機(jī) 中，直到使用SPOOL OFF命令為止。SPOOL命令的 語法如下： SPOOL file_name CREATE | REPLACE | APPEND | OFF | OUT 4語法說明如下。 file_name：指定一個(gè)操作系統(tǒng)文件。 CREATE：創(chuàng)建一個(gè)指定的file_name文

15、件。 REPLACE：如果指定的文件已經(jīng)存在，則替換該文件。 APPEND：將內(nèi)容附加到一個(gè)已經(jīng)存在的文件中。 OFF：停止將SQL*Plus中的輸出結(jié)果復(fù)制到file_name文件中， 并關(guān)閉該文件。 OUT：啟動(dòng)該功能，將SQL*Plus中的輸出結(jié)果復(fù)制到 file_name指定的文件中。 18 3.2.8 使用臨時(shí)變量 4在Oracle數(shù)據(jù)庫中，可以使用變量來編寫通用的SQL 語句，在運(yùn)行SQL語句時(shí)，為變量輸入值，就會在 SQL語句中將變量替換成這些值。 1使用&符號表示臨時(shí)變量 |在在SQL語句中，如果在某個(gè)變量前面使用了語句中，如果在某個(gè)變量前面使用了&符符 號，那么就表示該變量是

16、一個(gè)臨時(shí)變量。執(zhí)行號，那么就表示該變量是一個(gè)臨時(shí)變量。執(zhí)行 SQL語句時(shí)，系統(tǒng)會提示用戶為該變量提供一個(gè)語句時(shí)，系統(tǒng)會提示用戶為該變量提供一個(gè) 具體的數(shù)據(jù)。具體的數(shù)據(jù)。 2使用&符號表示臨時(shí)變量 |在在SQL語句中，如果希望重新使用某個(gè)變量并語句中，如果希望重新使用某個(gè)變量并 且不希望重新提示輸入值，那么可以使用且不希望重新提示輸入值，那么可以使用&符符 號來定義臨時(shí)變量。號來定義臨時(shí)變量。 19 3.2.8 使用臨時(shí)變量 43使用SET VERIFY和SET DEFINE命令 在使用臨時(shí)變量時(shí)，還可以使用SET VERIFY命令和 SET DEFINE命令，其中 SET VERIFY ON

17、| OFF ：用來指定是否輸出原值 和新值信息。 SET DEFINE：用于指定一個(gè)除字符&之外的字符， 作為定義變量的字符。 44在腳本文件中使用臨時(shí)變量 在創(chuàng)建的腳本文件中也可以使用臨時(shí)變量。在運(yùn)行該 腳本文件時(shí)，需要為臨時(shí)變量傳遞相應(yīng)的變量值。 20 3.2.9 使用已定義變量 41使用DEFINE命令 DEFINE命令用來創(chuàng)建一個(gè)數(shù)據(jù)類型為CHAR 的變量，該命令的語法形式如表3-4所示。 命 令說 明 DEFINE顯示所有的已定義變量 DEFINE variable顯示指定變量的名稱、值和其數(shù)據(jù)類型 DEFINE variable = value 創(chuàng)建一個(gè)CHAR類型的用戶變量，并且

18、為該變量 賦初始值 21 3.2.9 使用已定義變量 42使用ACCEPT命令 使用ACCEPT命令也可以定義變量，并且定制 一個(gè)用戶提示，用于提示用戶輸入指定變量的 數(shù)據(jù)。 ACCEPT命令的語法如下： |ACCEPT variable data_type FORMAT format DEFAULT default | PROMPT text | NOPROMPT HIDE 22 3.3 格式化查詢結(jié)果 4SQL*Plus提供了大量用來格式化結(jié)構(gòu)集的命令。 這些命令包括COLUMN、PAGESIZE、 LINESIZE、TTITLE和BTITLE等。 4在使用格式化命令時(shí)，應(yīng)該遵循下面一些規(guī)

19、則： 格式化命令設(shè)置之后，該命令一直起作用，直到會話 結(jié)束或者下一個(gè)格式化命令的設(shè)置。 每一次報(bào)表結(jié)束后，重新設(shè)置SQL*Plus默認(rèn)值。 如果為某個(gè)列指定了別名，那么必須引用該別名，而 不能使用列名。 23 3.3.1 格式化列的顯示效果 4COLUMN命令可對列的顯示效果(包括列標(biāo)題 和列數(shù)據(jù))進(jìn)行格式化。命令語法如下： COLUMN column_name | ALIAS alias options 4語法說明如下。 column_name：指定列名。 ALIAS alias：指定要格式化的列的別名。 Options：指定用于格式化列或別名的一個(gè)或 者多個(gè)選項(xiàng)。 24 3.3.2 設(shè)置一

20、頁顯示多少行數(shù)據(jù) 4 使用PAGESIZE命令，可以設(shè)置每一頁的大小，從而控制每一 頁顯示的數(shù)據(jù)量。PAGESIZE命令的語法如下： SET PAGESIZE n |其中，參數(shù)其中，參數(shù)n表示每一頁大小的正整數(shù)，最大值可以為表示每一頁大小的正整數(shù)，最大值可以為50000，默認(rèn)值為，默認(rèn)值為14。 4 【例例3.19】使用PAGESIZE命令，設(shè)置一頁顯示20行信息。然 后再執(zhí)行前面的SELECT語句，如下： SQL SET PAGESIZE 20 SQL / 員工編號 員工姓名上級編號受雇日期員工工資 - - - 7369SMITH 790217-12月-80 $800.00 7499ALLE

21、N 769820-2月 -81 $1,600.00 . 7902FORD 756603-12月-81 $3,000.00 7934MILLER 778223-1月 -82 $1,300.00 已選擇14行。 4 從輸出結(jié)果可以看出，所有的數(shù)據(jù)在一頁內(nèi)進(jìn)行顯示。 25 3.3.3 設(shè)置一行顯示多少個(gè)字符 4使用LINESIZE命令可以設(shè)置一行數(shù)據(jù)可以容納 的字符數(shù)量，默認(rèn)數(shù)量為80。如果LINESIZE的 值比較小，那么表中一行數(shù)據(jù)，有可能在屏幕 上需要分多行顯示；如果LINESIZE的值設(shè)置大 一些，一行數(shù)據(jù)就可以在屏幕的一行中進(jìn)行顯 示了。 4LINESIZE命令的語法如下： SET LIN

22、ESIZE n 4其中，n表示屏幕上一行數(shù)據(jù)可以容納的字符數(shù) 量，有效范圍是132767。 26 3.3.3 設(shè)置一行顯示多少個(gè)字符 4【例例3.20】對scott用戶的表emp執(zhí)行查詢操作 時(shí)，使用LINESIZE命令之前和之后的輸出結(jié)果 如圖3-6所示。 4從圖3-6的輸出結(jié)果可以看出，執(zhí)行SELECT語 句后的輸出結(jié)果中，應(yīng)該一行顯示的數(shù)據(jù)分成 兩行進(jìn)行顯示。 27 3.3.4 創(chuàng)建簡單報(bào)表 4【例例3.21】查詢scott用戶的emp表時(shí)，使用 TTITLE和BTITLE命令設(shè)置頁眉和頁腳信息。 由于需要使用PAGESIZE和LINESIZE等多條命 令設(shè)置輸出格式，所以，將所有的設(shè)置

23、命令定 義在一個(gè)文本文件report.sql中，如圖3-7所示。 28 3.3.4 創(chuàng)建簡單報(bào)表 41為報(bào)表設(shè)置頁眉和頁腳 使用TTITLE和BTITLE命令，分別為報(bào)表設(shè)置頁眉和頁腳信息。 這兩個(gè)命令的使用方法如表3-7所示。 命 令說 明 TTITLE printspec text|variable . | OFF | ON 指定出現(xiàn)在報(bào)表中每一個(gè)頁面頂端的頁 眉。其中printspec的可選值有LEFT、 CENTER、RIGHT、BOLD、 FORMAT text、COL n、SKIP n 和TAB n。ON選項(xiàng)表示啟用設(shè)置； OFF選項(xiàng)表示取消設(shè)置。還可以帶 有用戶變量(內(nèi)容由系統(tǒng)來維護(hù))， 如SQL.PNO頁號、SQL.LNO行號等 BTITLE printspec text|variable . | OFF | ON 指定出現(xiàn)在報(bào)表中每一個(gè)頁面底端的頁 腳 29 3.3.4 創(chuàng)建簡單報(bào)表 42計(jì)算小計(jì) BRENK和COMPUTE的一般用法如下： |BREAK ON column_name SKIP n |COMPUTE func