oracle pl/sql delete from starts with

ลบข้อมูลทุกๆ table ที่ขึ้นต้นด้วย XXX ภายใต้ schema ที่กำหนด (ไม่ได้เช็ค constraint)

procedure

create or replace procedure delete_from_starts_with(p_schema varchar2, p_prefix varchar2) as
begin
    for record in (select x.table_name origin_name
            from dba_tables x
            where x.owner = p_schema
                  and x.table_name like (p_prefix || '_%')
        ) loop
      execute immediate ('delete from ' || record.origin_name);
    
    end loop;
end;

example to use

begin
    delete_from_starts_with('MY_SCHEMA', 'MY_TABLE_NAME_PREFIX');
end;