Oracleg数据库基础教程XXXX 第章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常用命令连接命令编辑命令文件操作命令交互式命令显示与设置环境变量格式化命令其它常用命令5.2.1连连接命令令CONNECT：进行行用户切换换或连接到新新的数据库。。语法：CONN[ECT][username]/[password][@hoststring]DISC[ONNECT]：：断开与与数据库库的连接接。注意：该命令令作用仅仅是断断开与数据库库的连接，不退出SQL*Plus环境境！5.2.2编编辑命令令在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>/DEPTNODNAMELOC10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON5.2.3文文件操作作命令通常，将将经常执执行的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_已创建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后，才会把屏幕幕输出内容写入文件！5.2.4交交互式命令令替换变量使用替换变量量&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>键继续续请输入工资界界限:30005.2.5显显示与设置置环境变量使用图形化界界面现实与设设置环境变量量使用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语句句的执行时间间，默认OFF5.2.6格格式化命令令对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;DEPTNOENAME

10CLARKKINGMILLER20SMITHADAMS…注意：每次只有有一个BREAK命令起作用用，但一次可以以在多个列列上使用BREAK命令，即BREAKON列名1ON列名25.2.7其它常常用命令显示数据库库对象结构构：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: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'结束束'BREAKONdnSKIP2COLdnHEADING'Department|Name'COLenHEADING'EMPLOYEE|NAME'COLehHEADING'STARTED|DATE'COLesHEADING'SALARY'FORMAT$99,999.99COLeaHEADING'ANNUAL|SALARY'FO