ORACLE生成所有表对应的序列

DECLARE
V_EXIST_FLAG NUMBER; --判断某序列是否已经存在
V_TABLE_NAME VARCHAR2(30); --表明
V_SQEUENCE_NAME VARCHAR2(30); --序列表
V_CREATE_SEQUENCE_SQL VARCHAR2(1000); --动态拼接形成的创建序列的sql语句
BEGIN
FOR X IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'SYS%') LOOP
SELECT COUNT(*)
INTO V_EXIST_FLAG
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM USER_SEQUENCES
WHERE
SEQUENCE_NAME = 'SEQ_' || SUBSTR(X.TABLE_NAME, 5, 26));
IF V_EXIST_FLAG = 0 THEN
V_CREATE_SEQUENCE_SQL := 'CREATE SEQUENCE ' || 'SEQ_' ||
SUBSTR(X.TABLE_NAME, 5, 26) ||
' minvalue 1 maxvalue 9999999999999999999999999999 start with 100 increment by 1 cache 20';
DBMS_OUTPUT.put_line(V_CREATE_SEQUENCE_SQL);--输出内容表示创建了一个相关序列
EXECUTE IMMEDIATE V_CREATE_SEQUENCE_SQL;
END IF;
END LOOP;
FOR X IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'T%') LOOP
SELECT COUNT(*)
INTO V_EXIST_FLAG
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM USER_SEQUENCES
WHERE
SEQUENCE_NAME = 'SEQ_' || SUBSTR(X.TABLE_NAME, 3, 26));
IF V_EXIST_FLAG = 0 THEN
V_CREATE_SEQUENCE_SQL := 'CREATE SEQUENCE ' || 'SEQ_' ||
SUBSTR(X.TABLE_NAME, 3, 26) ||
' minvalue 1 maxvalue 9999999999999999999999999999 start with 100 increment by 1 cache 20';
DBMS_OUTPUT.put_line(V_CREATE_SEQUENCE_SQL);--输出内容表示创建了一个相关序列
EXECUTE IMMEDIATE V_CREATE_SEQUENCE_SQL;
END IF;
END LOOP;
END;