GPT3.5生成的PLSQL代码用来删除与迁移大量对象

1，批量删除plsql

oracle通过imp导入大量对象到sysl用户下，现在需要批量删除

-----使用gpt-3.5生成plsql

DECLARE
    v_object_type VARCHAR2(50);
    v_object_name VARCHAR2(100);
BEGIN
    FOR obj IN (SELECT object_type, object_name
                FROM all_objects
                WHERE owner = 'SYSL'
                AND created >= TO_DATE('2024-03-12', 'YYYY-MM-DD'))
    LOOP
        v_object_type := obj.object_type;
        v_object_name := obj.object_name;
        
        BEGIN
            IF v_object_type = 'TABLE' THEN
                EXECUTE IMMEDIATE 'DROP TABLE SYSL.' || v_object_name;
                DBMS_OUTPUT.PUT_LINE('Table ' || v_object_name || ' dropped successfully.');
            ELSIF v_object_type = 'VIEW' THEN
                EXECUTE IMMEDIATE 'DROP VIEW SYSL.' || v_object_name;
                DBMS_OUTPUT.PUT_LINE('View ' || v_object_name || ' dropped successfully.');
            ELSIF v_object_type = 'PROCEDURE' THEN
                EXECUTE IMMEDIATE 'DROP PROCEDURE SYSL.' || v_object_name;
                DBMS_OUTPUT.PUT_LINE('Procedure ' || v_object_name || ' dropped successfully.');
            ELSIF v_object_type = 'FUNCTION' THEN
                EXECUTE IMMEDIATE 'DROP FUNCTION SYSL.' || v_object_name;
                DBMS_OUTPUT.PUT_LINE('Function ' || v_object_name || ' dropped successfully.');
            ELSIF v_object_type = 'PACKAGE' THEN
                EXECUTE IMMEDIATE 'DROP PACKAGE SYSL.' || v_object_name;
                DBMS_OUTPUT.PUT_LINE('Package ' || v_object_name || ' dropped successfully.');
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error dropping ' || v_object_type || ' ' || v_object_name || ': ' || SQLERRM);
        END;
    END LOOP;
END;
/

2，批量迁移对象

此前导入对象没有进入sysl表空间，都是user表空间，现需要迁移到sysl表空间中
----使用gpt3.5生成的plsql代码

DECLARE
    CURSOR cur_move_objects IS
          SELECT OBJECT_NAME, OBJECT_TYPE
            FROM all_objects
           WHERE OWNER = 'SYSL'
             AND OBJECT_TYPE IN ('TABLE', 'INDEX', 'SEQUENCE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE')
        ORDER BY 2;

    c_m cur_move_objects%ROWTYPE;
BEGIN
    FOR c_m IN cur_move_objects LOOP
        BEGIN
            IF c_m.OBJECT_TYPE IN ('TABLE', 'INDEX', 'SEQUENCE', 'VIEW') THEN
                EXECUTE IMMEDIATE 'ALTER ' || c_m.OBJECT_TYPE || '  SYSL.' || c_m.OBJECT_NAME || ' MOVE TABLESPACE SYSL';
                DBMS_OUTPUT.PUT_LINE(c_m.OBJECT_NAME || ' 的类型是: ' || c_m.OBJECT_TYPE || ', 迁移成功');
            ELSIF c_m.OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE') THEN
                EXECUTE IMMEDIATE 'ALTER ' || c_m.OBJECT_TYPE || ' SYSL.' || c_m.OBJECT_NAME || ' COMPILE';
                DBMS_OUTPUT.PUT_LINE(c_m.OBJECT_NAME || ' 的类型是: ' || c_m.OBJECT_TYPE || ', 迁移成功');
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error moving ' || c_m.OBJECT_TYPE || ' ' || c_m.OBJECT_NAME || ': ' || SQLERRM);
        END;
    END LOOP;

    COMMIT;
END;
/