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 1
10 start with 1
11 nomaxvalue --不设置最大值
12 nocycle --一直累加,不循环
13 cache 10;
14 --创建触发器
15 create or replace trigger person_id_tri before insert on person
16 for each row
17 declare
18 v_newVal number(12) := 0;
19 v_incval NUMBER(12) := 0;
20 BEGIN
21 IF INSERTING AND :new.id IS NULL THEN
22 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 table
26 SELECT NVL(max(id),0) INTO v_newVal FROM person;
27 v_newVal := v_newVal + 1;
28 --set the sequence to that value
29 LOOP
30 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 column
37 :new.id := v_newVal;
38 END IF;
39 END;
40
41 --简单触发器,上面触发器有问题,序列被跳过
42 create or replace trigger person_id_tri before insert on person
43 for each row when(new.id is null)
44 BEGIN
45 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;