Oracle编程入门经典 第9章 掌握SQL*Plus
目錄
9.1???????????? 高級啟動選項... 1
9.1.1???????? LOGON.. 2
9.1.2???????? 設置描述文件... 2
9.1.3???????? START. 4
試驗：調用SQL*Plus. 6
工作原理... 6
9.1.4???????? GUI SQL*Plus的啟動選項... 6
9.2???????????? 日常使用SQL*Plus. 7
9.3???????????? SQL*Plus命令入門... 8
9.3.1???????? COLUMN.. 8
9.3.2???????? DESCRIBE. 9
9.3.3???????? PROMPT. 11
9.3.4???????? ACCEPT. 11
9.3.5???????? SHOW.. 14
9.3.6???????? VARIABLE. 16
9.4???????????? 內建的SQL*Plus HELP. 18
9.4.1???????? 內建HELP. 18
9.4.2???????? 安裝HELP. 20
9.4.3???????? 輸入新的HELP主題... 20
9.5???????????? 使用SQL*Plus緩沖區... 20
9.5.1???????? 設置用戶編輯器... 20
9.5.2???????? 使用SQL*Plus進行編輯... 21
9.6???????????? 腳本... 23
9.6.1???????? START，@，@@... 23
9.6.2???????? HOST命令... 25
9.7???????????? 小結... 25
?
?
?
在本意中，我們將要討論：
- SQL*Plus可以使用的高級啟動選項，包括站點描述文件和用戶描述文件
- 怎樣定制用戶的SQL*Plus腳本和輸出
- SQL*Plus的HELP命令
- SQL*Plus緩沖
9.1???????? 高級啟動選項
打開命令窗口，并且輸入命令sqlplus –
C:\Documents and Settings\zhyongfeng>sqlplus -SQL*Plus: Release 9.2.0.1.0 - Production 用法: SQLPLUS [ [<option>] [<logon>] [<start>] ] 其中 <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ] <logon> ::= <username>[/<password>][@<connect_string>] | / | /NOLOG <start> ::= @<URI>|<filename>[.<ext>] [<parameter> ...] "-H" 顯示 SQL*Plus 的版本標幟和使用語法 "-V" 顯示 SQL*Plus 的版本標幟 "-L" 只嘗試登錄一次 "-M <o>" 使用 HTM標志選項 <o> "-R <n>" uses restricted mode <n> "-S" uses silent mode就如用戶所見，這里有大量可用選項。我們依次快速討論它們：
- -或者-H：使用破折號（或者-H）作為啟動選項將會告訴SQL*Plus輸出版本標題以及使用信息。
- -V：這個選項可以告訴SQL*Plus輸出版本標題，以便告訴用戶可以知道用戶正在運行的SQL*Plus的版本。
- -M <o>：這個選項用于標記。當使用SQL*Plus的時候，用戶可以要求工具使用HTML標記輸出數據，來代替普通的文本。對于一些用戶，這可能是一種從SQL*Plus中方便生成HTML標簽數據的方法。
- -R <n>：R代表RESTRICT。通過使用這個選項，用戶就能夠禁用用來與操作系統進行交互的特定SQL*Plus命令。這里有三個層次，隨著編號的增加會有起來越多的限制。限制會禁用如下命令：
- 層次1：EDIT、HOST以及!
- 層次2：EDIT、HOST、!、SAVE、SPOOL、STORE
- 層次3：EDIT、GET、HOST、!、SAVE、SPOOL、START、@、@@、STORE
為了展示這些限制，我們首先來打開一個SQL*Plus會話，輸入dir命令：
C:\Documents and Settings\zhyongfeng>sqlplus scott/tiger@newdb SQL*Plus: Release 9.2.0.1.0 - Production o星期一 4 月 8 11:29:13 2013 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng 連接到: Oracle9i Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production SQL> host Microsoft Windows XP [版本 5.1.2600] (C) 版權所有 1985-2001 Microsoft Corp.由于沒有規定限制選項，所以我們能夠隨意與操作系統進行交互（如輸入host mstsc，進行遠程桌面連接）。然而，如果我們打開另一個會話，并且使用如下命令：
C:\Documents and Settings\zhyongfeng>sqlplus -R 3 scott/tiger@newdb SQL*Plus: Release 9.2.0.1.0 - Production o星期一 4 月 8 11:29:35 2013 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 連接到: Oracle9i Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production SQL> host SP2-0738: 限制的命令 "host" 不可用 SQL>?
- -S：SILENT模式。這個選項會禁止向屏幕回送提示以及與SQL*Plus有關的信息。
9.1.1???? LOGON
如果用戶啟動SQL*Plus，但是沒有規定登錄方法，那么就會提示用戶輸入用戶名和密碼，要注意，密碼不會回顯到屏幕：
C:\Documents and Settings\zhyongfeng>sqlplus SQL*Plus: Release 9.2.0.1.0 - Production o星期一 4 月 8 11:37:21 2013 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 請輸入用戶名: scott 請輸入口令: 連接到: Oracle9i Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production SQL>?
9.1.2???? 設置描述文件
SQL*Plus最重要但是也是最常被忽略的特性就是描述文件（profiles）。描述文件只是包含了SQL*Plus設置、SQL查詢和/或者PL/SQL程序塊的SQL文件。它可以讓用戶設置SQL*Plus環境來滿足用戶的需求。
站點描述文件（stie profile）是由數據庫管理員建立的SQL*Plus腳本，它會在數據庫服務器上使用SQL*Plus的用戶建立默認設置。
這要通過在執行SQL*Plus的計算機建立名為glogin.sql的文件來實現。這個文件可以位于如下位置：
- Unix：$ORACLE_HOME/sqlplus/admin/glogin.sql
- Windows：%ORACLE_HOME%/sqlplus/admin/glogin.sql
Glogin.sql站點描述文件的示例如下所示：
---- Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved. ---- NAME -- glogin.sql ---- DESCRIPTION -- SQL*Plus global login startup file. ---- Add any sqlplus commands here that are to be executed when a user -- starts SQL*Plus on your system ---- USAGE -- This script is automatically run when SQL*Plus starts ---- For backward compatibility SET PAGESIZE 14 SET SQLPLUSCOMPATIBILITY 8.1.7 -- Used by Trusted Oracle COLUMN ROWLABEL FORMAT A15 -- Used for the SHOW ERRORS command COLUMN LINE/COL FORMAT A8 COLUMN ERROR FORMAT A65 WORD_WRAPPED -- Used for the SHOW SGA command COLUMN name_col_plus_show_sga FORMAT a24 -- Defaults for SHOW PARAMETERS COLUMN name_col_plus_show_param FORMAT a36 HEADING NAME COLUMN value_col_plus_show_param FORMAT a30 HEADING VALUE Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng -- Defaults for SET AUTOTRACE EXPLAIN report COLUMN id_plus_exp FORMAT 990 HEADING i COLUMN parent_id_plus_exp FORMAT 990 HEADING p COLUMN plan_plus_exp FORMAT a60 COLUMN object_node_plus_exp FORMAT a8 COLUMN other_tag_plus_exp FORMAT a29 COLUMN other_plus_exp FORMAT a44?
要記住，由于這些文件位于服務器上，所以其它計算機上的SQL*Plus客戶程序不能執行這個腳本。只有從glogin.sql文件所處計算機上執行的SQL*Plus客戶程序才會使用全局站點描述文件。
用戶描述文件是個性化的描述文件，它與站點描述文件類似，但是能夠針對各個用戶考慮。如果在SQL*Plus當前工作目錄下沒有login.sql，SQL*Plus就會依次尋找SQLPATH環境變量中的各個目錄。只要SQL*Plus找到名為login.sql的文件，搜索就會結束。如果沒有這個文件，SQL*Plus就不去執行這個文件。
9.1.3???? START
START命令可以讓用戶有能力從文件中，或者從統一資源定位符（URI）運行腳本。START選項的語法如下所示：
@{ uri | filename[.ext] } [arg…] @@ filename[.ext] [arg…] sta[rt] { uri | filename[.ext] } [arg…]在這里，我們應該注意到由W3C（Worldwide Web Consortium）發布的統一資源定位符的定義，如下所示：
“統一資源定位符（UIR）是短字符串，它可以標識Web中的資源：文檔、圖像、可下載文件、服務、電子郵件信箱以及其它資源。它們可以在各種命名模式和方法下使用資源，例如HTTP、FTP以及采用相同的簡單方式尋址的Internet郵件。”
在W3C的Web站點http://www.w3.org/Addressing/上可以找到更多有關URI和RUL命令和尋址的信息。
簡而言之，URI為SQL*Plus提供了運行在遠程Web上存儲腳本的能力。在SQL*Plus版本9.0.1中，HTTP、FTP和GOPHER協議都在URI中得到支持。
注意：
要注意，這種支持當前只能夠在Windows平臺上得到。
我們能夠使用SQL*Plus去分析表，找到數據庫中所有列的名稱、數據類型、長度、精度。為了完成這項工作，我們要執行如下步驟：
- 啟動SQL*Plus
- 為我們進行數據庫認證（如下所示，啟動和認證能夠在一個單獨的步驟中完成）。
- 輸入DESCRIBE(DESC)命令
- 檢查結果
- 退出工具
連接到:
連接到: Oracle9i Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - ProductioCopyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng SQL> desc employees 名稱 是否為空? 類型 ----------------------------------------- -------- ------------EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) SQL> exit?
這個過程并不困難，但是它明顯很“乏味”。與此相反，我們來編寫一個Shell腳本，為我們完成所有重復的工作，而我們只需輸入絕對必要的信息。將如下腳本保存到desc.sql文件中：
describe &1 exit在這個腳本中，我們要在&1上執行DESCRIBE命令。&字符在SQL*Plus中用來表示變量。在這個例子中。&1表示從命令行傳遞給腳本的第一個參數。我們能夠使用&2、&3等，為多個命令參數使用相同的方法。
為了能夠在Windows平臺上執行這個腳本，可以將如下內容保存到名為desc.cmd的文件中：
sqlplus –S %1 @desc.sql %2在UNIX中，可以將如下Shell腳本保存到名為desc的文件中：
sqlplus –S $1 @desc.sql $2在這個desc文件中，我們使用了-S選項，以便可以抑止SQL*Plus標題和工具輸出，我們傳遞給desc.sql的第一個參數包括了用戶名和密碼。我們為這2個值使用了一個參數原因是，可以讓用戶能夠與遠程數據庫進行連接，并且使用如下方式調用腳本：
desc scott/tiger@newdb employees如下所示：
C:\Documents and Settings\zhyongfeng>desc hr/hr@newdb employees C:\Documents and Settings\zhyongfeng>sqlplus -S hr/hr@newdb @desc.sql employees 名稱 是否為空? 是否為空? ----------------------------------------- -------- ---------------------------EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) 6 Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4) C:\Documents and Settings\zhyongfeng>將以上desc.sql與desc.cmd放在C:\Documents and Settings\zhyongfeng文件下。
試驗：調用SQL*Plus
(1)???? 我們首先要寫Shell腳本中的SQL腳本。將如下腳本為versions.sql：
select banner from v$version / exit?
(1)???? 現在，我們來編寫Shell腳本，并且將它保存為versions.cmd（UNIX上為versions）：
sqlplus -S hr/hr@newdb @versions.sql用戶必須提供用戶自己的用戶名和密碼，來代替hr/hr@neweb
(1)???? 現在調用這個腳本，如下所示：
?
工作原理
在這個例子中，versions腳本會調用SQL*Plus，連接數據庫，并且使用-S選項抑制SQL*Plus消息，然后再調用SQL腳本。
9.1.4???? GUI SQL*Plus的啟動選項
即使GUI SQL*Plus工具會從Windows中的圖標啟動，啟動選項也與工具的命令行版本的選項完全相同。唯一的區別是，在SQL*Plus的GUI模式中，可以使用菜單來執行常見的任務，例如將文件載入到SQL*Plus緩存，將腳本保存到文件系統中，調用定制的文本編輯器，等等。例如，在命令版本中，為了設置查詢結果中的行的寬度，用戶要使用命令：
SET LINESIZE N在GUI中，用戶就可以簡單使用Options | Environment 菜單打開Environment對話框，在那里用戶就可以設定所有的SQL8Plus環境設置，見圖9-1。
圖9-1 GUI SQL*Plus
- 打開DOS窗口，輸入命令sqlplusw打開SQL*Plus
- 現在，使用HR用戶賬號連接數據庫
- 使用如下命令運行我們本章前面建立的SQL腳本desc.sql
@desc.sql
若以sqlplusw –R 3打開SQL*Plus，則會受到限制（以sqlplusw則能完全打開）：
SQL*Plus: Release 9.2.0.1.0 - Production o星期一 4 月 8 17:58:17 2013 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 請輸入用戶名: hr 請輸入口令: ** 連接到: Oracle9i Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production SQL> @c:\desc.sql employees SP2-0738: 限制的命令 "@ (START)" 不可用 SQL>9.2???????? 日常使用SQL*Plus
我們來討論SQL*Plus所提供的，可以支持用戶民數據庫進行日常交互的特性。我們會討論怎自定義工具的輸出，怎樣請求工具幫助。不僅如此，我們還要考慮怎樣使用功能強大的SQL*Plus緩沖器，并且將一些用戶可以在SQL*Plus中使用，能夠更多了解用戶Oracle數據庫的有用腳本打包。
9.3???????? SQL*Plus命令入門
9.3.1???? COLUMN
默認情況下，查詢中的列標題是從數據庫中選擇的列的名稱。當用戶在查詢中使用表達式建立一個列的時候，就會使用表達式本身作為標題。考慮如下示例：
SQL> select ename,sal,sal*1.1 raise from emp; ENAME SAL RAISE ---------- ---------- ----------SMITH 800 880 ALLEN 1600 1760 WARD 1250 1375 JONES 2975 3272.5 MARTIN 1250 1375 BLAKE 2850 3135 CLARK 2450 2695 KING 5000 5500 TURNER 1500 1650 JAMES 950 1045 FORD 3000 3300 ENAME SAL RAISE ---------- ---------- ---------- MILLER 1300 1430 已選擇 12 行。?
如果我們想要擴展這些列標題，讓不熟悉EMP表的用戶更可以容易地理解它們，我們就可以使用COLUMN命令，如下所示：
SQL> column ename format a13 heading "Employee Name" SQL> column sal heading "Salary" SQL> column raise heading "Raise" SQL> select ename,sal,sal*1.1 raise from emp; Employee Name Salary Raise ------------- ---------- ---------- SMITH 800 880 ALLEN 1600 1760 WARD 1250 1375 JONES 2975 3272.5 MARTIN 1250 1375 BLAKE 2850 3135 CLARK 2450 2695 KING 5000 5500 TURNER 1500 1650 JAMES 950 1045 FORD 3000 3300 Employee Name Salary Raise ------------- ---------- ----------MILLER 1300 1430 MILLER 1300 1430 已選擇 12 行。?
在COLUMN命令中還有許多選項可以配置數字列格式。如下表：
表9-1 格式掩碼
| 字符 | 示例 | 解釋 |
| 9 | 9999 | 格式掩碼中的每個字符都代表了查詢結果中所返回數字的點位符 |
| 0 | 999.00 | 每個0都表示了將要在查詢結果中占用一個數位的數字。這個字符通常用于前導或者收尾的0 |
| $ | $9999 | 這個字符會使用美元符號作為數字的前綴 |
| S | S9999 | 為數字顯示符號類型。可以用于查詢結果中的正負數字 |
| PR | 9999PR | 在尖括號串顯示負數 |
| D or . | 99D99，99.99 | 在D或者.的位置上放置小數點 |
| ， | 9，999 | 在，的位置上放置逗號 |
| RN or rn | RN | 根據格式掩碼的大小寫，以大寫或者小寫的方式顯示羅馬數字 |
| DATE | date | 假定數字是格式化的日期，以MM/DD/YY的格式顯示結果值 |
- COLUMN命令還有其它一些可選命令，為了完整起見，我們在這里將其列出：
- CLEAR。這個選項會清除特定列的列設置
- CLEAR COLUMNS。這個選項會為所有的列重置列設置。
- JUSTIFY { LEFT | CENTER | RIGHT }。使用調整命令將會導致列的標題根據用戶傳遞的方向進行調整（左、中或者右）。
- NEW_VALUE <VARIABLE>。這個選項為列值賦予一個由傳遞給命令的名稱所標識的變量。
- PRINT | NOPRINT。這個選項可以控制是否在查詢輸出中顯示列和它的標題。
- ON | OFF。這個選項控制SQL*Plus是否使用COLUMN命令所規定的列屬性。
9.3.2???? DESCRIBE
DESCRIBE命令會返回數據庫中存儲對象的描述。對于表、視圖和指向表（或者視圖）的同義語，DESCRIBE都可以列出各個列以及各個列的屬性。DESCRIBE還會輸出過程、函數以及程序包的規范。
DESCRIBE命令的語法是：
DESC[RIBE] { [schema.]object[@connect_identifier]}我們已經在前面章節中的幾個地方使用了DESCRIBE命令，在這里，我們會提供一個簡要的回顧。
DESCRIBE函數也能夠用于描述數據庫中的PL/SQL單元。過程、函數和程序包都能夠通過SQL*Plus描述，這在編寫使用其它程序單元的PL/SQL代碼時會很有幫助。
在以下的代碼示例中，我們在DBMS_OUTPUT上使用了DESCRIBE，它是SQL*?? Plus中用來向屏幕輸出數據庫程序包：
SQL> desc dbms_output PROCEDURE DISABLE PROCEDURE ENABLE 參數名稱 類型 輸入/輸出默認值? ------------------------------ ----------------------- ------ --------BUFFER_SIZE NUMBER(38) IN DEFAULT PROCEDURE GET_LINE 參數名稱 類型 輸入/輸出默認值? ------------------------------ ----------------------- ------ --------LINE VARCHAR2 OUT STATUS NUMBER(38) OUT PROCEDURE GET_LINES 參數名稱 類型 輸入/輸出默認值? ------------------------------ ----------------------- ------ --------LINES TABLE OF VARCHAR2(255) OUT NUMLINES NUMBER(38) IN/OUT PROCEDURE NEW_LINE PROCEDURE PUT 參數名稱 類型 輸入/輸出默認值? ------------------------------ ----------------------- ------ --------A VARCHAR2 IN PROCEDURE PUT 參數名稱 類型 輸入/輸出默認值? ------------------------------ ----------------------- ------ --------A NUMBER IN PROCEDURE PUT_LINE 參數名稱 類型 輸入/輸出默認值? ------------------------------ ----------------------- ------ --------A VARCHAR2 IN PROCEDURE PUT_LINE 參數名稱 類型 輸入/輸出默認值? ------------------------------ ----------------------- ------ --------A NUMBER IN?
9.3.3???? PROMPT
PROMPT命令會在屏幕上輸出一行數據。這非常有助于在存儲腳本中向用戶傳送信息。例如，如果用戶要編寫一個查詢，提供用戶要看到的數據的純文本描述，用戶就可以使用PROMPT命令完成這項工作。將如下腳本存儲到名為usernames.sql的文件中：
prompt prompt This is a list of users and their default prompt Tablespaces select username,default_tablespace from dba_users /現在使用START或者@命令在SQL*Plus中運行文件。
您必須作為能夠訪問DBA_USERS視圖的用戶運行這個文件。
SQL> @c:\usernames.sql This is a list of users and their default Tablespaces USERNAME DEFAULT_TABLESPACE ------------------------------ -------------------SYS SYSTEM SYSTEM SYSTEM DBSNMP SYSTEM HR EXAMPLE SCOTT USERS OUTLN SYSTEM WMSYS SYSTEM ORDSYS SYSTEM ORDPLUGINS SYSTEM MDSYS SYSTEM CTXSYS DRSYS USERNAME DEFAULT_TABLESPACE ------------------------------ -------------------XDB XDB ANONYMOUS XDB WKSYS DRSYS WKPROXY DRSYS 已選擇 15 行。?
9.3.4???? ACCEPT
如果所交互的用戶正在運行要將輸入數據賦予變量的腳本時，就可以使用ACCEPT命令。ACCEPT可以讓作者規定變量的數據類型、輸入數據的格式，以及當用戶執行未規定值的腳本時的默認值。可以使用HIDE來隱藏正在輸入SQL*Plus的值，這有助于規定密碼這樣的數據。ACCEPT命令的語法如下所示：
ACC[EPT] variable [NUM[BER] | CHAR | DATE] [FOR [MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]數據類型、format、default、ROMPT和HIDE都是編寫ACCEPT命令時的可選設置。然而，如果規定了數據類型或/或者格式，SQL*Plus就會只接受符合數據類型和/或者格式掩碼的合法值。這可以進一步確保正在接受數據的腳本的作者能夠在所使用變量的上下文中工作。考慮如下腳本：
prompt CREATE NEW EMPLOYEE RECORD prompt prompt Enter the employee's information: prompt accept l_ename char format a10 prompt 'Last name:' accept l_empno number format '9999' prompt 'Employee #:' accept l_sal number format '99999.99' prompt 'Salary [1000]:' default '1000.00' accept l_comm number format '99999.99' prompt 'Commission %[0]:'default '0' accept l_hired date format 'mm/dd/yyyy' prompt 'Hire date (mm/dd/yyyy):' prompt List of available jobs: select distinct job from emp order by job / accept l_job char format a9 prompt 'Job:' prompt List of managers and employee numbers: select empno,ename from emp order by ename / accept l_mgr number format '9999' prompt 'Manager”s Employee #:' prompt List of department numbers and names: select deptno,dname from dept order by deptno / accept l_dept number format '99' prompt 'Department #:' insert into emp(empno,ename,job,mgr,hiredate,sal,comm.,deptno) values(&l_empno,'&l_ename','&l_job',&l_mgr, todate('&l_hired','mm/dd/yyyy'),&l_sal,&l_comm,&l_dept) /?
如果用戶輸入錯誤數據（錯誤數據格式），SQL*Plus就會顯示錯誤代碼和消息通知用戶：
SQL> @c:/new_emp.sql CREATE NEW EMPLOYEE RECORD Enter the employee's information: Last name:Ellison Employee #:1 Salary [1000]:0 Commission %[0]:99 Hire date (mm/dd/yyyy):05-MAY-1973 SP2-0685: 日期"05-MAY-1973"無效或格式不匹配"mm/dd/yyyy" Hire date (mm/dd/yyyy):05-05-1975 List of available jobs: JOB ---------ANALYST CLERK MANAGER PRESIDENT SALESMAN Job:GOTOWORK List of managers and employee numbers: EMPNO ENAME ---------- ----------7499 ALLEN 7698 BLAKE 7782 CLARK 7902 FORD 7900 JAMES 7566 JONES 7839 KING 7654 MARTIN 7934 MILLER 7369 SMITH 7844 TURNER EMPNO ENAME ---------- ----------7521 WARD 已選擇 12 行。?
9.3.5???? SHOW
SHOW命令有助于找出與大量不同內容有關的更多信息。包括：
- SQL*Plus環境設置
- PL/SQL代碼或者Oracle正在編譯的數據庫中的錯誤
- init.ora參數
通過使用SHOW命令，用戶就可以快速而方便的了解到SQL*Plus環境的當前設置。當用戶不知道是否可以向文件輸出數據的時候，為了能夠快速地查看SPOOL的配置，我們就可以使用如下命令：
SQL> show spool spool OFF查看SHOW命令可以使用的所有SQL*Plus環境設置，可以使用SHOW ALL。
SQL> show all當使用SQL*Plus編寫PL/SQL，或者建立特定的數據庫對象時，默認情況下，SQL*Plus不會顯示任何詳細的錯誤消息。必須使用SHOW ERRORS命令明確請求它們。
SHOW ERRORS命令可以應用如下對象類型：
- 過程
- 函數
- 程序包
- 程序包體
- 觸發器
- 視圖
- 類型
- 類型體
- 維數
- Java源
- Java類
以下代碼展示了正在編譯出現了故障的PL/SQL過程：
SQL> CREATE OR REPLACE PROCEDURE show_emp(p_empno IN NUMBER) 2 IS 3 BEGIN 4 FOR c1 IN (SELECT * FROM emp WHERE empno=p_empno) LOOP 5 dbms_output.put_line('Name:'|| c1.ename); 6 dbms_output.put_line('Job:'|| c1.job); 7 dbms_output.put_line('Salary:'|| c1.sal); 8 dbms_output.put_line('Commission:'|| c1.comm); 9 END; 10 END show_emp; 11 / 警告: 創建的過程帶有編譯錯誤。這段代碼在Oracle中編譯會導致編譯錯誤。然而，錯誤消息卻未能提供大量信息，用SHOW ERRORS命令可以獲得很大的幫助。SHOW ERRORS的默認行為當前正在執行的SQL*Plus會話中最近編譯的對象顯示錯誤。因此，如果用戶嘗試向Oracle中編譯其它代碼之前執行SHOW ERRORS命令，就會顯示PROCEDURE SHOW_EMP的錯誤：
SQL> show errors; PROCEDURE SHOW_EMP 出現錯誤: LINE/COL ERROR -------- ---------------------------------------- 9/11 PLS-00103: 出現符號 ";"在需要下列之一時? loop 符號 "loop" 被替換為 ";" 后繼續。然而，如果過程代碼與其它的語句同時出現在腳本中，當SQL*Plus顯示沒有太多幫助的錯誤消息時，用戶可以采用2種方法來查看錯誤：
- 用戶可以在每個CREATE語句之后放置SHOW ERRORS消息。
- 用戶可以等到整個腳本執行完畢，使用SHOW ERRORS PROCEDURE SHOW_EMP語法來查看可能在用戶腳本中出現問題的特定對象的錯誤。
如果沒有錯誤，SQL*Plus就會輸出如下文本：
SQL> CREATE OR REPLACE PROCEDURE show_emp(p_empno IN NUMBER) 2 IS 3 BEGIN 4 FOR c1 IN (SELECT * FROM emp WHERE empno=p_empno) LOOP 5 dbms_output.put_line('Name:'|| c1.ename); 6 dbms_output.put_line('Job:'|| c1.job); 7 dbms_output.put_line('Salary:'|| c1.sal); 8 dbms_output.put_line('Commission:'|| c1.comm); 9 END LOOP; 10 END show_emp; 11 / 過程已創建。 SQL> show errors; 沒有錯誤。Oracle中的數據庫管理員需要能夠快速訪問它們數據庫的init.ora參數。通過使用SQL*Plus，就可以很容易地看到數據庫中當前init.ora參數的值。只需使用SHOW PARAMETERS命令，如下所示：
要注意，在這個例子中，您必須作為具有詞典視圖V_$PARAMETERS上SELECT特權的用戶進行連接。
QL> show parameters block; NAME TYPE VALUE ------------------------------------ ----------- ------db_block_buffers integer 0 16 Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng db_block_checking boolean FALSE db_block_checksum boolean TRUE db_block_size integer 8192 db_file_multiblock_read_count integer 16?
通過使用SHOW PARAMETERS BLOCK命令，SQL*Plus可以在屏幕之后執行查詢，并且在屏幕上顯示結果：
SQL> -- show parameters db_ is like running the following query: SQL> SELECT NAME name_col_plus_show_param, 2 TYPE, 3 decode(TYPE,1,'boolean',2,'string',3,'integer',4,'file',6,'big integer','unknown') TYPE, 4 VALUE value_col_plus_show_param 5 FROM v$parameter 6 WHERE upper(NAME) LIKE upper('%block%') 7 ORDER BY name_col_plus_show_param,ROWNUM 8 / NAME TYPE TYPE VALUE ------------------------------------ ---------- ----------- ------------------------------db_block_buffers 3 integer 0 db_block_checking 1 boolean FALSE db_block_checksum 1 boolean TRUE db_block_size 3 integer 8192 db_file_multiblock_read_count 3 integer 16?
9.3.6???? VARIABLE
使用VARIABLE命令可以建立能夠在用戶的SQL*Plus會話期間，在PL/SQL代碼中使用的綁定變量。綁定變量可能為許多類型，包括：
- NUMBER
- CHAR
- CHAR(n [CHAR|BYTE])
- NCHAR
- NCHAR(n)
- VARCHAR2(n[CHAR|BYTE])
- NVARCHAR2(n)
- CLOB
- NCLOB
- REFCURSOR
注意：
要注意，DATE被特別排除在了這個列表之外。
綁定變量提供了一種有效方式，可以在正在執行的SQL*Plus會話中，在SQL語句以及PL/SQL程序塊之間存儲狀。在PL/SQL程序塊的范圍內聲明的變量只能夠在PL/SQL程序塊的執行期間存在。因此，在PL/SQL程序塊中指定的局域PL/SQL變量不能夠在其它的PL/SQL程序塊中引用。綁定變量為此提供了解決方案。
這里是一個在SQL*Plus會話中使用綁定變量的簡單示例。考慮如下名為deptemps.sql的腳本：
SQL> @c:\deptemps.sql 已連接。 Department Names ------------------------------ Accounting Administration Benefits Construction Contracting Control And Credit Corporate Tax Executive Finance Government Sales Human Resources Department Names ------------------------------ IT IT Helpdesk IT Support Manufacturing Marketing NOC Operations Payroll Public Relations Purchasing Recruiting Department Names ------------------------------ Retail Sales Sales Shareholder Services Shipping Treasury 已選擇 27 行。 Enter the department name to report on :Finance PL/SQ過程已成功完成。 18 Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng Department Information Id Name Manager location ------ --------------- --------------- -------------------- 100 Finance Greenberg Seattle Washington Department Employees Job First Name Last Name Phone Number -------------------- ------------ ------------ ------------ Finance Manager Nancy Greenberg 515.124.4569 Accountant Luis Popp 515.124.4567 Jose Manuel Urman 515.124.4469 Ismael Sciarra 515.124.4369 John Chen 515.124.4269 Danie Faviet 515.124.4169 已選擇 6 行。?
這里只是最常使用的SQL*Plus命令的簡要概述。用戶還可以使用許多其它命令，在接下來的章節中，我們將要繼續學習怎樣使用SQL*Plus，找到更多有關它們的信息。
9.4???????? 內建的SQL*Plus HELP
9.4.1???? 內建HELP
SQL*Plus的HELP命令是用戶的朋友。試著在SQL提示符下輸入如下命令：
SQL> help index Enter Help [topic] for help. @ COPY PAUSE SHUTDOWN @@ DEFINE PRINT SPOOL / DEL PROMPT SQLPLUS ACCEPT DESCRIBE QUIT START APPEND DISCONNECT RECOVER STARTUP ARCHIVE LOG EDIT REMARK STORE ATTRIBUTE EXECUTE REPFOOTER TIMING BREAK EXIT REPHEADER TTITLE BTITLE GET RESERVED WORDS (SQL) UNDEFINE CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE CLEAR HOST RU WHENEVER OSERROR COLUMN INPUT SAVE WHENEVER SQLERROR COMPUTE LIST SET CONNECT PASSWORD SHOW?
如果用戶用戶收到錯誤：
SQL> help index SP2-0171:HELP not accessible.?
就意味著在用戶的數據庫中沒有安裝幫助系統。為了解決這個問題，可以閱讀以下題為“安裝HELP”的部分。
HELP索引是可以在幫助系統中使用的單詞列表。簡單輸入help[topic]就可以查看到特定單詞的注解：
SQL> help column COLUMN ------Specifies display attributes for a given column, such as: - column heading text - column heading alignment - data format - column data wrapping Also lists the current display attributes for a single column or all columns. COL[UMN] [{column | expr} [option ...] ] where option represents one of the following clauses: ALI[AS] alias CLE[AR] ENTMAP {ON|OFF} FOLD_A[FTER] FOLD_B[EFORE] FOR[MAT] format HEA[DING] text JUS[TIFY] {L[EFT] | C[ENTER] | C[ENTRE] | R[IGHT]} LIKE {expr | alias} NEWL[INE] NEW_V[ALUE] variable NOPRI[NT] | PRI[NT] NUL[L] text OLD_V[ALUE] variable ON|OFF WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]?
SQL*Plus幫助系統通常可以顯示各個命令的相同屬性。這里是用戶可能會看到的內容：
- 命令的標題
- 命令的文本描述
- 命令的簡寫（例如，COL可以代替COLUMN）
- 可以向命令傳遞的強制參數和可選參數
9.4.2???? 安裝HELP
幫助由SQL*Plus工具提供，但是它要依賴所連接的數據庫配置了幫助系統。默認情況下，它安裝在數據庫上。
用戶可以發現，SQL*Plus幫助只是一個SYSTEM所擁有的稱為HELP的表。當用戶在SQL提示符下使用HELP命令時，SQL*Plus就要在后臺對數據庫進行查詢，并且在工具中向用戶顯示結果。例如，如果用戶使用HELP INDEX，它就等價于SQL*Plus使用了如下查詢：
select * from system.help where topic=upper(‘index’);?
如果數據庫沒有在SYSTEM模式中安裝HELP表那么當SQL*Plus進行查詢的時候，就會獲得指出表不存在的錯誤信息，然后工具就會向用戶返回錯誤：
SP2-0171:HELP not accessible.?
如果用戶數據庫沒有安裝HELP，那么裝配它也很容易。進入
D:\oracle\ora92\sqlplus\admin\help\ @hlpbld.sql helpus.sql?
hlpbld.sql文件將會建立HELP表，并且執行腳本，將幫助文件載入表中。
9.4.3???? 輸入新的HELP主題
由于構成幫助系統的數據存儲在表中，所以用戶可以修改表中的記錄，改變HELP命令的結果。在Oracle 9i中，許多以前版本的SQL*Plus命令現在都已經過時。這些過時的命令（以及它的替代命令）都列在了SQL*Plus的幫助表項中，并且在《SQL*Plus Users Guide and Reference》的附錄F中提供了進一步的解釋（從Oracle 8.1.5以來）。
在這個例子中，我們將要獲取《SQL*Plus Users Guide and Reference》中給出的解釋，并且將這些數據輸入到我們的SQL*Plus幫助索引中。這可以輔助SQL*Plus幫助的用戶，幫助他們配置他們的SQL*Plus環境。
9.5???????? 使用SQL*Plus緩沖區
SQL*Plus會在緩沖區（buffer），或者存儲內存區域（stored memory area）存儲用戶最近運行的命令。通過緩沖區中存儲這些命令，用戶就能夠重新調用，編輯，并且運行它們最近輸入的SQL語句。
有2種方法可以修改在緩沖區中存儲的命令。用戶可以將緩沖區的內容傳遞給Notepad或者vi這樣的編輯器，或者用戶也可以使用SQL*Plus的默認編輯器。這些操作并不互斥；用戶可以在Notepad中編輯整個腳本，保存腳本，并且隨后還可以使用SQL*Plus命令修改緩沖區的內容。很容易就可以知道用戶并沒有受限制于一種操作方式。首先，我們要討論如何使用選擇的編輯器，在SQL*Plus之外修改緩沖區的內容。
9.5.1???? 設置用戶編輯器
為了在SQL*Plus工具中利用vi或者Notepad作為用戶的編輯器，可以使用DEFINE命令，如下所示：
SQL> define_editor=vi SQL> -- or perhaps -- SQL> define_editor=notepad?
可以注意到，就如用戶在回顧以上的用戶描述文件所示的login.sql文件時可以看到的，您可以在用戶描述文件中包含DEFINE_EDITOR設置。這就可以避免用戶在每次啟動SQL*Plus的時候都要手工對其進行設置。一旦設置完畢，用戶就可以使用EDIT命令修改最近使用的SQL語句，或者輸入到SQL*Plus中的PL/SQL程序塊。在緩沖區中沒有內容的時候使用EDIT命令將會導致如下錯誤：
SQL> clear buffer - - this command clears the contents of the SQL*Plus buffer buffer cleared SQL> edit SP2-0107:Nothing to save error.?
通常，當用戶調用EDIT命令的時候，SQL*Plus會獲取最近的SQL語句，或者輸入到SQL*Plus中的PL/SQL程序塊，并且將其它寫入到當前工作目錄下的名為afiedt.buf的文件中。
要注意，一些操作系統具有不同的SQL*Plus編輯文件名稱。這些文件名稱可以使用SET EDITFILE命令進行修改。
文件的內容可以適當地修改。在退出編輯工具的時候，會將緩沖文件的內容自動讀回到SQL*Plus的緩沖區中，并且使用/字符或者RUN命令運行。
不要混淆SQL*Plus緩沖區和編輯文件之間的差異。緩沖區是用戶能夠使用SQL*Plus命令進行修改的內在中的區域。只有當用戶調用EDIT命令的時候，才會使用編輯文件（afiedt.buf）。
這里有一些SQL*Plus命令，在使用外部編輯器的時候會有所幫助：
- ED[IT] [filename[.ext]]——如上所述，EDIT命令可以用來調用應用，并且將SQL*Plus緩沖區的內容傳遞給它。如果忽略文件名參數，就會將afiedt.buf文件的內容傳遞給編輯器。
- SAV[E] filename[.exe]] [CREATE|REPLACE|APPEND]——SAVE命令可以用于將SQL*Plus緩沖區的內容保存到文件中。filename指出了將要存儲內容的文件名稱。有一個可選的參數可以用來規定SAVE命令的內容。
- CREATE——用來生成新文件。如果文件已經存在，這個選項就會失敗。當用戶想要保存文件名還沒有存在的文件時，就可以使用這個選項。
- REPLACE——用來建立新文件的文件名，或者在文件名已經存在的情況下覆寫文件。當文件已經存在，但是用戶不介意覆寫它的時候，就可以使用這個選項。
- APPEND——當需要將SQL*Plus緩沖區的內容增加到文件的末尾時，就可以使用這個選項。這個選項可以用于需要將SQL語句或者PL/SQL程序塊保存到文件中，但是又要保留文件的內容的時候。
9.5.2???? 使用SQL*Plus進行編輯
并不總是有必要使用外部的編輯器來編輯SQL*Plus緩沖區中的命令。在進行簡單改變的時候，用戶可以選擇使用SQL*Plus工具，以及它內建的編輯命令去修改緩沖區中的內容。這里是可以使用的命令的簡短描述：
- L[IST] [terms]——這個命令會列出SQL*Plus中的內容。使用星號（*）標記的行是SQL*Plus緩沖區的當前行。用戶能夠選擇傳遞條件（terms），指出用戶要列出SQL*Plus緩沖區的哪些部分。忽略LIST命令中的條件將會顯示所有的行。條件如下所示：
- n——傳遞單獨的行號只會向用戶顯示這一行。
- *——使用星號會列出當前行。
- LAST——傳遞單詞LAST將會列出緩沖區中的最后一行。
- n n2——傳遞2個使用空格的行編號，將會顯示這2個行及其之間的內容。這種情況下，能夠使用星號（*）和保留字LAST代替n和/或者n2。這將會有效地使用緩沖區中的當前行和/或者緩沖區的最后行顯示一定的范圍。
- A[PPEND] text——當用戶希望向SQL*Plus緩沖區中的行的末尾增加文件的時候，就可以使用Append。文本將會增加到當前行。
- C[HANGE] separator OLD [separator NEW]——CHANGE命令是一種非常靈活地執行表達式替換的方式。只需將需要修改的文本規定為OLD參數
- DEL[ETE] [terms]——使用DELETE命令可以將行從用戶的SQL語句中刪除。用戶可以向DELETE命令傳遞我們在以上的LIST命令中討論那些條件。
- I[INPUT] text——INPUT命令可以用來將行輸入到SQL*Plus緩沖區的當前行之后，新行的內容就是傳遞給INPUT命令的文本。
試驗：使用SQL*Plus編輯器
(1)???? 我們在這里要做的第一件事情就是向SQL*Plus輸入一個非法查詢。
SQL> select department_id,department_name 2 from departmens; from departmens * ERROR 位于第 2 行: ORA-00942: 表或視圖不存在?
(2)???? 現在，我們使用LIST命令，分析語句，尋找錯誤。執行沒有參數的LIST將會顯示SQL*Plus緩沖區的所有內容：
SQL> list 1 select department_id,department_name 2* from departmens?
SQL*Plus輸出了緩沖區的內容，用戶可以在第二行的行編號2之后看到一個星號。這告訴我們第2行是當前的行。這時使用CHANGE或者INPUT命令都會影響這個行。
(3)???? 我們可以看到表名稱DEPARTMENS拼寫錯誤。我們現在可以使用CHANGE命令修復這個錯誤。
SQL> change.mens.ments 2* from departments?
(4)???? 現在，我們來使用INPUT命令，向語句中增加一個WHERE子句以及一個ORDER BY子句：
SQL> l 1 select department_id,department_name 2* from departments SQL> input where department_id between 10 and 40 SQL> l 1 select department_id,department_name 2 from departments 3* where department_id between 10 and 40 SQL> input order by 2 SQL>23 Copyright 2013 All Rights Reserved by ZhouYongFeng , http://www.cnblogs.com/yongfeng 1 select department_id,department_name 2 from departments 3 where department_id between 10 and 40 4* order by 2?
(5)???? 最后，通過使用SQL*Plus執行字符/，用戶就可以執行SQL*Plus緩沖區的內容：
SQL> / DEPARTMENT_ID DEPARTMENT_NAME ------------- ---------------10 Administration 40 Human Resources 20 Marketing 30 Purchasing?
?
9.6???????? 腳本
用戶不必在每次使用的時候都編寫常用的SQL語句和PL/SQL程序塊，而是可以將它們保存到稱為腳本的文件中。這些腳本特別為反復執行的各種任務而設計。
9.6.1???? START，@，@@
我們在前面已經看到了怎樣使用START或者@命令調用腳本。概括而言，START和@命令基本上使用了相同的工作方式。用戶可以傳遞一個文件名作為第一個參數，并且在命令行參數的位置2（及其以后）傳遞用戶正在運行的腳本所希望的參數。
如果要組合必須執行其它許多文件的安裝腳本，這樣的復雜腳本，就會十分復雜。就我們目前已經了解到的，如果我們要運行在當前工作目錄以外的其它目錄中的腳本，我們就會遇到問題。考慮如下2個文件:
第一個文件稱為depts.sql
accept did prompt 'Enter Department Id:' default 10 select * from departments where department_id=&did / @emps.sql &did?
第二個文件稱為emps.sql：
select first_name,last_name,job_id from employees where department_id=&1 /?
現在，如果調用2個腳本都在當前工作目錄中，從SQL*Plus中調用depts.sql就會生成正確的結果：
SQL> @depts Enter Department Id:100 原值 3: where department_id=&did 新值 3: where department_id=100 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- -----------100 Finance 108 1700 原值 3: where department_id=&1 新值 3: where department_id=100 FIRST_NAME LAST_NAME JOB_ID -------------------- ------------------------- ----------Nancy Greenberg FI_MGR Daniel Faviet FI_ACCOUNT John Chen FI_ACCOUNT Ismael Sciarra FI_ACCOUNT Jose Manue Urman FI_ACCOUNT Luis Popp FI_ACCOUNT 已選擇 6 行。?
在這個例子中，腳本都存儲在文件系統的公共目錄中。然而，我們更希望根據腳本的目的，將它們放到合適的目錄中。我們將這2個腳本移動到d:\oracle\samples的目錄中。
在移動文件后，運行腳本就會導致錯誤：
SQL> @depts Enter Department Id: 原值 3: where department_id=&did 新值 3: where department_id=10 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- -----------10 Administration 200 1700 SP2-0310: 無法打開文件"emps.sql"?
p這是因為在depts.sql的第8行，腳本指示SQL*Plus去執行當前工作目錄下的emps.sql文件。由于我們目前處于d:\oracle\working目錄，所以它無法找到我們已經移動到d:\oracle\samples目錄中的emps.sql文件，這就是@@命令的用武之地。
當要用戶要調用隨后會調用其它腳本的腳本時，用戶就可以使用@@命令告訴SQL*Plus，相對于正在運行的文件尋找文件。所以，如果我們使用如下命令：
@@d:\oracle\samples\depts.sql?
SQL*Plus就會尋找到emps.sql文件。
9.6.2???? HOST命令
在SQL*Plus中，用戶可以從工具的內部在操作系統上執行命令。這在很多情況下都會很有幫助，所以，我們來討論一些使用HOST命令的示例。
在SQL*Plus中使用沒有參數的HOST（或者在Uinx上使用！，在VMS上使用$）將會用戶帶到操作系統外殼中。在Windows XP上，使用HOST會將用戶帶入DOS外殼中：
SQL> host Microsoft Windows XP [版本 5.1.2600] (C) 版權所有 1985-2001 Microsoft Corp. D:\OracleCommand>?
9.7???????? 小結
在本意中，我們分析了SQL*Plus，以及它作為數據庫訪問工具所提供的功能。SQL*Plus不是最好的查看工具，它不能夠選擇生成圖形或圖表，但是它可以在編寫SQL語句、編輯SQL和PL/SQL、以及執行啟動和關閉數據庫這樣的數據庫管理任務時，提供很高的效率。
?
文章根據自己理解濃縮，僅供參考。
摘自：《Oracle編程入門經典》 清華大學出版社?http://www.tup.com.cn
from:?http://www.cnblogs.com/yongfeng/archive/2013/04/25/3041580.html
總結
以上是生活随笔為你收集整理的Oracle编程入门经典 第9章 掌握SQL*Plus的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Oracle编程入门经典 第8章 索引
- 下一篇： Oracle编程入门经典 第10章 PL