SQLOracleg数据库基础教程第章SQLLUS工具介绍与常用命令

第5章SQL*PLUS本章内容SQL*PLUS工具介绍SQL*PLUS常用命令iSQL*Plus本章要求了解SQL*PLUS工具的应用掌握SQL*PLUS基本命令操作了解iSQL*Plus工具的使用5.1SQL*Plus概述概念SQL*Plus工具是随Oracle数据库服务器或客户端的安装而自动进行安装的管理与开发工具，Oracle数据库中所有的管理操作都可以通过SQL*Plus工具完成，同时开发人员利用SQL*Plus可以测试、运行SQL语句和PL/SQL程序。运行方式命令行方式图形界面方式(GUI)基于Web的iSQL*Plus方式两层结构——SQL*Plus客户端、数据库服务器三层结构——Web浏览器、应用服务器和数据库服务器在SQL*Plus中执行的语句有3种：SQL*Plus命令、SQL语句和PL/SQL程序作用输入、编辑、存储、提取、运行和调试SQL语句和PL/SQL程序开发、执行批处理脚本执行数据库管理处理数据，生成报表，存储、打印、格式化查询结果检查表和数据库对象定义启动SQL*Plus命令行方式：语法：sqlplus[username]/[password][@connect_identifier]|[NOLOG]仅启动SQL*Plus，不连接数据库运行：启动：不连接数据库输入用户名，并指定要连接的数据库输入口令注意：为了保证口令安全，可以先不输入，等提示“输入口令”时，再输入！运行：以系统用户sys身份登录启动SQL*Plus@orcl10gassysdba图形界面方式：选择【开始】／【程序】／【Oracle–OraDb10g_home1】／【应用程序开发】菜单中选择SQLPlus命令，出现如图所示登录窗口。在【用户名】文本框中输入用户名，如scott；在【口令】文本框中输入用户口令，如tiger；在【主机字符串】文本框中输入数据库名，如orcl10g。Orcl10gassysdba注意：如果以系统用户名(如sys)登录，则“主机字符串”处必须输入登录连接的身份！SQL*Plus站点配置文件站点配置文件(SiteProfile)是一个在SQL*Plus安装时自动创建的脚本文件，用于控制SQL*Plus与数据库连接时的会话行为。当用户启动SQL*Plus建立与数据库的连接时，Oracle自动读取该脚本文件，并根据该文件内容设置SQL*Plus的工作环境。SQL*Plus站点配置文件是针对所有会话的。在Windows系统中，SQL*Plus站点配置文件默认名称为glogin.sql,存放在<ORACLE_HOME>\sqlplus\admin文件夹中。SQL*Plus的退出：EXIT或QUIT命令说明：会话指用户到数据库的指定连接。5.2SQL*Plus常用命令连接命令编辑命令文件操作命令交互式命令显示与设置环境变量格式化命令其它常用命令连连接命命令CONNECT：进进行用户切切换或连接到到新的的数据库库。语法：：CONN[ECT][username]/[password][@hoststring]DISC[ONNECT]：：断开开与数数据库库的连连接。。注意：该命命令作作用仅仅是是断开开与数据据库的的连接，不退出出SQL*Plus环环境！编编辑命命令在SQL*Plus中执执行SQL语句句、PL/SQL程程序时时，输输入的的SQL语句句和PL/SQL程程序代代码会暂时存存放到到SQL缓缓冲区区中。输入SQL语句句在语句句最后后加分号，并按按回车，则立即执执行该语句句；语句输输入结结束后后回车，换行行后再按回回车，则结束SQL语句句输入入但不执行行该语句句；语句输输入结结束后后按回车车，换行行后按斜杠杠（/）），立即执执行该语句句。输入完完PL/SQL程序序，回车换行后后按点号号（.）），则则结束输输入，但不执行行；输入斜斜杠（/）），则则立即执执行。显示缓缓冲区区L[IST]编辑缓缓冲区区A[PPEND]———将指指定的的文本本追加加到缓缓冲区区内当当前行行的末末尾；；C[HANGE]———修改改缓冲冲区中中当前前行的的文本本；DEL——删删除缓缓冲区区中当当前行行的文文本；；N——用用数值值定位位缓冲冲区中中的当当前行行；I[NPUT]———在在缓冲冲区当当前行行的后后面新新增加加一行行文本本；ED[IT]———以以文本本编辑辑器方方式打打开缓缓冲区区，进进行编编辑。。执行缓缓冲区区R[UN]/清除缓缓冲区区CL[EAR]BUFF[ER]注意：使用用ED[IT]命令令时，，缓冲区区中必必须存存在信信息。常用编编辑命命令：：删除第n行DEL

n删除当前行DEL从SQL缓冲区中删除所有行CL[EAR]BUFF[ER]从当前行删除文本textC[HANGE]/text在当前行用新的文本替换旧的文本C[HANGE]/old/new添加文本到缓冲区当前行尾A[PPEND]text说明命令在第一行之前插入一行0text用text替换第n行ntext指定第n行为当前行n执行当前缓冲区的命令/执行当前缓冲区的命令R[UN]显示缓冲区中m到n行L[IST]mn显示缓冲区中第n行L[IST]n显示缓冲区中所有行L[IST]在当前行之后添加包含text的一行I[NPUT]text在当前行之后添加一行I[NPUT]删除从第m到n行DEL

mn说明命令例：在在SQL*PLUS中编编辑SQL缓冲冲区中中的SQL语句句。显示结结果：：SQL>SELECTdeptno,dname2FROMdept;DEPTNODNAME10ACCOUNTING20RESEARCH30SALES40OPERATIONS问题：：增加加一列列查询询信息息，不重新新输入入SQL语句句，如如何实实现？？SQL>L11*deptno,dnameSQL>A,loc1*deptno,dname,loc显示结结果：：SQL>L1SELECTdeptno,dname,loc2*FROMdeptSQL>/DEPTNODNAMELOC10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON文文件操操作命命令通常，，将经经常执执行的的SQL*Plus命命令、SQL语句句和PL/SQL程程序存储到到SQL脚脚本文文件(以.sql为为后缀缀)中中，然然后执执行SQL脚本本文件件。使用脚脚本文文件的的好处：可以降降低命命令输输入量量；可以避避免输输入错错误。。脚本文文件的的创建建SAV[E]filename[CREATE]|[REPLACE]|[APPEND]脚本文文件的的装载载与编编辑GETfilename[LIST]|[NOLIST]默认路路径是是<ORACLE_HOME>\BIN脚本文文件的的执行行STA[RT]filename@filename脚本文文件的的注释释REM[ARK]：单行行注释释，放放在一一行语语句的的头部部，表表示该该行为为注释释。--：：单行行注释释。/*………*/：：多行行注释释。例：SQL>SELECTempno,ename,sal2FROMemp3WHEREempno=7844;SQL>SAVd:\ora_code\sqlscript.sql已创建建filed:\ora_code\sqlscript.sqlSQL>CLEARBUFFERbuffer已已清除除SQL>INPUT1COLUMNsalHEADING'salary'2SELECTempno,ename,sal3FROMemp4WHEREempno=78445SQL>SAVd:\ora_code\iscript.sql已创建建filed:\ora_code\iscript.sql把SQL*Plus命令令送入入缓冲冲区SQL>GETd:\ora_code\sqlscript.sqlSQL>@d:\ora_code\sqlscript.sqlRUNor/？注意：包含含了SQL*Plus命命令的的缓冲冲区内容无法执执行，只能通过脚本文件的的执行行命令令来执行行！常用文文件命命令：：打开默认编辑器(Windows中是notepad.exe)，把缓冲区中最后一条SQL语句调入进行编辑ED[IT]把屏幕中的操作及执行结果“假脱机”，即保存到磁盘文件上，默认文件扩展名为.lstSPO[OL]filename[APPEND]显示当前的“假脱机”状态SPO[OL]把当前目录中指定的.sql文件调入编辑器进行编辑ED[IT]filename运行调入内存的sql文件，和START一样@filename运行调入内存的sql文件STA[RT]filename调入存盘的sql文件到内存GETfilename将缓冲区的内容以文件方式存盘，缺省文件扩展名为.sql，如果使用APPEND则向一个已存在的文件追加内容，如果使用REPLACE则是覆盖一个已经存在的文件SAV[E]filename[CRE[ATE]|REP[LACE]|APP[END]]说明命令退出SQL*PLUSEXIT停止输出并将结果送到系统打印机SPO[OL]OUT停止输出SPO[OL]OFF说明命令例：SPOd:\ora_code\outputfile.txtSELECTempno,ename,salFROMempWHEREsal>=1500;SPOOFFSPOd:\ora_code\outputfile.txtAPPENDSELECTempno,ename,salFROMempWHEREsal>=3000;SPOOFF注意：只有有SPOOFF后，才会会把屏屏幕输输出内容写入文文件！交交互式式命令令替换变变量使用替替换变变量&variable_name例：注意：替换换变量量为数值类类型时，可可以直接引引用；如果果为字符型型或日期型型，则需需将替替换变变量用用单引号号引起起来。不希望望每次次执行行时都都为替替换变变量赋赋值，，可以以使用用两个个“&”。。例：SQL>SELECTempno,ename2FROMemp3WHEREdeptno=&XANDjob='&Y';SQL>SELECTempno,ename2FROMemp3WHEREdeptno=&&no;…SQL>SELECTempno,ename2FROMemp3WHEREdeptno=&no;注意：此时时，只只需赋赋值一一次，，可在当前SQL*Plus环环境中中一直使用！''DEFINE命命令定定义替替换变变量使用DEFINE命命令为为当前前SQL*Plus环境境定义义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>键键继继续续3PAUSE4ACCEPTvalueNUMBERPROMPT'请请输输入入工工资资界界限限:'5SELECTempno,ename,salFROMscott.empWHEREsal>&valueSQL>SAVED:\ora_code\test.sqlSQL>@D:\ora_code\test.sql显示示工工资资高高于于XX的的员员工工信信息息按<Enter>键键继继续续请输输入入工工资资界界限限:3000显显示示与与设设置置环环境境变变量量使用用图图形形化化界界面面现现实实与与设设置置环环境境变变量量使用用SHOW命令令和和SET命令令来来显示示和设置置环境境标标量量指屏屏幕幕内内存存，，用用于于控控制制可可以以保保存存在屏屏幕幕上上的的数数据据量量。。列出出了了58个个用用户可可以以控制制的的选项项设置置选选项的的值值，，系统统显显示了了默默认认值值显示示所所有有环环境境变变量量SQL>SHOWALL显示示某某个个环环境境变变量量SQL>SHOWvariable_name设置置某某个个环环境境变变量量SQL>SETvariable_name=value常用用环环境境变变量量ARRAYSIZE——从从数数据据库库中中提提取取的的行行数数，，默默认认15AUTOCOMMIT——是是否否自自动动提提交交DML语语句句，，默默认认OFFCOLSP——选选定定列列之之间间的的分分隔隔符符号号，，默默认认空格格比较较：：与服务务器器初初始始化化参参数数的显示示与设置置的异异同同！！FEEDBACK—显显示反馈馈行信息息的最低低行数，，默认6HEADING—是否否显示列列标题，，默认ONLINESIZE—行行长度，，默认80LONG—LONG和和LOB类型的的显示长长度，默默认80PAGESIZE—每每页所显显示的行行数，默默认14SERVEROUTPUT——是否显显示执行行DBMS_OUTPUT.PUT_LINE命令的输输出结果果，默认认OFFAUTOTRACE——是否为为成功执执行的DML语语句产生生一个执执行报告告SETAUTOTRACE[ON|OFF|TRACEONLY][EXPLAIN][STATISTICS]TIME—是否否在SQL*Plus命令提提示符之之前显示示时间，，默认OFFTIMING——是否显显示SQL语句句的执行行时间，，默认OFF格格式化命命令对SQL语句或或PL/SQL程序执行结果果的显式式格式进行设置置。遵循循以下规规则：格式化命命令设置置之后，，将会一直起作作用，直到会会话结束束或下一一个格式式化命令令的设置置；每一次报报表结束束时，应应该重新新设置SQL*Plus为默默认值；；如果为某某个列指指定了别别名，必须使用该别别名，不能使使用列名名。COL[UMN]：控控制列的的输出显显示格式式。其中，option的的选项有有：CLE[AR]:清清除所有有列的显显示格式式FOR[MAT]format:使用用格式模型型改变列的的显示HEA[DING]text:设置置列标题题JUS[TIFY]{align}:调调整列标题的的位置居左左(left)、居中中(center)还还是居右右(right)[NO]PRINT:显式式[隐藏藏]列标标题NULL<text>：在指指定位置置将空值值位置显显示为textTRUNCATED：：删除第第一行的的字符串串WRAPPED:换换行COL[UMN][{column|alias}[option]]格式模型型：结果123401234$1234L12341234.001,234举例A15999999099999$9999L99999999.999,999元素An90$L.,描述设置显示示宽度为为n个字符每个9表表是一位位数字，，不显示示前导零零表示前导导零美元符号号本地货币币符号小数点的的位置千位分割割符例：显示某列列的当前前设置：：清除某列列的当前前设置：：TTITLE和BTITLE：设置报报表的页页眉和页页脚。例：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命令：用用于去掉掉重复的的行以及及断点处处跳跃指指定的行行数。其中：page表示遇到到断点时时产生新新的一页页skipn表示遇到到断点时时跳跃n行duplicate显示重复复值清除所有有BREAK设设置使用用CLEAR命命令：：BREAKoncolumn[|alias|row][skipn|dup|page]on..[onreport]CLEARBREAK例：控制制记录显显示分组组顺序BREAKONdeptnoSELECTdeptno,enameFROMempORDERBYdeptno;DEPTNOENAME10CLARKKINGMILLER20SMITHADAMS…注意：每次只只有一个个BREAK命令起作作用，但一次可可以在多多个列上上使用BREAK命令，即即BREAKON列名1ON列名2其其它常用用命令显示数据据库对象象结构：：DESC[RIBE]清除屏幕幕内容CLEARSCREENShift+Delete帮助命令令：HELP修改用户户口令：：PASSW[ORD]注意：任何用用户都可可以修改改自己的的口令，，只有DBA身份份登录的用户才才可修改其他他用户的口令。。保存环境境变量STORESETfilename[CREATE|REPLACE|APPEND]说明：默默认路径径为<ORACLE_HOME>\BIN脚本文件件的创建建：创建SQL、、PL/SQL语句句，并保保存到脚脚本文件件；从编辑器器中调用用该文件件；在SQL、PL/SQL语句句前增加加格式化化命令；；在SQL、PL/SQL语句句之后清清除格式式化命令令；保存脚本本文件。。例：编辑辑脚本文文件，以以创建一一个报表表，报表表要根据据用户输输入的部部门位置置(不区区分大小小写)显显示雇员员的所属属部门名名称(DepartmentName)、姓名名(EMPOYEENAME)、、受雇日日期(STARTDATE)、工工资(SALARY)、年工工资(ANNUALSALARY)，并并将列标标题分两两行显示示。系统统提示和和输出结结果的形形式参考考如下：：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'结束束'BREAKONdnSKIP2COLdnHEADING'Department|Name'COLenHEADING'EMPLOYEE|NAME'

COLehHEADING'STARTED|DATE'

CO