oracle 应用开发总结（基础篇上)

今晚任务蛮重，要把理清oracle应用开发的基本知识，现把总体知识结构归纳如下：
(1) 表       
        --|表的创建
               --|  create table  zmb(id int ,
                           name varchar2(20),
                           sex char(2) );
               --|  create table  zmb as select * from hxq (可加where条件);
         --|表的更改
           --| 增加列
                    alter table zmb
                          add(age  int)
                --|更改列
                    alter table zmb
                          modify(id number(10))
                --|删除列
                    alter table zmb
                          drop column age;
                --|给列注释
                    comment on column
                            zmb.id  is '学号' ;    
                --|给表注释
                    comment on table
                            zmb is '这是zmb表'；
            
                --|重新命名表
                      alter table zmb rename to hxq;
                --|删除表
                      drop table zmb  cascade constraints ;(有约束情况下)
         --|分区表                                                 
                      --| 范围分区                
                            create table 考生(
                             考号 varchar2(5),
                            姓名 varchar2(30),
                            成绩  number(3,1)
                              )
                            partition by range(成绩)
                            (partition A values less then (300)  tablespace users,
                            partition B values less then(500)  tablespace users,
                            partition C values less then(maxvalues)
                            );  
                            -----------------------------------------------------------------------
                          Insert into 考生 (考号，姓名，成绩)  
                          select  '001','张三',280  from dual
                          union all
                          select  '002','李四',730  from dual
                          union all
                          select  '003','王五,550  from dual
                          union all
                          select  '004','赵六',490  from dual
                          union all
                          select  '005','钱八',670  from dual
                       -------------------------------------------------------------------------
                    SQL>  select * from 考生 partition(A)
                             考号    姓名      成绩
                              001     张三       280    
                                  
                      --|哈斯分区
                      --|混合分区
          --|约束
                       --|非空约束
                      alter table zmb
                             modify  name not null;
                       --|主键约束
                        alter table zmb
                              add constraint pk_zmb primary key (id);
                       --|外键约束
                          alter table zmb
                              add constraint  fk_zmb_xxd   (假设有另外一张表xxd,其主键为sno)
                              foreign key(sno)
                                references zmb(id)                                
                       --|唯一约束
                           alter table zmb
                                 add constraint  un_name unique(name);  
                       --|默认约束
                            alter table zmb
                                modify age int
                                default(20);
                       --|检查约束
                          alter table zmb
                                 add constraint ck_sex
                                    check(sex in ('男','女'))；
                       --|禁止约束
                          alter table zmb
                                 disable constraint ck_sex
                            =>某些情况下，可以禁止约束，这样可以提高插入或者更新数据的速度，不用经过检查。                      
                       --|激活约束
                             alter table zmb
                                enable constraint ck_sex;
                       --|删除约束
                              alter table zmb
                                  drop constraint ck_sex;
        --|表的锁定
                  --|隐式锁
                  --|显示锁
                  --|锁定行
                  --|锁定表
(2) 序列    
        --|创建序列
create sequence  aaa
                incement by 1
                  start with 1
                  maxvalue 99999
                  nocycle
                  nocache ;
        --|使用序列
--|currval的使用
                  select aaa.currval from dual      ==>每次返回的都是1
                  --|nextval的使用
                    select aaa.nextval from dual    ==>第一次结果为 1，第二次为 2 ，......
        --|更改序列
alter sequence aaa
               increment by 2
              maxvlue 10000
              cycle
              cache 20;
        --|删除序列
drop sequence aaa;