Oracle trigger中 動态建立表 建立序列 建立觸發器

trigger中 動态建立表 建立序列 建立觸發器

1:grant create table to wprss;

2:grant create sequence to wprss;

3:grant create trigger to wprss;

4:

create or replace trigger trigger_aa

before INSERT ON aa

for each row

declare

count_num number;

param_num number;

cnt number;

tablename varchar2(50);

sequencesname varchar2(50);

triggername varchar2(50);

sql_stmt VARCHAR2(200);

sql_stmt2 VARCHAR2(200);

sql_Sequencestmt VARCHAR2(200);

sql_triggerstmt VARCHAR2(200);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

tablename:=NLS_UPPER('aa_bk'||to_char(sysdate,'yyyymm'));

sequencesname:=NLS_UPPER('se_aa_bk'||to_char(sysdate,'yyyymm'));

triggername:=NLS_UPPER('tri_aa_bk'||to_char(sysdate,'yyyymm'));

select count(*) into cnt from user_tables where table_name=tablename;

--RAISE_APPLICATION_ERROR(-20001, tablename||cnt);

select count(*) into count_num from aa;

select PARAMNUMNAME into param_num from t_param_num where PARAMNUMID=1;

if cnt>0 then

If count_num>param_num

THEN

sql_stmt2:='insert into '|| tablename|| ' (sex) select sex from (select sex from aa order by AA_ID asc) aa where rownum<='|| param_num;

execute immediate sql_stmt2;

delete from aa where rownum<=param_num;

commit;

END IF;

else

--RAISE_APPLICATION_ERROR(-20001, cnt);

sql_stmt:='create table ' || tablename || '(AA_ID NUMBER not null,SEX VARCHAR2(10),constraint PK_AA_backup primary key (AA_ID))';

--RAISE_APPLICATION_ERROR(-20001, sql_stmt);

execute immediate sql_stmt;

sql_Sequencestmt:='create sequence '||sequencesname||' minvalue 1 maxvalue 1000000 start with 322 increment by 1 cache 20';

execute immediate sql_Sequencestmt;

sql_triggerstmt:='CREATE OR REPLACE TRIGGER '|| triggername ||' BEFORE INSERT ON '|| tablename ||' FOR EACH ROW '

||' BEGIN '||' SELECT '||sequencesname||'.nextval '||' INTO :NEW.aa_id '||' FROM dual; '||' END; ';

execute immediate sql_triggerstmt;

If count_num>param_num

THEN

sql_stmt2:='insert into '|| tablename|| ' (sex) select sex from (select sex from aa order by AA_ID asc) aa where rownum<='|| param_num;

execute immediate sql_stmt2;

delete from aa where rownum<=param_num;

commit;

END IF;

end if;

--exception

--when others then

--rollback;

END trigger_aa;