Use table function to perform actions on every row of data

Question

I have a query that returns multiple records based on the Number ID (parameter) passed into it. I would like to create a table function which takes in "num_id" (number datatype) as a parameter, passes that into a query, performs operations on every record of that query's result, and returns "mainKey" (character datatype) as a record.

Every tutorial I find has me create an Object and Table of that Object before I create the function.. I don't quite understand the use of this.

I also don't understand where I would add my original query (the one that retrieves records from a DB).

Can anyone please show me how I would achieve this? I was able to perform actions on every record

Here is my work so far:

-- create object
CREATE TYPE test_t AS OBJECT
    (mainKey char);

-- create collection type:
CREATE TYPE testSet_t AS TABLE OF test_t;

--Create table function: "test_tf"
CREATE OR REPLACE FUNCTION test_tf 
    (num_id IN NUMBER)
    RETURN char
    IS
        z char;
    BEGIN
        SELECT one_column_name from testSet_t
    END;

I used to be able to perform operations in every record with a cursor and a for loop... but here I was only able to print data as dbms_output. I need to be able to produce some records as a result. Here is that cursor attempt:

DECLARE
    cursor cur_test IS
    <my select statement which returns 4 records>;
BEGIN
    FOR ln_index IN cur_test
    LOOP
        DBMS_OUTPUT.put_line(ln_index.one_column_name);
        DBMS_OUTPUT.put_line(ln_index.another_column_name);
    END LOOP;
END;

The above code works fine and prints to dbms output... but I need to be able to perform operations to the data in each line, and return records instead of DBMS Output.

Answer 1

For Oracle 19c (19.7) and above you may use SQL_MACRO to create a table-valued function that may be used as parameterized view.

create table t(id, val)
as
select level, mod(level, 4)
from dual
connect by level < 10

create function f_do_smth(
  p1 int, p2 in int
) return timestamp
is
begin
  /*To demonstrate some work*/
  dbms_session.sleep(trunc(p1+p2)/10);
  return systimestamp;
end;/

create function f_get_t_byval (
  p_val in int
) return varchar2
sql_macro(table)
is
begin
  return '
    select
      t.*,
      f_do_smth(
        p1 => t.id,
        /*Here we use a parameter as a parameter
          for processing, not only as a filter*/
        p2 => f_get_t_byval.p_val
      ) as processed_result
    from t
    where val >= f_get_t_byval.p_val
  ';
end;/

select *
from f_get_t_byval(p_val => 2)

ID	VAL	PROCESSED_RESULT
2	2	16-NOV-22 22.18.31.164939000
3	3	16-NOV-22 22.18.31.668203000
6	2	16-NOV-22 22.18.32.508269000
7	3	16-NOV-22 22.18.33.468175000

fiddle

UPD. If you want to have a more generic way to apply something to arbitrary table, you may also use Polymorphic Table Function (PTF) concept, implementing transformation logic inside PTF package and applying it to the different tables (or CTEs). It is available since 18c.

Below is an example of application of the same f_do_smth to the table and column specified in the function call (runtime), not in the implementation. Please note, that tab and cols parameters (of type table and columns respectively) are identifiers, so they cannot be parameterized (via bind variables or string expressions) in this example.

create or replace package pkg_proc as
  /*Package to implement PTF*/

  function describe(
    tab in out dbms_tf.table_t,
    cols in out dbms_tf.columns_t,
    p_val in int,
    proc_res_nm in dbms_id default 'RES'
  ) return dbms_tf.describe_t
  ;

  procedure fetch_rows(
    p_val in int,
    proc_res_nm in dbms_id default 'RES'
  );
end pkg_proc;

create or replace package body pkg_proc as

  function describe(
    /*Input table identifier*/
    tab in out dbms_tf.table_t,
    /*Input columns' identifiers to be processed by the function logic*/
    cols in out dbms_tf.columns_t,
    /*Parameter to be passed to the processing function*/
    p_val in int,
    /*New column name with the result of processing*/
    proc_res_nm in dbms_id default 'RES'
  ) return dbms_tf.describe_t
  as
    l_col_tmp dbms_tf.column_t;
  begin
    /*Mark input column to be used for subsequent row processing*/
    for i in 1..tab.column.count loop
      for j in 1..cols.count() loop
        if tab.column(i).description.name = cols(j) then
          /*Pass it to the FETCH step making it
            available in the implementation logic*/
          tab.column(i).for_read := TRUE;
          /*Do not modify this column during processing*/
          tab.column(i).pass_through := TRUE;
        end if;
      end loop;
    end loop;

    /*Declare new output column*/
    return dbms_tf.describe_t(
      new_columns => dbms_tf.columns_new_t(
        1 => dbms_tf.column_metadata_t(
          name => proc_res_nm,
          type => dbms_tf.type_timestamp
        )
      )
    );
  end;

  procedure fetch_rows(
    p_val in int,
    proc_res_nm in dbms_id default 'RES'
  )
  /*Process rowset and perform "action" on each row*/
  as
    rowset dbms_tf.row_set_t;
    num_rows pls_integer;
    col dbms_tf.tab_number_t;
    new_col dbms_tf.tab_timestamp_t;
  begin
    /*Get "rows" projectiong only required column.
    May be replaced by get_row_set to obtain all for_read=TRUE columns
    */
    dbms_tf.get_col(
      columnId => 1,
      collection => col
    );

    for rn in 1..col.count() loop
      /*Calculate new column value in the same row*/
      new_col(rn) := f_do_smth(
        p1 => col(rn),
        p2 => p_val
      );
    end loop;

    /*Put calculated column to output*/
    dbms_tf.put_col(
      /*The same ID as in DESCRIBE procedure*/
      columnid => 1,
      collection => new_col
    );
  end;
end pkg_proc;

create or replace function f_apply_do_smt(
  tab in table,
  col in columns,
  p_val in int,
  proc_res_nm in dbms_id default 'RES'
)
/*Function to apply f_do_smth using PTF*/
return table pipelined
row polymorphic using pkg_proc;

And application of this function to different columns of the table.

• column val

select *
from f_apply_do_smt(t, columns(val), 2)
where val > 1

ID	VAL	RES
2	2	2022-11-17 12:49:55.532608000
3	3	2022-11-17 12:49:57.212626000
6	2	2022-11-17 12:49:58.571941000
7	3	2022-11-17 12:50:00.252031000

• column id

select *
from f_apply_do_smt(t, columns(id), 2)

ID	VAL	RES
1	1	2022-11-17 12:56:33.932650000
2	2	2022-11-17 12:56:35.292699000
3	3	2022-11-17 12:56:36.972652000
4	0	2022-11-17 12:56:39.052664000
5	1	2022-11-17 12:56:41.452734000
6	2	2022-11-17 12:56:44.172713000
7	3	2022-11-17 12:56:47.212648000
8	0	2022-11-17 12:56:50.572676000
9	1	2022-11-17 12:56:54.252680000

• and another table (CTE) with another column

with sample_tab(a) as (
  select level
  from dual
  connect by level < 4
)
select *
from f_apply_do_smt(sample_tab, columns(a), 1, 'AND_EVEN_CTE')

A	AND_EVEN_CTE
1	2022-11-17 12:58:59.852804000
2	2022-11-17 12:59:00.892778000
3	2022-11-17 12:59:02.252728000