|
时间间隔函数和表达式:
- select table_name from user_tables order by 1;
- create table t05110_h ( a interval year(3) to month );
- insert into t05110_h values ( interval '999-5' year(3) to month ) ;
- insert into t05110_h values ( numtoyminterval ( 999, 'year' ) + numtoyminterval ( 5, 'month' ) );
- select sysdate + a from t05110_h;
- create table t05110_i ( a interval day(3) to second(3) );
- insert into t05110_i values ( interval '100 01:01:01.001' day(3) to second(3) );
- insert into t05110_i values ( numtodsinterval(100, 'day') +
- numtodsinterval(1 ,'hour') +
- numtodsinterval(1,'minute') +
- numtodsinterval(1.001,'second') );
- select a from t05110_i;
复制代码 非空约束:
- create table t05110_j ( a number constraint nn_t05110_j not null );
- select *
- from user_constraints c
- where c.OWNER='HR' and c.TABLE_NAME like 'T05110_J%';
- select tc.TABLE_NAME, tc.NULLABLE
- from user_tab_columns tc
- where tc.TABLE_NAME like 'T05110_J%';
-
- create table t05110_j2 as select * from t05110_j;
-
- create table t05110_k ( a number ,
- constraint nn_t05110_k check (a is not null ) );
-
- create table t05110_k2 as select * from t05110_k;
-
- select *
- from user_constraints c
- where c.OWNER='HR' and c.TABLE_NAME like 'T05110_K%';
- select tc.TABLE_NAME, tc.NULLABLE
- from user_tab_columns tc
- where tc.TABLE_NAME like 'T05110_K%';
-
- select * from user_indexes i
- where i.TABLE_OWNER='HR' and i.TABLE_NAME >= 'T05110_J';
-
- alter table t05110_k2 add constraint nn_t05110_k2 check (a is not null ) novalidate ;
复制代码
- create table t05110_l (a number );
- select * from user_indexes i
- where i.TABLE_OWNER='HR' and i.TABLE_NAME='T05110_L';
- alter table t05110_l add constraint uk_t05110_l unique (a) ;
- select * from user_indexes i
- where i.TABLE_OWNER='HR' and i.TABLE_NAME='T05110_L';
-
- create table t05110_m (a number );
-
- create index i05110_m on t05110_m (a) ;
-
- select * from user_indexes i
- where i.TABLE_OWNER='HR' and i.TABLE_NAME='T05110_M';
-
- alter table t05110_M add constraint uk_t05110_M unique (a) ;
- ----
- select * from user_constraints c
- where c.TABLE_NAME in ('T05110_L','T05110_M');
-
- alter table t05110_l drop constraint uk_t05110_l ;
- alter table t05110_m drop constraint uk_t05110_m;
- -----
- select * from user_indexes i
- where i.TABLE_OWNER='HR' and i.TABLE_NAME in ( 'T05110_L','T05110_M');
- -----
- create unique index i05110_l on t05110_l ( a ) ;
-
- ----
-
- alter table t05110_m add constraint uk_t05110_m
- unique ( a ) deferrable initially deferred ;
-
- ---
- select * from user_constraints c
- where c.TABLE_NAME in ('T05110_L','T05110_M');
- ---
- alter table t05110_l add constraint uk_t05110_l
- unique ( a ) deferrable initially deferred ;
- ---
- alter table t05110_l add constraint uk_t05110_l
- unique ( a ) not deferrable initially immediate ;
-
- -------------------
-
- alter table t05110_m modify constraint uk_t05110_m initially immediate;
-
- alter table t05110_m modify constraint uk_t05110_m initially deferred;
- ---
- create table t05110_n ( a number constraint pk_t05110_n primary key );
-
- ---
-
- create table t05110_o ( aa number constraint fk_t05110_o referencing t05110_n );
-
-
- select * from user_constraints c
- where c.TABLE_NAME in ('T05110_N' , 'T05110_O');
-
- select * from user_cons_columns cc
- where cc.TABLE_NAME in ('T05110_N' , 'T05110_O');
- ----
-
- create table t05110_p ( a number , aa number ,
- constraint pk_t05110_p primary key (a) ,
- constraint fk_aa_t05110_p foreign key (aa)
- references t05110_p) ;
-
-
-
- insert into t05110_p values ( 1 ,2 ) ;
-
- create table t05110_q ( a number , aa number ,
- constraint pk_t05110_q primary key (a) ,
- constraint fk_aa_t05110_q foreign key (aa)
- references t05110_q deferrable initially deferred ) ;
-
-
- insert into t05110_q values ( 1 ,2 ) ;
-
- ----
-
- select * from user_constraints c
- where c.TABLE_NAME in ('T05110_N' , 'T05110_O');
- ---
- alter table t05110_o drop constraint fk_t05110_o ;
-
- alter table t05110_o add constraint fk_t05110_o foreign key (aa) referencing t05110_n
- on delete cascade;
-
-
- alter table t05110_o add constraint fk_t05110_o foreign key (aa) referencing t05110_n
- on delete set null;
-
- ---------------
- create table t05110_r ( a number ,
- constraint chk_t05110_r check ( a > 10 ) ) ;
-
-
- create or replace trigger trig05110_r
- after update on t05110_r
- referencing new as new old as old
- for each row
- begin
- if :new.a <= :old.a
- then
- raise_application_error(-20888,'My GOD');
- end if;
- end;
- select * from user_errors ;
- select * from user_source s where s.name='TRIG05110_R';
- select * from user_triggers t where t.TRIGGER_NAME='TRIG05110_R';
- ----
- ls -l utlexpt1.sql
- ---
- create table t05110_s ( a number ) ;
- insert into t05110_s values ( 1 ) ;
- commit;
- alter table t05110_s add constraint chk_t05110_s check ( a > 10 )
- novalidate ;
- select * from exceptions;
- alter table t05110_s modify constraint chk_t05110_s
- validate exceptions into exceptions;
-
- select * from exceptions;
-
- alter table t05110_s modify constraint chk_t05110_s disable novalidate;
-
- delete from t05110_s;
-
- alter table t05110_s modify constraint chk_t05110_s disable validate ;
复制代码
|
|