通用的Oralce数据表删除函数

通用的Oralce数据表删除函数,根据查询SQL动态生成的删除SQL,并执行删除动作的Oracle函数,参数表名和查询SQL,是否自动提交事务:

function delete_tbl(   p_tbl_nm    in varchar2,
                       p_query     in varchar2,
                       p_autocommit in boolean default false)
return number
is
    --l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_cnt           number default 0;
    desctab         DBMS_SQL.DESC_TAB;
    desccnt         PLS_INTEGER;

begin
    dbms_sql.parse(l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns(l_thecursor, desccnt, desctab);
       
    for i in 1 .. desccnt loop
        begin
          dbms_sql.define_column( l_theCursor,i, l_columnValue,18);
        exception
          when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;   

    l_status := dbms_sql.execute(l_theCursor);
   
    --如果没有数据返回0
    if (dbms_sql.fetch_rows(l_theCursor) <= 0 ) then
      dbms_sql.close_cursor(l_theCursor);
      return 0;
    end if;
   
    loop
        dbms_sql.column_value( l_theCursor, 1, l_columnValue);
       
        execute immediate 'delete '|| p_tbl_nm || ' where rowid = ''' || l_columnValue || ''''; 
       
        l_cnt := l_cnt+1;
        if ((l_cnt mod 10000) = 0) and p_autocommit then
          commit;
          --rollback;
        end if;
        exit when (dbms_sql.fetch_rows(l_theCursor) <= 0 );
    end loop;
    if p_autocommit then
      commit ; 
      --rollback;
    end if;
   
    dbms_sql.close_cursor(l_theCursor);

    return l_cnt;
   
exception
  when others then
    rollback;    
   
    if dbms_sql.is_open(l_theCursor) then
      dbms_sql.close_cursor(l_theCursor);
    end if;
 
    pkg_data_backup.pkg_log('PKG_DATA_BACKUP',
                   -1, --Level
                   'delete_tbl', --procedure or function name
                   ' [sql-text]:' || p_query,
                   null, --operator
                   sysdate, --operate date
                   sqlerrm); --errormessage;
    raise; 
                    
    return -1;  
       
end delete_tbl;