Is this possible to apply a function to every fields of table (groupy by type)-CodePudding

I would like to replace every string are null by 'n' and every number by 0.

Is there a way to do that?

With a polymorphic table function I can select all the columns of a certain type but I can't modify the value of the columns.

CodePudding user response：

Use COALESCE or NVL and list the columns you want to apply them to:

SELECT COALESCE(col1, 'n') AS col1,
       COALESCE(col2, 0)   AS col2,
       COALESCE(col3, 'n') AS col3
FROM   table_name;

CodePudding user response：

Yes, it is possible with PTF also. You may modify columns in case you've set pass_through to false for the column in the describe method (to drop it) and copy it into new_columns parameter of the describe.

Below is the code:

create package pkg_nvl as
  /*Package to implement PTF*/

  function describe(
    tab in out dbms_tf.table_t
  ) return dbms_tf.describe_t
  ;
  
  procedure fetch_rows;
end pkg_nvl;
/

create package body pkg_nvl as

  function describe(
    tab in out dbms_tf.table_t
  ) return dbms_tf.describe_t
  as
    modif_cols dbms_tf.columns_new_t;
    new_col_cnt pls_integer := 0;
  begin
    /*Mark input columns as used and as modifiable for subsequent row processing*/
    for i in 1..tab.column.count loop
      if tab.column(i).description.type in (
        dbms_tf.type_number,
        dbms_tf.type_varchar2
      ) then
        /*Modifiable*/
        tab.column(i).pass_through := FALSE;
        /*Used in the PTF context*/
        tab.column(i).for_read := TRUE;
        /* Propagate column to the modified*/
        modif_cols(new_col_cnt) := tab.column(i).description;
        new_col_cnt := new_col_cnt   1;
      end if;
    end loop;
    
    /*Return the list of modified cols*/
    return dbms_tf.describe_t(
      new_columns => modif_cols
    );
  end;
  
  procedure fetch_rows
  /*Process rowset and replace nulls*/
  as
    rowset dbms_tf.row_set_t;
    num_rows pls_integer;
    in_col_vc2 dbms_tf.tab_varchar2_t;
    in_col_num dbms_tf.tab_number_t;
    new_col_vc2 dbms_tf.tab_varchar2_t;
    new_col_num dbms_tf.tab_number_t;
  begin
    /*Get rows*/
    dbms_tf.get_row_set(
      rowset => rowset,
      row_count => num_rows
    );
    
    
    for col_num in 1..rowset.count() loop
    /*Loop through the columns*/
      for rn in 1..num_rows loop
        /*Calculate new values in the same row*/
        /*Get column by index and nvl the value for return column*/
        if rowset(col_num).description.type = dbms_tf.type_number then
          dbms_tf.get_col(
            columnid => col_num,
            collection => in_col_num
          );
          new_col_num(rn) := nvl(in_col_num(rn), 0);
        elsif rowset(col_num).description.type = dbms_tf.type_varchar2 then
          dbms_tf.get_col(
            columnid => col_num,
            collection => in_col_vc2
          );
          new_col_vc2(rn) := nvl(in_col_vc2(rn), 'n');
        end if;
      end loop;

      /*Put the modified column to the result*/
      if rowset(col_num).description.type = dbms_tf.type_number then
        dbms_tf.put_col(
          columnid => col_num,
          collection => new_col_num
        );
      elsif rowset(col_num).description.type = dbms_tf.type_varchar2 then
        dbms_tf.put_col(
          columnid => col_num,
          collection => new_col_vc2
        );
      end if;
    end loop;

  end;
end pkg_nvl;
/

create function f_replace_nulls(tab in table)
/*Function to replace nulls using PTF*/
return table pipelined
row polymorphic using pkg_nvl;
/

with a as (
  select
    1 as id, 'q' as val_vc2, 1 as val_num
  from dual
  union all
  select
    2 as id, '' as val_vc2, null as val_num
  from dual
  union all
  select
    3 as id, ' ' as val_vc2, 0 as val_num
  from dual
)
select
  id
  , a.val_num
  , a.val_vc2
  , n.val_num as val_num_repl
  , n.val_vc2 as val_vc2_repl
from a
  join f_replace_nulls(a) n
  using(id)

ID | VAL_NUM | VAL_VC2 | VAL_NUM_REPL | VAL_VC2_REPL
-: | ------: | :------ | -----------: | :-----------
 3 |       0 |         |            0 |             
 2 |    null | null    |            0 | n           
 1 |       1 | q       |            1 | q

db<>fiddle here

CodePudding user response：

The way I understood the question, you actually want to modify table's contents. If that's so, you'll need dynamic SQL.

Here's an example; sample data first, with some numeric and character columns having NULL values:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(20)
 SALARY                                             NUMBER

SQL> select * from test order by id;

        ID NAME           SALARY
---------- ---------- ----------
         1 Little            100
         2                   200
         3 Foot                0
         4                     0

Procedure reads USER_TAB_COLUMNS, checking desired data types (you can add some more, if you want), composes the update statement and executes it:

SQL> declare
  2    l_str varchar2(1000);
  3  begin
  4    for cur_r in (select column_name, data_type
  5                  from user_tab_columns
  6                  where table_name = 'TEST'
  7                    and data_type in ('CHAR', 'VARCHAR2')
  8                 )
  9    loop
 10      l_str := 'update test set ' ||
 11                cur_r.column_name || ' = nvl(' || cur_r.column_name ||', ''n'')';
 12      execute immediate l_str;
 13    end loop;
 14
 15    --
 16
 17    for cur_r in (select column_name, data_type
 18                  from user_tab_columns
 19                  where table_name = 'TEST'
 20                    and data_type in ('NUMBER')
 21                 )
 22    loop
 23      l_str := 'update test set ' ||
 24                cur_r.column_name || ' = nvl(' || cur_r.column_name ||', 0)';
 25      execute immediate l_str;
 26    end loop;
 27  end;
 28  /

PL/SQL procedure successfully completed.

Result:

SQL> select * from test order by id;

        ID NAME           SALARY
---------- ---------- ----------
         1 Little            100
         2 n                 200
         3 Foot                0
         4 n                   0

SQL>