/***
[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;
[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;
/
本文转自My_King1 51CTO博客，原文链接：http://blog.51cto.com/apprentice/1360597，如需转载请自行联系原作者
