分区表

来源：互联网发布：python逻辑运算符编辑：程序博客网时间：2024/05/21 03:57

-------------------范围------------------create table recordgrade  (  sno varchar2(10),  dormitory varchar2(3),  grade int)partition by range(grade)(  partition bujige values less than(60), --不及格  partition jige values less than(85), --及格  partition youxiu values less than(maxvalue) --优秀);insert into recordgrade values('511601','229',92);insert into recordgrade values('511602','229',62);insert into recordgrade values('511603','229',26);insert into recordgrade values('511604','228',77);insert into recordgrade values('511605','228',47);insert into recordgrade(sno,dormitory) values('511606','228');insert into recordgrade values('511607','240',90);insert into recordgrade values('511608','240',100);insert into recordgrade values('511609','240',67);insert into recordgrade values('511610','240',75);insert into recordgrade values('511611','240',60);commit;

SQL> select * from recordgrade order by grade;                  SNO   DOR    GRADE---------- --- ----------511603   229       26511605   228       47511611   240       60511602   229       62511609   240       67511610   240       75511604   228       77511607   240       90511601   229       92511608   240      100511606   22811 rows selected.select * from recordgrade partition(bujige)order by grade;SNO   DOR    GRADE---------- --- ----------511603   229       26511605   228       47select * from recordgrade partition(jige)order by grade;  SNO   DOR    GRADE---------- --- ----------511611   240       60511602   229       62511609   240       67511610   240       75511604   228       77SQL> select * from recordgrade partition(youxiu)order by grade;SNO   DOR    GRADE---------- --- ----------511607   240       90511601   229       92511608   240      100511606   228SQL> 说明：数据中有空值，Oracle机制会自动将其规划到maxvalue的分区中。

------指定具体值------------create table grade_record  (  sno varchar2(10),  dormitory varchar2(3),  grade int)partition by list(grade)(  partition bujige values (60), --不及格  partition jige values (85), --及格  partition youxiu values (100) --优秀);insert into grade_record values('511609','240',67);insert into grade_record values('511610','240',75);insert into grade_record values('511611','240',60);insert into grade_record(sno,dormitory) values('511606','228');  commit;

insert into grade_record values('511609','240',67);insert into grade_record values('511609','240',67)*ERROR at line 1:ORA-14400: inserted partition key does not map to any partitionSQL> insert into grade_record values('511610','240',<strong>75)</strong>;insert into grade_record values('511610','240',<strong>75)</strong>*ERROR at line 1:ORA-14400:inserted partition key does not map to any partitionSQL> insert into grade_record values('511611','240',60);1 row created.SQL> insert into grade_record(sno,dormitory) values('511606','228');  insert into grade_record(sno,dormitory) values('511606','228')*ERROR at line 1:ORA-14400: inserted partition key does not map to any partitionSQL>

0 0