tags:

views:

189

answers:

1

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.

+3  A: 

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;
Peter Lang
+1 Comprehensive answer Peter - you beat me to it (need to speed up my typing)In case of need Paul, this link(http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/rowtype_attribute.htm#LNPLS01342) explains the %rowtype attribute
carpenteri
+1 Peter! I wish I could give multiple up votes. First, this looks really good. Second, I had no idea you could do "RETURNING ... INTO ..." in an UPDATE, that's really cool and will boost performance. Third, I had no idea one cannot perform an update inside a query of a table function, which would have eventually frustrated me greatly. I will mark this as the accepted answer once I get this implemented / verify it. Thank you!
Paul Hooper
Well, good luck then :)
Peter Lang
@Paul: I didn't think you could invoke DML inside of a function -- what happens if a SELECT statement invokes it?
Adam Musch
@Adam Musch: Oracle allows DML inside a function. It works fine when called from PL/SQL, but if it is invoked in a `SELECT` statement, you'll still get the `ORA-14551`. If one is not comfortable with that, consider writing a procedure instead of the function and returning the `ROWTYPE` as `OUT`-parameter.
Peter Lang
@Adam Musch: I my particular case, this function will most often be called from a .NET application, not a SELECT statement (it's part of a customized MembershipProvider). In the other rare cases, it will simply have to be from a PL/SQL function, as Peter described, which is just fine.
Paul Hooper
@Peter Lang: The PL/SQL works great, and it exactly answered the question I asked. Unfortunately, I wanted to call the function from the .NET world, which does not support a ROWTYPE style return value. So, I eventually created a whole bunch of OUT parameters. Sigh. Much uglier than I had hoped for, but thanks for answering the question. If nothing else I learned a lot.
Paul Hooper