Passing the whole actual row to function-CodePudding

I wonder if it is still not possible to pass an actual row as a parameter to a function or procedure in oracle. I saw already some questions according to this topic but haven't found an answer which is good for me. I need doing something like this:

function my_func(myrow t%rowtype)AS
BEGIN
END;

So the function would get the row as parameter.

Select 
   t.c1, 
   my_func(t.*) -- where t.* is actual row processed.
from t;

And in the select I would love to pass the single row, which is actually proccesed. I wish to avoid to pass many parameters, which would be a case if I would pass them separately like this:

Select 
       t.c1, 
       my_func(t.c1,... t.c10) 
    from t;

I also don't want to pass an actual id as I don't want to do an additional select in a function.

CodePudding user response：

It would be great, if Oracle could handle the rowtype just as you suggest. Unfortunatly, it doesn't. The syntax in the query is not allowed, because Oracle's SQL does not know about PL/SQL's rowtype.

I suggest you use the rows' ROWID instead, so the function can access the row immediately. This is different from using the row's primary key (e.g. an ID), beacuse Oracle doesn't have to find the row. With the ROWID it already knows where it is.

The function

CREATE OR REPLACE FUNCTION my_func(p_rowid urowid) RETURN INTEGER AS
  v_row mytable%ROWTYPE;
BEGIN
  SELECT * INTO v_row FROM mytable WHERE rowid = p_rowid;
  RETURN v_row.some_column   v_row.some_other_column;
END;

The query

select t.*, my_func(t.rowid)
from mytable t;

CodePudding user response：

I'd second @Thorsten's suggestion to use rowid, but just for fun, you could work around this restriction by converting the whole row to JSON - since the json_object() function does allow t.* as an argument - and then extract the required values from the JSON in the function.

So for example, if your table had column c2 as a date and you wanted to add 7 days to it, you could do:

function my_func(myrow json) return date AS
BEGIN
  return json_value(myrow, '$.C2' returning date)   interval '7' day;
END;
/

and then call it as:

select 
   t.c1, 
   my_func(cast(json_object(t.*) as json))
from t;

fiddle

That doesn't really seem any more intuitive, and may well perform worse, than re-querying with the rowid (since the data will be cached anyway, and converting to/from JSON may have a higher overhead).

Neither really seems much more useful than listing the columns as separate arguments though. If the table definition changed then the function and call would pick up the added/removed columns automatically; but the function would still need to be modified if it needed to refer to an added column, or stop referring to a removed one (which would cause an error otherwise).

CodePudding user response：

The %ROWTYPE syntax creates a PL/SQL record data type that can be used in a PL/SQL scope (but not in an SQL scope).

Therefore:

Select t.c1, 
       my_func(t.*) -- where t.* is actual row processed.
from   t;

Will not work because (1) the syntax is not supported and (2) you cannot create the PL/SQL record type in the SQL scope.

However, using PL/SQL, if you declare the function:

CREATE FUNCTION my_func(myrow t%rowtype) RETURN NUMBER
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE( myrow.id || ', ' || myrow.name );
  RETURN 42;
END;
/

then you can use a PL/SQL block and loop through a cursor passing each row to the function:

DECLARE
  unused NUMBER;
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR i IN (SELECT * FROM t) LOOP
    unused := my_func(i);
  END LOOP;
END;
/

Which, for the sample data:

CREATE TABLE t (id, name) AS
  SELECT 1, 'Alice' FROM DUAL UNION ALL
  SELECT 2, 'Betty' FROM DUAL UNION ALL
  SELECT 3, 'Carol' FROM DUAL UNION ALL
  SELECT 4, 'Debra' FROM DUAL;

Outputs:

1, Alice
2, Betty
3, Carol
4, Debra

fiddle