/***
[email=*@PARAM]*@PARAM[/email] STNAME 不要创建序列的表，多个表则以“,”隔开
**/
CREATE OR REPLACE PROCEDURE PROC_CREATE_SEQ_TRIG(STNAME IN VARCHAR2)
AS
 STRSQL VARCHAR2(4000);
 TABLENAME VARCHAR2(50);
 PID VARCHAR2(50);
 PIDTYPE VARCHAR2(50);
 MAX_ID NUMBER(20);
 CNT NUMBER(20);
 CURSOR CUR IS 
   SELECT TABLE_NAME FROM USER_TABLES;
BEGIN
   --创建HIBERNATE 专用序列
   SELECT COUNT(*) INTO CNT FROM USER_SEQUENCES T WHERE T.SEQUENCE_NAME =  'HIBERNATE_SEQUENCE';
   IF (CNT = 0) THEN
      STRSQL := 'CREATE SEQUENCE HIBERNATE_SEQUENCE INCREMENT BY 1 START WITH 1 ORDER MAXVALUE 999999999999999 CYCLE CACHE 10';
      EXECUTE IMMEDIATE STRSQL;
   END IF;
   OPEN CUR;
        LOOP        
          FETCH CUR INTO TABLENAME;    
                --判断当前表是否需要创建序列
                SELECT INSTR(UPPER(STNAME),TABLENAME) INTO CNT FROM DUAL;               
                IF(CNT = 0) THEN             
                    --判断当前表是否存在主键     
                    SELECT COUNT(*) INTO CNT FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME  = (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
                    WHERE TABLE_NAME = TABLENAME AND CONSTRAINT_TYPE = 'P');
                    IF CNT = 1 THEN
                       --获取当前表的主键名称
                        SELECT COLUMN_NAME INTO PID FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME  = (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
                        WHERE TABLE_NAME = TABLENAME AND CONSTRAINT_TYPE = 'P');
                        --获取主键的数据类型
                        SELECT DATA_TYPE INTO PIDTYPE FROM USER_TAB_COLS WHERE TABLE_NAME = TABLENAME AND COLUMN_NAME = PID;
                        IF (PID IS NOT NULL AND PIDTYPE = 'NUMBER') THEN                   
                          --判断当前序列是否存在  
                          SELECT COUNT(*) INTO CNT FROM USER_SEQUENCES T WHERE T.SEQUENCE_NAME =  'SEQ_' || TABLENAME;
                          IF (CNT = 0) THEN
                            --获取当前表主键的最大值
                            STRSQL := 'SELECT MAX('|| PID ||') FROM '|| TABLENAME;                     
                            EXECUTE IMMEDIATE STRSQL INTO MAX_ID;
                            IF MAX_ID IS NULL THEN
                               MAX_ID := 1;
                            ELSE 
                               MAX_ID := MAX_ID + 1;
                            END IF;     
                            --根据最大值创建序列                                     
                            STRSQL := 'CREATE SEQUENCE SEQ_' || TABLENAME || ' INCREMENT BY 1 START WITH ' || MAX_ID || ' NOMAXVALUE NOCYCLE CACHE 10'; 
                            DBMS_OUTPUT.put_line(STRSQL);                    
                            EXECUTE IMMEDIATE STRSQL;
                            --创建触发器
                            STRSQL := 'CREATE OR REPLACE TRIGGER TRIG_' || TABLENAME || ' BEFORE INSERT ON '|| TABLENAME ||' FOR EACH ROW BEGIN IF INSERTING THEN 
                                      SELECT SEQ_' || TABLENAME || '.NEXTVAL INTO :NEW.' || PID || ' FROM DUAL; END IF; END;';                      
                            EXECUTE IMMEDIATE STRSQL;
                          END IF;
                        END IF;                            
                     END IF;
                 ELSE
                     --判断当前序列或触发器是否存在，如果存在则删除
                     SELECT COUNT(*) INTO CNT FROM USER_SEQUENCES T WHERE T.SEQUENCE_NAME =  'SEQ_' || TABLENAME;
                     IF (CNT > 0) THEN
                        STRSQL := 'DROP SEQUENCE SEQ_' || TABLENAME;
                        EXECUTE IMMEDIATE STRSQL;
                     END IF;
                     SELECT COUNT(*) INTO CNT FROM USER_TRIGGERS T WHERE T.TRIGGER_NAME = 'TRIG_' || TABLENAME;
                     IF (CNT > 0) THEN
                        STRSQL := 'DROP TRIGGER TRIG_' || TABLENAME;
                        EXECUTE IMMEDIATE STRSQL;
                     END IF;                     
                 END IF;
          EXIT WHEN CUR%NOTFOUND;
        END LOOP;    
     CLOSE CUR;    
END PROC_CREATE_SEQ_TRIG;
/
--为当前用户授予动态创建序列和触发器的权限
CONN [email=SYS/ORCL@ORCL]SYS/ORCL@ORCL[/email] AS SYSDBA;
GRANT CREATE ANY SEQUENCE TO ESTATE_TM;
GRANT CREATE ANY TRIGGER TO ESTATE_TM;
CONN [email=ESTATE_TM/ESTATE@ORCL]ESTATE_TM/ESTATE@ORCL[/email];
--创建序列和触发器
DECLARE
    STRFORM VARCHAR2(4000);
BEGIN
    STRFORM := 'DF_AFORM,DF_BIGOPT,DF_CITY,DF_FORM,DF_GRANTRIGHT,DF_HOLIDAY,DF_MENU,DF_PARAMETERS,DF_REPORT,
    DF_RMENU,DF_RREPORT,DF_RRIGHT,DF_SFORM,DF_SMLOPT,DF_UINPUTOPT,DF_UMENU,DF_UREPORT,DF_URIGHT,WF_GRANTRIGHT,
    WF_OMSG,WF_RRIGHT,WF_SFORM,WF_URIGHT';
    PROC_CREATE_SEQ_TRIG(STRFORM);    
END;
/