Oracleg數(shù)據(jù)庫基礎(chǔ)教程XXXX 第章SQLLUS

第5章SQL*PLUS本章內(nèi)容SQL*PLUS工具介紹SQL*PLUS常用命令iSQL*Plus本章要求了解SQL*PLUS工具的應(yīng)用掌握SQL*PLUS基本命令操作了解iSQL*Plus工具的使用5.1SQL*Plus概述概念SQL*Plus工具是隨Oracle數(shù)據(jù)庫服務(wù)器或客戶端的安裝而自動進行安裝的管理與開發(fā)工具，Oracle數(shù)據(jù)庫中所有的管理操作都可以通過SQL*Plus工具完成，同時開發(fā)人員利用SQL*Plus可以測試、運行SQL語句和PL/SQL程序。運行方式命令行方式圖形界面方式(GUI)基于Web的iSQL*Plus方式兩層結(jié)構(gòu)——SQL*Plus客戶端、數(shù)據(jù)庫服務(wù)器三層結(jié)構(gòu)——Web瀏覽器、應(yīng)用服務(wù)器和數(shù)據(jù)庫服務(wù)器在SQL*Plus中執(zhí)行的語句有3種：SQL*Plus命令、SQL語句和PL/SQL程序作用輸入、編輯、存儲、提取、運行和調(diào)試SQL語句和PL/SQL程序開發(fā)、執(zhí)行批處理腳本執(zhí)行數(shù)據(jù)庫管理處理數(shù)據(jù)，生成報表，存儲、打印、格式化查詢結(jié)果檢查表和數(shù)據(jù)庫對象定義啟動SQL*Plus命令行方式：語法：sqlplus[username]/[password][@connect_identifier]|[NOLOG]僅啟動SQL*Plus，不連接數(shù)據(jù)庫運行：啟動：不連接數(shù)據(jù)庫輸入用戶名，并指定要連接的數(shù)據(jù)庫輸入口令注意：為了保證口令安全，可以先不輸入，等提示“輸入口令”時，再輸入！運行：以系統(tǒng)用戶sys身份登錄啟動SQL*Plus@orcl10gassysdba圖形界面方式：選擇【開始】／【程序】／【Oracle–OraDb10g_home1】／【應(yīng)用程序開發(fā)】菜單中選擇SQLPlus命令，出現(xiàn)如圖所示登錄窗口。在【用戶名】文本框中輸入用戶名，如scott；在【口令】文本框中輸入用戶口令，如tiger；在【主機字符串】文本框中輸入數(shù)據(jù)庫名，如orcl10g。Orcl10gassysdba注意：如果以系統(tǒng)用戶名(如sys)登錄，則“主機字符串”處必須輸入登錄連接的身份！SQL*Plus站點配置文件站點配置文件(SiteProfile)是一個在SQL*Plus安裝時自動創(chuàng)建的腳本文件，用于控制SQL*Plus與數(shù)據(jù)庫連接時的會話行為。當用戶啟動SQL*Plus建立與數(shù)據(jù)庫的連接時，Oracle自動讀取該腳本文件，并根據(jù)該文件內(nèi)容設(shè)置SQL*Plus的工作環(huán)境。SQL*Plus站點配置文件是針對所有會話的。在Windows系統(tǒng)中，SQL*Plus站點配置文件默認名稱為glogin.sql,存放在<ORACLE_HOME>\sqlplus\admin文件夾中。SQL*Plus的退出：EXIT或QUIT命令說明：會話指用戶到數(shù)據(jù)庫的指定連接。5.2SQL*Plus常用命令連接命令編輯命令文件操作命令交互式命令顯示與設(shè)置環(huán)境變量格式化命令其它常用命令5.2.1連接命命令CONNECT：進進行用戶切換或連接到新的的數(shù)據(jù)庫。語法：CONN[ECT][username]/[password][@hoststring]DISC[ONNECT]：：斷開與數(shù)數(shù)據(jù)庫的連連接。注意：該命令作作用僅僅是斷開開與數(shù)據(jù)庫的的連接，不退出SQL*Plus環(huán)環(huán)境！5.2.2編輯命命令在SQL*Plus中執(zhí)行SQL語句句、PL/SQL程程序時，輸輸入的SQL語句句和PL/SQL程序代代碼會暫時存放到到SQL緩緩沖區(qū)中。輸入SQL語句在語句最后后加分號，并按回車，則立即執(zhí)行該語句；語句輸入結(jié)結(jié)束后回車，換行后再按回車，則結(jié)束SQL語句輸入入但不執(zhí)行該語句；語句輸入結(jié)結(jié)束后按回車，換行后按斜杠（/），立即執(zhí)行該語句。輸入完P(guān)L/SQL程序，回車換行后按點號（.），則則結(jié)束輸入，但不執(zhí)行；輸入斜杠（/），則則立即執(zhí)行。顯示緩沖區(qū)區(qū)L[IST]編輯緩沖區(qū)區(qū)A[PPEND]———將指定的的文本追加加到緩沖區(qū)區(qū)內(nèi)當前行行的末尾；；C[HANGE]———修改緩沖沖區(qū)中當前前行的文本本；DEL——刪除緩緩沖區(qū)中當當前行的文文本；N——用數(shù)值值定位緩沖沖區(qū)中的當當前行；I[NPUT]——在在緩沖區(qū)當當前行的后后面新增加加一行文本本；ED[IT]———以文本本編輯器方方式打開緩緩沖區(qū)，進進行編輯。。執(zhí)行緩沖區(qū)區(qū)R[UN]/清除緩沖區(qū)區(qū)CL[EAR]BUFF[ER]注意：使用ED[IT]命令時，，緩沖區(qū)中必必須存在信信息。常用編輯命命令：刪除第n行DEL

n刪除當前行DEL從SQL緩沖區(qū)中刪除所有行CL[EAR]BUFF[ER]從當前行刪除文本textC[HANGE]/text在當前行用新的文本替換舊的文本C[HANGE]/old/new添加文本到緩沖區(qū)當前行尾A[PPEND]text說明命令在第一行之前插入一行0text用text替換第n行ntext指定第n行為當前行n執(zhí)行當前緩沖區(qū)的命令/執(zhí)行當前緩沖區(qū)的命令R[UN]顯示緩沖區(qū)中m到n行L[IST]mn顯示緩沖區(qū)中第n行L[IST]n顯示緩沖區(qū)中所有行L[IST]在當前行之后添加包含text的一行I[NPUT]text在當前行之后添加一行I[NPUT]刪除從第m到n行DEL

mn說明命令例：在SQL*PLUS中編編輯SQL緩沖區(qū)中中的SQL語句。顯示結(jié)果：：SQL>SELECTdeptno,dname2FROMdept;DEPTNODNAME

10ACCOUNTING20RESEARCH30SALES40OPERATIONS問題：增加加一列查詢詢信息，不重新輸入入SQL語句句，如何實實現(xiàn)？SQL>L11*deptno,dnameSQL>A,loc1*deptno,dname,loc顯示結(jié)果：：SQL>L1SELECTdeptno,dname,loc2*FROMdeptSQL>/DEPTNODNAMELOC

10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON5.2.3文件操操作命令通常，將經(jīng)經(jīng)常執(zhí)行的的SQL*Plus命命令、SQL語句句和PL/SQL程序存儲到SQL腳本文文件(以.sql為為后綴)中中，然后執(zhí)執(zhí)行SQL腳本文件件。使用腳本文文件的好處：可以降低命命令輸入量量；可以避免輸輸入錯誤。。腳本文件的的創(chuàng)建SAV[E]filename[CREATE]|[REPLACE]|[APPEND]腳本文件的的裝載與編編輯GETfilename[LIST]|[NOLIST]默認路徑是是<ORACLE_HOME>\BIN腳本文件的的執(zhí)行STA[RT]filename@filename腳本文件的的注釋REM[ARK]：單行注釋釋，放在一一行語句的的頭部，表表示該行為為注釋。--：單行行注釋。/*……*/：多行行注釋。例：SQL>SELECTempno,ename,sal2FROMemp3WHEREempno=7844;SQL>SAVd:\ora_code\sqlscript.sql已創(chuàng)建filed:\ora_code\sqlscript.sqlSQL>CLEARBUFFERbuffer已清除除SQL>INPUT1COLUMNsalHEADING'salary'2SELECTempno,ename,sal3FROMemp4WHEREempno=78445SQL>SAVd:\ora_code\iscript.sql已創(chuàng)建filed:\ora_code\iscript.sql把SQL*Plus命令送入入緩沖區(qū)SQL>GETd:\ora_code\sqlscript.sqlSQL>@d:\ora_code\sqlscript.sqlRUNor/？注意：包含了SQL*Plus命命令的緩沖沖區(qū)內(nèi)容無法執(zhí)行，只能通過腳本文件的執(zhí)行行命令來執(zhí)行！常用文件命命令：打開默認編輯器(Windows中是notepad.exe)，把緩沖區(qū)中最后一條SQL語句調(diào)入進行編輯ED[IT]把屏幕中的操作及執(zhí)行結(jié)果“假脫機”，即保存到磁盤文件上，默認文件擴展名為.lstSPO[OL]filename[APPEND]顯示當前的“假脫機”狀態(tài)SPO[OL]把當前目錄中指定的.sql文件調(diào)入編輯器進行編輯ED[IT]filename運行調(diào)入內(nèi)存的sql文件，和START一樣@filename運行調(diào)入內(nèi)存的sql文件STA[RT]filename調(diào)入存盤的sql文件到內(nèi)存GETfilename將緩沖區(qū)的內(nèi)容以文件方式存盤，缺省文件擴展名為.sql，如果使用APPEND則向一個已存在的文件追加內(nèi)容，如果使用REPLACE則是覆蓋一個已經(jīng)存在的文件SAV[E]filename[CRE[ATE]|REP[LACE]|APP[END]]說明命令退出SQL*PLUSEXIT停止輸出并將結(jié)果送到系統(tǒng)打印機SPO[OL]OUT停止輸出SPO[OL]OFF說明命令例：SPOd:\ora_code\outputfile.txtSELECTempno,ename,salFROMempWHEREsal>=1500;SPOOFFSPOd:\ora_code\outputfile.txtAPPENDSELECTempno,ename,salFROMempWHEREsal>=3000;SPOOFF注意：只有SPOOFF后，才會把屏屏幕輸出內(nèi)容寫入文件！5.2.4交互式式命令替換變量使用替換變變量&variable_name例：注意：替換變量量為數(shù)值類型時，可以直接引用；如果為字符型或日期型，則需將替替換變量用用單引號引起起來。不希望每次次執(zhí)行時都都為替換變變量賦值，，可以使用用兩個“&”。例：SQL>SELECTempno,ename2FROMemp3WHEREdeptno=&XANDjob='&Y';SQL>SELECTempno,ename2FROMemp3WHEREdeptno=&&no;…SQL>SELECTempno,ename2FROMemp3WHEREdeptno=&no;注意：此時，只只需賦值一一次，可在當前SQL*Plus環(huán)環(huán)境中一直使用！''DEFINE命令定定義替換變變量使用DEFINE命命令為當前前SQL*Plus環(huán)境定義義CHAR類類型的替換變量量，語法：：DEFINEvariable_name='value'例：ACCEPT命令定定義替換變變量可以自定義義提示信息息，以提示示用戶為替替換變量輸輸入值。語語法：ACCEPTvariable[datatype][FORMATformat][PROMPTtext]{HIDE}SQL>DEFINEdno='10'SQL>SELECTempno2FROMemp3WHEREdeptno=&dno;注意：使用DEFINE定義替換換變量時，，1.變量只能是CHAR類型的；2.定義的的同時要進行賦值。說明：PROMPT選項指指定提示值值，用HIDE選項項隱藏輸入入，以便于于用戶輸入入替換變量量的值。例：查看替換變變量DEFINESQL>DEFINE清除替換變變量UNDEFINEvariable_nameSQL>UNDEFINEdno是否顯示用用值替換替替代變量前前后的命令令文本：SETVERIFYON|OFFSQL>ACCEPTdenoNUMBERPROMPT'請請輸入部門門編號：'SQL>SELECTempno2FROMemp3WHEREdeptno=&deno;綁定變量量定義：指指在SQL*Plus中定義義，在PL/SQL程程序中使使用的變量。。語法：：VAR[IABLE]variable_nameTYPE說明：在PL/SQL中引用用：:variable_name賦值：EXECUTE:variable_name:=value顯示：PRINTvariable_name例：SQL>VARIABLEv_salNUMBERSQL>EXECUTE:v_sal:=10PL/SQL過過程已已成功完完成。SQL>BEGIN2:v_sal:=20;3END;4/PL/SQL過過程已已成功完完成。SQL>PRINTv_salV_SAL20--定義義綁定變變量v_sal--給綁綁定變量量v_sal賦賦值--引用用綁定變變量v_sal--顯示示綁定變變量v_sal的值與用戶通通信PROMPT命命令用于于輸出提提示信息息,引導導用戶進進行操作作。PAUSE命令令用于暫停腳本本文件的的運行。SQL>CLEARBUFFERbuffer已已清除除SQL>INPUT1PROMPT顯示工資資高于XX的員員工信息息2PROMPT按<Enter>鍵繼繼續(xù)3PAUSE4ACCEPTvalueNUMBERPROMPT'請請輸入工工資界限限:'5SELECTempno,ename,salFROMscott.empWHEREsal>&valueSQL>SAVED:\ora_code\test.sqlSQL>@D:\ora_code\test.sql顯示工資資高于XX的員員工信息息按<Enter>鍵繼繼續(xù)請輸入工工資界限限:3000顯顯示與設(shè)設(shè)置環(huán)境境變量使用圖形形化界面面現(xiàn)實與與設(shè)置環(huán)環(huán)境變量量使用SHOW命令和SET命令來顯示和設(shè)置環(huán)境標量量指屏幕內(nèi)內(nèi)存，用用于控制制可以保保存在屏幕上上的數(shù)據(jù)據(jù)量。列出了58個用用戶可以控制的選項設(shè)置選項的值，，系統(tǒng)顯示了默認認值顯示所有有環(huán)境變變量SQL>SHOWALL顯示某個個環(huán)境變變量SQL>SHOWvariable_name設(shè)置某個個環(huán)境變變量SQL>SETvariable_name=value常用環(huán)境境變量ARRAYSIZE——從數(shù)據(jù)據(jù)庫中提提取的行行數(shù)，默默認15AUTOCOMMIT—是否否自動提提交DML語句句，默認認OFFCOLSP—選選定列之之間的分分隔符號號，默認認空格比較：與服務(wù)器初初始化參參數(shù)的顯示與設(shè)置的異同！！FEEDBACK—顯顯示反饋饋行信息息的最低低行數(shù)，，默認6HEADING—是否否顯示列列標題，，默認ONLINESIZE—行行長度，，默認80LONG—LONG和和LOB類型的的顯示長長度，默默認80PAGESIZE—每每頁所顯顯示的行行數(shù)，默默認14SERVEROUTPUT——是否顯顯示執(zhí)行行DBMS_OUTPUT.PUT_LINE命令的輸輸出結(jié)果果，默認認OFFAUTOTRACE——是否為為成功執(zhí)執(zhí)行的DML語語句產(chǎn)生生一個執(zhí)執(zhí)行報告告SETAUTOTRACE[ON|OFF|TRACEONLY][EXPLAIN][STATISTICS]TIME—是否否在SQL*Plus命令提提示符之之前顯示示時間，，默認OFFTIMING——是否顯顯示SQL語句句的執(zhí)行行時間，，默認OFF格格式化命命令對SQL語句或或PL/SQL程序執(zhí)行結(jié)果果的顯式式格式進行設(shè)置置。遵循循以下規(guī)規(guī)則：格式化命命令設(shè)置置之后，，將會一直起作作用，直到會會話結(jié)束束或下一一個格式式化命令令的設(shè)置置；每一次報報表結(jié)束束時，應(yīng)應(yīng)該重新新設(shè)置SQL*Plus為默默認值；；如果為某某個列指指定了別別名，必須使用該別別名，不能使使用列名名。COL[UMN]：控控制列的的輸出顯顯示格式式。其中，option的的選項有有：CLE[AR]:清清除所有有列的顯顯示格式式FOR[MAT]format:使用用格式模型型改變列的的顯示HEA[DING]text:設(shè)置置列標題題JUS[TIFY]{align}:調(diào)調(diào)整列標題的的位置居左左(left)、居中中(center)還還是居右右(right)[NO]PRINT:顯式式[隱藏藏]列標標題NULL<text>：在指指定位置置將空值值位置顯顯示為textTRUNCATED：：刪除第第一行的的字符串串WRAPPED:換換行COL[UMN][{column|alias}[option]]格式模型型：結(jié)果123401234$1234L12341234.001,234舉例A15999999099999$9999L99999999.999,999元素An90$L.,描述設(shè)置顯示示寬度為為n個字符每個9表表是一位位數(shù)字，，不顯示示前導零零表示前導導零美元符號號本地貨幣幣符號小數(shù)點的的位置千位分割割符例：顯示某列列的當前前設(shè)置：：清除某列列的當前前設(shè)置：：TTITLE和BTITLE：設(shè)置報報表的頁頁眉和頁頁腳。例：COLenameHEADING'Employee|Name'FORMATA15COLsalJUSTIFYLEFTFORMAT$99,999.99COLmgrFORMAT999999999NULL'Nomanager'COL[UMN]enameCOL[UMN]enameCLEARTTI[TLE]/BTITLE[text|OFF|ON]SQL>TTITLE'Salary|Report'SQL>BTITLE'Confidential'分兩行顯顯示BREAK命令：用用于去掉掉重復(fù)的的行以及及斷點處處跳躍指指定的行行數(shù)。其中：page表示遇到到斷點時時產(chǎn)生新新的一頁頁skipn表示遇到到斷點時時跳躍n行duplicate顯示重復(fù)值清除所有BREAK設(shè)置置使用CLEAR命令令：BREAKoncolumn[|alias|row][skipn|dup|page]on..[onreport]CLEARBREAK例：控制記錄錄顯示分組順順序BREAKONdeptnoSELECTdeptno,enameFROMempORDERBYdeptno;DEPTNOENAME10CLARKKINGMILLER20SMITHADAMS…注意：每次只有一一個BREAK命令起作用，，但一次可以在在多個列上使使用BREAK命令，即BREAKON列名1ON列名2其其它常用命命令顯示數(shù)據(jù)庫對對象結(jié)構(gòu)：DESC[RIBE]清除屏幕內(nèi)容容CLEARSCREENShift+Delete幫助命令：HELP修改用戶口令令：PASSW[ORD]注意：任何用戶都都可以修改自自己的口令，，只有DBA身身份登錄的用戶才可修改其他用戶戶的口令。保存環(huán)境變量量STORESETfilename[CREATE|REPLACE|APPEND]說明：默認路路徑為<ORACLE_HOME>\BIN腳本文件的創(chuàng)創(chuàng)建：創(chuàng)建SQL、PL/SQL語句句，并保存到到腳本文件；；從編輯器中調(diào)調(diào)用該文件；；在SQL、PL/SQL語句前增加加格式化命令令；在SQL、PL/SQL語句之后清清除格式化命命令；保存腳本文件件。例：編輯腳本本文件，以創(chuàng)創(chuàng)建一個報表表，報表要根根據(jù)用戶輸入入的部門位置置(不區(qū)分大大小寫)顯示示雇員的所屬屬部門名稱(DepartmentName)、姓名(EMPOYEENAME)、受受雇日期(STARTDATE)、工資(SALARY)、年工資資(ANNUALSALARY)，并將列標標題分兩行顯顯示。系統(tǒng)提提示和輸出結(jié)結(jié)果的形式參參考如下：Pleaseenterthelocationname:ChicagoDepartmentEMPLOYEESTARTANNUALNameNAMEDATESALARYSALARY

SALESBLAKE01-MAY-81$2,850.00$34,200.00MARTIN28-SEP-81$1,250.00$15,000.00ALLEN20-FEB-81$1,600.00$19,200.00……腳本文件SETPAGESIZE20TTITLE'雇員員受雇情況表表'

BTITLE'結(jié)束'BREAKONdnSKIP2

COLdnHEADING'Department|Name'

COLenHEADING'EMPLOYEE|NAME'

COLehHEADING'STARTED|DATE'

COLesHEADING'SALARY'FORMAT$99,999.99COLeaHEA