Bo's Oracle Station

查看: 2368|回复: 0

第25次活动:2017-10-18(星期三晚上7:00-9:30)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-10-18 19:34:37 | 显示全部楼层 |阅读模式
时间间隔函数和表达式:
  1. select  table_name from user_tables order by 1;

  2. create table t05110_h ( a  interval year(3) to month );

  3. insert into t05110_h values ( interval '999-5' year(3) to month ) ;

  4. insert into t05110_h values ( numtoyminterval ( 999,  'year'  )  +    numtoyminterval ( 5,  'month'  )    );

  5. select  sysdate + a from t05110_h;

  6. create table t05110_i ( a  interval day(3) to second(3) );

  7. insert into t05110_i  values ( interval '100 01:01:01.001' day(3) to second(3) );

  8. insert into t05110_i  values ( numtodsinterval(100, 'day') +
  9.                                                numtodsinterval(1 ,'hour') +
  10.                                                numtodsinterval(1,'minute') +
  11.                                                numtodsinterval(1.001,'second') );


  12. select  a from t05110_i;

复制代码
非空约束:
  1. create table t05110_j ( a  number   constraint nn_t05110_j  not null );

  2. select *
  3. from user_constraints c
  4. where c.OWNER='HR' and c.TABLE_NAME like 'T05110_J%';

  5. select  tc.TABLE_NAME, tc.NULLABLE
  6. from user_tab_columns tc
  7. where tc.TABLE_NAME like 'T05110_J%';

  8. create table t05110_j2  as select * from t05110_j;

  9. create table t05110_k ( a  number ,  
  10.                                     constraint nn_t05110_k   check (a is  not null ) );
  11.   
  12. create table t05110_k2 as select * from t05110_k;

  13. select *
  14. from user_constraints c
  15. where c.OWNER='HR' and c.TABLE_NAME like 'T05110_K%';

  16. select  tc.TABLE_NAME, tc.NULLABLE
  17. from user_tab_columns tc
  18. where tc.TABLE_NAME like 'T05110_K%';

  19. select  * from user_indexes i
  20.   where i.TABLE_OWNER='HR' and i.TABLE_NAME >= 'T05110_J';

  21. alter table t05110_k2 add constraint nn_t05110_k2 check (a is not null ) novalidate  ;
复制代码
Screenshot.png




  1. create table t05110_l (a  number );

  2. select  * from user_indexes i
  3. where i.TABLE_OWNER='HR' and i.TABLE_NAME='T05110_L';

  4. alter table t05110_l add constraint uk_t05110_l unique (a)  ;

  5. select  * from user_indexes i
  6. where i.TABLE_OWNER='HR' and i.TABLE_NAME='T05110_L';

  7. create table t05110_m (a  number );

  8. create index i05110_m on t05110_m (a) ;

  9. select  * from user_indexes i
  10. where i.TABLE_OWNER='HR' and i.TABLE_NAME='T05110_M';

  11. alter table t05110_M add constraint uk_t05110_M unique (a)  ;
  12. ----
  13. select  * from user_constraints c
  14.   where c.TABLE_NAME in ('T05110_L','T05110_M');

  15. alter table t05110_l drop constraint uk_t05110_l ;
  16. alter table t05110_m drop constraint uk_t05110_m;
  17. -----
  18.   select  * from user_indexes i
  19. where i.TABLE_OWNER='HR' and i.TABLE_NAME  in (    'T05110_L','T05110_M');
  20. -----
  21. create  unique  index i05110_l on t05110_l ( a ) ;

  22. ----

  23. alter table t05110_m add constraint uk_t05110_m  
  24.    unique (  a )  deferrable initially deferred ;
  25.    
  26. ---
  27. select  * from user_constraints c
  28.   where c.TABLE_NAME in ('T05110_L','T05110_M');
  29.   ---
  30.    alter table t05110_l add constraint uk_t05110_l  
  31.    unique (  a )  deferrable initially deferred ;
  32.   ---
  33.   alter table t05110_l add constraint uk_t05110_l  
  34.    unique (  a )  not deferrable initially immediate ;
  35.    
  36.    -------------------
  37.    
  38.    alter table t05110_m   modify constraint  uk_t05110_m  initially  immediate;
  39.    
  40.     alter table t05110_m   modify constraint  uk_t05110_m  initially  deferred;
  41.     ---
  42.     create table t05110_n ( a  number     constraint pk_t05110_n     primary key );
  43.    
  44.     ---
  45.    
  46.     create table t05110_o ( aa number constraint fk_t05110_o referencing t05110_n );
  47.    
  48.    
  49.    select  * from user_constraints c
  50.     where c.TABLE_NAME  in ('T05110_N' , 'T05110_O');
  51.    
  52.     select  * from user_cons_columns  cc
  53.      where cc.TABLE_NAME   in ('T05110_N' , 'T05110_O');
  54.    ----
  55.    
  56.    create table t05110_p ( a  number  ,  aa number ,
  57.                                         constraint pk_t05110_p primary key (a) ,
  58.                                         constraint fk_aa_t05110_p foreign key (aa)   
  59.                                         references  t05110_p) ;
  60.                                        
  61.                                        
  62.                                        
  63.     insert into     t05110_p values     ( 1 ,2 ) ;
  64.       
  65.        create table t05110_q ( a  number  ,  aa number ,
  66.                                         constraint pk_t05110_q primary key (a) ,
  67.                                         constraint fk_aa_t05110_q foreign key (aa)   
  68.                                         references  t05110_q   deferrable initially deferred      ) ;
  69.                                        
  70.    
  71.           insert into     t05110_q values     ( 1 ,2 ) ;
  72.          
  73.           ----
  74.          
  75.           select  * from user_constraints c
  76.     where c.TABLE_NAME  in ('T05110_N' , 'T05110_O');
  77.     ---
  78.     alter table t05110_o  drop constraint fk_t05110_o ;
  79.    
  80.     alter table t05110_o add constraint fk_t05110_o   foreign key (aa)    referencing t05110_n
  81.        on delete cascade;
  82.          
  83.       
  84.          alter table t05110_o add constraint fk_t05110_o   foreign key (aa)    referencing t05110_n
  85.        on delete set null;
  86.          


  87. ---------------

  88. create table t05110_r ( a  number ,
  89.     constraint chk_t05110_r  check ( a > 10 ) ) ;
  90.    
  91.    
  92. create or replace trigger  trig05110_r
  93. after update on t05110_r
  94. referencing new as new   old as old
  95. for each  row
  96. begin
  97.   if  :new.a  <= :old.a
  98.    then
  99.       raise_application_error(-20888,'My GOD');
  100.    end if;
  101. end;

  102. select  * from user_errors ;

  103. select  * from user_source s where s.name='TRIG05110_R';

  104. select  * from user_triggers  t  where  t.TRIGGER_NAME='TRIG05110_R';

  105. ----
  106. ls -l utlexpt1.sql

  107. ---
  108. create table t05110_s ( a  number ) ;

  109. insert into t05110_s values ( 1 )  ;

  110. commit;

  111. alter table t05110_s add constraint chk_t05110_s  check ( a  > 10 )
  112.   novalidate  ;

  113. select  * from exceptions;

  114. alter table t05110_s modify constraint chk_t05110_s  
  115.   validate exceptions into exceptions;
  116.   
  117.   select  * from exceptions;
  118.   
  119.   alter table t05110_s modify constraint chk_t05110_s  disable novalidate;
  120.   
  121.   delete from  t05110_s;
  122.   
  123.   alter table t05110_s modify constraint chk_t05110_s disable validate ;
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 21:06 , Processed in 0.037200 second(s), 27 queries .

快速回复 返回顶部 返回列表