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;