بتاريخ: 28 مارس 201115 سنة comment_210202 DECLARE CURSOR DOOM IS SELECT TABLE_NAME FROM USER_TABLES; V_ID NUMBER; V_DML VARCHAR2(3000); V_PK_COL VARCHAR2(100); V_FOUND NUMBER; BEGIN FOR REC IN DOOM LOOP IF LENGTH(REC.TABLE_NAME||'_SEQ') >30 THEN SELECT COUNT(*) INTO V_FOUND FROM USER_SEQUENCES WHERE SEQUENCE_NAME=SUBSTR(REC.TABLE_NAME,1,26)||'_SEQ'; ELSE SELECT COUNT(*) INTO V_FOUND FROM USER_SEQUENCES WHERE SEQUENCE_NAME=REC.TABLE_NAME||'_SEQ'; END IF; IF V_FOUND=0 THEN BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*)+1 FROM '||REC.TABLE_NAME INTO V_ID; IF LENGTH(REC.TABLE_NAME||'_SEQ') >30 THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE '||SUBSTR(REC.TABLE_NAME,4,30)||'_SEQ'||' START WITH '||V_ID||' INCREMENT BY 1 MINVALUE '||V_ID||' MAXVALUE 9999999999 NOCYCLE NOCACHE ORDER'; ELSE EXECUTE IMMEDIATE 'CREATE SEQUENCE '||REC.TABLE_NAME||'_SEQ'||' START WITH '||V_ID||' INCREMENT BY 1 MINVALUE '||V_ID||' MAXVALUE 9999999999 NOCYCLE NOCACHE ORDER'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(V_DML); END; ELSE --- THE SEQUENCE IS FOUND DONT DO ANYTHING NULL; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); END; تقديم بلاغ
انضم إلى المناقشة
يمكنك المشاركة الآن والتسجيل لاحقاً. إذا كان لديك حساب, سجل دخولك الآن لتقوم بالمشاركة من خلال حسابك.