动态SQL

最新推荐文章于 2023-12-23 18:45:37 发布

cj0379

最新推荐文章于 2023-12-23 18:45:37 发布

阅读量60

点赞数

create or replace procedure cseq(sequence_name in varchar2,tname in varchar2,inid in varchar2) is
v_cursor    number;
seq_string varchar2(2000);
tr_string varchar2(2000);
begin
   seq_string := 'CREATE SEQUENCE lkss_'||sequence_name||' START ';
   seq_string := seq_string||'WITH 1 MAXVALUE 999999999999999 INCREMENT BY 1 NOCACHE CYCLE';
   v_cursor := dbms_sql.open_cursor;
   dbms_sql.parse(v_cursor,seq_string,dbms_sql.native);

   tr_string := 'CREATE TRIGGER TR_'||sequence_name||' BEFORE INSERT ON '||tname||' FOR EACH ROW ';
   tr_string := tr_string||'DECLARE NEW_ID NUMBER; BEGIN SELECT LKSS_'||sequence_name||'.NEXTVAL INTO NEW_ID FROM DUAL;';
   tr_string := tr_string||':NEW.'||inid||' := NEW_ID; :new.create_date := sysdate; end;';
   dbms_sql.parse(v_cursor,tr_string,dbms_sql.native);
   dbms_sql.close_cursor(v_cursor);
end;

call cseq('temp_table','temp_table','id');

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/109975/viewspace-604103/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/109975/viewspace-604103/