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;DEPTNODNAME

10ACCOUNTING20RESEARCH30SALES40OPERATIONS问题：增加加一列查询询信息，不重新输入入SQL语句句，如何实实现？SQL>L11*deptno,dnameSQL>A,loc1*deptno,dname,loc显示结果：：SQL>L1SELECTdeptno,dname,loc2*FROMdeptSQL>/DEPTNODNAMELOC

10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON5.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_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后，才会把屏屏幕输出内容写入文件！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>键继继续请输入工工资界限限: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'结束'BREAKONdnSKIP2

COLdnHEADING'Department|Name'

COLenHEADING'EMPLOYEE|NAME'

COLehHEADING'STARTED|DATE'

COLesHEADING'SALARY'FORMAT$99,999.99COLeaHEA