oracle创建表相关

迈不过友情╰ 2022-07-16 07:20 196阅读 0赞

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;