ITPub博客

首页 > 数字化转型 > ERP > set constraints all deferred|immediate的作用

set constraints all deferred|immediate的作用

原创 ERP 作者:gowin 时间:2006-06-03 11:07:37 0 删除 编辑
约束有三个选项:
DEFERRABLE INITIALLY DEFERRED
DEFERRABLE INITIALLY IMMEDIATE
NOT DEFERRABLE
默认的话是第三种,而第三中是immediate的,并不受set constraint影响[@more@]约束有三个选项:
DEFERRABLE INITIALLY DEFERRED
DEFERRABLE INITIALLY IMMEDIATE
NOT DEFERRABLE
默认的话是第三种,而第三中是immediate的,并不受set constraint影响

例如:
以一般方式添加的约束
SQL> create table t1(a int);

Table created.

SQL> alter table t1 add constraint pk unique(a);

Table altered.

SQL> select deferrable from dba_constraints where owner='GOWIN' and constraint_n
ame='PK';

DEFERRABLE
--------------
NOT DEFERRABLE

SQL> create or replace procedure tes
2 as
3 begin
4 insert into t1 values (1);
5 insert into t1 values (1);
6 end;
7 /

Procedure created.

SQL> set constraints all deferred;

constraint set.

SQL> exec tes
BEGIN tes; END;

*
ERROR at line 1:
ORA-00001: unique constraint (GOWIN.PK) violated
ORA-06512: at "GOWIN.TES", line 5
ORA-06512: at line 1

然后以另两种方式中的一种添加约束的方式
SQL> drop table t1;

Table dropped.

SQL> create table t1(a int);

Table created.

SQL> alter table t1 add constraint pk unique(a) DEFERRABLE initially deferred;

Table altered.

SQL> select deferrable from dba_constraints where owner='GOWIN' and constraint_n
ame='PK';

DEFERRABLE
--------------
DEFERRABLE

SQL> create or replace procedure tes
2 as
3 begin
4 insert into t1 values (1);
5 insert into t1 values (1);
6 end;
7 /

Procedure created.

SQL> exec tes

PL/SQL procedure successfully completed.

SQL> select * from t1;

A
----------
1
1

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (GOWIN.PK) violated

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28429/viewspace-839545/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 一世静思语
请登录后发表评论 登录
全部评论

注册时间:2008-02-02

  • 博文量
    55
  • 访问量
    954696