oracle创建表规范参考

总体步骤:
1.建表
2.cxbx授权给指定账户
3.指定用户创建同义词和对角色授权
4.创建行级触发器
5.记录触发器

--cxbx建表01
create table cxbx.test_plan_info
(
    id_test_plan_info varchar2(32),          -- 主键   id+表名
    plan_code         varchar2(20),          --普通字段
    user_create       varchar2(100) not null,-- 4个审计字段必填
    date_create       date          not null,
    user_update       varchar2(100) not null,
    date_update       date          not null
);

--添加注释
comment on table cxbx.test_plan_info is '表名称';
comment on column cxbx.test_plan_info.id_test_plan_info is '主键';
comment on column cxbx.test_plan_info.plan_code is '险种代码';
comment on column cxbx.test_plan_info.user_create is '创建人';
comment on column cxbx.test_plan_info.date_create is '创建时间';
comment on column cxbx.test_plan_info.user_update is '修改人';
comment on column cxbx.test_plan_info.date_update is '修改时间';

-- 主键唯一索引(必须)  索引名:idx+字段名,注意长度限制30
-- 为了避免大数据量下DML锁住,所有建立索引必须加online
create unique index cxbx.idx_id_test_plan_info on cxbx.test_plan_info (id_test_plan_info) online;
-- 设置主键关联主键索引  主键名:PK+字段名   外键名:FK+字段名
alter table cxbx.test_plan_info
    add constraint pk_id_test_plan_info primary key (id_test_plan_info);
--外键同理

-- 其他字段索引  结合实际情况自行决定是否添加
create index cxbx.idx_plan_code on cxbx.test_plan_info (plan_code) online;


--cxbx授权给指定账户创建同义词02
--这里的synTest账户主要负责创建同义词和授权
grant all on cxbx.test_plan_info to synTest with grant option;

--synTest创建公共同义词03
create public synonym test_plan_info for cxbx.test_plan_info;
--synTest 授权角色组03
grant select, insert, delete, update on cxbx.test_plan_info to 角色组;


--traggTest触发器04,每张表都需要建立触发器
--触发器管理表
-- create table traggTest.tragger_manager(
create table tragger_manager
(
    tragger_name   varchar2(30),
    status         varchar2(2),
    table_name     varchar2(30),
    update_project varchar2(100),
    update_version varchar2(60),
    user_create    varchar2(100) not null,
    date_create    date          not null,
    user_update    varchar2(100) not null,
    date_update    date          not null
);

comment on table traggTest.tragger_manager is '触发器管理';
comment on column traggTest.tragger_manager.tragger_name is '触发器名称';
comment on column traggTest.tragger_manager.status is '触发器状态0有效,1无效';
comment on column traggTest.tragger_manager.table_name is '表名';
comment on column traggTest.tragger_manager.update_project is '项目名称';
comment on column traggTest.tragger_manager.update_version is '项目版本或者DB版本';
comment on column traggTest.tragger_manager.user_create is '创建人';
comment on column traggTest.tragger_manager.date_create is '创建时间';
comment on column traggTest.tragger_manager.user_update is '修改人';
comment on column traggTest.tragger_manager.date_update is '修改时间';

create unique index traggTest.idx_tragger_name on traggTest.tragger_manager (tragger_name) online;
alter table traggTest.tragger_manager
    add constraint pk_tragger_name primary key (tragger_name);



--traggTest触发器04  建完之后记得测试
create or replace trigger traggTest.tr_test_plan_info
    before update or insert
    on cxbx.test_plan_info
    for each row
declare
    cursor c_get_status is
        select aa.status
        from tragger_manager aa
        where aa.table_name = 'test_plan_info';
    v_status varchar2(2);
    --当前操作用户
    v_user   varchar2(20);
begin
    v_user := '111';-- 这里是需要获取当前的操作用户,后面补充实现方法
    open c_get_status;
    fetch c_get_status into v_status;
    close c_get_status;
    --有效
    if v_status = '0' then
        :new.date_update = sysdate;
        if :new.user_update is null then
            :new.user_update := v_user;
        end if;
        -- insert
        if inserting then
            :new.date_create = sysdate;
            if :new.user_create is null then
                :new.user_create := v_user;
            end if;
        end if;
    end if;
end;
/

-- traggTest添加触发器信息05
insert into traggTest.tragger_manager(tragger_name, status, table_name, update_project, update_version, user_create, date_create,
                            user_update, date_update)
values ('tr_test_plan_info', '0', 'test_plan_info', 'weixin', '123456', 'yushaoyang', sysdate, 'yushaoyang', sysdate);