下载掌阅APP，畅读海量书库
立即打开
2.3 PL/SQL在SQL*Plus中的使用
SQL*Plus可以操作Oracle数据库，随着数据库一起安装，是编写PL/SQL的常用工具。启动后，将进入一个CMD窗口，利用命令连接指定的数据库实例后，即可编辑相应的代码，对数据库的数据进行操作。
2.3.1 启动SQL*Plus
SQL*Plus是PL/SQL初学者必须学习的工具，当然，它不是必须使用的工具，和它的功能相似的产品有很多，比如：SQL Developer等。通过SQL*Plus可以完成对数据库的很多操作，其中主要有：
◆对数据库的数据进行增加、删除、修改、查询操作。
◆编写和执行PL/SQL代码。
◆管理数据库对象，如用户、表空间、角色、表、存储过程、函数、视图等对象。
当确认Oracle数据库安装成功后，就可以利用SQL*Plus工具对数据库进行访问了。启动SQL*Plus的方法有多种，其中一种就是直接在【开始】菜单中单击【程序】→【Oracle-OraDb11g_home1】→【应用程序开发】→【SQL Plus】命令，如图 2.7 所示，即可进入SQL*Plus界面。
图 2.7 SQL*Plus启动路径
进入SQL*Plus界面后，还不能马上访问数据库中的数据，因为此时并没有连接到数据库，要想连接到指定的数据库，需要执行连接命令。下面的实例演示了如何连接指定的数据库实例。
【实例 2-4】连接scott数据库实例。
要求在SQL*Plus下，连接scott数据库实例，密码为“123456+a”，SID为“ORCL”，并查询该用户下DEPT表的数据。执行的连接脚本如下：
scott/123456+a@orcl
执行的查询脚本如下：
SELECT *FROM DEPT;
【代码解析】
◆scott表示数据库实例的用户名。
◆SELECT后面的星号表示查询表中的所有数据。
【执行效果】
打开SQL*Plus并执行相关脚本，执行效果见图 2.8。
图 2.8 在SQL*Plus中连接数据库并查询数据
除了使用上面的这种方法连接数据库外，也可以在【开始】→【运行】当中输入以下语句：
C:>sqlplus username/password@sid
使用这种方法，可以直接连接到某个数据库实例。
2.3.2 SQL*Plus追加文本命令
其实在SQL*Plus下运行SQL语句是很简单的，只需在“SQL>”提示符后面输入需要执行的SQL语句，并按下【Enter】键即可。利用“/”可以直接运行上一次的程序。
SQL*Plus是命令编辑器，开发人员可以利用它编辑SQL语句，执行各种命令等操作。本小节将介绍一下SQL*Plus中的追加文本命令，该命令用于在当前行尾追加一行，具体语法如下所示：
APPEND TEXT;
【语法说明】
◆APPEND表示追加文本的关键词。
◆TEXT表示要追加的文本，APPEND和TEXT之间尽量使用 2 个空格。
【实例 2-5】演示APPEND命令的使用方法。
要求首先对DEPT表执行基本查询，然后对该查询追加WHERE子句，查询DNAME为“SALES”的记录。执行SQL语句如下：
SELECT *FROM DEPT;
执行效果见图 2.9，图中列出了DEPT表中的所有记录。在该语句后面追加WHERE条件语句，只把DNAME为“SALES”的记录查询出来。整个操作过程见图 2.10。
从图 2.10 中可以看出，当追加WHERE子句后，可以按照计划把指定的记录查询出来。
图 2.9 查询DEPT表中的所有数据
图 2.10 追加文本后的命令
当使用APPEND命令时，它后面最好接 1 个以上空格，如果是 1 个空格的话，可能导致追加的文本和原来的文本之间没有空格。还有就是执行追加后的命令需要用到“/”才能正常执行。
2.3.3 SQL*Plus增加文本命令
本小节将介绍一下SQL*Plus中的增加文本命令，有 2 种用法，语法如下：
INPUT
INPUT TEXT
【语法说明】
◆INPUT：表示利用INPUT命令在缓冲区当前行之后可以添加多行。
◆INPUT TEXT：表示利用INPUT命令在缓冲区当前行之后添加一行。
【实例 2-6】演示INPUT命令的用法。
要求首先对DEPT表执行基本查询，然后利用INPUT命令对该查询追加排序子句。执行效果见图 2.11。
图 2.11 排序结果
该实例利用了在缓冲区当前行之后添加多行的方式创建了排序查询。除此之外，也可以利用INPUT命令直接添加“ORDER BY DNAME”这行代码，如“INPUT ORDER BY DNAME”，回车，执行查询。
2.3.4 SQL*Plus替换文本命令
利用替换文本的功能可以对某些错误的文本进行替换，以达到修改代码的目的。基本语法如下：
CHANGE /旧文本/新文本
CHANGE /文本
【语法说明】
◆CHANGE /旧文本/新文本：表示利用CHANGE命令以“新文本”替换缓冲区指定的“旧文本”。
◆CHANGE /文本：表示删除指定的文本。
【实例 2-7】演示CHANGE命令的用法。
要求首先查询DEPT表的前 2 条数据，然后利用CHANGE命令改为查询SALGRADE表的前 2条数据，执行效果见图 2.12。最后利用CHANGE命令去掉查询记录数限制，查询SALGRADE表的所有记录，执行效果见图 2.13。
图 2.12 查询SALGRADE表的前 2 条数据
图 2.13 查询SALGRADE表的所有记录
当利用CHANGE命令替换表名称时，需要两个表有相同的列，或利用星号代表所有列，否则会出现“列名不存在”的错误。
2.3.5 SQL*Plus删除命令
在SQL*Plus中使用的删除命令主要有以下几种。
1．删除缓冲区当前行
删除当前行的具体语法如下：
DEL
【实例 2-8】演示删除当前行命令的使用方法。
首先查询DEPT表的所有数据，然后删除当前行，并验证。执行过程见图 2.14。
【代码解析】
该实例首先分 2 行执行了一个查询命令，然后利用“/”重复执行上次执行的命令。读者可以发现，此时依然能够正常执行查询命令。当利用“DEL”命令删除当前行时，再次利用“/”执行查询命令，会发现提示“表名无效”的错误，说明“DEPT”这行已经被正常删除。
2．删除第 n 行
删除第 n 行的语法是在删除当前行的基础上完成的，语法如下：
DEL n
【实例 2-9】演示删除第 n 行命令的使用方法。
要求首先查询DEPT表的所有数据，并排序，然后利用删除命令，去掉排序部分，执行查询。执行过程见图 2.15。
图 2.14 删除当前行命令演示过程
图 2.15 删除排序行
【代码解析】
首先利用标准的查询列出了DEPT表中的数据，并按照“LOC”列进行排序；然后删除第 4 行，也就是“ORDER BY LOC”部分，利用“/”执行，读者可以发现，此时的查询，已经取消了按照“LOC”列进行排序。
3．删除第 n 行到当前行
删除第 n 行到当前行的语法结构如下：
DEL n*
【实例 2-10】演示删除第 n 行到当前行命令的使用方法。
要求首先查询DEPT表的所有数据，并排序，然后删除第 2 行到当前行，并验证删除后的语句。执行过程见图 2.16。
图 2.16 删除第 2 行到当前行
【代码解析】
首先利用标准查询列出了DEPT表中的数据，并按照“LOC”列进行排序；然后删除第 2 行到当前行（“ORDER BY LOC”）；最后利用“/”执行，读者可以发现，此时的查询不能正常执行，提示没有FROM关键字。也就是说，删除命令执行成功，已经删除了第 2 行到第 4 行。
除了以上介绍的删除命令外，还有其他的几种使用方式，这里不再一一举例说明，感兴趣的读者可以参考Oracle官方网站。
2.3.6 SQL*Plus清除缓冲区命令
SQL*Plus可以利用清除命令清除缓冲区中的程序，具体语法如下：
CLEAR BUFFER
【实例 2-11】演示如何清除缓冲区内容。
要求首先查询DEPT表的所有内容，然后清除缓冲区内容，最后进行验证。具体的执行过程见图 2.17。
图 2.17 清除缓冲区命令
当执行“CLEAR BUFFER”命令时，会提示“buffer已清除”，此时再利用“/”执行命令，会提示缓冲区中没有程序。
2.3.7 SQL*Plus显示缓冲区内容命令
利用显示缓冲区内容命令可以更好地查看缓冲区中的命令，并对其进行操作。显示缓冲区命令和删除命令类似，同样有着多种组合，这里只介绍其中的几种，更多的命令读者可以参考Oracle官方网站。
1．显示缓冲区中所有的命令行
显示所有行的命令语法如下：
LIST
【实例 2-12】演示如何显示缓冲区中的所有命令行。
要求首先清除缓冲区中的所有内容，然后查询DEPT表的数据，再利用显示缓冲区内容命令查看缓冲区内程序。具体操作步骤见图 2.18。
LIST命令可以使用缩写“LIS”、“LI”、“L”，在后面介绍的有关LIST命令也可以使用缩写，感兴趣的读者可以自己试验。
2．显示缓冲区第 n 行
在LIST命令的基础上添加参数，可以组成其他命令。显示缓冲区第 n 行的语法结构如下：
LIST n
【实例 2-13】演示如何显示缓冲区第 n 行。
要求首先清除缓冲区中的所有内容，然后查询DEPT表的数据，最后利用显示缓冲区内容命令查看第 2 行程序。具体操作步骤见图 2.19。
图 2.18 显示缓冲区中的所有命令行
图 2.19 显示缓冲区第 2 行程序
3．显示缓冲区第 n 行到最后一行内容
利用LIST命令可以显示缓冲区第 n 行到最后一行的程序，语法结构如下：
LIST n last
【实例 2-14】演示如何使用显示第 n 行到最后一行的内容命令。
要求首先清除缓冲区中的所有内容，然后查询DEPT表的数据，最后利用显示缓冲区内容命令查看第 2 行到最后一行的内容。具体操作步骤见图 2.20。
图 2.20 显示缓冲区第 2 行到最后一行的内容
2.3.8 SQL*Plus保存命令
开发人员在使用SQL*Plus操作数据库时，有保存命令的需求，通常会有 2 种方式来保存缓冲区的命令，下面就分别进行说明。
1．利用记事本保存
SQL*Plus可以调用记事本，然后把缓冲区的内容自动放入记事本中，利用记事本把其中的内容保存下来。具体语法如下：
ED
在SQL*Plus中输入该命令后，会提示“已写入file afiedt.buf”，并弹出记事本窗口，如图 2.21所示，此时可以利用记事本的【文件】→【另存为】命令来保存该文件。
图 2.21 SQL*Plus调用记事本
“ED”这个命令实际上是“EDIT”的缩写形式。
2．利用SAVE命令保存
SQL*Plus执行SAVE命令，可以把当前缓冲区的内容作为一个文件保存下来，具体语法如下：
SAVE file
【语法说明】
◆file指要保存文件的路径和名称。
【实例 2-15】验证如何利用SAVE命令保存缓冲区的内容。
要求首先清除缓冲区的内容，然后查询DEPT表的数据，最后利用SAVE命令保存当前缓冲区的内容。操作步骤见图2.22。当执行SAVE命令后，会在D盘下创建一个名为“sqltest.txt”的文件，打开后可以查看缓冲区的内容。
图 2.22 SQL*Plus保存缓冲区的内容
2.3.9 查询结果的格式化
在使用SQL*Plus进行数据查询时，会遇到格式不完美的情况，这会影响到开发人员的阅读效果。例如，一个表有很多字段，当不对查询结果进行格式化时，会出现列自动换行的情况，致使人们无法很好地阅读查询结果。这种情况可以参考图 2.23。
图 2.23 未格式化的查询结果
这是EMP表的查询结果，可以看到，在查询结果列表中，列出现了自动换行的情况，导致了阅读不方便。这是一个很常见的问题，所以，在使用SQL*Plus时，在很多情况下都需要使用者对查询结果进行格式化。下面介绍几种常用的格式化输出结果的命令。
1．设置查询结果显示的行数
在SQL*Plus中查询数据时，默认把所有的数据都显示在整个页面上，这样就会出现页面显示数据比较乱的情况，开发人员看起来会很吃力。此时可以利用每页显示记录数的设置来解决这个问题，相关的语法如下：
SET PAGESIZE n
【语法说明】
◆ n 代表每页显示的行数。在默认情况下，每页显示 14 行记录。需要注意的是，这里所说的每页并不是指一屏。
【实例 2-16】验证设置每页显示的记录行数。
要求使用命令设置每页显示的记录行数，然后查询EMP表中的数据。操作步骤可以参考图2.24。
图 2.24 每页显示 18 行的效果
该实例中页和页之间的间隔是一行（下面会对此进行讲解），并且每页显示 18 行，即图 2.24中标出部分。
2．利用SET设置页和页之间的间隔
设置页和页之间的行数可以帮助读者区分两个页面，具体语法结构如下：
SET NEWPAGE n
【语法说明】
◆ n 代码页和页之间的行数。
【实例 2-17】对两页之间的行数进行设置。
要求利用命令设置每页显示的记录行数，然后设置两页之间的行数，最后查询EMP表中的数据。操作步骤可以参考图 2.25。
图 2.25 中标出部分为一页，可以看出两页之间的间隔加大了，两页的数据也能看得更清楚了。
图 2.25 两页之间的行数设置
3．显示每页的行数以及两页之间的行数
利用下面的两个命令，可以显示每页的行数，也可以显示两页之间的行数。这两个命令的语法结构如下：
SHOW PAGESIZE
SHOW NEWPAGE
【实例 2-18】演示显示每页行数以及两页间隔数。
这两个命令很简单，直接在SQL*Plus中运行即可，运行结果和步骤见图 2.26。
图 2.26 查看页面行数和两页间隔数
4．设置行字符数
设置行字符数命令是一个非常有用的命令，利用该命令可以使自动换行的列能够正常显示，查询结果更加容易理解。其语法结构如下：
SET LINESIZE n
【语法说明】
◆ n 表示行要容纳的字符数。
与该命令配套的命令是显示行字符数据，其语法结构如下：
SHOW LINESIZE
【实例 2-19】演示如何设置每行字符数，具体操作步骤见图 2.27。
图 2.27 正常显示所有列
这种用法很普遍，读者应该灵活运用。
5．列名的格式化
通常和设置行字符数命令配合使用的还有一个命令，就是列名的格式化命令，利用这两个命令，可以使得查询结果更加友好。有关列名格式化命令的语法结构如下：
COLUMN column_name FORMAT dataformat
【语法说明】
◆column_name指格式化查询结果的列名。
◆dataformat指格式化后的显示格式，其含义见表 2-2。
表 2-2 数据格式设置
【实例 2-20】演示列名的格式化命令的用法。
要求对EMP表中的JOB列进行格式化，格式化后的列显示长度为 12 个字符。具体操作步骤见图 2.28。
图 2.28 JOB列格式化后的长度效果
当使用COLUMN命令设置列的格式之后，这个列的格式在当前窗口是一直保持的，除非重新设置该列的格式或者取消该列的格式。取消列格式命令的语法如下：
COLUMN column_name CLEAR
【实例 2-21】演示取消已经格式化的列的格式。
要求把已经格式化的JOB字段重新还原成默认格式，具体步骤见图 2.29。
图 2.29 取消列的格式化效果
6．保存查询结果
很多时候开发人员会把查询结果保存到文件中，在SQL*Plus中读者可以使用“SPOOL”命令来完成这项操作。其语法结构如下：
SPOOL filename
SPOOL OFF
【语法说明】
◆filename表示查询结果要保存的文件路径和名称，后缀名可以是“txt”或“sql”，根据实际需求来定。当指定的文件不存在时，会自动创建；当文件存在时，内容将被覆盖。
◆OFF表示该命令的结束，当完成查询时，调用该命令，表示查询结果输出完成，清空缓存。
【实例 2-22】演示如何保存查询结果。
要求查询EMP表，并把查询结果存放到C盘的“ss.txt”中。操作步骤见图 2.30。
图 2.30 保存查询结果
此时，查看ss.txt文件，即可看到保存的查询结果，如图 2.31 所示。
图 2.31 保存的查询结果
从ss.txt文件可以看到，它除了保存数据外，还保存了查询过程，这为数据的备份和再现提供了方便。
7．调用外部脚本文件
在SQL*Plus中可以调用外部脚本文件，也就是说，可以调用以前保存的执行脚本，而不用一句句地重新编写。调用外部脚本文件的语法结构如下：
@filename
【实例 2-23】演示如何调用外部脚本文件。
要求调用外部脚本文件执行查询，其文件名为“sqlq.txt”，位于C盘根目录下，该文件内容见图 2.32。
图 2.32 外部脚本文件内容
下面在SQL*Plus中调用“sqlq.txt”文件，该文件可以是“*.sql”类型，调用过程见图 2.33。
也可以利用START命令调用外部脚本文件，例如调用“sqlq.txt”文件，就可以写成“START C:\sqlq.txt”。
图 2.33 调用外部脚本文件
2.3.10 注释的使用
注释在所有的高级编程语言中都存在，利用它可以快捷地提高代码可读性，是编程代码中不可缺少的一部分。PL/SQL中支持注释的使用，这在前面已经介绍过。下面介绍一个有关注释的命令，该命令可以在SQL*Plus中使用，其语法格式如下：
REMARK comment
【语法说明】
◆comment表示注释语句。利用REMARK将在每行的开始使用，添加的注释只能是它所在的行，在一个完整的SQL语句中不能既包含REMARK又包含SQL脚本。
【实例 2-24】演示如何利用REMARK注释语句。
要求在查询EMP表的数据前，对该查询添加注释，并把所有语句保存到“sqlq.txt”文件中。具体操作步骤见图 2.34。
图 2.34 REMARK命令的使用
以上验证了SQL*Plus中如何使用该命令。读者还可以把这些命令写到记事本中，如图 2.35所示，然后在SQL*Plus中调用该记事本，查看执行效果，可以发现REMARK后面的语句被成功地注释掉了。
图 2.35 记事本中的REMARK命令
“REM”命令是“REMARK”命令的缩写形式，实际上前面介绍的命令很多都具有缩写形式，读者可以自己进行试验。
2.3.11 在SQL*Plus中编辑语句
在前面的小节中重点介绍了SQL*Plus的一些常用命令，这些命令能够帮助初学者深入地了解SQL*Plus工具及其使用方法。由于SQL*Plus是一个非常重要的编辑PL/SQL的工具，所以，读者应认真理解它的常用命令，本小节将介绍如何在SQL*Plus中编写程序。
有关在SQL*Plus中编写PL/SQL语句前面已经接触到了，这里只用一个实例来演示如何在SQL*Plus中编写自定义函数。
【实例 2-25】演示如何在SQL*Plus中编写自定义函数。
要求在SQL*Plus中编写一个自定义函数，并验证是否能正常使用。编写的具体脚本如下：
【代码解析】
◆整个脚本代表的含义就是创建一个自定义函数，该函数会把传入的两个参数相加并返回。
◆有关该脚本的详细解释，读者可以参考后面的自定义函数部分。
【执行效果】
在SQL*Plus中逐行编写以上脚本并执行，可以创建一个函数；利用查询语句可以调用该自定义函数。整个过程见图 2.36。
以上就是如何在SQL*Plus中编写PL/SQL的实例。刚开始时读者可能不适应以这种方式来编写脚本，所以初学者应多加练习。
图 2.36 创建并调用自定义函数
DUAL表实际上是Oracle中实际存在的表，有 1 行 1 列，任何用户均可读取，通常在没有目标表的查询语句块中使用。