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_aabefore INSERT ON aafor each rowdeclarecount_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; BEGINtablename:=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 thenIf count_num>param_numTHENsql_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_numTHENsql_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;