ORACLE常见操作

--oracle建表
drop table work_order;

CREATE TABLE work_order(
  id number(11) primary key,
  csdm number(30),
  user_id number(11),
  gdlx number(1),
  sf_xf number(1),
  xxqk varchar2(255),
  insert_time date,
  update_time date,
  is_delete number(1)
);

comment on table work_order is '工单表';
comment on column work_order.id is 'id';
comment on column work_order.csdm is 'CSDM';
comment on column work_order.user_id is '用户表id';
comment on column work_order.gdlx is '工单类型：0: \"功能异常\", 1: \"使用建议\"';
comment on column work_order.sf_xf is '是否修复';
comment on column work_order.xxqk is '详细情况说明';
comment on column work_order.insert_time is '提交工单时间';
comment on column work_order.update_time is '更新时间';
comment on column work_order.is_delete is '是否已删除';

--实现主键自增
--第一步：创建序列：
create sequence userID_seq 
  minvalue 1           --最小值限制为１
  maxvalue 9999 　 --最大值限制
  increment by 1      --每次自增１
  start with 1;           --从1开始计数
  
  
--第二步：创建立触发器：
create or replace trigger userID_tri
  before insert on t_zdry_user
  for each row
  begin 
  select userID_seq.Nextval into :new.id from dual;
  end ;

--新增字段和注解 
alter table gxdw add departfullcode VARCHAR2(100);
comment on column gxdw.departfullcode is '部门全码';

--删除字段
alter table gxdw DROP COLUMN departfullcode