曾经写过的存储过程，留以后做参考

  CREATE OR REPLACE TRIGGER "AERIALCM"."AAE_ATTR_DATA_TYPE_BI" BEFORE INSERT on AERIALCM.AAE_ATTR_DATA_TYPE  for each row DECLARE BEGIN :new.CREATED_TIMESTAMP := current_timestamp; END;

CREATE USER ALINEO_DB_USR
   IDENTIFIED BY ALINEO_DB_USR
   DEFAULT TABLESPACE ALINEO_TBLSPACE
   TEMPORARY TABLESPACE ALINEO_TEMP_TBLSPACE;

declare
  sequence_owner VARCHAR2(1000);

  sequence_name VARCHAR2(1000);

  new_sequence_name VARCHAR2(1000);

  sql_string VARCHAR2(1000);

  current_seq_value NUMBER(10) DEFAULT 1;

  cursor cur is
    select SEQUENCE_OWNER, SEQUENCE_NAME, LAST_NUMBER
      from ALL_SEQUENCES
     where SEQUENCE_NAME in (select SEQUENCE_NAME
                               from all_SEQUENCEs
                              group by SEQUENCE_NAME
                             having count(*) > 1)
     order by SEQUENCE_NAME;

BEGIN
  open cur;
  fetch cur
    into sequence_owner, sequence_name, current_seq_value;
  while cur%found loop
    dbms_output.put_line(sequence_owner);
    dbms_output.put_line(sequence_name);
 
    case sequence_owner
      when 'APPEAL' then
        select SUBSTR(REPLACE(sequence_name, 'SQ', concat('SQ_', 'APL')),
                      0,
                      30)
          into new_sequence_name
          from dual;
      when 'MEMBER' then
        select SUBSTR(REPLACE(sequence_name, 'SQ', concat('SQ_', 'MBR')),
                      0,
                      30)
          into new_sequence_name
          from dual;
      when 'PROVIDER' then
        if ('SQ_PROV_NETWORK' = sequence_name or
           'SQ_CUSTOM_FIELD' = sequence_name or
           'SQ_CUSTOM_FIELD_SEL' = sequence_name) then
          fetch cur
            into sequence_owner, sequence_name, current_seq_value;
          continue;
        else
          select SUBSTR(REPLACE(sequence_name, 'SQ', concat('SQ_', 'PROV')),
                        0,
                        30)
            into new_sequence_name
            from dual;
        end if;
      when 'CLNTLTTR' then
        select SUBSTR(REPLACE(sequence_name, 'SQ', concat('SQ_', 'CL')),
                      0,
                      30)
          into new_sequence_name
          from dual;
      when 'CORREMGMT' then
        select SUBSTR(REPLACE(sequence_name, 'SQ', concat('SQ_', 'CM')),
                      0,
                      30)
          into new_sequence_name
          from dual;
      when 'CODES' then
        fetch cur
          into sequence_owner, sequence_name, current_seq_value;
        continue;
      else
        select SUBSTR(REPLACE(sequence_name,
                              'SQ',
                              concat('SQ_', SUBSTR(sequence_owner, 0, 3))),
                      0,
                      30)
          into new_sequence_name
          from dual;
    END CASE;
    sql_string := 'CREATE SEQUENCE ' || sequence_owner || '.' ||
                  new_sequence_name ||
                  ' MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH ' ||
                  current_seq_value || ' CACHE 20 NOORDER NOCYCLE';
 
    dbms_output.put_line(sql_string);
 
    EXECUTE IMMEDIATE sql_string;
 
    sql_string := 'GRANT SELECT ON ' || sequence_owner || '.' ||
                  new_sequence_name || ' TO ALINEOAPP';
 
    EXECUTE IMMEDIATE sql_string;
 
    sql_string := 'DROP SEQUENCE ' || sequence_owner || '.' ||
                  sequence_name;
 
    EXECUTE IMMEDIATE sql_string;
 
    fetch cur
      into sequence_owner, sequence_name, current_seq_value;
  end loop;
  close cur;
END;

DECLARE
OWNER VARCHAR2(100);
TRIGGERNAME VARCHAR2(100);
STRSQL VARCHAR2(1000);
CURSOR CUR IS SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE OWNER='AERIALCM';
BEGIN
OPEN CUR;
FETCH CUR INTO TRIGGERNAME;
WHILE CUR%FOUND LOOP
   STRSQL := 'DROP TRIGGER AERIALCM.'||TRIGGERNAME;
   EXECUTE IMMEDIATE STRSQL;
   FETCH CUR INTO TRIGGERNAME;
   END LOOP;

   END;

CREATE OR REPLACE TRIGGER LEAVELINE_T
BEFORE INSERT OR UPDATE ON LEAVELINE
FOR EACH ROW
DECLARE NEXTVAL INTEGER;
BEGIN
SELECT MAXSEQ.NEXTVAL INTO NEXTVAL FROM DUAL;
:NEW.ROWSTAMP := NEXTVAL;
END;