Oracle10g数据库基础教程 第05章SQLPLUS

第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行DELn删除当前行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行DELmn阐明命令例：在SQL*PLUS中编辑SQL缓冲区中的SQL语句。显示结果：SQL>SELECTdeptno,dname2FROM dept;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,sal2FROM emp3WHEREempno=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>键继续请输入工资界限: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]]格式模型：结果1234 01234$1234L12341234.001,234 举例A15999999 099999$9999L99999999.999,999元素An9 0$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: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……脚本文件SETPAGESIZE20

TTITLE'雇员受雇情况表'

BTITLE'终了'

BREAKONdnSKIP2

COLdnHEADING'Department|Name'

COLenHEADING'EMPLOYEE|NAME'

COLehHEADING'STARTED|DATE'

COLesHEADI