PL/SQL 杂谈(六)（pl/sql for）

承接（五）。内容书写不容易，各位友友们，如果可以，麻烦点个赞，让我有动力可以写下去。

这节主要谈谈 关于 SQL*Plus 的其他任务。SQL*Plus 有大量命令，但由于文章字数有限，我们只能聊一聊那些特别重要的、或是特别让人困惑的少数一些命令。

1、个性化设置

我们可以对 SQL*PLUS 进行个性化设置，通过命令行的方式改变它内置的变量值和设置。我们已在上面的示例中看到了 SET SERVEROUTPUT ON 命令，

SQL*PLUS 中的 SET 命令有许多选项，如 SET SUFFIX(改变默认的文件扩展名)、SET LINESIZE n(设置显示时每行最大字符数，超过部分进行折行处理)等，

可以用下面的命令来查看当前会话的各选项的值：

SQL>SHOW ALL

------

SQL*Plus 也可以创建和操作它自己的一些内存变量，它预留了一些特殊的变量，这些变量可以影响它的行为。

实际上，SQL*PIus中有两类变量：DEFINE 和 绑定变量。我们可以使用 DEFINE 命令给一个 DEFINE 变量赋值：

SQL> DEFINE x="the answer is 42"

如要查看x的值，可以这样:

SQL>DEFINE x

DEFINE x="the answer is 42"(CHAR)

---

若要引用这样的变量，需要使用连接符号“&”。SQL*plus 在把语句发送给 Oracle 数据库之前，会对变量做一个简单的替代，所以当我们希望一个字符串被当作变量进行替代，我们就需要把它用单引号括起来:

SELECT '&X' FROM DUAL;

----

对于绑定变量，我们需要首先对它进行声明，然后就可以在 PL/SQL 里使用它了，如要显示它的值，就用 SQL*Plus 里的 PRINT 命令:

SQL> VARIABLE x VARCHAR2(10)

SQL> BEGIN

:x := 'hello';

END ;

----

PL/SQL procedure successfully completed.

SQL> PRINT :x

----

X

-----------

hello

在这里，大家可能有点小困惑，因为现在出现了两个不同的变量“x”，一个是定义出来的，一个是声明出来的:

SQL> SELECT :x,'&x' FROM DUAL;

----

原值 1: SELECT :x,'&x' FROM DUAL

新值 1: SELECT :x,'the answer is 42' FROM DUAL

:X 'THEANSWERIS42'

--------------------------- -----------------------------------

hello the answer is 42

-----------------------------------------

只需记住，DEFINE 的变量总是被SQL*PLUS 当作扩展的字符串使用，而 DECLARED 的变量可以被 SQL 和 PL/SQL 当作真正的绑定变量使用。

------------------

2、将输出保存到文件

时不时地，我们需要把一个 SQL*Plus 会话的输出保存到一个文件中去，用于生成报告，或是记录我们的操作过程，或是我们需要把执行的命令记录下来以便日后再次运行。在SQL*Plus中，一个简便的方法是使用 SPOOL 命令:

SQL> SPOOL report

---我们要事先在当前目录新建一个 run_report.sql文件，或者指明具体路径。写一段SQL语句，比如 SELECT * FROM EMPLOYEES

SQL> @run_report.sql

........输出的内容不断向下滚动..........

第一条命令的意思就是写入名为 report.lst的文件，这个文件所在的目录是当前所在目录，可以打开cmd，就看到当前目录……

SQL> SPOOL OFF

SPOOL OFF命令告诉SQL*Plus，停止将输出保存到文件，同时关闭文件。

而 report 文件扩展名.1st 是默认的，可以通过在 SPOOL 命令中指定扩展名来改变默认扩展名:

SQL> SPOOL report.txt

------------------------------

3、退出 SQL*Plus

使用 EXIT 命令，可以退出 SQL*Plus，返回到操作系统环境:

SQL> EXIT ;

如果我们退出之前还在进行 SPOOL，那么 SQL*Plus 会自动停止 SPOOL 并关闭文件，如果我们在会话中修改了一些表的数据，没有用显式的事务控制语句结束事务，就退出SQL*Plus，会有什么后果?

默认地，SQL*Plus 退出前会强制进行事务提交(COMMIT)，特殊情况是因为 SQL错误而导致会话终止，并且我们在SOL*Plus会话里发出过 WHENEVER SQLERROR EXIT ROLLBACK 指令。

若要断开数据库连接，但仍留在 SQL*Plus 环境，可以使用 DISCONNECT 命令，这个命令的表现是这样的:

SQL> DISCONNECT

切换连接时，我们无需使用 DISCONNECT 命令——我们只要再发一条 CONNECT 命令即可，SQL*Plus 会断开前一个连接，再建立新的连接。但实际上，有时断开再重新连接实属必要：如果我们正在使用操作系统验证，脚本就可能会自动重新连接，但使用的是错误的账号，这样的情况是有可能发生的。

-----------------------

4、 编辑语句

SQL*PLUS 会在缓存中保留最新接收的一些语句，我们可以对这些语句进行编辑，既可以使用内置的行编辑器、也可使用任意的外部编辑器。

下面进行演示如何设置和使用一个外部编辑器：

edit 命令、可以使得SQL*PLUS 把当前保留在缓存中的命令存储能到一个文件去，暂时停止SQL*Plus 并调用编译器:

SQL>EDIT

保存命令的文件名默认是 afiedt.buf,我们也可以使用 SET EDITFILE 命令进行修改。或者，如果我们想编辑一个现有的文件，

只需把文件名作为EDTT命令的变量:

SQL> EDIT abe.pkg

如果我们没有abe.pkg这个文件，他会提示是否新建。一旦我们保存文件并退出编辑器、SQL*PLUS 会话就将新边界的文件内容读入缓存，然后会话继续。

ORACLE 默认的外部编辑器是:

（1）UNIX、Linux 相关操作系统使用 ed.

（2）Windows 系列使用 Notepad.

虽然默认编译器的选择已经被硬编码到 SQLPLUS 可执行文件中了，我们仍然可以通过设置 SQL*Plus 的 _EDITOR 变量的值，来轻松地改变当前的编码器。

看下面的示例:

SQL> DEFINE _EDITOR = /bin/vi

---不建议修改

/bin/vi 是一款“怪人”们常用的编辑软件的全路径。为安全考虑,建议在这里使用编辑器的全路径名称。

如果我们只想使用 SQL*Plus 内置的行编辑器(也确实挺方便的)，有一些基本的命令需要了解：

（1）L

显示最后使用的语句。

（2）n

把语句的第n行作为当前行。

（3）DEL

删除当前行。

（4）C/old/new

当前行中，把第一个出现的 old 替换成 new。分隔符(这里是一个斜杠)可以是任意字符

（5）n text

把 text 作为第n行的内容,

（6）I

在当前行之后插入一行。若要在当前行之前插入一行，使用0行命令(即“0 text”)。

---------------

5、在启动时自动加载自定义环境

若要自定义我们的 SQL*Plus 环境，在不同的会话中都保有我们的自定义，我们需要编辑一个或是多个自动启动脚本。

SQL*Plus在启动时的表现是这样的:

(1)搜索$

ORACLE_HOME/sqlplus/admin/glogin.sgl文件，如果成功，执行文件里面的命令，这是个“全局”登录脚本，

任何人只要执行的是同一个 Oracle home 下的 SQL*Plus，不管是从哪个目录登录的，都会执行这个脚本。

（2）下一步，如果当前目录下存在 login.sql文件，它就运行这个文件。

启动脚本内所包含的语句内容，可以跟任何 SOL*Plus脚本内容一样：SET命令、SQL语句、列格式化命令，等等。

上述的两个启动脚本都不是必须存在的，如果都存在，则先执行glogin.sql，然后是login.sql,如果两个文件中的变量设置有冲突，则以最后一个脚本为准。