[Oracle] -- Oracle创建表、删除表、新增序列、主键自增

1、创建过程

create or replace procedure proc_dropifexists(
  p_table in varchar2
) is
v_count number(10);
begin
  select count(*)
  into v_count
  from user_tables
  where upper(table_name)=upper(p_table);
  
  if v_count > 0 then
    execute immediate 'drop table' || p_table || ' purge';
  end if;
end proc_dropifexists;
           

2、使用示例

call proc_dropifexists('tb_user');

create table tb_user(
  ID varchar2(36) default sys_guid() primary key,
  USER_NAME varchar2(20) DEFAULT NULL,
  PASSWORD varchar2(20) DEFAULT NULL
);
COMMENT ON COLUMN tb_user.ID IS '主键';
COMMENT ON COLUMN tb_user.USER_NAME IS '用户名';
           

3、创建序列

drop sequence tb_user_id;
create sequence tb_user_id minvalue 1 nomaxvalue increment by 1 start with 1 nocache;
           

4、创建触发器，实现主键自增

create or replace trigger tb_user_trigger
before insert on tb_user for each row
begin
  select tb_user_id.nextval into:new.id from dual;
end;