数据库中三大问题：约束、CASE语句和文件（转）

本文用实例方式讲述了关于约束、CASE语句和文件三大问题。

约束

“定义为可延迟（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:只要所有受更新影响的子记录都指向现有的父记录，这条命令就能成功执行。

 

此外，你能在各种多语句事务中使用延迟约束，这些事务在处理的过程中需要暂时破坏完整性，但最后他们都会物归原样。

Case语句

怎么计算时间

你是怎么计算asktom.oracle.com第一页中AGE列显示的时间的？我之所以问这个问题是因为我看到他有多种格式，如9个月3小时；2.3年；19个小时；等等。我是Oracle新手，想知道你们在使用哪种日期计算法。

 

非常好用的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语句。

 

网站公开下面这段代码：

 

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语句的功能完全相同,只是不太明显。

 

文件

我正在考虑为一个应用程式设计些选项，利用他用户能上传和存储可供他人下载的文件。文件能是平均大小为150Kb的Microsoft Word文件。最初需要（从CD）移植18000到20000个文件，当使用该应用程式时存储数量会增加到大约25000个文件。浏览器前端是用于上传和下载的PL/SQL插件（PL/SQL cartridge）页面。一开始，会有400到500人几乎同时访问该应用程式，两周内每天将有300人访问（分散访问）。 文件本身在数据库中作为BLOB存储。

 

从使用方面考虑，你觉得这样的选项好吗？他会过多占用系统全局区（SGA）吗？考虑到应用程式的需求及前端（基于浏览器），除了保存为BLOB，更有没有其他选择，如文件系统？

 

我什么都存在数据库里。就是这样。如果数据就是你的一切，无论他们有什么样的值，事实上都要放到数据库中，在那里数据能得到专业化的管理、备份，恢复而且安全。除了这些实实在在的好处，你还能索引及搜索文件。（诚然，用文件系统也能做这些，但在索引和文件之间不存在完整性。）在数据库中，你能转换文件格式（例如，上传一个DOC文件，而显示为HTML格式）。你的数据是完全集成的、安全的、有备份的而且随时供你使用。

 

在Oracle公司内部，我们将一个几千G字节的数据库作为整个公司的一个单一的文件服务器。公司所有文件都存在那里，存在这样一个单一的地方，能对这些文件进行备份、搜索、建立索引和访问。在常规的文件系统中管理成千上万的文件是不可能的。

http://www.sudu.cn/info/html/edu/20080402/257105.html

以上内容由 华夏名网 搜集整理，如转载请注明原文出处，并保留这一部分内容。