title: How to return a record from an existing table from an Oracle PL/SQL function?
body: <p>I know it seems like a basic thing, but I've never done this before.</p>
  
  <p>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 <em>best</em> way to do it (read: I'm not all that happy with what I've found).</p>
  
  <p>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.</p>
  
  <p>This is the basic outline of the code in my head at the moment (read: no idea if syntax is even close to correct):</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <p>I'm open to all potential solutions and commentary, but I do <em>really</em> 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.</p>