How to return a record from an existing table from an Oracle PL/SQL function?

Question

I know it seems like a basic thing, but I've never done this before.

I'd like to return a single record from an existing table as the result of an Oracle PL/SQL function. I've found a few different ways of doing this already, but I'm interested in the best way to do it (read: I'm not all that happy with what I've found).

The jist of what I am doing is this... I have a table called 'users', and I want a function 'update_and_get_user' which given a UserName (as well as other trusted information about said user) will potentially perform various actions on the 'users' table, and then return either zero or one row/record from said table.

This is the basic outline of the code in my head at the moment (read: no idea if syntax is even close to correct):

CREATE FUNCTION update_and_get_user(UserName in VARCHAR2, OtherStuff in VARCHAR2)
    RETURN users PIPELINED IS
  TYPE ref0 IS REF CURSOR;
  cur0       ref0;
  output_rec users%ROWTYPE;
BEGIN
  -- Do stuff

  -- Return the row (or nothing)
  OPEN cur0 FOR 'SELECT * FROM users WHERE username = :1'
    USING UserName;

  LOOP
    FETCH cur0 INTO output_rec;
    EXIT WHEN cur0%NOTFOUND;
    PIPE ROW(output_rec);
  END LOOP;
END update_and_get_user;

I've seen examples where a record or table is returned, the type of record or table having been created / declared beforehand, but it seems like if the table has already been defined, I should be able to utilize that, and thus not have to worry about syncing the type declaration code if table changes are ever made.

I'm open to all potential solutions and commentary, but I do really want to keep this in a single PL/SQL function (as opposed to code in some other language / framework that communicates with the database multiple times, finishing with some form of 'SELECT * FROM users WHERE username=blah') as the system calling the function and the database itself may be different cities. Outside of that limit, I'm open to changing my thinking.

Answer 1

This is how I would do it. Variables/table-names/column-names are case-insensitive in Oracle, so I would use user_name instead of UserName.

CREATE TABLE users( UserName varchar2(20), OtherStuff VARCHAR2(20) );

Function update_and_get_user. Note that I return a ROWTYPE instead of Pipelined Tables.

CREATE OR REPLACE FUNCTION update_and_get_user(
  in_UserName   IN users.UserName%TYPE,
  in_OtherStuff IN users.OtherStuff%TYPE )
RETURN users%ROWTYPE
IS
  output_rec users%ROWTYPE;
BEGIN
  UPDATE users
  SET OtherStuff = in_OtherStuff
  WHERE UserName = in_UserName
    RETURNING UserName, OtherStuff
    INTO output_rec;
  RETURN output_rec;
END update_and_get_user;

And this is how you would call it. You can not check a ROWTYPE to be NULL, but you can check username for example.

DECLARE
  users_rec users%ROWTYPE;
BEGIN
  users_rec := update_and_get_user('user', 'stuff');
  IF( users_rec.username IS NOT NULL ) THEN
    dbms_output.put_line('FOUND: ' || users_rec.otherstuff);
  END IF;
END;

---

A solution using PIPED ROWS is below, but it doesn't work that way. You can not update tables inside a query.

SELECT * FROM TABLE(update_and_get_user('user', 'stuff'))

ORA-14551: cannot perform a DML operation inside a query

Solution would look like that:

CREATE OR REPLACE TYPE users_type
AS OBJECT
(
  username   VARCHAR2(20),
  otherstuff VARCHAR2(20)
)

CREATE OR REPLACE TYPE users_tab
   AS TABLE OF users_type;

CREATE OR REPLACE FUNCTION update_and_get_user(
  in_UserName   IN users.username%TYPE,
  in_OtherStuff IN users.otherstuff%TYPE )
RETURN users_tab PIPELINED
IS
  output_rec users%ROWTYPE;
BEGIN
  UPDATE users
  SET OtherStuff = in_OtherStuff
  WHERE UserName = in_UserName
    RETURNING username, otherstuff
    INTO output_rec;
  PIPE ROW(users_type(output_rec.username, output_rec.otherstuff));
END;