15.txt

Oracle database 10g基础教程（第二版） 源码

set transaction read only;
update emp
set ename = lower(ename);


 

select object_id
from all_objects
where rownum <= 2000;



drop table t;
create table t(
x int,
constraint ck_x_greater_than_zero check(x>0)
  deferrable initially immediate
);






insert into t values(-1)
/



set constraint ck_x_greater_than_zero deferred;
insert into t values(-1);



commit








set constraint ck_x_greater_than_zero deferred;
insert into t values(-1);
set constraint ck_x_greater_than_zero immediate;





drop table t;
create table t(
x number(1)
);


insert into t values(1);
insert into t values(2);

rollback;
select * from t;


insert into t values(1);
insert into t values(2);
commit;
select * from t;





create table t(
x int,
constraint pk_t primary key(x)
);
create trigger t_trigger
after update
on t 
for each row
begin
  dbms_output.put_line('update x='||:old.x||' to x='||:new.x);
end;
/


insert into t values(1);
insert into t values(2);



set serveroutput on
begin
  update t
  set x = 2;
end;
/



set serveroutput on
begin
  update t
  set x = x + 1;
end;
/



create table parent(
pk int,
constraint pk_parent primary key(pk)
);
create table child(
fk int,
constraint fk_child foreign key(fk) references parent deferrable
);



insert into parent values(1);
insert into child values(1);
commit;



update parent
set pk = 2;
update child
set fk = 2;



set constraint fk_child deferred
/


update parent
set pk = 2;
select * from parent;
select * from child;


commit;


set constraint fk_child deferred;
update parent
set pk = 2;
select * from parent;
select * from child;


set constraint fk_child immediate;


update child
set fk = 2;
set constraint fk_child immediate;
commit;




select * from parent;
select * from child;


create table a
as
select 1 x
from dual;
create table b
as
select 1 x
from dual;










update a
set x=x+1;

update b
set x=x+1;