Oracle 数据库应用：第6章 SQL工具

第6章SQL工具学习目标了解SQL*Plus的作用学会启动和退出SQL*Plus掌握常用的SQL*Plus命令的使用6.1SQL*Plus

概述SQL*Plus工具是随Oracle数据库服务器或客户端的安装而自动进行安装的管理与开发工具，Oracle数据库中所有的管理操作都可以通过SQL*Plus工具完成，同时开发人员利用SQL*Plus可以测试、运行SQL语句和PL/SQL程序。

SQL*Plus的主要功能:描述表的结构；编辑输入各种SQL和PL/SQL语句；执行输入的SQL和PL/SQL语句；执行各种SQL*Plus命令；格式化SQL和PL/SQL语句的输出结果等。注意:SQL*Plus不能直接操纵数据库中的数据。6.2启动SQL*Plus启动SQL*Plus的两种方式：命令行方式GUI方式注意:oracle11g中的sql*plus是以命令方式启动的，不像oracle10g中的GUI方式，因此在11g中没有单独的登录界面，也没有GUI界面。不过你可以创建一个sql*plus快捷方式，方便每次的登录。

从命令行中启动SQL*Plus为了从命令行中启动SQL*Plus，可以从DOS中输入sqlplus

命令。找到Oracle存储的文件夹选择“开始”菜单→“运行”→在打开文本框输入cmdcd

路径名在新的路径下输入:sqlplus

输入用户名（如：scott）、口令（如：tiger）启动成功会出现SQL>从命令行中启动SQL*Plus假设用“OS>”代替操作系统提示符。

OS>sqlplus

SQL*Plus:

Release

11.1.0.6.0

-

Production

on

Fri

Nov

7

10:28:26

2008

Copyright

(c)

1982,

2007,

Oracle.

All

rights

reserved.

Enter

user-name:

scott

Enter

password:

tiger

Connected

to:

Oracle

Database

11g

Enterprise

Edition

Release

11.1.0.6.0

-

64bit

SQL>

从命令行中启动SQL*Plus可以在命令行中提供用户名和密码的方式进入SQL*Plus：

OS>sqlplus

scott/tiger

不建议用这种方式，因为在某些操作系统中其他用户可以看到你输入的命令行参数，这样他们就能获得你的密码。

从命令行中启动SQL*Plus

在多用户系统，你应该使用/NOLOG选项来启动SQL*Plus，这种方式只是进入SQL*Plus环境，而不会连接到数据库。接下来再通过CONNECT命令，并提供用户名、密码进入数据库。

OS>

sqlplus

/nolog

SQL*Plus:

Release

11.1.0.6.0

-

Production

on

Fri

Nov

7

10:28:26

2008

Copyright

(c)

1982,

2007,

Oracle.

All

rights

reserved.

SQL>

CONNECT

scott/tiger

SQL>

Connected.

从命令行中启动SQL*Plus如果运行SQL*Plus的这台计算机上已经正确的配置了OralceNetConfigurationAssistant(即进行网络服务名的命名，也称数据库的连接标识符)，并且远程数据库（即运行在另外一台计算机上的数据库）的管理员已经授权了你的访问，你就可以用SQL*Plus连接到这些远程数据库上。SQL>CONNECT

scott/tiger@test

SQL>Connected

通过GUI启动SQL*Plus选择“开始”→“所有程序”→“Oracle-OraDb10g-home1”→”ApplicationDevelopment”→”SQLPlus”命令。出现SQL*Plus的界面。输入用户名称、口令、主机字符串（如果连接的数据库存放在本机上可不填）直接登录连接到数据库为SQL*Plus建立快捷方式通过快捷方式启动并直接登录数据库的步骤：

在路径D:\oracle\product\10.2.0\db_1\BIN\中找到sqlplusw.exe。单击右键选取“快捷方式”。在建好的“快捷方式sqlplusw”上单击右键，选取“属性”。在“属性”的“目标”栏中添加如下样式的参数：

D:\oracle\product\10.2.0\db_1\bin\sqlplusw.exescott/tiger@test然后单击“确定”按钮，并将该“快捷方式sqlplusw”拖到桌面。

获取SQL*Plus命令的帮助为获得某个SQL*Plus命令的帮助，只需在SQL>提示符下输入:

HELP<命令名称>HELPindex

命令可以显示SQL*Plus中的所有命令。退出SQL*Plus要退出SQL*Plus

返回操作系统，只需在SQL>提示符下，输入exit

或quit

命令。

6.3编辑语句和命令通常所说的SQL语句、PL/SQL块都是SQL*Plus语句，它们执行完后可以保存在SQL缓冲区中，并且只能保存一条最近执行的SQL*Plus语句。除了SQL*Plus语句，在SQL*Plus中执行的其他语句都被称为SQL*Plus命令。它们执行完后不保存在SQL缓冲区中。编辑已保存在SQL缓冲区中语句的方法：使用SQL*Plus内嵌的命令行编辑器。将缓冲区中的内容传递给记事本(Windows系统编辑器)。

编辑命令输入SQL语句在语句最后加分号，并按回车，则立即执行该语句；语句输入最后加空格并回车，换行后再按回车，则结束SQL语句输入但不执行该语句；语句输入结束后按回车，换行后按斜杠（/），立即执行该语句。输入完PL/SQL程序回车换行后，如果按点号（.），则结束输入，但不执行；输入斜杠（/），则立即执行。

常用编辑命令显示缓冲区LIST编辑缓冲区A[PPEND]——将指定的文本追加到缓冲区内当前行的末尾；C[HANGE]——修改缓冲区中当前行的文本；DEL——删除缓冲区中当前行的文本；N——用数值定位缓冲区中的当前行；I[NPUT]——在缓冲区当前行的后面新增加一行文本；ED[IT]——以文本编辑器方式打开缓冲区，进行编辑。执行缓冲区RUN--显示SQL缓冲区中的内容和运行结果，并使SQL缓冲区中的最后一行成为当前行（以*表示的行）。

/--只显示运行结果，而不显示SQL缓冲区的内容，也不使SQL缓冲区中的最后一行成为当前行。清除缓冲区CLEARBUFFERSQL*Plus提供的编辑命令DESCRIBE命令DESCRIBE命令的功能：返回数据库中所存储的对象的描述。对于表、视图等对象来说，可以列出其各个列的定义（名称、是否可以取空值、数据类型等）。输出过程、函数以及程序包等的规范。DESCRIBE命令的语法格式如下：DESC[RIBE]{[schema.]object[@connect_identifier]}schema：对象所属的模式或用户。object：获取其描述的对象的名称。connect_identifier：数据库的连接标识符。【例】用DESCRIBE命令查看emp

表结构。

SQL>DESCRIBEemp小窍门若在SQL*Plus中输入了很长的SQL语句后，想通过DESCRIBE命令查看表结构，但又不想取消刚输入的SQL语句，怎么办？解决方法：在下一行以“#”开头，临时先运行一个SQL*Plus命令，执行完后，还可以继续输入刚才的SQL语句。【例】SQL>SELECT*FROMdept2WHERE

3#DESCdept

名称是否为空？类型-----------------------------------DEPTNONOTNULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)3deptno=20;脚本文件脚本文件是为那些反复执行的各种任务而设计的。用户可以使用自己喜欢的编辑器创建一个脚本文件(.sql)，并将所有语句或命令全部输入其中。在SQL*Plus中，使用start

和

@

命令来运行脚本文件，还可以在命令行中传递脚本需要使用的任何参数。当调用脚本时，如果该脚本文件不在用户的当前工作目录中，用户必须使用指定的目录名称。常用文件操作命令脚本文件的创建(.sql)SAVEfilename[CREATE]|[REPLACE]|[APPEND]功能：将SQL缓冲区的内容保存到由filename

指定的文件。默认文件扩展名为.sql。脚本文件的装载GETfilenameLIST|NOLIST功能：将由filename指定的文件读入SQL缓冲区。LIST选项表示加载文件到缓冲区时会列出文件的内容。脚本文件的编辑EDITfilename脚本文件的执行START|@filename[arg1arg2arg3…]arg1、arg2、agr3等是赋予替换变量的值脚本文件的注释REMARK：单行注释，放在一行语句的头部，表示该行为注释。--：单行注释。/*……*/：多行注释。常用文件操作命令各种操作及运行结果的“假脱机”复制(创建“假脱机”文件.lst)SPOOLfilename[CREATE]|[REPLACE]|[APPEND]查看当前的“假脱机”状态SPOOL关闭“假脱机”文件SPOOLOFF交互式命令如果希望运行时提示用户交互式地提供相应的值，就需要编写交互式SQL命令。交互式SQL命令一般保存在脚本文件中，以便进一步提高脚本文件的可重用性。常用的交互命令类型有：替换变量绑定变量与用户通信替换变量在SQL*Plus环境中，可以使用替换变量来临时存储有关的数据。Oracle系统提供了以下3种类型的替换变量：&&&define&替换变量在select语句中，如果在某个变量前面使用了&符号，那么表示该变量是一个替换变量。在执行select语句的过程中，系统会提示用户为该变量提供一个具体的数据。【例】

Selectename,sal,job,hiredate

fromempwheresal>&amount;&&替换变量在select语句中，如果需要重新使用某个变量并且不希望重新提示输入该值，那么可以使用&&替换变量。为了避免为同一个变量提供两个不同的值，且使得系统为同一个变量值提示一次信息，那么可以使用&&符号。用在同一SQL语句中；【例】Selectempno,ename,job,&&columnname

fromempwheresal=3000orderby&&columnname;定义替换变量定义替换变量命令格式：DEFINEvariable_name[=text]注意：用DEFINE定义的替换变量总是采用CHAR数据类型，其他数据类型应使用ACCEPT命令来定义。【例】Definedeptname=′sales′Select*fromdeptwheredname=upper(′&depename′);查看与清除替换变量查看替换变量命令格式：DEFINE[variable_name]说明：不指定variable_name

则查看所有替换变量。清除替换变量命令格式：UNDEFINEvariable_name绑定变量绑定变量命令格式：VARIABLEvariable_name

datatype说明：当在SQL语句或PL/SQL块中使用绑定变量时，必须在绑定变量前加冒号“：”。当直接给绑定变量赋值时，需要使用EXECUTE命令。显示定义的绑定变量：PRINT<绑定变量1><绑定变量2><绑定变量3>…绑定变量【例】SQL>VARIABLEv1NUMBERSQL>VARIABLEv2VARCHAR2(10)SQL>EXECUTE:v1=7788PL/SQL过程已成功完成。SQL>EXECUTE:v2=′KING′PL/SQL过程已成功完成。SQL>SELECTenameFROMempWHEREempno=:v1ORename=:v2;与用户通信与用户通信的命令主要有：PROMPTPAUSEACCEPTPROMPT命令PROMPT命令格式：PROMPT[text]功能：将指定的提示信息text或一个空行输出到屏幕上。PAUSE命令PAUSE命令格式：PAUSE[text]功能：暂停脚本文件的运行，将指定的提示信息text或一个空行输出到屏幕上，等待用户按Enter键响应后继续执行。ACCEPT命令ACCEPT命令格式：ACCEPTvariable_name[datatype][PROMPTtext][HIDE]功能：接受用户输入的替换变量的值。说明：可以让用户指定替换变量的数据类型。PROMPTtext：指定提示信息。HIDE：隐藏输入。一个例子UNDEFINEpwdabACCEPTpwdPROMPT′请输入密码：′HIDEDEFINEpwdPROMPTPROMPT显示xx部门xx工种的员工姓名PROMPT================================PROMPTPAUSE′按<Enter>键继续′ACCEPTaNUMBERPROMPT′请输入部门编号：′ACCEPTbCHARPROMPT′请输入工种名称：′DEFINEaDEFINEbSELECTenameFROMempWHEREdeptno=&aANDjob=′&b′;显示环境变量SHOW命令格式：SHOWoption功能：显示当前SQL*Plus环境变量中的系统变量、错误信息、初始化参数、当前用户信息等。Option选项可以是：system_variable：系统变量all：所有系统变量errors：错误信息parameters：初始化参数的值release：数据库的版本SGA：SGA的大小。只有具有DBA

权限的用户才能使用该选项sqlcode：数据库操作之后的状态代码user：当前连接的用户设置环境变量SET命令格式：SETsystem_variable1value1[system_variable2value2]…功能：用于设置系统变量的值。部分环境变量介绍：AUTOCOMMIT：是否自动提交，默认为OFFFEEDBACK：当一个查询选择出至少n行记录时，就在查询结果集的后面显示返回的行数。默认为ON，默认的n值是6LINESIZE：每行的宽度，默认为80NUMFORMAT：数值数据输出占的位置数，默认为10PAGESIZE：每页显示的行数，默认为14SERVEROUTPUT：是否显示PL/SQL块或存储过程的输出，默认为OFFTIME：是否在SQL*Plus命令提示符前显示系统当前时间，默认为OFFTIMING：是否显示执行SQL语句、PL/SQL块花费的时间，默认为OFF保存设置环境变量STORESET命令格式：STORESETfilename[CREATE]|[REPLACE]|[APPEND]功能：将设置好的环境变量（或COLUMN格式化命令）保存到指定的脚本文件中。以后可以用START命令来运行该脚本文件，重现当前的环境设置。SQL*Plus中查询结果的显示在使用SQL*Plus执行查询操作时，经常会遇到查询结果的显示格式非常混乱。遇到这种问题时，建议使用以下3种方法来解决：(1)忍受它。(2)查询少量的列。(3)格式化查询结果。格式化查询结果SQL*Plus提供的用于格式化结果集的命令主要有：ColumnPausePagesizeLinesizeFeedbackNumformatLong使用格式化命令时，应该遵循的规则：格式化命令设置之后，将会一直起作用，直到该会话结束或下一个格式化命令的设置；每一次报表结束时，重新设置SQL*Plus为默认值；如果为某个列指定了别名，必须引用该别名，而不能使用列名。格式化列COLUMNColumn命令用来控制某个列的显示格式，其详细语法格式如下：COLUMN[{column_name|column_alias}[option]]修改列标题显示SQL>COLUMNenameHEADING雇员SQL>COLUMNsalHEADING月薪SQL>SELECTename,salFROMempWHEREempno=7788;雇员月薪---

-----SCOTT3000可以使用COLUMN命令的HEADING子句来重新定义列标题。标题分行显示SQL>COLUMNenameHEADING雇员|姓名JUSTIFYCENTERSQL>COLUMNsalHEADING每月|工资JUSTIFYCENTERSQL>SELECTename,salFROMempWHEREempno=7788;雇员每月姓名工资

---

------

SCOTT3000“|”为分行分割符。JUSTIFY子句指定列标题（而不是其下的数据）的对齐方式（LEFT、CENTER、RIGHT）。对于NUMBER类型的列，列标题默认在右边，其他类型默认在左边。格式化数据的显示格式使用COLUMN命令的FORMAT子句来定义列的宽度和显示格式。SQL>COLUMNename1HEADING雇员1FORMATA6SQL>COLUMNename2HEADING雇员2FORMATA6TRUNCATEDSQL>COLUMNsal1HEADING月薪1FORMATL99999.999SQL>COLUMNsal2HEADING月薪2FORMAT$00，999.000SQL>COLUMNcommHEADING奖金FORMATS99999NULL无SQL>SELECTename,enameename1,enameename2,sal,salsal1,salsal2,commFROMempWHEREdeptno=30;可以按列名、列的别名对数据进行格式化。An:设置宽度。TRUNCATED：设置的宽度不足以显示数据时截断。L:显示本地货币符号。$：显示美元符号。S：显示符号位(+、-),：千隔符0：强制显示前导0NULLtext：当数据为空时，显示text文本。复制列的格式在COLUMN命令中可以使用LIKE子句来复制前面定义好的列的显示格式。SQL>COLUMNenameHEADING雇员SQL>COLUMNsalHEADING月薪FORMAT$99999.99SQL>COLUMNcomm

LIKEsalHEADING奖金NULL无SQL>SELECTename,sal,commFROMempWHEREempn