oracle结合序列和触发器生成表的主键

Oracle序列与触发器自动化创建

最新推荐文章于 2025-07-07 09:26:01 发布

最新推荐文章于 2025-07-07 09:26:01 发布 · 126 阅读

文章标签：

#Oracle #Hibernate #Cache

Oracle 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一个Oracle PL/SQL过程，该过程能够自动创建序列和触发器以支持自动增长的主键字段。通过传入表名字符串，此过程会检查每张表并根据需要创建对应的序列和触发器。

/***
*@PARAM 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 SYS/ORCL@ORCL AS SYSDBA;
GRANT CREATE ANY SEQUENCE TO ESTATE_TM;
GRANT CREATE ANY TRIGGER TO ESTATE_TM;
CONN ESTATE_TM/ESTATE@ORCL;
--创建序列和触发器
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;
/