oracle创建表相关

 1 --创建表 2 create table person( 3 id number primary key, 4 name varchar2(40), 5 birth date 6 ); 7 --创建序列 8 create sequence person_id_seq 9 increment by 110 start with 111 nomaxvalue --不设置最大值12 nocycle  --一直累加，不循环13 cache 10;14 --创建触发器15 create or replace trigger person_id_tri before insert on person16 for each row17 declare18 v_newVal number(12) := 0;19 v_incval NUMBER(12) := 0;20 BEGIN21   IF INSERTING AND :new.id IS NULL THEN22     SELECT  person_id_SEQ.NEXTVAL INTO v_newVal FROM DUAL;23     -- If this is the first time this table have been inserted into (sequence == 1)24     IF v_newVal = 1 THEN 25       --get the max indentity value from the table26       SELECT NVL(max(id),0) INTO v_newVal FROM person;27       v_newVal := v_newVal + 1;28       --set the sequence to that value29       LOOP30            EXIT WHEN v_incval>=v_newVal;31            SELECT person_id_seq.nextval INTO v_incval FROM dual;32       END LOOP;33     END IF;34     --used to emulate LAST_INSERT_ID()35     --mysql_utilities.identity := v_newVal; 36    -- assign the value from the sequence to emulate the identity column37    :new.id := v_newVal;38   END IF;39 END;40 41 --简单触发器，上面触发器有问题,序列被跳过42 create or replace trigger person_id_tri before insert on person43 for each row when(new.id is null)44 BEGIN45 select person_id_seq.nextval into :new.id from dual;46 end;47 48 --插入实例49 insert into person(name, birth) values('ceshi',sysdate);50 --错误的时间格式51 insert into person(name,birth) values('hehe','2015-06-02 00:00:00');52 --正确的插入日期53 insert into person(name,birth) values('hehe',to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss'));54 insert into person(name,birth) values('hehe',to_date('2005-01-01','yyyy-MM-dd'));55 56 --oracle 中日期的格式化57 select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;58 59 --查询表60 select * from person ;61 62 --截断表63 truncate table person;