技术 向TOM咨询
关于约束、CASE语句和游标
作者：Tom Kyte
我们的技术专家谈完整性、时间和归属问题。
我仔细阅读了《Oracle9i 数据库概念手册（Oracle9i Database Concepts Manual）》和你们的站点，但对下述概念仍不明白：
"定义为可延迟（deferrable）的约束可以指定为：
1. initially immediate（初始化立即执行）或
2. initially deferred（初始化延迟执行）。"
我知道什么是延迟约束，但不明白什么叫"初始化立即执行的可延迟约束"和"初始化延迟执行的可延迟约束"。请解释二者的区别。还有，这些约束有什么用途？这是通常容易混淆的问题。我希望下面的例子能解释清楚。初始化立即执行/延迟执行规定了在默认情况下应该如何执行约束：
初始化立即执行--在每条语句执行结束时检验约束
初始化延迟执行--一直等到事务完成后（或者调用set constraint immediate语句时）才检验约束
来看下面的代码：
SQL> create table t
2( x int constraint
check_x check ( x > 0 )
deferrable
initially immediate,
3y int constraint
check_y check ( y > 0 )
deferrable
initially deferred
4)
5/
Table created.
SQL> insert into t values ( 1,1 );
1 row created.
SQL> commit;
Commit complete.
所以，当两个约束同时满足时才能正确无误地插入行。但是，如果我试图插入违反CHECK_X约束（初始化立即执行的约束）的行，则系统会立即检验约束，并得到下面的结果：
SQL> insert into t values ( -1,1);
insert into t values ( -1,1)
*
ERROR at line 1:
ORA-02290: check constraint
(OPS$TKYTE.CHECK_X) violated
由于CHECK_X是可延迟但初始化为立即执行的约束，所以这一行立刻被拒绝了。而CHECK_Y则不同，它不仅是可延迟的，而且初始化为延迟执行，这就意味着直到我用COMMIT命令提交事务或将约束状态设置为立即执行时才检验约束。
SQL> insert into t values ( 1,-1);
1 row created.
现在它是成功的（总之到目前为止是成功的）。我将约束检验延迟到了执行COMMIT的时候：
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint
(OPS$TKYTE.CHECK_Y) violated
此时数据库将事务回滚，因为违反约束导致了COMMIT语句的失败。这些语句说明了初始化立即执行与初始化延迟执行约束之间的区别。initially（初始化）部分指定Oracle什么时候会进行默认的约束检验--是在语句结束时［immediate（立即执行）］，还是在事务结束时［deferred（延迟执行）］。我还要说明deferred（可延迟）子句有什么用。我可以发出命令，让所有可延迟的约束变为延迟执行的。注意，你也可以对一个约束使用该命令；你不必让所有可延迟的约束都变为延迟执行的：
SQL> set constraints all deferred;
Constraint set.
SQL> insert into t values ( -1,1);
1 row created.
由于将初始化立即执行的约束设置为延迟执行的模式，这个语句似乎执行成功；但是，当我用COMMIT语句提交事务时，看一下会发生什么：
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint
(OPS$TKYTE.CHECK_X) violated
事务提交失败并回滚，因为在COMMIT语句之后对约束进行了检验。相反，我可以将初始化为延迟执行的约束变为"立即"执行的约束：
SQL> set constraints all immediate;
Constraint set.
SQL> insert into t values ( 1,-1);
insert into t values ( 1,-1)
*
ERROR at line 1:
ORA-02290: check constraint
(OPS$TKYTE.CHECK_Y) violated
前面在我提交前能执行的语句现在立即出了问题。因为我手动修改了默认的约束模式。
延迟约束有哪些实际用处呢？ 有很多。它主要用于物化视图（快照）。这些视图会使用延迟约束来进行视图刷新。在刷新物化视图的过程中，可能会破坏完整性，而且将不能逐句检验约束。但到执行COMMIT时，数据完整性就没问题了，而且能满足约束。没有延迟约束，物化视图的约束可能会使刷新过程不能成功进行。
使用延迟约束的另一个普遍原因是，当预测是否需要更新父/子关系中的主键时，它有助于级联更新。如果你将外键设为可延迟、但初始化为立即执行，那么你就可以
将所有约束设置为可延迟。
将父键更新为一个新值--至此子关系的完整性约束不会被验证。
将子外键更新为这个新值。
COMMIT--只要所有受更新影响的子记录都指向现有的父记录，这条命令就能成功执行。
如果没有延迟约束，这一更新过程将极为艰难。参见asktom.oracle.com/~tkyte/update_cascade/index.html中给出的没有这一特性时进行级联更新所必需的示例代码！
此外，你可以在各种多语句事务中使用延迟约束，这些事务在处理的过程中需要暂时破坏完整性，但最后它们都会物归原样。
如何计算时间
你是如何计算asktom.oracle.com第一页中AGE列显示的时间的？我之所以问这个问题是因为我看到它有多种格式，如9个月3小时；2.3年；19个小时；等等。我是Oracle新手，想知道你们在使用哪种日期计算法。
我就是使用Oracle8i第2版（8.1.6版）中介绍的使用已久但很好用的CASE语句：
Select
case
when sysdate-timestamp < 1/24
then round(24*60*(sysdate-timestamp))
|| ' minutes old '
when sysdate-timestamp < 1
then round(24*(sysdate-timestamp))
|| ' hours old '
when sysdate-timestamp < 14
then trunc(sysdate-timestamp)
|| ' days old '
when sysdate-timestamp < 60
then trunc((sysdate-timestamp)/7)
|| ' weeks old '
when sysdate-timestamp < 365
then round(months_between
(sysdate,timestamp))
|| ' months old '
else round(months_between
(sysdate,timestamp)/12,1)
|| ' years old '
end age, ...
如果你想在Oracle8i的PL/SQL中使用CASE语句，则会出现一个错误消息，因为PL/SQL语法分析程序不识别CASE语句。（请注意，在Oracle9i不存在这样的问题。）为了避开Oracle8i的限制，你可以
将CASE语句隐藏在视图当中，并用PL/SQL来查询视图。
使用嵌套的DECODE语句来代替CASE语句。
我本人愿意使用视图，但读者Martin Burbridge在asktom.oracle.com 网站上公布了下面这段DECODE代码：
decode(sign(sysdate-timestamp-1/24),-1,
round(24*60*(sysdate-timestamp))
|| ' minutes old ',
decode(sign(sysdate-timestamp - 1), -1,
round(24*(sysdate-timestamp))
|| ' hours old ',
decode(sign(sysdate-timestamp-14),-1,
trunc(sysdate-timestamp)
|| ' days old ',
decode(sign(sysdate-timestamp-60),-1,
trunc((sysdate-timestamp)/7)
|| ' weeks old ',
decode(sign(sysdate-timestamp-365),-1,
round(months_between
(sysdate,timestamp))
|| ' months old ',
round(months_between
(sysdate,timestamp)/12,1)
|| ' years old '
))))) age
它与CASE语句的功能完全相同--只是不太明显。
文件放在哪？
我正在考虑为一个应用程序设计些选项，利用它用户可以上传和存储可供他人下载的文档。文档可以是平均大小为150K的Microsoft Word文档。最初需要（从CD）移植18000到20000个文档，当使用该应用程序时存储数量会增加到大约25000个文档。浏览器前端是用于上传和下载的PL/SQL插件（PL/SQL cartridge）页面。一开始，会有400到500人几乎同时访问该应用程序，两周内每天将有300人访问（分散访问）。 文档本身在数据库中作为BLOB存储。
从使用方面考虑，你觉得这样的选项好吗？它会过多占用系统全局区（SGA）吗？考虑到应用程序的需求以及前端（基于浏览器），除了保存为BLOB，还有没有其他选择，如文件系统？
我什么都存在数据库里。就是这样。如果数据就是你的一切，无论它们有什么样的值，事实上都要放到数据库中，在那里数据可以得到专业化的管理、备份，恢复而且安全。除了这些实实在在的好处，你还可以索引及搜索文档。（诚然，用文件系统也可以做这些，但在索引和文档之间不存在完整性。）在数据库中，你可以转换文档格式（例如，上传一个DOC文件，而显示为HTML格式）。你的数据是完全集成的、安全的、有备份的而且随时供你使用。
在Oracle公司内部，我们将一个几千吉字节的数据库作为整个公司的一个单一的文件服务器。公司所有文档都存在那里，存在这样一个单一的地方，可以对这些文档进行备份、搜索、建立索引和访问。在常规的文件系统中管理成千上万的文档是不可能的，即便文件系统能存下这些文档。
至于SGA的问题，这完全在你。如果你不想把BLOB放在缓冲区里，可以对其使用NOCACHE，这样你就不必担心"过多占用"SGA的问题了。
游标（Cursors）放在哪？
你能告诉我在编写PL/SQL代码时最好把游标放在哪吗？我们应该把它们放在包说明中还是包体中？我问这些问题是因为和我一起工作的一名开发人员硬要把所有游标都放在包说明中。他告诉我这样做才对。的确，如果在包中不止一次使用这些游标，我们应该把它们放在包说明中。但这个包里的所有游标都只使用一次，所以我认为应该把它们放到调用它们的过程/函数的声明部分。我说得对吗？把所有游标都放在包说明中有什么优缺点？游标的放置位置影响性能吗？
无论现在、过去还是将来我个人的偏好都是在大多数情况下使用隐式游标，也就是说根本就不显式地定义游标！例如：
is
...
begin
....
for x in ( select * from emp )
loop
这个技巧用于大约50到100行以内的结果集时特别好。比起显式游标来我更喜欢这种方法是因为：
与使用显式游标相比，使用它的CPU效率更高。
我可以浏览代码，轻松地查看正在处理的数据。查询过程就在我面前。
当查询变大时，我可以使用视图。我在视图中仍能查看正在查询的数据，但是视图的复杂性被隐藏了起来。我不是把它隐藏在游标中，而是隐藏在视图中。
有些时候你必须使用显式游标，最常见的是要处理更大的结果集以及在使用FETCH语句时需要使用BULK COLLECT以保证系统性能的情况。当我必须使用显式定义的游标时，我选择定义在过程自身内（不仅在包体中，而且正好在包体的过程里）的局部游标。为什么呢？
与使用全局游标（在说明中定义）相比，使用它的CPU效率更高。
我仍能浏览代码，并能轻松地查看正在处理的数据。
它使我可以使用视图，因为查询是在过程中，而我不希望它影响到代码的其余部分。
游标属于谁一目了然。
你理解这里的模式吗？同样，具有局部作用域（在过程中）的游标使用后会自动清除。没有具有%isopen属性的游标垃圾弄乱我的代码。 （我的代码从没用过isopen"特性"。）我不必担心："你知道过程P1使用了cursor_x，我也使用了cursor_x，而且由于它们是同一个cursor_x，所以我们也许会互相干扰。"所以我没有人们使用全局变量时总出现的问题。我以与查看全局变量相同的不信任级查看包说明或包体中的游标（这些游标不是在过程中定义的，但有全局作用域）；多个过程访问这些全局变量产生副作用的可能性太高了。只是在别无选择时我才使用全局变量。
总而言之，优先选择的顺序为：
1.不用游标（select into, for x in ( select..... ）
2.不管出于什么原因被迫使用游标时都声明局部游标，如：
a. 需要使用LIMIT子句的批量绑定
b. 引用游标（ref cursors）
我建议不要在包说明中声明全局游标，理由是：
这会丧失封装的良好特性。游标可以全局访问，任何能访问该包的人都能看到它。
这多少会降低一些性能（我强调多少，而且这不是主要方面）。
会降低包体的可读性。
一般来讲，使用全局参数是编写代码时应尽量避免的一个不好的习惯。
在Oracle9i数据库中切换UNDO表空间
我这样理解，如果我用ALTER SYSTEM命令将UNDO从一个表空间切换到另一个表空间，Oracle实际上只有在所有使用第一个表空间的活动事务都被提交或回滚后才切换到另一个表空间。我的理解对吗？另外，如果我想知道在第一个UNDO表空间中哪些事务是活动的，我该怎么办？任何视图或查询都会有用。
你的理解不正确。 Oracle会立刻开始使用另一个UNDO表空间。下一个例子很好，它不仅证明了这一点，而且还为你提供所需要的查询，这样你就可以查看谁在使用要启动的UNDO表空间中的哪个回滚段。
我要做的是先开始某一会话中的一个事务，但不用COMMIT命令提交。我通过查询来看一看哪些会话正在使用哪些表空间中哪些回滚段。然后，我发出ALTER SYSTEM命令以切换UNDO表空间，执行会话中的另一个事务，再执行查询看一下谁在使用哪一个UNDO表空间。这时我要查看一下旧UNDO表空间中的旧事务和新UNDO表空间中的新事务。首先，我要看一看谁在使用什么。该查询将V$SESSION（得到会话信息）与V$TRANSACTION（只报告有活动事务的会话）及DBA_ROLLBACK_SEGS（呈交回滚段信息，如名称和表空间）连接起来：
select a.username,
b.segment_name,
b.tablespace_name tspace
from v$session a,
dba_rollback_segs b,
v$transaction c
where a.taddr = c.addr
and b.segment_id = c.xidusn
/
USERNAME SEGMENT_NAMETSPACE
-------- -------------------
OPS$TKYTE _SYSSMU11$UNDO
这说明有一个事务是活动的，它使用名为UNDO的UNDO表空间。现在我要切换UNDO表空间：
alter system
set undo_tablespace = undo2;
现在我在这个会话中开始另一个事务：
update dept set deptno = deptno;
2 rows updated.
select a.username,
b.segment_name,
b.tablespace_name tspace
from v$session a,
dba_rollback_segs b,
v$transaction c
where a.taddr = c.addr
and b.segment_id = c.xidusn
/
USERNAMESEGMENT_NAMETSPACE
-------- -------------------
OPS$TKYTE
_SYSSMU11$UNDO
OPS$TKYTE
_SYSSMU16$UNDO2
这时我看到这两个表空间都在被使用。我还不能撤消UNDO表空间，因为它有活动事务，但它不能用于任何新的事务。
此时你也用到了你想要的查询。
随机性
怎样用一条SQL语句在1到49之间创建6个各不相同的随机数？
如果你有时从过程的角度考虑SQL是"基于集"的，你就能更深入地使用SQL。SQL被认为是一种非过程语言，但有时我发现，如果我考虑某些过程化的需要，它们也能帮助我设计一个查询。
为了解答你这个问题，我需要：
生成一个从1到49的数字集合。我要从这个数字集合中抓取6个随机数。 下一步
向Tom咨询
asktom.oracle.com
Oracle副总裁Tom Kyte为你解答最棘手的技术问题。本专栏从该论坛中精选了部分重要内容。
阅读
Tom的更多文章
asktom.oracle.com/~tkyte
《高效的Oracle 设计（Effective Oracle by Design）》 shop.osborne.com/cgi-bin/oraclepress /0072230657.html
Oracle资料
所提供的PL/SQL包以及类型参考
otn.oracle.com/documentation
讨论Oracle技术
oracle.com/forums
将这49个数随机排序。这有点类似于为这49个数中的每个数分配一个随机数，然后按照它们排序。
取结果集的前6个数。
为了生成由49个数组成的集合，我只需一个至少有49行的表。我发现ALL_OBJECTS是一个非常安全的表，能用于这种场合。它里面始终有至少1000行，而且在各种系统上人人都能访问它。
首先，我需要创建由49个数组成的集合。这条SQL查询很简单：
select rownum r
From all_objects
where rownum < 50
这样便会生成数字1、2、3、……、49。接下来，我需要用这个集合并将它随机排序。我会使用一个内联视图来完成这件事。在下面的语句中，用上面的查询代替QUERY这个词：
select r
from ( QUERY )
order by dbms_random.value
此时，如果你在SQL*Plus中反复运行order by dbms_ random.value查询，你会发现总能得到49行，而且每次执行查询都返回不同的顺序。
现在我只需取前6个数。我要使用另一个内联视图将前面查询的结果限制在前6行。完整的查询是：
select r
from
( select r
from
( select rownum r
from all_objects
where rownum < 50 )
order by dbms_random.value )
where rownum <= 6
/
R
-----
8
20
32
12
44
26
6 rows selected.
如果我再执行一次，将会得到6个不同的数。
Tom Kyte ([email protected]) 从1993年起一直在Oracle工作。Kyte是负责Oracle 管理、教育和保健集团的副总裁，也是"Effective Oracle by Design"（Oracle 出版社出版）和"Expert One-on-One： Oracle"（Apress出版）两书的作者。 |
