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ù)器或客戶端的安裝而自動進(jìn)行安裝的管理與開發(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ù)庫連接時的會話行為。當(dāng)用戶啟動SQL*Plus建立與數(shù)據(jù)庫的連接時，Oracle自動讀取該腳本文件，并根據(jù)該文件內(nèi)容設(shè)置SQL*Plus的工作環(huán)境。SQL*Plus站點配置文件是針對所有會話的。在Windows系統(tǒng)中，SQL*Plus站點配置文件默認(rèn)名稱為glogin.sql,存放在<ORACLE_HOME>\sqlplus\admin文件夾中。SQL*Plus的退出：EXIT或QUIT命令說明：會話指用戶到數(shù)據(jù)庫的指定連接。5.2SQL*Plus常用命令連接命令編輯命令文件操作命令交互式命令顯示與設(shè)置環(huán)境變量格式化命令其它常用命令5.2.1連連接命令令CONNECT：進(jìn)行行用戶切換換或連接到新新的數(shù)據(jù)庫。。語法：CONN[ECT][username]/[password][@hoststring]DISC[ONNECT]：：斷開與與數(shù)據(jù)庫庫的連接接。注意：該命令令作用僅僅是斷斷開與數(shù)據(jù)庫庫的連接，不退出SQL*Plus環(huán)境境！5.2.2編編輯命令令在SQL*Plus中中執(zhí)行SQL語語句、PL/SQL程程序時，，輸入的的SQL語語句和PL/SQL程程序代碼碼會暫時存放放到SQL緩沖沖區(qū)中。輸入SQL語句句在語句最最后加分號，并按回車，則立即執(zhí)行行該語句；；語句輸入入結(jié)束后后回車，換行后后再按回車車，則結(jié)束SQL語句句輸入但不執(zhí)行該語句；；語句輸入入結(jié)束后后按回車，換行后后按斜杠（/），，立即執(zhí)行行該語句。。輸入完P(guān)L/SQL程程序，回車換行后按點號（.），，則結(jié)束輸入入，但不執(zhí)行；輸入斜杠杠（/），，則立即執(zhí)行行。顯示緩沖沖區(qū)L[IST]編輯緩沖沖區(qū)A[PPEND]——將將指定的的文本追追加到緩緩沖區(qū)內(nèi)內(nèi)當(dāng)前行行的末尾尾；C[HANGE]——修修改緩沖沖區(qū)中當(dāng)當(dāng)前行的的文本；；DEL——刪除除緩沖區(qū)區(qū)中當(dāng)前前行的文文本；N——用數(shù)數(shù)值定位位緩沖區(qū)區(qū)中的當(dāng)當(dāng)前行；；I[NPUT]———在緩緩沖區(qū)當(dāng)當(dāng)前行的的后面新新增加一一行文本本；ED[IT]——以以文本編編輯器方方式打開開緩沖區(qū)區(qū)，進(jìn)行行編輯。。執(zhí)行緩沖沖區(qū)R[UN]/清除緩沖沖區(qū)CL[EAR]BUFF[ER]注意：使用ED[IT]命命令時，，緩沖區(qū)中中必須存存在信息息。常用編輯輯命令：：刪除第n行DEL

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

mn說明命令例：在SQL*PLUS中編編輯SQL緩沖沖區(qū)中的的SQL語句。。顯示結(jié)果果：SQL>SELECTdeptno,dname2FROMdept;DEPTNODNAME10ACCOUNTING20RESEARCH30SALES40OPERATIONS問題：增增加一列列查詢信信息，不重新輸輸入SQL語語句，如如何實現(xiàn)現(xiàn)？SQL>L11*deptno,dnameSQL>A,loc1*deptno,dname,loc顯示結(jié)果果：SQL>L1SELECTdeptno,dname,loc2*FROMdeptSQL>/DEPTNODNAMELOC10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON5.2.3文文件操作作命令通常，將將經(jīng)常執(zhí)執(zhí)行的SQL*Plus命令令、SQL語語句和PL/SQL程程序存儲到SQL腳腳本文件件(以.sql為后綴綴)中，，然后執(zhí)執(zhí)行SQL腳本本文件。。使用腳本本文件的的好處：可以降低低命令輸輸入量；；可以避免免輸入錯錯誤。腳本文件件的創(chuàng)建建SAV[E]filename[CREATE]|[REPLACE]|[APPEND]腳本文件件的裝載載與編輯輯GETfilename[LIST]|[NOLIST]默認(rèn)路徑徑是<ORACLE_HOME>\BIN腳本文件件的執(zhí)行行STA[RT]filename@filename腳本文件件的注釋釋REM[ARK]：單行注注釋，放放在一行行語句的的頭部，，表示該該行為注注釋。--：單單行注釋釋。/*………*/：：多行注注釋。例：SQL>SELECTempno,ename,sal2FROMemp3WHEREempno=7844;SQL>SAVd:\ora_已創(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ū)區(qū)SQL>GETd:\ora_code\sqlscript.sqlSQL>@d:\ora_code\sqlscript.sqlRUNor/？注意：包含了SQL*Plus命令的的緩沖區(qū)內(nèi)容無法執(zhí)行，只能通過腳本文件的執(zhí)行命命令來執(zhí)行！常用文件命令令：打開默認(rèn)編輯器(Windows中是notepad.exe)，把緩沖區(qū)中最后一條SQL語句調(diào)入進(jìn)行編輯ED[IT]把屏幕中的操作及執(zhí)行結(jié)果“假脫機”，即保存到磁盤文件上，默認(rèn)文件擴展名為.lstSPO[OL]filename[APPEND]顯示當(dāng)前的“假脫機”狀態(tài)SPO[OL]把當(dāng)前目錄中指定的.sql文件調(diào)入編輯器進(jìn)行編輯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í)執(zhí)行時都為替替換變量賦值值，可以使用用兩個“&””。例：SQL>SELECTempno,ename2FROMemp3WHEREdeptno=&XANDjob='&Y';SQL>SELECTempno,ename2FROMemp3WHEREdeptno=&&no;…SQL>SELECTempno,ename2FROMemp3WHEREdeptno=&no;注意：此時，只需需賦值一次，，可在當(dāng)前SQL*Plus環(huán)境中中一直使用！''DEFINE命令定義替替換變量使用DEFINE命令為為當(dāng)前SQL*Plus環(huán)境定義CHAR類型型的替換變量，，語法：DEFINEvariable_name='value'例：ACCEPT命令定義替替換變量可以自定義提提示信息，以以提示用戶為為替換變量輸輸入值。語法法：ACCEPTvariable[datatype][FORMATformat][PROMPTtext]{HIDE}SQL>DEFINEdno='10'SQL>SELECTempno2FROMemp3WHEREdeptno=&dno;注意：使用DEFINE定義義替換變量時時，1.變量只能是CHAR類型的；2.定義的同時要進(jìn)行賦值。說明：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命令用于輸輸出提示信息息,引導(dǎo)用戶戶進(jìn)行操作。。PAUSE命命令用于暫停腳本文件件的運行。SQL>CLEARBUFFERbuffer已清除SQL>INPUT1PROMPT顯示工資高于于XX的員工工信息2PROMPT按<Enter>鍵繼續(xù)續(xù)3PAUSE4ACCEPTvalueNUMBERPROMPT'請請輸入工資界界限:'5SELECTempno,ename,salFROMscott.empWHEREsal>&valueSQL>SAVED:\ora_code\test.sqlSQL>@D:\ora_code\test.sql顯示工資高于于XX的員工工信息按<Enter>鍵繼續(xù)續(xù)請輸入工資界界限:30005.2.5顯顯示與設(shè)置置環(huán)境變量使用圖形化界界面現(xiàn)實與設(shè)設(shè)置環(huán)境變量量使用SHOW命令和SET命令來顯示和設(shè)置環(huán)境標(biāo)量指屏幕內(nèi)存，，用于控制可可以保存在屏幕上的數(shù)數(shù)據(jù)量。列出了58個用戶可以控制的選項設(shè)置選項的值，系統(tǒng)顯示了默認(rèn)值顯示所有環(huán)境境變量SQL>SHOWALL顯示某個環(huán)境境變量SQL>SHOWvariable_name設(shè)置某個環(huán)境境變量SQL>SETvariable_name=value常用環(huán)境變量量ARRAYSIZE—從從數(shù)據(jù)庫中提提取的行數(shù)，，默認(rèn)15AUTOCOMMIT——是否自動提提交DML語語句，默認(rèn)OFFCOLSP——選定列之間間的分隔符號號，默認(rèn)空格比較：與服務(wù)器初始化化參數(shù)的顯示與設(shè)置的異同！FEEDBACK—顯示示反饋行信息息的最低行數(shù)數(shù)，默認(rèn)6HEADING—是否顯顯示列標(biāo)題，，默認(rèn)ONLINESIZE—行長長度，默認(rèn)80LONG—LONG和LOB類型的的顯示長度，，默認(rèn)80PAGESIZE—每頁頁所顯示的行行數(shù)，默認(rèn)14SERVEROUTPUT—是否顯顯示執(zhí)行DBMS_OUTPUT.PUT_LINE命令的輸出結(jié)結(jié)果，默認(rèn)OFFAUTOTRACE—是是否為成功執(zhí)執(zhí)行的DML語句產(chǎn)生一一個執(zhí)行報告告SETAUTOTRACE[ON|OFF|TRACEONLY][EXPLAIN][STATISTICS]TIME—是是否在SQL*Plus命令提示符符之前顯示時時間，默認(rèn)OFFTIMING—是否顯示示SQL語句句的執(zhí)行時間間，默認(rèn)OFF5.2.6格格式化命令令對SQL語句句或PL/SQL程序執(zhí)行結(jié)果的顯顯式格式進(jìn)行設(shè)置。遵遵循以下規(guī)則則：格式化命令設(shè)設(shè)置之后，將將會一直起作用，直到會話結(jié)結(jié)束或下一個個格式化命令令的設(shè)置；每一次報表結(jié)結(jié)束時，應(yīng)該該重新設(shè)置SQL*Plus為默認(rèn)認(rèn)值；如果為某個列列指定了別名名，必須使用該別名，不能使用列列名。COL[UMN]：：控制列的輸輸出顯示格式式。其中，option的選選項有：CLE[AR]:清除除所有列的顯顯示格式FOR[MAT]format:使用格式模型改變列的顯示示HEA[DING]text:設(shè)置列標(biāo)標(biāo)題JUS[TIFY]{align}:調(diào)整列標(biāo)題的位置居左(left)、、居中(center)還是居右(right)[NO]PRINT:顯顯式[隱藏藏]列標(biāo)題NULL<text>：在指定位位置將空值位位置顯示為textTRUNCATED：刪刪除第一行的的字符串WRAPPED:換行行COL[UMN][{column|alias}[option]]格式模型：結(jié)果123401234$1234L12341234.001,234舉例A15999999099999$9999L99999999.999,999元素An90$L.,描述設(shè)置顯示寬度度為n個字符每個9表是一一位數(shù)字，不不顯示前導(dǎo)零零表示前導(dǎo)零美元符號本地貨幣符號號小數(shù)點的位置置千位分割符例：顯示某列的當(dāng)當(dāng)前設(shè)置：清除某列的當(dāng)當(dāng)前設(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ù)復(fù)的行以及及斷點處跳跳躍指定的的行數(shù)。其中：page表示遇到斷斷點時產(chǎn)生生新的一頁頁skipn表示遇到斷斷點時跳躍躍n行duplicate顯示重復(fù)值值清除所有BREAK設(shè)置使用用CLEAR命令令：BREAKoncolumn[|alias|row][skipn|dup|page]on..[onreport]CLEARBREAK例：控制記記錄顯示分分組順序BREAKONdeptnoSELECTdeptno,enameFROMempORDERBYdeptno;DEPTNOENAME

10CLARKKINGMILLER20SMITHADAMS…注意：每次只有有一個BREAK命令起作用用，但一次可以以在多個列列上使用BREAK命令，即BREAKON列名1ON列名25.2.7其它常常用命令顯示數(shù)據(jù)庫庫對象結(jié)構(gòu)構(gòu)：DESC[RIBE]清除屏幕內(nèi)內(nèi)容CLEARSCREENShift+Delete幫助命令：：HELP修改用戶口口令：PASSW[ORD]注意：任何用戶戶都可以修修改自己的的口令，只有DBA身份登錄錄的用戶才可可修改其他用用戶的口令。保存環(huán)境變變量STORESETfilename[CREATE|REPLACE|APPEND]說明：默認(rèn)認(rèn)路徑為<ORACLE_HOME>\BIN腳本文件的的創(chuàng)建：創(chuàng)建SQL、PL/SQL語句，，并保存到到腳本文件件；從編輯器中中調(diào)用該文文件；在SQL、、PL/SQL語句句前增加格格式化命令令；在SQL、、PL/SQL語句句之后后清除除格式式化命命令；；保存腳腳本文文件。。例：編編輯腳腳本文文件，，以創(chuàng)創(chuàng)建一一個報報表，，報表表要根根據(jù)用用戶輸輸入的的部門門位置置(不不區(qū)分分大小小寫)顯示示雇員員的所所屬部部門名名稱(DepartmentName)、姓姓名(EMPOYEENAME)、、受雇雇日期期(STARTDATE)、工工資(SALARY)、、年工工資(ANNUALSALARY)，并并將列列標(biāo)題題分兩兩行顯顯示。。系統(tǒng)統(tǒng)提示示和輸輸出結(jié)結(jié)果的的形式式參考考如下下：Pleaseenterthelocationname:ChicagoDepartmentEMPLOYEESTARTANNUALNameNAMEDATESALARYSALARYSALESBLAKE01-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é)束束'BREAKONdnSKIP2COLdnHEADING'Department|Name'COLenHEADING'EMPLOYEE|NAME'COLehHEADING'STARTED|DATE'COLesHEADING'SALARY'FORMAT$99,999.99COLeaHEADING'ANNUAL|SALARY'FO