declare
num number;
begin
-- prompt dropping sequence
num := 0;
select count(1) into num from user_sequences where sequence_name = 'COMMON_MYTASK_SEQUENCE';
if num > 0 then
execute immediate 'DROP SEQUENCE COMMON_MYTASK_SEQUENCE';
end if;
-- prompt dropping trigger
num := 0;
select count(1) into num from user_triggers where trigger_name = 'COMMON_MYTASK_TG';
if num > 0 then
execute immediate 'DROP TRIGGER COMMON_MYTASK_TG';
end if;
-- prompt Dropping
num := 0;
select count(1) into num from user_tables where TABLE_NAME = 'T_COMMON_MYTASK';
if num=1 then
execute immediate 'drop table T_COMMON_MYTASK';
end if;
end;
/
--我的任务表
CREATE TABLE T_COMMON_MYTASK(
id NUMBER NOT NULL,
process_definition_id varchar2(100) not null,--流程定义id
request_staff_id number not null,--请求人id
approver_id number not null,--审批人id
process_type number not null,--流程类型:入廊;巡检;....
crt_time date,--申请时间
CONSTRAINT PK_T_COMMON_MYTASK PRIMARY KEY ("ID")
);
--create sequence
create sequence COMMON_MYTASK_SEQUENCE
start with 1
increment by 1
nomaxvalue
nocycle
cache 20;
-- create trigger
CREATE OR REPLACE TRIGGER COMMON_MYTASK_TG
BEFORE INSERT ON T_COMMON_MYTASK
FOR EACH ROW
WHEN (new.id is null)
begin
select COMMON_MYTASK_SEQUENCE.nextval into :new.id from dual;
end COMMON_MYTASK_TG;
/