oracle建表语句

drop TABLE REQIONALISM;
create table REQIONALISM (
    REQIONALISMID VARCHAR2(64) not null primary key ,
    REQIONALISMNAME VARCHAR2(50),
    BELONGUNIT     VARCHAR2(64),
    PARENTID VARCHAR2(64) REFERENCES REQIONALISM(REQIONALISMID),
    CREATEORGID    VARCHAR2(64),
    CREATEORGCODE VARCHAR2(64),
    CREATETIME     DATE,
    CREATEUSERID VARCHAR2(64)
);

insert into REQIONALISM names(REQIONALISMID,REQIONALISMNAME,BELONGUNIT) values (sys_guid(),'华北地区','cjjg');
insert into REQIONALISM names(REQIONALISMID,REQIONALISMNAME,BELONGUNIT) values (sys_guid(),'东北地区','cjjg');
insert into REQIONALISM names(REQIONALISMID,REQIONALISMNAME,BELONGUNIT) values (sys_guid(),'华东地区','cjjg');
insert into REQIONALISM names(REQIONALISMID,REQIONALISMNAME,BELONGUNIT) values (sys_guid(),'中南地区','cjjg');
insert into REQIONALISM names(REQIONALISMID,REQIONALISMNAME,BELONGUNIT) values (sys_guid(),'西南地区','cjjg');
insert into REQIONALISM names(REQIONALISMID,REQIONALISMNAME,BELONGUNIT) values (sys_guid(),'西北地区','cjjg');
insert into REQIONALISM names(REQIONALISMID,REQIONALISMNAME,BELONGUNIT) values (sys_guid(),'港澳台地区','cjjg');
commit;
drop TABLE BASEREQIONALISM;
create table BASEREQIONALISM(
    BASEREQIONALISMID VARCHAR2(64) primary key  NOT NULL,
    REQIONALISMID VARCHAR2(64) references REQIONALISM(REQIONALISMID),
    ORGID VARCHAR2(64)
);

--华北地区：（1.北京，2.天津，3.河北，4.山西，5.内蒙古）
insert into BASEREQIONALISM values (sys_guid(),'C24FAE77EABC417B85DBF42CDB79F6E8','A9A44290-B99E-11DF-B836-A253CA211C53');
insert into BASEREQIONALISM values (sys_guid(),'C24FAE77EABC417B85DBF42CDB79F6E8','A9AEC9E0-B99E-11DF-B836-882D46D34FF0');
insert into BASEREQIONALISM values (sys_guid(),'C24FAE77EABC417B85DBF42CDB79F6E8','A9B1B010-B99E-11DF-B836-9235686884A6');
insert into BASEREQIONALISM values (sys_guid(),'C24FAE77EABC417B85DBF42CDB79F6E8','A9CD0040-B99E-11DF-B836-E9D5F985B012');
insert into BASEREQIONALISM values (sys_guid(),'C24FAE77EABC417B85DBF42CDB79F6E8','A9E03A20-B99E-11DF-B836-99D5ECA41A58');

--东北地区：（1.辽宁，2.吉林，3.黑龙江，4.大连市）
insert into BASEREQIONALISM values (sys_guid(),'0C988B1E0E4148CAACDDB0DD65CE1876','A9F19F40-B99E-11DF-B836-A327305BE21E');
insert into BASEREQIONALISM values (sys_guid(),'0C988B1E0E4148CAACDDB0DD65CE1876','AA030460-B99E-11DF-B836-E8A584EC34E3');
insert into BASEREQIONALISM values (sys_guid(),'0C988B1E0E4148CAACDDB0DD65CE1876','AA0DB2C0-B99E-11DF-B836-F82B6D781120');
insert into BASEREQIONALISM values (sys_guid(),'0C988B1E0E4148CAACDDB0DD65CE1876','A9F3E930-B99E-11DF-B836-B15CC1DD95FD');

commit;

--华东地区（包括山东、江苏、安徽、浙江、福建、上海、江西）；
insert into BASEREQIONALISM values (sys_guid(),'63A14EBC0F174637B84723FD2DA65A63','AA7AF380-B99E-11DF-B836-B431C40C9EEA');
insert into BASEREQIONALISM values (sys_guid(),'63A14EBC0F174637B84723FD2DA65A63','AA270720-B99E-11DF-B836-C787CF464713');
insert into BASEREQIONALISM values (sys_guid(),'63A14EBC0F174637B84723FD2DA65A63','AA48BFF0-B99E-11DF-B836-AAC09DE429F3');
insert into BASEREQIONALISM values (sys_guid(),'63A14EBC0F174637B84723FD2DA65A63','AA392F90-B99E-11DF-B836-D410FB5F1D2F');
insert into BASEREQIONALISM values (sys_guid(),'63A14EBC0F174637B84723FD2DA65A63','AA5B84A0-B99E-11DF-B836-D9AD65E6740E');
insert into BASEREQIONALISM values (sys_guid(),'63A14EBC0F174637B84723FD2DA65A63','AA5B84A0-B99E-11DF-B836-D9AD65E6740E');
insert into BASEREQIONALISM values (sys_guid(),'63A14EBC0F174637B84723FD2DA65A63','AA6A0390-B99E-11DF-B836-D5FE25A95551');
commit;

--中南地区（包括河南、湖北、湖南、广东、广西、海南、）；
insert into BASEREQIONALISM values (sys_guid(),'77514BFFC5D447E4A767E15E1E735F2B','AA930F60-B99E-11DF-B836-E1CF3504C0A6');
insert into BASEREQIONALISM values (sys_guid(),'77514BFFC5D447E4A767E15E1E735F2B','AAAE1170-B99E-11DF-B836-BB043C20C323');
insert into BASEREQIONALISM values (sys_guid(),'77514BFFC5D447E4A767E15E1E735F2B','AABFEBC0-B99E-11DF-B836-BB604A7F7887');
insert into BASEREQIONALISM values (sys_guid(),'77514BFFC5D447E4A767E15E1E735F2B','AAD4D350-B99E-11DF-B836-8BF691ADCBD9');
insert into BASEREQIONALISM values (sys_guid(),'77514BFFC5D447E4A767E15E1E735F2B','AAEAA540-B99E-11DF-B836-C7C79427A173');
insert into BASEREQIONALISM values (sys_guid(),'77514BFFC5D447E4A767E15E1E735F2B','AAFD9100-B99E-11DF-B836-893A9A1C2017');
commit;

--西南地区（包括四川、云南、贵州、西藏、重庆）；
insert into BASEREQIONALISM values (sys_guid(),'C09882A0169E42A1B60F0142E5BFB43F','AB07CA30-B99E-11DF-B836-E587A72883BA');
insert into BASEREQIONALISM values (sys_guid(),'C09882A0169E42A1B60F0142E5BFB43F','AB3569E0-B99E-11DF-B836-EA6BF4F26FD1');
insert into BASEREQIONALISM values (sys_guid(),'C09882A0169E42A1B60F0142E5BFB43F','AB2675C0-B99E-11DF-B836-8F703EF0348D');
insert into BASEREQIONALISM values (sys_guid(),'C09882A0169E42A1B60F0142E5BFB43F','AB4BB100-B99E-11DF-B836-B1C302A91DE0');
insert into BASEREQIONALISM values (sys_guid(),'C09882A0169E42A1B60F0142E5BFB43F','AB0188A0-B99E-11DF-B836-8F3413022C59');
commit;
--西北地区（包括宁夏、新疆、青海、陕西、甘肃）；
insert into BASEREQIONALISM values (sys_guid(),'F8549BED4D9341E188E6A5509AA2B5A0','AB816700-B99E-11DF-B836-85D2F719D9E7');
insert into BASEREQIONALISM values (sys_guid(),'F8549BED4D9341E188E6A5509AA2B5A0','AB8585B0-B99E-11DF-B836-8DD3E3C7794F');
insert into BASEREQIONALISM values (sys_guid(),'F8549BED4D9341E188E6A5509AA2B5A0','AB7977C0-B99E-11DF-B836-CDF1F9BE7D69');
insert into BASEREQIONALISM values (sys_guid(),'F8549BED4D9341E188E6A5509AA2B5A0','AB580D10-B99E-11DF-B836-CB3269397F7C');
insert into BASEREQIONALISM values (sys_guid(),'F8549BED4D9341E188E6A5509AA2B5A0','AB6A0E70-B99E-11DF-B836-D035997418C3');
commit;

--台港澳地区（包括台湾、香港、澳门）。
insert into BASEREQIONALISM values (sys_guid(),'91E78F5A92DC45089996F4A2FF5EF5C0','');
insert into BASEREQIONALISM values (sys_guid(),'91E78F5A92DC45089996F4A2FF5EF5C0','');
insert into BASEREQIONALISM values (sys_guid(),'91E78F5A92DC45089996F4A2FF5EF5C0','');
commit;

 

create sequence REQIONALISM_sequence
increment by 1
start with 1
nomaxvalue
nocycle
cache 10;

create sequence BASEREQIONALISM_sequence
increment by 1
start with 1
nomaxvalue
nocycle
cache 10;

------------------------------------------------------------------------------
在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。 
1、Create Sequence 
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限， 
CREATE SEQUENCE emp_sequence 
     INCREMENT BY 1   -- 每次加几个 
     START WITH 1     -- 从1开始计数 
     NOMAXVALUE       -- 不设置最大值 
     NOCYCLE          -- 一直累加，不循环 
     CACHE 10; 

一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL 
CURRVAL=返回 sequence的当前值 
NEXTVAL=增加sequence的值，然后返回 sequence 值 
比如： 
   emp_sequence.CURRVAL 
   emp_sequence.NEXTVAL 

可以使用sequence的地方： 
- 不包含子查询、snapshot、VIEW的 SELECT 语句 
- INSERT语句的子查询中 
- NSERT语句的VALUES中 
- UPDATE 的 SET中   

可以看如下例子： 
INSERT INTO emp VALUES  
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20); 

SELECT empseq.currval      FROM DUAL; 

但是要注意的是： 
- 第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白？ 

- 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。 

2、Alter Sequence 
你或者是该sequence的owner，或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须 drop   sequence 再 re-create . 
Alter sequence 的例子 
ALTER SEQUENCE emp_sequence 
     INCREMENT BY 10 
     MAXVALUE 10000 
     CYCLE     -- 到10000后从头开始 
     NOCACHE ; 

影响Sequence的初始化参数： 
SEQUENCE_CACHE_ENTRIES =设置能同时被cache的sequence数目。  

可以很简单的Drop Sequence 
DROP SEQUENCE order_seq;

 

=========================
oracle 建表 主、外键2009-08-30 16:20--主键
create table emp (id number constraint id_pr primary key ,name1 varchar(8));
create table emp9 (id number ,name1 varchar(8) ,constraint aba_pr primary key(id,name1));
--外键
create table emp1(id number references emp(id),name varchar(8));

--符合外键
create table emp0(id number ,name varchar(8) ,constraint fk_nam1e foreign key(id,name) references emp9(id,name1));

--主键另外写法
create table emp2(id number,name varchar(8),id1 number, constraint pk_id primary key(id),constraint fk_name foreign key(id1) references emp(id))
--check 约束的写法
create table emp4(id number check(id in(1,2 ,3)),name varchar(8));
--唯一的写法
create table emp5(id number,name varchar(8) constraint cm_unique unique)
create table emp7(id number,name varchar(8) constraint cm_unique1 unique check (name in('xu','a')))
create table emp6(id number,name varchar(8) constraint cm_unique12 unique,name1 varchar(8) constraint cm2_unique unique)
create table emp8(id number,name varchar(8), name1 varchar(22), constraint cc_unique unique(name,name1))
--添加删除约束
create table xu(id number,name varchar(200),type number);
--alter table xu add xh number; 增加一列
--alter table xu drop column xh 删除一列；
--alter table xu modify xh varchar(200);修改学号数据类型
rename xu to xu1 --修改表的名字

select * from xu1;

comment on table xu1 is 'ss';--对表的描述
comment on column xu1.xh is '许进学号' --对列的描述
create table ctable_name
(

       field1 varchar2(20), --注释的内容1
       field2 number,       --注释的内容2
       field3 char(2),      --注释的内容3
       field4 date          /*注释的内容4*/
)
comment on table ctable_name is '对表注释的内容';/*给表添加注释的方式*/
select * from user_tab_comments where table_name = 'CTABLE_NAME';/*查询某表的注释*/
comment on column ctable_name.field1 is '对field1列注释的内容';/*给列添加注释内容的方式，有多少个列应该写多少个*/