关于约束、CASE语句和游标

关于约束、CASE语句和游标

咱们的本领大师谈完备性、功夫和归属题目。

我提防观赏了《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,  3    y 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 backora-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 backora-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/24then round(24*60*(sysdate-timestamp))     || ' minutes old 'when sysdate-timestamp < 1then round(24*(sysdate-timestamp))      || ' hours old 'when sysdate-timestamp < 14then trunc(sysdate-timestamp)     || ' days old 'when sysdate-timestamp < 60then trunc((sysdate-timestamp)/7)     || ' weeks old 'when sysdate-timestamp < 365then 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_name    tspace--------  ------------    -------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/

username  segment_name    tspace--------  ------------    -------ops$tkyte _syssmu11$      undoops$tkyte _syssmu16$      undo2

这时候我看到这两个表空间都在被运用。我还不许撤消undo表空间，由于它有震动工作，但它不许用来任何新的工作。

此时你也用到了你想要的查问。

随机性

还好吗用一条sql语句在1到49之间创造6个各不沟通的随机数？

即使你偶尔从进程的观点商量sql是"鉴于集"的，你就能更深刻地运用sql。sql被觉得是一种非进程谈话，但偶尔我创造，即使我商量某些进程化的须要，它们也能扶助我安排一个查问。

为领会答你这个题目，我须要：

天生一个从1到49的数字汇合。我要从这个数字汇合中抓取6个随机数。 　

将这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 (thomas.kyte@oracle.com) 从1993年起从来在oracle处事。kyte是控制oracle 处置、培养和保健团体的副总裁，也是"effective oracle by design"（oracle 出书社出书）和"expert one-on-one： oracle"（apress出书）两书的作家。